Oracle RMAN Restore to the Same Machine as the Original Database

6 min read

Apr 11, 2014

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.

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

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

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

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.

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

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
 ;

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.

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

← RMAN Infatuation?

Why is Affinity Mask Negative in sp_configure? →

Get Email Notifications

No Comments Yet

Let us know what you think

Oracle RMAN Restore to the Same Machine as the Original Database

Share this

You May Also Like

ORA-01555: snapshot too old, When Running Flashback Query

Total Recall by DUDE: Extracting Oracle Data After RAID Corruption

Be Careful When Revoking UNLIMITED TABLESPACE

Get Email Notifications

No Comments Yet