Monday, September 26, 2011

Duplicate database using RMAN

You can easily create your Development or QA databases from Production using DUPLICATE TARGET DATABASE command.

Here are the simple steps to duplicate the database.

1. Copy the source database init.ora file for auxiliary instance and replace the SID with Auxiliary Database SID (for ex, DUP)

Prodsrv$ cp $ORACLE_HOME/dbs/initPRD.ora cp $ORACLE_HOME/dbs/initDUP.ora
Replace SID=PRD with DUP in initDUP.ora

2. Set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT to convert the datafile and redo log file names in Auxilary database init.ora file

3. Copy the auxilary database init.ora parameter from source server (for ex, Prodsrv) to Target Server (for ex, Devsrv)
Prodsrv$ scp $ORACLE_HOME/dbs/initDUP.ora oracle@Devsrv:/$ORACLE_HOME/dbs/initDUP.ora

4. Add Auxilary database TNS entry in $ORACLE_HOME/network/admin/tnsnames.ora file on source server.
(ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1521))

5. Set ORACLE_SID and create password file for DUP database on target host
Devsrv$export ORACLE_SID=DUP
Devsrv$orapwd file=orapwDUP password=xxxxx

6. Create a static listener for auxiliary database and reload, because auxiliary database will not register itself with the listener.


7. Startup the Auxilary database instance in nomount state on target host
SYS@DUP> startup nomount

8. Run the duplicate the target database from source
Prodsrv$ rman TARGET SYS/xxxxx@PRD CATALOG rman/xxxx@rmancat AUXILIARY SYS/xxxx@DUP

9. After database is duplicated successfully, RMAN will open the database. Please check the RMAN log and Alert.log file for issues,if any.

From 11g, you can able to duplicate the database without connecting to target database. Make sure that you have backups available on duplicate site.

Here is the example for duplicate database
connect auxiliary sys/xxxx@dup
connect catalog rman/xxx@rmancat
duplicate database 'PRD' to DUP' until time "to_date('05/01/2011 14:00:00','mm/dd/yy hh24:mi:ss')"
db_file_name_convert =( “/dbs1/oradata/PRD","/dbs2/oradata/DUP")
backup location '/rman_backup' ;

Duplicate Database options
-NOREDO – Using this option no archivelogs will be applied
-UNDO_TABLESPACE – You must specify the UNDO tablesapce when you are not connected to target database.

Satishbabu Gunukula