ORA-01207: file is&nbs…

OS:

[root@yoon ~]# more /etc/oracle-release
Oracle Linux Server release 5.7

 

DB:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production
PL/SQL Release 11.2.0.3.0 - Production

 

测试：

1、对数据库的控制文件备份

2、删除现有的控制文件后，用备份的控制文件还原，出现old control file报错

3、服务器突然停电、也会引起这种情况

原因：

根据oracle运行原理，数据库在运行期间，由于检查点发生等原因不断的更新控制文件，同时数据库在关闭和重启过程中都会更新控制文件的内容，But，oracledb突然停电或者其他原因，会导致当前的数据库信息无法更新到控制文件中，再次启动数据库后，当oracle检查控制文件和其他文件信息是否一致时，就出现了这个错误。

 

SQL> startup
ORACLE instance started.

Total System Global Area 1653518336 bytes
FixedSize                 2228904 bytes
VariableSize            956304728 bytes
DatabaseBuffers         687865856 bytes
RedoBuffers               7118848 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1:'/u01/oracle/oradata/yoon/system01.dbf'
ORA-01207: file is more recent than control file - old controlfile

 

解决方法：

1、startup mount

2、alter database backup controlfile to trace

3、create controlfile script from the trace file

4、RECOVER DATABASE USING BACKUP CONTROLFILE

5、alter database open resetlogs

 

具体步骤：

1.

SQL> shutdown immediate
ORA-01109: database not open

 

2.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1653518336 bytes
FixedSize                 2228904 bytes
VariableSize            956304728 bytes
DatabaseBuffers         687865856 bytes
RedoBuffers               7118848 bytes
Database mounted.
SQL>

 

3.

SQL> alter database backup controlfile totrace;

 

4.

[oracle@yoon trace]$ strings yoon_ora_3529.trc

 

5.create controlfile script

 

脚本内容如下：

SQL> CREATE CONTROLFILE REUSE DATABASE "YOON"RESETLOGS  NOARCHIVELOG
 2     MAXLOGFILES 16
 3     MAXLOGMEMBERS 3
 4     MAXDATAFILES 100
 5     MAXINSTANCES 8
 6     MAXLOGHISTORY 292
  7  LOGFILE
 8    GROUP 1'/u01/oracle/oradata/yoon/redo01.log'  SIZE 50MBLOCKSIZE 512,
 9    GROUP 2'/u01/oracle/oradata/yoon/redo02.log'  SIZE 50MBLOCKSIZE 512,
 10   GROUP 3 '/u01/oracle/oradata/yoon/redo03.log' SIZE 50M BLOCKSIZE 512
 11  DATAFILE
 12   '/u01/oracle/oradata/yoon/system01.dbf',
 13   '/u01/oracle/oradata/yoon/sysaux01.dbf',
 14   '/u01/oracle/oradata/yoon/undotbs01.dbf',
 15   '/u01/oracle/oradata/yoon/users01.dbf'
 16  CHARACTER SET ZHS16GBK;

Control file created.

 

 

SQL> RECOVER DATABASE USING BACKUPCONTROLFILE;

ORA-00279: change 1220104 generated at 09/02/2013 15:38:49needed for thread 1
ORA-00289: suggestion :
/u01/oracle/fast_recovery_area/YOON/archivelog/2013_09_02/o1_mf_1_16_%u_.arc
ORA-00280: change 1220104 for thread 1 is in sequence #16

Specify log: {=suggested | filename | AUTO | CANCEL}

/u01/oracle/oradata/yoon/redo01.log
Log applied.
Media recovery complete.

 

 

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

 

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE'/u01/oracle/oradata/yoon/temp01.dbf' REUSE;

Tablespace altered.