Tuesday, October 6, 2009

CREATING DUPLICATE DATABASE

Creation of duplicate database is more of a necessity and should not be viewed as a luxury. If you are in an environment where you have “DEV”, “TEST”, “STAGE” and “PROD” or some combination of these, you may, in all probability, be required to create Duplicate databases on a regular basis. The whole process can also be automated and you don’t thereafter need to lose sleepless nights for Database duplication. Here it is endeavored to be run in more or less “cut and paste” form for clarity of understanding.

Database Version :- 9.2.0.4

Operating System :- Redhat Advanced Server 3.0

Scenario :- There are three servers :-

TARGET Server – The machine where you have the database to be cloned. This machine has IP address as 10.8.169.94. ORACLE_SID=TGT

RMAN Server – The machine, which is running RMAN catalog. The IP address of this machine is 10.8.169.99. ORACLE_SID=RMND

DUPLICATE Server – The machine, which is running RMAN catalog. The IP address of this machine is 10.8.169.96. ORACLE_SID=AUX

Following is assumed: -
-    All three databases are running in ARCHIVELOG Mode. Though it is necessary only for the TARGET database to be in ARCHIVELOG mode.
-    RMAN with CATALOG has been implemented on the RMAN server.
-    The target database has been registered in the RMAN CATALOG.
-    The connect string for the target database is TGT
-    The connect string for the auxiliary database (where duplicate is to be created is AUX)
-    The connect string for the RMAN database is RMND
-    The tnsnames.ora file for all the three servers have been updated and you can connect to any server from any server using the appropriate connect string

For the purpose of this project it is proposed that the directory structure in all the three databases be identical. It helps to follow the principle of “KISS” (Keep it simple, Stupid!).
In our environment, we follow the following nomenclature :-

Under “/” directory (only directories having anything to with oracle have been shown):-

drwxr-xr-x   56 root     root         4096 May 14 14:50 etc
(has two files “oratab” and “oraInst.loc”)
drwxr-xr-x    4 root     root         4096 May  6 21:44 home
(has the home for oracle user – “/home/oracle” – Good place for your healthcheck, export, backup scripts)
drwxr-xr-x    5 root     root         4096 May  6 21:40 opt
( has oracle binaries installed as per OFA as follows:-
ORACLE_BASE =/opt/oracle
ORACLE_HOME=/opt/oracle/product/9.2.0 )
drwxr-xr-x    3 oracle   oradba       4096 May 14 14:41 oraarch1
(Archives from first member of all the groups is sent here)
drwxr-xr-x    3 oracle   oradba       4096 May 14 14:42 oraarch2
(Archives from the second members of all the groups are sent here )
drwxr-xr-x    3 oracle   oradba       4096 May 18 10:06 oraback
(has two directories – “Hotback” and “Export” – one for hot backups and the other for all kinds of exports)
drwxr-xr-x    3 oracle   oradba       4096 May 14 14:42 oradata1
(The datafiles including system, tools and example are kept here. In case of applications datafiles, the same can be sent to different disks for performance reasons.  Therefore, you may have oradata2, oradata3 etc under “/”)
drwxr-xr-x    2 oracle   oradba       4096 May 13 10:13 oraindex1
(The datafiles including system, tools and example are kept here. In case of applications datafiles, the same can be sent to different disks for performance reasons.  Therefore, you may have oradata2, oradata3 etc under “/”)
drwxr-xr-x    3 oracle   oradba       4096 May 14 14:43 oraredo1
(The first members of the redo groups are located here)
drwxr-xr-x    3 oracle   oradba       4096 May 14 14:43 oraredo2
(The second members of the redo groups are located here)
drwxr-xr-x    2 oracle   oradba       4096 May  6 14:50 software
(The installable of the oracle software can be kept here)
drwxr-xr-x   15 root     root         4096 May  6 12:16 usr
(It has three files under /usr/local/bin :-
“oraenv”, “coraenv” and “dbhome”)

Preliminary Steps

On Target Server, run these commands to make its backup directory shared:-

