Client Side Connect -Time Failover for Oracle RAC

The connect-time failover enables clients to connect to another listener if the initial connection to the first listener fails. The number of listener protocol addresses determines how many listeners are tried. Without connect-time failover, Oracle Net attempts a connection with only one listener. The default is on.

Tnsnames Parameter: FAILOVER

(failover=on) is default for ADDRESS_LISTs, DESCRIPTION_LISTs, and a set of DESCRIPTIONs, therefore, you do not have to specify it explicitly.

The tests are held under the following environment, we use both 11gR2  and 10gR2 clients for testing the behaviors of client side connection failover by using SCAN and non-SCAN host name.

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

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

$ cat create_multi_sessions.sh
#!/bin/bash
for ((i=1; i <= 40 ; i++))
do
  nohup sqlplus -S testuser/password@RACTEST << 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
  • By using VIPs with or without “FAILOVER=ON”, the same results are received from both 11gR2 and 10gR2 clients. All connections go to fourth node where service RAC_TEST is running on, because service RAC_TEST is not running on node2 and node3.
    RACTEST_VIP=
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST =node2-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST =node3-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST =node4-vip)(PORT = 1521))
    (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(*)
---------- -------- --------
4 TESTUSER 40
  • By using VIPs in TNS above with “FAILOVER=OFF”, get error message:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
  • Fail over and load balancing.

By using VIPs adding “LOAD_BALANCE=ON”, get same results from both 11gR2 and 10gR2 clients. All connections are balanced on two nodes where service RAC_TEST are running on.

RACTEST=
(DESCRIPTION =
(ADDRESS_LIST = ( LOAD_BALANCE=ON )( FAILOVER=ON )
(ADDRESS = (PROTOCOL = TCP)(HOST =node2-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =node3-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST =node4-vip)(PORT = 1521))
(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

SCAN provides load balancing and failover for client connections to the database. The SCAN works as a cluster alias for databases in the cluster. Client side Failover and load balancing are always working for TNS entries using SCAN or SCAN IPs, no matter failover and load balancing are turned on/off explicitly.

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.