Client Connectivity in Data Guard Configuration | HA service

Client Connectivity in Data Guard Configuration

create database service on primary database server

exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ddb_ha', network_name => 'ddb_ha');

create or replace procedure start_db_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('ddb_ha');

else

DBMS_SERVICE.STOP_SERVICE('ddb_ha');

end if;

end;

/

TRIGGER TO START SERVICE ON DB STARTUP:

create or replace TRIGGER db_hasrv_on_startup

after startup on database

begin

start_db_ha_service;

end;

/

TRIGGER TO START SERVICE ON DB ROLECHANGE:

create or replace TRIGGER db_hasrv_on_role_change

after db_role_change on database

begin

start_db_ha_service;

end;

/

Start the new service on primary

SQL> exec start_db_ha_service;

SQL> alter system archive log current;

SQL>

SQL>

SQL> exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ddb_ha', network_name => 'ddb_ha');

PL/SQL procedure successfully completed.

SQL> create or replace procedure start_db_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('ddb_ha');

else

DBMS_SERVICE.STOP_SERVICE('ddb_ha');

end if;

end;

/  2    3    4    5    6    7    8    9   10   11   12

Procedure created.

SQL> create or replace TRIGGER db_hasrv_on_startup

after startup on database

begin

start_db_ha_service;

end;

/

  2    3    4    5    6

Trigger created.

SQL>

SQL> create or replace TRIGGER db_hasrv_on_role_change

after db_role_change on database

begin

start_db_ha_service;

end;

/  2    3    4    5    6

Trigger created.

SQL>

SQL>

SQL> exec start_db_ha_service;

PL/SQL procedure successfully completed.

SQL>

SQL> alter system archive log current;

System altered.

SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

SQL>

tnsnames.ora (client site)

DDB =

  (DESCRIPTION =

    (ADDRESS_LIST=

      (ADDRESS = (PROTOCOL = TCP)(HOST = 9.82.83.159)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 72.471.436.842)(PORT = 1521))

    )

   (CONNECT_DATA = (SERVICE_NAME = ddb_ha)

     (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=60)(DELAY=20))

   )

  )