Channels ▼
RSS

JVM Languages

JDBC Fast Connection Failover with Oracle RAC


JDBC Client Environment

To begin the client side testing and configuration you need to gather some configuration information and required libraries. From the previous Oracle environment setup, you should already know:

  • The hosts and ports for the RAC node listeners
  • The service name(s) of the target database
  • The hosts and ports for the ONS servers

o use the ONS service on the client side, Oracle's ONS library (ons.jar) must be obtained from Oracle; available directly from the Oracle JDBC Driver website (see "Resources" at the end of the article) or it can be copied from a thick client or server installation. The library implements all the client-server communications to subscribe to the ONS servers and receive FAN events.

Most Tomcat- or Spring-based applications currently use the Apache Database Connection Pooling (DBCP) library to create and manage a connection pool. While robust and well supported, the DBCP library is a generic connection pool that is not aware of vendor specific extensions such as FCF or ONS. Oracle therefore provides the Universal Connection Pool (UCP) which serves as a generic connection pool which implements the standard javax.sql.DataSource interface while supporting the Oracle specific features for HA. Prior to Oracle 11g, Oracle recommended using Implicit Connection Caching (ICC) in the oracle.jdbc.pool.OracleDataSource; however according to the Oracle 11g JDBC documentation:

In this release the Oracle Implicit Connection Cache feature is deprecated. Users are strongly encouraged to use the new Universal Connection Pool instead. The UCP has all of the features of the ICC, plus much more. The UCP is available in a separate jar file, ucp.jar.

Therefore any new application should use UCP rather than starting with ICC. The 11g JDBC drivers are backward compatible with 10g databases so even in instances where the database is only 10g, the client should consider using UCP with an 11g driver. The UCP library (ucp.jar) and the 11g JDBC driver (ojdbc6.jar) can be downloaded directly from Oracle's website (see "Resources").

The final component for client side setup is the JDBC URL used to connect to the server. When working with a single database instance, the standard thin JDBC driver URL took the form jdbc:oracle:thin:@<host>:<port>:<SID> which specifies the single host, port, and SID of the instance to which to connect. To support HA, the client needs to be able to talk to multiple RAC nodes in the cluster in the event that any node goes down. To support this, the thin JDBC driver allows for the use of a connect descriptor similar to what is found in the tnsnames.ora configuration file on the Oracle Server. Your DBA may be able to provide you with a connect descriptor, you can use the tnsping command on the server, or you can write one from scratch such as:


jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=sales1node1.foo.com)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=sales1node2.foo.com) (PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=sales.foo.com)))

The key is to include multiple instance host names in descriptor so the connection pool can contact both instances at runtime.

Load Balancing In the Connect Descriptor

Although the connect descriptor presented above contains the options LOAD_BALANCE=ON, the UCP, FCF, and ONS components must be configured correctly to use the runtime load-balance advisory. The LOAD_BALANCE option simply tells the client to randomly choose an available listener from the address list when making new connections. However the runtime load-balance advisory will take precedence over this simple client side load balancing approach if advisory events are received.

Testing the Setup

Testing the setup with a simple test client can verify that all the configuration pieces are in place for both the Oracle server environment and the JDBC client environment before trying to configure a complete application or container. Direct access to all of the hosts and service ports from the client machine is required because the listener may redirect the client to another host based on load and availability. Access can be checked using the telnet command on the client machine and verifying that a server answers for each request:

telnet <ons host 1><port>
telnet <ons host 2><port>
telnet <listener host 1><port>
telnet <listener host 2> <port>

With the ports accessible, you can run a simple test client to verify the configuration information and to get a better understanding of how ONS, FCF, and UCP are working together. Listing 1 contains a JDBC client that performs some basic JDBC interactions and prints the UCP log information.


import static java.lang.String.*;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import java.util.logging.*;

import oracle.jdbc.pool.OracleDataSource;
import oracle.ucp.admin.UniversalConnectionPoolManager;
import oracle.ucp.admin.UniversalConnectionPoolManagerImpl;
import oracle.ucp.jdbc.*;

