8 Using Oracle RAC Features

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.

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.

Database Version Compatibility for Oracle RAC

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

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.

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.

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)))");
...

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

pds.setFastConnectionFailoverEnabled(true);

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

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

localport=4100
remoteport=4200
nodes=racnode1:4200,racnode2:4200
walletfile=/oracle11/onswalletfile

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

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.

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

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

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.

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.

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;
      }
   }
}

ConnectionAffinityCallback callback = new MyCallback();

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

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

PoolDataSource  pds = PoolDataSourceFactory.getPoolDataSource();

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