Duplicating a Database with RMAN

Operating Environment Windows XP Prof. SP-2
Oracle Release / Version 10.1.0.2.0 Enterprise Edition
ORACLE_BASE d:\oracle
ORACLE_HOME d:\oracle\product\10.1.0
Target Database SID ORA101
Duplicate Database SID TESTDB
RMAN Catalog Database No recovery catalog. Using control file.
Archive Log Mode Enabled
-----------------------------------------------------------------------------------------------
1.Create password File for duplicate database

C:\>orapwd file=d:\oracle\product\10.1.0\db_2\database\pwdtestdb.ora password=oracle entries=5 force=y

----------------------------------------------------------------
2.Create Init.ora file for duplicate database. copy init.ora file from target database (ora101) and edit.

SQL> create pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ora' from spfile;
File created.

#Minimum Changes in init.ora file
db_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product\10.1.0\oradata\TESTDB')
log_file_name_convert = ('d:\oracle\product\10.1.0\oradata\ORA101','d:\oracle\product\10.1.0\oradata\TESTDB')
background_dump_dest='D:\oracle\product\10.1.0\admin\testdb\bdump'
control_files='D:\oracle\product\10.1.0\oradata\testdb\control01.ctl','D:\oracle\product\10.1.0\oradata\testdb\control02.ctl','D:\oracle\product\10.1.0\oradata\testdb\control03.ctl'
core_dump_dest='D:\oracle\product\10.1.0\admin\testdb\cdump'
db_name='testdb'
dispatchers='(PROTOCOL=TCP)(SERVICE=testdbXDB)'
user_dump_dest='D:\oracle\product\10.1.0\admin\testdb\udump'

---------------------------------------------------------------------------------------------
3.Create / Start the Auxiliary Instance (Duplicate Database)

Create all required directory.
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\bdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\cdump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\udump
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\pfile
C:\>mkdir d:\oracle\product\10.1.0\admin\TESTDB\scripts
C:\>mkdir d:\oracle\product\10.1.0\oradata\TESTDB

C:\>ORADIM -NEW -SID TESTDB
Instance created.
C:\>set ORACLE_SID=TESTDB
C:\>SQLPLUS "/ AS SYSDBA"
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 12:15:14 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='d:\oracle\product\10.1.0\db_2\database\initTESTDB.ORA';
File created.
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
SQL>

----------------------------------------------------------------
4.Mount or Open Target Database.(ora101)

C:\>sqlplus sys/oracle@ora101 as sysdba
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 16:33:33 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select status from v$instance;
STATUS
------------
OPEN

-------------------------------------------------
5.Make sure you have valid Target Database backup and Archive redo logs.

C:\>rman target sys@ora101
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
target database Password:connected to target database: ORA101 (DBID=5128390)
RMAN> configure controlfile autobackup on;
using target database controlfile instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> run
2> {
3> backup database;
4> backup archivelog all;
5> }
Starting backup at 25-DEC-06
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=131 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
Finished backup at 25-DEC-06
Starting backup at 25-DEC-06
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=1 stamp=610113930
input archive log thread=1 sequence=8 recid=2 stamp=610117104
input archive log thread=1 sequence=9 recid=3 stamp=610120818
input archive log thread=1 sequence=10 recid=4 stamp=610124804
input archive log thread=1 sequence=11 recid=5 stamp=610129547
input archive log thread=1 sequence=12 recid=6 stamp=610130542
channel ORA_DISK_1: starting piece 1 at 25-DEC-06
channel ORA_DISK_1: finished piece 1 at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_ANNNN_TAG20061225T164224_2RZKSLN0_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
Finished backup at 25-DEC-06
Starting Control File and SPFILE Autobackup at 25-DEC-06
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\AUTOBACKUP\2006_12_25\O1_MF_S_610130562_2RZKT33X_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 25-DEC-06
RMAN>

--------------------------------------------------------------------------
6.Configure TNSNAMES.ORA file for duplicate database.

#TNSNAMES.ORA (ORACLEHOME/NETWORK/ADMIN
TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = testdb)
)
)
#LISTENER.ORA ( ORACLEHOME/NETWORK/ADMIN
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=d:\oracle\product\10.1.0\db_2)
(SID_NAME=testdb)
)

cmd>lsnrctl reload

-------------------------------------------------------------------------
7.Connect to RMAN. (Duplicate Database with Different directory structure.)

C:\>rman TARGET=sys/oracle@ora101 AUXILIARY=sys/oracle@testdb
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to target database: ORA101 (DBID=5128390)
connected to auxiliary database: testdb (not mounted)

RMAN>
run
2> {
3> allocate auxiliary channel c1 device type DISK;
4> allocate auxiliary channel c2 device type DISK;
5> allocate auxiliary channel c3 device type DISK;
6> DUPLICATE target database to TESTDB;
7>
}
allocated channel: c1channel c1: sid=160 devtype=DISK
allocated channel: c2channel c2: sid=159 devtype=DISK
allocated channel: c3channel c3: sid=158 devtype=DISK
Starting Duplicate Db at 25-DEC-06
contents of Memory Script:
{
set until scn 380106;
set newname for datafile 1 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
set newname for datafile 3 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
set newname for datafile 4 to
"D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
set newname for datafile 5 to "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.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 25-DEC-06
channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
channel c1: restored backup piece 1
piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP tag=TAG20061225T164042channel c1: restore complete
Finished restore at 25-DEC-06
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
switch clone datafile all
;
}executing Memory Script
datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=610136064 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
set until scn 380106;
recover
clone database
delete archivelog ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 25-DEC-06
starting media recovery
archive log thread 1 sequence 12 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARCarchive log thread 1 sequence 13 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_13_2RZPF26C_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_12_2RZKSFPG_.ARC thread=1 sequence=12archive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\ARCHIVELOG\2006_12_25\O1_MF_1_13_2RZPF26C_.ARC thread=1 sequence=13
media recovery complete
Finished recover at 25-DEC-06
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 180355072 bytes
Fixed Size 788028 bytes
Variable Size 145488324 bytes
Database Buffers 33554432 bytes
Redo Buffers 524288 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO01.LOG' ) SIZE 10 M REUSE,
GROUP 2 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO02.LOG' ) SIZE 10 M REUSE,
GROUP 3 ( 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\REDO03.LOG' ) SIZE 10 M REUSE
DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF";
catalog clone datafilecopy "D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF recid=1 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF recid=2 stamp=610136100
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF recid=3 stamp=610136101
cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF recid=4 stamp=610136102
datafile 2 switched to datafile copyinput datafilecopy recid=1 stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=2 stamp=610136100 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=3 stamp=610136101 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBFdatafile 5 switched to datafile copyinput datafilecopy recid=4 stamp=610136102 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 25-DEC-06
RMAN>

----------------------------------------------------------
8.Create Tempfile for Temporary Tbs.

C:\>SQLPLUS SYS/ORACLE@TESTDB AS SYSDBA
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Dec 25 18:25:16 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
ProductionWith the Partitioning, OLAP and Data Mining options
SQL> alter tablespace temp add tempfile
2 'd:\oracle\product\10.1.0\oradata\TESTDB\temp01.dbf' size 10m;
Tablespace altered.

---------------------------------------
Hope this helps
Taj