Duplicate An Oracle Database With RMAN

It’s easy to duplicate a database with RMAN. Since Oracle 10g it will even create the duplicate database for you — you only have to create the instance.

Setup auxiliary instance

  1. Create admin directory under $ORCLE_BASE/admin and password file
  2. Register duplicate database in TNS and listener, $TNS_ADMIN/tnsnames.ora and $TNS_ADMIN/listener.ora
  3. Create a the parameter file init.ora to start the instance, don’t forget to set DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT

I assume OFA (Optimal Flexible Architecture) directory naming here:

  • ORACLE_BASE = /u01/app/oracle
  • ORACLE_HOME = $ORACLE_BASE/product/10.2.0
  • TNS_ADMIN = $ORACLE_HOME/network/admin

and usage of OMF (Oracle Managed Files) and the FRA (flash recovery area):

SQL> show parameter file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /u02/app/oracle/oradata
db_recovery_file_dest                string      /u02/app/oracle/fra
db_recovery_file_dest_size           big integer 2G

Step 1

mkdir /u01/app/oracle/admin/DUPDB

Step 2

Edit $TNS_ADMIN/tnsnames.ora and add the duplicate database:

ORIGDB.bensmann.com =
  (DESCRIPTION =
    (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = localhost)
          (PORT = 1521)
    )
    (CONNECT_DATA = (SID = ORIGDB)
    )
  )

DUPDB.bensmann.com =
  (DESCRIPTION =
    (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = localhost)
          (PORT = 1521)
    )
    (CONNECT_DATA = (SID = DUPDB)
    )
  )

Add an entry to the Oracle listener, for example:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORIGDB.bensmann.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ORIGDB)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = DUPDB.bensmann.com)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = DUPDB)
    )
  )

and restart the listener:

lsnrctl stop
lsnrctl start

Step 3

Create the parameter file /u01/app/oracle/product/10.2.0/dbs/initDUPDB.ora. Take care and look for DUPDB string in the values and change them to reflect your naming.

This is for 10g:

db_name='DUPDB'
instance_name='DUPDB'
sga_target=150m
control_files=('controlDUPDB01.ctl', 'controlDUPDB02.ctl')
db_block_size=8192
db_create_file_dest='/u02/app/oracle/oradata'
db_create_online_log_dest_1='/u02/app/oracle/oradata'
db_create_online_log_dest_2='/u02/app/oracle/fra'
db_recovery_file_dest='/oracle/app/oradata'
db_recovery_file_dest_size=2G
background_dump_dest='/u01/app/oracle/admin/DUPDB/ddump'
core_dump_dest='/u01/app/oracle/admin/DUPDB/cdump'
user_dump_dest='/u01/app/oracle/admin/DUPDB/udump'
processes=50
undo_management='AUTO'
undo_tablespace='UNDOTBS'
remote_login_passwordfile=EXCLUSIVE

and set the parameters DB|LOG_FILE_NAME_CONVERT to reflect changes in names for logfiles datafiles. This is neccessary as the control files restored from backup contain paths to the original locations — and you want to access new files and don’t want to access or even overwrite the original ones. It’s very important if the duplicate database is on the same system as the original database ;-)

The values in *_FILE_NAME_CONVERT consist of a simple list containing a pair of mappings: OLD_PATH, NEW_PATH[, OLD_PATH, NEW_PATH ...] .

db_file_name_convert=('/u02/app/oracle/oradata/ORIGDB', '/u02/app/oracle/oradata/DUPDB',
                      '/u02/app/oracle/fra/ORIGDB', '/u02/app/oracle/fra/DUPDB')
log_file_name_convert=('/u02/app/oracle/oradata/ORIGDB', '/u02/app/oracle/oradata/DUPDB',
                       '/u02/app/oracle/fra/ORIGDB', '/u02/app/oracle/fra/DUPDB')

For 11g replace sga_target = 150m with memory_target = 180m and all three *_dump_dest parameters with diagnostic_dest = '/u01/app/oracle/admin/DUPDB'.

To be able to login AS SYSDBA via TNS, create a password file. This file has to be named orapwDUPDB and must be in directory $ORACLE_HOME/dbs. The password must be exactly like the one for the user SYS:

orapwd file=$ORACLE_HOME/dbs/orapwDUPDB password=oracle

Duplicate

To duplicate a database, run the DUPLICATE TARGET DATABASE command. RMAN will automatically create the auxiliary database itself, the only thing needed is to start the Oracle instance in NOMOUNT mode before.

Take care of your ORACLE_SID and RMAN CONNECT strings.

export ORACLE_SID=DUPDB
$ sqlplus / as sysdba
SQL> startup nomount

Duplicate the database ORIGDB to DUPDB:

rman target sys/oracle@ORIGDB catalog rman/rman@rcat auxiliary /
RMAN> duplicate target database to DUPDB;

You can re-un the command from time to time to synchronize the duplicate database (e.g. in a script).

Resources

This entry was posted in Databases, System Administration and tagged , . Bookmark the permalink.