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 (prod1) on a test server linux01 .

The following assumptions are made in this note:

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
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.

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

Restore the SPFILE

[oracle@linux01 prod1]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 24 13:23:58 2010

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

connected to target database (not started)

RMAN> set dbid=4266928631

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/dbs/initprod1.ora'

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

Total System Global Area 159383552 bytes

Fixed Size 2039056 bytes
Variable Size 67109616 bytes
Database Buffers 83886080 bytes
Redo Buffers 6348800 bytes



RMAN> run
2> {SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/prod1/%F';
3> restore spfile from autobackup;
4> }


executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 24-JUN-10
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20100624
channel ORA_DISK_1: looking for autobackup on day: 20100623
channel ORA_DISK_1: looking for autobackup on day: 20100622
channel ORA_DISK_1: autobackup found: /u02/backup/prod1/c-4266928631-20100622-00
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 24-JUN-10


RMAN> shutdown immediate;

database closed
database dismounted
Oracle instance shut down


Restore the Control File

In this case, the instance is now being started with the restored spfile. We can create a pfile as well from this spfile is so required.


RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area 3154116608 bytes

Fixed Size 2043904 bytes
Variable Size 1879052288 bytes
Database Buffers 1258291200 bytes
Redo Buffers 14729216 bytes


RMAN> set dbid=4266928631

executing command: SET DBID

RMAN> run
2> {SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backup/prod1/%F';
3> restore controlfile from autobackup;
4> }


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

Starting restore at 24-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK

recovery area destination: /u02/flash_recovery_area
database name (or database unique name) used for search: PROD1
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20100624
channel ORA_DISK_1: looking for autobackup on day: 20100623
channel ORA_DISK_1: looking for autobackup on day: 20100622
channel ORA_DISK_1: autobackup found: /u02/backup/prod1/c-4266928631-20100622-00
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u03/oradata/prod1/control1.ctl
output filename=/u04/oradata/prod1/control2.ctl
output filename=/u05/oradata/prod1/control3.ctl
Finished restore at 24-JUN-10


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1



Restore the database




RMAN> restore database;

Starting restore at 24-JUN-10
Starting implicit crosscheck backup at 24-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=540 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=538 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=537 devtype=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: sid=536 devtype=DISK
allocated channel: ORA_DISK_6
channel ORA_DISK_6: sid=535 devtype=DISK
Crosschecked 44 objects
Finished implicit crosscheck backup at 24-JUN-10

Starting implicit crosscheck copy at 24-JUN-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6
Finished implicit crosscheck copy at 24-JUN-10

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to /u03/oradata/prod1/system02.dbf
restoring datafile 00009 to /u03/oradata/prod1/glassfishjms_data01.dbf
restoring datafile 00010 to /u03/oradata/prod1/mciw_data01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/prod1/hqlgt5hp_1_1
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u03/oradata/prod1/jira.dbf
restoring datafile 00005 to /u03/oradata/prod1/users01.dbf
restoring datafile 00018 to /u03/oradata/prod1/mcollage_lob01.dbf
channel ORA_DISK_2: reading from backup piece /u02/backup/prod1/holgt5ho_1_1
channel ORA_DISK_3: starting datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u03/oradata/prod1/sysaux01.dbf

...
...

channel ORA_DISK_2: restored backup piece 1
piece handle=/u02/backup/prod1/hplgt5ho_1_1 tag=TAG20100622T200404
channel ORA_DISK_2: restore complete, elapsed time: 00:27:35
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/prod1/holgt5ho_1_1 tag=TAG20100622T200404
channel ORA_DISK_1: restore complete, elapsed time: 00:49:12
Finished restore at 24-JUN-10


Recover the database

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

In this case the last archived log file which has been backed up is sequence 613.

This information can be obtained by issuing a LIST BACKUP OF ARCHIVELOG ALL command.

So RMAN will fail when it tries to apply sequence 614 .

RMAN> recover database

Starting recover at 24-JUN-10
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
using channel ORA_DISK_6

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=613
channel ORA_DISK_1: reading from backup piece /u02/backup/prod1/hulgt6bo_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/prod1/hulgt6bo_1_1 tag=TAG20100622T201759
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u02/flash_recovery_area/PROD1/archivelog/2010_06_24/o1_mf_1_613_6260ob01_.arc thread=1 sequence=613
channel default: deleting archive log(s)
archive log filename=/u02/flash_recovery_area/PROD1/archivelog/2010_06_24/o1_mf_1_613_6260ob01_.arc recid=1224 stamp=722530858
unable to find archive log
archive log thread=1 sequence=614
RMAN-00571: ================================================== =========
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ================================================== =========
RMAN-03002: failure of recover command at 06/24/2010 15:01:03
RMAN-06054: media recovery requesting unknown log: thread 1 seq 614 lowscn 39895194


Open the database with RESETLOGS


[oracle@linux01 PROD1]$ rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Jun 24 15:04:48 2010

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

connected to target database: PROD1 (DBID=4266928631, not open)

RMAN> sql 'alter database open resetlogs';

using target database control file instead of recovery catalog
sql statement: alter database open resetlogs