How to disable Oracle OCI client transparent reconnect

Question

I have an application I wrote that uses OCI to connect to an Oracle database. The database has recently been upgraded to Oracle 18, and since then database searches in the application randomly stop working.

I have narrowed the problem down to this. When the app connects to the DB it runs some ALTER SESSION commands to disable case-sensitivity in LIKE conditions (so that I can search for "smith" and still find "Smith"). After some hours, the network drops the TCP connection. The next morning when someone tries to access the system, the Oracle client transparently reconnects and executes the query.

The problem is that this reconnection happens without my code knowing about it, apparently by the Oracle client itself, and it does not re-run the original ALTER SESSION commands after reconnecting. Our DBAs have supplied me with an audit log that shows the session ID changing, a new LOGON attempt and a single ALTER SESSION running to set the timezone (which does not come from my code, and also appeared in the original connection).

This means that my application issues queries to the database as per normal, unaware that there has been any interruption, but suddenly they become case-sensitive and the users start complaining that the search isn't returning any results.

With the previous Oracle server version my code would receive an error if it had been disconnected, and the code would try to reconnect and as part of the reconnection, it would run the ALTER SESSION commands again so that there was no change to the behaviour of the system.

I can't find anything about this auto-reconnect feature or how to adjust it, so is there some way to either preserve the ALTER SESSION changes so they are re-applied after the reconnection, or to disable this behaviour and return an error to the code if the connection is lost as used to happen?

The client is an older 11.2 one and upgrading it isn't really practical, but since it worked before the server upgrade I would've thought that version would be ok.

Answer 1

It seems that Transparent Application Failover (TAF) is enabled for your connection. If TAF is enabled the Oracle client will reconnect you to the database or an alternate database if the connection between the client and the server is disrupted. You use an OCI client and for this it is possible to register a failover callback function that is called after the client reconnects. In this callback function you can issue the 'ALTER SESSION' command you need. But maybe you don't want to use TAF.

So first the DBA should check if your session uses TAF. An appropriate query can be found in the Net Services Administrator's Guide

SELECT MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, COUNT(*)
FROM V$SESSION
GROUP BY MACHINE, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER;

With the following sample outpu

MACHINE              FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER   COUNT(*)
-------------------- ------------- ----------      ---           ----------
sales1               NONE          NONE            NO            11
sales2               SELECT        PRECONNECT      NO             1

FAILOVER_TYPE NONE tells us, that no TAF is configured. FAILOVER_TYPE different to NONE tells us that TAF is configured for these sessions.

If your the session uses TAF then you should find out, where it is configured. It can be configured on client side or on server side. You can check your 'tnsname.ora' file or use your 'tnsping' utlity to check your client configuration. If you find something similar to this example from Net Services Administrator's Guide

sales.us.example.com=
 (DESCRIPTION=
  (LOAD_BALANCE=on) 
  (FAILOVER=on) 
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales1-server)  
       (PORT=1521)) 
  (ADDRESS=
       (PROTOCOL=tcp)  
       (HOST=sales2-server)  
       (PORT=1521)) 
  (CONNECT_DATA=
     (SERVICE_NAME=sales.us.example.com) 
     (FAILOVER_MODE=
       (TYPE=select) 
       (METHOD=basic))))

then TAF is configured because of FAILOVER_MODE=(TYPE=select). If this is the case you should configure an appropriate connect string without TAF.

But even if no TAF is configure on client side it could be configured on server side for this service. So do the following query

select NETWORK_NAME, FAILOVER_METHOD, FAILOVER_TYPE
from DBA_SERVICES
where NETWORK_NAME is not null;

The following example output shows that TAF will be used for (SERVICE_NAME=sales.us.example.com) even if it is not configured on the client.

NETWORK_NAME             FAILOVER_METHOD      FAILOVER_TYPE
------------------------ -------------------- --------------------
sales.us.example.com     BASIC                SELECT
sales3.us.example.com    NONE                 NONE

In this case TAF will be used for (SERVICE_NAME=sales.us.example.com) even if TAF is not configured on the client. So you have to use a service that does not have configured TAF, in out example 'sales3.us.example.com' is such a service.

Service can be created with the DBMS_SERVICE package but in most cases they are created by some additional tools like Oracle Real Application Cluster. One can display the services created by the RAC for a database 'salesdb' by executing the following command on the database server

srvctl config service -d salesdb

The following output (that depends on the database version) shows that RAC will create a TAF service after start of the database

Service name: sales.us.example.com
Service is enabled
Server pool: SALESDB_sales.us.example.com
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Failover type: SELECT
Failover method: BASIC
TAF failover retries: 5
TAF failover delay: 2
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Preferred instances: sales2-server,sales2-server
Available instances:
Service is enabled on instances: SALESDB1,SALESDB2

you can create a new service 'sales3.us.example.com' without TAF with the following command

srvctl add service -d salsedb -s sales3.us.example.com

Answer 2

As a workaround a trigger was added to the user account, to run the ALTER SESSION command upon every connection. This has fixed the problem, however we are still unsure as to why the session transparently reconnects without any notification to the client app!