Dev's Weblog

Oracle 11G – Active Duplicate Database

December 14, 2011, 11:08 am

Problem :

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.

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.
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.
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'
;
}

Step 8 : Monitor the duplication process.

using target database control file instead of recovery catalog
allocated channel: C1
channel C1: SID=143 device type=DISK

allocated channel: DUP
channel DUP: SID=96 device type=DISK

Starting Duplicate Db at 14-DEC-11

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfileorcl11202.ora' auxiliary format
 '/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfilePRIMDB.ora'   ;
   sql clone "alter system set spfile= ''/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfilePRIMDB.ora''";
}
executing Memory Script

Starting backup at 14-DEC-11
Finished backup at 14-DEC-11

sql statement: alter system set spfile= ''/export/home/oracle/product/11.2.0.2/dbhome_1/dbs/spfilePRIMDB.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIMDB'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRIMDB'' comment=
 ''duplicate'' scope=spfile";
   sql clone "alter system set  MEMORY_TARGET =
 270M comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRIMDB'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_file_name_convert =
 ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_file_name_convert =
 ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment=
 '''' scope=spfile";
   sql clone "alter system set  control_files =
 ''/u02/database/PRIMDB/control01.ctl'', ''/u02/database/PRIMDB/control02.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_max_processes =
 5 comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''LOCATION=/u02/fra VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIMDB'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PRIMDB'' comment= ''duplicate'' scope=spfile

sql statement: alter system set  MEMORY_TARGET =  270M comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''PRIMDB'' comment= '''' scope=spfile

sql statement: alter system set  db_file_name_convert =  ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment= '''' scope=spfile

sql statement: alter system set  log_file_name_convert =  ''/u02/database/orcl11202'', ''/u02/database/PRIMDB'' comment= '''' scope=spfile

sql statement: alter system set  control_files =  ''/u02/database/PRIMDB/control01.ctl'', ''/u02/database/PRIMDB/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_max_processes =  5 comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''LOCATION=/u02/fra VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMDB'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                218108872 bytes
Database Buffers              58720256 bytes
Redo Buffers                   5021696 bytes
allocated channel: DUP
channel DUP: SID=135 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''ORCL1120'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''PRIMDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u02/database/PRIMDB/control01.ctl';
   restore clone controlfile to  '/u02/database/PRIMDB/control02.ctl' from
 '/u02/database/PRIMDB/control01.ctl';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''ORCL1120'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''PRIMDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                222303176 bytes
Database Buffers              54525952 bytes
Redo Buffers                   5021696 bytes
allocated channel: DUP
channel DUP: SID=134 device type=DISK

Starting backup at 14-DEC-11
channel C1: starting datafile copy
copying current control file
output file name=/u02/database/orcl11202/snapcf_orcl11202.f tag=TAG20111214T103545 RECID=15 STAMP=769862155
channel C1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-DEC-11

Starting restore at 14-DEC-11

channel DUP: copied control file copy
Finished restore at 14-DEC-11

database mounted

