It seems that Transparent Application Failover (TAF) is enabled for your connection. If TAF is enabled the Oracle Lient will reconnect you tho the database or an alternate database if the connection between the client and the server is disruppted. 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 funtion 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 thession uses TAF then you should find out, wherre 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 conenct string without TAF.
But even if no TAF is configure on client side it could be configured on server sise 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. sales.us.example.com 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 comand on the database server
srvctl config service -d salesdb
The folowing 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