/**
 * <p>
 * A test class that provides some simple JDBC functionality to test the Oracle
 * Universal Connection Pool, Fast Connection Failover, and the Oracle
 * Notification Service. You may need to modify the DB_* constants to configure
 * the test driver for your DB.
 * </p>
 * <p>
 * To run the test, execute:<br/>
 * java -cp ons.jar:ucp.jar:ojdbc6.jar:. UcpTest
 * </p>
 * <p>
 * The commands include:
 * <ul>
 * <li>exit: close all connections and exit</li>
 * <li>get: get a connection from the pool</li>
 * <li>print: print current connections and connection distribution</li>
 * <li>stats: print some basic connection pool stats</li>
 * <li>commit: commit the current transaction on a connection</li>
 * <li>close: close (release) a connection and return it to the pool</li>
 * </ul>
 * </p>
 * 
 * @author mpilone
 */
public class UcpTest
{
  /**
   * The user to connect as. This user must have select access to v$instance.
   */
  private static final String DB_USER = "bsmith";

  /**
   * The password for the user.
   */
  private static final String DB_PASSWORD = "secret";

  /**
   * The user to connect as for gather statistics information such as connection
   * distribution. This user must have select access to gv$session. It is
   * recommended that you don't use the same user as DB_USER so that the
   * connection distribution count is accurate. It is safe to use
   * "sys as sysdba" for this user if desired.
   */
  private static final String DB_STATS_USER = "sys as sysdba";

  /**
   * The password for the stats user.
   */
  private static final String DB_STATS_PASSWORD = "dba_secret";
  /**
   * The URL to the DB using a connection descriptor.
   */
  private static final String DB_URL =
      "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)"
          + "(ADDRESS=(PROTOCOL=TCP)"
          + "(HOST=sales1node1.foo.com)(PORT=1521))"
          + "(ADDRESS=(PROTOCOL=TCP)"
          + "(HOST=sales1node2.foo.com)(PORT=1521)))"
          + "(CONNECT_DATA=(SERVICE_NAME=sales.foo.com)))";

  /**
   * The ONS node configuration which uses the remoteport of the ONS remote
   * nodes.
   */
  private static final String ONS_CONFIG =
      "nodes=sales1node1.foo.com:6151,sales1node2.foo.com:6151";

  /**
   * Main entry point to the application. No arguments are supported.
   * 
   * @param args
   *          the command line arguments
   */
  public static void main(String[] args) throws Exception
  {
    new UcpTest().go();
  }
  /**
   * Starts the test client and prompts for input.
   * 
   * @throws Exception
   */
  private void go() throws Exception
  {
    // The list of active connections
    List<Connection> conns = new ArrayList<Connection>();

    // Create the universal connection pool
    PoolDataSource ds = initConnectionPool();

    // Setup logging so we can see the FAN events coming from ONS
    Handler fh = new FileHandler("ucptest.log");
    fh.setLevel(Level.FINE);
    fh.setFormatter(new SimpleFormatter());

    UniversalConnectionPoolManager mgr =
        UniversalConnectionPoolManagerImpl.getUniversalConnectionPoolManager();
    mgr.setLogLevel(Level.FINE);
    Logger log = Logger.getLogger("oracle.ucp");
    log.setLevel(Level.FINE);
    log.addHandler(fh);
    log.setUseParentHandlers(false);

    BufferedReader sysin = new BufferedReader(new InputStreamReader(System.in));

    while (true)
    {
      System.out
          .println("\nWaiting for command [exit, get, print, stats, commit, close]:");
      String command = sysin.readLine();
      System.out.println();

      // Handle the specific command
      if (command.equalsIgnoreCase("exit"))
      {
        for (Connection conn : conns)
        {
          conn.close();
        }
        System.exit(0);
      }
      else if (command.equalsIgnoreCase("get"))
      {
        Connection conn = ds.getConnection();
        conn.setAutoCommit(false);
        conns.add(conn);
        printConnections(conns);
      }
      else if (command.equalsIgnoreCase("print"))
      {
        printConnections(conns);
      }
      else if (command.equalsIgnoreCase("stats"))
      {
        JDBCConnectionPoolStatistics stats = ds.getStatistics();

        if (stats != null)
        {
          printStat("AbandonedConnectionsCount", stats);
          printStat("AvailableConnectionsCount", stats);
          printStat("AverageBorrowedConnectionsCount", stats);
          printStat("AverageConnectionWaitTime", stats);
          printStat("BorrowedConnectionsCount", stats);
          printStat("ConnectionsClosedCount", stats);
          printStat("ConnectionsCreatedCount", stats);
          printStat("RemainingPoolCapacityCount", stats);
          printStat("TotalConnectionsCount", stats);
        }
        printConnectionDistribution();
      }
      else if (command.equalsIgnoreCase("commit"))
      {
        int index = readInt("Connection number: ", sysin);
        if (index >= 0 && index < conns.size())
        {
          Connection conn = conns.get(index);
          conn.commit();
        }
      }
      else if (command.equalsIgnoreCase("close"))
      {
        int index = readInt("Connection number: ", sysin);
        if (index >= 0 && index < conns.size())
        {
          Connection conn = conns.remove(index);
          conn.close();
        }
        printConnections(conns);
      }
    }
  }

