DUPLICATE from ACTIVE Database Using rman Step-by-Step
Oct 24, 2012 / By Kamran Bakhshandeh
Duplicate using RMAN
Recently I had a request from one of our clients to duplicate a single instance database into a two node RAC database. Both source and destination databases were 11g Rel 2 on Linux.
I could use old approach using rman backups or using 11g new feature DUPLICATE FROM ACTIVE database which doesn’t need any backup from target database and I preferred to use second approach.
Duplicate from ACTIVE database
Duplicate from active database doesn’t need any rman backup to be taken from source database and actually it reads all database structure from the source database which needs to be mounted or opened during duplication. Although this method seems easy as we don’t need to backup source database and make it accessible for auxiliary instance anymore, but it has its own disadvantages as well.
Two bold disadvantages of ACTIVE database duplication method are:
- Negative performance impact that it causes on source database and this impact applies to whole duplication time.
- High network traffic on connection between source and target database.
If either of these negative impacts are not acceptable in your environment , you will need to switch to old backup-based approach.
By the way I think if you have a latest backup of your source database available and it can be accessed by auxiliary instance , still the best method for duplication is backup-based approach.
Duplicate from active database is an easy task ONLY IF you follow all steps accurately otherwise it can be a complicated task and can waste lots of your time for troubleshooting.
Following part is a simplified step-by-step instructions for DUPLICATE from ACTIVE database:
Step by Step instruction
Source database:
1- Prepare auxiliary instance HRPRD1 on prd-db-01 ,
Stop all instances of your cluster database except one.
In this example we will use only HRPRD1 instance which runs on prd-db-01 and we need to stop other instance HRPRD2 :
srvctl stop instance -d HRPRD -i HRPRD2
set following parameters on HRPRD1 instance:
. oraenv HRPRD1 sqlplus / as sysdba alter system set db_name=HRPRD scope=spfile; alter system set cluster_database=false scope=spfile; alter system set db_create_file_dest='+HR'; alter system set db_create_online_log_dest_1='+HR'; shutdown immediate startup nomount
2- Enable statis registration for HRPRD1 to running listener LISTENER.
Add following entries into listener.ora file in GI_HOME.
SID_LIST_LISTENER =
(SID_LIST =
)
(SID_DESC =
(SID_NAME = HRPRD1)
(ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = HRPRD)
)
)
Make sure that ORACLE_HOME in this entry points to correct home which is the home that HRPRD database runs from
3- Add following tns entries to BOTH auxiliary and target tnsnames.ora file :
HRDEV =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dev-db-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HRDEV)
)
)
HRPRD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prd-db-01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = HRPRD)
)
)
4- Ceate a password file for auxiliary instance HRPRD1 on prd-db-01:
Connections to both instances will be through listener and using TNS , so we need to use passwords for both auxiliary and target connections.
For HRPRD as a new and empty database we may need to create a password file for it as follow:
. oraenv HRPRD1 cd $ORACLE_HOME/dbs orapwd password=sys file=orapwHRPRD1
5- Test connectivity to auxiliary and target instance from BOTH hosts using TNS
Make sure your connectivity to source database and also to your auxiliary instance works fine , otherwise duplicate from active database won’t work.
sqlplus sys/sys@HRPRD1 as sysdba sqlplus sys/sys@HRDEV as sysdba
6- On auxiliary host start RMAN and run the DUPLICATE command:
From host prd-db-01 which runs auxiliary instance hrprd1 start rman. Make sure auxiliary connection is established through listener not through OS authentication.
. oraenv
HRPRD1
rman target sys/sys@HRDEV auxiliary sys/sys@hrprd1
RMAN>run{
DUPLICATE TARGET DATABASE TO HRPRD
FROM ACTIVE DATABASE;
}
7- When step 6 finished successfully , start HRPRD database using srvctl , you need to enable second thread as well
. oraenv HRPRD1 sqlplus / as sysdba alter database add logfile thread 2 group 4 size 200m; -- add few more groups create tablespace UNDOTBS2 datafile '+HR' size 1g; alter system set undo_tablespace='UNDOTBS2' sid='HRPRD2'; alter database enable public thread 2; alter system set cluster_database=true scope=spfile; shutdown immediate srvctl start db -d HRPRD
That’s all, I hope it will work for you.
Very useful description – thanks!
Beside the “Two bold disadvantages of ACTIVE database” you’ve mentioned I see an other points: the whole directory structure has to be created before.
Regards, Heiko
Thanks for your feedback Heiko
This is an awesome post Kamran. In my case, I found that a few extra steps were needed however because I got this error after RMAN dup completed (only HRPRD1 will start (in my experience). Here is the error message:
[oracle@cgi-ma-uat-db1v ~]$ srvctl start database -d RATIONAL
PRCR-1079 : Failed to start resource ora.rational.db
CRS-5017: The resource action “ora.rational.db start” encountered the following error:
ORA-01618: redo thread 2 is not enabled – cannot mount
. For details refer to “(:CLSN00107:)” in “/u01/app/11.2.0/grid/log/cgi-ma-uat-db2v/agent/crsd/oraagent_oracle/oraagent_oracle.log”.
CRS-2674: Start of ‘ora.rational.db’ on ‘cgi-ma-uat-db2v’ failed
CRS-2632: There are no more servers to try to place resource ‘ora.rational.db’ on that would satisfy its placement policy
Following is needed to fix and convert single-instance cloned DB to RAC 2-node (more steps would be needed for 3-node etc) but similar to below.
Login to the HRPRD1 up and running instance.
alter database add logfile thread 2 group 4;
alter database add logfile thread 2 group 5;
alter database add logfile thread 2 group 6;
Then also:
create undo tablespace UNDOTBS2 datafile size 99614720;
(I used size identical to UNDOTBS1).
Now 2nd instance startup fine:
srvctl start instance -d HRPRD -i HRPRD2
Thanks again for this awesome post Kamran!!!
Thanks for your useful comment Gilber
You are absolutely right. In my test case HRPRD was originally a two node RAC set , that’s why all these steps has been missed.
Anyway thanks again for this awesome comment and I am glad my post has helped you.
I updated my post considering your recommendations related to extra steps for enabling second thread.
Thanks again for your valuable feedback
Forgot one command in my post above. After the add of the logfiles on thread 2, you have to run this command also:
alter database enable public thread 2;
Thanks
Hi
The script catclust.sql is not needed? I mean, if the source is single instance, all GV$ views are missing, am i right?
Regards
Hi Hector
Thanks for your comment , I have done this duplications many times and I’ve never executed catclus.sql
If you check a single instance database you can see al GV$ exist
As spelled above about the bold disadvantages for duplicating database from target (Implies HRDEV in current case) , can you please detail on the tools that can be used to “gauge” the high network traffic connection prob and the impending performance impact on the source before ahead.
Hi Kamran Bakhshandeh,
Another worthy point that I’m persuaded to emphasize is regards the “Directory Structure” and the “Host” wherein which you are been planning to DUPLICATE the database.
CASE -1
=======
Duplicating a Database on a “Remote Host” with the ‘Same Directory Structure’ as source (HRDEV), then NO changes is required to be made for PFILE.
DUPLICATE DATABASE TO HRPRD
FROM ACTIVE DATABASE
PFILE (if starting the auxiliary instance with a client-side parameter file)
NOFILENAMECHECK; (if duplicated datafiles location= Source data file’s location and database’s are in DIFFERENT Host’s)
CASE-2
======
Duplicating a Database on a Remote Host with a Different Directory Structure:
Here you MUST change the PFILE as the duplicate database filenames structure is DIFFERENT from the Source filenames.
DUPLICATE DATABASE TO HRPRD
FROM ACTIVE DATABASE
DB_FILE_NAME_CONVERT=(/oracle/oradata/HRDEV/,/dup/oracle/oradata/HRPRD/)
LOGFILE (names and sizes for the duplicate database redo logs)
‘/dup/oracle/oradata/HRPRD/redo01.log’ SIZE 200K,
‘/dup/oracle/oradata/HRPRD/redo02.log’ SIZE 200K;
Surmise : If we DON’T specify the LOGFILE option, then RMAN after having done with Incomplete Recovery, opens up the database with RESETLOGS option to create the online logs.
CASE 3
======
Creating a Duplicate Database on the Local Host wherein the SOURCE (HRDEV) is resident.
Same procedure need to be followed as spelled in CASE 2.
NOTE : Do not use the NOFILENAMECHECK option in here as doing so will lead to overwriting the SOURCE Datafiles.
SO the above enunciation by Kamran Bakhshandeh will falls in CASE 2.
Hi Panuganti
Thanks for your comments.
My case was exactly CASE 1 , directory structure is same but actual location is different and I didn’t used NOFILENAMECHECK option.
For CASE 2 and CASE 3 yes we need to use DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT parameters either at instance level or in the command.
For CASE 3 we must NOT use NOFILENAMECHECK
Thanks again for your valuable updates.
Hi,
there is a typo for the listener config in step 2 – extra bracket:
This ->
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = HRPRD1)
(ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = HRPRD)
)
)
Should be ->
SID_LIST_LISTENER =
(SID_LIST =
)
(SID_DESC =
(SID_NAME = HRPRD1)
(ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = HRPRD)
)
)
Sorry – pasted in the wrong order! [The extra bracket is under the \"SID_LIST\" value]
This ->
SID_LIST_LISTENER =
(SID_LIST =
)
(SID_DESC =
(SID_NAME = HRPRD1)
(ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = HRPRD)
)
)
Should be ->
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = HRPRD1)
(ORACLE_HOME = /apps/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME = HRPRD)
)
)