Disaster recovery using RMAN Demo

As part of disaster recovery exercise or to test the validity of a RMAN backup,  a full restore and recovery of databases can be performed on scratch or test servers utilising the production RMAN backups which have been restored from the tape backups on these test or scratch servers.

This note will illustrate the above procedure by detailing the steps required to restore the backup of a production database 

1.  same server with same location

2.  Different server with same location

The following assumptions are made in this note:

1.The RMAN backups have been restored from tape backups to the same backup location on the test server as the production server where the backup was originally taken
2.The identical directory structure as is present on production has been created on the test server. This will apply to not only the location of the database files (data, control files, redo log files), but also to the bdump,cdump, udump and adump locations. 

3.Controlfile autobackup has been enabled. This is important.

#Overview

Restore the spfile from the autobackup
Restore the controlfile from the autobackup
Restore the data files
Recover by applying archived redo log files
Open the database with resetlogs

1.Restore the SPFILE

[oracle@rac1 backup]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Aug 28 03:12:17 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> set dbid=3148849783 
executing command: SET DBID

RMAN> startup force nomount; 
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/10.2.0/db_1/dbs/initjay.ora'

starting Oracle instance without parameter file for retrival of spfile
Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     1218268 bytes
Variable Size                 54528292 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

RMAN> run 
2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> RESTORE SPFILE FROM AUTOBACKUP; 

4> } 
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

Starting restore at 28-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20100828
channel ORA_DISK_1: autobackup found: /u01/app/oracle/backup/JAY_c-3148849783-20100828-03
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-AUG-10

RMAN>
RMAN> shutdown immediate

Oracle instance shut down

2.Restore the Control File

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     444596224 bytes

Fixed Size                     1219904 bytes
Variable Size                130024128 bytes
Database Buffers             310378496 bytes
Redo Buffers                   2973696 bytes

RMAN> set dbid=3148849783 
executing command: SET DBID

RMAN> run
 2> { set controlfile autobackup format for device type disk to '/u01/app/oracle/backup/JAY_%F';
3> RESTORE controlfile FROM AUTOBACKUP; 

4> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 28-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: JAY
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u01/app/oracle/flash_recovery_area/JAY/autobackup/2010_08_28/o1_mf_s_728189319_67jbxhyh_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u01/app/oracle/oradata/jay/control01.ctl
output filename=/u01/app/oracle/oradata/jay/control02.ctl
output filename=/u01/app/oracle/oradata/jay/control03.ctl
Finished restore at 28-AUG-10


3.Restore the database


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 28-AUG-10
Starting implicit crosscheck backup at 28-AUG-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 28-AUG-10

Starting implicit crosscheck copy at 28-AUG-10
using channel ORA_DISK_1
Finished implicit crosscheck copy at 28-AUG-10

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/JAY/autobackup/2010_08_28/o1_mf_s_728189319_67jbxhyh_.bkp
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc
File Name: /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_23_67jc4rpk_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/jay/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/jay/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/jay/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/jay/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/JAY_0mlmei9b_1_1.bckp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/backup/JAY_0mlmei9b_1_1.bckp tag=TAG20100828T024706channel ORA_DISK_1: restore complete, elapsed time: 00:01:47
Finished restore at 28-AUG-10

4.Recover the database

RMAN> recover database;
Starting recover at 28-AUG-10
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 22 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_22_67jbxb7n_.arc
archive log thread 1 sequence 23 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_23_67jc4rpk_.arc
archive log thread 1 sequence 24 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc
archive log thread 1 sequence 25 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc
archive log thread 1 sequence 26 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc
archive log thread 1 sequence 27 is already on disk as file /u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc
archive log thread 1 sequence 28 is already on disk as file /u01/app/oracle/oradata/jay/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_22_67jbxb7n_.arc thread=1 sequence=22
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_23_67jc4rpk_.arc thread=1 sequence=23
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc thread=1 sequence=24
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc thread=1 sequence=25
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc thread=1 sequence=26
archive log filename=/u01/app/oracle/flash_recovery_area/JAY/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc thread=1 sequence=27
archive log filename=/u01/app/oracle/oradata/jay/redo03.log thread=1 sequence=28media recovery complete, elapsed time: 00:00:06
Finished recover at 28-AUG-10 

 The recovery will fail at a point where it cannot restore any more archived redo log
files.

I had full backup so no error else it gives error , no problem just exit from rman relogin 

and open the database with resetlog.

RMAN> sql 'alter database open resetlogs';
sql statement: alter database open resetlogs

RMAN> exit


Recovery Manager complete.

5.Verify the database
[oracle@rac1 backup]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 28 03:22:35 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning and OLAP options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
JAY       3148849783

SQL>