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