'How to auto reconnect Oracle connection pool after connection interruption?

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?)

Solution 1:^[1]

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

Solution 2:^[2]

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;

Solution 3:^[3]

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>

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	mhaller
Solution 2	Ramsay Domloge
Solution 3	jindal

'How to auto reconnect Oracle connection pool after connection interruption?

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]

Solution 3:^[3]