Transparent application failover (TAF) is the ability of applications to automatically reconnect to the database if the connection fails. If the server fails, the connection fails as well. The next time the client tries to use the connection, e.g., to execute a new SQL statement, the operating system displays an error to the client. At this point, the user must log in to the database again. With TAF, however, Oracle automatically obtains a new connection to the database. This allows the user to continue to work using the new connection as if the original connection had never failed. If the client is not involved in a database transaction, then users may not notice the failure of the server. Because this reconnect happens automatically, the client application code may not need changes to use TAF. TAF automatically restores:

• Client-Server Database Connections;
• Users' Database Sessions;
• Executing Commands;
• Open Cursors Used for Fetching;
• Active Transactions;
• Server-Side Program Variables.

Unfortunately, TAF cannot automatically restore some session properties. If the application issued ALTER SESSION commands, then the application must reissue them after TAF processing is complete.

Frequently failure of one instance and failover to another takes time. Because of this delay, you may want to inform users that failover is in progress. Additionally, the session on the initial instance may have received some ALTER SESSION commands. These will not be automatically reissued on the second instance. You may need to reissue these commands on the second instance.

To address such problems, you can use the OracleConnection.Failover event. This event is raised during session recovery process when connection is lost. When connection failure is detected Failover event is raised first time. Then application keeps raising it until connection is restored or user stops failover process.

Transparent Application Failover Restrictions:

• All PL/SQL package states on the server are lost at failover.
• ALTER SESSION statements are lost.
• If failover occurs when a transaction is in process, then each subsequent call causes an error message until the user issues Rollback call. Then a success message is issued. Be sure to check this informational message to see if you must perform any additional operations.
• Continuing work on failed over cursors may cause an error message.
• If the first command after failover is not a SELECT statement or fetch operation, an error message results.
• Failover only takes effect for Oracle 8.0 or higher.
• At failover time, any queries in progress are reissued and processed again from the beginning. This may result in the next query taking a long time if the original query took a long time.
• This feature works only for server shutdown. It doesn't help to resolve connection problems caused by network.

Preparing and Running the Sample

To enable TAF on the Oracle server, the corresponding entry in the tnsnames.ora file should be modified. The tnsnames.ora file is located at <Oracle_Home>\network\Admin directory where <Oracle_Home> is the directory where your Oracle database or client is installed. For example, the TNS entry for the server with TAF may look like this:

<DBFAILOVER> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = ORCL)
      (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES=100))
    )
  )

For the detailed information on possible subparameters of FAILOVER_MODE, refer to the Oracle documentation

The following sample shows how TAF may be used. First, we create a connection and subscribe a method for the Failover event of this connection:

First register method as Failover event handler.

...
OracleConnection conn = new OracleConnection(
   "User Id=scott;Password=tiger;Data Source=DBFAILOVER.US.ORACLE.COM");
conn.Failover += new OracleFailoverEventHandler(conn_Failover);
...

...
Dim conn As OracleConnection = new OracleConnection( _
   "User Id=scott;Password=tiger;Data Source=DBFAILOVER.US.ORACLE.COM")
AddHandler conn.Failover, New OracleFailoverEventHandler(AddressOf conn_Failover)
...

To demonstrate the failover, we break the connection at this moment (see below how to do so). We try to execute a simple query, and the failover process starts:

...
OracleCommand cmd = new OracleCommand("SELECT * FROM dept", conn);
cmd.ExecuteReader();
...

...
Dim cmd As New OracleCommand("SELECT * FROM dept", conn)
cmd.ExecuteReader()
...

public void conn_Failover(object sender, OracleFailoverEventsArgs eventArgs)
{
  switch(eventArgs.State)
  {
    // The connection was lost and the failover started
    case OracleFailoverState.Begin:
      Console.WriteLine("Failover began");
    break;

    // Failover was aborted
    case OracleFailoverState.Abort:
      Console.WriteLine("Failover aborted");
      break;

    // Failover completed successfully
    case OracleFailoverState.End:
      Console.WriteLine("Failover ended");
      break;

    // An error occurred while reconnecting
    case OracleFailoverState.Error:
      // Retry re-establishing the connection
      eventArgs.Retry = true;
      Console.WriteLine("An error occurred, trying again");
      break;

    // Reautentication is required during failover
    case OracleFailoverState.Reauth:
      Console.WriteLine("Reauthenticating");
      break;

    // Unknown stage, may indicate a failover internal error
    default:
      Console.WriteLine("Unknown failover stage");
      break;
  }
}

Private Sub conn_Failover(ByVal sender As Object, _
   ByVal eventArgs As OracleFailoverEventsArgs) 

  Select eventArgs.State
  
    ' The connection was lost and the failover started
    Case OracleFailoverState.Begin
      Console.WriteLine("Failover began");

    ' Failover was aborted
    Case OracleFailoverState.Abort
      Console.WriteLine("Failover aborted")

    ' Failover completed successfully
    Case OracleFailoverState.End
      Console.WriteLine("Failover End")

    ' An error occurred while reconnecting
    Case OracleFailoverState.Error
      ' Retry re-establishing the connection
      eventArgs.Retry = true
      Console.WriteLine("An error occurred, trying again")

    ' Reautentication is required during failover
    Case OracleFailoverState.Reauth
      Console.WriteLine("Reauthenticating")

    ' Unknown stage, may indicate a failover internal error
    Case Else
      Console.WriteLine("Unknown failover stage")

  End Select
End Sub

When TAF occurs, this method is called. Different values of the State event argument allow to perform appropriate actions on the corresponding stage of the failover process. E.g., it is possible to inform user that the connection failed and is re-establishing, or ask whether it is necessary to reconnect, or ask for credentials if re-authorizing is necessary. Provided that failover succeeds, the query will be executed again with a new connection.

To try TAF, you need to break the connection after it is opened, e.g., by shutting down the Oracle server. This can be done with the SQL*Plus tool. First, it is necessary to login as a user with SYSDBA privileges:

SQL> Connect sys/<password>@<TNSName> as sysdba

Here <password> and <TNSName> should be replaced with the password of the sys user and the TNS name of the server. To shutdown the server, run the command

SQL> shutdown immediate

(the 'immediate' keyword is necessary to tell the server that it should not wait for the pending connection we've opened before). To restart the server, run

SQL> startup 

See Also

OracleConnection.Failover event  | Devart.Data.Oracle Namespace