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 utilizing 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/ora_%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/ORA_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/ORA_%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/ORA/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/ora/control01.ctl
output filename=/u01/app/oracle/oradata/ora/control02.ctl
output filename=/u01/app/oracle/oradata/ora/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/ORA/archivelog/2010_08_28/o1_mf_1_26_67jc8po5_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA/archivelog/2010_08_28/o1_mf_1_24_67jc5rko_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA/archivelog/2010_08_28/o1_mf_1_27_67jc90og_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA/archivelog/2010_08_28/o1_mf_1_25_67jc6knx_.arc
File Name: /u01/app/oracle/flash_recovery_area/ORA/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 option.
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
--------- ----------
ABC 3148849783
SQL>
