Types of Workload Distribution (Load Balancing) in Oracle RAC

Types of Workload Distribution

With RAC, multiple listeners on multiple nodes can be configured to handle client connection requests for the same database service. A multiple-listener configuration enables you to leverage the following failover and load-balancing features:

• Client-side connect-time load balancing
• Client-side connect-time failover
• Server-side connect-time load balancing

These features can be implemented either one by one, or in combination with each other. Moreover, if you are using connection pools, you can benefit from readily available runtime connection load balancing to distribute the client work requests across the pool of connections established by the middle tier. This possibility is offered by the Oracle Universal Connection Pool (UCP) for Java feature as well as Oracle Data Provider for .NET (ODP.NET) connection pool.

Client-Side Connect-Time Load Balancing

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.

ERP =
  (DESCRIPTION =
   (ADDRESS_LIST =
     (LOAD_BALANCE=ON)
     (ADDRESS=(PROTOCOL=TCP)(HOST=node1vip)(PORT=1521))
     (ADDRESS=(PROTOCOL=TCP)(HOST=node2vip)(PORT=1521))
  )
  (CONNECT_DATA=(SERVICE_NAME=ERP))

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 the three IP addresses you defined for the SCAN, unless you are using EZConnect.

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.

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.

Fast Application Notification (FAN): Overview

FAN is a high-availability notification mechanism that Oracle RAC uses to notify other processes about configuration and service-level information that includes service status changes, such as UP or DOWN events. The Oracle client drivers and Oracle connection pools respond to FAN events and take immediate action. FAN UP and DOWN events can apply to instances, services, and nodes.

Oracle connection pools, for example, use FAN to receive very fast detection of failures, to balance connections following failures, and to balance connections again after the failed components are repaired. So, when a service at an instance starts, the FAN event is used immediately to route work to that resource. When a service at an instance or node fails, the FAN event is used immediately to interrupt applications to recover.

Using FAN events eliminates applications waiting on TCP timeouts, time wasted processing the last result at the client after a failure has occurred, and time wasted executing work on slow, hung, or dead nodes. For cluster configuration changes, the Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster. Instead of waiting for the application to time out against the database and detect a problem, applications can receive FAN events and react immediately. With FAN, in-flight transactions are immediately terminated and the client notified when the instance fails.

FAN also publishes load balancing advisory events. Applications can take advantage of the load balancing advisory FAN events to direct work requests to the instance in the cluster that is currently providing the best service quality.

Fast Application Notification: Benefits

Traditionally, client or mid-tier applications connected to the database have relied on connection timeouts, out-of-band polling mechanisms, or other custom solutions to realize that a system component has failed. This approach has huge implications in application availability because down times are extended and more noticeable.

With FAN, important high-availability events are pushed as soon as they are detected, which results in a more efficient use of existing computing resources, and a better integration with your enterprise applications, including mid-tier connection managers, or IT management consoles, including trouble ticket loggers and email/paging servers.

FAN is, in fact, a distributed system that is enabled on each participating node. This makes it very reliable and fault tolerant because the failure of one component is detected by another. Therefore, event notification can be detected and pushed by any of the participating nodes. FAN events are tightly integrated with Oracle JDBC Universal Connection Pool, ODP.NET connection pool, OCI session pool, Oracle WebLogic Server Active Gridlink for Oracle RAC, and OCI and ODP.NET clients. This includes applications that use Application Continuity or Transaction Guard. For example, Oracle JDBC applications managing connection pools do not need custom code development. They are automatically integrated with the ONS if implicit connection cache and fast connection failover are enabled.

Implementing FAN Events

You can take advantage of FAN events in the following three ways:

1. Your application can use FAN without programmatic changes if you use an integrated Oracle client. The integrated clients for FAN events include Oracle JDBC Universal Connection Pool, ODP.NET connection pool, OCI session pool, Oracle WebLogic Server Active Gridlink for Oracle RAC, and OCI and ODP.NET clients. This includes applications that use Application Continuity or Transaction Guard. The integrated Oracle clients must be Oracle Database 10g release 2 or later to take advantage of the FAN high-availability events. The pooled clients can also take advantage of the load balancing advisory FAN events.
2. Applications can use FAN programmatically by using the JDBC and Oracle RAC FAN application programming interface (API) or by using callbacks with OCI and ODP.NET to subscribe to FAN events and to execute event handling actions upon the receipt of an event.
3. You can implement FAN with server-side callouts on your database tier. If you use one of the integrated clients listed in item 1 of the preceding list, then, for DOWN events, the disruption to the application is minimized because the FAN-aware client terminates the sessions to the failed instance or node before they are reused. Incomplete transactions are terminated and the application user is immediately notified. Application users who request connections are directed to available instances only.

For UP events when services and instances are started, new connections are created so the application can quickly take advantage of the extra hardware resources or additional capacity.

FAN and Oracle Integrated Clients

Fast Application Notification (FAN) enables end-to-end, lights-out recovery of applications and load balancing based on real transaction performance in a RAC environment. Applications use the FAN high availability (HA) events to achieve very fast detection of failures, balancing of connection pools following failures, and distribution of connections again when the failed components are repaired.

The FAN events carrying load balancing advice help connection pools consistently deliver connections to available instances that provide the best service. FAN HA is integrated with:

• JDBC-thin
• OCI drivers
• JDBC Universal Connection Pool (and the deprecated Implicit Connection Cache)
• OCI session pools
• ODP.NET connection pool, and
• Oracle WebLogic Server Active GridLink for Oracle RAC.

Because of integration with FAN, integrated clients are more aware of the current status of a RAC cluster. This prevents client connections from waiting or trying to connect to instances or services that are no longer available. When instances start, Oracle RAC uses FAN to notify the connection pool so that the connection pool can create connections to the recently started instance and take advantage of the additional resources that this instance provides.

Oracle client drivers that are integrated with FAN can:

• Remove terminated connections immediately when a service is declared DOWN at an instance, and immediately when nodes are declared DOWN
• Report errors to clients immediately when Oracle Database detects the NOT RESTARTING state, instead of making the client wait while the service repeatedly attempts to restart

Oracle connection pools that are integrated with FAN can:

• Balance connections across all of the Oracle RAC instances when a service starts; this is preferable to directing the sessions that are defined for the connection pool to the first Oracle RAC instance that supports the service
• Balance work requests at run time using load balancing advisory events

The use of client drivers or connection pools and FAN requires that you properly configure the Oracle Notification Service to deliver the FAN events to the clients. In addition, for load balancing, configure database connection load balancing across all of the instances that provide the services used by the connection pool. Oracle recommends that you configure both client-side and server-side load balancing with Oracle Net Services. If you use DBCA to create your database, then both client-side and server-side load balancing are configured by default.

FAN-Supported Event Types

FAN delivers events pertaining to the list of managed cluster resources shown in the slide. The table describes each of the resources.

FAN Event Reasons

The event status for each managed resource is associated with an event reason. The reason further describes what triggered the event. The table in the slide gives you the list of possible reasons with a corresponding description.

FAN Event Status

This table describes the event status for each of the managed cluster resources seen previously.

FAN Event Format

In addition to its type, status, and reason, a FAN event has other payload fields to further describe the unique cluster resource whose status is being monitored and published:

• The event payload version
• The name of the primary or shadow application service. This name is excluded from NODE events.
• The name of the RAC database, which is also excluded from NODE events
• The name of the RAC instance, which is excluded from SERVICE, DATABASE, and NODE events
• The name of the cluster host machine, which is excluded from SERVICE and DATABASE events
• The service cardinality, which is excluded from all events except for SERVICE STATUS=UP events
• The server-side date and time when the event is detected

The general FAN event format is described in the slide along with possible FAN event examples. Note the differences in event payload for each FAN event type.

<Event_Type>
VERSION=<n.n>
[service=<serviceName.dbDomainName>]
[database=<dbName>] [instance=<sid>] [host=<hostname>]
status=<Event_Status>
reason=<Event_Reason>
[card=<n>]
timestamp=<eventDate> <eventTime>

SERVICE VERSION=1.0 service=ERP.example.com
database=ORCL status=up reason=user card=4 timestamp=16-Jul-2013 13:21:11

NODE VERSION=1.0 host=host01
status=nodedown timestamp=16-Jul-2013 11:42:05

Load Balancing Advisory: FAN Event

The Load Balancing Advisory FAN event is described in the slide. Basically, it contains a calculated percentage of work requests that should be sent to each instance. The flag indicates the behavior of the service on the corresponding instance relating to the thresholds set on that instance for the service. The easiest way to take advantage of these events is to use the run-time connection load balancing feature of an Oracle integrated client such as JDBC, Universal Connection Pool, ODP.NET Connection Pools, OCI session pools, or Oracle WebLogic Server Active GridLink for Oracle RAC. Other client applications can take advantage of FAN programatically by using the Oracle RAC FAN API to subscribe to FAN events and execute event-handling actions upon receipt. Here is an example:

Notification Type: database/event/servicemetrics/prod
VERSION=1.0 database=PROD service=myServ { {instance=PROD2 percent=38 flag=GOOD 
aff=TRUE}{instance=PROD3 percent=62 flag=GOOD aff=TRUE} } timestamp=2013-07-30 08:47:06

Server-Side Callouts Implementation

Each database event detected by the RAC High Availability (HA) framework results in the execution of each executable script or program deployed in the standard Oracle Clusterware callout directory. On UNIX, it is [Grid Home]/racg/usrco. Unless your Oracle Clusterware home directory is shared across the network, you must deploy each new callout on each RAC node. The order in which these callouts are executed is nondeterministic. However, RAC guarantees that all callouts are invoked once for each recognized event in an asynchronous fashion. Thus, it is recommended to merge callouts whose executions need to be in a particular order.

You can install as many callout scripts or programs as your business requires, provided each callout does not incur expensive operations that delay the propagation of HA events. If many callouts are going to be written to perform different operations based on the event received, it might be more efficient to write a single callout program that merges each single callout.

Writing server-side callouts involves the steps shown in the slide. In order for your callout to identify an event, it must parse the event payload sent by the RAC HA framework to your callout. After the sent event is identified, your callout can filter it to avoid execution on each event notification. Then, your callout needs to implement a corresponding event handler that depends on the event itself and the recovery process required by your business.

Server-Side Callout Parse: Example

#!/bin/sh
NOTIFY_EVENTTYPE=$1
for ARGS in $*; do
 PROPERTY=`echo $ARGS | $AWK -F"=" '{print $1}'`
 VALUE=`echo $ARGS | $AWK -F"=" '{print $2}'`
 case $PROPERTY in
   VERSION|version)     NOTIFY_VERSION=$VALUE ;;
   SERVICE|service)     NOTIFY_SERVICE=$VALUE ;;
   DATABASE|database)   NOTIFY_DATABASE=$VALUE ;;
   INSTANCE|instance)   NOTIFY_INSTANCE=$VALUE ;;
   HOST|host)           NOTIFY_HOST=$VALUE ;;
   STATUS|status)       NOTIFY_STATUS=$VALUE ;;
   REASON|reason)       NOTIFY_REASON=$VALUE ;;
   CARD|card)           NOTIFY_CARDINALITY=$VALUE ;;
   TIMESTAMP|timestamp) NOTIFY_LOGDATE=$VALUE ;; 
   ??:??:??)            NOTIFY_LOGTIME=$PROPERTY ;; 
  esac
