Setup the client Failover in Oracle DataGuard Environment

We need to setup the automatic client fail-over by using database services in Oracle Dataguard Environment. For automatic connectivity of the database in case of switchover or failover in Oracle Dataguard Environment.

From 11.2 version, we will be using role-based database services. You need to install Oracle Clusterware on both primary and standby Database.

Following are the steps with using SRVCTL command:

1. Modified the TNS entry in both Primary and Standby DB.

PRIM_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim_db)
)
)


STBY_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = ON)
(LOAD_BALANCE = OFF)
(ADDRESS = (PROTOCOL = TCP)(HOST = primary.oracle.com)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = standby.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stby_db)
)
)


2. Create PRIMARY services at both Primary and Standby Site.

On primary:
PRIM$ srvctl add service -d ora11gR2 -s prim_db -l PRIMARY -e SESSION -m BASIC -w 10 -z 10


On standby:
STANDBY$ srvctl add service -d sby11gR2 -s prim_db -l PRIMARY -e SESSION -m BASIC -w 10 -z 10


3. Create STANDBY Service on Both Primary and Standby site.

On primary:
PRIM$ srvctl add service -d ora11gR2 -s stby_db -l PHYSICAL_STANDBY -e SESSION -m BASIC -w 10 -z 10

On standby:
STANDBY$ srvctl add service -d sby11gR2 -s stby_db -l PHYSICAL_STANDBY -e SESSION -m BASIC -w 10 -z 10

4. Start primary service on Primary site of dataguard environment.

PRIM$ srvctl start service -d ora11gR2 -s prim_db


5. Start the Standby Service on Standby Site of Dataguard environment.

STANDBY$ srvctl start service -d sby11gR2 -s stby_db


In 11.1 or previous version, we are using trigger to configure the same thing which we configured with ROLE Based

--Create services on both
SQL> exec dbms_service.create_service('prim_db','prim_db');
SQL> exec dbms_service.create_service('stby_db','stby_db');

-- Start the Services on primary site
exec dbms_service.start_service('prim_db');

-- Start Service on Standby Site
exec dbms_service.start_service('stby_db');

--Create trigger to manage the services on both primary and standby:
CREATE OR REPLACE TRIGGER startDgServices after startup on database
DECLARE
db_role VARCHAR(30);
db_open_mode VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE, OPEN_MODE INTO db_role, db_open_mode FROM V$DATABASE;
IF db_role = 'PRIMARY' THEN DBMS_SERVICE.START_SERVICE('prim_db'); END IF;
IF db_role = 'PHYSICAL STANDBY' AND db_open_mode LIKE 'READ ONLY%' THEN DBMS_SERVICE.START_SERVICE('stby_db'); END IF;
END;
/


For more details follow the white paper link:

Click to access maa-wp-11gr2-client-failover-173305.pdf