Oracle RMAN is a powerful tool with many features for recovering datafiles, tablespaces or even single blocks, as well as cloning databases for non production uses. However restoring a database to an entirely different server (or set of servers) it was backed up from is a somewhat cumbersome process. In this post we will restore an RMAN backup to a new host, keeping the same database name and datafile names. This
example uses Oracle 11gR2 on OEL Linux 6.3. The backup device is a Dell EMC Data Domain mounted as an NFS share. RMAN allow us to see what databases are held in the catalog by using the list db_unique_name all command, Connect to the RMAN catalog and issue the command: In this example
we are going to restore the NYC11 database to a new host. The database DBID is 334041916 and the unique name of the primary instance is NYC11. Create an INIT.ORA on a the recovery host for a new database instance with the DB_NAME and DB_UNIQUE_NAME set to match the database we are restoring.
There are several scenarios in which you might want to restore a database to a different host. One scenario might be if the source server is completely lost.
Start up the recovery instance to a NOMOUNT state:
SQL> startup nomount pfile=initnyc11res.ora ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 671089544 bytes Database Buffers 390070272 bytes Redo Buffers 5517312 bytesConnect to the RMAN tool and the RMAN catalog and set the DBID to match the database we want to recover:
rman target / catalog rman/rman@rcat RMAN> set dbid 334041916; executing command: SET DBID database name is "NYC11" and DBID is 334041916With the DBID set, and the DB_NAME and the DB_UNIQUE_NAME of the recovery set to match the database we want to recover, we can now query RMAN for the backups that are available.
In the following example we are looking for backups of the database completed within the last 6 hours:
RMAN> list backup of database completed after 'sysdate-(6/24)'; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 398375 71.63M DISK 00:00:09 18-MAR-17 BP Key: 398382 Status: AVAILABLE Compressed: NO Tag: L0_20170318_1435 Piece Name: /nfs_mount/dd0205_rman/nyc11/arch_NYC11_9481_1.rmn List of Archived Logs in backup set 398375 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 7670 33185858 18-MAR-17 33188267 18-MAR-17 1 7671 33188267 18-MAR-17 33191033 18-MAR-17 1 7672 33191033 18-MAR-17 33193413 18-MAR-17 1 7673 33193413 18-MAR-17 33197977 18-MAR-17 1 7674 33197977 18-MAR-17 33200412 18-MAR-17 1 7675 33200412 18-MAR-17 33202829 18-MAR-17 1 7676 33202829 18-MAR-17 33205289 18-MAR-17 1 7677 33205289 18-MAR-17 33209045 18-MAR-17 1 7678 33209045 18-MAR-17 33211458 18-MAR-17 1 7679 33211458 18-MAR-17 33213891 18-MAR-17 1 7680 33213891 18-MAR-17 33216287 18-MAR-17 1 7681 33216287 18-MAR-17 33219901 18-MAR-17 output removed to aid clarity BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 400892 16.00K DISK 00:00:00 18-MAR-17 BP Key: 400896 Status: AVAILABLE Compressed: NO Tag: AR_20170318_2020 Piece Name: /nfs_mount/dd0205_rman/nyc11/arch_NYC11_9511_1.rmn List of Archived Logs in backup set 400892 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 7693 33238855 18-MAR-17 33238873 18-MAR-17The last archivelog sequence backed up with RMAN was sequence 7693 or SCN 33238855. We will use this in the restore clause.
First, we will restore the SPFILE. The following is the RMAN run block tht specifies the SCN we are using as a recovery target.
run { set until scn 33238855; restore spfile; }Once the SPFILE has been restored, shutdown the recovery instance and then restart it to a NOMOUNT state. Check that the instance is now using the SPFILE.
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /dbhome_1/dbs/spfilelax11.oraNext we will restore the database controlfiles.
The RMAN run block will look as follows:
run { set until scn 33238855; restore controlfile; }While this block executes on the recovery host expect to see several “failover to previous backup” messages. This is expected as RMAN is looking for automated backups made to the FRA, which are not present on the recovery host.
Executing the code against RMAN should produce output similar to this:
RMAN> run { 2> set until scn 33238855; 3> restore controlfile; 4> } output removed to aid clarity failover to previous backup channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: restoring control file channel ORA_DISK_1: reading from backup piece /nfs_mount/dd0205_rman/nyc11/NYC11_controlfile_8urvfe8u_1_1.ctl channel ORA_DISK_1: piece handle=/nfs_mount/dd0205_rman/nyc11/NYC11_controlfile_8urvfe8u_1_1.ctl tag=TAG20170318T201422 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+DATA/nyc11/controlfile/current.259.938992993 output file name=+FRA/nyc11/controlfile/current.317.938992993 Finished restore at 18-MAR-17When the controlfile restore completes we can mount the recovery database:
RMAN> alter database mount; database mounted released channel: ORA_DISK_1 released channel: ORA_DISK_2 released channel: ORA_DISK_3 released channel: ORA_DISK_4Now connect to the recovery database and check that the database role is correct and that it is using the correct control files:
SQL> select name, open_mode, database_role from v$database; NAME OPEN_MODE DATABASE_ROLE --------- -------------------- ---------------- NYC11 MOUNTED PRIMARY SQL> show parameter control_files NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/nyc11/controlfile/curren t.259.938995595, +FRA/nyc11/co ntrolfile/current.317.93899559 5The next step will recover the database datafiles. We will use the following RMAN run block:
run { set until scn 33238855; restore database; }Leave the recovery database mounted, and launch RMAN again, but this time DO NOT CONNECT TO THE CATALOG.
rman target / RMAN> run { set until scn 33238855; restore database; } executing command: SET until clause Starting restore at 19-MAR-17 Starting implicit crosscheck backup at 19-MAR-17 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=63 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=132 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=192 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=5 device type=DISK Crosschecked 71 objects Crosschecked 125 objects Finished implicit crosscheck backup at 19-MAR-17 Starting implicit crosscheck copy at 19-MAR-17 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 Crosschecked 5 objects Finished implicit crosscheck copy at 19-MAR-17 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 output removed to aid clarity handle=/nfs_mount/dd0205_rman/nyc11/data_NYC11_9486_1.rmn tag=L0_20170318_2000 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:01:23 channel ORA_DISK_3: piece handle=/nfs_mount/dd0205_rman/nyc11/data_NYC11_9485_1.rmn tag=L0_20170318_2000 channel ORA_DISK_3: restored backup piece 1 channel ORA_DISK_3: restore complete, elapsed time: 00:06:33 Finished restore at 19-MAR-17With the database files restored, we can now recover them. This step can be combined into the same run block as the restore operation, or run as a separate step.
The RMAN run block to recover the database is as follows:
run { set until scn 33238855; recover database; }Running this against the RMAN recovery session:
executing command: SET until clause Starting recover at 19-MAR-17 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery channel ORA_DISK_1: starting archived log restore to default destination channel ORA_DISK_1: restoring archived log archived log thread=1 sequence=7688 channel ORA_DISK_1: reading from backup piece /nfs_mount/dd0205_rman/nyc11/arch_NYC11_9500_1.rmn output removed to aid clarity name=+FRA/nyc11/archivelog/2017_03_19/thread_1_seq_7689.304.938996283 thread=1 sequence=7689 channel default: deleting archived log(s) archived log file name=+FRA/nyc11/archivelog/2017_03_19/thread_1_seq_7689.304.938996283 RECID=17032 STAMP=938996283 unable to find archived log archived log thread=1 sequence=7690 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 03/19/2017 00:18:05 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7690 and starting SCN of 33237931The recovery operation is now complete. The final step is to open the database.
Connect to the recovery database with SQL*Plus, and open the database with the resetlogs option. The resetlogs options is necessary as this is a different host from the original, and there are no online redo logs to use.
[oracle@rstemc64vm31 ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 19 00:18:40 2017 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> recover database using backup controlfile until cancel; ORA-00279: change 33237931 generated at 03/18/2017 20:14:19 needed for thread 1 ORA-00289: suggestion : +FRA ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'NYC11' ORA-00280: change 33237931 for thread 1 is in sequence #7690 Specify log: {=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered.The database is now recovered and on a new host.