.JPG)
Posts
Oracle Database 11g extends the well-known duplicate database command to let you duplicate a database over the network without any need for prior backups of the source database. This feature is called active database duplication or networkaware database duplication. Using the active duplication method, you can duplicate a live or active database, with a minimal network and CPU overhead. You can perform the duplication on the same or a different server in your network. You don’t need to make any copies of the source database, which saves you time and storage space. You can use active database duplication techniques to clone a database or create a standby database.
We can use RMAN to achieve this. with RMAN we need to write a lot of commands.on rman , Best is to use OEM.
Command line option:
RMAN> duplicate database to dupdb from active database .......(+ few options)
Step 1: Login to 11g Oracle Enterprise manager.
- You should be logged in as sys as sysdba and you should have the source and target OS credentials.
Step 2:
Go to data movement -- > clone
Once you are logged in as SYS, You will prompted for Source host credentials.
After you have provided this information, You will be prompted for the destination credentials, Target database ,Target instance name and about the database storage options.
Here you need to specify the OFA or Database area and Fast (Flash)recovery area
I have opted for not installing the enterprise manager for the new database
You can specify if you want to execute this clone now OR at a later time. For example you can schedule this during night hours when the load on the database is least.
Here you can review and submit the JOB.
Once submited you can monitor the job.
This is the easiest method for cloning,
LOG FILE from OEM
RMAN>
connected to target database: SANDEEP (DBID=4225970695)
RMAN>
connected to auxiliary database: PANCHAL (not mounted)
RMAN>
echo set on
RMAN> run {
2> DUPLICATE TARGET DATABASE TO panchal
3> FROM ACTIVE DATABASE
4> ;
5> }
Starting Duplicate Db at 12-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK
contents of Memory Script:
{
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
datafile 5 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 12-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\11GSOFTWARE\ORADATA\SANDEEP\SYSAUX01.DBF
output file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSAUX_DATA_D-SANDEEP_I-4225970695_TS-SYSAUX_FNO-2_39LAVDIK_.DBF tag=TAG20100412T205547 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\11GSOFTWARE\ORADATA\SANDEEP\SYSTEM01.DBF
output file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSTEM_DATA_D-SANDEEP_I-4225970695_TS-SYSTEM_FNO-1_3ALAVDLM_.DBF tag=TAG20100412T205547 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\11GSOFTWARE\ORADATA\SANDEEP\UNDOTBS01.DBF
output file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_UNDOTBS1_DATA_D-SANDEEP_I-4225970695_TS-UNDOTBS1_FNO-3_3BLAVDOC_.DBF tag=TAG20100412T205547 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\11GSOFTWARE\ORADATA\SANDEEP\EXAMPLE01.DBF
output file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_EXAMPLE_DATA_D-SANDEEP_I-4225970695_TS-EXAMPLE_FNO-5_3CLAVDOU_.DBF tag=TAG20100412T205547 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\11GSOFTWARE\ORADATA\SANDEEP\USERS01.DBF
output file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_USERS_DATA_D-SANDEEP_I-4225970695_TS-USERS_FNO-4_3DLAVDPO_.DBF tag=TAG20100412T205547 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 12-APR-10
sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PANCHAL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSTEM_DATA_D-SANDEEP_I-4225970695_TS-SYSTEM_FNO-1_3ALAVDLM_.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
backup as copy reuse
archivelog like "D:\11GSOFTWARE\FLASH_RECOVERY_AREA\SANDEEP\ARCHIVELOG\2010_04_12\O1_MF_1_142_5W6YXFW8_.ARC" auxiliary format
"D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\ARCHIVELOG\2010_04_12\O1_MF_1_142_%U_.ARC" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 12-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=142 RECID=24 STAMP=716158783
output file name=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\ARCHIVELOG\2010_04_12\O1_MF_1_142_ARCH_D-SANDEEP_ID-4225970695_S-142_T-1_A-711413643_3ELAVDQ2_.ARC RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 12-APR-10
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\ARCHIVELOG\2010_04_12\O1_MF_1_142_ARCH_D-SANDEEP_ID-4225970695_S-142_T-1_A-711413643_3ELAVDQ2_.ARC
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\ARCHIVELOG\2010_04_12\O1_MF_1_142_ARCH_D-SANDEEP_ID-4225970695_S-142_T-1_A-711413643_3ELAVDQ2_.ARC
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=716158789 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSAUX_DATA_D-SANDEEP_I-4225970695_TS-SYSAUX_FNO-2_39LAVDIK_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=716158789 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_UNDOTBS1_DATA_D-SANDEEP_I-4225970695_TS-UNDOTBS1_FNO-3_3BLAVDOC_.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=716158789 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_USERS_DATA_D-SANDEEP_I-4225970695_TS-USERS_FNO-4_3DLAVDPO_.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=716158789 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_EXAMPLE_DATA_D-SANDEEP_I-4225970695_TS-EXAMPLE_FNO-5_3CLAVDOU_.DBF
contents of Memory Script:
{
set until scn 4446456;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 12-APR-10
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 142 is already on disk as file D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\ARCHIVELOG\2010_04_12\O1_MF_1_142_ARCH_D-SANDEEP_ID-4225970695_S-142_T-1_A-711413643_3ELAVDQ2_.ARC
archived log file name=D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\ARCHIVELOG\2010_04_12\O1_MF_1_142_ARCH_D-SANDEEP_ID-4225970695_S-142_T-1_A-711413643_3ELAVDQ2_.ARC thread=1 sequence=142
media recovery complete, elapsed time: 00:00:05
Finished recover at 12-APR-10
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 167775332 bytes
Database Buffers 360710144 bytes
Redo Buffers 5828608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PANCHAL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSTEM_DATA_D-SANDEEP_I-4225970695_TS-SYSTEM_FNO-1_3ALAVDLM_.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSAUX_DATA_D-SANDEEP_I-4225970695_TS-SYSAUX_FNO-2_39LAVDIK_.DBF";
catalog clone datafilecopy "D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_UNDOTBS1_DATA_D-SANDEEP_I-4225970695_TS-UNDOTBS1_FNO-3_3BLAVDOC_.DBF";
catalog clone datafilecopy "D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_USERS_DATA_D-SANDEEP_I-4225970695_TS-USERS_FNO-4_3DLAVDPO_.DBF";
catalog clone datafilecopy "D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_EXAMPLE_DATA_D-SANDEEP_I-4225970695_TS-EXAMPLE_FNO-5_3CLAVDOU_.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_TEMP_%U_.TMP in control file
cataloged datafile copy
datafile copy file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSAUX_DATA_D-SANDEEP_I-4225970695_TS-SYSAUX_FNO-2_39LAVDIK_.DBF RECID=1 STAMP=716158820
cataloged datafile copy
datafile copy file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_UNDOTBS1_DATA_D-SANDEEP_I-4225970695_TS-UNDOTBS1_FNO-3_3BLAVDOC_.DBF RECID=2 STAMP=716158821
cataloged datafile copy
datafile copy file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_USERS_DATA_D-SANDEEP_I-4225970695_TS-USERS_FNO-4_3DLAVDPO_.DBF RECID=3 STAMP=716158821
cataloged datafile copy
datafile copy file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_EXAMPLE_DATA_D-SANDEEP_I-4225970695_TS-EXAMPLE_FNO-5_3CLAVDOU_.DBF RECID=4 STAMP=716158821
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=716158820 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_SYSAUX_DATA_D-SANDEEP_I-4225970695_TS-SYSAUX_FNO-2_39LAVDIK_.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=716158821 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_UNDOTBS1_DATA_D-SANDEEP_I-4225970695_TS-UNDOTBS1_FNO-3_3BLAVDOC_.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=716158821 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_USERS_DATA_D-SANDEEP_I-4225970695_TS-USERS_FNO-4_3DLAVDPO_.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=716158821 file name=D:\11GSOFTWARE\ORADATA\PANCHAL\DATAFILE\O1_MF_EXAMPLE_DATA_D-SANDEEP_I-4225970695_TS-EXAMPLE_FNO-5_3CLAVDOU_.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 12-APR-10
Recover database log file from OEM:
Warning in closing SQL_SCRIPT : at D:\11gsoftware\product\11.1.0\db_1/sysman/admin/scripts/db/dbclone/db_clone.pl line 350.
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 21:02:23 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 Checked file 1:
filename# D:\11GSOFTWARE\ORADATA\PANCHAL\CONTROLFILE\O1_MF_5W6YXHRB_.CTL
Checked file 2:
filename# D:\11GSOFTWARE\FLASH_RECOVERY_AREA\PANCHAL\CONTROLFILE\O1_MF_5W6YXJBP_.CTL
PL/SQL procedure successfully completed.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Warning in closing SQL_SCRIPT : at D:\11gsoftware\product\11.1.0\db_1/sysman/admin/scripts/db/dbclone/db_clone.pl line 350.
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 21:02:25 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> Connected.
SQL> ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Warning in closing SQL_SCRIPT : at D:\11gsoftware\product\11.1.0\db_1/sysman/admin/scripts/db/dbclone/db_clone.pl line 350.
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 21:02:30 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> Connected to an idle instance.
SQL> ORACLE instance shut down.
SQL>
File created.
SQL> ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 167775332 bytes
Database Buffers 360710144 bytes
Redo Buffers 5828608 bytes
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Warning in closing SQL_SCRIPT : at D:\11gsoftware\product\11.1.0\db_1/sysman/admin/scripts/db/dbclone/db_clone.pl line 350.
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Apr 12 21:02:39 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> Connected.
SQL> SQL> ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 167775332 bytes
Database Buffers 360710144 bytes
Redo Buffers 5828608 bytes
Database mounted.
SQL>
Database altered.
SQL>
STATUS
------------
OPEN
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Great Article !!
ReplyDeleteRegards,
Nikunj Dhawan