RAC Services: How do you manage TAF services in dataguard environment, manually? No more with 11gR2

In RAC, you may all know about a TAF services which gives the failover capability to availability instances when the preferred failed. But how does this can work in when you have a Dataguard Environment with Primary and standby.

Well answer is until 10g/11gR1 create a trigger like below and manage the service based upon the instance role.

Creating a service

exec DBMS_SERVICE.CREATE_SERVICE(service_name =>’taf’,network_name =>’taf’,aq_ha_notifications =>true,failover_method =>’BASIC’,failover_type  =>’SELECT’,failover_retries =>180,failover_delay => 5);

and have database level trigger to start service in case database role switch happens:

CREATE OR REPLACE TRIGGER set_rc_svc AFTER DB_ROLE_CHANGE ON DATABASE DECLARE
role VARCHAR(30);

BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = ‘PRIMARY’ THEN
DBMS_SERVICE.START_SERVICE(‘taf’);
ELSE
DBMS_SERVICE.STOP_SERVICE(‘taf’);
END IF;
END;
/

Again 11gR2, changed this and you do not need this trigger any more, called Role based Service Management where you can manage the services with their database roles (Primary/Standby) and they can start automatically as they switch as you can create a service for standby, Clusterware will handle role change. Srvctl has new option where you can specify the database role.

How to do it? 

Note: Oracle Notification Service ( ONS) and ONS for Enterprise Manager (ONS) must be enabled

A high level steps:-

Step 1

a) Create production service

srvctl add service -d prmy-s prod -l PRIMARY -m BASIC -e SELECT -w 1 -z 180

Step 2

a) Start service

srvctl start service -d prmy -s prod

Step 3

a) Add standby database to the srvctl on standby node.

srvctl add database -d sby1 -o /u01/app/oracle/product/11.2.0/dbhome_1 -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilepc10sby1.ora -r PHYSICAL_STANDBY -a "SBDAT,SBFRA"

srvctl config database -d sby1

Step 4

a) Create the production service on the standby node

srvctl add service -d sby -s prod -l PRIMARY -m BASIC -e SELECT -w 1 -z 180

Step 5

Create the service entries in the tnsnames on both nodes

Add service entry on the primary node

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXX)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXX)(PORT = 12001))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)

Add service entry on the standby node

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXX)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = XXXXXX)(PORT = 12001))
)
(CONNECT_DATA =
(SERVICE_NAME = prod)
)

Hope this helps

-Thanks

Geek DBA