This discussion is archived
6 Replies Latest reply: Feb 26, 2013 5:24 AM by MahirM.Quluzade RSS

Automatic switchover of client connections

SherrieK Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points