Client Connectivity in Data Guard Configuration

When you have a physical standby, you must make sure client connectivity is set properly so that when you perform failover or switchover, client must smoothly connect to the new primary.

Create a database service on primary: This service is created on primary database to connect proddb

exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ip7_ha', network_name => 'ip7_ha', failover_method => 'BASIC', failover_type => 'SELECT', failover_retries => 30, failover_delay => 10);

Make above service run only on primary: This service should run only on the primary database. Even when there is switchover or failover, this service should continue to run on new primary

create or replace procedure start_ha_service
is
v_role VARCHAR(30);
begin
select DATABASE_ROLE into v_role from V$DATABASE;
if v_role = 'PRIMARY' then
DBMS_SERVICE.START_SERVICE('ip7_ha');
else
DBMS_SERVICE.STOP_SERVICE('ip7_ha');
end if;
end;
/

Create trigger to start above service: We need to create trigger to start above service on database startup and also role change on primary

TRIGGER TO START SERVICE ON DB STARTUP:
=======================================
create or replace TRIGGER ha_on_startup
after startup on database
begin
start_ha_service;
end;
/

TRIGGER TO START SERVICE ON DB ROLECHANGE:
==========================================
create or replace TRIGGER ha_on_role_change
after db_role_change on database
begin
start_ha_service;
end;
/

Start the new service: Start the new service on primary

SQL> exec start_ha_service;
SQL> alter system archive log current;

Edit client tnsnames.ora: We need to enable client tns entries to access database via above service

ip7 =
  (DESCRIPTION =
    (ADDRESS_LIST=
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.171)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.172)(PORT = 1521))
    )
   (CONNECT_DATA = (SERVICE_NAME = ip7_ha)
     (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=30)(DELAY=10))
   )
  )