Duplicate Database with RMAN is as easy as it should be

Duplicating a database with RMAN saves you from an ordinary routine consists of a hot backup, restore, recover and open the aux database procedure.

C:\Users\dtepeker> set ORACLE_SID=ORCL2
C:\Users\dtepeker> sqlplus "/ as sysdba

SQL> shutdown abort;
ORACLE instance shut down.
SQL>
SQL>
SQL> create pfile='C:\oracle\product\initORCL2.ora' from spfile;

File created.

SQL>

C:\Users\dtepeker> more C:\oracle\product\initORCL2.ora
orcl2.__db_cache_size=369098752
orcl2.__java_pool_size=4194304
orcl2.__large_pool_size=4194304
orcl2.__shared_pool_size=142606336
orcl2.__streams_pool_size=8388608
*.audit_file_dest='C:\oracle\product\admin\ORCL2\adump'
*.background_dump_dest='C:\oracle\product\admin\ORCL2\bdump'
*.compatible='10.2.0.3.0'
*.control_files='C:\oracle\product\oradata\ORCL2\control01.ctl'
*.core_dump_dest='C:\oracle\product\admin\ORCL2\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='ORCL2'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL2XDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=268435456
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=536870912
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\admin\ORCL2\udump'

*.db_file_name_convert=(C:\oracle\product\oradata\ORCL,C:\oracle\product\oradata\ORCL2)
*.log_file_name_convert=(C:\oracle\product\oradata\ORCL,C:\oracle\product\oradata\ORCL2)

SQL> create spfile from pfile='C:\oracle\product\initORCL2.ora';

File created.

SQL>

C:\Users\dtepeker> more C:\oracle\product\db10g\network\admin\tnsnames.ora
# tnsnames.ora Network Configuration File: C:\oracle\product\db10g\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

### duplicate/destination db
ORCL2 =
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dtl1optim1)(PORT = 1521))
  )
  (CONNECT_DATA =
    (SERVICE_NAME = ORCL2)
  )
)
### target/source db
ORCL =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = dtl1optim1)(PORT = 1521))
  (CONNECT_DATA =
    (SID = ORCL)
  )
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
  (CONNECT_DATA =
    (PRESENTATION = RO)
    (SID = PLSExtProc)
  )
)

C:\oracle\product\oradata\ORCL2>del *.dbf

C:\oracle\product\oradata\ORCL2>dir
Volume in drive C has no label.
Volume Serial Number is 3A6A-99E9

Directory of C:\oracle\product\oradata\ORCL2

05/31/2010  12:06 PM              .
05/31/2010  12:06 PM              ..
05/31/2010  11:58 AM         7,389,184 CONTROL01.CTL
05/27/2010  04:58 PM             1,317 CONTROLFILE.CTL
05/27/2010  04:52 PM               854 initORCL2.ora
04/20/2010  03:27 PM              ORCL2
05/31/2010  11:54 AM        52,429,312 REDO01.LOG
05/31/2010  11:54 AM        52,429,312 REDO02.LOG
05/31/2010  11:54 AM        52,429,312 REDO03.LOG
             6 File(s)    164,679,291 bytes
             3 Dir(s)  92,947,480,576 bytes free

C:\oracle\product\oradata\ORCL2>del *.log

C:\oracle\product\oradata\ORCL2>del *.ctl

C:\oracle\product\oradata\ORCL2>dir
Volume in drive C has no label.
Volume Serial Number is 3A6A-99E9

Directory of C:\oracle\product\oradata\ORCL2

05/31/2010  12:06 PM              .
05/31/2010  12:06 PM              ..
05/27/2010  04:52 PM               854 initORCL2.ora
04/20/2010  03:27 PM              ORCL2
             1 File(s)            854 bytes
             3 Dir(s)  93,112,172,544 bytes free

C:\oracle\product\oradata\ORCL2>

C:\oracle\product\oradata\ORCL2>rman target sys/password@ORCL auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon May 31 12:07:32 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: ORCL (DBID=1235781100)
connected to auxiliary database: ORCL2 (not mounted)

