11gR2 RAC DataGuard setup using ASM

This document would describe the Dataguard setup for 11gR2 RAC database using ASM. We will be using same host for setting up Dataguard.

Environment Details

OS - RHEL5
Database Version - 11.2.0.3
DB Host - oradbdev01,oradbdev02
Primary DB Unique Name - tintin
BCP DB Unique Name - snowy

ORACLE_HOME=/home/oracle/product/11.2.0/dbhome_1
ORACLE_GRID=/home/oragrid/product/11.2.0/grid

oradbdev01[tintin1]:/home/oracle>srvctl status db -d tintin
Instance tintin1 is running on node oradbdev01
Instance tintin2 is running on node oradbdev02
oradbdev01[tintin1]:/home/oracle>srvctl status db -d snowy
Instance snowy2 is running on node oradbdev01
Instance snowy1 is running on node oradbdev02

Configuring Tnsnames.ora and Listener.ora

We will be using RMAN active database duplication commands to duplicate the database. So we need to setup static service registration by adding entries in listener.ora. Also we will use same setup for Dataguard Broker setup, so the service name will be snowy_DGMGRL and tintin_DGMGRL. There is one additional service per server i.e snowy_DGB and tintin_DGB. This will be used for Dataguard Broker setup. In case you don't plan to setup DG broker, you can ignore this.

Listener.ora entries for oradbdev01

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
          (GLOBAL_DBNAME = tintin_DGMGRL)
          (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
          (SID_NAME = tintin1)
    )
    (SID_DESC =
             (GLOBAL_DBNAME = snowy_DGMGRL)
             (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = snowy2)
   )
	(SID_DESC =
             (GLOBAL_DBNAME = tintin_DGB)
             (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = tintin1)
   )
  )

Listener.ora entries for oradbdev02

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
          (GLOBAL_DBNAME = tintin_DGMGRL)
          (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
          (SID_NAME = tintin2)
    )
    (SID_DESC =
             (GLOBAL_DBNAME = snowy_DGMGRL)
             (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = snowy1)
   )
	(SID_DESC =
             (GLOBAL_DBNAME = snowy_DGB)
             (ORACLE_HOME = /home/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = snowy1)
   )
  )

Once done, restart the listener and scan listener

srvctl stop listener -n oradbdev01
srvctl start listener -n oradbdev01
srvctl stop listener -n oradbdev02
srvctl start listener -n oradbdev02
srvctl stop scan_listener
srvctl start scan_listener

Tnsnames.ora entries will remain same on both nodes. You can either maintain two separate files or use a shared location for files and give IFILE=<shared_location> in tnsnames.ora

tintin_dgmgrl =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbdev01)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbdev02)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = tintin_DGMGRL)))
)

snowy_dgmgrl =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbdev02)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oradbdev01)(PORT = 1521))
    (CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = snowy_DGMGRL)))
)

Note that we have not used load_balance or failover in these entries. We will be having only one auxiliary instance started i.e snowy1 on oradbdev02.

Primary Database Configuration

Ensure that database is running in archivelog mode.First and important step before creating Oracle standby is to put database in force logging mode

SQL> alter database force logging;

You can confirm this by following query

SQL> select force_logging from V$DATABASE;

FOR
---
YES

Next we need to set initialization parameters

DB_NAME=tintin
DB_UNIQUE_NAME=tintin
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(tintin,snowy)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+FLASH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=tintin' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=snowy_dgmgrl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=snowy' scope=both sid='*';
alter system set FAL_SERVER=snowy_dgmgrl scope=both sid='*';
alter system set DB_FILE_NAME_CONVERT='+DATA/tintin','+DATA/snowy' scope=spfile sid='*';
alter system set LOG_FILE_NAME_CONVERT=+FLASH/tintin','+FLASH/snowy' scope=spfile sid='*';
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';

Since we are using ASM, we have set *FILE_NAME_CONVERT to '+DATA/tintin','+DATA/snowy'. When we run rman duplicate, rman will automatically create new database in +DATA/<db_unique_name>.

Add standby logfile

alter database add standby logfile thread 1 group 10 '+FLASH' size 200m;
alter database add standby logfile thread 1 group 11 '+FLASH' size 200m;
alter database add standby logfile thread 1 group 12 '+FLASH' size 200m;
alter database add standby logfile thread 2 group 13 '+FLASH' size 200m;
alter database add standby logfile thread 2 group 14 '+FLASH' size 200m;
alter database add standby logfile thread 2 group 15 '+FLASH' size 200m;

BCP Database Configuration

We will use snowy1 instance on oradbdev02 as auxiliary instance. We create parameter file initsnowy1.ora under $ORACLE_HOME/dbs with following parameters

*.audit_file_dest='/home/oracle/admin/snowy/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='tintin'
*.db_unique_name='snowy'
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='+FLASH'
*.diagnostic_dest='/home/oracle'
snowy1.instance_number=1
*.cluster_database_instances=2
*.log_archive_config='DG_CONFIG=(tintin,snowy)'
*.log_archive_dest_1='LOCATION=+FLASH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=snowy'
*.log_archive_dest_2='SERVICE=tintin_dgmgrl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tintin'
*.open_cursors=300
*.pga_aggregate_target=786432000
*.processes=150
*.remote_listener='oradbdev2-clu:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=1500M
*.standby_file_management='AUTO'
snowy1.thread=1
snowy1.undo_tablespace='UNDOTBS1'

