Oracle RMAN Restore to the Same Machine as the Original Database

Among the most critical but often most neglected database administration tasks is testing restore from backup. But sometimes, you don’t have a test system handy, and need to test the restore on the same host as the source database. In such situations, the biggest fear is overwriting the original database. Here is a simple procedure you can follow, which will not overwrite the source.

1. Add an entry to the oratab for the new instance, and source the new environment:
   

   oracle$ cat >> /etc/oratab <<EOF
   > foo:/u02/app/oracle/product/11.2.0/dbhome_1:N
   > EOF
   
   oracle$ . oraenv
   ORACLE_SID[oracle]? foo
   The Oracle base remains unchanged with value /u02/app/oracle

2. Create a pfile and spfile with a minimum set of parameters for the new instance. In this case the source database is named ‘orcl’ and the new database will have a DB unique name of ‘foo’. This example will write all files to the +data ASM diskgroup, under directories for ‘foo’. You could use a filesystem directory as the destination as well. Just make sure you have enough space wherever you plan to write:
   

   oracle$ cat > $ORACLE_HOME/dbs/initfoo.ora <<EOF
   > db_name=orcl
   > db_unique_name=foo
   > db_create_file_dest=+data
   > EOF
   
   oracle$ sqlplus / as sysdba
   SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 9 15:35:00 2014
   Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   Connected to an idle instance.
   
   SQL> create spfile from pfile;
   File created.
   
   SQL> exit
   Disconnected

3. Now, using the backup pieces from your most recent backup, try restoring the controlfile only. Start with the most recently written backup piece, since RMAN writes the controlfile at the end of the backup. It may fail once or twice, but keep trying backup pieces until you find the controlfile:
   

   oracle$ ls -lt /mnt/bkup
   total 13041104
   -rwxrwxrwx 1 root root      44544 Apr  4 09:32 0lp4sghk_1_1
   -rwxrwxrwx 1 root root   10059776 Apr  4 09:32 0kp4sghi_1_1
   -rwxrwxrwx 1 root root 2857394176 Apr  4 09:32 0jp4sgfr_1_1
   -rwxrwxrwx 1 root root 3785719808 Apr  4 09:31 0ip4sgch_1_1
   -rwxrwxrwx 1 root root 6697222144 Apr  4 09:29 0hp4sg98_1_1
   -rwxrwxrwx 1 root root    3647488 Apr  4 09:28 0gp4sg97_1_1
   
   $ rman target /
   Recovery Manager: Release 11.2.0.3.0 - Production on Wed Apr 9 15:37:10 2014
   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   connected to target database (not started)
   
   RMAN> startup nomount;
   Oracle instance started
   Total System Global Area     238034944 bytes
   Fixed Size                     2227136 bytes
   Variable Size                180356160 bytes
   Database Buffers              50331648 bytes
   Redo Buffers                   5120000 bytes
   
   RMAN> restore controlfile from '/mnt/bkup/0lp4sghk_1_1';
   Starting restore at 09-APR-14
   using target database control file instead of recovery catalog
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: SID=1 device type=DISK
   channel ORA_DISK_1: restoring control file
   RMAN-00571: ===========================================================
   RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
   RMAN-00571: ===========================================================
   RMAN-03002: failure of restore command at 04/09/2014 15:42:10
   ORA-19870: error while restoring backup piece /mnt/bkup/0lp4sghk_1_1
   ORA-19626: backup set type is archived log - can not be processed by this conversation
   
   RMAN> restore controlfile from '/mnt/bkup/0kp4sghi_1_1';
   Starting restore at 09-APR-14
   using target database control file instead of recovery catalog
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: SID=19 device type=DISK
   channel ORA_DISK_1: restoring control file
   channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
   output file name=+DATA/foo/controlfile/current.348.844443549
   Finished restore at 09-APR-14

   As you can see above, RMAN will report the path and name of the controlfile that it restores. Use that path and name below:

   RMAN> sql "alter system set
   2>  control_files=''+DATA/foo/controlfile/current.348.844443549''
   3>  scope=spfile";
   
   sql statement: alter system set 
   control_files=''+DATA/foo/controlfile/current.348.844443549'' 
   scope=spfile

