Multiple Standby Databases Dataguard Oracle

We will create 2 standby databases from a single RAC primary database. 11gR2 allows upto 30 standby databases.  The most important part is to consider the dataguard parameters, the rest is a normal dataguard creation. The important parameters are as below :
db_file_name_convert
log_archive_config
log_archive_dest_1
log_archive_dest_2
log_archive_dest_3
LOG_ARCHIVE_DEST_STATE_1
LOG_ARCHIVE_DEST_STATE_2
LOG_ARCHIVE_DEST_STATE_3
log_file_name_convert
remote_login_passwordfile
STANDBY_FILE_MANAGEMENT
fal_client
fal_server
sec_case_sensitive_logon    (if on 11gR2)
All database are running on Grid + ASM 11.2.0.3.4 with RAC primary and RAC standby’s. Please note the procedure is identical to a single instance setup. In Rac the only special thing you do is register the standby database and instances to the OCR via srvctl command.
We all call our primary database SWX and our PHYSICAL STANDBY databases as SWXSTANDBY, SWXSTANDYBY2. We will use RMAN Duplicate Active Standby database feature of 11gR2. If your standby databases are in different data centesr and the bandwidht isn’t suffcient you can use the tradionatl RMAN  duplicate command.
1. Create tnsnames.ora entry on Primary database. Use RDBMS owner for the tnsnames.ora not Grid infrastructure owner

SWX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.213)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swx1)(UR=A)
)
)
SWXSTANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.219)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swxstandby)(UR=A)
)
)
SWXSTANDBY2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =  *.*.*.224)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swxstandby2)(UR=A)
)

2. Add an SID entry on the listener.ora as Grid infrastructure user

SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=swx)(ORACLE_HOME=/grid/app/11.2.0/grid/)))

3. Create a pfile and the below datagaurd parameters to the pfile

#########################STANDBY PARAMETERS########################
*.DB_UNIQUE_NAME='swx'
*.db_file_name_convert='+DATA/SWXSTANDBY2/','+DATA/SWXSTANDBY/','+DATA/SWX/','+FRA/SWXSTANDBY2/','+FRA/SWXSTANDBY/','+FRA/SWX/'
*.log_archive_config='dg_config=(swxstandby2,swxstandby,swx)'
*.log_archive_dest_1='LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swx'
*.log_archive_dest_2='service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby'
*.log_archive_dest_3='service=swxstandby2 LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='+DATA/SWXSTANDBY2/','+DATA/SWXSTANDBY/','+DATA/SWX/','+FRA/SWXSTANDBY2/','+FRA/SWXSTANDBY/','+FRA/SWX/'
*.remote_login_passwordfile='exclusive'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.fal_client='SWX'
*.fal_server='SWXSTANDBY2','SWXSTANDBY'
*.sec_case_sensitive_logon=FALSE
########################################

4. Restart the database with the new pfile and create spfile from it and bounce the database again
5. Login to the First DR database server as Oracle user and create tnsnames.ora entry

SWX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.213)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swx)(UR=A)
)
)
SWXSTANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.218)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swxstandby)(UR=A)
)
)
SWXSTANDBY2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.224)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = swxstandby2)(UR=A)
)
)

6. Add SID listener entry in listener.ora file as Grid infrastructure user.

SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=swx)(ORACLE_HOME=/grid/app/11.2.0/grid)))

7. Create a pfile with the dataguard parameters from the pfile created before on primary and add below parameters.

#########################STANDBY DATABASE PARAMETERS########################
*.DB_UNIQUE_NAME='SWXSTANDBY'
*.db_file_name_convert='+DATA/SWX/','+DATA/SWXSTANDBY/','+DATA/SWXSTANDBY2/','+FRA/SWX/','+FRA/SWXSTANDBY/','+FRA/SWXSTANDBY2/'
*.log_archive_config='dg_config=(swxstandby2,swxstandby,swx)'
*.log_archive_dest_1='LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swxstandby'
*.log_archive_dest_2='service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swx'
*.log_archive_dest_3='service=swxstandby2 LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby2'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='+DATA/SWX/','+DATA/SWXSTANDBY/','+DATA/SWXSTANDBY2/','+FRA/SWX/','+FRA/SWXSTANDBY/','+FRA/SWXSTANDBY2/'
*.remote_login_passwordfile='exclusive'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.fal_client='SWXSTANDBY'
*.fal_server='SWX','SWXSTANDBY2'
*.sec_case_sensitive_logon=FALSE
########################################

8. Start the database with the pfile and create spfile from it
9. Clone the database from RMAN using active option

