Loading ...
Global Do...
News & Politics
9
0
Try Now
Log In
Pricing
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/<pwd>@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/<pwd>@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**
