Databases
StackOverflow2013
Postsdbo
PORecovering jdbc connection after db link failure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORecovering jdbc connection after db link failure
    primarykey
    data
    text
    <p>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 (<code>REMOTE_DB</code>). 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:</p> <pre><code>1. SELECT 1 FROM DUAL@REMOTE_DB =&gt; ok &lt;&lt;Network failure&gt;&gt; 2. SELECT 1 FROM DUAL@REMOTE_DB =&gt; SQLException. 3. SELECT 1 FROM DUAL =&gt; SQLException. </code></pre> <p>The specific Java exception with the JDBC driver <code>ojdbc6.jar</code> occuring with statements 2 and 3 are</p> <pre><code> java.sql.SQLRecoverableException: No more data to read from socket at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1185) </code></pre> <p>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 <code>SQLPlus</code> or <code>Perl DBI</code>. 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.</p> <pre><code>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(); } } </code></pre> <p>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.</p> <p><strong>EDIT:</strong> Note that with <code>SQLPlus</code> I can do the following, a problem that using a JDBC connection pool won't solve:</p> <pre><code>SQL&gt; update my_table set ...; 1 row updated. SQL&gt; select * from dual@REMOTE_DB; D - X &lt;&lt;Network failure&gt;&gt; SQL&gt; 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&gt; update my_table set ...; 1 row updated. SQL&gt; commit; Commit complete. SQL&gt; </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USgatinueta
    1. USgatinueta
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORecovering jdbc connection after db link failure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PORecovering jdbc connection after db link failure
      1. USuser75ponic
      1. VTFavorite
    3. VO
      singulars
      1. PORecovering jdbc connection after db link failure
      1. This table or related slice is empty.
      1. VTUpMod
    1. COA lot will depend on the implementation of the driver and it's ability to detect these failures. You could try having a look at some of the other properties of `Connection`, such as [`Connection#isClosed`](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#isClosed()) and [`Connection#isValid`](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#isValid(int))
      singulars
      1. PORecovering jdbc connection after db link failure
      1. USMadProgrammer
    2. COMaybe offtpic, maybe usefull. When you execute query like `select * from dual join dual@remote ...` then you can get an error `ORA-1555 Snapshot too old` and Oracle will NOT tell which database (local/remote) thrown an error. Your case is probably similar - some layer received some "critical" ORA error and thinks that it came from local database. Try to execute OCIPing/pingDatabase method from JDBC.
      singulars
      1. PORecovering jdbc connection after db link failure
      1. USibre5041
    3. CO@MadProgrammer after the failure, `connection.isValid()` returns false, `connection.isClosed()` returns false.
      singulars
      1. PORecovering jdbc connection after db link failure
      1. USgatinueta
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload