Tales From A Lazy Fat DBA

Solution steps when you started receiving ORA-01207.

Reason: The control file change sequence number in the data file is greater than the number in the control file. This implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop happening without correcting the real problem. Every attempt to open the database will advance the control file change sequence number until it is great enough.

Scenario: In my scenario one of our SIT box lost all the copies of multiplexed ControlFiles from the system and after couple of minutes Database got crashed. While trying fixing the problem we discovered that we did’nt have the backup copies of Control Files Available, but we had a mount point backup available which holds all backup information but due to issues with our SMARTS (Monitoring Tool) backup script got failed to update the control file backup. So we didn’t knew that the ControlFile backup available was too old and have a very old sequence number registered.

So, after restoring the controlfile from that outdated backup we started recieving this error message during Databse OPEN:

SQL> alter database open;

ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: ‘/app/oracle/oradata/rac10g/system01.dbf’
ORA-01207: file is more recent than control file – old control file

Solution: recreate the control file.

1) dump controlfile to trace
SQL> alter database backup controlfile to trace as ‘/tmp/ccc.sql’;

2) startup database nomount
SQL> startup nomount

3) create the control file
SQL> @/tmp/ccc.sql
Control file created.

sys@SIT4435Z89> recover database using backup controlfile;

SQL> recover database using backup controlfile ;
ORA-00279: change 1904510 generated at 12/11/2012 15:46:23 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1904510 for thread 1 is in sequence #62

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/orcl/onlinelog/group_1.261.801074115
ORA-00310: archived log contains sequence 61; sequence 62 required
ORA-00334: archived log: ‘+DATA/orcl/onlinelog/group_1.261.801074115’

Now it is asking for Log sequence 62 which is not available under archive dest
ASMCMD> ls
thread_1_seq_52.314.801739289
thread_1_seq_53.294.801743225
thread_1_seq_54.295.801743259
thread_1_seq_55.285.801743403
thread_1_seq_56.283.801757235
thread_1_seq_57.282.801758303
thread_1_seq_58.281.801759635
thread_1_seq_59.286.801761231
thread_1_seq_60.305.801762327
thread_1_seq_61.296.801762385

We only have seq:61 available.

Hint: It could be still inside REDO Log groups and is not archived before the crash or Control file loss.
Hence we’ll check both of the two avialable redo groups to find seq:62
+DATA/orcl/onlinelog/group_1.261.801074115
+DATA/orcl/onlinelog/group_2.262.801074117

SQL> startup  mount
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size                  2213856 bytes
Variable Size            1140852768 bytes
Database Buffers          486539264 bytes
Redo Buffers                7208960 bytes
Database mounted.
SQL> recover database using backup controlfile ;
ORA-00279: change 1904510 generated at 12/11/2012 15:46:23 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1904510 for thread 1 is in sequence #62

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/orcl/onlinelog/group_1.261.801074115
ORA-00310: archived log contains sequence 61; sequence 62 required
ORA-00334: archived log: ‘+DATA/orcl/onlinelog/group_1.261.801074115’

SQL> recover database using backup controlfile ;
ORA-00279: change 1904510 generated at 12/11/2012 15:46:23 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-00280: change 1904510 for thread 1 is in sequence #62

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
+DATA/orcl/onlinelog/group_2.262.801074117
Log applied.
Media recovery complete.

Shows it was available under redo group: 2 (could be Current or Active) and it is successfully applied.

Finally we’d open DB in reset log mode.

SQL> alter database open resetlogs ;
Database altered.