Mastering Oracle RAC Connect Time Load Balancing: Configurations and Runtime Strategies

Connect Time Load Balancing Overview

Connect Time Load Balancing determines which Oracle RAC node a client connects to when establishing a new session. It has two variants: client‑side (performed by the client) and server‑side (performed by the database listeners).

Client‑Side Connect Time Load Balancing (Pre‑11gR2)

Before Oracle RAC 11gR2, enable client‑side load balancing by defining multiple VIP addresses and setting LOAD_BALANCE=ON in tnsnames.ora:

(DESCRIPTION=
  (ADDRESS_LIST=
    (LOAD_BALANCE=ON)
    (ADDRESS=(PROTOCOL=TCP)(HOST=RAC1-vip)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=RAC2-vip)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=RAC3-vip)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=RAC4-vip)(PORT=1521))
  )
  (CONNECT_DATA=(SERVICE_NAME=RAC10g))
)

The client randomly selects one of the VIPs, achieving load distribution.

Impact of SCAN in 11gR2

Oracle 11gR2 introduces SCAN (Single Client Access Name). LOAD_BALANCE=ON is effective only when multiple SCAN VIPs exist; the client then randomly picks a SCAN VIP. If only a single SCAN VIP is configured (e.g., via a hosts file), client‑side Connect Time Load Balancing does not occur.

Server‑Side Connect Time Load Balancing

All RAC versions implement server‑side load balancing by configuring local_listener and remote_listener. The following examples show version‑specific configurations.

RAC 9iR2 Example (4‑node cluster)

Define listeners in each node’s tnsnames.ora:

LISTENER_RAC1 = (ADDRESS = (PROTOCOL = TCP)(HOST = RAC1-ip)(PORT = 1521))
LISTENER_RAC2 = (ADDRESS = (PROTOCOL = TCP)(HOST = RAC2-ip)(PORT = 1521))
LISTENER_RAC3 = (ADDRESS = (PROTOCOL = TCP)(HOST = RAC3-ip)(PORT = 1521))
LISTENER_RAC4 = (ADDRESS = (PROTOCOL = TCP)(HOST = RAC4-ip)(PORT = 1521))
LISTENERS_RAC = (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = RAC1-ip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = RAC2-ip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = RAC3-ip)(PORT = 1521))
  (ADDRESS = (PROTOCOL = TCP)(HOST = RAC4-ip)(PORT = 1521))
)

Set initialization parameters (example):

RAC1.local_listener=LISTENER_RAC1
RAC2.local_listener=LISTENER_RAC2
RAC3.local_listener=LISTENER_RAC3
RAC4.local_listener=LISTENER_RAC4
*.remote_listener=LISTENERS_RAC

Each listener knows its own load and the load of other nodes, allowing a second‑level load balancing where a heavily loaded listener can redirect connections to a lighter node.

RAC 10gR2 Example

Configuration is similar to 9iR2 but listeners monitor VIP hostnames instead of public IPs. Use the same local_listener and remote_listener settings with VIP addresses.

RAC 11gR2 Example

Because SCAN is present, remote_listener should point to SCAN:port. Example tnsnames.ora entry:

(DESCRIPTION =
  (FAILOVER=ON)(LOAD_BALANCE=ON)
  (ADDRESS = (PROTOCOL = TCP)(HOST = MySCAN)(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =RAC11g))
)

Listener definitions use VIP addresses, and the initialization parameters are:

RAC1.local_listener=LISTENER_RAC1
RAC2.local_listener=LISTENER_RAC2
RAC3.local_listener=LISTENER_RAC3
RAC4.local_listener=LISTENER_RAC4
*.remote_listener=MySCAN:1521

The SCAN listener knows the load of all RAC nodes and can redirect a client‑side balanced connection to the least‑loaded node.

Load‑Balancing Decision Parameters

The view v$servicemetric provides metrics used by the server‑side load balancer:

GOODNESS – higher values indicate heavier load (lower probability of being chosen).

DELTA – estimated load increase for an additional session.

FLAGS – status bits (0 = all good, 1 = blocked, 2 = threshold crossed, 4 = goodness unknown).

Each service has CLB_GOAL (Client Load Balance Goal) and GOAL: CLB_GOAL can be LONG (default) or SHORT. LONG bases load on the number of connected sessions. SHORT enables Load‑Balancing Advisory (LBA) and requires a GOAL value. GOAL can be THROUGHPUT, SERVICE_TIME, or NONE. THROUGHPUT evaluates CPU‑related metrics. SERVICE_TIME evaluates response‑time‑related metrics. NONE disables LBA.

Runtime Connection Load Balancing

Connect Time Load Balancing does not guarantee that a connection retrieved from a pool points to a low‑load node because the pool may contain connections created earlier. Fast Application Notification (FAN) solves this by:

Marking connections to failed instances/services as invalid (Fast Connection Failover, FCF).

Subscribing to LBA events so the pool can select a connection to the currently least‑loaded node.

JDBC Runtime Connection Load Balancing

Configure JDBC Fast Connection Failover as described in the JDBC FCF example.

Enable LBA events with the command:

srvctl modify service -d RAC11g -s Email -B SERVICE_TIME -j SHORT

which sets CLB_GOAL=SHORT and GOAL=SERVICE_TIME.

ODP.NET Runtime Connection Load Balancing

Enable AQ notification and set goals:

srvctl modify service -d RAC11g -s Email -q TRUE -B SERVICE_TIME -j SHORT

Set aq_tm_processes=1 in the database.

Grant dequeue privilege to the application user:

exec dbms_aqadm.grant_queue_privilege('DEQUEUE','SYS.SYS$SERVICE_METRICS','<your_username>');

Add Load Balancing=true to the .NET connection string, e.g.:

User Id=user_name;Password=password;Data Source=odpapp;Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;Load Balancing=true;Incr Pool Size=5;Decr Pool Size=2

TCP Timeout Tuning

On 32‑bit Windows the default OS‑level TNS timeout is about 20 seconds. To change it:

Set SQLNET.OUTBOUND_CONNECT_TIMEOUT=5 in sqlnet.ora to reduce the timeout to 5 seconds.

Alternatively, specify CONNECT_TIMEOUT and RETRY_COUNT in the tnsnames.ora entry. Example for a 15‑second effective timeout (5 s per attempt × 3 retries):

CUIHUA112 =
 (DESCRIPTION =
   (CONNECT_TIMEOUT=5)(RETRY_COUNT=2)
   (ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.190.11)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = cuihua112)
   )
 )

If both sqlnet.ora and tnsnames.ora define a timeout, the tnsnames.ora value overrides the former.