Oracle database 11g provides new feature of online cloning.You don't need to take the backup from the production database while cloning your target database.You need to bring source database in archivelog mode if you want to keep it open.I am doing it on windows vista operating system.My source database is orcl and target database is sp.
step1:
create the oracle service first.
oradim -new -sid sp -startmode manual
step2:
Create the necessary directories
C:\app\Shashank\oradata\spC:\app\Shashank\diag\rdbms\sp\spC:\app\Shashank\admin\sp\adumpC:\app\Shashank\admin\sp\dpdumpC:\app\Shashank\admin\sp\pfile
C:\app\Shashank\flash_recovery_area\sp
Step3:
Create the the password file:
create the password file as below
orapwd file=PWDsp password=oracle
step 4:
create the pfile of the target database
My pfile looks like.
Notice the db_file_name_convert and log_file_name_convert
sp.__db_cache_size=310378496
sp.__java_pool_size=12582912
sp.__large_pool_size=4194304
sp.__oracle_base='C:\app\Shashank'#ORACLE_BASE set from environment
sp.__pga_aggregate_target=322961408
sp.__sga_target=536870912
sp.__shared_io_pool_size=0
sp.__shared_pool_size=201326592
sp.__streams_pool_size=0
*.audit_file_dest='C:\app\Shashank\admin\sp\adump'
*.audit_trail='db'*.compatible='11.1.0.0.0'
*.control_files='C:\app\Shashank\oradata\sp\control01.ctl','C:\app\Shashank\oradata\sp\control02.ctl','C:\app\Shashank\oradata\sp\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='sp'
*.db_recovery_file_dest='C:\app\Shashank\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.diagnostic_dest='C:\app\Shashank'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=spXDB)'
*.memory_target=857735168
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_FILE_NAME_CONVERT=('C:\app\Shashank\oradata\orcl','C:\app\Shashank\oradata\sp')Log_FILE_NAME_CONVERT=('C:\app\Shashank\oradata\orcl','C:\app\Shashank\oradata\sp')
step 5:
Add the SID to the Listener.ora file as below
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = C:\app\Shashank\product\11.1.0\db_1) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = sp) (ORACLE_HOME = C:\app\Shashank\product\11.1.0\db_1) (SID_NAME = sp) ) )
step 7)
lsnrctl reload
step 8)
I am bringing the source database in archivelog mode as I am intending to keep it open.
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.
Total System Global Area 535662592 bytesFixed Size 1348508 bytesVariable Size 226495588 bytesDatabase Buffers 301989888 bytesRedo Buffers 5828608 bytesDatabase mounted.SQL> alter database archivelog;
Database altered.
SQL> alter database open;
step9) And finally we are ready to see the cloning
C:\app\Shashank\product\11.1.0\db_1\database>rman target=sys/oracle@orcl auxiliary=sys/oracle@sp
duplicate target database to sp from active database;
C:\app\Shashank\product\11.1.0\db_1\database>rman target=sys/oracle@orcl auxiliary=sys/oracle@sp
Recovery Manager: Release 11.1.0.7.0 - Production on Mon Jul 20 23:20:53 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1219463033)connected to auxiliary database: SP (not mounted)
RMAN> duplicate target database to sp from active database;
Starting Duplicate Db at 20-JUL-09using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=170 device type=DISK
contents of Memory Script:{ set newname for datafile 1 to "C:\APP\SHASHANK\ORADATA\SP\SYSTEM01.DBF"; set newname for datafile 2 to "C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBF"; set newname for datafile 3 to "C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBF"; set newname for datafile 4 to "C:\APP\SHASHANK\ORADATA\SP\USERS01.DBF"; set newname for datafile 5 to "C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF"; backup as copy reuse datafile 1 auxiliary format "C:\APP\SHASHANK\ORADATA\SP\SYSTEM01.DBF" datafile 2 auxiliary format "C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBF" datafile 3 auxiliary format "C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBF" datafile 4 auxiliary format "C:\APP\SHASHANK\ORADATA\SP\USERS01.DBF" datafile 5 auxiliary format "C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF" ; 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 20-JUL-09allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=154 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\SHASHANK\ORADATA\ORCL\SYSTEM01.DBFoutput file name=C:\APP\SHASHANK\ORADATA\SP\SYSTEM01.DBF tag=TAG20090720T232106RECID=0 STAMP=0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\SHASHANK\ORADATA\ORCL\SYSAUX01.DBFoutput file name=C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBF tag=TAG20090720T232106RECID=0 STAMP=0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=C:\APP\SHASHANK\ORADATA\ORCL\EXAMPLE01.DBF
output file name=C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF tag=TAG20090720T232106 RECID=0 STAMP=0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\SHASHANK\ORADATA\ORCL\UNDOTBS01.DBF
output file name=C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBF tag=TAG20090720T232106 RECID=0 STAMP=0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\SHASHANK\ORADATA\ORCL\USERS01.DBFoutput file name=C:\APP\SHASHANK\ORADATA\SP\USERS01.DBF tag=TAG20090720T232106 RECID=0 STAMP=0channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 20-JUL-09
sql statement: alter system archive log currentsql statement: CREATE CONTROLFILE REUSE SET DATABASE "SP" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( 'C:\APP\SHASHANK\ORADATA\SP\REDO01.LOG' ) SIZE 50 M REUSE, GROUP 2 ( 'C:\APP\SHASHANK\ORADATA\SP\REDO02.LOG' ) SIZE 50 M REUSE, GROUP 3 ( 'C:\APP\SHASHANK\ORADATA\SP\REDO03.LOG' ) SIZE 50 M REUSE DATAFILE 'C:\APP\SHASHANK\ORADATA\SP\SYSTEM01.DBF' CHARACTER SET WE8MSWIN1252
contents of Memory Script:{ backup as copy reuse archivelog like "C:\APP\SHASHANK\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_07_20\O1_MF_1_33_569CVL6T_.ARC" auxiliary format "C:\APP\SHASHANK\FLASH_RECOVERY_AREA\SP\ARCHIVELOG\2009_07_20\O1_MF_1_33_%U_.ARC" ; catalog clone recovery area; switch clone datafile all;}executing Memory Script
Starting backup at 20-JUL-09using channel ORA_DISK_1channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=33 RECID=2 STAMP=692753098output file name=C:\APP\SHASHANK\FLASH_RECOVERY_AREA\SP\ARCHIVELOG\2009_07_20\O1_MF_1_33_ARCH_D-ORCL_ID-1219463033_S-33_T-1_A-691682301_06KKL4MF_.ARC RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01Finished backup at 20-JUL-09
searching for all files in the recovery area
List of Files Unknown to the Database=====================================File Name: C:\APP\SHASHANK\FLASH_RECOVERY_AREA\SP\ARCHIVELOG\2009_07_20\O1_MF_1_33_ARCH_D-ORCL_ID-1219463033_S-33_T-1_A-691682301_06KKL4MF_.ARCcataloging files...cataloging done
List of Cataloged Files=======================File Name: C:\APP\SHASHANK\FLASH_RECOVERY_AREA\SP\ARCHIVELOG\2009_07_20\O1_MF_1_33_ARCH_D-ORCL_ID-1219463033_S-33_T-1_A-691682301_06KKL4MF_.ARC
datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=692753106 file name=C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBFdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=692753107 file name=C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBFdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=692753108 file name=C:\APP\SHASHANK\ORADATA\SP\USERS01.DBFdatafile 5 switched to datafile copyinput datafile copy RECID=4 STAMP=692753108 file name=C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF
contents of Memory Script:{ set until scn 1749738; recover clone database delete archivelog ;}executing Memory Script
executing command: SET until clause
Starting recover at 20-JUL-09using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 33 is already on disk as file C:\APP\SHASHANK\FLASH_RECOVERY_AREA\SP\ARCHIVELOG\2009_07_20\O1_MF_1_33_ARCH_D-ORCL_ID-1219463033_S-33_T-1_A-691682301_06KKL4MF_.ARCarchived log file name=C:\APP\SHASHANK\FLASH_RECOVERY_AREA\SP\ARCHIVELOG\2009_07_20\O1_MF_1_33_ARCH_D-ORCL_ID-1219463033_S-33_T-1_A-691682301_06KKL4MF_.ARC thread=1 sequence=33media recovery complete, elapsed time: 00:00:03Finished recover at 20-JUL-09
contents of Memory Script:{ shutdown clone immediate; startup clone nomount ;}executing Memory Script
database dismountedOracle instance shut down
connected to auxiliary database (not started)Oracle instance started
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytesVariable Size 218106980 bytesDatabase Buffers 310378496 bytesRedo Buffers 5828608 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "SP" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( 'C:\APP\SHASHANK\ORADATA\SP\REDO01.LOG' ) SIZE 50 M REUSE, GROUP 2 ( 'C:\APP\SHASHANK\ORADATA\SP\REDO02.LOG' ) SIZE 50 M REUSE, GROUP 3 ( 'C:\APP\SHASHANK\ORADATA\SP\REDO03.LOG' ) SIZE 50 M REUSE DATAFILE 'C:\APP\SHASHANK\ORADATA\SP\SYSTEM01.DBF' CHARACTER SET WE8MSWIN1252
contents of Memory Script:{ set newname for tempfile 1 to "C:\APP\SHASHANK\ORADATA\SP\TEMP01.DBF"; switch clone tempfile all; catalog clone datafilecopy "C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBF"; catalog clone datafilecopy "C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBF"; catalog clone datafilecopy "C:\APP\SHASHANK\ORADATA\SP\USERS01.DBF"; catalog clone datafilecopy "C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF"; switch clone datafile all;}executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to C:\APP\SHASHANK\ORADATA\SP\TEMP01.DBF in control file
cataloged datafile copydatafile copy file name=C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBF RECID=1 STAMP=692753140
cataloged datafile copydatafile copy file name=C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBF RECID=2 STAMP=692753140
cataloged datafile copydatafile copy file name=C:\APP\SHASHANK\ORADATA\SP\USERS01.DBF RECID=3 STAMP=692753141
cataloged datafile copydatafile copy file name=C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF RECID=4 STAMP=692753142
datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=692753140 file name=C:\APP\SHASHANK\ORADATA\SP\SYSAUX01.DBFdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=692753140 file name=C:\APP\SHASHANK\ORADATA\SP\UNDOTBS01.DBFdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=692753141 file name=C:\APP\SHASHANK\ORADATA\SP\USERS01.DBFdatafile 5 switched to datafile copyinput datafile copy RECID=4 STAMP=692753142 file name=C:\APP\SHASHANK\ORADATA\SP\EXAMPLE01.DBF
contents of Memory Script:{ Alter clone database open resetlogs;}executing Memory Script
database openedFinished Duplicate Db at 20-JUL-09
RMAN>
