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 reconnection happens automatically, the client application code may not need changes to use TAF. TAF automatically restores:
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 TOraSession.OnFailover event. The event is raised during the session recovery process when connection is lost. When connection failure is detected TOnFailover event is raised first time. Then application keeps raising it until connection is restored or user stops failover process.
Transparent Application Failover Restrictions:
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 in the <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.
procedure TfmMain.OraSessionFailover(Sender: TObject; FailoverState: TFailoverState; FailoverType: TFailoverType; var Retry: Boolean); begin case FailoverState of fsBegin: begin ShowMessage('Failover Begin'); StatusBar1.Panels.Text := 'Trying to reconnect, Please wait...'; end; fsAbort: begin ShowMessage('Failover Aborted'); StatusBar1.Panels.Text := 'Failover Aborted'; end; fsEnd: ShowMessage('Failover End'); fsError: begin StatusBar1.Panels.Text := 'Failover Error. Retrying to connect ' + 'to database. Please wait... '; Retry:=true; end; fsReauth: begin ShowMessage('Failover reauthenticating'); StatusBar1.Panels.Text := 'Failover reauthenticating'; end; else ShowMessage('Bad Failover'); StatusBar1.Panels.Text := 'Bad Failover'; end; end;
When run, the sample shows a form with a blank data grid. The user should click "Open" button to start fetching the Dept table records.
For demonstrating TAF, user should restart the database from SQL* Plus using following command:
SQL> Connect sys/<your_sys_password>@<Your_TNSName> as sysdba
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 and in the status bar of application. The query will be executed again against the database using a new connection, data fetched and displayed in the data grid.