8 Using Oracle RAC Features

The following sections are included in this chapter:

Overview of Oracle RAC Features

UCP JDBC connection pools provide a tight integration with various Oracle Real Application Clusters (RAC) Database features. The features include Fast Connection Failover (FCF), Run-Time Connection Load Balancing, and Connection Affinity. These features require the use of an Oracle JDBC driver, Oracle RAC database, and the Oracle Notification Service library (ons.jar) that is included with the Oracle Client software. For those new to these technologies, refer to the Oracle Real Application Clusters Administration and Deployment Guide and the Oracle Database JDBC Developer's Guide.

Note:

Starting from Oracle Database 11g Release 2 (11.2), FCF is also supported by Oracle Restart on a single instance database. Oracle Restart was previously known as Single-Instance High Availability (SIHA). For more information on Oracle Restart, refer to Oracle Database Administrator's Guide.

Applications use Oracle RAC features to maximize connection performance and availability and to mitigate down-time due to connection problems. Applications have different availability and performance requirements and should implement Oracle RAC features accordingly.

Generic High Availability and Performance Features

The UCP for JDBC APIs and connection pool properties include many high availability and performance features that do not require an Oracle RAC database. These features work well with both Oracle and non-Oracle connections and are discussed throughout this guide. For example: validating connections on borrow; setting timeout properties; setting maximum reuse properties; and connection pool manager operations are all used to ensure a high-level of connection availability and optimal performance.

Note:

Generic high availability and performance features work slightly better when using Oracle connections because UCP for JDBC leverages Oracle JDBC internal APIs.

Database Version Compatibility for Oracle RAC

Table 8-1 lists supported Database versions for various Oracle RAC features:

Table 8-1 Oracle RAC Version Compatibility

Feature Supported Database Version

Fast Connection Failover

10.1.x and later versions

Run-time Connection Load-Balancing

10.2.x and later versions

Web Session Affinity

11.1.x and later versions

Transaction-Based Affinity

10.2.x and later versions (11.1.x recommended)


Oracle JDBC Driver Version Compatibility for Oracle RAC

Oracle JDBC driver 10.1.x and later versions are supported with Oracle RAC features.

Using Fast Connection Failover

The Fast Connection Failover (FCF) feature is a Fast Application Notification (FAN) client implemented through the connection pool. The feature requires the use of an Oracle JDBC driver and an Oracle RAC database or an Oracle Restart on a single instance database. This section only describes the steps that an application must perform when using FCF with Oracle RAC. For more information on setting up an Oracle RAC database, see the Oracle Real Application Clusters Administration and Deployment Guide or consult an Oracle database administrator.

FCF manages pooled connections for high availability and provides the following features:

  • FCF supports unplanned outages. Dead connections are rapidly detected and then the connections are aborted and removed from the pool. Connection removal relies on abort to rapidly sever socket connections in order to prevent hangs. Borrowed and in-use connections are interrupted only for unplanned outages.

  • FCF supports planned outages. Borrowed or in-use connections are not interrupted and closed until work is done and control of the connection is returned to the pool.

  • FCF encapsulates fatal connection errors and exceptions into the isValid API for robust and efficient retries. See "Checking If a Connection Is Valid" for more information on using this API.

  • FCF recognizes new nodes that join an Oracle RAC cluster and places new connections on that node appropriately in order to deliver maximum quality of service to applications at run-time. This facilitates middle-tier integration of Oracle RAC node joins and work-request routing from the application tier.

  • FCF distributes runtime work requests to all active Oracle RAC instances.

Unplanned Shutdown Scenarios

FCF supports unplanned shutdown scenarios by detecting and removing stale connections to an Oracle RAC cluster. Stale connections include connections that do not have a service available on any instance in an Oracle RAC cluster due to service-down and node-down events. Borrowed connections and available connections that are stale are detected, and their network connection is severed before removing them from the pool. These removed connections are not replaced by the pool. Instead, the application must retry connections before performing any work with a connection.

Note:

Borrowed connections are immediately aborted and closed during unplanned shutdown scenarios. Any on-going transactions immediately receive an exception.

Planned Shutdown Scenarios

FCF supports planned shutdown scenarios where an Oracle RAC service can be gracefully shutdown. In such scenarios, stale borrowed connections are marked and will only be aborted and removed after they are returned to the pool. Any on-going transactions do not see any difference and proceed to complete.

The primary difference between unplanned and planned shutdown scenarios is how borrowed connections are handled. Stale connections that are idle in the pool (not borrowed) are removed in the same manner as the unplanned shutdown scenario.

Oracle RAC Instance Rejoin and New Instance Scenarios

FCF supports scenarios where an Oracle RAC cluster adds instances that provide a service of interest. The instance may be new to the cluster or may have been restarted after a down event. In both cases, UCP for JDBC recognizes the new instance and creates connections to the node as required.

Example Fast Connection Failover Configuration

The following example demonstrates a connection pool that uses the FCF feature. FCF is configured through a pool-enabled data source. The example includes enabling FCF, configuring the Oracle Notification Service (ONS) and configuring a connection URL. These topics are discussed after the example.

Example 8-1 Fast Connection Failover Configuration Example

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionPoolName("FCFSamplePool");
pds.setFastConnectionFailoverEnabled(true);
pds.setONSConfiguration("nodes=racnode1:4200,racnode2:4200\nwalletfile= /oracle11/onswalletfile");
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");pds.setURL("jdbc:oracle:thin@(DESCRIPTION= "+
   "(LOAD_BALANCE=on)"+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=racnode1) (PORT=1521))"+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=racnode2) (PORT=1521))"+
   "(CONNECT_DATA=(SERVICE_NAME=service_name)))");
...

The isValid method of the oracle.ucp.jdbc.ValidConnection interface is typically used in conjunction with the FCF feature and is used to check if a borrowed connection is still usable after an SQL exception has been thrown due to a Oracle RAC down event. For example:

try {  conn = pds.getConnection;  ...}catch (SQLException sqlexc)
{
   if (conn == null || !((ValidConnection) conn).isValid())
        
   // take the appropriate action
   
...
conn.close();
}

For more information on the ValidConnection interface, see "Checking If a Connection Is Valid".

Enabling Fast Connection Failover

The FCF pool property is used to enable and disable FCF. FCF is disabled by default. The following example demonstrates enabling FCF as shown in Example 8-1.

pds.setFastConnectionFailoverEnabled(true);

Note:

FCF must also be enabled to use run-time connection load balancing and connection affinity. These features are discussed later in this chapter.

Configuring ONS

FCF relies on the Oracle Notification Service (ONS) to propagate database events between the connection pool and the Oracle RAC database. At run-time, the connection pool must be able to setup an ONS environment. ONS (ons.jar) is included as part of the Oracle Client software. ONS can be configured using either remote configuration or client-side ONS daemon configuration. Remote configuration is the preferred configuration for standalone client applications.

Remote Configuration

UCP for JDBC supports remote configuration of ONS through the SetONSConfiguration pool property. The ONS property value is a string that closely resembles the content of an ons.config file. The string contains a list of name=value pairs separated by a new line character (\n). The name can be: nodes, walletfile, or walletpassword. The parameter string should at least specify the ONS configuration nodes attribute as a list of host:port pairs separated by a comma. SSL would be used when the walletfile attribute is specified as an Oracle wallet file.

The following example demonstrates an ONS configuration string as shown in Example 8-1:

...
pds.setONSConfiguration("nodes=racnode1:4200,racnode2:4200\nwalletfile=/oracle11/onswalletfile");
...

Applications that use remote configuration must set the oracle.ons.oraclehome system property to the location of ORACLE_HOME before starting the application. For example:

java -Doracle.ons.oraclehome=$ORACLE_HOME ...

Note:

The parameters in the configuration string must match those for the Oracle RAC database. In addition, the setONSConfiguration property is only used for standalone Java applications. When using Oracle Application Server, ONS should be configured using procedures that are applicable to the server.

Client-Side Daemon Configuration

Client-Side ONS daemon configuration is typical of applications that run on a middle tier server such as the Oracle Application Server. Clients in this scenario directly configure ONS by updating the ons.config file. The location of the file may be different depending on the platform. The following example demonstrates an ons.config file for Example 8-1:

localport=4100
remoteport=4200
nodes=racnode1:4200,racnode2:4200
walletfile=/oracle11/onswalletfile
  • localport: The port that ONS binds to on the localhost interface to talk to local clients.

  • remoteport: the port that ONS binds to on all interfaces for talking to other ONS daemons.

The ONS utility (onsctl) can be used to start, stop, ping, and refresh ONS and can also be used to debug ONS. ONS must be refreshed after updating the ons.config file.

For more information on setting up ONS, refer to the following links:

Configuring the Connection URL

A connection factory's connection URL must use the service name syntax when using FCF. The service name is used to map the connection pool to the service. In addition, the factory class must be an Oracle factory class. The following example demonstrates configuring the connection URL as shown in Example 8-1:

...
pds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
pds.setURL("jdbc:oracle:thin@//host:port/service_name");
...

Note:

An exception is thrown if a service identifier (SID) is specified for the connection URL when FCF is enabled.

The following examples demonstrate valid connection URL syntax when connecting to an Oracle RAC database. Examples for both the Oracle JDBC thin and Oracle OCI driver are included. Notice that the URL can be used to explicitly enable load balancing among Oracle RAC nodes:

Valid Connection URL Usage

pds.setURL("jdbc:oracle:thin@//host:port/service_name"); 

pds.setURL("jdbc:oracle:thin@//cluster-alias:port/service_name"); 

pds.setURL("jdbc:oracle:thin:@(DESCRIPTION= "+
   "(LOAD_BALANCE=on)"+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=host1)(PORT=1521))"+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))"+
   "(CONNECT_DATA=(SERVICE_NAME=service_name)))");

pds.setURL("jdbc:oracle:thin:@(DESCRIPTION= "+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias) (PORT=1521)) "+
   "(CONNECT_DATA=(SERVICE_NAME=service_name)))");

pds.setURL("jdbc:oracle:oci:@TNS_ALIAS");

pds.setURL("jdbc:oracle:oci:@(DESCRIPTION= "+
   "(LOAD_BALANCE=on) "+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521)) "+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521)) "+
   "(CONNECT_DATA=(SERVICE_NAME=service_name)))"); 

pds.setURL("jdbc:oracle:oci:@(DESCRIPTION= "+
   "(ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias) (PORT=1521)) "+
   "(CONNECT_DATA=(SERVICE_NAME=service_name)))");

Using Run-Time Connection Load Balancing

UCP JDBC connection pools leverage the load balancing functionality provided by an Oracle RAC database. Run-time connection load balancing requires the use of an Oracle JDBC driver and an Oracle RAC database. For more information on setting up an Oracle RAC database, see the Oracle Real Application Clusters Administration and Deployment Guide or consult an Oracle database administrator.

Run-time connection load balancing is useful when:

  • Traditional balancing of workload is not optimal

  • Requests must be routed to make optimal use of resources in a clustered database

  • Capacity within the cluster differs and is expected to change over time

  • The need to avoid sending work to slow, hung, and dead nodes is required

UCP for JDBC uses the Oracle RAC Load Balancing Advisory. The advisory is used to balance work across Oracle RAC instances and is used to determine which instances offer the best performance. Applications transparently receive connections from instances that offer the best performance. Connection requests are quickly diverted from instances that have slowed, are not responding, or that have failed.

Run-time connection load balancing provides the following benefits:

  • Manages pooled connections for high performance and scalability

  • Receives continuous recommendations on the percentage of work to route to database instances

  • Adjusts distribution of work based on different back-end node capacities such as CPU capacity or response time

  • Reacts quickly to changes in cluster reconfiguration, application workload, overworked nodes, or hangs

  • Receives metrics from the Oracle RAC Load Balance Advisory. Connections to well performing instances are used most often. New and unused connections to under-performing instances will gravitate away over time. When distribution metrics are not received, connection are selected using a random choice.

Setting Up Run-Time Connection Load Balancing

Run-time connection load balancing requires that FCF is enabled and configured properly. See "Using Fast Connection Failover" for detailed instructions on setting up FCF.

In addition, the Oracle RAC Load Balancing Advisory must be configured with service-level goals for each service for which load balancing is enabled. The Oracle RAC Load Balancing Advisory may be configured for SERVICE_TIME or THROUGHPUT. The connection load balancing goal should be set to SHORT. For example:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'sjob' -, goal => 
   DBMS_SERVICE.GOAL_THROUGHPUT -, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

Or

EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'sjob' -, goal => 
   DBMS_SERVICE.GOAL_SERVICE_TIME -, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

The Load Balancing Advisory goal can also be set by calling the DBMS_SERVICE.create_service. See the Introduction to Automatic Workload Management chapter in the Oracle Real Application Clusters Administration and Deployment Guide. In particular, refer to the "Load Balancing Advisory" section.

Using Connection Affinity

UCP JDBC connection pools leverage affinity functionality provided by an Oracle RAC database. Connection affinity requires the use of an Oracle JDBC driver and an Oracle RAC database version 11.1.0.6 or higher. For more information on setting up an Oracle RAC database, see the Oracle Real Application Clusters Administration and Deployment Guide or consult an Oracle database administrator.

