数据库链接故障后恢复jdbc连接
访问已断开连接的远程数据库的数据库链接后,能否恢复JDBC数据库连接? 我们有一个应用程序使用单个连接到(本地)oracle数据库,但偶尔会通过数据库链接从远程数据库读取数据(REMOTE_DB
)。 问题是,如果远程数据库由于某种原因(网络断开)而脱机,则在访问数据库链接后,jdbc连接将变得不可用。 我执行以下三个SQL语句:数据库链接故障后恢复jdbc连接
1. SELECT 1 FROM [email protected]_DB => ok
<<Network failure>>
2. SELECT 1 FROM [email protected]_DB => SQLException.
3. SELECT 1 FROM DUAL => SQLException.
与JDBC驱动程序ojdbc6.jar
发生的历史与报表的编制具体的Java异常和3
java.sql.SQLRecoverableException: No more data to read from socket
at oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1185)
究其原因,我认为这种行为是不是“设计“是当我使用SQLPlus
或Perl DBI
执行相同的序列时,不会发生同样的问题。使用多个版本的Oracle瘦JDBC驱动程序的Oracle 11会出现问题。下面的java程序可以用来重现问题。
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 [email protected]_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();
}
}
关闭并重新打开连接就能解决问题,但它会preferrable不这样做,因为发生错误时,我们可能会在交易的中间。
编辑:注意与SQLPlus
我可以做下面一个问题,即使用JDBC连接池不会解决:他们恢复失败
SQL> update my_table set ...;
1 row updated.
SQL> select * from [email protected]_DB;
D
-
X
<<Network failure>>
SQL> select * from [email protected]_DB;
select * from [email protected]_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>
我们能够解决问题。正如编辑的问题所述,只是在错误情况下删除连接是不可行的,因为我们可能正处于事务处理的中间。
事实证明,在每次执行后关闭PreparedStatement
并在上面的示例程序中重新创建该问题后,问题就消失了。 除非您使用oracle implicit statement caching来提高我们所做的性能。
看来只有在oracle使用现有的光标作为使用断开连接的服务器链接的语句时才会出现此问题。问题似乎与JDBC的版本无关,但仅与Oracle 11g相关,而不适用于早期版本的Oracle RDBMS。
因此,解决方案包括对使用数据库链接的语句禁用语句缓存。
下面的修改示例程序演示了解决方案。
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 [email protected]_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 [email protected]_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);
}
}
}
很大程度上取决于驱动程序的实现以及检测这些故障的能力。你可以尝试看一下'Connection'的其他一些属性,比如['Connection#isClosed'](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection .html#isClosed())和['Connection#isValid'](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#isValid(int)) – MadProgrammer
也许offtpic ,也许有用。当你执行查询如'select * from dual join dual @ remote ...'那么你可能会得到'ORA-1555快照太旧'的错误,Oracle不会告诉哪个数据库(本地/远程)抛出错误。您的情况可能类似 - 某些图层收到了一些“严重”的ORA错误,并认为它来自本地数据库。尝试从JDBC执行OCIPing/pingDatabase方法。 – ibre5041
@MadProgrammer失败后,'connection.isValid()'返回false,'connection.isClosed()'返回false。 – gatinueta