Intermittent Oracle JDBC Connection Failures to RAC

More often then not I get the following come my way regarding Oracle JDBC connections to a RAC Cluster.

"My JDBC connections to a RAC cluster intermittently fail every now and then, but SQL*Plus works fine?"

Here is a little demo that will enable you to continually get JDBC connections to the RAC cluster , waiting for a connection failure. Nice way to verify if indeed there are intermittent problems from ORACLE JDBC when connecting to the cluster. We use the ScheduledExecutorService to get connections periodically and never terminate until we get a connection request failure. This demo is using an 11g R2 cluster with SCAN.

PeriodicJDBCRacTester.java (Main class to run the test)

package pas.au.jdbc.rac;

import java.io.FileInputStream;

import java.text.DateFormat;

import java.util.Date;
import java.util.Properties;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ScheduledFuture;
import java.util.concurrent.TimeUnit;

public class PeriodicJDBCRacTester
{
  private static final DateFormat DATEFORMAT = DateFormat.getInstance();
  private String periodString = "30";
  private String url = null;
  private String user = null;
  private String passwd = null;
  private static Properties myProperties = new Properties();
  
  public PeriodicJDBCRacTester()
  {
    try
    {
      myProperties.load(new FileInputStream("jdbcractest.properties"));
      processProps(myProperties);
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
  
  public void run()
  {
    ScheduledExecutorService scheduler = Executors.newScheduledThreadPool(1);
    try 
    {

      LoadBalanceTest loadBalanceTest = 
         new LoadBalanceTest(url, user, passwd);

      String periodString = "30";
      int prd = Integer.parseInt(periodString);
      long period = (long) prd;
      
      System.out.println("\nPeriodic JDBC Load Balance Test ");
      System.out.println("Period to report results in seconds is : " + period + "\n");
     
      System.out.printf("** Started [%s] with %s(sec) interval between runs **\n", 
                        DATEFORMAT.format(new Date()),
                        periodString);
      
      final ScheduledFuture jdbcRacMonitor = 
          scheduler.scheduleAtFixedRate(loadBalanceTest, 0, 30, 
                                        TimeUnit.SECONDS);
                                       
              
    } 
    catch (Exception ex) 
    {
      ex.printStackTrace();
    } 
  }

  private void processProps(Properties inProps)
  {
    periodString = inProps.getProperty("period");
    url = inProps.getProperty("url");
    user = inProps.getProperty("user");
    passwd = inProps.getProperty("passwd");
  }
  
  public static void main(String[] args)
  {
    PeriodicJDBCRacTester test = new PeriodicJDBCRacTester();
    test.run();
  }
}  

LoadBalanceTest.java (Test class which is called periodically which obtains connections)

package pas.au.jdbc.rac;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.text.DateFormat;

import java.util.Date;

import oracle.jdbc.pool.OracleDataSource;

public class LoadBalanceTest implements Runnable
{
  private static final DateFormat DATEFORMAT = DateFormat.getInstance();
  
  private OracleDataSource ods = null;
  public final String userId = "scott";
  public final String password = "tiger";

  private String url = null;
  
  public LoadBalanceTest(String url, String user, String password) throws SQLException
  {
    ods = new OracleDataSource();
    ods.setUser(user);
    ods.setPassword(password);
    ods.setURL(url);  
    
    this.url = url;
    
  }

  public Connection getConnection() throws SQLException
  {
    return ods.getConnection();
  }
  
  public void run ()
  {
    Connection[] connArray = new Connection[5];
    System.out.println("\nCurrent run started at " + new Date());
    System.out.println("Obtaining 5 connections");
    try
    {
      for (int i = 0; i < connArray.length; i++)
      {
        connArray[i] = getConnection();
      }

      for (int j = 0; j < connArray.length; j++)
      {
        if (j == 0)
        {
          DatabaseMetaData meta = connArray[j].getMetaData();
          System.out.println("using URL : " + url + "\n");
          // gets driver info:

          System.out.println("=============\nDatabase Product Name is ... " +
                             meta.getDatabaseProductName());
          System.out.println("Database Product Version is  " +
                             meta.getDatabaseProductVersion());
          System.out.println("=============\nJDBC Driver Name is ........ " +
                             meta.getDriverName());
          System.out.println("JDBC Driver Version is ..... " +
                             meta.getDriverVersion());
          System.out.println("JDBC Driver Major Version is " +
                             meta.getDriverMajorVersion());
          System.out.println("JDBC Driver Minor Version is " +
                             meta.getDriverMinorVersion());
          System.out.println("=============");
        }

        getInstanceDetails(connArray[j], j);
      }

      System.out.println("Closing Connections\n");
      for (int y = 0; y < connArray.length; y++)
      {
        connArray[y].close();
      }
    }
    catch (SQLException sqle)
    {
      // TODO: Add catch code
      sqle.printStackTrace();
    }
  }

  public void getInstanceDetails (Connection conn, int i) throws SQLException
  {
    String sql = 
      "select sys_context('userenv', 'instance_name'), " + 
      "sys_context('userenv', 'server_host'), " + 
      "sys_context('userenv', 'service_name') " + 
      "from dual";
    
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery(sql);
    while (rset.next())
    {
      System.out.println
        ("Connection #" + i + " : instance[" + rset.getString(1) + "], host[" + 
         rset.getString(2) + "], service[" + rset.getString(3) + "]");
    }
   
    stmt.close();
    rset.close();
  }
}

jdbcractest.properties (Properties file used to connect to the cluster and define the period in seconds for when the test will run)

period=30

url=jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv

user=scott

passwd=tiger

If you want to run this with ANT you can use a build.xml as follows.

<?xml version="1.0" encoding="windows-1252" ?>

<project default="run" name="JDBCRACTester" basedir=".">

  <property file="build.properties"/>
   
  <path id="j2ee.classpath">
    <pathelement path="./lib/ojdbc6.jar"/>
    <pathelement path="./lib/JDBCRACTester.jar"/>
  </path>

  <property name="src.dir" value="src"/>
  <property name="classes.dir" value="classes"/>
  <property name="lib.dir" value="lib"/>
  <property name="class.name" value="pas.au.jdbc.rac.PeriodicJDBCRacTester"/>
  
  <target name="init">
    <tstamp/>
    <mkdir dir="${lib.dir}"/>
    <mkdir dir="${classes.dir}"/>
  </target>
  
  <target name="clean" description="Clean lib, classes directories">
    <delete dir="${classes.dir}"/>
  </target>
  
  <target name="compile" description="Compiles classes into ${classes.dir}" depends="init">
    <javac includeantruntime="false" srcdir="${src.dir}" debug="true" destdir="${classes.dir}" 
           includes="**/*.java" >
      <classpath refid="j2ee.classpath"/>
    </javac>
  </target>

  <target name="package" depends="compile" description="Package application into ${ant.project.name}.jar">
    <jar basedir="${classes.dir}" destfile="${lib.dir}/${ant.project.name}.jar">
      <include name="**/*.class"/>
    </jar>
  </target>
  
  <target name="run" depends="package" description="Run the JDBC RAC Tester application">
    <echo message="Running the JDBC RAC Tester application"/>
    <java classname="${class.name}" fork="true">
      <classpath>
        <path refid="j2ee.classpath"/>
        <path path="${lib.dir}/${ant.project.name}.jar"/>
      </classpath>
    </java>
  </target>

</project>

Output (Run using ANT) 

  run:
     [echo] Running the JDBC RAC Tester application
     [java]
     [java] Periodic JDBC Load Balance Test
     [java] Period to report results in seconds is : 30
     [java]
     [java] ** Started [5/25/11 9:38 AM] with 30(sec) interval between runs **
     [java]
     [java] Current run started at Wed May 25 09:38:59 EST 2011
     [java] Obtaining 5 connections
     [java] using URL : jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv
     [java]
     [java] =============
     [java] Database Product Name is ... Oracle
     [java] Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
     [java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
     [java] Data Mining and Real Application Testing options
     [java] =============
     [java] JDBC Driver Name is ........ Oracle JDBC driver
     [java] JDBC Driver Version is ..... 11.2.0.2.0
     [java] JDBC Driver Major Version is 11
     [java] JDBC Driver Minor Version is 2
     [java] =============
     [java] Connection #0 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #1 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #2 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #3 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #4 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Closing Connections
     [java]
     [java]
     [java] Current run started at Wed May 25 09:39:29 EST 2011
     [java] Obtaining 5 connections
     [java] using URL : jdbc:oracle:thin:@apctcsol1.au.oracle.com:1521/pas_srv
     [java]
     [java] =============
     [java] Database Product Name is ... Oracle
     [java] Database Product Version is  Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
     [java] With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
     [java] Data Mining and Real Application Testing options
     [java] =============
     [java] JDBC Driver Name is ........ Oracle JDBC driver
     [java] JDBC Driver Version is ..... 11.2.0.2.0
     [java] JDBC Driver Major Version is 11
     [java] JDBC Driver Minor Version is 2
     [java] =============
     [java] Connection #0 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #1 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #2 : instance[A12], host[auw2k4], service[pas_srv]
     [java] Connection #3 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Connection #4 : instance[A11], host[auw2k3], service[pas_srv]
     [java] Closing Connections
     [java]