Restore oracle database from rman backup to another server

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.

 
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.

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:

RMAN> list db_unique_name all;

List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
63367   NYC11    334041916        PRIMARY          NYC11               
9936    MYCLONE  527884335        PRIMARY          XIO11WSB_CLONE      
36652   XIO11WSB 2386171380       PRIMARY          XIO11WSB            
36652   XIO11WSB 2386171380       STANDBY          XIO11WSB_XIO11WSB   
36652   XIO11WSB 2386171380       STANDBY          XIO11WSB_CLONE

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.

*.db_name='NYC11'
*.db_unique_name=NYC11

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 bytes

Connect 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 334041916

With 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-17

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

Next 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-17

When 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_4

Now 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
                                                 5

The 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-17

With 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 33237931

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