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.

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

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:

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:

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.

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:

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.

Great post Jeremiah! Thanks for the excellent, vetted example. Many DBAs would probably rather risk an untested restore than risk potentially damaging their customers live Production system by accidently stepping on a live db file.

Hi Jimmy how’s it going? The reason you probably got an error is because the example is for a source DB named ORCL and a test DB named FOO. What is the DB_NAME of your source (probably UW01, right – sounds kinda familiar), and what values did you use in the pfile you created in step 2? -Jeremiah

I reworked the end of this procedure to include a ‘create controlfile’ step. Previously, I was using ‘alter database rename …’ to change the controlfile entries for the redologs and block change tracking file. It turns out that if the source database used OMF, the ‘alter database rename …’ statement can physically delete the original file. If we completely re-create the controlfile, then we remove the risk of OMF deleting the source DB files.

Doing a controlfile re-create seems a little old-school to me. My future plans for this procedure include tracing an RMAN duplicate to see if I can use dbms_backup_restore procedures and functions to more neatly accomplish some of the manual steps such as controlfile re-creation.

Nice article,
I think this command in 3 is not necessary since you started nomount from spfile:
RMAN> sql "alter system set
2> control_files=''+DATA/foo/controlfile/current.348.844443549''
3> scope=spfile";

Seems like your archivelog is not part of your backup backupset.
Try to find out where this archivelog is located by running this:
rman target /
list archivelog from logseq=1422 until logseq=1422;

If you do not find it, try searching in your backups.
rman target /
list backup of archivelog from logseq=1422 until logseq=1422;

If this is part of the backup, restore it:
rman target /
restore archivelog from logseq=1422 until logseq=1422;

With the archive in place:
rman target /
recover database until sequence 1422;

It may asks for aditional archivelogs if required.

Sometimes, it requests for an archivelog that wasn’t archive yet. If you check on the v$log, you’ll see that the sequence it requests was not archived yet (Usually when you run a backup without switching archivelog first).

In this case, run a
alter system switch logfile;

Wait for it to archive that redolog then you have it.

If this is a clone in a new server, then you may not be able to switch logile. In that case, query the v$logfile to findout the location of the needed logfile then

rman target /
recover database until cancel;

when it requests for the location of the archivelog that contains sequence, inform the location of your logfile.