3 Replies Latest reply: Aug 16, 2011 1:24 AM by mseberg RSS

    transparent failover to application

    849883
      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
          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
            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
              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