Earlier in pre Oracle 11g releases in order to clone or duplicate a database, we had to take backups with RMAN of the source database and copy the backup files to the destination server as well. But with 11g database duplication or cloning has gone one step further. With the new RMAN active database duplication feature taking backups and staging them is no longer required. RMAN can now directly clone the database from the source without the need for backups. The only criteria are that the source database should be in archivelog mode.

In this example I am going to clone the database to the remote server.

Source

IP address:192.168.1.160
Database name: orcl

Destination
IP address:192.168.1.162
Cloned database name: dup


Source Settings.

1)Make sure your database is in archive log mode.
make a couple of log switches to generate archive log
if your database is fresh.
Ex: SQL>alter system switch logfile;
2)Make appropriate listener entries

Sample listener.ora file:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
)

ADR_BASE_LISTENER = /u01/app/oracle


3)Make entries in the tnsnames.ora files for the source and
destination server.These entries are same in both the
source as well as destination servers tnsmanes.ora files.

Sample tnsnames.ora file:

DUP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.162)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dup)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.160)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)


4)start the listener

5)create parameter file for the duplicate database.

SQL>create pfile=’/u01/initdup.ora’ from spfile;

6) copy the file to the destination server.

scp /u01/initdup.ora 192.168.1.162:/u01/app/oracle/product
/11.2.0/db_1/dbs/

Destination Settings:

1)make listener and tnsnames entries here as well.
2)Create the password file for the duplicate instance
make sure the passwords are same for both the databases

orapwd file=/u01/app/oracle/product/11.2.0/db_1/orapwdup
password=sys entries=10
(my password for both the databases is ‘sys’).

3)create appropriate directories.

$ mkdir –p /u01/app/oracle/oradata/dup
$ mkdir –p /u01/app/oracle/flash_recovery_area/dup
$ mkdir –p /u01/app/oracle/admin/dup/adump

4)Edit the parameter file and change every occurrence of the word ‘orcl’
to ‘dup’.

Sample parameter file:

dup.__db_cache_size=46137344
dup.__java_pool_size=4194304
dup.__large_pool_size=4194304
dup.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dup.__pga_aggregate_target=125829120
dup.__sga_target=138412032
dup.__shared_io_pool_size=0
dup.__shared_pool_size=79691776
dup.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/dup/control01.ctl',
'/u01/app/oracle/flash_recovery_area/dup/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='dup'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert=(/u01/app/oracle/oradata/orcl/,
/u01/app/oracle/oradata/dup/)
*.log_file_name_convert=(/u01/app/oracle/oradata/orcl/,
/u01/app/oracle/oradata/dup/)

Notice that at the end there are two parameters namely
db_file_name_convert and log_file_name_convert. These
parameters are necessary if you are duplicating a
database to a different file structure.

Before going further start your listeners in both the databases and check using tnsping.

$ tnsping orcl
$ tnsping dup

On the destination Server.

$ export ORACLE_SID=dup
$ sqlplus / as sysdba

SQL> startup nomount;
Then connect with rman using both the source as well as destination servers.

$ export ORACLE_SID=dup

$ rman target sys/sys@orcl nocatalog auxiliary sys/sys@dup

RMAN> duplicate database to dup
from active database
nofilenamecheck;


After the cloning is finished unset the following values.

alter system reset db_file_name_convert scope=spfile sid='*'

alter system reset log_file_name_convert scope=spfile sid='*'


and restart your instance.