#echo “/oraback     10.8.169.96(rw)” >> /etc/exports

The /etc/exports files is read by nfs deamon and the resources mentioned therein are mounted with the stated options.

#/etc/rc.d/init.d/nfs stop
#/etc/rc.d/init.d/portmap stop
#/etc/rc.d/init.d/portmap start
#/etc/rc.d/init.d/nfs start

On Duplicate Server, run these commands as “root” user to mount the backed up files:-

#/etc/rc.d/init.d/nfs stop
#/etc/rc.d/init.d/portmap stop
#/etc/rc.d/init.d/portmap start
#/etc/rc.d/init.d/nfs start


#mount 10.8.169.94:/oraback /oraback

Check whether the resource has mounted

# mount
#su – oracle
cd /
chmod –R 755 /oraback

$cd /oraback/Hotback
$ls –al
total 1116388
drwxr-xr-x    3 oracle   oradba       4096 May 19 14:25 .
drwxr-xr-x   28 root     root         4096 May 12 14:46 ..
-rw-r-----    1 oracle   oradba    2097152 May 19 14:25 c-342784712-200405
-rw-r-----    1 oracle   oradba   212478464 May 19 14:15 TGTf_10fm5na1_1
-rw-r-----    1 oracle   oradba   209715200 May 19 14:15 TGTf_11fm5na1_1
-rw-r-----    1 oracle   oradba   83073536 May 19 14:15 TGTf_12fm5na1_1_
-rw-r-----    1 oracle   oradba    2185728 May 19 14:15 TGTf_13fm5nbh_1_
-rw-r-----    1 oracle   oradba   586088448 May 19 14:19 TGTf_14fm5nd4_1
-rw-r-----    1 oracle   oradba   15769600 May 19 14:25 TGTf_15fm5nd4_1_
-rw-r-----    1 oracle   oradba   27869184 May 19 14:25 TGTf_16fm5nd4_1_
-rw-r-----    1 oracle   oradba     643072 May 19 14:25 TGTf_17fm5nuf_1_
-rwxr-xr-x    1 oracle   oradba    2056192 May 19 14:25 snapcf_V92321.f

This command should show you the same resource as on the /oraback of TARGET server.

The oracle initialization parameters need to be changed. The mandatory init.ora parameters in TARGET database:-

$ vi /opt/oracle/product/9.2.0/dbs/initTGT.ora
db_name=AUX
db_files = 80                                                        
db_file_multiblock_read_count = 8                                   
db_block_buffers = 100                                             
db_block_size = 8192
shared_pool_size = 3500000                                           
log_checkpoint_interval = 10000
processes = 50                                                      
parallel_max_servers = 5                                             
log_buffer = 32768                                                   
max_dump_file_size = 10240     
global_names = TRUE
control_files = (/oradata1/ora_control1, /oradata1/ora_control2)
compatible = 9.2.0.0
undo_management=AUTO
remote_login_passwordfile= EXCLUSIVE

Ensure that the DB_BLOCK_SIZE parameter should be same and undo_management set to “AUTO” in both the databases.

On the AUX server, modify the init.ora file as follows:-

$ vi /opt/oracle/product/9.2.0/dbs/initAUX.ora
db_name=AUX
db_files = 80                                                        
db_file_multiblock_read_count = 8                                   
db_block_buffers = 100                                             
db_block_size = 8192
shared_pool_size = 3500000                                           
log_checkpoint_interval = 10000
processes = 50                                                      
parallel_max_servers = 5                                             
log_buffer = 32768                                                   
max_dump_file_size = 10240     
global_names = TRUE
control_files = (/oradata1/ora_control1, /oradata1/ora_control2)
compatible = 9.2.0.0
undo_management=AUTO
remote_login_passwordfile= EXCLUSIVE

Duplicate Database Creation

Run the following commands on target database only.

Sqlplus “/as sysdba”
SQL>Alter system switch logfile;
Exit

$rman target / catalog rman/rman@RMND2 auxiliary sys/@AUX

