• Arun Kumar

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))
   )
  ) 
   

Recent Posts

See All

Oracle Data Guard Protection Modes

A Data Guard configuration always runs in one of three data protection modes (also called as redo transport rules): Maximum Protection Maximum Availability Maximum Performance (default mode of operati

Oracle Data Guard Physical Standby Configuration

There are various steps in which you can configure physical standby database. We need to make several changes to the primary database before we can even setup the standby database. This article applie

Convert Physical Standby into Active Data Guard

In very simple terms, when you open Physical Standby in read only mode, it is known as Active Data Guard. But, Active Data Guard needs license for and you must check with Oracle for same before implem

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback