Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Feb 15, 2007

Oracle RAC Administration - Part 15: Connection Load Balancing and FAN

By Tarry Singh

Brief intro

In our last article, we looked at the service architecture. We will continue our quest to understand the workload characteristics and the load balancing connections in our Virtualized RAC.

Introduction to Workload Management

Connection Load Balancing

With Oracle Notification Services (ONS), we are able to have our RAC balance our client connections across the nodes. The CLB (connection load balancing) can be handled on the server-side or on the client-side. The client-side takes care of the connection sharing across the Listeners, while server-side load balancing manages and handles the connection requests to the most willingly available node whose information is in turn provided to it by the LBA (Load Balancing Advisory).

This in turn is analyzing the goals that may have been set for the LBA. You can use a long goal or a short goal for this connection load balancing. Let’s take a look at them both quickly.

Long Goals: Use long goals for applications that need long uninterrupted connections. It is the default connection load balancing goal. To see how to implement them, lets take a simple syntax. Using the DBMS_SERVICE and CLB_GOAL_LONG packages, you can define the connection load balancing for long connections as follows and here FOKESERV is our service:

        , clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

Short Goals: Think of quick and fast transactions like ordering or placing a secure purchase like that on Amazon or any other place. You would prefer to fill the order up fast and if, for any reason the process is delayed, the connection is restarted, ensuring that the transaction is performed in a short-lived connection.  Checking the syntax, here CARTSERV is the service in question:


Note: When you install your RAC with the DBCA (Database Configuration Assistant), server-side load balancing is enabled by default. Also check out the sample script in tnsnames.ora file. The services created through the DBCA will have the Connection Load Balancing default setting as CLB_GOAL=CLB_GOAL_LONG.

The ONS maintains client connections until the client decides to close the connection or if a node fails or any other form of outage prevents the continuity of that connection. However, if you configure TAF (transparent application failover) then the connections are moved to another healthy instance. For a typical SARG (Select Arguments), TAF can restart the query. Alternately, from the client side, if you were accessing information on the web, the search would simply carry on without you noticing the failover of the connection to another node. However, if you were in the middle of a transaction (INSERT, UPDATE, or DELETE) then the application must rollback the failed transaction and submit it again. Any other session's customizations must be re-executed. However, in a normal processing scenario the sessions don’t move irrespective of the workload changes, newer sessions are just redirected to other nodes.

With the services, the deployment of TAF only becomes easier. By defining a TAF policy for a service, all connections using this service will have TAF automatically enabled for them. No client-side intervention is required, meaning the server-side TAF setting will override the TAF setting at the client level. In order to define the TAF policy you will need the DBMS_SERVICE procedure:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'fokeserv.domain.com' 
, aq_ha_notifications => TRUE 
, failover_retries => 180 
, failover_delay => 5 

Client-side load balancing is defined in your client connection with the parameter LOAD_BALANCE=ON (the default is ON). Upon setting this parameter to ON, Oracle will pick out an address from the address list at random and connect to that node, thereby balancing the client connections across the cluster. Run the command lsnrctl services occasionally to see what services a listener supports.

FAN (Fast Application Notification)

As the Oracle RAC manual states:

FAN is a notification mechanism that RAC uses to notify other processes about configuration and service level information such as includes service status changes, such as UP or DOWN events. Applications can respond to FAN events and take immediate action. FAN UP and DOWN events can apply to instances, services, and nodes.

RAC publishes the FAN events the minute any changes are made to the cluster. So, instead of waiting for the application to check on individual nodes to detect an anomaly, the applications are notified by FAN events and are able to react immediately.

FAN also publishes load balancing advisory (LBA) events. Applications are in a position to take full advantage of the LBA FAN events to facilitate a smooth transition of connections to healthier nodes in a cluster. One can take advantage of FAN is the following ways:

  • When using integrated Oracle Client, the applications can use FAN with no programming whatsoever. Oracle 120g JDBC, ODP.NET and OCI would be considered as the components of the integrated clients.

  • Programmatic changes in ONS API make it possible for applications to still subscribe to the FAN events and can execute the event handling actions appropriately.

  • FAN can be implemented with server-side callouts on your database tier.

For instance, a typical DOWN event will prevent any further disruption of service by cleanly terminating the sessions on that failed node and notifying the user. Moreover, a typical UP event can address and allocate extra resources for new incoming requests/ connections. There are however several additional benefits with the server-side callouts, mainly you can utilize FAN in order to:

  • Logging
  • Paging/SMS the DBA and/or to open trouble tickets when the resources fail to (re)start
  • Change resource plans or to shut down services when the number of available instances decreases, thus preventing further load on the cluster and keeping the RAC running until another healthy node is added to the cluster.
  • Automate the fail service back to PREFERRED instances when required.


In this article, we looked at the CLB and FAN. In future articles, we will continue to discuss the services architecture and discuss FAN in detail and take a look at the LBA.

» See All Articles by Columnist Tarry Singh

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM