Server Side Transparent Application Failover for Oracle RAC

TAF setting on service overrides any TAF settings on the client side configurations.

Concept & Overview 

The TAF setting on a service overrides any TAF settings in the client connection definition that might be configured in TNS connect descriptors. If the failover type is set on the server side, then the failover method will default to BASIC.

Oracle Call Interface applications with TAF enabled should use FAN high availability events for fast connection failover ( FCF ).

TAF is a feature of the OCI driver, and TAF cannot be used with thin driver

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 establishes the failover connection, BASIC and PRECONNECT.

BASIC: The second connection is re-established only after the first connection to the instance 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.

The tests are held under the environment as below,  both 11gR2 database and client are used to test the behaviors of server 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 preferred nodes 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

Steps for Testing Server Side TAF

1) Create a service if not yet, here we modify it because the service has already existed.

$ srvctl modify service -s RAC_TEST -d TESTDB -P BASIC -e SELECT -m BASIC -w 120 -z 5

2) Start the service if not yet.

$ srvctl start service -s RAC_TEST -d TESTDB

3) Check service is running

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

4) Check service from database query.

SQL>col name format a15
col failover_method format a11 heading 'METHOD'
col failover_type format a10 heading 'TYPE'
col failover_retries format 9999999 heading 'RETRIES'
col goal format a10
col clb_goal format a8
col AQ_HA_NOTIFICATIONS format a5 heading 'AQNOT'

SQL> select name, failover_method, failover_type,
failover_retries,goal, clb_goal,aq_ha_notifications
from dba_services
where name='RAC_TEST';

NAME METHOD TYPE RETRIES GOAL CLB_GOAL AQNOT
-------- ------- ------ -------- ----- -------- -----
RAC_TEST BASIC SELECT 5 NONE LONG NO

5) Create a service name into TNS entry.

SERVER_TAF =
(DESCRIPTION = (LOAD_BALANCE = YES )
(ADDRESS = (PROTOCOL = TCP)(HOST =CLU-SCAN)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = RAC_TEST)
)
)

6) Make a connection to database by using above TNS entry.

SQL> connect testuser@server_taf
Enter password:
Connected.

SQL> select host_name,instance_name from v$instance;

HOST_NAME INSTANCE_NAME
----------- ----------------
racnode4 TESTDB4
SQL> select INST_ID,sid,serial#,USERNAME,FAILOVER_TYPE,
FAILOVER_METHOD,FAILED_OVER,status  
from gv$session
where username='TESTUSER';

INST_ID SID SERIAL# USERNAME TYPE METHOD FAI STATUS
-------- ---- ------- --------- ------ ------- ----- --------
4 380 4569 TESTUSER SELECT BASIC NO INACTIVE

7) Shutdown the instance or the service on the node the connection has made to, then we can see session failed over successfully.

SQL> select host_name,instance_name from v$instance;

HOST_NAME INSTANCE_NAME
--------- ----------------
racnode1  TESTDB1

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

INST_ID SID SERIAL# USERNAME TYPE METHOD FAI STATUS
--------- ---- ------- -------- ------ ------- --- --------
1 250 971 TESTUSER SELECT BASIC YES INACTIVE

Client Side Transparent Application Failover for Oracle RAC

TAF – Transparent Application Failover is a very useful feature in mission critical environment.

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 following environment,  both 11gR2 database and client are used to test the behaviors 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 establishes the failover connection, BASIC and PRECONNECT.

BASIC: The second connection is re-established only after the first connection to the instance or service 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, which demonstrates the server side TAF is not configured. That is what we expected in this case.

SQL>select INST_ID,USERNAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER
from gv$session
where username='TESTUSER';

INST_ID USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER ------- --------- -------------- ----------- -------------
4 TESTUSER NONE NONE NO

Use the following 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 :

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

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI -------- ----- -------- --------- ------------- ----------- --- 4 70 11171 TESTUSER SELECT BASIC NO

2) 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.

 Query the session status, we can see a new failed over session with different ‘SID, serial#’ created, 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='TESTUSER';

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAILED_OVER -------- ---- ------- -------- ------------- --------- ------------ 4 366 14271 TESTUSER SELECT BASIC YES

