How to auto reconnect Oracle connection pool after connection interruption?

12
votes

I'm running across a problem with Oracle connection pooling via OracleConnectionCacheImpl. When I connect to a connection pool on my database server (Oracle 10g) via the thin driver, everything works fine until after an unspecified time, the db connection gets dropped (possibly due to idle connections?) and I get an error message:

Closed Connection.

Refreshing the page and reattempting the connection reconnects the database, so it does not seem to be a problem with the network or the database. Is a way for the Oracle connection pool to validate its connection and reconnect if the connection in the pool is dead for any reason?

I am using Apache Tomcat 6.0.18, Java EE6 update 11.

(There seems to be a ping function in the Oracle thin driver. Does that help and where can I find it?)

3
jdbcoracle10gdatabase-connectionconnection-pooling
1) Can you add the whole stack trace for Closed Connection 2) Did you set InactivityTimeout, TimeToLiveTimeout or AbandonedConnectionTimeout to any non-default values? 3) Do you use ValidateConnection? – Andrew not the Saint
4) Are physical connections between the app server and the DB server getting cut off on the network level, e.g. by a firewall? – Andrew not the Saint
Where do I set these parameters? I am new to programming Oracle, so I am not sure where to find these parameters. Where can I find ValidateConnection and force a reconnect? (Simply refreshing my browser right after I see the error will fix things immediately. I am not sure if this is a firewall issue.) – futureelite7
Refer to download.oracle.com/docs/cd/B19306_01/java.102/b14355/… And do send the whole error stack, it would be quite helpful – Andrew not the Saint
+1 on validate connection. I had the same issue in .NET world, had to add Validate Connection=true to the connection string. Your syntax may vary with jdbc. – MatthewMartin

3 Answers

0
votes

Apache DBCP (DB connection pool) will help here. You can configure with with a 'validationQuery' parameter, which is an SQL query to run on a connection removed from the pool BEFORE it gives it to you. If the query fails, the connection is discarded and a new one is made and given to you.

See here http://commons.apache.org/proper/commons-dbcp/configuration.html for more details.

Note, the validationQuery will be called every time you get a connection from the pool, so it needs to be as fast as possible. eg:

SELECT id FROM users LIMIT 1;
0
votes

You can add some configuration parameters in the context.xml so that tomcat connection pool will take care of the dead connections. Here is one example of the context.xml please use the user/password/url/ports etc according to you settings.

<?xml version="1.0" encoding="UTF-8"?>
<Context path="">
    <!-- Specify a JDBC data source -->
    <Resource name="jdbc/db" auth="Container"
              type="javax.sql.DataSource"
              username="user"
              password="password"
              driverClassName="driver.class.fullpath"
              url="jdbc:oracle://localhost:3306/
              maxActive="50"
              maxIdle="10"
              testOnBorrow="true"
              testWhileIdle="true"
              validationQuery="/* ping */"
              removeAbandoned="true"
              logAbandoned="true"/>
</Context>
-1
votes

Use another connection pool library, such as C3P0 and use the feature for automatic reconnect.