Is there any way to have the JBoss connection pool reconnect to Oracle when connections go bad?

Question

We have our JBoss and Oracle on separate servers. The connections seem to be dropped and is causing issues with JBoss. How can I have the JBoss reconnect to Oracle if the connection is bad while we figure out why the connections are being dropped in the first place?

Answer 1

Whilst you can use the old "select 1 from dual" trick, the downside with this is that it issues an extra query each and every time you borrow a connection from the pool. For high volumes, this is wasteful.

JBoss provides a special connection validator which should be used for Oracle:

<valid-connection-checker-class-name>
    org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
</valid-connection-checker-class-name>

This makes use of the proprietary ping() method on the Oracle JDBC Connection class, and uses the driver's underlying networking code to determine if the connection is still alive.

However, it's still wasteful to run this each and every time a connection is borrowed, so you may want to use the facility where a background thread checks the connections in the pool, and silently discards the dead ones. This is much more efficient, but means that if the connections do go dead, any attempt to use them before the background thread runs its check will fail.

See the wiki docs for how to configure the background checking (look for background-validation-millis).

Answer 2

There is usually a configuration option on the pool to enable a validation query to be executed on borrow. If the validation query executes successfully, the pool will return that connection. If the query does not execute successfully, the pool will create a new connection.

The JBoss Wiki documents the various attributes of the pool.

<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>

Seems like it should do the trick.

Answer 3

Not enough rep for a comment, so it's in a form of an answer. The 'Select 1 from dual' and skaffman's org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker method are equivalent , although the connection check does provide a level of abstraction. We had to decompile the oracle jdbc drivers for a troubleshooting exercise and Oracle's internal implementation of the ping is to perform a 'Select 'x' from dual'. Natch.

Answer 4

JBoss provides 2 ways to Validate connection: - Ping based AND - Query based

You can use as per requirement. This is scheduled by separate thread as per duration defined in datasource configuration file.

<background-validation>true</background-validation> <background-validation-minutes>1</background-validation-minutes>

Some time if you are not having right oracle driver at Jboss, you may get classcast or related error and for that connection may start dropout from connection pool. You can try creating your own ConnectionValidator class by implementing org.jboss.resource.adapter.jdbc.ValidConnectionChecker interface. This interface provides only single method 'isValidConnection()' and expecting 'NULL' in return for valid connection.

Ex:

public class OracleValidConnectionChecker implements ValidConnectionChecker, Serializable {

   private Method ping;

   // The timeout (apparently the timeout is ignored?)
   private static Object[] params = new Object[] { new Integer(5000) };

   public SQLException isValidConnection(Connection c) {

       try {
           Integer status = (Integer) ping.invoke(c, params);

           if (status.intValue() < 0) {
               return new SQLException("pingDatabase failed status=" + status);
           }

       }
       catch (Exception e) {
           log.warn("Unexpected error in pingDatabase", e);
       }

       // OK
       return null;
   }
}

Answer 5

A little update to @skaffman's answer. In JBoss 7 you have to use "class-name" attribute when setting valid connection checker and also package is different:

<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker" />

Answer 6

We've recently had some floating request handling failures caused by orphaned oracle DBMS_LOCK session locks that retained indefinitely in client-side connection pool.

So here is a solution that forces session expiry in 30 minutes but doesn't affect application's operation:

<check-valid-connection-sql>select case when 30/60/24 > sysdate-LOGON_TIME then 1 else 1/0 end 
from V$SESSION where AUDSID = userenv('SESSIONID')</check-valid-connection-sql>

This may involve some slow down in process of obtaining connections from pool. Make sure to test this under load.