Oracle DBA – A lifelong learning experience

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


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
role VARCHAR(30);

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.

5 Responses to “Using a service name with load balancing and standby databases”

  1. coskan said

    Hi John,

    Thank you very much for the answer.

    I asked the question because my previous company choose RAC for high availability without using any database services for load balancing (software was not suitable to do it). What we were doing was we point report users to node-B and oltp to node-A but this wasn’t a proper solution and after I left I heard that they went back active passive clustering and had better performance and availability. Like Robert mentioned in his presentation RAC for HA only most of the time, causing more downtime than single instances because of the bugs and problems.

    From my understanding of RAC to make most out of RAC architecture, using service based connections and load balancing is a good practice and Your presentation was a good customer study so I wondered how you were using RAC.

    From your answer I understand that you use database services for standby purposes which is a good practice as well.

    What I really wonder was if you are using database services for client connections to production RAC databases as well ?

  2. Sam said

    Hi, I appreciate this posting. We are attempting to impelement this same scenario however, since I am new a this and have two conflicting documents, your post and Oracles [ID 740029.1]. I was wondering if you could clarify some things? Isn’t this used to “failover” to a standby database if the primary goes down? How does the trigger fire, to switch the connection to the standby database, if the primary database is down? Or should these triggers and services be created on both the primary and the secondary db and whichever is up will start the service? I don’t see how a client would ever connect to the standby db based on your description. In the explination paragraph you state “We have a database trigger (MANAGE_DGSERVICES) that starts the service “ABCPRD” only if the database role is “Primary”.” However, the trigger starts ‘abc123’ if it is primary, not ‘ABCPRD’. Your help clarifying this would be greatly appreciated.

  3. paul said

    Hi, just a tiny typo:
    exec dbms_service.start_service(abc123′);

    needs a ‘ before abc123.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: