Can jdbc connections be recovered?

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-sta开发者_如何学JAVArt. 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

---

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!

---

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.