This document will detail recovery scenarios dealing with missing or corrupted control files using RMAN disk based backups. See the post Setting up RMAN for backup and restore using Database Control for information on setting up RMAN.
Scenario 1: Corrupt or Missing control file
When attempting to start the database you see the following error.
SQL> startup ORACLE instance started. Total System Global Area 849530880 bytes Fixed Size 1339824 bytes Variable Size 528485968 bytes Database Buffers 314572800 bytes Redo Buffers 5132288 bytes ORA-00205: error in identifying control file, check alert log for more info SQL>
Looking the alert log you see that the /u02/app/oracle/oradata/orcl/ctl/control01.ctl is corrupt.
ALTER DATABASE MOUNT ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/app/oracle/oradata/orcl/ctl/control01.ctl' ORA-27048: skgfifi: file header information is invalid ORA-205 signalled during: ALTER DATABASE MOUNT... Mon Apr 26 10:11:23 2010 Checker run found 1 new persistent data failures ORA-205 signalled during: ALTER DATABASE MOUNT...
Or you may see the file is missing when looking in the alert log.
ALTER DATABASE MOUNT ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/app/oracle/oradata/orcl/ctl/control01.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 Mon Apr 26 10:28:28 2010 Checker run found 1 new persistent data failures ORA-205 signalled during: ALTER DATABASE MOUNT...
In either case, to recover from this problem is simple and does not require going to the last backup provided your control files are multiplexed. See the post Multiplexing Control Files for more information on multiplexing the control file. To resolve this problem we simply copy a control file from one of the other locations.
Identify the control files:
SQL> startup
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 528485968 bytes
Database Buffers 314572800 bytes
Redo Buffers 5132288 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u02/app/oracle/oradata/orcl/c
tl/control01.ctl, /u01/app/ora
cle/flash_recovery_area/orcl/c
ontrol02.ctl, /u03/app/oracle/
oradata/orcl/ctl/control03.ctl
SQL>
We know from the alert log that /u02/app/oracle/oradata/orcl/ctl/control01.ctl is the control file that is having problems so we will copy the one from the /u01/app/oracle/flash_recovery_area/orcl/control02.ctl and then mount and open the database.
SQL> host [oracle@ora1 ~]$ cp /u01/app/oracle/flash_recovery_area/orcl/control02.ctl /u02/app/oracle/oradata/orcl/ctl/control01.ctl [oracle@ora1 ~]$ exit exit SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL>
By having a multiplexed copy of the control file we were able to recover from a corrupt or missing control file without having to go to a database backup.
Scenario 2: All control files are corrupt or missing
When starting the database we see the following error.
SQL> startup ORACLE instance started. Total System Global Area 849530880 bytes Fixed Size 1339824 bytes Variable Size 528485968 bytes Database Buffers 314572800 bytes Redo Buffers 5132288 bytes ORA-00205: error in identifying control file, check alert log for more info SQL>
In anticipation of restoring a control we get a list of the control file locations.
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u02/app/oracle/oradata/orcl/c
tl/control01.ctl, /u01/app/ora
cle/flash_recovery_area/orcl/c
ontrol02.ctl, /u03/app/oracle/
oradata/orcl/ctl/control03.ctl
SQL>
Looking at the alert log we see that none of the control files are available.
ALTER DATABASE MOUNT ORA-00210: cannot open the specified control file ORA-00202: control file: '/u03/app/oracle/oradata/orcl/ctl/control03.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00210: cannot open the specified control file ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-00210: cannot open the specified control file ORA-00202: control file: '/u02/app/oracle/oradata/orcl/ctl/control01.ctl' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 ORA-205 signalled during: ALTER DATABASE MOUNT... Mon Apr 26 11:07:53 2010 Checker run found 2 new persistent data failures
Since all of the control files are missing we will need to restore them from a backup using RMAN.
[oracle@ora1 ctl]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 26 11:24:32 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database: ORCL (not mounted) RMAN>
Currently none of the control files are mounted so RMAN does not know about the backups or any pre-configured RMAN settings. All settings are at their default.
RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default RMAN>
In order to use the backups we will need to tell RMAN the Database ID. If you do not have or know the database you have two options available.
Option 1: Shutdown the database and re-start it in RMAN
SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> host [oracle@ora1 ctl]$ rman Recovery Manager: Release 11.2.0.1.0 - Production on Mon Apr 26 11:30:49 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. RMAN> connect target / connected to target database (not started) RMAN> startup Oracle instance started RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 04/26/2010 11:31:00 ORA-00205: error in identifying control file, check alert log for more info
You will again receive an error stating that the control files could not be found. That is OK because in the next step we are going to restore them. Because the RMAN configuration is back to the default we are going to have to pass the entire path along with the file name of the latest control file autobackup to the restore control file command.
RMAN> restore controlfile from '/u03/app/oracle/oradata/orcl/backup/c-1239150297-20100426-00'; Starting restore at 26-APR-10 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u02/app/oracle/oradata/orcl/ctl/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl output file name=/u03/app/oracle/oradata/orcl/ctl/control03.ctl Finished restore at 26-APR-10 RMAN>
After restoring the control files we can see mount the database.
RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN>
Now that the database has been mounted we can see that our custom RMAN settings are back.
RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/oradata/orcl/backup/%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u03/app/oracle/oradata/orcl/backup/%U' MAXPIECESIZE 2 G; CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE'; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default RMAN>
Now it would appear we are ready to open the database.
RMAN> alter database open; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 04/26/2010 11:38:40 ORA-01589: must use RESETLOGS or NORESETLOGS option for database open RMAN>
We just restored the control files. In order to open the database we have to use the RESETLOGS option.
RMAN> alter database open resetlogs; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of alter db command at 04/26/2010 11:39:08 ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/u02/app/oracle/oradata/orcl/system01.dbf' RMAN>
The restored control files are older than the data files. We need to restore the database.
RMAN> restore database; Starting restore at 26-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/orcl/test.dbf channel ORA_DISK_1: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1olc324t_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00004 to /u02/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_2: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1plc326b_1_1 channel ORA_DISK_2: piece handle=/u03/app/oracle/oradata/orcl/backup/1plc326b_1_1 tag=TAG20100426T092133 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:15 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to /u02/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_2: restoring datafile 00002 to /u02/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_2: restoring datafile 00007 to /u03/app/oracle/oradata/orcl/dbfs01.dbf channel ORA_DISK_2: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1nlc324t_1_1 channel ORA_DISK_1: piece handle=/u03/app/oracle/oradata/orcl/backup/1olc324t_1_1 tag=TAG20100426T092133 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:34 channel ORA_DISK_2: piece handle=/u03/app/oracle/oradata/orcl/backup/1nlc324t_1_1 tag=TAG20100426T092133 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:02:09 Finished restore at 26-APR-10 RMAN>
Next we recover the database.
RMAN> recover database; Starting recover at 26-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery archived log for thread 1 with sequence 13 is already on disk as file /u03/app/oracle/oradata/orcl/redo/red01_b.log archived log for thread 1 with sequence 14 is already on disk as file /u02/app/oracle/oradata/orcl/redo/redo02.log archived log file name=/u03/app/oracle/oradata/orcl/redo/red01_b.log thread=1 sequence=13 archived log file name=/u02/app/oracle/oradata/orcl/redo/redo02.log thread=1 sequence=14 media recovery complete, elapsed time: 00:00:02 Finished recover at 26-APR-10 RMAN>
Now that the database has been restored and recovered we can finally open the database, again with the RESETLOGS option.
RMAN> alter database open resetlogs; database opened RMAN>
Option 2: Set the DBID
Oracle embeds the DBID in the name of the control file autobackup. In this case our control file autobackups were configured as shown below.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/oradata/orcl/backup/%F';
Our control file autobackup is /u03/app/oracle/oradata/orcl/backup/c-1239150297-20100426-00. The first number after the c-, 1239150297, is the DBID. We will use that number in the SET DBID command.
RMAN> set dbid 1239150297 executing command: SET DBID RMAN>
With the DBID set, we can now recover the control files from the control file autobackup. Again we have to pass the entire path along with the control file auto backup name.
RMAN> restore controlfile from '/u03/app/oracle/oradata/orcl/backup/c-1239150297-20100426-00'; Starting restore at 26-APR-10 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u02/app/oracle/oradata/orcl/ctl/control01.ctl output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl output file name=/u03/app/oracle/oradata/orcl/ctl/control03.ctl Finished restore at 26-APR-10 RMAN>
Next we mount the database and restore the database.
RMAN> alter database mount; database mounted released channel: ORA_DISK_1 RMAN> restore database; Starting restore at 26-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00003 to /u02/app/oracle/oradata/orcl/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u02/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: restoring datafile 00006 to /u02/app/oracle/oradata/orcl/test.dbf channel ORA_DISK_1: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1olc324t_1_1 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00004 to /u02/app/oracle/oradata/orcl/users01.dbf channel ORA_DISK_2: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1plc326b_1_1 channel ORA_DISK_2: piece handle=/u03/app/oracle/oradata/orcl/backup/1plc326b_1_1 tag=TAG20100426T092133 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:00:15 channel ORA_DISK_2: starting datafile backup set restore channel ORA_DISK_2: specifying datafile(s) to restore from backup set channel ORA_DISK_2: restoring datafile 00001 to /u02/app/oracle/oradata/orcl/system01.dbf channel ORA_DISK_2: restoring datafile 00002 to /u02/app/oracle/oradata/orcl/sysaux01.dbf channel ORA_DISK_2: restoring datafile 00007 to /u03/app/oracle/oradata/orcl/dbfs01.dbf channel ORA_DISK_2: reading from backup piece /u03/app/oracle/oradata/orcl/backup/1nlc324t_1_1 channel ORA_DISK_1: piece handle=/u03/app/oracle/oradata/orcl/backup/1olc324t_1_1 tag=TAG20100426T092133 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:34 channel ORA_DISK_2: piece handle=/u03/app/oracle/oradata/orcl/backup/1nlc324t_1_1 tag=TAG20100426T092133 channel ORA_DISK_2: restored backup piece 1 channel ORA_DISK_2: restore complete, elapsed time: 00:02:09 Finished restore at 26-APR-10 RMAN>
Next we recover the database.
RMAN> recover database; Starting recover at 26-APR-10 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery archived log for thread 1 with sequence 13 is already on disk as file /u03/app/oracle/oradata/orcl/redo/red01_b.log archived log for thread 1 with sequence 14 is already on disk as file /u02/app/oracle/oradata/orcl/redo/redo02.log archived log file name=/u03/app/oracle/oradata/orcl/redo/red01_b.log thread=1 sequence=13 archived log file name=/u02/app/oracle/oradata/orcl/redo/redo02.log thread=1 sequence=14 media recovery complete, elapsed time: 00:00:02 Finished recover at 26-APR-10
Finally open the database with the RESETLOGS option.
RMAN> alter database open resetlogs; database opened RMAN>
I think after seeing what is required to restore the control files you will agree that multiplexing the control files is a good thing to do.
really nice
It is appreciated
What if… there is no auto-backup control files??
Much Appreciated..great work…
Excellent…..very well done….thank you!!!!
Good Luck, cheers!!!!!
thank you for sharing the knowledge Eric. RMAN is okay, how to handle the situation with SQL itself with normal backup (online or offline), I mean without RMAN.
You can say, go man check it in Oracle Documentation, yes you are right, but I would like to know your view and suggestions in handling the situation. I’m a SAP BASIS guy and haven’t yet got any chance to get into deeper areas of DB. Just interest in DBs. 🙂
thanks again
Vamsi
hi
THIS IS WHAT I NEED ,THANK YOU FRIEND KEEP POST ALL NEW CONCEPTS ,MAIL ME (prakashjidba@gmail.com)
THANKS,
BY PRAKASH..!
This is really a awesome document.Thanks
But the same doubt what if rman backup is not there.
Thanks in advance.
Thanks for sharing a wonderful way to recover corrupt or missing BKF file, please take new experience with Kernel for BKF file recover tool to repair and recover corruption, damage and deleted BKF file from your PC.
shukriya ,
Hello Eric,
Some has has deleted all the control files in my test db, and there is no backup of those files.
Please help me how to restore it.
Notify me of new posts by email.
Thnx a lot. explain in very well
hi
if we dont have the backup, and we still wants to recover that.
is there any way to do that.
yes you could create a control file by using the import initialization parameters from the alert log file
very useful!!
Until I found this I thgohut I’d have to spend the day inside.
Thank you alot! Best ever explanation!!
when you use old control file to restore/recover database,
does it cause any data lost?
Assume you work in a company as a senior DBA and on your absence your buck up has corrupted all the control files while working with the alter dba backup control files command . what do you do
Assume you work in a company as a senior DBA and on your absence your buck up has corrupted all the control files while working with the alter dba backup control files command . what do you do
Hi,
What would restore database and recover database command do. Do they restore all the data files and what if I have a TBs of database…?
sharing Increases!
thank you, you saved my job! 🙂