4. Mount the database with the newly restored controlfile, and perform a restore to the new location. The ‘set newname’ command changes the location that RMAN will write the files to the db_create_file_dest of the new instance. The ‘switch database’ command updates the controlfile to reflect the new file locations. When the restore is complete, use media recovery to apply the archived redologs.
   

   RMAN> startup force mount
   Oracle instance started
   database mounted
   Total System Global Area     238034944 bytes
   Fixed Size                     2227136 bytes
   Variable Size                180356160 bytes
   Database Buffers              50331648 bytes
   Redo Buffers                   5120000 bytes
   
   RMAN> run {
   2> set newname for database to new;
   3> restore database;
   4> }
   
   executing command: SET NEWNAME
   Starting restore at 09-APR-14
   allocated channel: ORA_DISK_1
   channel ORA_DISK_1: SID=23 device type=DISK
   channel ORA_DISK_1: starting datafile backup set restore
   channel ORA_DISK_1: specifying datafile(s) to restore from backup set
   channel ORA_DISK_1: restoring datafile 00002 to +data
   channel ORA_DISK_1: reading from backup piece /mnt/bkup/0hp4sg98_1_1
   channel ORA_DISK_1: piece handle=/mnt/bkup/0hp4sg98_1_1 tag=TAG20140404T092808
   channel ORA_DISK_1: restored backup piece 1
   channel ORA_DISK_1: restore complete, elapsed time: 00:01:35
   channel ORA_DISK_1: starting datafile backup set restore
   channel ORA_DISK_1: specifying datafile(s) to restore from backup set
   channel ORA_DISK_1: restoring datafile 00001 to +data
   channel ORA_DISK_1: restoring datafile 00004 to +data
   channel ORA_DISK_1: restoring datafile 00005 to +data
   channel ORA_DISK_1: reading from backup piece /mnt/bkup/0ip4sgch_1_1
   channel ORA_DISK_1: piece handle=/mnt/bkup/0ip4sgch_1_1 tag=TAG20140404T092808
   channel ORA_DISK_1: restored backup piece 1
   channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
   channel ORA_DISK_1: starting datafile backup set restore
   channel ORA_DISK_1: specifying datafile(s) to restore from backup set
   channel ORA_DISK_1: restoring datafile 00003 to +data
   channel ORA_DISK_1: reading from backup piece /mnt/bkup/0jp4sgfr_1_1
   channel ORA_DISK_1: piece handle=/mnt/bkup/0jp4sgfr_1_1 tag=TAG20140404T092808
   channel ORA_DISK_1: restored backup piece 1
   channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
   Finished restore at 09-APR-14
   
   RMAN> switch database to copy;
   
   datafile 1 switched to datafile copy "+DATA/foo/datafile/system.338.844531637"
   datafile 2 switched to datafile copy "+DATA/foo/datafile/sysaux.352.844531541"
   datafile 3 switched to datafile copy "+DATA/foo/datafile/undotbs1.347.844531691"
   datafile 4 switched to datafile copy "+DATA/foo/datafile/users.350.844531637"
   datafile 5 switched to datafile copy "+DATA/foo/datafile/soe.329.844531637"
   
   RMAN> recover database;
   
   Starting recover at 09-APR-14
   using channel ORA_DISK_1
   starting media recovery
   archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_25_841917031.dbf thread=1 sequence=25
   archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_26_841917031.dbf thread=1 sequence=26
   archived log file name=/u02/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_27_841917031.dbf thread=1 sequence=27
   media recovery complete, elapsed time: 00:00:01
   Finished recover at 09-APR-14
   
   RMAN> exit
   
   Recovery Manager complete.

5. Before opening the database, we need to re-create the controlfile so that we don’t step on any files belonging to the source database. The first step is to generate a “create controlfile” script, and to locate the trace file where it was written:
   

   $ sqlplus / as sysdba
   SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 16 10:56:28 2014
   Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
   SQL> alter database backup controlfile to trace;
   Database altered.
   
   SQL> select tracefile
     2  from v$session s,
     3       v$process p
     4  where s.paddr = p.addr
     5  and s.audsid = sys_context('USERENV', 'SESSIONID');
   TRACEFILE
   ----------------------------------------------------------
   /u02/app/oracle/diag/rdbms/foo/foo/trace/foo_ora_19168.trc
   
   SQL> exit
   Disconnected from Oracle Database 11g Enterprise Edition

6. Next, we need to edit the controlfile creation script so that all we have left is the “create controlfile … resetlogs” statement, and so that all file paths to the original database are removed or changed to reference the db_unique_name of the test database.Below is a pipeline of clumsy awks I created that creates a script called create_foo_controlfile.sql. It should take care of most permutations of these trace controlfile scripts.
   

   $ sed -n '/CREATE.* RESETLOGS/,$p' /u02/app/oracle/diag/rdbms/foo/foo/trace/foo_ora_18387.trc | \
   > sed '/.*;/q' | \
   > sed 's/\(GROUP...\).*\( SIZE\)/\1\2/' | \
   > sed 's/orcl/foo/g' | \
   > sed 's/($//' | \
   > sed 's/[\)] SIZE/SIZE/' | \
   > grep -v "^    '" > create_foo_controlfile.sql

   If it doesn’t work for you, just edit the script from your trace file, so that you end up with something like this:

   CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
       MAXLOGFILES 16
       MAXLOGMEMBERS 3
       MAXDATAFILES 100
       MAXINSTANCES 8
       MAXLOGHISTORY 292
   LOGFILE
     GROUP 1 
     SIZE 50M BLOCKSIZE 512,
     GROUP 2 
     SIZE 50M BLOCKSIZE 512,
     GROUP 3 
     SIZE 50M BLOCKSIZE 512
   -- STANDBY LOGFILE
   DATAFILE
     '+DATA/foo/datafile/system.338.845027673',
     '+DATA/foo/datafile/sysaux.347.845027547',
     '+DATA/foo/datafile/undotbs1.352.845027747',
     '+DATA/foo/datafile/users.329.845027673',
     '+DATA/foo/datafile/soe.350.845027673'
   CHARACTER SET WE8MSWIN1252
   ;

7. The next step is to use the above script to open the database with the resetlogs option on a new OMF controlfile:
   

   $ sqlplus / as sysdba
   SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 16 10:56:28 2014
   Copyright (c) 1982, 2011, Oracle.  All rights reserved.
   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
   
   SQL> alter system reset control_files scope=spfile;
   System altered.
   
   SQL> startup force nomount
   ORACLE instance started.
   
   Total System Global Area  238034944 bytes
   Fixed Size                  2227136 bytes
   Variable Size             180356160 bytes
   Database Buffers           50331648 bytes
   Redo Buffers                5120000 bytes
   
   SQL> @create_foo_controlfile
   Control file created.
   
   SQL> select value from v$parameter where name = 'control_files';
   VALUE
   -------------------------------------------
   +DATA/foo/controlfile/current.265.845031651
   
   SQL> alter database open resetlogs;
   Database altered.

8. Last but not least, don’t forget to provide a tempfile or two to the temporary tablespaces:
   

   SQL> alter tablespace temp
     2  add tempfile size 5G;
   Tablespace altered.