This discussion is archived
3 Replies Latest reply: Aug 15, 2011 11:24 PM by mseberg RSS

transparent failover to application

849883 Newbie
Currently Being Moderated
Hi,

I am working on a system with 11gR2 data guard. Both production site and DR site are two nodes RAC.
To minimize the RTO (return to operation), we are expecting the service name is configurable to be transparent to our application. When service name for production and DR database are named differently, each time when planned or unplanned failover, the application has to update the oracle client tnsnames.ora and connection pool detail, etc.

Any advice?
  • 1. Re: transparent failover to application
    mseberg Guru
    Currently Being Moderated
    There's an easier way to do this :

    You tnsnames.ora needs changing
    ernie =
     (DESCRIPTION =
        (ADDRESS_LIST =
           (ADDRESS = (PROTOCOL = TCP)(HOST = pdbuserv04.uservices.umn.edu)(PORT = 1521))
           (ADDRESS = (PROTOCOL = TCP)(HOST = bdbuserv01.uservices.umn.edu)(PORT = 1521))
           )
           (CONNECT_DATA =
           (SERVICE_NAME = ernie)
        )
     )
    Next the service 'ernie' needs to be created manually on the primary database and started.
    BEGIN
     
       DBMS_SERVICE.CREATE_SERVICE('ernie','ernie');
    
    END;
    /
    
    
    
     DBMS_SERVICE.START_SERVICE('ernie');
    Several of the default parameters can now be set for 'ernie'.
    BEGIN
       DBMS_SERVICE.MODIFY_SERVICE
       ('ernie',
       FAILOVER_METHOD => 'BASIC',
       FAILOVER_TYPE => 'SELECT',
       FAILOVER_RETRIES => 200,
       FAILOVER_DELAY => 1);
    END;
    /
    Finally a database STARTUP trigger should be created to ensures that this service is only offered if the database is primary.

    CREATE TRIGGER CHECK_ERNIE_START AFTER STARTUP ON DATABASE
    DECLARE
     V_ROLE VARCHAR(30);
    BEGIN
     SELECT DATABASE_ROLE INTO V_ROLE FROM V$DATABASE;
     IF V_ROLE = 'PRIMARY' THEN
     DBMS_SERVICE.START_SERVICE('ernie');
     ELSE
     DBMS_SERVICE.STOP_SERVICE('ernie');
     END IF;
    END;
    /
    Search B19306_01 for 'ernie' and more details.

    Best Regards

    mseberg
  • 2. Re: transparent failover to application
    849883 Newbie
    Currently Being Moderated
    Thanks a lot first.

    This seems both the production and DR need to have exact same service name. Need to test it myself. Thanks.
  • 3. Re: transparent failover to application
    mseberg Guru
    Currently Being Moderated
    This seems both the production and DR need to have exact same service name.
    Kind of. They could be "Primary" and "Standby". "ernie" is what the customer sees. An extra entry.

    So a "lsnrctl status" would show both.


    Service "ernie" has 1 instance(s).
    Instance "PRIMARY", status READY, has 1 handler(s) for this service...

    You can use DBMS_SERVICE.CREATE_SERVICE to create several aliases for the same database.

    Best Regards

    mseberg