RMAN Active Duplication an Overview:
It is a new
feature in Oracle 11g where cloning is done from one database to another
database without any outage or downtime of the primary database.
Earlier we use
to clone the database using cold back or rman backup. But RMAN Active
duplication feature allows a database to be duplicated directly from its live
source database instead of using its backup.
RMAN directly
reads the data from the database using PRIMARY database CONTROLFILE.
Advantages:
1. No outage
or downtime required for the Primary Database.
2. No Backup
required for cloning.
3. It can be
performed in NoCatalog mode itself.
4. Instant
Database Clones possible (On Demand Cloning)
Disadvantages:
1. When
cloning there will be more I/O's in the Primary Database.
2. There will
be performance overheads in the Primary Database.
3. Incomplete recovery
NOT possible (no SET UNTIL) - Active Database Duplication cannot be used to duplicate a database until a point in time. One has to rely on the backup-based
database duplication method if one wants to create a duplicate database until a
specific point in time ( a specific time, log sequence or SCN). Furthermore,
active duplicate copies data only until the last archived log file in the
source database. The contents of the online redologs will not be copied to the
duplicate database.
Environment Details:
PRIMARY SERVER
|
RMDB
|
SERVER1
|
11gR2 ASM Grid and Oralce 11gR2 11.2.0.2.0
Binaries
|
SECONDARY SERVER
|
CLONEDB
|
SERVER2
|
11gR2 ASM Grid and Oralce 11gR2 11.2.0.2.0
Binaries
|
Step 1:
Copy the password
file from Target database to auxiliary database location and rename it.
scp orapwrmdb oracle@server2:/usr2/oracle/product/11.2.0.2/dbs/.
cd /usr2/oracle/product/11.2.0.2/dbs
mv orapwrmdb orapwclonedb
Step 2:
Create the pfile from target database for Auxilarydatabase( if target has spfile create pfile from spfile)Parameter File of Target Database:
*.audit_file_dest='/usr2/oracle/admin/rmdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA1/rmdb/controlfile/current.260.816249205'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_file_name_convert='+DATA1/rmdb/datafile','+DATA1/clonedb/datafile'
*.db_name='rmdb'
*.diagnostic_dest='/usr2/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=rmdbXDB)'
*.local_listener='LISTENER_RMDB'
*.log_archive_dest_1='location=/vol1/arch'
*.log_file_name_convert='+DATA1/rmdb/onlinelog','+DATA1/clonedb/onlinelog'
*.memory_target=1932525568
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
Edited Parameter File of Auxiliary Database:
*.audit_file_dest='/usr2/oracle/admin/clonedb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA1/clonedb/controlfile/current.257.816499055'#Set by
RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_file_name_convert='+DATA1/rmdb/datafile','+DATA1/clonedb/datafile'
*.db_name='CLONEDB'#Reset to original value by RMAN
*.diagnostic_dest='/usr2/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=clonedbXDB)'
*.log_archive_dest_1='location=/vol1/arch'
*.log_file_name_convert='+DATA1/rmdb/onlinelog','+DATA1/clonedb/onlinelog'
*.memory_target=1932525568
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'Note:
In Step 2, we have
added two parameters db_file_name_convert and log_file_name_convert.
This
parameter is required to match the location of the datafiles and logfiles. It
can be removed once after the cloning / duplication is completed.
Step 3:
Configure the
linster&tnsname on both target database and auxiliary database.
Egs:
Tnsnames entries:
RMDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = server1)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = rmdb)
)
)
CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL =
TCP)(HOST = server2)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = clonedb)
)
)
Step 4:
Create the required directory structure in Auxiliarydb
location (based on pfile).
Step 5:
Open the Auxiliary
database in Nomount state. Created spfile from pfile and again shutdown the
auxiliary database and brought up to Nomount state using spfile.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1937457152 bytes
Fixed Size 2220848
bytes
Variable Size 1124076752
bytes
Database Buffers 805306368
bytes
Redo Buffers 5853184
bytes
SQL>Step 6:
Go to the Target
Database server and enter in to the RMAN prompt. Issue the below command to
duplicate the Target database to auxiliary database.
rman
connect target sys/*******@rmdb
connect auxiliary sys/********@clonedb
duplicate target database to clonedb from active database
nofilenamecheck;
RMAN> connect target sys/*****@rmdb
connected to target database: RMDB (DBID=873495541)
RMAN> connect auxiliary sys/*****@clonedb
connected to auxiliary database: CLONEDB (not mounted)
RMAN> duplicate target database to clonedb from active database
nofilenamecheck;
Starting Duplicate Db at 27-MAY-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=189 device type=DISK
contents of Memory Script:
{
sql clone "alter system set
control_files =
''+DATA1/clonedb/controlfile/current.256.816499053'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set
db_name =
''RMDB'' comment=
''Modified by RMAN duplicate''
scope=spfile";
sql clone "alter system set
db_unique_name =
''CLONEDB'' comment=
''Modified by RMAN duplicate''
scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '+DATA1/clonedb/controlfile/current.257.816499055';
sql clone "alter system set
control_files =
''+DATA1/clonedb/controlfile/current.257.816499055'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set
control_files =
''+DATA1/clonedb/controlfile/current.256.816499053'' comment= ''Set by
RMAN'' scope=spfile
sql statement: alter system set
db_name = ''RMDB'' comment= ''Modified
by RMAN duplicate'' scope=spfile
sql statement: alter system set
db_unique_name = ''CLONEDB''
comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area
1937457152 bytes
Fixed Size
2220848 bytes
Variable Size
1107299536 bytes
Database Buffers
822083584 bytes
Redo Buffers
5853184 bytes
Starting backup at 27-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/usr2/oracle/product/11.2.0.2/dbs/snapcf_rmdb.f
tag=TAG20130527T051755 RECID=1 STAMP=816499076
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-MAY-13
sql statement: alter system set
control_files =
''+DATA1/clonedb/controlfile/current.257.816499055'' comment= ''Set by
RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area
1937457152 bytes
Fixed Size
2220848 bytes
Variable Size
1107299536 bytes
Database Buffers
822083584 bytes
Redo Buffers 5853184 bytes
database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names;
names changed to disk group only.
contents of Memory Script:
{
setnewname for datafile 1 to
"+data1";
setnewname for datafile 2 to
"+data1";
setnewname for datafile 3 to
"+data1";
setnewname for datafile 4 to
"+data1";
backup as copy reuse
datafile 1 auxiliary format
"+data1" datafile
2 auxiliary format
"+data1" datafile
3 auxiliary format
"+data1" datafile
4 auxiliary format
"+data1" ;
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
Starting backup at 27-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
inputdatafile file number=00001
name=+DATA1/rmdb/datafile/system.256.816249133
output file name=+DATA1/clonedb/datafile/system.258.816499099
tag=TAG20130527T051817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
inputdatafile file number=00002
name=+DATA1/rmdb/datafile/sysaux.257.816249133
output file name=+DATA1/clonedb/datafile/sysaux.259.816499105
tag=TAG20130527T051817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
inputdatafile file number=00003
name=+DATA1/rmdb/datafile/undotbs1.258.816249133
output file name=+DATA1/clonedb/datafile/undotbs1.260.816499113 tag=TAG20130527T051817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
inputdatafile file number=00004
name=+DATA1/rmdb/datafile/users.259.816249133
output file name=+DATA1/clonedb/datafile/users.261.816499113
tag=TAG20130527T051817
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-MAY-13
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like "/vol1/arch/1_9_816249207.dbf"
auxiliary format
"/vol1/arch/1_9_816249207.dbf" ;
catalog clone archivelog "/vol1/arch/1_9_816249207.dbf";
switch clone datafile all;
}
executing Memory Script
Starting backup at 27-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=9 RECID=8 STAMP=816499115
output file name=/vol1/arch/1_9_816249207.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 27-MAY-13
cataloged archived log
archived log file name=/vol1/arch/1_9_816249207.dbf RECID=8
STAMP=816499117
datafile 1 switched to datafile copy
inputdatafile copy RECID=1 STAMP=816499117 file
name=+DATA1/clonedb/datafile/system.258.816499099
datafile 2 switched to datafile copy
inputdatafile copy RECID=2 STAMP=816499117 file
name=+DATA1/clonedb/datafile/sysaux.259.816499105
datafile 3 switched to datafile copy
inputdatafile copy RECID=3 STAMP=816499117 file
name=+DATA1/clonedb/datafile/undotbs1.260.816499113
datafile 4 switched to datafile copy
inputdatafile copy RECID=4 STAMP=816499117 file
name=+DATA1/clonedb/datafile/users.261.816499113
contents of Memory Script:
{
set until scn 1171572;
recover
clone database
deletearchivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 27-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
starting media recovery
archived log for thread 1 with sequence 9 is already on disk as file /vol1/arch/1_9_816249207.dbf
archived log file name=/vol1/arch/1_9_816249207.dbf thread=1 sequence=9
media recovery complete, elapsed time: 00:00:01
Finished recover at 27-MAY-13
Oracle instance started
Total System Global Area
1937457152 bytes
Fixed Size
2220848 bytes
Variable Size
1107299536 bytes
Database Buffers
822083584 bytes
Redo Buffers
5853184 bytes
contents of Memory Script:
{
sql clone "alter system set
db_name =
''CLONEDB'' comment=
''Reset to original value by RMAN''
scope=spfile";
sql clone "alter system reset
db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set
db_name = ''CLONEDB'' comment=
''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset
db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area
1937457152 bytes
Fixed Size
2220848 bytes
Variable Size
1107299536 bytes
Database Buffers
822083584 bytes
Redo Buffers
5853184 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB"
RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '+data1' ) SIZE 50 M REUSE,
GROUP 2 ( '+data1' ) SIZE 50 M REUSE,
GROUP 3 ( '+data1' ) SIZE 50 M REUSE
DATAFILE
'+DATA1/clonedb/datafile/system.258.816499099'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
setnewname for tempfile 1 to
"+data1";
switch clone tempfile all;
catalog clone datafilecopy
"+DATA1/clonedb/datafile/sysaux.259.816499105",
"+DATA1/clonedb/datafile/undotbs1.260.816499113",
"+DATA1/clonedb/datafile/users.261.816499113";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamedtempfile 1 to +data1 in control file
catalogeddatafile copy
datafile copy file name=+DATA1/clonedb/datafile/sysaux.259.816499105
RECID=1 STAMP=816499143
catalogeddatafile copy
datafile copy file name=+DATA1/clonedb/datafile/undotbs1.260.816499113
RECID=2 STAMP=816499143
catalogeddatafile copy
datafile copy file name=+DATA1/clonedb/datafile/users.261.816499113
RECID=3 STAMP=816499143
datafile 2 switched to datafile copy
inputdatafile copy RECID=1 STAMP=816499143 file
name=+DATA1/clonedb/datafile/sysaux.259.816499105
datafile 3 switched to datafile copy
inputdatafile copy RECID=2 STAMP=816499143 file
name=+DATA1/clonedb/datafile/undotbs1.260.816499113
datafile 4 switched to datafile copy
inputdatafile copy RECID=3 STAMP=816499143 file
name=+DATA1/clonedb/datafile/users.261.816499113
Reenablingcontrolfile options for auxiliary database
Executing: alter database force logging
contents of Memory Script:
{
Alter clone database open
resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 27-MAY-13
Step 7:
Check the status of
the cloned database.
SQL> select name,db_unique_name,database_role,dbid from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE DBID
---------------- --------------------------- ---------------------- ---------------
CLONEDB CLONEDB PRIMARY 903172166
VERIFICATIONS:
1. Check the DBID of both the databases RMDB
and CLONEDB. It will be different.
Primary Database – RMDB
NAME DB_UNIQUE_NAME DATABASE_ROLE DBID
---------------- --------------------------- ---------------------- ---------------
RMDB RMDB PRIMARY 873495541
Clone Database - CLONEDB
SQL> select name,db_unique_name,database_role,dbid from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE DBID
---------------- --------------------------- ---------------------- ---------------
CLONEDB CLONEDB PRIMARY 903172166
2. Before performing Active Duplication I
created a schema and a table. The same has been replicated to the cloned
database.
On Primary Database – RMDB
SQL> conn test/*****@rmdb
Connected.
SQL> select count(*) from tab;
COUNT(*)
----------
1
SQL> select * from t1;
NO
----------
1
2
3
SQL>
On Clone Database – CLONEDB
SQL> conn test/*****@clonedb
Connected.
SQL> select count(*) from tab;
COUNT(*)
----------
1
SQL> select * from t1;
NO
----------
1
2
3
Veera,
ReplyDeleteCould you Please explain, why do we generally get this warning while perform active duplication,
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
Best
Murugan
Hi Murugan,
DeleteRMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
This warning comes when you use OMF (Oracle Managed Files). If you use OMF, then db_file_name_convert and log_file_name_convert will NOT work. This is one of the restrictions.
The Warning states that, RMAN was not able possible to convert the ASM Oracle Managed Files names using DB_FILE_NAME_CONVERT parameter. So it has changed these invalid names
automatically to the converted Disk Group name instead.
This is a warning / informational message only. But if you feel that the automatic change of name by RMAN is incorrect then you can use the below options,
1)RMAN command SET NEWNAME for each Oracle Managed File.
2)set DB_CREATE_FILE_DEST initialization parameter in auxiliary instance (Duplicating/Secondary Database) and do not specify parameter DB_FILE_NAME_CONVERT.
Regards,
Veera
Dude, you are a lifesaver! We had been trying to create a standby for a 20T RAC database and were having all types of problems. I found your article and it is now being created. Thank you so much!
ReplyDeleteHey what a brilliant post I have come across and believe me I have been searching out for this similar kind of post for past a week and hardly came across this. Thank you very much and will look for more postings from you. cd duplication services
ReplyDelete