test-case-01 - OCI TAF Test · Pollo Tech Blog

Table of Contents

When designing a TAF (Transparent Application Failover) experiment in an Oracle 19c RAC environment, the key requirement is that the client must use an OCI-based (Oracle Call Interface) connection utility (such as SQL*Plus or custom OCI applications on Linux), as the JDBC Thin driver does not support TAF.

Below is a complete step-by-step guide for validating TAF via TNS on a Linux client.

1. Prerequisites
#

Environment Requirements

Database: Oracle 19c RAC 2-node cluster (assuming nodes are node1 and node2, instance names are db1 and db2, and the service name is db175_svc).
Client OS: Linux with Oracle Client or Oracle Instant Client installed (including SQL*Plus and required OCI libraries).

Configure Client-Side TNS Settings Add the following TAF configuration to $ORACLE_HOME/network/admin/tnsnames.ora (or the Instant Client TNS directory) on your Linux client:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


RAC_TAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = yes)
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm171-db01-vip.home.pollochang.work)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm172-db02-vip.home.pollochang.work)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db175_svc)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

TYPE = SELECT: When a node fails, the session switches to the surviving node, and in-flight SELECT queries continue fetching remaining rows without requiring manual re-execution.
METHOD = BASIC: Establishes a connection to the backup node only after a failure is detected.
RETRIES / DELAY: Configures the number of retry attempts and the delay interval in seconds.

Verify TNS Connectivity Run the following command in the Linux terminal:

1

tnsping RAC_TAF

Ensure the alias resolves properly and connects to the listener. 4. Create Test Data in the Database Log in with a DBA account and create a sufficiently large table (e.g., 1,000,000 rows) so you have enough time to simulate a node crash during query execution:

1
2
3
4


CREATE TABLE test_taf AS
SELECT rownum AS id, 'TAF_TEST_DATA_' || rownum AS note, sysdate AS created_time
FROM dual
CONNECT BY level <= 1000000;

2. Test Steps
#

Test Case 1: Validating Seamless Query Failover with `TYPE = SELECT`
#

Objective: Verify that when a node crashes, an ongoing SELECT statement automatically resumes fetching data on the other node.

Establish Client Connection and Check Status Launch SQL*Plus on the Linux client:

1

sqlplus pollo_ap/PaSsw0rd..@RAC_TAF

Run the following queries to verify the connected instance and TAF registration status:

1
2
3
4
5


SELECT instance_name, host_name FROM v$instance;

SELECT sid, serial#, failover_type, failover_method, failed_over 
FROM v$session 
WHERE sid = sys_context('userenv', 'sid');

1
2
3
4
5
6


SQL> SELECT sid, serial#, failover_type, failover_method, failed_over 
FROM v$session WHERE sid = sys_context('userenv', 'sid');

       SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
       751      15563 SELECT        BASIC      NO

Non-DBA users can also query the current instance details using sys_context:

1
2
3
4
5
6


SELECT 
    sys_context('userenv', 'instance_name') AS instance_name,
    sys_context('userenv', 'server_host')   AS host_name,
    sys_context('userenv', 'service_name')  AS service_name,
    sys_context('userenv', 'sid')           AS sid
FROM dual;

Example output for standard users:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


SQL> SELECT                                          
    sys_context('userenv', 'instance_name') AS instance_name,
    sys_context('userenv', 'server_host')   AS host_name,
    sys_context('userenv', 'service_name')  AS service_name,
    sys_context('userenv', 'sid')           AS sid
FROM dual;

INSTANCE_NAME
--------------------------------------------------------------------------------
HOST_NAME
--------------------------------------------------------------------------------
SERVICE_NAME
--------------------------------------------------------------------------------
SID
--------------------------------------------------------------------------------
db1
vm171-db01
db175_svc
746

Expected Result: FAILOVER_TYPE displays SELECT, FAILOVER_METHOD displays BASIC, and FAILED_OVER displays NO. 2. Execute a Long-Running Query In the same SQL*Plus session, run a full table scan:

1
2


SET PAGESIZE 50000
SELECT * FROM test_taf;

Simulate Node Failure on the Database Server While data is actively scrolling on the client screen, log in to the RAC node handling this session (e.g., node1) and forcefully abort the instance:

1
2


# Stop the instance using srvctl
srvctl stop instance -d db -i db1 -o abort -f

Alternatively, execute shutdown abort; in SQL*Plus on that node. 4. Observe Client Behavior

The screen output will pause briefly for a few seconds while OCI detects the severed connection and re-establishes communication with node2.
The output will then resume and scroll continuously until all 1,000,000 rows are returned without throwing disconnection errors.

Verify Session Status After Failover Once the query finishes, run the verification queries in the same SQL*Plus session:

1
2
3
4
5
6
7
8
9


SELECT 
    sys_context('userenv', 'instance_name') AS instance_name,
    sys_context('userenv', 'server_host')   AS host_name,
    sys_context('userenv', 'service_name')  AS service_name,
    sys_context('userenv', 'sid')           AS sid
FROM dual;

SELECT sid, serial#, failover_type, failover_method, failed_over 
FROM v$session WHERE sid = sys_context('userenv', 'sid');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26


SQL> SELECT 
    sys_context('userenv', 'instance_name') AS instance_name,
    sys_context('userenv', 'server_host')   AS host_name,
    sys_context('userenv', 'service_name')  AS service_name,
    sys_context('userenv', 'sid')           AS sid
FROM dual;

INSTANCE_NAME
--------------------------------------------------------------------------------
HOST_NAME
--------------------------------------------------------------------------------
SERVICE_NAME
--------------------------------------------------------------------------------
SID
--------------------------------------------------------------------------------
db2
vm172-db02
db175_svc
751

