Client Side Transparent Application Failover for Oracle RAC

TAF – – Transparent Application Failover is a feature of the OCI driver, and TAF cannot be used with thin driver. The client side TAF can be configured through client connection definitions in TNS connect descriptors.

The tests are held under the environment as below,  both 11gR2 database and client are used to test the behaviours of client side TAF by using SCAN.

GI: 12.1.0.2.0
4 nodes RAC: 11.2.0.4
Oracle Client: 11.2.0.4

The service name is RAC_TEST running on node1 and node4, while available nodes are node2 and node3.

$ srvctl status service -s RAC_TEST -d TESTDB
Service RAC_TEST is running on instance(s) TESTDB1,TESTDB4

There are two types of TAF available, SESSION and SELECT.

SESSION: Session Failover re-creates the connections and sessions to the surviving instance.
SELECT: In addition to recreating the session, Select Failover also replays the queries that were in progress.

There are two methods in which TAF estabilishes the failover connection, BASIC and PRECONNECT.

BASIC: The second connection is re-established only after the first connection to the instance or srvice failed.
PRECONNECT: Two connections are established when the client logs in to the database. A login to database will create two connection at the same time. For this to work, clusterware actually starts two services. One main service and another shadow service.

Client Side TAF Configuration — BASIC

For non-TAF database connection, by query gv$session to get client side TAF connection details below, which demonstrates the server side TAF is not configured. That is what we wanted in this case.

select INST_ID,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from gv$session where username='JAMESH';
INST_ID USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER
--------- ------------------------------ ------------- ----------
4 JAMESH NONE NONE NO

Use the below TNS entry to test client side TAF with BASIC method.

CLIENT_TAF =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =CLU-SCAN)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = RAC_TEST)
(FAILOVER_MODE= (TYPE=select)(METHOD=basic)(RETRIES=20)(DELAY=15))
)
)

1) Make a database connection by using TNS entry CLIENT_TAF, and run a long query :

select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER 
from gv$session where username='JAMESH';
INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI
---------- ---------- ---------- ------------------------------ ------------- ---------- ---
 4 70 11171 JAMESH SELECT BASIC NO

2a) Manually kill the session ( only works when session is ACTIVE, otherwise it is not working for killing from testing )

SQL> alter system kill session ‘70,11171,@4’;

System altered.

3a) Query the session status, we can see a new failed over session with different ‘SID, serial#’, and the query is still going until completed.

 SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER from gv$session where username='JAMESH';
INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER
---------- ---------- ---------- ------------------------------ ------------- ---------- ---
 4 366 14271 JAMESH SELECT BASIC YES

2b) Instead of killing session in step 2a, we manually stop the service on node 4 to simulate instance crash. (  it works for both ACTIVE and INACTIVE sessions )

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI
———- ———- ———- —————————— ————- ———- —
4 70 11185 JAMESH SELECT BASIC NO
SQL> ! srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from gv$session where username=’JAMESH’;

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI
———- ———- ———- —————————— ————- ———- —
1 367 8663 JAMESH SELECT BASIC YES

SQL> ! srvctl start service -s RAC_TEST -i TESTDB4 -d TESTDB

Client Side TAF Configuration — PRECONNECT

PRIMARY=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)(HOST=clu-scan)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=RAC_TEST)
(FAILOVER_MODE=
(BACKUP=SECONDARY)
(TYPE=select)
(METHOD=preconnect))))

SECONDARY=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=clu-scan)(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=RAC_TEST)
(FAILOVER_MODE=
(BACKUP=PRIMARY)
(TYPE=select)
(METHOD=preconnect))))
1) Make a database connection by using TNS entry above, and run a long query :

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,status from gv$session where username='JAMESH'

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
---------- ---------- ---------- ------------ ------------- ---------- --- --------
 1 42 8485 JAMESH NONE NONE NO INACTIVE
 4 366 14375 JAMESH SELECT PRECONNECT NO INACTIVE

2) Manually stop service RAC_TEST on node 4

SQL> ! srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f

3) The preconnected session takes over the session and continue the query until complete.

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,status from gv$session where username='JAMESH';
INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
---------- ---------- ---------- ------------ ------------- ---------- --- --------
 1 42 8485 JAMESH SELECT PRECONNECT YES ACTIVE

SQL> ! srvctl start service -s RAC_TEST -i -i TESTDB4 -d TESTDB

4) if you kill this PRECONNECT ssession again , TAF still works again as preconnect.

SQL> alter system kill session ‘42,8485,@1′;

System altered.

SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,status from gv$session where username=’JAMESH’;

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS
———- ———- ———- ———— ————- ———- — ——–
4 265 1563 JAMESH SELECT PRECONNECT YES ACTIVE

Advertisements
This entry was posted in GI and RAC and tagged , , , , . Bookmark the permalink.

One Response to Client Side Transparent Application Failover for Oracle RAC

  1. Pingback: Transparent Application Failover for Oracle RAC | Oracle RAC Goldengate

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s