Recover jdbc connection after db link failure

Question

Can I reconnect to a JDBC database after accessing a database link of a remote database that has been disconnected? We have an application that uses one connection to the local oracle database, but sometimes reads data from a remote database through a database link ( REMOTE_DB ). The problem is that if the remote database is disconnected for some reason (network disconnection), after accessing the database link, the jdbc connection becomes unusable. I execute the following three SQL statements:

 1. SELECT 1 FROM DUAL@REMOTE _DB => ok <<Network failure>> 2. SELECT 1 FROM DUAL@REMOTE _DB => SQLException. 3. SELECT 1 FROM DUAL => SQLException. 

A specific Java exception with the JDBC driver ojdbc6.jar having operators 2 and 3,

  java.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1185) 

The reason I think this behavior is not “by design” is because the same problem does NOT occur when I execute the same sequence using SQLPlus or Perl DBI . The problem occurs with Oracle 11 with several versions of the thin Oracle JDBC driver. To reproduce the problem, you can use the following java program.

 import java.io.BufferedReader; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestJdbc { private static Connection connect() throws Exception { String jdbcURL = "jdbc:oracle:thin:@localhost:1521:TNSNAME"; String user = "scott" ; String passwd ="tiger"; Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); return DriverManager.getConnection(jdbcURL,user,passwd); } public static void main(String[] args) throws Exception { Connection conn = connect(); PreparedStatement stServer = conn.prepareStatement("SELECT 'server' FROM DUAL@REMOTE _DB"); PreparedStatement stClient = conn.prepareStatement("SELECT 'client' FROM DUAL"); ResultSet resultSet; try { stServer.execute(); resultSet = stServer.getResultSet(); if (resultSet.next()) { System.out.println("server: " + resultSet.getString(1)); } } catch (SQLException e) { System.out.println("exception on server link: " + e); } // force network disconnect here and press enter BufferedReader lineOfText = new BufferedReader(new InputStreamReader(System.in)); lineOfText.readLine(); try { stServer.execute(); resultSet = stServer.getResultSet(); if (resultSet.next()) { System.out.println("server: " + resultSet.getString(1)); } } catch (SQLException e) { //SQLRecoverableException occurs here System.out.println("exception on server link: " + e); } // press enter again lineOfText.readLine(); try { stClient.execute(); resultSet = stClient.getResultSet(); if (resultSet.next()) { System.out.println("client: " + resultSet.getString(1)); } } catch (SQLException e) { System.out.println("exception on client connection: " + e); } stServer.close(); stClient.close(); } } 

Closing and reopening the connection will solve the problem, but it would be preferable not to do this, since we may be in the middle of the transaction when an error occurs.

EDIT: Please note that with SQLPlus I can do the following: the problem of using the JDBC connection pool will not be resolved:

 SQL> update my_table set ...; 1 row updated. SQL> select * from dual@REMOTE _DB; D - X <<Network failure>> SQL> select * from dual@REMOTE _DB; select * from dual@REMOTE _DB * ERROR at line 1: ORA-12545: Connect failed because target host or object does not exist SQL> update my_table set ...; 1 row updated. SQL> commit; Commit complete. SQL> 

Answer 1

Use a connection pool, for example Apache DBCP http://commons.apache.org/proper/commons-dbcp/ , they automatically recover failed connections. This is also the preferred way to work with DB connections.

Answer 2

We were able to solve the problem. As described in the edited question, simply removing the connection in the event of an error is not viable, as we might end up in the middle of a transaction.

It turns out that closing PreparedStatement after each execution and re-creating it in the above sample program fixes the problem. If you do not use oracle caching of statements to improve performance, what we do.

It seems that the problem only occurs if oracle uses the existing cursor for the statement using the disconnected server link. And the problem does not seem to depend on the JDBC version, but only happens with Oracle 11g and not with earlier versions of Oracle RDBMS.

Thus, the solution was to disable statement caching for statements using a database link.

The following modified sample program demonstrates the solution.

 import java.io.BufferedReader; import java.io.InputStreamReader; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import oracle.jdbc.OracleConnection; import oracle.jdbc.OraclePreparedStatement; public class TestJdbc { private static Connection connect() throws Exception { String jdbcURL = "jdbc:oracle:thin:@localhost:1521:TNSNAME"; String user = "scott" ; String passwd ="tiger"; Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); OracleConnection conn = (OracleConnection) DriverManager.getConnection(jdbcURL,user,passwd); // use implicit statement caching, so Oracle cursors are reused for // frequent SQL statements conn.setImplicitCachingEnabled(true); conn.setStatementCacheSize(100); return conn; } public static void main(String[] args) throws Exception { Connection conn = connect(); ResultSet resultSet; try { PreparedStatement stServer = conn.prepareStatement("SELECT 'server' FROM DUAL@REMOTE _DB"); stServer.execute(); resultSet = stServer.getResultSet(); if (resultSet.next()) { System.out.println("server: " + resultSet.getString(1)); } resultSet.close(); // don't cache this statement, so calling it after a network // failure will not destroy our connection ((OraclePreparedStatement)stServer).setDisableStmtCaching(true); stServer.close(); } catch (SQLException e) { System.out.println("exception on server link: " + e); } // force network disconnect here and press enter BufferedReader lineOfText = new BufferedReader(new InputStreamReader(System.in)); lineOfText.readLine(); try { PreparedStatement stServer = conn.prepareStatement("SELECT 'server' FROM DUAL@REMOTE _DB"); stServer.execute(); resultSet = stServer.getResultSet(); if (resultSet.next()) { System.out.println("server: " + resultSet.getString(1)); } resultSet.close(); ((OraclePreparedStatement)stServer).setDisableStmtCaching(true); stServer.close(); } catch (SQLException e) { System.out.println("exception on server link: " + e); } // press enter again lineOfText.readLine(); try { PreparedStatement stClient = conn.prepareStatement("SELECT 'client' FROM DUAL"); stClient.execute(); resultSet = stClient.getResultSet(); if (resultSet.next()) { System.out.println("client: " + resultSet.getString(1)); } resultSet.close(); stClient.close(); } catch (SQLException e) { System.out.println("exception on client connection: " + e); } } }