contents of Memory Script:
{
   sql clone 'alter database flashback off';
   set newname for datafile  1 to
 "/u02/database/PRIMDB/system01.dbf";
   set newname for datafile  2 to
 "/u02/database/PRIMDB/sysaux01.dbf";
   set newname for datafile  3 to
 "/u02/database/PRIMDB/undotbs01.dbf";
   set newname for datafile  4 to
 "/u02/database/PRIMDB/users01.dbf";
   set newname for datafile  5 to
 "/u02/database/PRIMDB/example01.dbf";
   set newname for datafile  6 to
 "/u02/database/PRIMDB/ccdata.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/u02/database/PRIMDB/system01.dbf"   datafile
 2 auxiliary format
 "/u02/database/PRIMDB/sysaux01.dbf"   datafile
 3 auxiliary format
 "/u02/database/PRIMDB/undotbs01.dbf"   datafile
 4 auxiliary format
 "/u02/database/PRIMDB/users01.dbf"   datafile
 5 auxiliary format
 "/u02/database/PRIMDB/example01.dbf"   datafile
 6 auxiliary format
 "/u02/database/PRIMDB/ccdata.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

sql statement: alter database flashback off

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 14-DEC-11
channel C1: starting datafile copy
input datafile file number=00004 name=/u02/database/orcl11202/users01.dbf
output file name=/u02/database/PRIMDB/users01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:03:35
channel C1: starting datafile copy
input datafile file number=00003 name=/u02/database/orcl11202/undotbs01.dbf
output file name=/u02/database/PRIMDB/undotbs01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:01:45
channel C1: starting datafile copy
input datafile file number=00006 name=/u02/database/orcl11202/ccdata.dbf
output file name=/u02/database/PRIMDB/ccdata.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:01:15
channel C1: starting datafile copy
input datafile file number=00001 name=/u02/database/orcl11202/system01.dbf
output file name=/u02/database/PRIMDB/system01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:00:35
channel C1: starting datafile copy
input datafile file number=00002 name=/u02/database/orcl11202/sysaux01.dbf
output file name=/u02/database/PRIMDB/sysaux01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:00:25
channel C1: starting datafile copy
input datafile file number=00005 name=/u02/database/orcl11202/example01.dbf
output file name=/u02/database/PRIMDB/example01.dbf tag=TAG20111214T103611
channel C1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 14-DEC-11

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/u02/fra/ORCL11202/archivelog/2011_12_14/o1_mf_1_429_7gjzhnxp_.arc" auxiliary format
 "/u02/fra/1_429_769281339.arc"   ;
   catalog clone archivelog  "/u02/fra/1_429_769281339.arc";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 14-DEC-11
channel C1: starting archived log copy
input archived log thread=1 sequence=429 RECID=427 STAMP=769862645
output file name=/u02/fra/1_429_769281339.arc RECID=0 STAMP=0
channel C1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-DEC-11

cataloged archived log
archived log file name=/u02/fra/1_429_769281339.arc RECID=427 STAMP=769862647

datafile 1 switched to datafile copy
input datafile copy RECID=15 STAMP=769862648 file name=/u02/database/PRIMDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=16 STAMP=769862648 file name=/u02/database/PRIMDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=17 STAMP=769862648 file name=/u02/database/PRIMDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=18 STAMP=769862648 file name=/u02/database/PRIMDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=19 STAMP=769862648 file name=/u02/database/PRIMDB/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=20 STAMP=769862648 file name=/u02/database/PRIMDB/ccdata.dbf

contents of Memory Script:
{
   set until scn  3834830;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 14-DEC-11

starting media recovery

archived log for thread 1 with sequence 429 is already on disk as file /u02/fra/1_429_769281339.arc
archived log file name=/u02/fra/1_429_769281339.arc thread=1 sequence=429
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-DEC-11
Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                243274696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5021696 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PRIMDB'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''PRIMDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     284008448 bytes

Fixed Size                     2157624 bytes
Variable Size                243274696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5021696 bytes
allocated channel: DUP
channel DUP: SID=133 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PRIMDB" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     1597
 LOGFILE
  GROUP  1 ( '/u02/database/PRIMDB/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u02/database/PRIMDB/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u02/database/PRIMDB/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u02/database/PRIMDB/system01.dbf'
 CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u02/database/PRIMDB/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u02/database/PRIMDB/sysaux01.dbf",
 "/u02/database/PRIMDB/undotbs01.dbf",
 "/u02/database/PRIMDB/users01.dbf",
 "/u02/database/PRIMDB/example01.dbf",
 "/u02/database/PRIMDB/ccdata.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/database/PRIMDB/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/sysaux01.dbf RECID=1 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/undotbs01.dbf RECID=2 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/users01.dbf RECID=3 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/example01.dbf RECID=4 STAMP=769862682
cataloged datafile copy
datafile copy file name=/u02/database/PRIMDB/ccdata.dbf RECID=5 STAMP=769862682

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=769862682 file name=/u02/database/PRIMDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=769862682 file name=/u02/database/PRIMDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=769862682 file name=/u02/database/PRIMDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=769862682 file name=/u02/database/PRIMDB/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=769862682 file name=/u02/database/PRIMDB/ccdata.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database enable block change tracking using file '/u02/database/PRIMDB/bct.dbf'

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Executing: alter database flashback on
Finished Duplicate Db at 14-DEC-11
released channel: C1
released channel: DUP

Category: 11G, Oracle, Oracle DB | Comment (RSS) | Trackback

Oracle 11G – Active Duplicate Database

Leave a Reply

Categories

Recent Posts