Cloning/Duplicating Database with RMAN

Cloning database ASM to ASM

Clone an Oracle database using RMAN duplicate (same server)

Duplicating Database using dynamic SQL

This tutorial is for cloning database on the same server.

1. Create Password File for Auxiliary Database

orapwd file=c:\oracle\ora92\database\PWDAUX.ora password=oracle

2.Create an Initialization Parameter for the Auxiliary Database

Copy the initialization parameter for the target database and make the necessary changes for the duplicated database.

SQL> create pfile=’c:\oracle\product\10.2.0\db\database\initAUX.ora’ from spfile;

After creating the initialization parameter for the duplicate database, and change at least the following parameters:

db_file_name_convert = ('C:\ORACLE\ORADATA\ORCL', 'C:\ORACLE\ORADATA\AUX')
log_file_name_convert = ('C:\ORACLE\ORADATA\ORCL', 'C:\ORACLE\ORADATA\AUX')
control_files = 'C:\oracle\oradata\AUX\control01.ctl'
              , 'C:\oracle\oradata\AUX\control02.ctl'
              , 'C:\oracle\oradata\AUX\control03.ctl'
db_name = 'AUX'
instance_name = 'AUX'
background_dump_dest = 'C:\oracle\admin\AUX\bdump'
core_dump_dest = 'C:\oracle\admin\AUX\cdump'
user_dump_dest = 'C:\oracle\admin\AUX\udump'
service_names = 'AUX.IDEVELOPMENT.INFO'
log_archive_dest_1 = 'location=C:\oracle\oradata\AUX\archive MANDATORY'

3. Create / Start the Auxiliary Instance

Create new windows service for duplicate database AUX using oradim

oradim -new -sid AUX -intpwd oracle -startmode auto -pfile ‘C:\oracle\product\10.2.0\db\dbs\initaux.ora’

3. Ensure Oracle Net Connectivity to Auxiliary Database

Modify both the listener.ora and tnsnames.ora file to be able to connect to the auxiliary database. After making changes to the networking files, test the connection keeping in mind that you must be able to connect to the auxiliary instance with SYSDBA privileges, so a valid password file must exist.

C:\> lsnrctl stop LISTENER

C:\> lsnrctl start LISTENER

C:\> sqlplus sys/oracle@AUX as sysdba

It would connect as idle instance

SQL> startup nomount pfile=’C:\oracle\product\10.2.0\db\database\initAUX.ora’;
ORA-02778: Name given for the log directory is invalid

Check the location of your bdump file(other dump directories as well). If situation persists then change the bdump destination and other log file destinations.

SQL> startup nomount pfile=’C:\oracle\product\10.2.0\db\database\initAUX.ora’;

3. Mount or Open the Source Database

As mentioned in the pre-requisites section of this article, the target database should be either opened or mounted.

C:\> set ORACLE_SID=orcl
C:\> sqlplus "/ as sysdba"
SQL> startup open

4. Ensure You Have the Necessary Backups and Archived Redo Log Files

As mentioned in the pre-requisites section of this article, ensure that you have a current backup that you wish to use to create the duplicate database. Login to query the RMAN catalog:

C:\> rman target sys/passwd@ORCL

RMAN> list backup summary;

5.Login to Target and Auxiliary Database using RMAN

C:\> rman target sys/passwd@ORCL  auxiliary sys/password@AUX

6.Run the RMAN DUPLICATE DATABASE Command

The following RUN block can be used to fully duplicate the target database from the latest full backup.
Note that you can duplicate the database to a specific date/time using the UNTIL TIME '<DATE>' clause. For example, to duplicate the new database to yesterdays date/time, use the following:

duplicate target database to AUX until time 'SYSDATE-1';.

run {
# Allocate the channel for the duplicate work
allocate auxiliary channel ch1 type disk;

# Duplicate the database to AUX
set until sequence 156;

duplicate target database to AUX;

}