Yet another reason to use Oracle’s JDBC connection manager

Over the weekend we had an odd issue.

We have four nodes in a cluster, with two core services. SERVICE_A runs on servers 1 and 2, while SERVICE_B runs on server 4. Server 3 is effectively idle most of the time unless we need to allow for additional throughput. Server 3 died on Friday afternoon, and after it came back up, the other three instances would not register anything about the services they were well, servicing, with the listener on server 3. As a result, we began to see an increase in the number of TNS-12514 in the listener log on server 3. While this is a problem in and of itself, the bigger issue that came to light was when developers began calling us and asking why they were getting TNS-12514. My immediate question was, how do you know that?

The reason I asked is because Oracle’s connection time load balancing is based on each instance in the cluster telling the other listeners where they are. So if I connect to the listener on server 3, it should tell me, “I can’t help you, but here is where that service is located on server 1”. If the listener can’t provide that information (as it couldn’t in our case), it will simply throw a TNS-12514 back to the user. However, if you use the Oracle JDBC connection manager, you will find it is smart enough to handle these exceptions and just get a connection from one of the other servers in the URL you supply. Incidentally, this is why you want all servers in your URL.

This is also why you should never see a TNS-12514 in your application if a case similar to what I described earlier occurs.

We can prove this with the code below…

import java.sql.*;
import java.io.*;
import java.util.*;

class testConn {

  public static void main(String args[]) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
      Properties p = new Properties();
      p.load(new FileInputStream(System.getProperty("propfile")));


      //we first build a connection manager, and load each physical connection into an array

      oracleConnMgr cm = new oracleConnMgr();
      ArrayList al = new ArrayList();
      for (int i = 0; i < 100; i++) {
        al.add(cm.getConnection());
      }


      //...and we then check the host to which we are connected.  problem instances where the listener does not
      //  know where the service is actually running should be ignored and result in no exceptions.
      //  we will still see tns-12514 errors in the listener log file on the offending node, but our application
      //  will continue to be unaware of any issue.

      for (int j = 0; j<100; j++) {
        Connection conn = (Connection)al.get(j);
        ResultSet rst = conn.createStatement().executeQuery("select sys_context('userenv','instance') from dual");
        while (rst.next()) {
          System.out.println(rst.getString(1));
        }
      }
    }
    catch(Exception e) {
      e.printStackTrace();
    }
  }
}

I should note that the properties file and oracleConnMgr class noted above simply contain the URL and number of connections to use (we had 100 in the file), as well as the code to fire up an OracleDataSource object, respectively.

Don't roll your own connection manager. Life is too short.