Connection affinity is a performance feature that allows a connection pool to select connections that are directed at a specific Oracle RAC instance. The pool uses run-time connection load balancing (if configured) to select an Oracle RAC instance to create the first connection and then subsequent connections are created with an affinity to the same instance.

Note:

Affinity is only a hint. A connection pool will select a new Oracle RAC instance for connections if a desired instance is not found.

UCP JDBC connection pools support two types of connection affinity: transaction-based affinity and Web session affinity.

Transaction-Based Affinity

Transaction-based affinity is an affinity to an Oracle RAC instance that can be released by either the client application or a failure event. Applications typically use this type of affinity when long-lived affinity to an Oracle RAC instance is desired or when the cost (in terms of performance) of being redirected to a new Oracle RAC instance is high. Distributed transactions are a good example of transaction-based affinity. XA connections that are enlisted in a distributed transaction keep an affinity to the Oracle RAC instance for the duration of the transaction. In this case, an application would incur a significant performance cost if a connection is redirect to a different Oracle RAC instance during the distributed transaction.

Web Session Affinity

Web session affinity is an affinity to an Oracle RAC instance that can be released by either the instance, a client application, or a failure event. The Oracle RAC instance uses a hint to communicate to a connection pool whether affinity has been enabled or disabled on the instance. An Oracle RAC instance may disable affinity based on many factors, such as performance or load. If an Oracle RAC instance can no longer support affinity, the connections in the pool are refreshed to use a new instance and affinity is established once again.

Applications typically use this type of affinity when short-lived affinity to an Oracle RAC instance is expected or if the cost (in terms of performance) of being redirected to a new Oracle RAC instance is minimal. For example, a mail client session might use Web session affinity to an Oracle RAC instance to increase performance and is relatively unaffected if a connection is redirected to a different instance.

Setting Up Connection Affinity

Connection affinity is set up as follows:

Note:

Transaction-based affinity is strictly scoped between the application/middle-tier and UCP for JDBC; therefore, transaction-based affinity only requires that the setFastConnectionFailoverEnabled property be set to true and does not require complete FCF configuration.

In addition, transaction-based affinity does not technically require run-time connection load balancing. However, it can help with performance and is usually enabled regardless. If run-time connection load balancing is not enabled, the connection pool randomly picks connections.

Creating a Connection Affinity Callback

Connection affinity requires the use of a callback. The callback is an implementation of the ConnectionAffinityCallback interface which is located in the oracle.ucp package. The callback is used by the connection pool to establish and retrieve a connection affinity context and is also used to set the affinity policy type (transaction-based or Web session).

The following example demonstrates setting an affinity policy in a callback implementation. The example also demonstrates manually setting an affinity context. typically, the connection pool sets the affinity context inside an application. However, the ability to manually set an affinity context is provided for applications that want to customize affinity behavior and control the affinity context directly.

public class AffinityCallbackSample
   implements ConnectionAffinityCallback {
   
   Object appAffinityContext = null;
   ConnectionAffinityCallback.AffinityPolicy affinityPolicy =
   ConnectionAffinityCallback.AffinityPolicy.TRANSACTION_BASED_AFFINITY;
   
   //For Web session affinity, use WEBSESSION_BASED_AFFINITY;
   
   public void setAffinityPolicy(AffinityPolicy policy)
   {
      affinityPolicy = policy;
   }
   
   public AffinityPolicy getAffinityPolicy()
   {
      return affinityPolicy;
   }
   
   public boolean setConnectionAffinityContext(Object affCxt)
   {
      synchronized (lockObj)
      {
         appAffinityContext = affCxt;
      }
      return true;
   }
   
   public Object getConnectionAffinityContext()
   {
      synchronized (lockObj)
      {
         return appAffinityContext;
      }
   }
}

Registering a Connection Affinity Callback

A connection affinity callback is registered on a connection pool using the registerConnectionAffinityCallback method. The callback is registered when creating the connection pool. Only one callback can be registered per connection pool.

The following example demonstrates registering a connection affinity callback implementation:

ConnectionAffinityCallback callback = new MyCallback();

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionPoolName("AffinitySamplePool");
pds.registerConnectionAffinityCallback(callback);
...

Removing a Connection Affinity Callback

A connection affinity callback is removed from a connection pool using the removeConnectionAffinityCallback method. For example:

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

pds.setConnectionPoolName("AffinitySamplePool");
pds.removeConnectionAffinityCallback();
...