SQL> SELECT sid, serial#, failover_type, failover_method, failed_over 
FROM v$session WHERE sid = sys_context('userenv', 'sid');

       SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
       751      15563 SELECT        BASIC      YES

Expected Result: INSTANCE_NAME has changed to db2, and FAILED_OVER is now YES.

Test Case 2: Validating Automatic Rollback for Uncommitted Transactions (DML)
#

Objective: Confirm that TAF cannot replay write transactions (DML), and uncommitted transactions are automatically rolled back with an exception raised.

Connect and Execute Uncommitted DML Start a new SQL*Plus session on the Linux client:

1

sqlplus pollo_ap/PaSsw0rd..@RAC_TAF

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


SQL> SELECT instance_name, host_name FROM v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
db1
vm171-db01.home.pollochang.work

SQL> SELECT sid, serial#, failover_type, failover_method, failed_over 
FROM v$session WHERE sid = sys_context('userenv', 'sid');

       SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
       139      15645 SELECT        BASIC      NO

Execute an INSERT statement without committing:

1

INSERT INTO test_taf (id, note) VALUES (9999999, 'UNCOMMITTED_DATA');

Abort the Current Instance Again

1

srvctl stop instance -d db -i db1 -o abort -f

Execute the Next Command on the Client Attempt to run any SQL statement in the client session (e.g., COMMIT; or SELECT * FROM test_taf WHERE id = 9999999;): Expected Result:

The client receives an error, typically ORA-25402: transaction must roll back.
Although the session reconnects to the surviving node, the uncommitted INSERT is rolled back.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


SQL> INSERT INTO test_taf (id, note) VALUES (9999999, 'UNCOMMITTED_DATA');

1 row created.

SQL> SELECT * FROM test_taf WHERE id = 9999999;
SELECT * FROM test_taf WHERE id = 9999999
            *
ERROR at line 1:
ORA-25402: transaction must roll back

SQL> rollback;

Rollback complete.

SQL> SELECT sid, serial#, failover_type, failover_method, failed_over FROM v$session WHERE sid = sys_context('userenv', 'sid');

       SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
       751      32978 SELECT        BASIC      YES

Test Case 3: Comparing Behavior with `TYPE = SESSION`
#

Objective: Observe how query execution fails to resume when configured with TYPE = SESSION.

Update FAILOVER_MODE in the client’s tnsnames.ora to (TYPE = SESSION).
Reconnect using SQL*Plus and repeat the steps from Test Case 1 (run SELECT * FROM test_taf; and forcefully abort the connected instance).
Expected Result:

The query is interrupted and throws an error such as ORA-25401: can not continue fetches.
While the session itself fails over to the new node, open cursors cannot continue fetching, requiring the query to be manually re-executed.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


NO_RAC_TAF =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = yes)
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm171-db01-vip.home.pollochang.work)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = vm172-db02-vip.home.pollochang.work)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db175_svc)
      (FAILOVER_MODE =
        (TYPE = SESSION)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48


❯ sqlplus pollo_ap/PaSsw0rd..@NO_RAC_TAF 

SQL> SELECT instance_name, host_name FROM v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
db2
vm172-db02.home.pollochang.work

SQL> SELECT sid, serial#, failover_type, failover_method, failed_over FROM v$session WHERE sid = sys_context('userenv', 'sid');

       SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
       629      25203 SESSION       BASIC      NO

SQL> SELECT * FROM test_taf;
    1 TAF_TEST_DATA_999995                                   19-AUG-26
    ...
        ID NOTE                                                   CREATED_T
---------- ------------------------------------------------------ ---------
     19119 TAF_TEST_DATA_19119                                    19-AUG-26
     19120 TAF_TEST_DATA_19120                                    19-AUG-26
     19121 TAF_TEST_DATA_19121                                    19-AUG-26
     19122 TAF_TEST_DATA_19122                                    19-AUG-26
     19123 TAF_TEST_DATA_19123                                    19-AUG-26
     19124 TAF_TEST_DATA_19124                                    19-AUG-26
     19125 TAF_TEST_DATA_19125                                    19-AUG-26
ERROR:
ORA-25401: can not continue fetches

19125 rows selected.

SQL> SELECT instance_name, host_name FROM v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
db1
vm171-db01.home.pollochang.work

SQL> SELECT sid, serial#, failover_type, failover_method, failed_over FROM v$session WHERE sid = sys_context('userenv', 'sid');

       SID    SERIAL# FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ------------- ---------- ---
       862      57532 SESSION       BASIC      YES

3. Practical Observations and Considerations
#

Failover Latency During an actual failover, the client will experience a few seconds of pause. This delay occurs because TCP disconnection detection, timeout handling, and retry intervals (RETRIES and DELAY) all require time to complete.
Application Compatibility TAF is a legacy high-availability feature specifically designed for OCI. For web applications using standard Java JDBC Thin Drivers (such as Spring Boot, WebLogic, or Tomcat), configuring TAF in tnsnames.ora has no effect. In modern RAC environments, such applications should use UCP combined with FCF (Fast Connection Failover) or TAC (Transparent Application Continuity).

Referance
#

https://github.com/marhar/oracle-taf
https://www.51cto.com/article/771628.html
MOS: KB895599, KB293567, KB833432, KB358566 ,KB195198

1. Prerequisites #

2. Test Steps #

Test Case 1: Validating Seamless Query Failover with TYPE = SELECT #

Test Case 2: Validating Automatic Rollback for Uncommitted Transactions (DML) #

Test Case 3: Comparing Behavior with TYPE = SESSION #

3. Practical Observations and Considerations #

Referance #