Oracle 11gR2 RMAN – Recovery from loss of all controlfiles

Oracle 11gR2 RMAN – Recovery from loss of all controlfiles

While I’m preparing for my OCM 11gR2 exam in June, I started to practice on various backup and recovery scenarios. Hopefully, it will also be helpful to anyone looking to refresh their mind on RMAN recovery scenarios.

Verify database controlfiles information.

SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------------------------------
 /u01/app/oracle/oradata/orcl/orcl/control01.ctl NO 16384 594
 /u01/app/oracle/fast_recovery_area/orcl/control02.ctl NO 16384 594
SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- --------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------

Delete all controlfiles and simulate failure

#rm /u01/app/oracle/oradata/orcl/orcl/control01.ctl
#rm /u01/app/oracle/fast_recovery_area/orcl/control02.ctl
SQL> alter tablespace users offline;
alter tablespace users online
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
SQL> startup;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 255855476 bytes
Database Buffers 50331648 bytes
Redo Buffers 6328320 bytes
ORA-00205: error in identifying control file, check alert log for more info

Perform RMAN recovery. Note that I’m using RMAN catalog here with known DBID.

[oracle@localhost tmp]$ echo $ORACLE_SID
orcl
[oracle@localhost tmp]$ rman target / catalog rcuser@rman
connected to target database: ORCL (not mounted)
recovery catalog database Password:
connected to recovery catalog database
RMAN> set DBID 1426604546;
executing command: SET DBID
database name is "ORCL" and DBID is 1426604546
RMAN> run
2> {
3> set controlfile autobackup format
4> for device type disk to '%F';
5> restore controlfile from autobackup;
6> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 02-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISK
recovery area destination: /u01/app/oracle/fast_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_01_02/o1_mf_s_900095659_c8h9xcgo_.bkp found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20160102
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2016_01_02/o1_mf_s_900095659_c8h9xcgo_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/orcl/orcl/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
Finished restore at 02-JAN-16
RMAN> exit

Now, let’s try to bring up the database since my controlfiles are restored 🙂

[oracle@localhost tmp]$ echo $ORACLE_SID
orcl
[oracle@localhost tmp]$ sqlplus / as sysdba
SQL> startup mount;
ORACLE instance started.
Total System Global Area 313860096 bytes
Fixed Size 1344652 bytes
Variable Size 255855476 bytes
Database Buffers 50331648 bytes
Redo Buffers 6328320 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/orcl/system01.dbf'

Note the above error. Apparently, my controlfile is not insync with my datafile1 . Oh dear, let’s try to restore and recover the entire database then.

RMAN> recover database;
Starting recover at 02-JAN-16
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_27_c8h7hltr_.arc
archived log for thread 1 with sequence 28 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_28_c8hlwc1w_.arc
archived log for thread 1 with sequence 29 is already on disk as file /u01/app/oracle/oradata/orcl/orcl/redo02.log
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_27_c8h7hltr_.arc thread=1 sequence=27
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_01_02/o1_mf_1_28_c8hlwc1w_.arc thread=1 sequence=28
archived log file name=/u01/app/oracle/oradata/orcl/orcl/redo02.log thread=1 sequence=29
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-JAN-16
RMAN> exit

Let’s try to bring up the database again! Note that we are opening the database with resetlogs as we are restoring controlfiles from backup.

[oracle@localhost tmp]$ sqlplus / as sysdba
SQL> alter database open resetlogs;
Database altered.

Verify that the controlfile records are the same.

SQL> select * from v$controlfile_record_section;
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- --------
DATABASE 316 1 1 0 0 0
CKPT PROGRESS 8180 11 0 0 0 0
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------
-------------------------------------------------------------

Regards,
Wei Shan

Advertisements

One thought on “Oracle 11gR2 RMAN – Recovery from loss of all controlfiles”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s