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

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 2

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:

org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker

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.