How to restore from a loss of all current control files to the default location

Tweet

The following scenario simulate a loss of all the control files and the restore process using a backup control file with any Recovery Catalog.

In this situation you are only able to open your database in NOMOUNT mode. Also remember that when you lose all (or one) control files and restore them (or one of them) from a backup control file, you have to perform a recovery of your database and open it with the RESETLOGS option, even if any datafile is restored (like in this scenario).

That's not always true when you're dealing with "created" control file (I hope to simulate that scenario one day), as long as you must specify RESETLOGS if the online logs are lost or NORESETLOGS if the online logs are available.

Anyway a control file restored from a backup has an SCN taken at that "remote" time, different compared with those currently available in the datafiles and redo logs and so they have to be resynchronized.
Generally speaking, having the instance in NOMOUNT mode means your control files are still not read (if available), so RMAN is not able to know how to find information about an "unidentified" database: DBID indeed is contained into the control file.
If you are using a flash recovery area or a recovery catalog (best practice's solution) then you don't have to set the DBID before executing the RESTORE command of your NOMOUNTED instance, saving time and avoiding extra manual steps always prone to error.

Let's start. My instance is running

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   11655     1  0 08:13 ?        00:00:00 ora_smon_orcl
oracle   11811  2820  0 08:20 pts/1    00:00:00 grep smon

Suddenly all my control file are lost.

[oracle@localhost orcl]$ rm /home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl /home/oracle/app/oracle/oradata/orcl/control01.ctl

When trying to create a tablespace some errors are thrown:

SQL> create tablespace t1 datafile
'/home/oracle/app/oracle/oradata/orcl/t101.dbf' size 1M;
create tablespace t1 datafile '/home/oracle/app/oracle/oradata/orcl/t101.dbf'
size 1M
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/home/oracle/app/oracle/oradata/orcl/control01.ctl'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

The instance is crashed

[oracle@localhost orcl]$ ps -ef|grep smon
oracle   11655     1  0 08:13 ?        00:00:00 ora_smon_orcl

As you can verify the mentioned (/home/oracle/app/oracle/oradata/orcl/control01.ctl) file doesn't exist.

[oracle@localhost orcl]$ ll
total 2502160
-rw-rw---- 1 oracle oracle    7348224 Jul 21 08:14 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle   85991424 Jul 21 08:14 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 21 08:11 non_default_location
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:23 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:14 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:14 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:23 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 21 08:20 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 21 07:14 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 21 08:19 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 21 08:14 users01.dbf

Let's try to restore the missing control files, starting the instance in NOMOUNT mode:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  456146944 bytes
Fixed Size                  1344840 bytes
Variable Size             360712888 bytes
Database Buffers           88080384 bytes
Redo Buffers                6008832 bytes

Connect using RMAN and issue the RESTORE CONTROLFILE FROM AUTOBACKUP command. DBIS is not set, but because I'm using the flash recovery area, RMAN is able to find a backup control file.

[oracle@localhost orcl]$ rman target /

RMAN> restore controlfile from autobackup;

Starting restore at 21-07-2012 08:36:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /home/oracle/app/oracle/flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/home/oracle/app/oracle/oradata/orcl/control01.ctl
output file
name=/home/oracle/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 21-07-2012 08:36:25

Let's see if the instance is able to read our restored control files, bringing the database in MOUNT state:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

What does it happen if I try to simply open the database ? It fails with a clear error.

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 07/21/2012 08:42:50
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

As said at the beginning of this post when you restore a control file from a backup you have first to recover the database...

RMAN> recover database;

Starting recover at 21-07-2012 08:43:26
Starting implicit crosscheck backup at 21-07-2012 08:43:26
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 21-07-2012 08:43:28

Starting implicit crosscheck copy at 21-07-2012 08:43:28
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 21-07-2012 08:43:28

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_21/o1_mf_s_789203952_80ogm1c3_.bkp
File Name:
/home/oracle/app/oracle/flash_recovery_area/ORCL/autobackup/2012_07_17/o1_mf_s_788864449_80c39jlo_.bkp

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc
archived log for thread 1 with sequence 3 is already on disk as file
/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc
archived log for thread 1 with sequence 4 is already on disk as file
/home/oracle/app/oracle/oradata/orcl/redo01.log
archived log file
name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_2_80oj4ppv_.arc
thread=1 sequence=2
archived log file
name=/home/oracle/app/oracle/flash_recovery_area/ORCL/archivelog/2012_07_21/o1_mf_1_3_80ojktc5_.arc
thread=1 sequence=3
archived log file name=/home/oracle/app/oracle/oradata/orcl/redo01.log
thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 21-07-2012 08:43:31

... and then open it with the RESETLOGS option.

RMAN> alter database open resetlogs;

database opened

The database is now open and control files are available again.

[oracle@localhost orcl]$ ll
total 2511696
-rw-rw---- 1 oracle oracle    7348224 Jul 21 08:44 APEX_1930613455248703.dbf
-rw-rw---- 1 oracle oracle    9748480 Jul 21 08:45 control01.ctl
-rw-rw---- 1 oracle oracle   85991424 Jul 21 08:44 example01.dbf
drwxrwxr-x 2 oracle oracle       4096 Jul 21 08:11 non_default_location
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:45 redo01.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:44 redo02.log
-rw-rw---- 1 oracle oracle   52429312 Jul 21 08:44 redo03.log
-rw-rw---- 1 oracle oracle 1158684672 Jul 21 08:44 sysaux01.dbf
-rw-rw---- 1 oracle oracle  871374848 Jul 21 08:44 system01.dbf
-rw-rw---- 1 oracle oracle   20979712 Jul 21 07:14 temp01.dbf
-rw-rw---- 1 oracle oracle   41951232 Jul 21 08:44 undotbs01.dbf
-rw-rw---- 1 oracle oracle  235937792 Jul 21 08:44 users01.dbf

That's all