Using a service name with load balancing and standby databases
Posted by John Hallas on January 26, 2010
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
(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)
(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
CREATE OR REPLACE TRIGGER manage_DGservice after startup on database
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
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.