如何处理ORA-17820错误并捕获数据库连接异常

问题场景 #

你希望捕获数据库连接时的错误，当前代码已尝试捕获SQLRecoverableException和SQLException，甚至尝试捕获Exception，但依然会看到Oracle驱动打印的大量异常日志（如ORA-17820网络连接失败），你不需要修复连接问题，只需要正确处理这些异常（控制日志输出、确保异常被自己的逻辑处理）。

你的代码 #

private static String getValueFromDB(String dbConnection,String dbUsername,String dbPassword,String condition,String field){
        String data="";
        try {
            Connection connection = DriverManager.getConnection(dbConnection, dbUsername, dbPassword);
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM NDTBNODE WHERE " + condition);
            preparedStatement.setString(1, licenseKey);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                // Process the result set
                if(field.equals("DC_ID"))
                    data=String.valueOf(resultSet.getInt(field));
                else
                    data=resultSet.getString(field);
            }
        } catch (java.sql.SQLRecoverableException e) {
            loggerS.error("Recoverable error occurred while connecting to the database: {}", e.getMessage());
        } catch (java.sql.SQLException e) {
            loggerS.error("SQL Exception occurred while interacting with the database: {}", e.getMessage());
        }
        return data;
}

当前出现的异常日志 #

May 12, 2024 12:32:34 PM oracle.jdbc.driver.PhysicalConnection connect
INFO: I:thread-1 main entering args (oracle.jdbc.internal.AbstractConnectionBuilder$1@44a59da3)
May 12, 2024 12:32:34 PM oracle.net.ns.NSProtocol connect
INFO: U:thread-1 main traceId=5276D6EE. 
java.sql.SQLRecoverableException: ORA-17820: The network adapter could not establish the connection.
oracle.jdbc.driver.T4CConnection.handleLogonNetException(T4CConnection.java:1398)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:991)
at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:1157)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:104)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:825)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:651)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:230)
at com.iono_tech.SDK_Service.SdkServiceApplication.getValueFromDB(SdkServiceApplication.java:408)
at com.iono_tech.SDK_Service.SdkServiceApplication.checkForId(SdkServiceApplication.java:435)
at com.iono_tech.SDK_Service.SdkServiceApplication.checkForTheFile(SdkServiceApplication.java:490)
at com.iono_tech.SDK_Service.SdkServiceApplication.main(SdkServiceApplication.java:562)
Caused by: oracle.net.ns.NetException: ORA-17820: The network adapter could not establish the connection. 
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:779)
at oracle.net.resolver.AddrResolution.resolveAndExecute(AddrResolution.java:707)
at oracle.net.ns.NSProtocol.establishConnection(NSProtocol.java:938)
at oracle.net.ns.NSProtocol.connect(NSProtocol.java:327)
at oracle.jdbc.driver.T4CConnection.connectNetworkSessionProtocol(T4CConnection.java:3185)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:926)
    ... 10 more
Caused by: java.io.IOException: 
at oracle.net.nt.TcpNTAdapter.establishSocket(TcpNTAdapter.java:325)
at oracle.net.nt.TcpNTAdapter.connect(TcpNTAdapter.java:222)
at oracle.net.nt.ConnOption.connect(ConnOption.java:327)
at oracle.net.nt.ConnStrategy.executeConnOption(ConnStrategy.java:1144)
at oracle.net.nt.ConnStrategy.execute(ConnStrategy.java:718)
    ... 15 more
Caused by: java.net.NoRouteToHostException: No route to host: no further information
at java.base/sun.nio.ch.Net.pollConnect(Native Method)
at java.base/sun.nio.ch.Net.pollConnectNow(Net.java:682)
at java.base/sun.nio.ch.SocketChannelImpl.finishConnect(SocketChannelImpl.java:973)
at oracle.net.nt.TimeoutSocketChannel.initializeSocketChannel(TimeoutSocketChannel.java:246)
at oracle.net.nt.TimeoutSocketChannel.connect(TimeoutSocketChannel.java:203)
May 12, 2024 12:32:34 PM oracle.jdbc.diagnostics.Diagnostic dumpDiagnoseFirstFailure

处理方案 #

1. 明确核心问题：异常已被捕获，冗余日志来自Oracle驱动

你代码中的catch块已经成功捕获了SQLRecoverableException，但Oracle JDBC驱动默认会输出INFO级别的连接追踪日志和异常详情，这些日志是驱动内部打印的，不属于未捕获的异常。要控制这些日志，需要调整驱动的日志级别。

2. 禁用Oracle驱动的冗余日志

如果使用日志框架（如Logback、Log4j2），可以在配置文件中添加以下规则，屏蔽Oracle驱动的INFO/DEBUG日志：

• 对于Logback：

<logger name="oracle.jdbc" level="ERROR"/>
<logger name="oracle.net" level="ERROR"/>

• 对于Log4j2：

<Logger name="oracle.jdbc" level="ERROR"/>
<Logger name="oracle.net" level="ERROR"/>

这样只会保留驱动的ERROR级日志，避免大量冗余的连接追踪信息输出。

3. 完善异常处理逻辑（可选）

如果需要更完整的异常覆盖：

• 新增兜底的Exception捕获块（必须放在具体异常捕获之后）：

catch (Exception e) {
    loggerS.error("Unexpected error during database operation: {}", e.getMessage());
}

• 记录完整异常栈轨迹（而非仅消息），方便后续排查：

loggerS.error("Recoverable error occurred while connecting to the database", e);
// 直接传入异常对象，日志框架会自动打印完整栈轨迹

4. 补充资源关闭逻辑（避免资源泄漏）

虽然你不需要修复连接问题，但未关闭的数据库连接、语句和结果集会导致资源泄漏，建议使用try-with-resources语法自动关闭资源：

private static String getValueFromDB(String dbConnection,String dbUsername,String dbPassword,String condition,String field){
    String data="";
    try (Connection connection = DriverManager.getConnection(dbConnection, dbUsername, dbPassword);
         PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM NDTBNODE WHERE " + condition)) {
        preparedStatement.setString(1, licenseKey);
        try (ResultSet resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                if(field.equals("DC_ID"))
                    data=String.valueOf(resultSet.getInt(field));
                else
                    data=resultSet.getString(field);
            }
        }
    } catch (java.sql.SQLRecoverableException e) {
        loggerS.error("Recoverable error occurred while connecting to the database", e);
    } catch (java.sql.SQLException e) {
        loggerS.error("SQL Exception occurred while interacting with the database", e);
    } catch (Exception e) {
        loggerS.error("Unexpected error during database operation", e);
    }
    return data;
}

try-with-resources会自动关闭实现AutoCloseable接口的资源，无需手动调用close()。

内容的提问来源于stack exchange，提问作者Mhd adnan Al kharfan