Complete solution to Oracle ORA-01207 error due to power failure

When the hardware is maintained, someone accidentally disconnected the power supply from the production library, and after restarting the enclosure and server, an Oracle 10g 10.2.0.4 startup error message appears:

The code is as follows	Copy Code
Database loading complete. ORA-01122: Database file 1 validation failed ORA-01110: Data file 1: ' E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01. DBF ' ORA-01207: File newer than control file-old control file (file is more recent than Controlfile-old Controlfile)

Due to the time constraints and unfamiliar with this error, the production library was first cut to the Dataguard physical reserve, and there was time to study how to recover the problem.

Ora-01207:old Control File Complete Solution

This error is one of the most common errors in Oracle DB, causing many reasons, but the main reason is that the database server suddenly drops power and then restarts the start database error.

The reason for this problem is that the control file records the DB information is too old, causing the database to start detection inconsistent.

The control file records all the information of the whole database, including data files, log files and so on.

So why is it that the database information that controls the file record is too old for a simple reason: according to the Oracle DB operation principle, the database will update the control files continuously during the operation due to the checkpoint, while the database will update the contents of the control files during shutdown and restart. However, a sudden drop of power from the database server can cause the current DB information to not be updated to the control file in a timely manner, and this error occurs when the Oracle detects that the control file and other file information are consistent.



So there are two ways to solve this problem:
Ixdba. NET Community Forum


Method 1:

You lead the idea: Create a control file and then open the database.

U specific 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 trace ' command above
Creaet the control file
6.recover Database
7.alter Database Open

U Step Demo:

[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, +, Oracle. All rights reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-production

With the partitioning, OLAP and Data Mining options

To 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



The mock ORA-01207 error is simple, no longer explained here, and then 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, +, Oracle. All rights reserved.

Connected to a 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

A ORA-01207 error has occurred:

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, you can actually do not delete, personal habits, and then rebuild the controlling files.

The rebuild control file can execute ALTER DATABASE backup Controlfile to trace in the DB to mount state to generate the SQL file, the operation is simple, no longer described.

Www.ixdba.net

[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 50M,

GROUP 2 '/free/oracle/oradata/orcl/redo02.log ' SIZE 50M,

GROUP 3 '/free/oracle/oradata/orcl/redo03.log ' SIZE 50M

--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, +, Oracle. All rights reserved.

Connected to a 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;
Ixdba. NET Community Forum

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 '

Can not be directly open, it seems to have to restore, the implementation of recover database;

sql> Recover database;

Media recovery complete.

Appears to be in the read Redo file roll forward, this phenomenon occurs because in the above operation, I added the test data finished, executed a commit command, and then directly executed the shutdown abort, so after the restart database to roll forward, from the redo Recover data in file.

The general database server after the sudden power off, that is, the equivalent of performing shutdown abort operation. Therefore, the recoverdatabase must be executed after the control file has been created.

If it is a normally closed database, you can open it directly without recover.

The background process then rolls forward the 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 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 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 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 at this time

sql> ALTER DATABASE open;

Sql> Select COUNT (*) from GAOJF;

COUNT (*)

----------

3160960



Sql>


Method 2:

Simulating ORA-01207 errors is simple and no longer describes:



U train of thought: Restore with old control file, finally open database with Resetlogs.

U specific steps:

1:startup Mount;
2:recover database using Backup controlfile until cancel;

Then, depending on the situation, specify archive log and redo file.
3:alter database open resetlogs;

U Operation Demo:

Oracle@linux:/free/oracle/oradata/orcl> sqlplus "/as sysdba"

Sql*plus:release 10.2.0.1.0-production on Sunday November 19 13:57:51 2006

Copyright (c) 1982, +, Oracle. All rights reserved.

Connected to a 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

To perform a restore:

sql> Recover database using Backup controlfile until cancel;

Ora-00279:change 891145 generated at 11/18/2006 06:02:11 for thread 1

Ora-00289:suggestion:

/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_47_%u_.arc
Ixdba. NET Community Forum

Ora-00280:change 891145 for thread 1 was in sequence #47



Specify log: {<ret>=suggested | AUTO | CANCEL}

Auto (due to o1_mf_1_47_%u_.arc this archive log, I have this file here, so specify auto, some archive logs are applied at this time)

Ora-00279:change 911145 generated at 11/19/2006 13:49:24 for thread 1

Ora-00289:suggestion:

/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_48_%u_.arc

Ora-00280:change 911145 for thread 1 was in sequence #48

Ora-00278:log file

'/FREE/ORACLE/FLASH_RECOVERY_AREA/ORCL/ARCHIVELOG/2006_11_19/O1_MF_1_47_2OZW355R

_.arc ' no longer needed for this recovery



Ora-00279:change 911687 generated at 11/19/2006 13:54:36 for thread 1

Ora-00289:suggestion:

/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_49_%u_.arc

Ora-00280:change 911687 for thread 1 was in sequence #49

Ora-00278:log file

'/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_48_2ozwf289

_.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_1_49_%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 would get error below

Ora-01152:file 1 is not restored out of sufficiently old backup

Ora-01110:data file 1: '/FREE/ORACLE/ORADATA/ORCL/SYSTEM01.DBF '


This recovery way, will not automatically find online redo files, so must be manually specified!



sql> Recover database using Backup controlfile until cancel;

Ora-00279:change 911687 generated at 11/19/2006 13:54:36 for thread 1

Ora-00289:suggestion:

/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_49_%u_.arc

Ora-00280:change 911687 for thread 1 was in sequence #49


Specify log: {<ret>=suggested | AUTO | CANCEL}

/free/oracle/oradata/orcl/redo01.log

Ora-00310:archived log contains sequence 47; Sequence Required

ora-00334:archived log: '/free/oracle/oradata/orcl/redo01.log '

Ora-01547:warning:recover succeeded but OPEN Resetlogs would get error below

Ora-01152:file 1 is not restored out of sufficiently old backup

Ora-01110:data file 1: '/FREE/ORACLE/ORADATA/ORCL/SYSTEM01.DBF '



Need is not this redo file, continue to specify!



sql> Recover database using Backup controlfile until cancel;

Ora-00279:change 911687 generated at 11/19/2006 13:54:36 for thread 1

Ora-00289:suggestion:

/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_49_%u_.arc

Ora-00280:change 911687 for thread 1 was in sequence #49


Specify log: {<ret>=suggested | AUTO | CANCEL}

/free/oracle/oradata/orcl/redo02.log

ora-00310:archived log contains sequence 48; Sequence Required
Ixdba. NET Technology Community

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



Ora-01547:warning:recover succeeded but OPEN Resetlogs would get error below

Ora-01152:file 1 is not restored out of 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 11/19/2006 13:54:36 for thread 1

Ora-00289:suggestion:

/free/oracle/flash_recovery_area/orcl/archivelog/2006_11_19/o1_mf_1_49_%u_.arc

Ora-00280:change 911687 for thread 1 was in sequence #49

Specify log: {<ret>=suggested | 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>

Recovery is complete, data is not lost, but redo file is reset, it is recommended to back up the database at once.



With all two of these methods, you can complete the recovery.

However, the method of rebuilding the control file in Method 1 may be simpler and can be directly open so that previous backups can be used. It is recommended that this method be used.

Method 2, because in recover, also want one of the designated redo file to try, and finally resetlogs the database, reset the redo file, so that after the restoration is completed, Previously, some backups might not be available. I think it's a little more complicated, but the goal of restoring the database has been achieved.