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

Advertisements

2 thoughts on “Multiple Standby Databases Dataguard Oracle

  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.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s