What you
will do when you lost all of your controlfile and want to restore from a cold backup that you have taken prior to the
controlfile corruption.In this post I will explain about how to restore the
last cold backed up controlfile and recover database using that backup
controlfile. Lets simulate this issue -In this case you can expect following
error message while starting up the database -
ORA-01207: file is more recent than control file - old control file
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Mon Sep 12 16:04:32 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>
select name from v$controlfile ;
NAME
-------------------------------------
/data1/control1.ctl
/data2/control2.ctl
Lets do
some activity - to make sure none of our transactions are affected by this.
SQL>
create table x1 as select * from dba_users;
Table
created.
SQL>
create table x2 as select * from dba_users;
Table
created.
SQL>
@switch
SQL>
set echo on
SQL>
alter system switch logfile;
System
altered.
SQL>
SQL>
/
System
altered.
SQL>
shut immediate ;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
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
[oracle@mydb01]:[TTREC1]
$
Take the
cold backup of your database - Here I am just taking the backup of controlfile
for our testing purpose.
[oracle@mydb01]:[TTREC1]
$ cp /data1/control1.ctl /t4_nfs/TTREC/control1.ctl
[oracle@mydb01]:[TTREC1]
$ cp /data2/control2.ctl /t4_nfs/TTREC/control2.ctl
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Mon Sep 12 16:07:06 2016
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Connected
to an idle instance.
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
Database
mounted.
Database
opened.
Do some
activity
SQL>
create table x3 as select * from all_objects;
Table
created.
SQL>
@switch
SQL>
alter system switch logfile;
System
altered.
SQL>
SQL>
@redo
SQL>
set echo on
SQL>
set lines 222
SQL>
col status for a10
SQL>
col member format a45
SQL>
select member, a.group#, a.status, b.status, a.archived from v$log a,
v$logfile b where a.group# = b.group# order by a.group#, member;
MEMBER
GROUP#
STATUS STATUS ARC
---------------------------------------------
---------- ---------- ---------- ---
/data1/TTREC/onlinelog/o1_mf_1_cwzoob3m_.log
1
INACTIVE
YES
/data2/TTREC/onlinelog/o1_mf_1_cwzoobdr_.log
1
INACTIVE
YES
/data1/TTREC/onlinelog/o1_mf_2_cwzoobs8_.log
2
ACTIVE
YES
/data2/TTREC/onlinelog/o1_mf_2_cwzooc2y_.log
2
ACTIVE
YES
/data1/TTREC/onlinelog/o1_mf_3_cwzoock3_.log
3
CURRENT
NO
/data2/TTREC/onlinelog/o1_mf_3_cwzoocvl_.log
3
CURRENT
NO
6 rows
selected.
SQL>
shut immediate ;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
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
Here I
am deleting all copies of my 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 Mon Sep 12 16:11:58 2016
Copyright
(c) 1982, 2014, Oracle. All rights reserved.
Connected
to an idle instance.
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
Lets
copy our controlfile back to original location -
[oracle@mydb01]:[TTREC1]
$ cp /t4_nfs/TTREC/control1.ctl /data1/control1.ctl
[oracle@mydb01]:[TTREC1]
$ cp /t4_nfs/TTREC/control2.ctl /data2/control2.ctl
[oracle@mydb01]:[TTREC1]
$ sqlplus / as sysdba
SQL*Plus:
Release 12.1.0.2.0 Production on Mon Sep 12 16:13:02 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>
startup;
ORA-01081:
cannot start already-running ORACLE - shut it down first
SQL>
shut abort ;
ORACLE
instance shut down.
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
Database
mounted.
ORA-01122:
database file 1 failed verification check
ORA-01110:
data file 1: '/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf'
ORA-01207: file is more recent than control file - old control file
SQL>
recover database;
ORA-00283:
recovery session canceled due to errors
ORA-01122:
database file 1 failed verification check
ORA-01110:
data file 1: '/data1/TTREC/datafile/o1_mf_system_cvf5lom7_.dbf'
ORA-01207:
file is more recent than control file - old control file
When
using SQLPLUS to recover the database by using a backup controlfile you need to
add "USING BACKUP CONTROLFILE" along with recover database command.
Note:-
Under
RMAN "USING BACKUP CONTROLFILE" syntax is not needed since RMAN automatically
determines a BACKUP CONTROLFILE.
SQL> recover database using backup controlfile until
cancel
ORA-00279:
change 133116019 generated at 09/12/2016 16:05:04 needed for thread 1
ORA-00289:
suggestion :
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxdkkqx4_.arc
ORA-00280:
change 133116019 for thread 1 is in sequence #5
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxdkkqx4_.arc
ORA-00279:
change 133116865 generated at 09/12/2016 16:07:51 needed for thread 1
ORA-00289:
suggestion : /data2/TTREC/archivelog/2016_09_12/o1_mf_1_6_%u_.arc
ORA-00280:
change 133116865 for thread 1 is in sequence #6
ORA-00278:
log file
'/data2/TTREC/archivelog/2016_09_12/o1_mf_1_5_cxdkkqx4_.arc'
no longer needed for this recovery
Specify
log: {<RET>=suggested | filename | AUTO | CANCEL}
/data1/TTREC/onlinelog/o1_mf_3_cwzoock3_.log
Log
applied.
Media
recovery complete.
Here I
just provided suggested archive log file and my current redo log file.
SQL>
alter database open;
alter
database open
*
ERROR at
line 1:
ORA-01589:
must use RESETLOGS or NORESETLOGS option for database open
SQL>
alter database open resetlogs;
Database
altered.
SQL>
Note:-
You must
run the RECOVER command after restoring a backup control file, even if no
datafiles have been restored.
You must
open the database with the RESETLOGS option after performing either complete or
point-in-time recovery with a backup control file.
SQL>
select count(1) from x1;
COUNT(1)
----------
248
SQL>
select count(1) from x3;
COUNT(1)
----------
248
SQL>
All data
are intact.
Here we
successfully recovered our corrupted/damaged controlfile from cold
backup.