  /**
   * Creates the connection to the DB as the statistics user.
   * 
   * @return a new connection to the database
   */
  private Connection initStatsConnection() throws SQLException
  {
    OracleDataSource ds = new OracleDataSource();
    ds.setUser(DB_STATS_USER);
    ds.setPassword(DB_STATS_PASSWORD);
    ds.setURL(DB_URL);

    return ds.getConnection();
  }

  /**
   * Creates the UCP with FCF enabled.
   * 
   * @return the UCP
   * @throws SQLException
   */
  private PoolDataSource initConnectionPool() throws SQLException
  {
    PoolDataSource ds = PoolDataSourceFactory.getPoolDataSource();
    ds.setConnectionFactoryClassName("oracle.jdbc.pool.OracleDataSource");
    ds.setUser(DB_USER);
    ds.setPassword(DB_PASSWORD);
    ds.setURL(DB_URL);
    ds.setMinPoolSize(5);
    ds.setMaxPoolSize(25);
    ds.setInactiveConnectionTimeout(20);
    ds.setConnectionWaitTimeout(20);
    ds.setPropertyCycle(60);
    ds.setFastConnectionFailoverEnabled(true);
    ds.setONSConfiguration(ONS_CONFIG);

    return ds;
  }

  /**
   * Prints a statistic given the name of the statistic. This method uses
   * reflection for simplicity.
   * 
   * @param statName
   *          the name of the statistic to print
   * @param stats
   *          the statistics from the connection pool
   * @throws NoSuchMethodException
   * @throws SecurityException
   * @throws InvocationTargetException
   * @throws IllegalAccessException
   * @throws IllegalArgumentException
   */
  private void printStat(String statName, JDBCConnectionPoolStatistics stats)
      throws Exception
  {
    Method m = stats.getClass().getMethod("get" + statName);
    Object value = m.invoke(stats);

    System.out.println(format("%-35s%s", statName, value));
  }

  /**
   * Reads an integer from standard input.
   * 
   * @param sysin
   *          standard input
   * @return the integer read
   * @throws IOException
   *           if there is an error reading
   */
  private int readInt(String prompt, BufferedReader sysin) throws IOException
  {
    System.out.print(prompt);
    int index = Integer.valueOf(sysin.readLine());
    return index;
  }

  /**
   * Prints the list of connections and connection distribution.
   * 
   * @param conns
   *          the list of active connections
   */
  private void printConnections(List<Connection> conns)
  {
    // Print the total number of connections actively held.
    System.out.println("Total held connections: " + conns.size());

    // Iterate over the connections and execute a query to determine
    // which instance the connection is talking to.
    int index = 0;
    for (Iterator<Connection> iterator = conns.iterator(); iterator.hasNext();)
    {
      Connection conn = iterator.next();
      try
      {
        Statement stmt = conn.createStatement();

        // Get the instance name that is serving this connection.
        ResultSet rs =
            stmt.executeQuery("select instance_name from v$instance");
        if (rs.next())
        {
          System.out.println(format("[%s] Instance name [%s]", index++, rs
              .getString(1)));
        }

        rs.close();
        stmt.close();
      }
      catch (SQLException ex)
      {
        System.out.println(format("Failed to query connection. "
            + "It will be removed from the list. Error [%s]", ex.getMessage()));

        iterator.remove();
      }
    }
  }

  /**
   * Prints the distribution of connections across the RAC nodes.
   * 
   * @throws SQLException
   */
  private void printConnectionDistribution() throws SQLException
  {
    Connection statsConn = initStatsConnection();

    // Get the distribution of connections across the nodes, including
    // connections just sitting in the cache.
    System.out.println();
    System.out.println("Connection distribution");
    System.out.println("Instance ID    Connection Count");

    Statement stmt = statsConn.createStatement();
    ResultSet rs =
        stmt
            .executeQuery(format("select inst_id, count(1) from gv$session "
                + "where type='USER' and username = '%s' group by inst_id",
                DB_USER));

    while (rs.next())
    {
      System.out.println(format("%6s%18s", rs.getString(1), rs.getString(2)));
    }

    rs.close();
    stmt.close();
    statsConn.close();
  }
}