done

Unless you want your callouts to be executed on each event notification, you must first identify the event parameters that are passed automatically to your callout during its execution. The example above shows you how to parse these arguments by using a sample Bourne shell script.

The first argument that is passed to your callout is the type of event that is detected. Then, depending on the event type, a set of PROPERTY=VALUE strings are passed to identify exactly the event itself. The script given in the slide identifies the event type and each pair of PROPERTY=VALUE strings. The data is then dispatched into a set of variables that can be used later in the callout for filtering purposes.

As mentioned in the previous slide, it might be better to have a single callout that parses the event payload, and then executes a function or another program on the basis of information in the event, as opposed to having to filter information in each callout. This becomes necessary only if many callouts are required.

Server-Side Callout Filter: Example

if ((( [ $NOTIFY_EVENTTYPE = "SERVICE" ]   ||
       [ $NOTIFY_EVENTTYPE = "DATABASE" ]  || \
       [ $NOTIFY_EVENTTYPE = "NODE" ]         \
    ) &&                                      \
   ( [ $NOTIFY_STATUS = "not_restarting" ] || \
     [ $NOTIFY_STATUS = "restart_failed" ]    \
   )) &&                                      \
   ( [ $NOTIFY_DATABASE = "HQPROD" ]       || \
     [ $NOTIFY_SERVICE = "ERP" ]              \
  ))