RMAN>run {
allocate channel C1 device type disk;
allocate channel C2 device type disk;
allocate channel C3 device type disk;
backup format '/oraback/%df_%U' database plus archivelog delete input;
}

After you have taken the backup, your /oraback/Hotback directory will look something like this:-

$ cd /oraback/Hotback
$ ls -al
total 1116388
drwxr-xr-x    3 oracle   oradba       4096 May 19 14:25 .
drwxr-xr-x   28 root     root         4096 May 12 14:46 ..
-rw-r-----    1 oracle   oradba    2097152 May 19 14:25 c-342784712-200405
-rw-r-----    1 oracle   oradba   212478464 May 19 14:15 TGTf_10fm5na1_1
-rw-r-----    1 oracle   oradba   209715200 May 19 14:15 TGTf_11fm5na1_1
-rw-r-----    1 oracle   oradba   83073536 May 19 14:15 TGTf_12fm5na1_1_
-rw-r-----    1 oracle   oradba    2185728 May 19 14:15 TGTf_13fm5nbh_1_
-rw-r-----    1 oracle   oradba   586088448 May 19 14:19 TGTf_14fm5nd4_1
-rw-r-----    1 oracle   oradba   15769600 May 19 14:25 TGTf_15fm5nd4_1_
-rw-r-----    1 oracle   oradba   27869184 May 19 14:25 TGTf_16fm5nd4_1_
-rw-r-----    1 oracle   oradba     643072 May 19 14:25 TGTf_17fm5nuf_1_
-rwxr-xr-x    1 oracle   oradba    2056192 May 19 14:25 snapcf_V92321.f

To create Duplicate database, run this command immediately after you have taken the backup. It is better to put this command in a script and call the script:-

$vi DuplicateDB.sql

run{
SET UNTIL TIME "to_date('2004/05/19 14:25:00','YYYY/MM/DD HH24:MI:SS')";
SET NEWNAME FOR DATAFILE '/oraindex1/TGT_idx01.dbf' TO '/oraindex1/AUX/TGT_idx01.dbf';
SET NEWNAME FOR DATAFILE '/oradata1/TGT_data01.dbf' TO '/oradata1/AUX/TGT_data01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/xdb01.dbf' TO '/oradata1/AUX/xdb01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/users01.dbf' TO '/oradata1/AUX/users01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/tools01.dbf' TO '/oradata1/AUX/tools01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/odm01.dbf' TO '/oradata1/AUX/odm01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/indx01.dbf' TO '/oraindex1/AUX/indx01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/example01.dbf' TO '/oradata1/AUX/example01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/drsys01.dbf' TO '/oradata1/AUX/drsys01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/cwmlite01.dbf' TO '/oradata1/AUX/cwmlite01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/undotbs01.dbf' TO '/oradata1/AUX/undotbs01.dbf';
SET NEWNAME FOR DATAFILE '/opt/oracle/oradata/TGT/system01.dbf' TO '/oradata1/AUX/system01.dbf';
DUPLICATE TARGET DATABASE TO AUX
LOGFILE
GROUP 1 ('/oradata1/redo01a.log','/oradata1/redo01b.log') size 10M reuse,
GROUP 2 ('/oradata1/redo02a.log','/oradata1/redo02b.log') size 10M reuse;
}

Now call the script to create the duplicate database.

$rman target / catalog rman/rman@RMND2 auxiliary sys/@AUX
RMAN>@/home/oracle/DuplicateDB.sql

The Process

For the purpose of understanding, it should be noted that following steps will happen during this command :-
-    Allocation of channels
-    Restoration of files from their present location to the new location on the duplicate database
-    Creation of controlfile
-    Switching of the datafiles’ names
-    Recovery of datafiles
-    Deletion of input logs
-    Opening the database with “RESETLOGS” option


The output should roughly look like this :-

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

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 restore at 19-MAY-04