Listing 1

With the client, you can watch the ONS events received, obtain connections from the RAC nodes, print information about the active connections, and selectively close connections. The client is launched from the console with the command: java –cp ons.jar:ucp.jar:ojdbc6.jar:. UcpTest

Setting Up the Test Privileges

There are a couple of DBA support items needed before you can run these FCF tests. The JDBC client application requires access to the v$instance and the gv$session tables which may require special privileges depending on the database user the client is configured to use. Your DBA should be able to setup access to these data dictionaries for your user rather than allowing you to use a more privileged user such as SYS.

With the client running, you should see events that indicate load balancing information from the RAC server. This information is published as part of the Runtime Load Balance Advisory that you configured earlier. The detailed information in the events, which won't be visible in the logs, contains a percentage of the load that each instance should be given. This information is used by the connection pool to allocate work and to rebalance idle connections. Figure 2 shows a conceptual view of the load-balance advisory information.

Figure 2: A single Oracle instance can host one or more services. Clients can use any instance that offers the service needed.

The advisory events in the log will look like:


<DATE> oracle.ucp.jdbc.oracle.ONSRuntimeLBEventHandlerThread run
FINE: Runtime Load Balancing event triggered

The first scenario to test is a planned outage where the Oracle service is shutdown gracefully allowing clients to complete their current transactions, close connections, and migrate to another instance. The UCP will handle all of this assuming it gets the DOWN FAN event from the server. The test case steps are:

  1. Get connections until the client has a connection from both RAC nodes
  2. Ask the DBA to stop the service using the srvctl command on one of the RAC nodes
  3. Verify that a DOWN FAN event was received in the logs such as: <DATE> oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread run FINE: event triggered: Service name: search.echo.nasa.gov, Instance name: rac1db2, Unique name: rac1db, Host name: rac1node2, Status: down, Cardinality: 0, Reason: user, Event type: database/event/service
  4. Print the connections to verify that they are all still valid
  5. Close the connections (release to the pool) to the node with the stopped service and watch as the UCP immediately closes the connections to the downed instance
  6. Get a couple new connections and verify that they all go to the only up instance
  7. Ask the DBA to start the downed service using the srvctl command
  8. Verify that an UP FAN event was received in the logs such as: oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread run FINE: event triggered: Service name: search.echo.nasa.gov, Instance name: rac1db2, Unique name: rac1db, Host name: rac1node2, Status: up, Cardinality: 2, Reason: user, Event type: database/event/service
  9. Close a few connections and verify that the UCP rebalances the pooled connections across the nodes based on the load-balance advisory.

In an operational environment, once the service is shutdown the DBA can monitor the active sessions manually or execute the SQLPlus command shutdown NORMAL to shutdown the instance once all of the connections have been released by the UCP.

The second scenario to test is an unplanned outage where the Oracle service is shutdown abnormally such as a machine crash or network failure. The UCP will immediately mark the active connections as no longer valid (preventing a client lag or hang) and will open new connections only to the remaining instance. In this scenario, it is up to the client to handle a SQLException raised by the connection. The test case steps are:

  1. Get connections until the client has a connection from both RAC nodes
  2. Ask the DBA to stop an instance using the SQLPlus command shutdown IMMEDIATE
  3. Verify that a FAILURE FAN event was received in the logs such as: <DATE> oracle.ucp.jdbc.oracle.ONSDatabaseEventHandlerThread run FINE: event triggered: Service name: search.echo.nasa.gov, Instance name: rac1db2, Unique name: rac1db, Host name: rac1node2, Status: down, Cardinality: 0, Reason: failure, Event type: database/event/service
  4. Print the connections to verify that connections to the failed instance are terminated
  5. Get a couple new connections and verify that they all go to the only up instance
  6. Ask the DBA to start the downed instance and service
  7. Verify that an UP FAN event was received in the logs
  8. Close a few connections and verify that the UCP rebalances the pooled connections across the nodes based on the load-balance advisory

Obviously unplanned outages are not preferred, but UCP does attempt to quickly close connections to downed services and redirect new connections to the remaining instances.

At this point you should be able to verify the configuration and functionality of your RAC setup and see the FAN events in the log of the test client. If the FCF functionality is not working, contact your DBA to verify the configuration and test again before continuing. It is easier to debug configuration problems in this simple scenario than in a larger application or container.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video