Database Cloning

RMAN Cloning using active database feature - Oracle 11g feature

Normally we well knew about RMAN cloning.

Oracle9i/10g using duplicate cloning command, we do the RMAN cloning.
We need a RMAN full backup for cloning the database in oracle9i/10g.
In oracle 11g provides a new feature, without RMAN database backup we can clone the database.
I have tested the active database RMAN cloning in my test server.
Environment Details:
Operating system: Windows XP service pack 2
Database Version: 11.1.0.7 (32 bit)
Source database name: troy
Clone database name: clonedb

How its work?

In an active duplication process, target database online image copies and archived redo log files were copied through the auxiliary instance service name. So we no need the target database backup.
Target database must be in archive log mode.

Database duplication process RMAN does the following things
1.Generate the unique DBID for auxiliary database.
2.Copy the data files & archived log files from target database to auxiliary database.
3.Recreate the new control files for auxiliary database.
4.Recreates the online redo log files.
5.Restart the auxiliary instance.
6.Open the database with RESETLOGS.

ACTIVE Database RMAN cloning in same server.

1.Configure the network files.
Listener configuration:
SID_LIST_LISTENER11G=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=troy)
(SID_NAME=troy)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
(SID_DESC=
(GLOBAL_DBNAME=clonedb)
(SID_NAME=clonedb)
(ORACLE_HOME=D:\Oracle\app\product\11.1.0\db_1)
)
)
)
TNS configuration:
TROY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = troy)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = troy)
)
)
CLONEDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = troy)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = clonedb)
)
)

2.Create the instance using oradim utility ( applicable for windows platform)
C:\>oradim -new -sid clonedb
Instance created.

3.Create a password file for clonedb database using orapwd utility
C:\>orapwd file=D:\Oracle\app\product\11.1.0\db_1\dbs\orapwCLONEDB password=clonedbadmin entries=20

4.Create the init. ora file for clonedb database.
Troy database: We create the pfile for clonedb from troy database & edit the parameter.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\ORACLE\APP\PRODUCT\11.1.0\D
B_1\DATABASE\SPFILETROY.ORA
SQL> create pfile='D:\Oracle\app\product\admin\clonedb\pfile\initclonedb.ora'
from spfile;
File created.
Init parameter change “clonedb” instead of troy & use to start the Clonedb.
clonedb.__db_cache_size=130023424
clonedb.__java_pool_size=12582912
clonedb.__large_pool_size=4194304
clonedb.__oracle_base='D:\Oracle\app\product'#ORACLE_BASE set from environment
clonedb.__pga_aggregate_target=192937984
clonedb.__sga_target=348127232
clonedb.__shared_io_pool_size=0
clonedb.__shared_pool_size=184549376
clonedb.__streams_pool_size=8388608
*.audit_file_dest='D:\Oracle\app\product\admin\clonedb\adump'
*.audit_trail='db'
*.compatible='11.1.0.0.0'
*.control_files='D:\Oracle\app\product\oradata\clonedb\control01.ctl','D:\Oracle\app\product\oradata\clonedb\control02.ctl','D:\Oracle\app\product\oradata\clonedb\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='clonedb'
*.diagnostic_dest='D:\Oracle\app\product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_archive_dest='D:\Oracle\app\product\archive\clonedb'
*.log_archive_start=TRUE
*.memory_target=538968064
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.skip_unusable_indexes=TRUE
*.undo_tablespace='UNDOTBS1'
db_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\TROY','D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB')
log_file_name_convert = ('D:\ORACLE\APP\PRODUCT\ORADATA\TROY','D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB')
5.Create the appropriate folder for clonedb database (admin/oradata folders etc..)
6.Startup the clone database in mount stage
C:\>set oracle_sid=clonedb
C:\>sqlplus

SQL*Plus: Release 11.1.0.7.0 - Production on Wed Apr 28 14:21:08 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 535662592 bytes
Fixed Size 1348508 bytes
Variable Size 398462052 bytes
Database Buffers 130023424 bytes
Redo Buffers 5828608 bytes

