RAC provides near-continuous availability by hiding failures from end-user clients and application server clients. This provides continuous, uninterrupted data access. Transparent Application Failover (TAF) is what applications use to sync up with Oracle RAC availability.TAF in the database reroutes application clients to an available database node in the cluster when the connected node fails. Application clients do not see error messages describing loss of service.
In the above configuration, if the users connection to Node 1 dies, their transaction is rolled back but they can continue work without having to manually reconnect. To get a good understanding of how the TAF architecture works, it is helpful to walk through a failover scenario where a user is querying the database to retrieve 1000 rows from the database. Assume that the user is connected to Node 1/ Instance 1. By following the steps identified in the above Figure:
- The heartbeat mechanism between the various nodes in the cluster checks to see if the other node is available and is participating in the cluster configuration.
- Let’s assume that a user on his/her terminal performs a query against the database to retrieve 1000 rows from the database via Instance 1.
- The initial 500-rows are retrieved from the database via Instance 1 and returned to the user for browsing through his/her interface.
- While the user is browsing through the first 500 rows through his browser, Node 1 fails and does not participate in the clustered configuration.
- Node 2 checks for the heartbeat of the other participating nodes deducts that Node 1 is not available.
- While the user is unaware of the failure and scrolls through the remaining 500 rows on the window. The process try’s to connect to Instance 1 but detects that the Instance is not available.
- Using the entries in the tnsnames.ora file the user connection to the other available node is established.
- User is now connected to Node 2.
- Oracle re-executes the query using the connection on Instance 2 and displays the remaining rows to the user. If the data was available in the buffer cache, the rows are returned back to the user instantaneously. However, if the rows are not available, Oracle has to perform an I/O operation. This would be delayed until the recovery process has completed.
In the above figure when Node 1 fails, any SELECT statements that had partially executed on Instance 1 are migrated as part of the failover process and are displayed through Instance 2 when the user process fails over to Node 2. All this happens transparently without any interruption to the user. It should be noted that along with the ‘SELECT’ statement, the following are also failed over:
- Client/server connection.
- User session state.
- Prepared statements.
- Active cursors that have begun to return results to the user.
The benefits that such a feature adds in meeting the high availability requirements in today’s machine critical applications are overwhelming and the first question that arises is this, why did Oracle not introduce such a feature before or why is this feature not available among other databases? Though the mechanism is very useful in meeting today’s high availability requirements, implementing such a feature is complex, basically because the database connections are not stateless. This means that during the moment of failure, the database, the users and the transactions are in a specific state of operation, such as:
- Retrieving data from the database
- Database has a connection via Oracle Net to the Instance on Node 1
- User connecting to the database has password and other user-authentication information
- The session has language and character set information that is specific to the Instance on which the user has established connection
- There are cursors open and in execution
- ‘SELECT’ cursors are open and partially scrolled by the user
- INSERT, UPDATE, and DELETE statements and PL/SQL procedures are being executed
It should be noted from the scenarios above that only SELECT statements are failed over from one node to another; transactional statements are not failed over by configuration of TAF. However, transactional statements can programmatically be transferred from Node 1 to Node 2 by proper validation of Oracle returned error messages and taking appropriate actions. This should be a preferred method to avoid any user interruptions as well as keeping the database or system failures transparent to the user. Among the transactional statements, the following do not failover or are not protected when a node fails:
- PL/SQL server-side package variables
- Global temporary tables
- Effect of any ALTER SESSION statements
- Applications not using OCI8 and above
- Applications not using the JDBC thick driver
- Transactional statements, i.e. Statements that include INSERT, UPDATE and DELETE operations
While the failover is in process, it is user friendly to inform the user via the application interface that the activity or command issued may take some time. This information could be communicated by validating the various error messages returned by Oracle as part of the node and connection failure. Some of the common Oracle error codes that should be handled by the application to track and transfer transactional statements include:
- ORA-01012: not logged on to Oracle
- ORA-01033: Oracle initialization or shutdown in progress
- ORA-01034: ORACLE not available
- ORA-01089: immediate shutdown in progress – no operations are permitted
- ORA-03113: end-of-file on communication channel
- ORA-03114: not connected to ORACLE
- ORA-12203: TNS: unable to connect to destination
- ORA-12500: TNS: listener failed to start a dedicated server process
- ORA-12571: TNS: packet writer failure
Configuring the TAF option involves adding Oracle Net parameters to the tnsnames.ora and, when one of the participating nodes encounters failure, the use of parameter values to ascertain the next step in the failover process. The parameter that drives the TAF option is the FAILOVER_MODE under the CONNECT_DATA section of a connect descriptor. By using one or more the following sub parameters, the full functionality of TAF can be obtained:
|BACKUP||Specifies a different net service name to be used to establish the backup connection. A backup should be specified when using PRECONNECT to pre-establish connections. Specifying a BACKUP is strongly recommended for BASIC methods; otherwise, reconnection may first attempt the Instance that has just failed, adding additional delay until the client reconnects.|
|TYPE||Specifies the type of failover. Three types of Oracle Net failover functionality are available by default to the Oracle Call Interface.
|METHOD||Determines the speed of the failover from the primary to the secondary or backup node.
|RETRIES||Specifies the number of times to attempt to connect to the BACKUP node after a failure before giving up.|
|DELAY||Specifies the amount of time in seconds to wait between attempts to connect to the BACKUP node after a failure before giving up.|
Another important parameter, or value, that should not be configured manually is the GLOBAL_DBNAME parameter in the SID_LIST_listener_name section of the listener.ora. Configuring this parameter in listener.ora disables TAF. If the GLOBAL_DBNAME parameter has been defined, the parameter should be deleted and the database should be allowed to dynamically register the global database names automatically.
The TAF option can be implemented in one of three ways:
- Connect-Time Failover and Client Load Balancing.
- Retrying a Connection.
- Pre-Establishing a Connection.
Through the examples below the various implementations options are explained.
Connect-Time Failover and Client Load BalancingG
Oracle Net connects randomly to one of the listener addresses on node ORADB3 or ORADB4. If the Instance fails after the connection, Oracle Net fails over to the other node’s Instance, preserving any SELECT statements in progress.
SUMSKYDB = (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=ON) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=ORADB3)(PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=ORADB4)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = SUMSKYDB.SUMMERSKYUS.COM) (FAILOVER_MODE = (TYPE=SELECT)(METHOD=BASIC) ) ) )
Retrying a Connection
With the RETRIES and DELAY parameters as part of the failover node sub parameter, the connections to the nodes are automatically retried to the number of times specified by the parameter. In this scenario the connection is retried 20 times with a delay of 15 seconds between every retry. Unlike the other option where one node in the cluster fails and the connection is reestablished on one of the other surviving nodes, under this option, the connection is retried on the same node and no backup node is defined as part of the configuration. Similarly there is no significance to the load-balancing parameter and has been set to off.
SUMSKYDB = (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE=OFF) (FAILOVER=ON) (ADDRESS=(PROTOCOL=TCP)(HOST=ORADB3)(PORT=1521)) ) (CONNECT_DATA = (SERVICE_NAME = SUMSKYDB.SUMMERSKYUS.COM) (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=20)(DELAY=15) ) ) )
Pre-Establishing a Connection
Another implementation option available under the TAF configuration is to setup a pre-established connection to the backup or secondary Instance. One of the potential performance issues is the time required to re-establish a connection after the primary, or the first, node has failed to the backup or secondary node. This could be resolved by pre-establishing connections, which means that the initial and backup connections are explicitly specified. While there is a great advantage in pre-establishing the connection, this is not without any drawbacks. Pre-established connections consume resources. During testing processes experienced by the author, up to 2% of additional CPU usage when using pre-established connections has been noted. This is because the process always validates the connection throughout its activity. In the following example, the Oracle Net connects to the listener on ORADB3. If ORADB3 fails after the connection, Oracle Net fails-over to ORADB4, preserving any SELECT statements in progress. Having the backup connection already in place can reduce the time needed for a failover in the event that one needs to take place. However, if the connect to the failed Instance does not succeed at connect time, then fail back to the original Instance is not possible. This option increases resource overhead in that the backup connection is always maintained.
SSRAC3 = (DESCRIPTION = (ADDRESS =(PROTOCOL=TCP)(HOST=ORADB3)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = SUMSKYDB.SUMMERSKYUS.COM) (INSTANCE_NAME = RAC3) (FAILOVER_MODE = (BACKUP=SSRAC4)(TYPE=SELECT)(METHOD=PRECONNECT) ) ) ) SSRAC4= (DESCRIPTION = (ADDRESS =(PROTOCOL=TCP)(HOST=ORADB4)(PORT=1521)) (CONNECT_DATA = (SERVICE_NAME = SUMSKYDB.SUMMERSKYUS.COM) (INSTANCE_NAME = RAC4) (FAIOVER_MODE = (BACKUP=SSRAC3)(TYPE=SELECT)(METHOD=PRECONNECT) ) ) ) REFERENCE://http://www.toadworld.com/platforms/oracle/