6 Replies Latest reply: Feb 26, 2013 7:24 AM by Mahir M. Quluzade RSS

    Automatic switchover of client connections

    SherrieK
      Oracle RAC 11.1.0.7
      Services always used for FAN / TAF client connections
      Dataguard using DG Broker
      <instance>_dgmgrl statically defined in listener.ora; <instance> is dynamically registered


      I have a primary and standby database. Services are defined in the database with srvctl and we see them starting up when the standby becomes the new primary.
      Our services use FAILOVER_TYPE=SELECT.

      As a test we use TOAD as the client, using the TNS entry of the service. If we initiate a query in TOAD and allow it to complete and then switchover, and then reissue the query, TOAD automatically reconnects and executes the query. We don't receive an error. Some end-user interaction was necessary.

      If we issue a long-running query, and as it is executing switchover, the session gets disconnected, we receive ORA-3113 end of communication and we must physically reconnect and reissue the query.

      We expected that by using failover type of SELECT, the session would switchover to the new primary, the select would be reissued automatically and pick up where it left off on the old primary. Switchover would be graceful and no end user action would be required.

      Is this expectation wrong?

      Here is our tns entry and service definition.
      DIRECT_APP_B =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = racsitea01-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = racsitea02-vip)(PORT = 1521))
      (LOAD_BALANCE = YES)
      )
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = racsiteb01-vip)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = racsiteb02-vip)(PORT = 1521))
      (LOAD_BALANCE = YES)
      )
      (CONNECT_DATA =
      (SERVICE_NAME = DIRECT_APP_B.ad.swfwmd.net)
      )
      )

      failover failover clb
      name method type FAILOVER_RETRIES FAILOVER_DELAY goal enabled AQ_ goal
      ------------------------- -------- -------- ---------------- --------------- ------------ ------- --- -----
      DIRECT_APP_B BASIC SELECT 15 5 SERVICE_TIME NO YES SHORT
        • 1. Re: Automatic switchover of client connections
          mseberg
          Hello;

          Take a look at this :

          Connect Time Failover & Transparent Application Failover for Data Guard

          http://uhesse.com/2009/08/19/connect-time-failover-transparent-application-failover-for-data-guard/

          Best Regards

          mseberg
          • 2. Re: Automatic switchover of client connections
            SherrieK
            Hello, thank you for the link.

            As in Our goal is to have a Runtime Failover completely transparent to the client, we define our service in the primary database as shown in the article.

            Our TNS entry is similar to UWE's, but because we are using RAC we group the nodes of a cluster in an address list.

            We are 11gR1 and use srvctl to create our services. We see that the CRS brings up the services when the standby becomes the primary. UWE also noted in some of his comments that this is a feature in 11g and the startup triggers are no longer needed.

            We are a physical standby, I forgot to mention that earlier.

            It looks like we are doing everything that we should. When we first started this project, DG seemed hard. As with anything, now that we understand it, our implementation seems simple.

            I'm hoping that someone, somewhere, has had a similar response and can provide some insight.

            To reiterate:
            We are running RAC with DG Broker (11.1.0.7), define services with srvctl on the primary. We also add those services on the standby but they don't get started until switchover. All client connections are through the service name, which is defined in a TNS entry.

            When there is an active session on the primary and we switchover, the client gets an ora-3113 end of file communication error and it we must reconnect and reissue the statement. This is our problem.

            Any insights?
            • 3. Re: Automatic switchover of client connections
              389403
              Don't use TOAD as client for tests, use SQL*Plus and it will work. Also before testing check in the v$session for the failover_type and failover_method for Your session.
              Also adjust the DELAY and RETRIES parameters.

              Note:
              In order for a client application to use TAF, it must be written to use Oracle Call Interface Version 8.
              SQL*Plus®, ODBC and JDBC drivers, Oracle’s .NET provider, and the Pro*C™ Precompiler all use Oracle Call
              Interface Version 8 and will support TAF. Any third party application written to use Oracle Call Interface
              Version 8 can also support TAF.

              Good luck.

              Edited by: Aychin T. Gasimov on Feb 22, 2013 7:26 PM
              • 4. Re: Automatic switchover of client connections
                SherrieK
                Thank you Aychin
                We've reverted to testing with SQLPlus as suggested. Something that I am confused about, though, is that TOAD uses Oracle NET with tnsnames, the reason that we were thinking we should see failover there. I'm seeing that it's not that using tnsnames, it's how the application is written. We'll just have to test every app.

                I've also seen references about additional exception handlers that should be added to code to handle the failover once it happens, but haven't found a concise description of what clients and what needs to happen to make them resilient.

                Any information about clients, whether they be ODP or Java is appreciated.
                • 5. Re: Automatic switchover of client connections
                  389403
                  Application can use Oracle NET but there is also some requirements for OCI applications to benefit from TAF.
                  Also consider that TAF is works only for OCI applications. For Java thin clients You will need to use FCF (Fast Connection Failover).
                  FCF depends on ONS events, that is why it must be properly configured to receive this events. Also consider that FCF can be applied
                  if You are using Connection Cache or in 11g UCP (Universal Connection Pool).
                  But anyway Your application must be ready to handle such error messages like "Closed connection" if failover occurs in the middle of some transaction, consider that transaction will be automatically rolled back.
                  You just need to handle such kind of errors and retry the operation or get new connection from pool.
                  For read-only transactions failover is transparent.
                  If You are not using Oracle JDBC driver or not using UCP then, You can handle this by error handlers. In case of connection errors just try reconnect.
                  • 6. Re: Automatic switchover of client connections
                    Mahir M. Quluzade
                    Hi,

                    I read all posts in this thread. It is interesting thread.
                    I want share : http://www.mahir-quluzade.com/2012/05/oracle-data-guard-11g-overview-client.html
                    There I showed on video, how to configure TAF for data guard configuration. But all of my test on SQL*Plus.

                    Regards
                    Mahir M. Quluzade

                    p.s. Thanks to Aychin for valuable information.