Tuesday, December 28, 2010

Restore RMAN backup to different server - Database Clone from RMAN Hot backup

How to restore RMAN backup to different server and Recover the database

The situation here is we have taken an RMAN backup on PROD (Source) and we need to restore those backup pieces on different server as TEST (target) database.

Its actually cloning the database from PROD(SOURCE) to TEST(target) using RMAN backup piecies taken on source.

Please follow the following steps outlined below.

Please note source (PROD) is where the backup piecies are available, we need to clone and create a database with another name TEST (target) on different server.

In target Database (TEST) perform the below

Modify the db_name in the $ORACLE_HOME/dbs/init.ora file to reflect the source database name

Start database in nomount mode and restore controlfile (this controlfile should be the RMAN backup controlfile after the RMAN backup on source)

Copy the control file backup piece from source to target and restore the controlfile.

rman target /

RMAN>startup nomount;

RMAN>restore controlfile from '/u01/backup/ccf_c-1250176543-20081031-01';

--Mount the database

RMAN>alter database mount;

Copy all the backup piecies of source(PROD) database to target(TEST) server.

Copy all the backup piecies of source(PROD) database to target(TEST) server using scp.

Once copy completes,Run the following script to list the backup pieces available:

Register all the copied backup piecies in the controlfile. (/u01/backup is the location of copied backup piecies in the target)

RMAN target /

RMAN>catalog backuppiece '/u01/backup/LEVEL0_1-10-2010_dxxthtcn_1_1';

--Run the above command for all the backup piecies copied from the source (PROD) to target (TEST)

List the archive logs. Note the last sequence number

The last sequence number should be used in the set until clause in the rman restore script.
It is referred to $SEQ

RMAN>list backup of archivelog all;

In a separate sqlplus session, rename the log files

sqlplus ‘/ as sysdba’

alter database rename file '/prod/oradata/log01.dbf' to '/test/oradata/log01.dbf';

alter database rename file '/prod/oradata/log02.dbf' to '/test/oradata/log02.dbf';

Run the following SQL to create the SQL to create the temp files for the temporary tablespace:

sqlplus ‘/ as sysdba’

select 'alter database rename file '''name''' to '''replace(name,'','')''' ;' from v$tempfile;

Run the generated SQL

alter database rename file '/prod/oradata/temp01.dbf' to '/test/oradata/temp01.dbf' ;

alter database rename file '/prod/oradata/temp02.dbf' to '/test/oradata/temp02.dbf' ;

Use the following SQL to generate the SQL to create the set new name commands to go into the rman command file for the restore

Sql> select 'set newname for datafile ' file# ' to ''' replace(name,'','') ''';' from v$datafile order by file# ;

Restore the database & Recover it:

Run the following rman restore command, pasting in the result of the above SQL where indicated.

Replace the SEQ+1 value with the sequence number + 1 obtained in step Note the last sequence number.

RMAN target /
run { set until sequence ;

-- Paste in set newname commands from previous SQL here restore database;

switch datafile all;

recover database;


Open the database and configure the database

Once Successful recovery of the database Recreate the controlfile:

Sql> alter database backup control file to trace;

Modify the trace file generated in the udump as required (file locations..etc) and SET the DB Name to TARGET SID.

Bring down the database

Modify the db_name parameter in the init.ora to target


Recreate the controlfile:

Open the database:

Sql> alter database open resetlogs;


  1. hi sir you have given nice information
    well said anonymous
    Really good Site for Orace Apps dba’s.

  2. Hi,
    Thnax for this article. But i m looking fr the same restore, but using a hot backup.
    Can u help me on this plz


  3. Hi, I want to restore my database (in ASM, and RAC db) to a separate filesystem on the server. I don't want to recover it, I just need complete backup copy of it. I have backups taken through RMAN and need a specific date's backup to restore. How can I achieve it? I need syntax for it for doing a PITR restore on a separate filesystem as this is needed to be sent to Dev team.(Dev team is in diff geography)

  4. This comment has been removed by the author.