ORA-01207 old control file full solution

This error is the most common error in Oracle db, which is caused by many reasons. However, one of the major reasons is that the database server suddenly loses power and then restarts the database to report an error.
The cause of this problem is that the database information recorded in the control file is too old, resulting in inconsistency during database startup detection.
The control file records all information about the entire database, including data files and log files.
So why is the database information recorded in the control file too old? The reason is very simple: according to the operating principle of oracle db, the control file will be constantly updated during the database operation due to the check point and other reasons, at the same time, the contents of the control file will be updated during database shutdown and restart, but the database server suddenly loses power, resulting in the current db information cannot be updated to the control file in a timely manner, after the database is started again, this error occurs when oracle checks whether the control file and other file information are consistent.

There are two solutions to this problem:

Method 1:
U-dominated idea: Create a control file and open the database.
U steps:
1. startup mount
2. alter database backup controlfile to trace
3. create a control file creation script from the trace file,
And use the noresetlogs option
4. shutdown
5. startup nomount
Use the script generated by the 'backup controlfile to track' command above
Creaet the control file
6. recover database
7. alter database open
U step demonstration:
[Oracle @ localhost orcl] $ sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Sun Nov 19 15:26:07 2006
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
Add test data:
SQL> create table gaojf as select * from all_objects;
Table created.
SQL> insert into gaojf select * from gaojf;
49390 rows created.
SQL>/
98780 rows created.
.......................................
1580480 rows created.
SQL> commit;
Commit complete.
SQL> shutdown abort
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options

Simulating a ORA-01207 error is simple, I will not explain it here, and then proceed as follows:
[Oracle @ localhost orcl] $ sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Sun Nov 19 15:26:47 2006
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file-old control file
ORA-01207 error:
SQL> shutdown abort
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
First, delete the old control file. In fact, you can also choose not to delete it. In your personal habits, and then recreate the control file.
You can execute alter database backup controlfile to trace to generate the SQL file from the database to the mount state. The specific operation is simple and will not be described.
IXDBA. NET Community Forum
[Oracle @ localhost orcl] $ rm-rf control0 *
[Oracle @ localhost orcl] $ vi create. SQL
STARTUP NOMOUNT
Create controlfile reuse database "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
# MAXLOGHISTORY 292
LOGFILE
GROUP 1'/free/oracle/oradata/orcl/redo01.log' SIZE 50 M,
GROUP 2'/free/oracle/oradata/orcl/redo02.log 'SIZE 50 M,
GROUP 3'/free/oracle/oradata/orcl/redo03.log' SIZE 50 M
-- STANDBY LOGFILE
DATAFILE
'/Free/oracle/oradata/orcl/system01.dbf ',
'/Free/oracle/oradata/orcl/undotbs01.dbf ',
'/Free/oracle/oradata/orcl/sysaux01.dbf ',
'/Free/oracle/oradata/orcl/users01.dbf ',
'/Free/oracle/oradata/orcl/gaojfdb. dbf'
Character set AL32UTF8
;
[Oracle @ localhost orcl] $ sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Sun Nov 19 15:28:00 2006
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @ create
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 71305460 bytes
Database Buffers 92274688 bytes
Redo Buffers 2973696 bytes

Control file created.
SQL> alter database open;

Alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'
You cannot open the database directly. If you want to restore the database, execute recover database;
SQL> recover database;
Media recovery complete.
It seems that the system is reading the redo file for rollback. This is because in the above operation, after adding the test data, I executed the commit command, the shutdown abort command is executed directly, so after the restart, the database will be rolled forward and data will be restored from the redo file.
Generally, when the database server suddenly loses power, it is equivalent to executing the shutdown abort operation. Therefore, you must execute recoverdatabase after creating the control file.
If the database is shut down normally, you can open it directly without recover.
At this time, the background process executes the rollback log information as follows:
Alter database recover database
Media Recovery Start
Sun Nov 19 15:28:23 2006
Recovery of Online Redo Log: Thread 1 Group 1 Seq 22 Reading mem 0
Mem #0 errs 0:/free/oracle/oradata/orcl/redo01.log
Sun Nov 19 15:28:29 2006
Recovery of Online Redo Log: Thread 1 Group 2 Seq 23 Reading mem 0
Mem #0 errs 0:/free/oracle/oradata/orcl/redo02.log
Sun Nov 19 15:28:38 2006
Recovery of Online Redo Log: Thread 1 Group 3 Seq 24 Reading mem 0
Mem #0 errs 0:/free/oracle/oradata/orcl/redo03.log
Sun Nov 19 15:28:40 2006
Media Recovery Complete (orcl)
Completed: alter database recover database
You can open the database
SQL> alter database open;
SQL> select count (*) from gaojf;
COUNT (*)
----------
3160960

