Automating Client Failover in a Data Guard Configuration

We target is preventing clients from connecting
to the wrong Database when failover occurs.

we use database services to prevent clients from connecting to the wrong database in the Data Guard configuration
we can arrange client tnsnames.ora so Clients connect to database services instead of database instances
Database services register with listeners
we creating services for the data guard configuration databases
DBMS_SERVICE.CREATE_SERVICE( -
SERVICE_NAME => 'PROD', -
NETWORK_NAME => 'PROD', -
FAILOVER_METHOD => 'BASIC', -
FAILOVER_TYPE => 'SELECT', -
FAILOVER_RETRIES => 120, -
FAILOVER_DELAY => 1);

DBMS_SERVICE.CREATE_SERVICE( -
SERVICE_NAME => 'STBY', -
NETWORK_NAME => 'STBY');

DBMS_SERVICE.CREATE_SERVICE( -
SERVICE_NAME => 'LSBY', -
NETWORK_NAME => 'LSBY');

we use Use a database event trigger to ensure that clients connect to a database in the Data Guard configuration that is in the correct state and role

CREATE TRIGGER MANAGE_SERVICES AFTER STARTUP ON DATABASE
DECLARE
ROLE VARCHAR(30);
OMODE VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO ROLE FROM V$DATABASE;
SELECT OPEN_MODE INTO OMODE FROM V$DATABASE;
IF ROLE = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE ('PROD');
ELSIF ROLE = 'PHYSICAL STANDBY' THEN
IF OMODE = 'READ ONLY' THEN
DBMS_SERVICE.START_SERVICE ('STBY');
END IF;
ELSIF ROLE = 'LOGICAL STANDBY' THEN
DBMS_SERVICE.START_SERVICE ('LSBY');
END IF;
END;


Use the trigger to start database services
PROD: Primary database
STBY: Physical standby database opened in READ ONLY mode
LSBY: Logical standby database


configure client tnsnames.ora
PROD = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = PROD)))

STBY = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = STBY)))


LSBY = (DESCRIPTION =
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS=(PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = LSBY)))

Relocating database services to the new primary database as part of a failover operation