Cloing Oracle Rac database two node but duplicate database is one node

DUPLICATE from ACTIVE Database Using RMAN, a Step-by-Step Guide

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 take the old approach and use RMAN backups or use 11g’s new feature DUPLICATE FROM ACTIVE database, which doesn’t need any backup from target database. I preferred to use the second approach.

Duplicate from ACTIVE database

Duplicating from an active database doesn’t require any RMAN backup to be taken from the source database. Actually, it reads all database structure from the source database that needs to be mounted or opened during the duplication. Although this method seems easy as we don’t need to backup source database or make it accessible for auxiliary instance anymore, it has its own disadvantages as well.

Two big disadvantages of the ACTIVE database duplication method are:

1.Negative performance impact on the source database. This impact applies to the whole duplication time.

2. High network traffic on the connection between the source and target databases.

If either of these negative impacts are not acceptable in your environment , you will need to switch to the old backup-based approach.

By the way, I think that if you have the latest backup of your source database available and it can be accessed by auxiliary instance, the best method for duplication is still the backup-based approach.

Duplicate from active database is an easy task ONLY IF you follow all the steps accurately. Otherwise, it can be a complicated task, and you can waste your time troubleshooting.

The following part is simplified step-by-step instructions for DUPLICATE from ACTIVE database:

Step-by-step instructions

In this guide, assume we are migrating a single instance database HRDEV to a two node RAC database HRPRD with the following specifications:

Source database:

Database name: HRDEV

Single instance

Version: 11.2.0.3

Hostname: dev-db-01

Filesystem: ASM

Target database:

Database name: HRPRD

RAC  2 nodes

Version: 11.2.0.3

ORACLE_HOME: /apps/oracle/product/11.2.0/db_1

GI_HOME: /apps/grid/11.2.0/

Hostname: prd-db-01/prd-db-02

Filesystem: ASM

Diskgroup: +HR

For target database HRPRD, we assume that an empty database has already been created with two instances, spfile and controlfiles already exist, and the database is already a member of clusterware. As a matter of fact, we will use only instances of this database as auxiliary instance, and all datafiles can be deleted manually before duplication, as this database is going to be a refreshed from HRDEV database by our DUPLICATE command.

The diskgroup that will be used for this database is “+HR”.

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 the HRPRD1 instance, which runs on prd-db-01. We need to stop the other instance, HRPRD2:

1	srvctl stop instance -d HRPRD -i HRPRD2

Set the following parameters on HRPRD1 instance:

1 2 3 4 5 6 7 8 9 10

. 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 status registration for HRPRD1 to run LISTENER:

Add the following entries into listener.ora file in GI_HOME.

Edit /apps/grid/11.2.0/network/admin/listener.ora and add the following lines:

1 2 3 4 5 6 7 8 9

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 from which HRPRD database runs.

3- Add following TNS entries to BOTH auxiliary and target tnsnames.ora file:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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 TNS, so we need to use passwords for both auxiliary and target connections.

For HRPRD, since it is a new and empty database, we may need to create a password file for it as follows:

1 2 3 4

. oraenv HRPRD1 cd $ORACLE_HOME/dbs orapwd password=sys file=orapwHRPRD1

I have assumed that the SYS password on the source database HRDEV is “sys”.

5- Test connectivity to auxiliary and target instances from BOTH hosts using TNS:

Make sure your connectivity to the source database and to your auxiliary instance works fine; otherwise, duplicate from active database won’t work.

1 2	sqlplus sys/sys@HRPRD1 as sysdba sqlplus sys/sys@HRDEV as sysdba

Try above commands on both target and auxiliary hosts prd-db-01 and dev-db-01. Do not continue unless your connectivity is fine.

6- On the auxiliary host, start RMAN and run the DUPLICATE command:

From host prd-db-01, which runs auxiliary instance hrprd1, start RMAN. Make sure the auxiliary connection is established through listener and not through OS authentication.

1 2 3 4 5 6 7

. 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 finishes successfully, start HRPRD database using srvctl. You need to enable the second thread as well:

Change the HRPRD database to be a cluster database again and start both instances:

1 2 3 4 5 6 7 8 9 10 11 12

. 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