Duplicate DataBase Using Rman

Operating Environment Windows 2003 Server- 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

1.Catalog Database No recovery catalog. Using control file. Archive Log Mode Enabled1.

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\bdumpC:\>mkdir

d:\oracle\product\10.1.0\admin\TESTDB\cdumpC:\>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=TESTDBC:\>SQLPLUS "/ AS SYSDBA"SQL*Plus:

Release 10.1.0.2.0 - Production on Mon Dec 25 12:15:14 2006Copyright (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
bytesFixed Size 788028
bytesVariable Size 145488324 bytesDatabase
Buffers 33554432 bytesRedo

Buffers 524288 bytesSQL

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 2006Copyright (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> 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 - ProductionCopyright (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 catalognew 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-06allocated channel:

ORA_DISK_1channel ORA_DISK_1: sid=131 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORA101\USERS01.DBFchannel ORA_DISK_1: starting piece 1 at 25-DEC-06channel

ORA_DISK_1: finished piece 1 at 25-DEC-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_NNNDF_TAG20061225T164042_2RZKPF0Z_.BKP comment=NONEchannel ORA_DISK_1:

backup set complete, elapsed time: 00:01:36Finished backup at 25-DEC-06Starting backup at 25-DEC-06current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel

ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=7 recid=1 stamp=610113930input archive log thread=1 sequence=8 recid=2 stamp=610117104input archive log thread=1 sequence=9 recid=3 stamp=610120818input archive log thread=1 sequence=10 recid=4 stamp=610124804input archive log thread=1 sequence=11 recid=5 stamp=610129547input archive log thread=1 sequence=12 recid=6 stamp=610130542channel ORA_DISK_1: starting piece 1 at 25-DEC-06channel

ORA_DISK_1: finished piece 1 at 25-DEC-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\BACKUPSET\2006_12_25\O1_MF_ANNNN_TAG20061225T164224_2RZKSLN0_.BKP

comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:17Finished backup at 25-DEC-06Starting Control File and SPFILE Autobackup at 25-DEC-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORA101\AUTOBACKUP\2006_12_25\O1_MF_S_610130562_2RZKT33X_.BKP comment=NONEFinished Control File and SPFILE Autobackup at 25-DEC-

6.Configure TNSNAMES.ORA file for duplicate database.


#TNSNAMES.ORA

(ORACLEHOME/NETWORK/ADMINTESTDB =

(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 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.connected to target database: ORA101 (DBID=5128390)connected to auxiliary database: testdb (not mounted)

RMAN>

run2> {
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=DISKallocated channel:
c2channel
c2: sid=159 devtype=DISKallocated channel:
c3channel c3: sid=158 devtype=DISKStarting Duplicate Db at 25-DEC-06contents 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";restorecheck readonlyclone database;}
executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 25-DEC-06channel c1: starting datafile backupset restorechannel c1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBFchannel c1: restored backup piece 1piece 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 completeFinished restore at 25-DEC-06sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 454LOGFILEGROUP 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 REUSEDATAFILE'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'CHARACTER SET WE8MSWIN1252contents of Memory Script:{switch clone datafile all;}executing Memory Scriptdatafile 2 switched to datafile copyinput datafilecopy recid=1 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=2 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=3 stamp=610136063 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBFdatafile 5 switched to datafile copyinput datafilecopy recid=4 stamp=610136064 filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBFcontents of Memory Script:{set until scn 380106;recoverclone databasedelete archivelog ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 25-DEC-06starting media recoveryarchive 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=13media recovery completeFinished recover at 25-DEC-06contents of Memory Script:{shutdown clone;startup clone nomount ;}executing Memory Scriptdatabase dismountedOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 180355072 bytesFixed Size 788028 bytesVariable Size 145488324 bytesDatabase Buffers 33554432 bytesRedo Buffers 524288 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB"
RESETLOGS ARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 454
LOGFILEGROUP 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 REUSEDATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSTEM01.DBF'CHARACTER SET WE8MSWIN1252contents 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 Scriptcataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\UNDOTBS01.DBF recid=1 stamp=610136100cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\SYSAUX01.DBF recid=2 stamp=610136100cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\USERS01.DBF recid=3 stamp=610136101cataloged datafile copydatafile copy filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\TESTDB\EXAMPLE01.DBF recid=4 stamp=610136102datafile 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.DBFcontents of Memory Script:{Alter clone database open resetlogs;}
executing Memory Scriptdatabase 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 2006Copyright (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 tempfile2 'd:\oracle\product\10.1.0\oradata\TESTDB\temp01.dbf' size 10m;

Tablespace altered

Babu B