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