Return to Answer

typos, format

Source Link

edited Nov 14, 2020 at 12:02

7.8k
26
42

It seems that Transparent Application Failover (TAF) is enabled for your connection. If TAF is enabled the Oracle Lientclient will reconnect you thoto the database or an alternate database if the connection between the client and the server is disruppteddisrupted. 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 funtionfunction you can issue the 'ALTER SESSION' command you need. But maybe you don't want to use TAF.

If your thessionthe session uses TAF then you should find out, wherrewhere it is configured. It can be configured on client side or on server side. You can check your tnsname'tnsname.oraora' 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

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

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

The following example output shows that TAF will be used for (SERVICE_NAME=sales.us.example.com)(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

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)(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'sales3.us.example.comcom' 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'salesdb' by executing the following comandcommand on the database server

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

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

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.

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

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

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

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

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

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.

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

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

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

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

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

Source Link

created Nov 14, 2020 at 6:52

miracle173

7.8k
26
42

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.

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;

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