SQL>

Method 2:
Simulate ORA-01207 error is very simple, not described:

U idea: use the old control file to restore and use resetlogs to open the database.
U steps:
1: startup mount;
2: recover database using backup controlfile untilcancel;
Specify the archive log and redo file as needed.
3: alter database open resetlogs;
U operation Demonstration:
Oracle @ linux:/free/oracle/oradata/orcl> sqlplus "/as sysdba"
SQL * Plus: Release 10.2.0.1.0-Production on Sunday November 19 13:57:512006
Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size
1218316 bytes
Variable Size 88082676 bytes
Database Buffers 75497472 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'
ORA-01207: file is more recent than control file-old control file
Execute recovery:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 891145 generated at 11/18/2006 06:02:11 needed for thread 1
ORA-00289: suggestion:
/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000047 _ % u _. arc

ORA-00280: change 891145 for thread 1 is in sequence #47

Specify log: {= suggested | filename | AUTO | CANCEL}
Auto (because o1_mf_000047 _ % u _. arc archive log, I have this file here, so specify auto, at this time some archive logs are applied)
ORA-00279: change 911145 generated at 13:49:24 needed for thread 1
ORA-00289: suggestion:
/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000048 _ % u _. arc
ORA-00280: change 911145 for thread 1 is in sequence #48
ORA-00278: log file
'/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000047204 zw355r
_. Arc' no longer needed for this recovery

ORA-00279: change 911687 generated at 13:54:36 needed for thread 1
ORA-00289: suggestion:
/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000049 _ % u _. arc
ORA-00280: change 911687 for thread 1 is in sequence #49
ORA-00278: log file
'/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_1_48132 zwf289
_. Arc' no longer needed for this recovery


ORA-00308: cannot open archived log
'/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000049 _ % u _. arc'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'


This recovery method will not automatically find the online redo file, so you must manually specify it!


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 911687 generated at 13:54:36 needed for thread 1
ORA-00289: suggestion:
/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000049 _ % u _. arc
ORA-00280: change 911687 for thread 1 is in sequence #49


Specify log: {= suggested | filename | AUTO | CANCEL}
/Free/oracle/oradata/orcl/redo01.log
ORA-00310: archived log contains sequence 47; sequence 49 required
ORA-00334: archived log: '/free/oracle/oradata/orcl/redo01.log'


ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'

This redo file is not required. Specify again!

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 911687 generated at 13:54:36 needed for thread 1
ORA-00289: suggestion:
/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000049 _ % u _. arc
ORA-00280: change 911687 for thread 1 is in sequence #49

Specify log: {= suggested | filename | AUTO | CANCEL}
/Free/oracle/oradata/orcl/redo02.log
ORA-00310: archived log contains sequence 48; sequence 49 required

ORA-00334: archived log: '/free/oracle/oradata/orcl/redo02.log'


ORA-01547: warning: RECOVER succeeded but open resetlogs wocould get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/free/oracle/oradata/orcl/system01.dbf'

Still not. Continue to specify!

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 911687 generated at 13:54:36 needed for thread 1
ORA-00289: suggestion:
/Free/oracle/flash_recovery_area/ORCL/archivelog/2006_11_19/o1_mf_000049 _ % u _. arc
ORA-00280: change 911687 for thread 1 is in sequence #49


Specify log: {= suggested | filename | AUTO | CANCEL}
/Free/oracle/oradata/orcl/redo03.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select count (*) from gaojf;
COUNT (*)
----------
791392
SQL>
After the restoration is completed, the data is not lost, but the redo file is reset. We recommend that you back up the database immediately.

Both methods can be used to complete the restoration.
However, the method of recreating the control file in method 1 may be simpler and can be opened directly at last, so that the previous backup can also be used. We recommend that you use this method.
In method 2, because in recover, you also need to specify the redo file one by one to try it. Finally, the resetlogs database resets the redo file. After the recovery is complete, in the past, some backups may be unavailable. maybe I think it is a little complicated, but the goal of restoring the database is achieved.

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12