Oracle Tips

I was asked to duplicate the production database (11.2.0.2) on the same machine which will be used as a test instance.
Solution:
With 11G there are many ways to duplicate the database.

1. Manual Duplicate – Where you need to take the backup of production system and move the backup pieces to the machine where want to duplicate the database.
2. Duplicate database from location – with 11GR2 you can use this option to duplicate the production database from its backup.The main advantage of this method is that while duplicating the database you don’t need to connect to production system at all.
3. Active Duplicate – With this method you need to have session with production and to be cloned database before you can perform the duplication.The advantage of this method is that you do not need to have production database’s backup beforehand.The duplication process with take the backup and will get rid of it once the duplication is done.

I opted for the Active duplication.Here are the steps which I performed to duplicate the database.
My environment.
Production DB
Clone DB
ORCL11202
PRIMDB

• Step 1 : Create TNS Aliases

Create tns alias entries into $ORACLE_HOME/network/admin/tnsnames.ora  for both Production and clone database.

ORCL11202 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = meteor)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl11202)
    )
  )

PRIMDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = meteor)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIMDB)
    )
  )

• Step 2 : Create Static Registration for both databases in the listner.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = meteor)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
          (GLOBAL_DBNAME = ORCL11202)
          (ORACLE_HOME = /export/home/oracle/product/11.2.0.2/dbhome_1)
          (SID_NAME = ORCL11202)
    )
    (SID_DESC =
             (GLOBAL_DBNAME = PRIMDB)
             (ORACLE_HOME = /export/home/oracle/product/11.2.0.2/dbhome_1)
             (SID_NAME = PRIMDB)
   )
  )

• Step 3 : Create password file and pfile for clone database.

Create password for the clone database.

$orapwd file=$ORACLE_HOME/dbs/orapwPRIMDB password=password entries=10

Create a pfile $ORACLE_HOME/dbs/initPRIMDB.ora with  following parameters.

*.db_name='PRIMDB'
*.diagnostic_dest=/export/home/oracle

• Step 4 : Start Clone database in the nomount mode.

$export ORACLE_SID=PRIMDB

$sqlplus / as sysdba

SQL>startup nomount;
ORACLE instance started.

Total System Global Area  217219072 bytes
Fixed Size                  2156896 bytes
Variable Size             159389344 bytes
Database Buffers           50331648 bytes
Redo Buffers                5341184 bytes

• Step 5 : Create necessary directories for the clone database.

$mkdir /u02/database/PRIMDB

$mkdir /u02/fra/PRIMDB

• Step 6 : Connect to Production instance and auxiliary instance using RMAN.

$rman target sys/password@orcl11202 auxiliary sys/password@PRIMDB

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Dec 14 10:31:25 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL1120 (DBID=3456964407)
connected to auxiliary database: PRIMDB (not mounted)

• Step 7 : Start the Duplication process.

run {
allocate channel C1 type disk;
allocate auxiliary channel DUP type disk;
duplicate target database
to PRIMDB
from active database
spfile
SET MEMORY_TARGET="270M"
set db_unique_name='PRIMDB'
set db_file_name_convert='/u02/database/orcl11202','/u02/database/PRIMDB'
set log_file_name_convert='/u02/database/orcl11202','/u02/database/PRIMDB'
set control_files='/u02/database/PRIMDB/control01.ctl','/u02/database/PRIMDB/control02.ctl'
set log_archive_max_processes='5'
set log_archive_dest_1='LOCATION=/u02/fra VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'
;
}