Skip to content

Insight and analysis of technology and business strategy

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.

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner