SYSDBA Sessions Do Not Failover with SRVCTL TAF Configured (Doc ID 1342992.1)

Last updated on AUGUST 26, 2011

In this Document
  Symptoms
  Cause
  Solution
     Scalability RAC Community
  References


Symptoms

SYSDBA sessions do not failover when Transparent Application Failover (TAF) on the Server side is configured with the Server Control Utility, SRVCTL. Sessions may report error ORA-3113.

However adding a client-side tnsnames entry with Failover properties will allow the session to connect with TAF enabled.

The following is a testcase example to better illustrate:

=========
TESTCASE:
=========

1. Create a server side TAF definition:

srvctl add service -d dbm -s test -r dbm2 -a dbm1 -P BASIC -l primary -y MANUAL -e SELECT -m BASIC -j LONG -B SERVICE_TIME


2. Start the service:

srvctl start service -d dbm -s test


3. Connect with this service:

sqlplus dbuser/dbuser@test


4. Verify the service and session connection have proper TAF settings in other session:

SQL> connect / as sysdba
Connected.
SQL> select NAME, NETWORK_NAME, FAILOVER_METHOD, FAILOVER_TYPE from dba_services;
...
NAME
----------------------------------------------------------------
NETWORK_NAME
----------------------------------------------------------------
FAILOVER_METHOD
----------------------------------------------------------------
FAILOVER_TYPE
----------------------------------------------------------------
test
test
BASIC
SELECT

SQL> select FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER from gv$session where inst_id = 2 and username ='dbuser';

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
SELECT        BASIC      NO



5. Then connect with SYSDBA privs:

SQL> connect dbuser@test as sysdba


6. Check the settings:

select FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER from gv$session where inst_id = 2 and username ='SYS'

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
NONE          NONE       NO
NONE          NONE       NO



7. WORKAROUND:  If client-side settings are added into tnsnames.ora, the connection will retain proper TAF settings; ie:

test2 =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = dmorl2-scan)(PORT = 1521))
    (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = test)
    (FAILOVER_MODE =
    (TYPE = SELECT)(METHOD = BASIC)(RETRIES = 180)(DELAY = 5)
    )
  )
)

SQL> connect dbuser@test2 as sysdba
Enter password:
Connected.

SQL> select FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER from gv$session where inst_id = 2 and username ='SYS'

FAILOVER_TYPE FAILOVER_M FAI
------------- ---------- ---
NONE          NONE       NO
SELECT        BASIC      NO



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms