Can jdbc connections be recovered?

1
votes

Can jdbc connections which are closed due to database un-availability be recovered.

To give back ground I get following errors in sequence. It doesn't look to be manual re-start. The reason for my question is that I am told that the app behaved correctly without the re-start. So if the connection was lost, can it be recovered, after a DB re-start.

java.sql.SQLException: ORA-12537: TNS:connection closed

java.sql.SQLRecoverableException: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist IBM AIX RISC System/6000 Error: 2: No such file or directory

java.sql.SQLRecoverableException: ORA-01033: ORACLE initialization or shutdown in progress

2
databaseoraclejdbc
As opposed to just opening a new connection? - Marvo
Are you keeping connections open during the whole application's lifetime? That's bad. They should be opened and closed in the shortest possible scope, preferably right inside the same method block as where you're executing the SQL query. - BalusC
NO, thats not the case, they are being closed, and I do use a connection pool. I got the exception in my log and am trying to decipher what might have happened. Keeping in mind that the application was running fine afterwards. Hope this clarifies. - Rig Veda
Perhaps your connection pool is not properly configured to test connections on borrow and remove abandoned connections? For example the poor DBCP pool does not do that by default. You may want to add more details about the connection pool used and its configuration to the question. - BalusC

2 Answers

3
votes

No. The connection is "dead". Create a new connection.

A good approach is to use a connection pool, which will test if the connection is still OK before giving it to you, and automatically create a new connection if needed.

There are several open source connection pools to use. I've used Apache's JDCP, and it worked for me.

Edited:
Given that you want to wait until the database comes back up if it's down (interesting idea), you could implement a custom version of getConnection() that "waits a while and tries again" if the database doesn't respond.

p.s. I like this idea!

0
votes

The connection cannot be recovered. What can be done is to failover the connection to another database instance. RAC and data guard installations support this configuration.

This is no problem for read-only transactions. However for transactions that execute DML this can be a problem, especially if the last call to the DB was a commit. In case of a commit the client cannot tell if the commit call completed or not. When did the DB fail; before executing the commit, or after executing the commit (but not sending back the acknowledgment to the client). Only the application has this logic and can do the right thing. If the application after failing over does not verify the state of the last transaction, duplicate transactions are possible. This is a known problem and most of us experienced it buying tickets or similar web transactions.