RESTORE AND RECOVERY PROCEDURE OF PRODUCTION DATABASE BACKUP ON TEST SERVERS

RESTORE AND RECOVERY PROCEDURE OF PRODUCTION DATABASE BACKUP ON TEST SERVERS

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

RMAN>