Database cloning using RMAN DUPLICATE

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

Let us look the steps involved in cloning a database using the RMAN duplicate command.

Step 1: Backup target database using RMAN

RMAN> run

2> {

3> allocate channel c1 device type disk format '/backups/clnbkp/_%F';

4> backup database plus archivelog;

5> }

Step 2: Configure listener and naming services

For eg: Edit the following in the listener.ora and tnsnames.ora files under $ORACLE_HOME/network/admin directory.

Listener:

LISTENER =

(DESCRIPTION_LIST =

  (DESCRIPTION =

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST=abc123.region.domain.com)(PORT = 1521))

      )

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

      )

  )

)

SID_LIST_LISTENER =

(SID_DESC =

     (GLOBAL_DBNAME = prod.world)

     (ORACLE_HOME = /oracle/app/product/10.2.0)

     (SID_NAME = prod)

)

)

Naming service:

clone_prod =

(DESCRIPTION =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = def123.region.domain.com)(PORT = 1521))

  )

  (CONNECT_DATA =

    (SERVICE_NAME = clonedb.world)

  )

)

Step 3: Check Listener and Naming services configured correctly

$ tnsping clone_prod

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 20-APR-2011 02:48:08

Copyright (c) 1997,  2007, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = def123.region.domain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = edisipd.world)))

OK (60 msec)

$ lsnrctl reload

$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-APR-2011 02:50:07

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= abc123.region.domain.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                15-APR-2011 11:57:28

Uptime                    4 days 14 hr. 52 min. 38 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/product/10.2.0/network/admin/listener.ora

Listener Log File         /oracle/app/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= abc123.region.domain.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Services Summary...

Service "prod.world" has 1 instance(s).

  Instance "prod", status UNKNOWN, has 1 handler(s) for this service...

 

Step 4: Create pfile for clone database from primary database and edit the created pfile

SQL> create pfile=’/oracle/home/pfile/initclonedb.ora’ from spfile;

Edit the create pfile for database name for bdump, udump and cdump directories and all the directory structures as for your clone database

If your directory structures of the databases on the servers is different, then add the following parameters in the pfile created for clone database.

log_file_name_convert = ‘/primary/db/directory/structure/’,’/clone/db/directory/structure/’

db_file_name_convert = ‘/primary/db/directory/structure/’,’/clone/db/directory/structure/’

Step 5: Transfer backup files and created pfile to clone database server.

Transfer the created RMAN database backup file to the clone database server to the same temporary location as of primary server.

Also transfer the created and edited pfile to the $ORACLE_HOME/dbs directory in the clone database server.

Note:  You can use ftp, sftp, scp file transfer utilities

Step 6: Configure new instance and create password file in clone database server

C:\> oradim –new –sid clonedb                        => required only on windows based servers.

$ orapwd file=’$ORACLE_HOME/dbs/pwdclonedb.ora’ password = sys

Note: Password should be same as of primary database

Step 7: Startup clone database in nomount stage

Set the database environment and start the database to nomount and exit using below example

$ . oraenv

ORACLE_SID = [oracle] ? clonedb

$ sqlplus / as sysdba

SQL > startup nomount

SQL> exit

Step 8: Connect RMAN and duplicate the database

$ rman target / auxiliary sys/sys@clone_prod

Note: I have connected RMAN to both primary database and clone database instances from primary database server.

RMAN> duplicate target database to ‘clonedb’;

The duplicate command will clone your primary database and you are done.

Note: In windows based servers, log files will not be created and RMAN seems to hang for a long time. So break the operation and create the logfiles manually using the following

In clone database

SQL> alter database clear logfile group 1;

SQL> alter database clear logfile group 2;

Step 9: Open the cloned database with reset logs option

SQL> alter database open resetlogs;

Cloned database is ready for operation.