7.Connect the target & auxiliary database using RMAN
C:\>rman target sys/troyadmin@troy
Recovery Manager: Release 11.1.0.7.0 - Production on Wed Apr 28 14:26:18 2010
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TROY (DBID=3876493159)

RMAN> connect auxiliary sys/clonedbadmin@clonedb
connected to auxiliary database: CLONEDB (not mounted)

RMAN> duplicate target database to clonedb from active database nofilenamecheck;
Starting Duplicate Db at 28-APR-10
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=152 device type=DISK
contents of Memory Script:
{
set newname for datafile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF";
set newname for datafile 2 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF";
set newname for datafile 3 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF";
set newname for datafile 4 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF";
set newname for datafile 5 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF";
set newname for datafile 6 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF";
backup as copy reuse
datafile 1 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF" datafile
2 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF" datafile
3 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF" datafile
4 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF" datafile
5 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF" datafile
6 auxiliary format
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.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
executing command: SET NEWNAME
Starting backup at 28-APR-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\SYSTEM01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF tag=TAG20100428T142723 RECID=0 S
TAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\SYSAUX01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF tag=TAG20100428T142723 RECID=0 S
TAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\EXAMPLE01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF tag=TAG20100428T142723 RECID=0
STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\MANI01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF tag=TAG20100428T142723 RECID=0 STA
MP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\UNDOTBS01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF tag=TAG20100428T142723 RECID=0
STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\ORACLE\APP\PRODUCT\ORADATA\TROY\USERS01.DBF
output file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF tag=TAG20100428T142723 RECID=0 ST
AMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 28-APR-10
sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
backup as copy reuse
archivelog like "D:\ORACLE\APP\PRODUCT\ARCHIVE\TROY\ARC00025_0715865130.001" auxiliary format
"D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001" ;
catalog clone archivelog "D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001";
switch clone datafile all;
}
executing Memory Script
Starting backup at 28-APR-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=25 RECID=9 STAMP=717517736
output file name=D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 28-APR-10
cataloged archived log
archived log file name=D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001 RECID=1 STAMP=7
17517739
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=717517739 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01
.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=717517739 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS0
1.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=717517739 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.
DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=717517740 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE0
1.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=717517740 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.D
BF
contents of Memory Script:
{
set until scn 1422661;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 28-APR-10
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 25 is already on disk as file D:\ORACLE\APP\PRODUCT\ARCHIVE\
CLONEDB\ARC00025_0715865130.001
archived log file name=D:\ORACLE\APP\PRODUCT\ARCHIVE\CLONEDB\ARC00025_0715865130.001 thread=1 sequen
ce=25
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-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 398462052 bytes
Database Buffers 130023424 bytes
Redo Buffers 5828608 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO01.LOG' ) SIZE 50 M REUSE,
GROUP 2 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO02.LOG' ) SIZE 50 M REUSE,
GROUP 3 ( 'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\REDO03.LOG' ) SIZE 50 M REUSE
DATAFILE
'D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSTEM01.DBF'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP01.DBF";
set newname for tempfile 2 to
"D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP02.DBF";
switch clone tempfile all;
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF";
catalog clone datafilecopy "D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP01.DBF in control file
renamed tempfile 2 to D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\TEMP02.DBF in control file
cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01.DBF RECID=1 STAMP=717517768
cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS01.DBF RECID=2 STAMP=717517768
cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.DBF RECID=3 STAMP=717517769
cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE01.DBF RECID=4 STAMP=717517769
cataloged datafile copy
datafile copy file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.DBF RECID=5 STAMP=717517769
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=717517768 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\SYSAUX01
.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=717517768 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\UNDOTBS0
1.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=717517769 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\USERS01.
DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=717517769 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\EXAMPLE0
1.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=717517769 file name=D:\ORACLE\APP\PRODUCT\ORADATA\CLONEDB\MANI01.D
BF
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 28-APR-10

8.Verify the clonedb database.

SQL> select name,open_mode from v$database;
NAME OPEN_MODE
--------- ----------
CLONEDB READ WRITE
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\Oracle\app\product\archive\clonedb
Oldest online log sequence 1
Next log sequence to archive 1
Current log sequence 1
Active database duplication is better way to clone the database, since don’t you have any prior backup.