then
    /usr/local/bin/logTicket $NOTIFY_LOGDATE \
                             $NOTIFY_LOGTIME \
                             $NOTIFY_SERVICE \
                             $NOTIFY_DBNAME  \
                             $NOTIFY_HOST 
fi

The example above shows you a way to filter FAN events from a callout script. This example is based on the example in the section. Now that the event characteristics are identified, this script triggers the execution of the trouble-logging program /usr/local/bin/logTicket only when the RAC HA framework posts a SERVICE, DATABASE, or NODE event type, with a status set to either not_restarting or restart_failed, and only for the production HQPROD RAC database or the ERP service.

It is assumed that the logTicket program is already created and that it takes the arguments shown above. It is also assumed that a ticket is logged only for not_restarting or restart_failed events, because they are the ones that exceeded internally monitored timeouts and seriously need human intervention for full resolution.

Server-Side ONS

usesharedinstall=true 
allowgroup=true 
localport=6100            # line added by Agent
remoteport=6200           # line added by Agent
nodes=host01:6200         # line added by Agent

The ONS configuration is controlled by the [GRID_HOME]/opmn/conf/ons.config configuration file. This file is automatically created during installation. Starting with Oracle Database 11g Release 2 (11.2) it is automatically maintained by the CRS ONS agent using information stored in the OCR. There are three important parameters that are always configured for each ONS:

• The first is localport, the port that ONS uses to talk to local clients.
• The second is remoteport, the port that ONS uses to talk to other ONS daemons.
• The third parameter is called nodes. It specifies the list of other ONS daemons to talk to. This list includes all RAC ONS daemons, and all mid-tier ONS daemons. Node values are given as either host names or IP addresses followed by their remoteport. This information is stored in Oracle Cluster Registry (OCR)

In the example above, it is assumed that ONS daemons are already started on each cluster node. This should be the default situation after a correct RAC installation.

Optionally Configuring the Client-Side ONS

Oracle Database 11g Release 2 introduced a new set of APIs for Oracle RAC Fast Application Notification (FAN) events. These APIs provide an alternative for taking advantage of the high-availability (HA) features of Oracle Database if you do not use Universal Connection Pool or Oracle JDBC connection caching. These APIs are not a part of Oracle JDBC APIs. For using Oracle RAC Fast Application Notification, the ons.jar file must be present in the CLASSPATH or an Oracle Notification Services (ONS) client must be installed and running in the client system. To use ONS on the client-side, you must configure all the RAC nodes in the ONS configuration file. A sample configuration file might look like the one shown above.

After configuring ONS, you start the ONS daemon with the onsctl start command. It is your responsibility to make sure that an ONS daemon is running at all times. You can check that the ONS daemon is active by executing the onsctl ping command.

UCP JDBC Fast Connection Failover: Overview

Oracle Universal Connection Pool (UCP) provides a tight integration with Oracle RAC database features like Fast Connection Failover (FCF). Basically, FCF is a FAN client implemented through the connection pool. FCF quickly and automatically recovers lost or damaged connections. This automatic connection management results from FAN events received by the local ONS daemon, or by a remote ONS if a local one is not used, and is handled by a special event handler thread. Both JDBC thin and JDBC OCI drivers are supported.

Therefore, if UCP and FCF are enabled, your Java program automatically becomes an ONS subscriber without having to manage FAN events directly. Whenever a service or node down event is received by the mid-tier ONS, the event handler automatically marks the corresponding connections as down and cleans them up. This prevents applications that request connections from the cache from receiving invalid or bad connections.

Whenever a service up event is received by the mid-tier ONS, the event handler recycles some unused connections and reconnects them using the event service name. The number of recycled connections is automatically determined by the connection cache. Because the listeners perform connection load balancing, this automatically rebalances connections across the preferred instances of the service without waiting for connection requests or retries.

JDBC/ODP.NET FCF Benefits

By enabling FCF, your existing Java applications connecting through Oracle JDBC and application services, or your .NET applications using ODP.NET connection pools and application services benefit from the following:

• All database connections are balanced across all RAC instances that support the new service name, instead of having the first batch of sessions routed to the first RAC instance. This is done according to the Load Balancing Advisory algorithm you use (see the next slide). Connection pools are rebalanced upon service, instance, or node up events.
• The connection cache immediately starts placing connections to a particular RAC instance when a new service is started on that instance.
• The connection cache immediately shuts down stale connections to RAC instances where the service is stopped on that instance, or whose node goes down.
• Your application automatically becomes a FAN subscriber without having to manage FAN events directly by just setting up flags in your connection descriptors.
• An exception is immediately thrown as soon as the service status becomes not_restarting, which avoids wasteful service connection retries.

