11g RAC | Using Duplicate target database 11g Active Database option

I have a 2 Node RAC Standby database (STDBY) . I need to replicate it as a Load TEST database (LDTEST) in a read/write mode.

I will run thru following steps:
1. Preparing the Auxiliary Instance
2. Starting and Configuring RMAN Before Duplication
3. Duplicating a Database

1. Preparing the Auxiliary Instance

Step 1: Create an Oracle Password File for the Auxiliary Instance

When using FROM ACTIVE DATABASE option the source database instance which is the database instance to which RMAN is connected as TARGET connects directly to the auxiliary database instance.  This connection requires a password file with the same SYSDBA password.

[oracle@Node3]$ pwd
/mnt/data/oradata/LDTEST   — The password file placed at Clustered storage.
[oracle@Node3]$ cp orapwSTDBY ../LDTEST
[oracle@Node3]$ cd ../LDTEST
[oracle@Node3]$ ls -lrt
total 4
-rw-r—– 1 oracle oinstall 1536 May 3 12:57 orapwSTDBY

[oracle@Node3]$ mv orapwSTDBY orapwLDTTEST
[oracle@Node3]$ ls -lrt
total 4
-rw-r—– 1 oracle oinstall 1536 May 3 12:57 orapwLDTTEST

Step 2: Establish Oracle Net Connectivity to the Auxiliary Instance

When duplicating from an active database, you must first have connected as SYSDBA to the auxiliary instance by means of a net service name.
Add new database instance details $ORACLE_HOME/netaork/admin/listener.ora

(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = LDTEST) # Replicated DB
(ORACLE_HOME = /opt/app/oracle/product/11.2/db_1)
(SID_NAME =LDTTEST1)
)

[oracle@Node3 admin]$ lsnrctl reload
[oracle@Node3 admin]$ lsnrctl status
Service “LDTEST” has 1 instance(s).
Instance “LDTTEST1”, status UNKNOWN, has 1 handler(s) for this service…
Service “STDBY_DGMGRL” has 1 instance(s).
Instance “STDBY1”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

Add following dedicated entry at /opt/app/oracle/product/11.2/db_1/network/admin

LDTTEST1=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Node3scan)(PORT = 1529))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LDTEST)
)
)

[oracle@Node3 admin]$ tnsping LDTTEST1
TNS Ping Utility for Linux: Version 11.2.0.2.0 – Production on 03-MAY-2011
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Node3scan)(PORT = 1529)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = LDTEST)))
OK (10 msec)

Step 3: Create an Initialization Parameter File for An Auxiliary Instance

Change directories at pfile to point to new database directory structure
** : set *.cluster_database=false
Let’s create dump directories needed. The easiest way is to copy structure of directory tree from existing instance.

[oracle@Node3 STDBY]$ pwd
/opt/app/oracle/diag/rdbms/STDBY
find . -type d -exec mkdir /opt/app/oracle/diag/rdbms/LDTEST/{} \;
“du -a” showed right directory structure created
84 ./LDTTEST1/trace
4 ./LDTTEST1/sweep
4 ./LDTTEST1/metadata
4 ./LDTTEST1/alert
4 ./LDTTEST1/stage
4 ./LDTTEST1/hm
4 ./LDTTEST1/incident
136 ./LDTTEST1

SQL> create pfile=’$ORACLE_HOME/dbs/initLDTTEST1.ora’ from spfile;

Edit pfile and make directory location changes required for new Database.

Step 4: Start Auxiliary Instance with SQL*Plus

SQL> startup nomount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size 2234056 bytes
Variable Size 6945769784 bytes
Database Buffers 2181038080 bytes
Redo Buffers 23818240 bytes
SQL> show parameter db_name
NAME TYPE VALUE
———————————— ———– ——————————
db_name string LDTTEST1

2. Starting and Configuring RMAN Before Duplication
Step 1: Start RMAN and Connect to the Database Instances
Step 2: Mount or Open the Source Database
Step 1: Start RMAN and Connect to the Database Instances

RMAN> connect target sys/sysgsadm@STDBY
connected to target database: PROD (DBID=4020163110)
RMAN> CONNECT AUXILIARY SYS/sysgsadm@LDTTEST1
connected to auxiliary database: LDTTEST1 (not mounted)

Step 2: Mount or Open the Source Database

Before beginning RMAN duplication, mount or open the source database it if it is not already mounted or open.

3. Duplicating a Database

Run following RMAN command

RMAN> DUPLICATE TARGET DATABASE TO LDTEST
FROM ACTIVE DATABASE
DB_FILE_NAME_CONVERT ‘/PROD’,’/LDTEST’
PFILE=’/opt/app/oracle/product/11.2/db_1/dbs/initLDTTEST1.ora’;

Starting Duplicate Db at 03-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=392 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=490 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=586 device type=DISK
contents of Memory Script:
{
sql clone “create spfile from memory”;
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 9152860160 bytes
Fixed Size 2234056 bytes
Variable Size 6979324216 bytes
Database Buffers 2147483648 bytes
Redo Buffers 23818240 bytes
contents of Memory Script:
{
sql clone “alter system set db_name =
”PROD” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”LDTEST” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format ‘/mnt/data/oradata/LDTEST/control01.ctl’;
restore clone controlfile to ‘/mnt/data/oradata/LDTEST/control02.ctl’ from
‘/mnt/data/oradata/LDTEST/control01.ctl’;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ”PROD” comment= ”Modified by RMAN duplicate” scope=spfile
sql statement: alter system set db_unique_name = ”LDTEST” comment= ”Modified by RMAN duplicate” scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 9152860160 bytes
Fixed Size 2234056 bytes
Variable Size 6979324216 bytes
Database Buffers 2147483648 bytes
Redo Buffers 23818240 bytes
Starting backup at 03-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=300 instance=STDBY1 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=396 instance=STDBY1 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=495 instance=STDBY1 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/opt/app/oracle/product/11.2/db_1/dbs/snapcf_STDBY1.f tag=TAG20110503T162228 RECID=13 STAMP=750183751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 03-MAY-11
Starting restore at 03-MAY-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=392 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=490 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=586 device type=DISK
channel ORA_AUX_DISK_2: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_3: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 03-MAY-11
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
“/mnt/data/oradata/LDTEST/system01.dbf”;
set newname for datafile 2 to
“/mnt/data/oradata/LDTEST/sysaux01.dbf”;
set newname for datafile 3 to
“/mnt/data/oradata/LDTEST/undotbs01.dbf”;
set newname for datafile 4 to
“/mnt/data/oradata/LDTEST/users01.dbf”;
set newname for datafile 5 to
“/mnt/data/oradata/LDTEST/undotbs02.dbf”;
backup as copy reuse
datafile 1 auxiliary format
“/mnt/data/oradata/LDTEST/system01.dbf” datafile
2 auxiliary format
“/mnt/data/oradata/LDTEST/sysaux01.dbf” datafile
3 auxiliary format
“/mnt/data/oradata/LDTEST/undotbs01.dbf” datafile
4 auxiliary format
“/mnt/data/oradata/LDTEST/users01.dbf” datafile
5 auxiliary format
“/mnt/data/oradata/LDTEST/undotbs02.dbf” datafile
}
executing Memory Script
Starting backup at 03-MAY-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/mnt/data/oradata/PROD/sysaux01.dbf
channel ORA_DISK_2: starting datafile copy
input datafile file number=00001 name=/mnt/data/oradata/PROD/system01.dbf
output file name=/mnt/data/oradata/LDTEST/system01.dbf tag=TAG20110503T162300
channel ORA_DISK_3: datafile copy complete, elapsed time: 00:04:05
channel ORA_DISK_3: starting datafile copy
input datafile file number=00005 name=/mnt/data/oradata/PROD/undotbs02.dbf
output file name=/mnt/data/oradata/LDTEST/sysaux01.dbf tag=TAG20110503T162300
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:06:50
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/mnt/data/oradata/PROD/undotbs01.dbf
output file name=/mnt/data/oradata/LDTEST/cdc_data01.dbf tag=TAG20110503T162300
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:02:15
channel ORA_DISK_2: starting datafile copy
input datafile file number=00004 name=/mnt/data/oradata/PROD/users01.dbf
output file name=/mnt/data/oradata/LDTEST/undotbs01.dbf tag=TAG20110503T162300
Finished backup at 03-MAY-11
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like “/mnt/logs/oradata/PROD/arch/2_753_747681489.arc” auxiliary format
“/mnt/logs/oradata/LDTEST/arch/2_753_747681489.arc” archivelog like
“/mnt/logs/oradata/PROD/arch/1_664_747681489.arc” auxiliary format
“/mnt/logs/oradata/LDTEST/arch/1_664_747681489.arc” archivelog like
“/mnt/logs/oradata/PROD/arch/2_754_747681489.arc” auxiliary format
“/mnt/logs/oradata/LDTEST/arch/2_754_747681489.arc” ;
catalog clone archivelog “/mnt/logs/oradata/LDTEST/arch/2_753_747681489.arc”;
catalog clone archivelog “/mnt/logs/oradata/LDTEST/arch/1_664_747681489.arc”;
catalog clone archivelog “/mnt/logs/oradata/LDTEST/arch/2_754_747681489.arc”;
switch clone datafile all;
}
executing Memory Script
Starting backup at 03-MAY-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=753 RECID=782 STAMP=750183649
channel ORA_DISK_2: starting archived log copy
input archived log thread=1 sequence=664 RECID=784 STAMP=750184270
channel ORA_DISK_3: starting archived log copy
input archived log thread=2 sequence=754 RECID=786 STAMP=750184271
output file name=/mnt/logs/oradata/LDTEST/arch/2_753_747681489.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
output file name=/mnt/logs/oradata/LDTEST/arch/1_664_747681489.arc RECID=0 STAMP=0
channel ORA_DISK_2: archived log copy complete, elapsed time: 00:00:01
output file name=/mnt/logs/oradata/LDTEST/arch/2_754_747681489.arc RECID=0 STAMP=0
channel ORA_DISK_3: archived log copy complete, elapsed time: 00:00:01
Finished backup at 03-MAY-11
cataloged archived log
archived log file name=/mnt/logs/oradata/LDTEST/arch/2_753_747681489.arc RECID=783 STAMP=750184304
cataloged archived log
archived log file name=/mnt/logs/oradata/LDTEST/arch/1_664_747681489.arc RECID=784 STAMP=750184304
cataloged archived log
archived log file name=/mnt/logs/oradata/LDTEST/arch/2_754_747681489.arc RECID=785 STAMP=750184305
datafile 1 switched to datafile copy
input datafile copy RECID=13 STAMP=750184307 file name=/mnt/data/oradata/LDTEST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=14 STAMP=750184308 file name=/mnt/data/oradata/LDTEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=15 STAMP=750184309 file name=/mnt/data/oradata/LDTEST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=16 STAMP=750184309 file name=/mnt/data/oradata/LDTEST/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=17 STAMP=750184310 file name=/mnt/data/oradata/LDTEST/undotbs02.dbf
contents of Memory Script:
{
set until scn 263980944;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 03-MAY-11
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
starting media recovery
archived log for thread 1 with sequence 664 is already on disk as file /mnt/logs/oradata/LDTEST/arch/1_664_747681489.arc
archived log for thread 2 with sequence 754 is already on disk as file /mnt/logs/oradata/LDTEST/arch/2_754_747681489.arc
archived log file name=/mnt/logs/oradata/LDTEST/arch/1_664_747681489.arc thread=1 sequence=664
archived log file name=/mnt/logs/oradata/LDTEST/arch/2_754_747681489.arc thread=2 sequence=754
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-MAY-11
Oracle instance started
Total System Global Area 9152860160 bytes
Fixed Size 2234056 bytes
Variable Size 6945769784 bytes
Database Buffers 2181038080 bytes
Redo Buffers 23818240 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “LDTEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 2048
MAXINSTANCES 32
MAXLOGHISTORY 1168
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M
DATAFILE
‘/mnt/data/oradata/LDTEST/system01.dbf’
CHARACTER SET AL32UTF8
sql statement: ALTER DATABASE ADD LOGFILE
INSTANCE ‘i2’
GROUP 3 SIZE 50 M ,
GROUP 4 SIZE 50 M
contents of Memory Script:
{
set newname for tempfile 1 to
“/mnt/data/oradata/LDTEST/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/mnt/data/oradata/LDTEST/sysaux01.dbf”,
“/mnt/data/oradata/LDTEST/undotbs01.dbf”,
“/mnt/data/oradata/LDTEST/users01.dbf”,
“/mnt/data/oradata/LDTEST/undotbs02.dbf”,
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /mnt/data/oradata/LDTEST/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/mnt/data/oradata/LDTEST/sysaux01.dbf RECID=1 STAMP=750184356
cataloged datafile copy
datafile copy file name=/mnt/data/oradata/LDTEST/undotbs01.dbf RECID=2 STAMP=750184356
cataloged datafile copy
datafile copy file name=/mnt/data/oradata/LDTEST/users01.dbf RECID=3 STAMP=750184356
cataloged datafile copy
datafile copy file name=/mnt/data/oradata/LDTEST/undotbs02.dbf RECID=4 STAMP=750184357
cataloged datafile copy
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=750184356 file name=/mnt/data/oradata/LDTEST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=750184356 file name=/mnt/data/oradata/LDTEST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=750184356 file name=/mnt/data/oradata/LDTEST/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=750184357 file name=/mnt/data/oradata/LDTEST/undotbs02.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data(PRIMARY KEY, UNIQUE) columns
Executing: alter database force logging
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 03-MAY-11

The database is now working fine on One Node1, I will have to convert it into a 2 node RAC database.

Create shared spfile for both instances , set CLUSTER_DATABASE to TRUE at spfile/pfile

SQL> alter system set cluster_database=TRUE scope=spfile;
System altered.
SQL> shutdown abort;
ORACLE instance shut down.
[oracle@Node3 dbs]$ cat initLDTTEST1.ora
SPFILE=’/mnt/data/oradata/LDTEST/spfileLDTTEST.ora'[oracle@Node4 dbs]$ cat initLDTTEST2.ora
SPFILE=’/mnt/data/oradata/LDTEST/spfileLDTTEST.ora’

Move password file to clustered shared storage and create soft links from both nodes Node3, Node4 to orapwLDTTEST

[oracle@Node3 dbs]$ ln -s /mnt/data/oradata/LDTEST/orapwLDTTEST orapwLDTTEST2
[oracle@Node3 dbs]$ ln -s /mnt/data/oradata/LDTEST/orapwLDTTEST orapwLDTTEST1
[oracle@Node3 dbs]$ scp initLDTTEST1.ora oracle@Node4:/opt/app/oracle/product/11.2/db_1/dbs
SQL> startup;
ORACLE instance started.
Total System Global Area 9152860160 bytes
Fixed Size 2234056 bytes
Variable Size 6945769784 bytes
Database Buffers 2181038080 bytes
Redo Buffers 23818240 bytes
Database mounted.
Database opened.

Make changes at Listener.ora,tnsnames.ora files on Second Node RAC – Node4

RACNode3> show parameter instance_name
instance_name string LDTTEST1
RACNode3> select count(*) from tab;
4865
RACNode3> show parameter cluster_database
cluster_database boolean TRUE

RACNode4> show parameter instance_name
instance_name string LDTTEST2
RACNode4> select count(*) from tab;
4865
RACNode4> show parameter cluster_database
cluster_database boolean TRUE

Lets make the database Cluster services aware.

[oracle@Node3 dbs]$ srvctl add database -d LDTEST -o /opt/app/oracle/product/11.2/db_1 -p /mnt/data/oradata/LDTEST/spfileLDTTEST.ora
[oracle@Node3 dbs]$ srvctl add instance -d LDTEST -i LDTTEST1 -n Node3
[oracle@Node3 dbs]$ srvctl add instance -d LDTEST -i LDTTEST2 -n Node4
[oracle@Node3 arch]$ /home/oracle/Scripts/crsstat.sh | grep LDTEST
ora.LDTEST.db OFFLINE OFFLINE

Finally  stop/start RAC Databases using srvctl commands

[oracle@Node3 dbs]$ srvctl start database -d LDTEST
[oracle@Node3 dbs]$ $HOME/Scripts/crsstat.sh | grep prod
ora.prod.db ONLINE ONLINE on Node3
[oracle@Node3 dbs]$ srvctl status database -d LDTEST
Instance LDTTEST1 is running on node Node3
Instance LDTTEST2 is running on node Node4

Have a look at alrtlog for any issues reported.