RMAN ACTIVE DUPLICATION ASM TO ASM

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:

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

RMAN>

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

SQL> select name,db_unique_name,database_role,dbid from v$database;

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