rman target sys/***@swx auxiliary sys/***@swxstandby
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

10. Register the database and instance with srvctl command

srvctl add database -d SWXSTANDBY -o /oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -r PHYSICAL_STANDBY -a DATA,FRA
srvctl add instance -d SWXSTANDBY -i swx1 -n csftest-2
srvctl stop database -d SWXSTANDBY
sqlplus "/as sysdba"
srvctl start database -d swxstandby
srvctl modify database -d swxstandby -s mount
srvctl config database -d swxstandby
## TO ACTIVATE ACTIVE STANDBY DATABASE ###
srvctl start database -d swxstandby -o open

11. Login to the Second DR database server as Oracle user and create tnsnames.ora entry

SWX =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.213)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = swx)(UR=A)
)
)
SWXSTANDBY2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.15)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SWXSTANDBY2)(UR=A)
)
)
SWXSTANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = *.*.*.218)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SWXSTANDBY)(UR=A)
)
)

12. Create a listener.ora entry as grid user

SID_LIST_EASYORADBA_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=swx1)(GLOBAL_DBNAME=swx)(ORACLE_HOME=/grid/app/11.2.0/grid)))

13. Create a pfile with the dataguard parameters from the pfile created before on primary and add below parameters.

#########################STANDBY DATABASE PARAMETERS########################
*.DB_UNIQUE_NAME='SWXSTANDBY2'
*.db_file_name_convert='+DATA/SWX/','+DATA/SWXSTANDBY/','+DATA/SWXSTANDBY2/','+FRA/SWX/','+FRA/SWXSTANDBY/','+FRA/SWXSTANDBY2/'
*.log_archive_config='dg_config=(swxstandby2,swxstandby,swx)'
*.log_archive_dest_1='LOCATION=+FRA valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=swxstandby2'
*.log_archive_dest_2='service=swx LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swx'
*.log_archive_dest_3='service=swxstandby LGWR ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=swxstandby'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_3='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.log_file_name_convert='+DATA/SWX/','+DATA/SWXSTANDBY/','+DATA/SWXSTANDBY2/','+FRA/SWX/','+FRA/SWXSTANDBY/','+FRA/SWXSTANDBY2/'
*.remote_login_passwordfile='exclusive'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.fal_client='SWXSTANDBY2'
*.fal_server='SWX','SWXSTANDBY'
*.sec_case_sensitive_logon=FALSE
########################################

14. Start the database with the pfile and create spfile from it
15. Clone the database from RMAN using active option

rman target sys/***@swx auxiliary sys/***@swxstandby
RMAN> duplicate target database for standby from active database nofilenamecheck dorecover;

16. Register the database and instance with srvctl command

srvctl add database -d swxstandby2 -o /oracle/app/oracle/product/11.2.0/dbhome_1 -c RAC -r PHYSICAL_STANDBY -n swx -a DATA,FRA
srvctl add instance -d swxstandby2 -i swx1 -n suncsftst

Now that we  have  a fully functional RAC PRIMARY with 2 PHYSICAL STANDBYs now let us consider the 2 most important things in Dataguard: Switchover and Failover
————–
Switchover
—————-
No extra steps are necessary when performing a switchover. All bystander physical
standby databases automatically apply redo data received from the new primary
database.
————–
Failover
—————-
The steps for performing a failover to a physical standby database depend on the
Redo Apply progress of the new primary database and any bystander physical
standby databases at the time of the failover.
• If the new primary database has applied more redo than all of the bystander
physical standby databases, no additional steps are required. Only the original
primary database needs to be reinstated, using the steps documented in Oracle
Data Guard Concepts and Administration[5], Section 12.4.1 “Flashing Back a Failed
Primary Database into a Physical Standby Database.”.
• If any bystander physical standby database has applied more redo than the new
primary database, then perform the following steps to reinstate the bystander
physical standby
SQL*Plus Physical/Physical Failover with Physical Bystander Ahead
1. Determine STANDBY_BECAME_PRIMARY_SCN from the new
primary.

SQL> select STANDBY_BECAME_PRIMARY_SCN from
v$database;

2. On the bystander physical standby, flash back to
STANDBY_BECAME_PRIMARY_SCN from the new primary
database.

SQL> flashback database to scn
<STANDBY_BECAME_PRIMARY_SCN>;

3. On the bystander physical standby, delete divergent archived redo logs
created at the time of, or after, the failover.

RMAN> delete archivelog from scn
<STANDBY_BECAME_PRIMARY_SCN>;

4. On the new primary database, enable the redo transport destination
for this bystander physical standby and archive the current redo log.

SQL> alter system set
log_archive_destination_2=enable;
SQL> alter system archive log current;

5. After the logs have been received by the bystander physical standby,
start Redo Apply on the bystander physical standby.

SQL> alter database recover managed standby
database using current logfile through all
switchover disconnect;

The bystander standby database is now reinstated.
For more information refer to this excellent Oracle document.
http://www.oracle.com/technetwork/database/features/availability/maa10gr2multiplestandbybp-1-131937.pdf
Interesting Case-Study on Multiple Standby Databases. Apparently Apple uses it for one of their Large databases.
http://www.oracle.com/technetwork/database/features/availability/311400-134359.pdf

Category: Database

Tags:

4 comments

  1. I did search many websites for multiple standby data guard setup, but I couldn’t except a MAA-best practice from oracle. The way you have provided the sequences of steps is making this docs an easy and effective tool in installing Multiple Standby database. So simple!

    Thank you very much for such a good stuff.

  2. Assalamualaikum

    Hi Shhadab,

    Why do you activate both log_archive_dest_3 on primary db swx and log_archive_dest_3 on standby db 1 swxstandby?

    Does it will cause the conflict when apply archive logs in the second standby db swxstandby2?

    Thanks

Leave a Reply

Article by: Shadab Mohammad