How to configure Client Failover after Data Guard Switchover or Failover

How to configure Client Failover after Data Guard Switchover or Failover
	Doc ID:	316740.1	Type:	HOWTO
	Modified Date:	23-JUL-2009	Status:	PUBLISHED

In this Document
  Goal
  Solution
  References

---

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.1
Information in this document applies to any platform.

Goal

This Document provides a Method to let Clients connect to the new Primary Database after Data Guard Switchover or Failover.

Solution

On the Primary and Standby Servers, configure the Listener and start them.

listener.ora on Server one (Primary):
=========================
listener=
(description=
(address=(protocol = TCP)(host=one.world.com)(port=1521))
)


listener.ora on Server two (Standby):
===========================
listener=
(description=
(address=(protocol = TCP)(host=two.world.com)(port=1521))
)

NOTE: Do not use SID_LIST_ on the listener.ora File to let the Database to register itself with the correct Service Names.


(S)PFILE on Primary (one)
===================
service_names=(db_prod.world.com, db_stby.world.com)
log_archive_dest_2='SERVICE=dbtwo LGWR reopen=60 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'

(S)PFILE on Standby (two)
===================
service_names=db_stby.world.com
log_archive_dest_2='SERVICE=dbone LGWR reopen=60 valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)'

REMARK: The valid_for-Attribute was introduced in 10.x, so not possible in 9.x, use the log_archive_dest_state_2 instead to toggle enable/defer depending on the Role


Listener Status on Primary Server will show both the Primary Service, i.e. db_prod.world.com, and PhysicalSstandby Service, i.e. db_stby.world.com.
Listener Status on Standby Server will show Physical Standby Service, i.e. db_stby.world.com only.



tnsnames.ora on Primary & Standby:
=========================

dbone.world.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=one.world.com)(PORT=1521))
)
(CONNECT_DATA = (SERVICE_NAME = db_stby.world.com))
)


dbtwo.world.com=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=two.world.com)(PORT=1521))
)
(CONNECT_DATA = (SERVICE_NAME = db_stby.world.com))
)


db.world.com =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=TCP)(HOST=one.world.com)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=two.world.com)(PORT=1521))
)
(CONNECT_DATA = (SERVICE_NAME= db_prod.world.com))
)


*** so you'll always use db_prod.world.com Service to reach the current Primary Database.


NOTES:

1. After Switchover or Failover, reset the service_names Parameter, this can be achieved by creating an "On Database Open" Trigger, or by executing the following 'alter system' Commands (when using SPFILE):
- New Primary (the 'old Standby' on Node two):
SQL> alter system set service_names = 'db_prod.world.com, db_stby.world.com' scope=both;
- New Standby (the 'old Primary' on Node one)
SQL> alter system set service_names='db_stby.world.com' scope = both;

So if you want to explicity connect to one of the Instances you'd use dbone or dbtwo depending to which Instance you want to connect, but if you want to connect to the Primary Database no matter in which Server the Primary Database is runing you'd use the TNS-Alias db.

2. If you setup the local_listener and remote_listener on the Primary and Standby Database, all Listeners will know which Instances provide which Service(s).

3. If you are using the DataGuard Broker and DGMGRL, you have to configure a static Listener Entry for each Database in the SID_LIST_LISTENER (in Contrast to what is mentioned above). This Entry must contain a Line using the following Syntax:

GLOBAL_DBNAME = db_unique_name_DGMGRL.db_domain
(See DataGuard Broker Guide for Details)

This can still work with this Example as long as the SERVICE_NAME called by the Client-side tnsnames.ora matches the GLOBAL_DBNAME-Value configured in the SID_LIST_LISTENER Section.

4. Change the Names of the Servers, Domain and TNS-Aliases to meet your Standards and this Example should be enough.

You can also configure Automatic Client Failover. Please see fallowing Note for more info:

References

Note 405120.1 - Prerequisites Required For Automatic Client Failover Configurations

---