Oracle Enterprise Manager 11g : Step by Step clone using 11g DB console.

11g OEM (I tested with DB console ) has made the cloning very easy. You can achieve this in only 6 steps.

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

 Make sure that logged in as 'Sys as sysdba' otherwise you will get error. as shown in the picture here.



 
Oracle Enterprise Manager 11g : 1

Once you are logged in as SYS, You will prompted for Source host credentials.

    Oracle Enterprise Manager 11g :2

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.

    Oracle Enterprise Manager 11g : 3

Here you need to specify the OFA or Database area and Fast (Flash)recovery area

   Oracle Enterprise Manager 11g :4

I have opted for not installing the enterprise manager for the new database

   Oracle Enterprise Manager 11g : 5

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.

    Oracle Enterprise Manager 11g :6

Here you can review and submit the JOB.

    Oracle Enterprise Manager 11g :7

Once submited you can monitor the job.
 Oracle Enterprise Manager 11g : 8.

    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

1 comment:

  1. Great Article !!

    Regards,
    Nikunj Dhawan

    ReplyDelete