Recovering jdbc connection after db link failure

Question

Can I recover a JDBC database connection after accessing a database link of a remote database that has been disconnected? We have an application that uses a single connection to a (local) oracle database, but occasionally reads data from a remote database through a database link (REMOTE_DB). The problem is that if the remote database goes offline for some reason (network disconnect), 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. 

The specific Java exception with the JDBC driver ojdbc6.jar occuring with statements 2 and 3 are

    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 that 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 Oracle thin JDBC driver. The following java program can be used to reproduce the problem.

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 preferrable not to do so, since we might be in the middle of a transaction when the error occurs.

EDIT: Note that with SQLPlus I can do the following, a problem that using a JDBC connection pool won't solve:

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, eg Apache DBCP http://commons.apache.org/proper/commons-dbcp/ they restore failed connections automatically. It also a preferred way to work with DB connections.

Answer 2

We were able to solve the problem. As described in the edited question, just dropping the connection in the error case is not viable, since we might be in the middle of a transaction.

It turns out that closing the PreparedStatement after each execution and recreating it in the example program above makes the problem disappear. Unless you use oracle implicit statement caching to improve performance, which we do.

It seems the problem only occurs if oracle is using an existing cursor for a statement that uses the disconnected server link. And the problem seems to be independent of the version of JDBC, but occuring only with the Oracle 11g and not with earlier versions of Oracle RDBMS.

So the solution consisted of disabling statement caching for statements that use the database link.

The following modified example 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);
        }

    }
}