using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /oradata1/AUX/system01.dbf
restoring datafile 00002 to /oradata1/AUX/undotbs01.dbf
restoring datafile 00005 to /oradata1/AUX/example01.dbf
restoring datafile 00009 to /oradata1/AUX/users01.dbf
channel ORA_AUX_DISK_2: starting datafile backupset restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /oradata1/AUX/drsys01.dbf
restoring datafile 00008 to /oradata1/AUX/tools01.dbf
restoring datafile 00010 to /oradata1/AUX/xdb01.dbf
restoring datafile 00011 to /oradata1/AUX/TGT_data01.dbf
channel ORA_AUX_DISK_3: starting datafile backupset restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /oradata1/AUX/cwmlite01.dbf
restoring datafile 00006 to /oraindex1/AUX/indx01.dbf
restoring datafile 00007 to /oradata1/AUX/odm01.dbf
restoring datafile 00012 to /oraindex1/AUX/TGT_idx01.dbf
channel ORA_AUX_DISK_2: restored backup piece 1
piece handle=/oraback/TGTf_16fm5nd4_1_1 tag=TAG20040519T141603 params=NULL
channel ORA_AUX_DISK_2: restore complete
channel ORA_AUX_DISK_3: restored backup piece 1
piece handle=/oraback/TGTf_15fm5nd4_1_1 tag=TAG20040519T141603 params=NULL
channel ORA_AUX_DISK_3: restore complete
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oraback/TGTf_14fm5nd4_1_1 tag=TAG20040519T141603 params=NULL
channel ORA_AUX_DISK_1: restore complete
Finished restore at 19-MAY-04
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES     50
  MAXLOGMEMBERS      5
  MAXDATAFILES      100
  MAXINSTANCES     1
  MAXLOGHISTORY      226
 LOGFILE
  GROUP  1 ( '/oradata1/redo01a.log', '/oradata1/redo01b.log' ) SIZE   10485760  REUSE,
  GROUP  2 ( '/oradata1/redo02a.log', '/oradata1/redo02b.log' ) SIZE   10485760  REUSE
 DATAFILE
  '/oradata1/AUX/system01.dbf'
 CHARACTER SET WE8ISO8859P1


printing stored script: Memory Script
{
   switch clone datafile all;
}
executing script: Memory Script

datafile 12 switched to datafile copy
input datafilecopy recid=1 stamp=526575097 filename=/oraindex1/AUX/TGT_idx01.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=2 stamp=526575097 filename=/oradata1/AUX/TGT_data01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=3 stamp=526575097 filename=/oradata1/AUX/xdb01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=4 stamp=526575097 filename=/oradata1/AUX/users01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=5 stamp=526575097 filename=/oradata1/AUX/tools01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=526575097 filename=/oradata1/AUX/odm01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=7 stamp=526575097 filename=/oraindex1/AUX/indx01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=8 stamp=526575097 filename=/oradata1/AUX/example01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=9 stamp=526575097 filename=/oradata1/AUX/drsys01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=10 stamp=526575098 filename=/oradata1/AUX/cwmlite01.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=11 stamp=526575098 filename=/oradata1/AUX/undotbs01.dbf

printing stored script: Memory Script
{
   set until time  "to_date('2004/05/19 14:25:00','YYYY/MM/DD HH24:MI:SS')";
   recover
   clone database
    delete archivelog
   ;
}
executing script: Memory Script

executing command: SET until clause

Starting recover at 19-MAY-04
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=25
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/oraback/TGTf_17fm5nuf_1_1 tag=TAG20040519T142519 params=NULL
channel ORA_AUX_DISK_1: restore complete
archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_25.dbf thread=1 sequence=25
channel clone_default: deleting archive log(s)
archive log filename=/opt/oracle/product/9.2.0/dbs/arch1_25.dbf recid=1 stamp=526575099
media recovery complete
Finished recover at 19-MAY-04

printing stored script: Memory Script
{
   shutdown clone;
   startup clone nomount ;
}
executing script: Memory Script


database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area      43357204 bytes