RMAN> duplicate target database to ORCL2;

Starting Duplicate Db at 31-MAY-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:
{
set until scn 23097370;
set newname for datafile 1 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF";
set newname for datafile 2 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF";
set newname for datafile 3 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF";
set newname for datafile 4 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF";
set newname for datafile 5 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF";
restore
check readonly
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 31-MAY-10
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: restoring datafile 00001
input datafile copy recid=20 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_SYSTEM_5ZWW6PW1_.DBF
destination for restore of datafile 00001: C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00001
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00002
input datafile copy recid=21 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_UNDOTBS1_5ZWW4TN9_.DBF
destination for restore of datafile 00002: C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00002
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00003
input datafile copy recid=22 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_SYSAUX_5ZWW5XQX_.DBF
destination for restore of datafile 00003: C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00003
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00004
input datafile copy recid=19 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_USERS_5ZWW7937_.DBF

destination for restore of datafile 00004: C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00004
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
channel ORA_AUX_DISK_1: restoring datafile 00005
input datafile copy recid=18 stamp=720116353 filename=C:\ORACLE\PRODUCT\FRA\ORCL\ORCL\DATAFILE\O1_MF_TS_STREA_5ZWW763V_.DBF
destination for restore of datafile 00005: C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF
channel ORA_AUX_DISK_1: copied datafile copy of datafile 00005
output filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF
Finished restore at 31-MAY-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET WE8ISO8859P9


contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

released channel: ORA_AUX_DISK_1
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=720446949 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=720446950 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=720446950 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=720446950 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF

contents of Memory Script:
{
set until scn 23097370;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 31-MAY-10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

starting media recovery

archive log thread 1 sequence 416 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00416_0708000366.001
archive log thread 1 sequence 417 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00417_0708000366.001
archive log thread 1 sequence 418 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00418_0708000366.001
archive log thread 1 sequence 419 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00419_0708000366.001
archive log thread 1 sequence 420 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00420_0708000366.001
archive log thread 1 sequence 421 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00421_0708000366.001
archive log thread 1 sequence 422 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00422_0708000366.001
archive log thread 1 sequence 423 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00423_0708000366.001
archive log thread 1 sequence 424 is already on disk as file C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00424_0708000366.001
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00416_0708000366.001 thread=1 sequence=416
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00417_0708000366.001 thread=1 sequence=417
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00418_0708000366.001 thread=1 sequence=418
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00419_0708000366.001 thread=1 sequence=419
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00420_0708000366.001 thread=1 sequence=420
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00421_0708000366.001 thread=1 sequence=421
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00422_0708000366.001 thread=1 sequence=422
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00423_0708000366.001 thread=1 sequence=423
archive log filename=C:\ORACLE\PRODUCT\ORADATA\ORCL\ARCHIVE\ARC00424_0708000366.001 thread=1 sequence=424
media recovery complete, elapsed time: 00:00:29
Finished recover at 31-MAY-10

contents of Memory Script:
{
shutdown clone;
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 536870912 bytes

Fixed Size 1297724 bytes
Variable Size 159384260 bytes
Database Buffers 369098752 bytes
Redo Buffers 7090176 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL2\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSTEM01.DBF'
CHARACTER SET WE8ISO8859P9


contents of Memory Script:
{
set newname for tempfile 1 to
"C:\ORACLE\PRODUCT\ORADATA\ORCL2\TEMP01.DBF";
switch clone tempfile all;
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF";
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF";
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF";
catalog clone datafilecopy "C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF";
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to C:\ORACLE\PRODUCT\ORADATA\ORCL2\TEMP01.DBF in control file

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF recid=1 stamp=720446995

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF recid=2 stamp=720446995

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF recid=3 stamp=720446995

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF recid=4 stamp=720446996

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=720446995 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=720446995 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=720446995 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=720446996 filename=C:\ORACLE\PRODUCT\ORADATA\ORCL2\TS_STREAMS_01.DBF

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 31-MAY-10

RMAN>


Related:
Oracle Database Backup and Recovery Advanced Users Guide
Oracle-Base.com