Load Balancing Advisory

Load balancing distributes work across all of the available RAC database instances. Well-written applications use persistent connections that span the instances of RAC offering a service. Connections are created infrequently and exist for a long duration. Work comes into the system with high frequency, borrows these connections, and exists for a relatively short duration.

The Load Balancing Advisory has the task of advising the direction of incoming work to the RAC instances that provide optimal quality of service for that work. The LBA algorithm uses metrics sensitive to the current performance of services across the system.

The load balancing advisory is deployed with key Oracle clients, such as a listener, the JDBC universal connection pool, OCI session pool, Oracle WebLogic Server Active GridLink for Oracle RAC, and the ODP.NET Connection Pools. Third-party applications can also subscribe to load balancing advisory events by using JDBC and Oracle RAC FAN API or by using callbacks with OCI.

Using the Load Balancing Advisory for load balancing recognizes machine power differences, sessions that are blocked in wait, failures that block processing, as well as competing services of different importance. Using the Load Balancing Advisory prevents sending work to nodes that are overworked, hung, or failed.

UCP JDBC/ODP.NET Runtime Connection Load Balancing: Overview

Without using the Load Balancing Advisory, work requests to RAC instances are assigned on a random basis, which is suitable when each instance is performing equally well. However, if one of the instances becomes more burdened than the others because of the amount of work resulting from each connection assignment, the random model does not perform optimally.

The Runtime Connection Load Balancing feature provides assignment of connections based on the Load Balancing Advisory information from the instances in the RAC cluster. The Connection Cache assigns connections to clients on the basis of a relative number indicating what percentage of work requests each instance should handle.

In the diagram above, the feedback indicates that the CRM service on INST1 is so busy that it should service only 10% of the CRM work requests; INST2 is so lightly loaded that it should service 60%; and INST3 is somewhere in the middle, servicing 30% of requests. Note that these percentages apply to, and the decision is made on, a per-service basis. In this example, CRM is the service in question.

Oracle Net Services provides the ability to distribute client connections across the instances in a RAC configuration. With server-side load balancing, the SCAN listener directs a connection request to the best instance currently providing the service, based on the –clbgoal setting for the service.

When you create a RAC database with DBCA, it automatically configures and enables server-side load balancing. A sample client-side load balancing connection definition in the tnsnames.ora file on the server is also created. 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. the goal can be either LONG or SHORT for connection load balancing. These goals have the following characteristics:

1. SHORT: Use the SHORT connection load balancing method for applications that use run-time load balancing. When using connection pools that are integrated with Load Balancing Advisory, set the goal to SHORT.

$ srvctl modify service -db orcl -service oltpapp -clbgoal SHORT

2. LONG: Use the LONG method if run-time load balancing is not required. This is typical for batch operations. LONG is the default connection load balancing goal.

$ srvctl modify service -db orcl -service batchconn -clbgoal LONG

Setting the run-time connection load balancing goal to NONE disables load balancing for the service. You can see the goal settings for a service in the data dictionary by querying the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views. You can also review the load balancing settings for a service using Oracle Enterprise Manager.

Monitoring LBA FAN Events

You can use the SQL query shown below to monitor the Load Balancing Advisory FAN events for each of your services.

SQL> SELECT TO_CHAR(enq_time, 'HH:MI:SS') Enq_time, user_data
   2 FROM sys.sys$service_metrics_tab
   3 ORDER BY 1 ;

ENQ_TIME   USER_DATA
--------   -----------------------------------------------------
...
04:19:46   SYS$RLBTYP('MYSERV', 'VERSION=1.0 database=orcl
           service=MYSERV { {instance=orcl_2 percent=50
           flag=UNKNOWN}{instance=orcl_1 percent=50 flag=UNKNOWN} 
           } timestamp=2013-07-19 11:07:32')
04:20:16   SYS$RLBTYP('MYSERV', 'VERSION=1.0 database=orcl
           service=MYSERV { {instance=orcl_2 percent=80
           flag=UNKNOWN}{instance=orcl_1 percent=20 flag=UNKNOWN} 
           } timestamp=2013-07-19 11:08:11')
SQL>