3) Instead of killing session in step 2, 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 TETSUSER SELECT BASIC NO

Now stop the service on node4, expect the session will failover onto survival node1 .

$ 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='TESTUSER';

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

restart the service on node4 for more tests.

$ srvctl start service -s RAC_TEST -i TESTDB4 -d TESTDB

Client Side TAF Configuration — PRECONNECT

Two tnsname entries are prepared with following contents:

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='TESTUSER';

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

2) Manually stop service RAC_TEST on node 4.

$ srvctl stop service -s RAC_TEST -i TESTDB4 -d TESTDB -f

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

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

INST_ID SID SERIAL# USERNAME FAILOVER_TYPE FAILOVER_M FAI STATUS ------- --- ------- ---------- ------------ ------------- --- -------- 1 42 8485 TESTUSER SELECT PRECONNECT YES ACTIVE

Restart service on node4 for next test.

$ srvctl start service -s RAC_TEST -i -i TESTDB4 -d TESTDB

4) if you kill this PRECONNECT session 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='TESTUSER';

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

TAF – Transparent Application Failover is a very useful feature in mission critical environment, and it should be used whenever it is available and possible.

Server Side Load Balancing for Oracle RAC

Server side load balancing is achieved via REMOTE_LISTENER on each node, so make sure both LOCAL_LISTENER and REMOTE_LISTENER are configured correctly on every RAC node to guarantee server side load balancing functions properly.

From 10gR2 the service can be set up to use load balancing advisory. This means connections can be routed using SERVICE TIME or THROUGHPUT.

The tests are held under the following environment. In order to show the concept of server side load balancing more clearly, I deliberately use 11gR2 databases for testing the behaviors of server side load balancing by using VIP hosts instead of SCAN. In this case, make sure all nodes VIPs are included in REMOTE_LISTENER because SCAN is only used as default in REMOTE_LISTENER from 11gR2 onwards.

                     GI: 12.1.0.2.0
                  RAC: 11.2.0.4
Oracle Client: 11.2.0.4

This shell script is used to create multiple concurrent sessions to simulate connections from clients.

$ cat create_multi_sessions.sh
#!/bin/bash
for ((i=1; i <= 40 ; i++))
do
nohup sqlplus -S testuser/password@RACTEST_NODE1_VIP<< EOT &
begin
dbms_lock.sleep(20);
end;
/
EOT
done

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

$srvctl modify service -s RAC_TEST -d TESTDB -h

Usage: srvctl modify service -d <db_unique_name> -s <service_name> [-g <pool_name>] [-c {UNIFORM | SINGLETON}] [-P {BASIC|NONE}] [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y {AUTOMATIC | MANUAL}][-q {true|false}] [-x {true|false}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z <integer>] [-w <integer>] [-t <edition>]
 -d <db_unique_name> Unique name for the database
 -s <service> Service name
 -g <pool_name> Server pool name
 -c {UNIFORM | SINGLETON} Service runs on every active server in the server pool hosting this service (UNIFORM) or just one server (SINGLETON)
 -P {NONE | BASIC} TAF policy specification
 -l <role> Role of the service (primary, physical_standby, logical_standby, snapshot_standby)
 -y <policy> Management policy for the service (AUTOMATIC or MANUAL)
 -e <Failover type> Failover type (NONE, SESSION, or SELECT)
 -m <Failover method> Failover method (NONE or BASIC)
 -w <integer> Failover delay
 -z <integer> Failover retries
 -t <edition> Edition (or "" for empty edition value)
 -j <clb_goal> Connection Load Balancing Goal (SHORT or LONG). Default is LONG.
 -B <Runtime Load Balancing Goal> Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)
 -x <Distributed Transaction Processing> Distributed Transaction Processing (TRUE or FALSE)
 -q <AQ HA notifications> AQ HA notifications (TRUE or FALSE)
 -h Print usage

Configure Connection Load Balancing ( CLB_GOAL )

LONG: Use the LONG connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. It does not matter if GOAL is set or not for this condition as the point behind this setting is to balance based on number of sessions. LONG is the default connection load balancing goal.

Example to modify service RAC_TEST and set CLB_GOAL LONG.

$srvctl modify service -s RAC_TEST -d TESTDB -j LONG

