Transparent Application Failover Support

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 also fails. The next time the client tries to use the connection to execute a new SQL statement, for example, 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 OracleConnection.Failover event. 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.

Preparing and Running the Sample

The tnsnames.ora file should be suitably modified for your database entry so that TAF tries to reconnect when the database connection is lost. The tnsnames.ora file is located at <Oracle_Home>/network/Admin directory. Your database TNS entry should look like this :

<DBFAILOVER.US.ORACLE.COM> =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = <myhostname>)(PORT = <1521>))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = <dbfailover>)
      (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES=100)
      (DELAY=1))
    )
  )

where <Oracle_Home> is the directory where your database or SQL*Plus client is installed. Replace the values for the database parameters highlighted in bold with your database parameters.

Following example demonstrate using TAF in your application.

First register method as Failover event handler.

[C#]

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

[Visual Basic]

...
Dim conn As OracleConnection = new OracleConnection( _
   "User Id=scott;Password=tiger;Data Source=DBFAILOVER.US.ORACLE.COM")
...

Second add method that will handle TAF event.

[C#]

public void OnFailover(object sender, OracleFailoverEventArgs eventArgs)
{
  switch(eventArgs.State)
  {
    // failover begins
    case OracleFailoverState.Begin:
      MessageBox.Show("Failover begind");
    break;
    // failover is aborted
    case OracleFailoverState.Abort:
      MessageBox.Show("Failover aborted");
      break;
    // failover is complete successful
    case OracleFailoverState.End:
      MessageBox.Show("Failover End");
      break;
    //error occurs while reconnecting
    case OracleFailoverState.Error:
      // Retry reconnecting
      eventArgs.Retry = true;
      break;
    // reautentication is required during Failover
    case OracleFailoverState.Reauth:
      MessageBox.Show("Failover reauthenticating");
      break;
    // Something goes wrong
    default:
      MessageBox.Show("Unknown failover stage");
      break;
  }
}

[Visual Basic]

Private Sub OracleConnection_Failover(ByVal sender As Object, _
  ByVal eventArgs As OracleFailoverEventsArgs) Handles OracleConnection.Failover

  Select eventArgs.State
    ' failover begins
    Case OracleFailoverState.Begin
      MessageBox.Show("Failover begind")
    ' failover is aborted
    Case OracleFailoverState.Abort
      MessageBox.Show("Failover aborted")
    ' failover is complete successful
    Case OracleFailoverState.End
      MessageBox.Show("Failover End")
    ' error occurs while reconnecting
    Case OracleFailoverState.Error
      ' Retry reconnecting
      eventArgs.Retry = true
    ' reautentication is required during Failover
    Case OracleFailoverState.Reauth
      MessageBox.Show("Failover reauthenticating")
    ' Something goes wrong
    Case Else
      MessageBox.Show("Unknown failover stage")
  End Select
End Sub

When TAF occured this method will be called. And you can inform user about failover progress or ask user about reconnecting to Oracle instanse.

For demonstrating TAF, user should restart the database from SQL* Plus using following command:

• To login as a DBA user type

  SQL> Connect sys/<your_sys_password>@<Your_TNSName> as sysdba
  

• To shutdown and restart database type

  SQL> startup force
  

After restarting the database, the user should return to the application and refresh the data by clicking "RefreshRecords". The Failover event is called and the Failover handler method displays the appropriate messages in a message box. The query will be executed again against the database using a new connection, data fetched and displayed in the data grid.