JDBC & the Oracle Database: if you want Transparent Application Failover you need the OCI driver

This is the second article in the series of JDBC articles I’m about to publish. It covers an old technology that’s surprisingly often found in use: Transparent Application Failover (TAF). It’s a client side feature for clustered Oracle databases allowing sessions (and to some extent, select statements) to fail over to a healthy node from a crashed instance.

Update 220607: updated the code and database to Oracle 21c. Instead of connecting to an on-premises database I opted for a 21c RAC system powered by OCI Database Cloud Service (DBCS).

I would wager a bet that you probably don’t want to use Transparent Application Failover in (new) Java code. There are many better ways to write resilient code these days. More posts to follow with my suggestions ;)

Well, then, why bother writing this post? Simple! There is a common misconception about the requirement: since Transparent Application Failover relies on the Oracle client libraries, you cannot use it with the thin driver. The little tool I have written demonstrates exactly that. And besides, I had the code more or less ready, so why not publish it?

Prerequisites for running the demo code

My Java code has been updated to Oracle work with Oracle 21c. I am also using an Oracle 21c RAC database as the back-end. The details about my setup can be found here:

  • Container Database (CDB): DEVDB
  • Database resource name in Grid Infrastructure: DEVDB_fra24
  • Pluggable Database (PDB): DEVPDB
  • Service Name: swingbench1_taf
  • Node list: rac1, rac2
  • Instance list: DEVDB1, DEVDB2
  • Single Client Access Name (SCAN): rac-scan.db.dbapp.oraclevcn.com

Preparing the client

Since I am going to use the Secure External Password Store again you need to prepare the client as per my previous article. The only difference this time is that I need a sqlnet.ora file in my client’s tns directory (the native OCI driver needs it). Continuing the previous example I created the file in /home/martin/tns, and it contains the following information:

WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /home/martin/tns)
    )
  )

SQLNET.WALLET_OVERRIDE = TRUE  

When you are creating yours, make sure to update the path according to your wallet location.

Since I’m connecting to a RAC database I need to change the entry in tnsnames.ora as well. This requires the application specific service to be created and started, a bit of a chicken and egg problem. The setup of the database service is explained in the next section. Here is my tnsnames.ora entry:

swingbench1_taf =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.db.dbapp.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = swingbench1_taf.db.dbapp.oraclevcn.com)
      (FAILOVER_MODE=(TYPE=select)(METHOD=basic))
    )
  )

Note that setting the failover_mode () on the client isn’t the preferred way to set TAF properties. It’s better to do that at the service level, see below.

Preparing the database service

Oracle strongly discourages the use of the default service name except for DBA tasks. As I’m a good citizen I’ll create a separate service for my little TAF application. You need to connect to the database server and use srvctl create service to create a service. I used the following properties:

[oracle@rac1]$ srvctl add service -db DEVDB_fra24p \
-pdb DEVPDB \
-service swingbench1_taf \
-role primary \
-policy automatic \
-clbgoal long \
-tafpolicy BASIC \
-failovermethod basic \
-failovertype select \
-preferred "DEVDB1,DEVDB2"

Adjust the command for your environment before hitting the return a final time. You have to set at least preferred nodes and the connect time load balancing goal. If you want to ensure anyone connecting to the TAF services actually makes use of it regardless of the tnsnames setting, you also need to set tafpolicy, failovertype and failovermethod.

Don’t forget to start the service after you created it! Once the service is created and running, let’s try to use it to see if all TAF properties are available. To do so, I connected to swingbench1_taf in session #1. It is imperative to do so via SQL*Plus or another tool using the OCI JDBC driver or else none of the properties will be populated. Connected as SYSTEM in another session I found the following properties to be set:

SQL> select inst_id, failover_method, failover_type, failed_over, service_name
  2  from gv$session where username = 'SOE'
  3  /

   INST_ID FAILOVER_M FAILOVER_TYPE FAI SERVICE_NAME
---------- ---------- ------------- --- --------------------
         1 BASIC      SELECT        NO  swingbench1_taf

SQL> show user
USER is "SYSTEM"

If you don’t create another session as SYSTEM (or another DBA user), you’ll get more rows back than you’d expect as queries against gv$-tables are special.

JDBC Thin Driver

The JDBC thin driver does not enable Transparent Application Failover. Here is an example:

$ java -cp ... de.martin.App thin
About to start a demonstration of Transparent Application Failover
Trying to connect to the database using the THIN driver
Connection string: jdbc:oracle:thin:/@swingbench1_taf?TNS_ADMIN=/home/martin/tns
Driver Name:          Oracle JDBC driver
Driver Version:       21.5.0.0.0
Database Username is: SOE

Let's do some work.

Connected to instance# 1 sid 475 failover type: NONE failover method: NONE failed over: NO module: TAF Demo action: thin
Connected to instance# 1 sid 475 failover type: NONE failover method: NONE failed over: NO module: TAF Demo action: thin
Connected to instance# 1 sid 475 failover type: NONE failover method: NONE failed over: NO module: TAF Demo action: thin

As you can easily spot there isn’t any trace of TAF in the output. Not surprisingly, the code crashes as soon as instance 2 fails:

Connected to instance# 1 sid 475 failover type: NONE failover method: NONE failed over: NO module: TAF Demo action: thin

Something unexpected happened: java.sql.SQLRecoverableException: No more data to read from socket
java.sql.SQLRecoverableException: No more data to read from socket
	at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:855)
	at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:487)
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:512)
	at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:163)
	at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1241)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
	at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1661)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1470)
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3761)
	at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3936)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1102)
	at de.martin.taf.TAFDemo.doSomeWork(TAFDemo.java:149)
	at de.martin.App.main(App.java:28)

There are potentially ways around that, but I yet have to see an application implement these. So in other words, in most cases the following equation is true: instance crash = application crash.

This is probably not what you had in mind.

JDBC OCI driver

Running the same code using the OCI driver should solve that problem. You will need an Oracle client installation for this to work, and you have to set LD_LIBRARY_PATH as well as TNS_ADMIN in the shell.

Be careful when using the instant client packages: the JDBC driver version must match the instant client version, or else you might get an error. In my case I went with oracle-instantclient-jdbc-21.5.0.0.0-1.x86_64.

$ export TNS_ADMIN=/home/martin/tns
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/21/client64/lib

Once these are set, start the application:

$ java -cp ... de.martin.App oci
About to start a demonstration of Transparent Application Failover
Trying to connect to the database using the OCI driver
Connection string: jdbc:oracle:oci:/@swingbench1_taf
Driver Name:          Oracle JDBC driver
Driver Version:       21.5.0.0.0
Database Username is: SOE

Let's do some work.

Connected to instance# 1 sid 1085 failover type: SELECT failover method: BASIC failed over: NO module: TAF Demo action: oci
Connected to instance# 1 sid 1085 failover type: SELECT failover method: BASIC failed over: NO module: TAF Demo action: oci
Connected to instance# 1 sid 1085 failover type: SELECT failover method: BASIC failed over: NO module: TAF Demo action: oci
Connected to instance# 1 sid 1085 failover type: SELECT failover method: BASIC failed over: NO module: TAF Demo action: oci
Connected to instance# 1 sid 1085 failover type: SELECT failover method: BASIC failed over: NO module: TAF Demo action: oci

Connected to instance# 1 sid 1085 failover type: SELECT failover method: BASIC failed over: NO module: TAF Demo action: oci
Connected to instance# 2 sid 480 failover type: SELECT failover method: BASIC failed over: YES module: java@devws1 (TNS V1-V3) action: oci
A failover must have occurrent. Trying to set client info, module and action again
Connected to instance# 2 sid 480 failover type: SELECT failover method: BASIC failed over: YES module: TAF Demo action: oci
Connected to instance# 2 sid 480 failover type: SELECT failover method: BASIC failed over: YES module: TAF Demo action: oci
^C

You should notice a seamless transition from node 1 to node 2. As you can imagine this is the simplest example, but it should convey the message as intended. For more details about TAF and RAC, including the use of “select” failover and DML support I suggest you have a look at Pro Oracle 11g RAC on Linux, chapter 11.

Summary

Contrary to what one might think using TAF with the JDBC thin driver doesn’t protect a session from instance failure. The only way to protect a (fat client) session is to make use of the Oracle Call Interface.

Then again, TAF is a very mature solution and there might be better ways of working with RAC. Connection pools based on Oracle’s own Universal Connection Pool look like the way forward. Newer technologies, such as (Transparent) Application Continuity are better suited to meet today’s requirements.

Advertisement