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.

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 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 19c. I am also using an Oracle 19c RAC database as the back-end.

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. 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:

taf_svc =
 (DESCRIPTION = 
  (ADDRESS = (PROTOCOL = tcp)(HOST = rac19pri-scan.example.com)(PORT = 1521))
  (CONNECT_DATA=
    (SERVICE_NAME = taf_svc) 
     (FAILOVER_MODE=(TYPE=select)(METHOD=basic)))
  )

Note that setting the failover_mode () 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@rac19pri1]$ srvctl add service -db NCDB -service taf_svc \
-role primary -policy automatic -clbgoal long \
-failovermethod basic -failovertype session \
-preferred "NCDB1,NCDB2"

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 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 taf_svc in my 1st session. I then checked the status after connecting as SYSTEM in a second session:

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

   INST_ID FAILOVER_M FAILOVER_TYPE FAI SERVICE_NAME
---------- ---------- ------------- --- ---------------
         1 BASIC      SESSION       NO  taf_svc

SQL> show user
USER is "SYSTEM"

Running the code

The complete code is available on github in my java-blogposts repository. After downloading it to your machine, change into the taf-demo-1 directory and trigger the compile target using mvn compile.

With the code built, you can run it easily on the command line. First off, try the thin driver.

JDBC Thin Driver

I used this command to start the execution using the thin driver:

java -cp /home/martin/java/libs/ojdbc10.jar:/home/martin/java/libs/oraclepki.jar:/home/martin/java/libs/osdt_cert.jar:/home/martin/java/libs/osdt_core.jar:target/taf-example-1-0.0.1-SNAPSHOT.jar de.martin.tafDemo.App thin

This should connect you to the database, but not with the desired effect.

About to start a demonstration using Transparent Application Failover
Driver Name: Oracle JDBC driver
Driver Version: 19.7.0.0.0
Connection established as MARTIN


inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver 

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:

inst_id: 2 sid: 00264 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00049 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
inst_id: 2 sid: 00049 failover_type: NONE       failover_method: NONE       failed_over: NO    module: TAF Demo action: thin driver
SQLException while trying to get the session information: java.sql.SQLRecoverableException: No more data to read from socket
[martin@appserver taf-demo-1]$

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.

JDBC OCI driver

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

$ export TNS_ADMIN=/home/martin/tns
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/path/to/client/installation

Once these are set, start the application:

$ java -cp /home/martin/java/libs/ojdbc10.jar:/home/martin/java/libs/oraclepki.jar:/home/martin/java/libs/osdt_cert.jar:/home/martin/java/libs/osdt_core.jar:target/taf-example-1-0.0.1-SNAPSHOT.jar de.martin.tafDemo.App oci
About to start a demonstration using Transparent Application Failover
Driver Name: Oracle JDBC driver
Driver Version: 19.7.0.0.0
Connection established as MARTIN


inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 1 sid: 00035 failover_type: SELECT     failover_method: BASIC      failed_over: NO    module: TAF Demo action: oci driver
inst_id: 2 sid: 00275 failover_type: SELECT     failover_method: BASIC      failed_over: YES   module: java@appserver (TNS V1-V3) action: null
inst_id: 2 sid: 00275 failover_type: SELECT     failover_method: BASIC      failed_over: YES   module: java@appserver (TNS V1-V3) action: null
inst_id: 2 sid: 00275 failover_type: SELECT     failover_method: BASIC      failed_over: YES   module: java@appserver (TNS V1-V3) action: null
^C[martin@appserver taf-demo-1]$ 

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.