We have kept cluster_database=false while using rman duplicate for standby command. Next step is to create password file for snowy database. Starting 11g, we need to copy the password file from primary (creating new password file for bcp with same password will not work). If you are using rman duplicate command, it will automatically copy password file for you

cd $ORACLE_HOME/dbs
cp orapwtintin2 orapwsnowy1

Ensure that directory mentioned in audit_file_dest is created. Now we do a startup nomount for snowy1

Check DB connectivity

Check that you are able to connect to database using tnsnames.ora entries

sqlplus sys/oracle123@tintin_dgmgrl as sysdba
sqlplus sys/oracle123@snowy_dgmgrl as sysdba

If this is not done, then you will have to ensure static listener entries and tnsnames.ora entries are correct.Once this is done, proceed to next step

Standby database creation using RMAN

We will be using 11g new feature of duplicating database without rman backups. Connect to target and auxiliary instance as mentioned below

[oracle@oradbdev01]~% rman

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Feb 15 08:28:33 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/oracle123@tintin_dgmgrl

connected to target database: TINTIN (DBID=2175947715)

RMAN> connect  auxiliary sys/oracle123@snowy_dgmgrl

connected to auxiliary database: TINTIN (not mounted)

You can create it from command line like below

rman target sys/oracle123@tintin auxiliary sys/oracle123@snowy_dgmgrl

Since I am using same host for Primary/BCP database, to have different instance_name snowy1/snowy2 we will not be using spfile option in rman

run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
duplicate target database for standby from active database DORECOVER;
}

In case you are using separate host for creating your standby database and instance_name will be same i.e tintin1/tintin2, then you can use following rman command. This would allow you to set the init.ora parameters in run block

run {
allocate channel ch1 type disk;
allocate channel ch2 type disk;
allocate auxiliary channel ch3 type disk;
allocate auxiliary channel ch4 type disk;
duplicate target database for standby from active database DORECOVER
spfile
parameter_value_convert 'tintin','snowy'
set 'db_unique_name'='snowy'
set sga_target="1500M"
set fal_server='tintin_dgmgrl'
set db_file_name_convert='+DATA/tintin','+DATA/snowy'
set log_archive_config='dg_config=(tintin,snowy)'
set log_file_name_convert='+FLASH/tintin','+FLASH/snowy'
set log_archive_dest_1='LOCATION=+FLASH VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=snowy'
set log_archive_dest_2='SERVICE=tintin_dgmgrl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tintin';
}

Above commands will duplicate the database to standby host and mount it. You can find the output of rman command here.

Starting Managed Recovery Process

Check that log shipping is working fine by running following query on primary database i.e tintin

set linesize 500
col DESTINATION for a30
SELECT inst_id,DEST_ID ,  STATUS ,DESTINATION , ERROR    FROM gV$ARCHIVE_DEST    WHERE DEST_ID

Destination 2 should status as VALID. If there is any error, then fix it. Next step is to start the MRP(Managed Recovery Process) on BCP database instance snowy1

alter database recover managed standby database  using current logfile parallel 8   disconnect
/

At this moment, we have only started instance snowy1 on oradbdev02. We need to start instance snowy2 also. Moreover the spfile created by rman duplicate is present in $ORACLE_HOME/dbs and we would like to move it to ASM. To do all this changes, we need to first stop Managed Recovery

alter database recover managed standby database cancel
/

Since BCP database was created with cluster_database=false, we need to make following changes

alter system set cluster_database=true scope=spfile sid='*';
 alter system set cluster_database_instances=2 scope=spfile sid='*';
 alter system set instance_number=2 scope=spfile sid='snowy2';
 alter system set  thread=2 scope=spfile sid='snowy2';
 alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='snowy2';

Next we need to move spfile to ASM.We will first create a directory in ASM +DATA/SNOWY/PARAMETERFILE using asmcmd Next is to copy file from $ORACLE_HOME/dbs to ASM using rman.

run
 {
 BACKUP AS BACKUPSET SPFILE;
 restore spfile to '+DATA/SNOWY/PARAMETERFILE/spfilesnowy.ora';
 }

Rename the $ORACLE_HOME/dbs/spfileinitsnowy1.ora and keep entry in initsnowy1.ora and initsnowy2.ora as below

SPFILE='+DATA/SNOWY/PARAMETERFILE/spfilesnowy.ora'

Next we can add this database to OCR using

[oracle@oradbdev02]~% srvctl add database -d snowy -o $ORACLE_HOME -p +DATA/SNOWY/PARAMETERFILE/spfilesnowy.ora
[oracle@oradbdev02]~% srvctl add instance -d snowy -i snowy1 -n oradbdev02
[oracle@oradbdev02]~% srvctl add instance -d snowy -i snowy2 -n oradbdev01

Now startup (mount) both instances and enable MRP on one node.

11gR2 RAC DataGuard setup using ASM