Oracle DB : java.sql.SQLException: Closed Connection

Question

Reasons for java.sql.SQLException: Closed Connection from Oracle??

java.sql.SQLException: Closed Connection at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:1131) at oracle.jdbc.OracleConnectionWrapper.commit(OracleConnectionWrapper.java:117)

We are getting this error from the fail over database connection. We use the same code for other databases as well. But seeing this issue with only one of the databases. Is this because the connection might have timeout due to long inactivity period and we are trying to use that? Pls let me know if you need more details...

AbandonedConnectionTimeout set to 15 mins InactivityTimeout set to 30 mins

Solution 1

It means the connection was successfully established at some point, but when you tried to commit right there, the connection was no longer open. The parameters you mentioned sound like connection pool settings. If so, they're unrelated to this problem. The most likely cause is a firewall between you and the database that is killing connections after a certain amount of idle time. The most common fix is to make your connection pool run a validation query when a connection is checked out from it. This will immediately identify and evict dead connnections, ensuring that you only get good connections out of the pool.

Solution 2

You have to validate the connection.

If you use Oracle it is likely that you use Oracle´s Universal Connection Pool. The following assumes that you do so.

The easiest way to validate the connection is to tell Oracle that the connection must be validated while borrowing it. This can be done with

pool.setValidateConnectionOnBorrow(true);

But it works only if you hold the connection for a short period. If you borrow the connection for a longer time, it is likely that the connection gets broken while you hold it. In that case you have to validate the connection explicitly with

if (connection == null || !((ValidConnection) connection).isValid())

See the Oracle documentation for further details.

Comments

[Java Guy]

thanks for the answer. Does the validation connection property add any overhead? I am seeing this only with one of the DB, I too doubt something wrong with that paticular DB.

[Ryan Stewart]

It will naturally add some overhead, as it has to actually hit the database in order to validate the connection; however, each database has a recommended "validation query" to use that incurs minimal overhead on the database side, so the only slowdown you'll get is from whatever network latency there is. Regardless of the problem, adding a validation query is a pretty foolproof way to avoid getting dead connections from the pool. I believe the current recommended query for Oracle is select 1 from dual

[obesechicken13]

If you can't use UCP or don't want to deal with the headache of Oracle artifacts not being available on public maven repositories I've just recently discovered that java.sql.Connection has an isValid method. It works on a timeout.

[obesechicken13]

And oracle.ucp.jdbc.ValidConnection's isValid causes my scheduled logger to crash on a java.lang.ClassCastException