Load Balancing in Oracle 11gr2 RAC:

Connection Load Balancing in Oracle cluster database
- Workload management in RAC systems

Oracle Net Services provides the ability to balance client connections across the instances in an Oracle RAC configuration. There are two types of load balancing that you can implement: client-side and server-side load balancing. Client-side load balancing balances the connection requests across the listeners. With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.

In an Oracle RAC database, client connections should use both types of connection load balancing.

1) Client-Side Load Balancing
    a) Concept
    b) Add other VIPs to access
    c) Changes to Basic Oracle Clients
    d) Oracle call interface Clients
2) Server-Side Load Balancing
3) For ODP.NET clients

Now we will discuss these points in details.

1) Client-Side Load Balancing

a) Concept
Client-side load balancing is defined in your client connection definition (tnsnames.ora file, for example) by setting the parameter LOAD_BALANCE=ON. When you set this parameter to ON, Oracle Database randomly selects an address in the address list, and connects to that node's listener. This balances client connections across the available SCAN listeners in the cluster.

The SCAN listener redirects the connection request to the local listener of the instance that is least loaded and provides the requested service. When the listener receives the connection request, the listener connects the user to an instance that the listener knows provides the requested service. To see what services a listener supports, run the lsnrctl services command.

When clients connect using SCAN, Oracle Net automatically load balances client connection requests across the three IP addresses you defined for the SCAN, unless you are using EZConnect.

b) Add other VIPs to access :

If you want to use both SCAN and non-SCAN VIPs on the client side, then set the REMOTE_LISTENER parameter on the Oracle RAC database to a mix of SCAN VIPs and a list of node VIPs (you must manually update the REMOTE_LISTENER parameter to include all the SCAN VIPs and all the node VIPs).

The following is an example of the format you use to add SCAN and node VIP information to the tnsnames.ora file for a two-node cluster:

e.g.,

LISTENERS_db_unique_name =
  (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP1)(PORT = scan_port_number))
    (ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP2)(PORT = scan_port_number))
    (ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP3)(PORT = scan_port_number))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node_VIP_name1-vip)(PORT = listener_port_number))
    (ADDRESS = (PROTOCOL = TCP)(HOST = node_VIP_name2-vip)(PORT = listener_port_number))
  )

Then Run:

SQL> ALTER SYSTEM SET REMOTE_LISTENER = 'LISTENERS_db_unique_name' SCOPE=BOTH SID='*';

Alternatively, you can run the following SQL*Plus command to update a two-node cluster:

SQL> ALTER SYSTEM SET remote_listener = '
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP1)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP2)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=scan_VIP3)(PORT = scan_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=node_VIP_name1-vip)(PORT = listener_port_number))
(ADDRESS=(PROTOCOL=TCP)(HOST=node_VIP_name2-vip)(PORT = listener_port_number)))' 
SCOPE=BOTH SID=*;

c) Changes to Basic Oracle Client:

With Oracle Database 11g Release 2 (11.2), Oracle Net Services introduces the ability to add the connect_timeout and retry_count parameters to individual tnsnames.ora connection strings.

(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
The granularity is seconds. Oracle Net waits for 10 seconds to receive a response, after which it assumes a failure. Oracle Net goes through the address list three times before it returns a failure to the client.

e.g.,

oltpapp =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.160.1.1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.160.1.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
(CONNECT_TIMEOUT=10)(RETRY_COUNT=3)
      (SERVICE_NAME = oltpapp)
    )
  )

d) Oracle Call Interface Clients:

For Oracle Call Interface clients, create a local sqlnet.ora file on the client side. Configure the connection timeout in this file by adding the following line:

sqlnet.outbound_connect_timeout = 1

The granularity of the timeout value for the Oracle Call Interface client is in seconds. The sqlnet.ora file affects all connections using this client.

Caution:

Do not configure the connection timeout in the sqlnet.ora file on the server.

2) Server-Side Load Balancing

When you create an Oracle RAC database with DBCA, it automatically:

• Configures and enables server-side load balancing
• Sets the remote listener parameter to the SCAN listener (Note: If you do not use DBCA, you should set the REMOTE_LISTENER database parameter to scan_name:scan_port.)
• Creates a sample client-side load balancing connection definition in the tnsnames.ora file on the server

Example:

SQL> show parameter remote_listener;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_listener                      string      prodscan:1521
SQL> 

FAN, Fast Connection Failover, and the load balancing advisory depend on an accurate connection load balancing configuration that includes setting the connection load balancing goal for the service. You can use a goal of either LONG or SHORT for connection load balancing. These goals have the following characteristics:

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. LONG is the default connection load balancing goal. The following is an example of modifying a service, batchconn, with the srvctl utility to define the connection load balancing goal for long-lived sessions:

srvctl modify service -d db_unique_name -s batchconn -j LONG 

SHORT: Use the SHORT connection load balancing method for applications that have short-lived connections. When using connection pools that are integrated with FAN, set the CLB_GOAL to SHORT. The following example modifies the service known as oltpapp, using SRVCTL to set the connection load balancing goal to SHORT:

srvctl modify service -d db_unique_name -s oltpapp -j SHORT

Click here to know more about FAN concepts and Setup from my blog.

Application High Availability with Services and FAN

Oracle Database focuses on maintaining service availability. In Oracle RAC, Oracle services are designed to be continuously available with loads shared across one or more instances. The Oracle RAC high availability framework maintains service availability by using Oracle Clusterware and resource profiles.

The Oracle RAC high availability framework monitors the database and its services and sends event notifications using FAN. Oracle Clusterware recovers and balances services according to business rules and the service attributes.


3) For ODP.NET Clients:

Enabling ODP.NET Clients to Receive FAN High Availability Events:

ODP.NET connection pools can subscribe to notifications that indicate when nodes, services, and service members are down. After a DOWN event, Oracle Database cleans up sessions in the connection pool that go to the instance and ODP.NET proactively removes connections that are no longer valid. ODP.NET establishes additional connections to existing Oracle RAC instances if the removal of invalid connections reduces the total number of connections to below the value for the MIN_POOL_SIZE parameter.

Perform the following steps to enable FAN for ODP.NET clients:

Enable Advanced Queuing notifications for a service by using SRVCTL as shown in the following example:

srvctl modify service -d crm -s odpnet.example.com -q TRUE

Grant permissions on the internal event queue table by executing the following command for the users that will be connecting by way of the ODP.NET application, where user_name is the database user name:

SQL> EXECUTE DBMS_AQADM.GRANT_QUEUE_PRIVILEGE('DEQUEUE','SYS.SYS$SERVICE_METR
ICS', user_name); 

Enable Fast Connection Failover for ODP.NET connection pools by subscribing to FAN high availability events. To enable FCF, include "HA Events=true" and "pooling=true" (the default value) in the connection string, as shown in the following example where user_name is the name of the database user and password is the password for that user:

con.ConnectionString =
   "User Id=user_name;Password=password;Data Source=odpnet;" +
   "Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
   "HA Events=true;Incr Pool Size=5;Decr Pool Size=2";

more on ODP.NET clients from Oracle Document.

Hope this document help to clear fundamental concept of Load balancing.