Fixed Size                      451604 bytes
Variable Size                 41943040 bytes
Database Buffers                819200 bytes
Redo Buffers                    143360 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES     50
  MAXLOGMEMBERS      5
  MAXDATAFILES      100
  MAXINSTANCES     1
  MAXLOGHISTORY      226
 LOGFILE
  GROUP  1 ( '/oradata1/redo01a.log', '/oradata1/redo01b.log' ) SIZE   10485760  REUSE,
  GROUP  2 ( '/oradata1/redo02a.log', '/oradata1/redo02b.log' ) SIZE   10485760  REUSE
 DATAFILE
  '/oradata1/AUX/system01.dbf'
 CHARACTER SET WE8ISO8859P1


printing stored script: Memory Script
{
   catalog clone datafilecopy  "/oradata1/AUX/undotbs01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/cwmlite01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/drsys01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/example01.dbf";
   catalog clone datafilecopy  "/oraindex1/AUX/indx01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/odm01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/tools01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/users01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/xdb01.dbf";
   catalog clone datafilecopy  "/oradata1/AUX/TGT_data01.dbf";
   catalog clone datafilecopy  "/oraindex1/AUX/TGT_idx01.dbf";
   switch clone datafile all;
}
executing script: Memory Script

cataloged datafile copy
datafile copy filename=/oradata1/AUX/undotbs01.dbf recid=1 stamp=526575891

cataloged datafile copy
datafile copy filename=/oradata1/AUX/cwmlite01.dbf recid=2 stamp=526575891

cataloged datafile copy
datafile copy filename=/oradata1/AUX/drsys01.dbf recid=3 stamp=526575891

cataloged datafile copy
datafile copy filename=/oradata1/AUX/example01.dbf recid=4 stamp=526575892

cataloged datafile copy
datafile copy filename=/oraindex1/AUX/indx01.dbf recid=5 stamp=526575892

cataloged datafile copy
datafile copy filename=/oradata1/AUX/odm01.dbf recid=6 stamp=526575892

cataloged datafile copy
datafile copy filename=/oradata1/AUX/tools01.dbf recid=7 stamp=526575892

cataloged datafile copy
datafile copy filename=/oradata1/AUX/users01.dbf recid=8 stamp=526575892

cataloged datafile copy
datafile copy filename=/oradata1/AUX/xdb01.dbf recid=9 stamp=526575892

cataloged datafile copy
datafile copy filename=/oradata1/AUX/TGT_data01.dbf recid=10 stamp=526575892

cataloged datafile copy
datafile copy filename=/oraindex1/AUX/TGT_idx01.dbf recid=11 stamp=526575892

datafile 12 switched to datafile copy
input datafilecopy recid=11 stamp=526575892 filename=/oraindex1/AUX/TGT_idx01.dbf
datafile 11 switched to datafile copy
input datafilecopy recid=10 stamp=526575892 filename=/oradata1/AUX/TGT_data01.dbf
datafile 10 switched to datafile copy
input datafilecopy recid=9 stamp=526575892 filename=/oradata1/AUX/xdb01.dbf
datafile 9 switched to datafile copy
input datafilecopy recid=8 stamp=526575892 filename=/oradata1/AUX/users01.dbf
datafile 8 switched to datafile copy
input datafilecopy recid=7 stamp=526575892 filename=/oradata1/AUX/tools01.dbf
datafile 7 switched to datafile copy
input datafilecopy recid=6 stamp=526575892 filename=/oradata1/AUX/odm01.dbf
datafile 6 switched to datafile copy
input datafilecopy recid=5 stamp=526575892 filename=/oraindex1/AUX/indx01.dbf
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=526575892 filename=/oradata1/AUX/example01.dbf
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=526575891 filename=/oradata1/AUX/drsys01.dbf
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=526575891 filename=/oradata1/AUX/cwmlite01.dbf
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=526575891 filename=/oradata1/AUX/undotbs01.dbf

printing stored script: Memory Script
{
   Alter clone database open resetlogs;
}
executing script: Memory Script

database opened
Finished Duplicate Db at 19-MAY-04

RMAN> **end-of-file**

No comments:

Post a Comment