TNSNAMES.ORA
PROD=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=DG_PROD)))

Use DBMS_SERVICE package to manage database services.

DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => ‘DG_PROD’,
NETWORK_NAME => ‘DG_PROD’,
FAILOVER_METHOD => ‘BASIC’,
FAILOVER_TYPE => ‘SELECT’,
FAILOVER_RETRIES => 180,
FAILOVER_DELAY => 1);

DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => ‘DG_RTQ’,
NETWORK_NAME => ‘DG_RTQ’);

DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => ‘DG_LSBY’,
NETWORK_NAME => ‘DG_LSBY’);

DBMS_SERVICE.CREATE_SERVICE(
SERVICE_NAME => ‘DG_SNAP’,
NETWORK_NAME => ‘DG_SNAP’);

The CREATE_SERVICE procedure creates a service in the data dictionary.

Use a database event trigger to ensure clients connect to a database in the data guard configuration that is in the correct state and role.
Use the trigger to start database services
– DG_PROD Primary db
– DG_RTQ  Physical standby db in READ ONLY mode (real-time query)
– DG_SNAP snapshot standby database
– DG_LSBY Logical standby db

TRIGGER
=======
CREATE OR REPLACE TRIGGER MANAGE_SERVICES AFTER STARTUP ON DATABASE
DECLARE
ROLE VARCHAR(30);
OMODE VARCHAR(30);
BEGIN
SELECT DATABASE ROLE INTO ROLE FROM V$DFATABASE;
SELECT OPEN_MODE INTO OMODE FROM V$DATABASE;
IF ROLE = ‘PRIMARY’ THEN
DBMS_SERVICE.START_SERVICE(‘DG_PROD’);
ELSEIF ROLE = ‘PHYSICAL_STANDBY’ THEN
IF OMODE = ‘READ ONLY’ THEN
DBMS_SERVICE.START_SERVICE(‘DG_RTQ’);
END IF;
ELSEIF ROLE = ‘LOGICAL STANDBY’ THEN
DBMS_SERVICE.START_SERVICE(‘DG_LSBY’);
ELSEIF ROLE = ‘SNAPSHOT STANDBY’ THEN
DBMS_SERVICE.START_SERVICE(‘DG_SNAP’);
END IF;
END;
/

TNSNAMES.ORA
============
PROD=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=DG_PROD)))

RTQ =(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=DG_RTQ)))

SNAP=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=DG_SNAP)))

LSBY=(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=NODE2)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=DG_LSBY )))

CLIENT FAILOVER – COMPONENTS
============================
1 CONNECT TIME FAILOVER:
Redirects failed connection requests to a secondary listener
2 TAF Transparent Application Failover:
Enables OCI client applications to automatically reconnect to a database if the original connection fails. TAF fails over only the session and SELECT statements.
Select statemets are automatically restarted if TAF is configured for SELECT failover.
Session customisations (e.g. ALTER SESSION) must be reexecuted by the application.
3 FAN Fast Application Notification
Provides quick notification when a resource (instance, node service etc) fails.
FAN is available to all applications by using either fast connection failover with a FAN integrated client(JDBC, OCI or OLE DB) or by using the FAN API to read FAN events directly.
4 Fast Connection Failover
by enabling you to configure FAN integrated JDBC clients to automatically subscribe to FAN high-availability events and react to service, instance and database up and down events.
5 DB_ROLE_CHANGE system event
Fired when the primary db is first opened after a dg role transition occurs. Using this system event you can write a trigger to perform post-role change actions.

CLIENT FAILOVER – BEST PRACTICES
================================
Configure OCI clients for FAN OCI
– AQ_HA_NOTIFICATIONS
Configure DBC clients for fast connection failover and FAN ONS
– ONS daemons on primary and standby clusters
– JDBC client uses remote subscription to all daemons
Implement fast ADDRESS_LIST transversal
– OCI: OUTBOUND_CONNECT_TIMEOUT
– JDBC: SQLnetDef.TCP_CONNTIMEOUT_STR

AUTOMATING FAILOVER FOR OCI CLIENTS
===================================
1 Ensure the configuration is managed by data guard broker.
2 Use DBMS_SERVICE.CREATE_SERVICE to create a db service, enable high-availability notification and configure server-side TAF settings.
3 Create a trigger on system startup to relocate db services after a role transition
4 Create an Oracle Net Service name that includes an address entry for the primary and all standby database hosts.
ON OCI CLIENTS

Use the OCI_EVENTS parameter to initialize the environment so that OCI clients receive FAN notifications.
– OCIEnvCreate(…OCI_EVENTS…)
– see the Oracle Call Interface Programmers guide for additional information

.Link the OCI client applications with the thread library libthread or libpthread
.Set the SQLNET.OUTBOUND_CONNECT_TIMEOUT in SQLNET.ORA to 3 seconds
.Register an event callback that is invoked when a high-availability event occurs.

– see the Oracle Call Interface Programmers guide for additional information

AUTOMATING FAILOVER FOR OLE DB CLIENTS
======================================
1 Ensure the configuration is managed by data guard broker.
2 Use DBMS_SERVICE.CREATE_SERVICE to create a db service, enable high-availability notification and configure server-side TAF settings.
3 Create a trigger on system startup to relocate db services after a role transition

CONFIGURE OLE DB CLIENTS FOR FAILOVER
=====================================
1 Set the DBNotifications and DBNotificationPort OraOLEDB connection string attributes
2 Set the SQLNET.OUTBOUND_CONNECT_TIMEOUT to 3 in SQLNET.ORA

AUTOMATING FAILOVER FOR JDBC CLIENTS
======================================
Because JDBC clients use FCF rather than TAF, db services for JDBC clients are not configured for AQ HA events. Instead a trigger is required to notify JDBC clients when a data guard failover occurs.
1) Use DBMS_SERVICE.CREATE_SERVICE to create the db service
2) Configure ONS in $ORACLE_HOME/opmn/conf on all hosts that may contain a primary database.
refer Oracle Database JDBC developers guide
3) Start the ONS daemon
4) Create the service trigger to relocate the db service after a role transition
5) Create a trigger on the DB_ROLE_CHANGE system event that calls a C program named the FAN ONS Publisher.
JDBC clients are notified of the primary site failure and instructed to reconnect to the new primary database.
refet to the white paper “Client Failover best practices fo Highly Available Oracle Databases: Oracle Database 10g Release 2”

CONFIGURING JDBC CLIENTS FOR FAILOVER
=====================================
1) Set “FastConnectionFailoverEnabledDatasource” property to true so the client app uses implicit JDBC connection cache on its datasource
2) Set oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR to 3 seconds on the data source.
This property enables the JDBC client to quickly traverse an address list in the event of a failure.
3) Create an oracle net service name that includes an ADDRESS entry for the primary db host and all standby hosts
4) Configure an remote ONS subscription on the JDBC client so that an ONS daemon is not required on the client. The remote ONS subscription should contain all hosts that can become a primary db.
5) Enable SSL fo communications.