Using a service name with load balancing and standby databases

This one is for Coskan

At the UKOUG SIG last week when I was presenting, Coskan asked a question ‘do you use RAC services’. I prevaricated a bit because I thought that there must be more to the question than a simple yes or no.

Anyway, the answer is yes and I will post a trigger that we use when we switch from primary to Dataguard standby

Firstly the tnsnames entry where the first 2 servers are primary and the next 2 are the standby servers

ABCPRD =
(DESCRIPTION =
(ADDRESS_LIST =
(FAILOVER = YES)
(ADDRESS = (PROTOCOL = TCP)(HOST = abc01-vip.unix.plc.net)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = abc02-vip.unix.plc.net)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = abc03-vip.unix.plc.net)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = abc04-vip.unix.plc.net)(PORT = 1522))
(LOAD_BALANCE = YES)
)
(CONNECT_DATA =
(SERVICE_NAME = ABCPRD )
)
)

We have a database trigger (MANAGE_DGSERVICES) that starts the service “ABCPRD” only if the database role is “Primary”. Therefore, the service is only ever available on the correct node(s). Note that we originally wanted to use an underscore but one of the Apps DBAs advised that he has seen an underscore cause problems. This appears to be because application servers seem to translate an underscore (ASCII code 95) to %95%. This is probably a bug but we decided not to use the underscore in the service name.

To create the service

exec dbms_service.create_service('abc123,'abc123');
exec dbms_service.start_service('abc123');

CREATE OR REPLACE TRIGGER manage_DGservice after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('abc123');
END IF;
END;
/

Maybe Coskan will come back and ask the question he was intending to get an answer to. I hope he does and even more so I hope I can answer it.