You are facing following error while starting up your database. ORA-01207: file is more recent than control file - old control file.
What you can do in this case ?
If you google this error, you may come across many article recommending recovery from backup controlfile and opening database with resetlogs.
Do we really want to open our database in resetlogs mode by using a backup controlfile- Is there any alternative to recover without resetlogs ? Yes there is one method to do that provided that you have a trace copy of your controlfile.
In order to maintain data consistency, the SCN must be
the same across all datafiles, the controlfile & current REDO file.
By definition when you use "backup controlfile"
it has an out of date SCN in it.RESETLOG forces the SCN back to 1 in order to
obtain consistency across all the files in the database.CREATE CONTROLFILE populates this
new file with the most recent SCN that exists in the datafiles. So it’s
a good idea to opt second option if you have a trace file copy of your
controlfile.
Lets demonstrate this with an example-
[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15
19:51:36 2016
Copyright (c) 1982, 2014, Oracle. All rights
reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
Create a trace file copy of your controlfile.
SQL> alter database backup
controlfile to trace as '/tmp/ttrecctl.sql';
Database altered.
Lets do some activit,to make sure none of the database
operation are affected by this activity.
SQL> create table test1 as select * from dba_tables;
Table created.
SQL> @switch
SQL> alter system switch logfile;
System altered.
SQL>
SQL> select name from v$controlfile;
NAME
--------------------
/data1/control1.ctl
/data2/control2.ctl
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
Lets remove your controlfile-
[oracle@mydb01]:[TTREC1] $ rm -f
/data1/control1.ctl /data2/control2.ctl
[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15
19:53:16 2016
Copyright (c) 1982, 2014, Oracle. All rights
reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
SQL> create table test2 as select * from dba_tables;
Table created.
SQL>
Database is still working as normal even when we don’t
have a controlfile.
SQL> @switch
SQL> alter system switch logfile;
System altered.
SQL>
SQL> /
System altered.
SQL>
I will crash my database-
SQL> shut abort ;
ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size
3712904 bytes
Variable Size
1996490872 bytes
Database Buffers
134217728 bytes
Redo Buffers
13062144 bytes
ORA-00205: error in identifying control file, check alert
log for more info
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
Edit the controlfile trace that we made prior to this
exercise.After edit the file should look like-
[oracle@mydb01]:[TTREC1] $ cat /tmp/ttrecctl.sql
CREATE CONTROLFILE REUSE DATABASE "TTREC" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/data1/TTREC/onlinelog/o1_mf_1_cwzoob3m_.log',
'/data2/TTREC/onlinelog/o1_mf_1_cwzoobdr_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 2 (
'/data1/TTREC/onlinelog/o1_mf_2_cwzoobs8_.log',
'/data2/TTREC/onlinelog/o1_mf_2_cwzooc2y_.log'
) SIZE 100M BLOCKSIZE 512,
GROUP 3 (
'/data1/TTREC/onlinelog/o1_mf_3_cwzoock3_.log',
'/data2/TTREC/onlinelog/o1_mf_3_cwzoocvl_.log'
) SIZE 100M BLOCKSIZE 512
DATAFILE
'/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf',
'/data1/TTREC/datafile/o1_mf_sysaux_cvf5jnh9_.dbf',
'/data1/TTREC/datafile/o1_mf_undotbs1_cvf5npon_.dbf',
'/data1/TTREC/datafile/o1_mf_users_cvf5oss4_.dbf',
'/data1/TTREC/datafile/o1_mf_orinon_t_cvf5lomr_.dbf',
'/data1/TTREC/datafile/o1_mf_oraion_t_cvf5jnjb_.dbf',
'/data1/TTREC/datafile/o1_mf_tbs_test_cvf5ostw_.dbf',
'/data1/TTREC/datafile/o1_mf_orion_da_cvf5npp4_.dbf',
'/data1/TTREC/datafile/o1_mf_testa_cxf1h6kw_.dbf',
'/data1/TTREC/datafile/o1_mf_testb_cxnhzbbf_.dbf'
CHARACTER SET AL32UTF8
;
[oracle@mydb01]:[TTREC1] $ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 15
19:56:58 2016
Copyright (c) 1982, 2014, Oracle. All rights
reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
- 64bit Production With the Partitioning, Real Application Clusters, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @/tmp/ttrecctl.sql
Control file created.
SQL> select name ,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
TTREC MOUNTED
SQL> recover database ;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(1) from test1;
COUNT(1)
----------
2508
SQL> select count(1) from test2;
COUNT(1)
----------
2509
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition
Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Advanced
Analytics and Real Application Testing options.
Simply we opened our database without using open
resetlogs.