or by using old fashion DBMS_SERVICE which is deprecated:

SQL>EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST', clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

By using following TNS entry, we can see the sessions are balanced between nodes where services are running on preferred node 1 and node 4.

RACTEST_NODE1_VIP =
 (DESCRIPTION = 
 (ADDRESS = (PROTOCOL = TCP)(HOST =node1-vip)(PORT = 1521))
(CONNECT_DATA =
 (SERVICE_NAME = RAC_TEST)
 )
 )
SQL>select inst_id, username, count(*) 
from gv$session
where username='TESTUSER'
group by inst_id, username;

INST_ID USERNAME COUNT(*)
---------- ---------- ----------
1 TESTUSER 20
4 TESTUSER 20


Run query against sys$service_metrics_tab :

20141101-10:59:59 SYS$RLBTYP('RAC_TEST', 'VERSION=1.0 database=TESTDB service=RAC_TEST { {instance=TESTDB1 percent=50 flag=UNKNOWN aff=TRUE}{instance=TESTDB2 percent=50 flag=UNKNOWN aff=TRUE} } timestamp=2014-11-01 21:59:59') SYS$SERVICE_METRICS

SHORT: Use the SHORT connection load balancing method for applications that have short-lived connections. The database uses first the GOAL setting to have PMON tell the Listener which node to prefer.

Example to modify service RAC_TEST and set CLB_GOAL short:

$srvctl modify service -s RAC_TEST -d TESTDB -j SHORT

or by using old fashion DBMS_SERVICE which is deprecated already:

SQL>EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST', CLB_GOAL => DBMS_SERVICE.CLB_GOAL_SHORT);

Please note for 11.2 onwards, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes.

Configure the Load Balancing Advisory ( GOAL )

None(0):When set to 0(NONE), this disables the ONS notifications to the Client as to the load of the various nodes.

SERVICE TIME:
Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. Good example is for application  of internet shopping system where work requests are various lengths.

To modify  service RAC_TEST and set up the service to use SERVICE_TIME.

$srvctl modify service -s RAC_TEST -d TESTDB -j SHORT -B SERVICE_TIME
SQL>EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST'
, goal => DBMS_SERVICE.GOAL_SERVICE_TIME -
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);
SQL>select inst_id, username, count(*) 
from gv$session
where username='TETSUSER'
group by inst_id, username;

INST_ID USERNAME COUNT(*)
---------- --------- ----------
1 TESTUSER 28
4 TESTUSER 12


Run query against sys$service_metrics_tab :

20141101-10:55:29
SYS$RLBTYP('RAC_TEST', 'VERSION=1.0 database=TESTDB service=RAC_TEST { {instance=TESTDB1 percent=74 flag=GOOD aff=TRUE}{instance=TESTDB2 percent=26 flag=GOOD aff=TRUE} } timestamp=2014-11-01 21:55:29')
SYS$SERVICE_METRICS

THROUGHPUT:
Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. Instead of figuring out how long something takes, it is the frequency this work occurs that is used. So if node one is able to handle 10 transactions, while node two can handle 12, in the same amount of time, then the client will be told to go to node two. So even if node two will take longer to handle a specific job, it can handle more jobs at one time then node. Good example is for application of trading system where work requests are similar lengths.

To modify  service RAC_TEST and set up the service to use throughput.

$srvctl modify service -s RAC_TEST -d TESTDB -j SHORT -B THROUGHPUT
SQL> EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST' -
, goal => DBMS_SERVICE.GOAL_THROUGHPUT -
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);
SQL>select inst_id, username, count(*) 
from gv$session
where username='TESTUSER'
group by inst_id, username;

INST_ID USERNAME COUNT(*)
-------- ---------- ----------
1 TESTUSER 8
4 TESTUSER 32
20141101-10:35:28
SYS$RLBTYP('RAC_TEST', 'VERSION=1.0 database=TESTDB service=RAC_TEST { {instance=TESTDB1 percent=14 flag=GOOD aff=TRUE}{instance=TESTDB2 percent=86 flag=GOOD aff=TRUE} } timestamp=2014-11-01 21:35:28')
SYS$SERVICE_METRICS

You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views

Also see the service performance real time data in  gv$servicemetric view.