Duplicate Database on Same Server & Same ORACLE_HOME Using RMAN in 10 Steps

This Duplication Process is applicable where Source DB is in Archivelog Mode or Noarchivelog Mode. Ensure we have RMAN backups of source DB. Also if Catalog DB is not available, RMAN will use controlfile instead.

Source DB: COSP1

Target DB: COSP1BK

Catalog DB: RMANDB

DB Version: 11.2.0.1 for all 3 databases

1) Set environment variable of SOURCE DB(like "go" profile)

2) Create PFILE using Source DB SPFILE

   $ sqlplus / as sysdba

   SQL> create pfile from spfile;

3) Modify new PFILE

   $ cd $ORACLE_HOME/dbs

   $ mv initcosp1.ora initcosp1bk.ora

   $ vi initcosp1bk.ora

   :%s/cosp1/cosp1bk (Alter DB name to Target DB)

Append:-

   db_file_name_convert=('cosp1','cosp1bk')

   log_file_name_convert=('cosp1','cosp1bk')

   :wq! (save the file)

------------------Move PFILE------------------------------ (Optional)

   $ mv initcosp1bk.ora /u02/app/oracle/admin/cosp1bk/pfile/initcosp1bk.ora

   $ ln -s /u02/app/oracle/admin/cosp1bk/pfile/initcosp1bk.ora /u02/app/oracle/product/11.2.0.1/db_1/dbs/initcosp1bk.ora

4) Create directory structures as per newly modified PFILE

5) Create ORAPWD file for Target DB

   $ cd $ORACLE_HOME/dbs

   $ orapwd file=orapwcosp1bk password=oracle entries=5

6) Update TNSNAMES.ORA file and LISTENER.ORA file, then reload it(MUST)

   $ lsnrctl reload

7) Set environment variable of TARGET DB(copy "go" profile from SOURCE DB)

8) Start TARGET DB in NOMOUNT Stage using new PFILE

   $ sqlplus / as sysdba

   SQL> startup nomount pfile='/u02/app/oracle/product/11.2.0.1/db_1/dbs/initcosp1bk.ora'

9) Set environment variable of SOURCE DB for final DUPLICATION Process

10) Connect to RMAN without catalog

   $ rman target / auxiliary sys/oracle@cosp1bk

Connect to RMAN with catalog

   $ rman target / auxiliary sys/oracle@cosp1bk catalog rmancat/rmancat@rmandb

Note: Common error encountered while connecting RMAN.

RMAN-04006: error from auxiliary database: ORA-01031: insufficient privileges

Please ensure the following:-

i)   Linux is case sensitive and hence double check Listener.ora is properly updated and case matches tnsnames.ora entry.

ii)  Verify tnsping is successful for auxiliary database

iii) Use the same password for auxiliary database which you created using ORAPWD on Step 5

   Once RMAN is connected execute the following run block:-

   RMAN> run

   2> {

   3> allocate channel tar1 device type disk;

   4> allocate auxiliary channel aux1 device type disk;

   5> set until time "to_date('Mar 31 2014 23:59:00','Mon DD YYYY HH24:MI:SS')";

   6> duplicate target database to 'cosp1bk';

   7> }

      Note: "set until time" clause is used to build Target DB using past backup of Source DB. Incase of building Target DB with latest backup of Source DB, just omit this clause; 

   RMAN> run

   2> {

   3> allocate channel tar1 device type disk;

   4> allocate auxiliary channel aux1 device type disk;

   5> duplicate target database to 'cosp1bk';

   6> }

=================================================================================

NOTE: I use “go” profile to set ORACLE_HOME Environment. Please find below an example:-

**********************************************************

#Oracle Settings

TMP=/tmp; export TMP

TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=diamond; export ORACLE_HOSTNAME

ORACLE_UNQNAME=cosp1; export ORACLE_UNQNAME

ORACLE_BASE=/u02/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/11.2.0.1/db_1; export ORACLE_HOME

ORACLE_SID=cosp1; export ORACLE_SID

TNS_ADMIN=$ORACLE_HOME/network/admin; export TNS_ADMIN

PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

echo

echo Oracle Home is set to `echo $ORACLE_HOME` 

echo Oracle SID is set to `echo $ORACLE_SID` 

echo

**********************************************************

LISTENER.ORA

**********************************************************

LISTENER =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = tcp)(PORT = 1521)(HOST = diamond))
  )

CONNECT_TIMEOUT_LISTENER = 0

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cosp1)
      (SID_NAME = cosp1)
      (ORACLE_HOME = /u02/app/oracle/product/11.2.0.1/db_1)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = cosp1bk)
      (SID_NAME = cosp1bk)
      (ORACLE_HOME = /u02/app/oracle/product/11.2.0.1/db_1)
    )

    (SID_DESC =
      (GLOBAL_DBNAME = rmandb)
      (SID_NAME = rmandb)
      (ORACLE_HOME = /u03/app/oracle/product/11.2.0.1/db_1)
    )

  )
**********************************************************

TNSNAMES.ORA

**********************************************************
cosp1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(Host = diamond)(Port = 1521))
    )
    (CONNECT_DATA = (SID = cosp1))
  )

cosp1bk =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(Host = diamond)(Port = 1521))
    )
    (CONNECT_DATA = (SID = cosp1bk))
  )

rmandb =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(Host = diamond)(Port = 1521))
    )
    (CONNECT_DATA = (SID = rmandb))
  )

**********************************************************

=================================================================================