Connection Load-Balancing

Learn how Oracle Net Services provide the ability to distribute client connections across the instances in an Oracle RAC configuration.

About Connection Load-Balancing

There are two types of load balancing that you can implement: client-side and server-side load balancing.

With client-side load balancing, connection requests are distributed across the listeners, independently at each client. With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service, based on the -clbgoal and -rlbgoal settings for the service.

The SCAN listener is aware of the HTTP protocol. With this awareness, the SCAN can redirect HTTP clients to the appropriate handler, which can reside on different nodes in the cluster, not only the node on which the SCAN listener resides.

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

Server-Side Load Balancing

Using Oracle DBCA to create an Oracle Real Application Clusters (Oracle RAC) database enables you to obtain server-side load-balancing configuration automatically.

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

Configures and enables server-side load balancing
Creates a sample client-side load balancing connection definition in the tnsnames.ora file on the server

The Oracle Clusterware Database Agent is responsible for managing the LISTENER_NETWORKS parameter.

Note:

If you set the REMOTE_LISTENER parameter manually, then set this parameter to scan_name:scan_port.

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:

SHORT: Use the SHORT connection load-balancing method for applications that benefit from using run-time load-balancing. The following is an example of modifying a service using SRVCTL to set the connection load balancing goal to SHORT:
```
$ srvctl modify service -db db_unique_name -service service_name -clbgoal SHORT
```
LONG: Use the LONG connection load-balancing method for applications that benefit from server-side load-balancing and do not require run-time load balancing. LONG is the default connection load balancing goal. The following is an example of modifying a service using SRVCTL to set the connection load balancing goal to LONG:
```
$ srvctl modify service -db db_unique_name -service service_name -clbgoal LONG
```

Generic Database Clients

Oracle Net Services enables you to add the CONNECT_TIMEOUT, RETRY_COUNT, and TRANSPORT_CONNECT_TIMEOUT parameters to the tnsnames.ora connection string.

For example, when using SCAN addresses for the remote listeners at the database:

jdbc:oracle:thin:@(DESCRIPTION =
 (TRANSPORT_CONNECT_TIMEOUT=3)(CONNECT_TIMEOUT=60)
 (RETRY_COUNT=3)(FAILOVER=ON)
 (ADDRESS_LIST =(ADDRESS=(PROTOCOL=tcp)
  (HOST=CLOUD-SCANVIP.example.com)(PORT=5221))
 (CONNECT_DATA=(SERVICE_NAME=orcl)))
Remote_listeners=CLOUD-SCANVIP.example.com:5221

For example, when using remote listeners pointing to VIPs at the database:

jdbc:oracle:thin:@(DESCRIPTION =
 (TRANSPORT_CONNECT_TIMEOUT=3)
 (CONNECT_TIMEOUT=60)(RETRY_COUNT=20)
 (RETRY_DELAY=3)(FAILOVER=ON)
 (ADDRESS_LIST=
 (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP1)(PORT=1521) )
 (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP2)(PORT=1521) )
 (ADDRESS=(PROTOCOL=tcp)(HOST=CLOUD-VIP3)(PORT=1521) ))
 (CONNECT_DATA=(SERVICE_NAME=GOLD)))

The value of these parameters is expressed in seconds. In the preceding examples, Oracle Net waits for 60 seconds for each full connection to receive a response, after which it assumes that a failure occurred and retries the next address in the ADDRESS_LIST. Oracle Net retries the address list 3 times before it returns a failure message to the client. The TRANSPORT_CONNECT_TIMEOUT parameter establishes the time to wait to establish a TCP connection to the database server.

For SCAN, Oracle Net Services tries all three addresses (returned by the SCAN address) before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.

This behavior is called Oracle Net connection failover. If an error is returned from a chosen address in the list, then Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list.

Client-Side Connection Configuration for Older Clients

Learn about the ways you can set up connection failovers and timeouts with Java Database Connectivity (JDBC) Thin Clients, and Oracle Call Interface (OCI) clients.

About Client-Side Connection Configuration for Older Clients

Oracle Net Services provide connection failover and availability features for service requests from older clients.

In addition to client-side load balancing, Oracle Net Services include connection failover. If an error is returned from the chosen address in the list, Oracle Net Services tries the next address in the list until it is either successful or it has exhausted all addresses in its list. For SCAN, Oracle Net Services tries all three addresses before returning a failure to the client. EZConnect with SCAN includes this connection failover feature.

To increase availability, you can specify a timeout that specifies how long Oracle Net waits for a response from the listener before returning an error. The method of setting this timeout parameter depends on the type of client access. Oracle Net maintains these parameters for backward compatibility.

JDBC Thin Clients

You can avoid delays by setting the oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR property

Use the following example to see how to set the property for Java Database Connectivity (JDBC) thin clients:

Properties prop = new Properties ();
prop.put (oracle.net.ns.SQLnetDef.TCP_CONNTIMEOUT_STR,
"" + (1 * 1000)); // 1 second
dbPools[ poolIndex ].setConnectionProperties ( prop );

The parameter value is specified in milliseconds, so you can configure a timeout less than one second. For example, if the application retries connecting, it is possible to reduce the timeout to 500Ms.

OCI Clients

For Oracle Call Interface (OCI) clients, create a local sqlnet.ora file on the client side.

To configure the connection timeout in the sqlnet.ora file, add the following line:

sqlnet.outbound_connect_timeout = number_of_seconds

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

Note:

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

Client-Side Load Balancing

Learn about client-side load balancing, and how a Single Client Access Name (SCAN) can assist with connection loads.

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 AI 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.

If you configured SCAN for connection requests, then client-side load balancing is not relevant for those clients that support SCAN access. When clients connect using SCAN, Oracle Net automatically balances the load of client connection requests across all available IP addresses you defined for the SCAN, unless you are using Easy Connect string.

The SCAN listener redirects the connection request to the local listener of the instance that is least loaded (if -clbgoal is set to SHORT) 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.

Note:

Oracle recommends -clbgoal LONG for client-side load balancing.

If you are using clients that do not support SCAN, then, to use SCAN you must change the client tnsnames.ora to include the SCAN VIPs, and set LOAD_BALANCE=ON to balance requests across the VIPs. For example:

Sales.example.com=(DESCRIPTION=
  (ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.22.67.192)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.22.67.193)(PORT=1521))
    (ADDRESS=(PROTOCOL=TCP)(HOST=172.22.67.194)(PORT=1521)))
    (CONNECT_DATA=(SERVICE_NAME=salesservice.example.com))
  )