java.sql.SQLException: 关闭的连接

 

 最近工作中遇到一个非常奇怪的问题，数据库没有进行补丁修复，应用没有修改过，程序也没有变动过，但是weblogic后台一直报异常，信息如下：

java.sql.SQLException: 关闭的连接
        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.ja
va:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:1
12)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:173)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:229)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:403)
        at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnect
ion.java:2925)
        at oracle.jdbc.driver.PhysicalConnection.createStatement(PhysicalConnect
ion.java:2902)
        at weblogic.jdbc.wrapper.Connection.createStatement(Connection.java:443)

        at qqbean.sm.oms.system.service.OrderTimerBO.selectProductTeam(OrderTime
rBO.java:166)
        at qqbean.sm.oms.system.service.OrderReduceTimer.run(OrderReduceTimer.ja
va:38)
        at java.util.TimerThread.mainLoop(Timer.java:512)
        at java.util.TimerThread.run(Timer.java:462)

当前有事业务高峰期，功能部分好用，部分异常，电话一个接一个，头脑开始肿大。根据以上异常，自我判断为以下几点并根据以下

几点进行排查；

1、服务器进行补丁修复

      查看系统日志，未发现有补丁修复的记录，也没有weblogic应用异常的信息；排除该可能性；

2、数据库进行补丁修复

      联系IBM运维团队，并为对数据库进行补丁修复；排除该可能性；

3、weblogic连接池配置问题

       weblogic连接池进行测试连接没有问题，重启部署不能解决问题，郁闷中......

4、oracle的session超过系统最大值

       查看当前用户的session没有限制排除该问题；

 

百感交集在查看后台日志，看到如下信息：

[ERROR] AbstractDAO:314 - java.sql.SQLException: ORA-00600: internal error code,
 arguments: [kkslgbv0], [], [], [], [], [], [], []

 

根据这个错误得知是数据库的一个bug：

Bug 5155885  OERI[kkslgbv0] with CURSOR_SHARING=similar
 This note gives a brief overview of bug 5155885.
 The content was last updated on: 03-APR-2009
 Click here for details of each of the sections below.
Affects:Product (Component)Oracle Server (Rdbms)Range of versionsbelieved to be affectedVersions < 11Versionsconfirmed as being affected

• 10.2.0.3

Platforms affectedGeneric (all / most platforms affected)
Fixed:This issue is fixed in

• 10.2.0.2 Patch 6 on Windows Platforms
• 10.2.0.3 Patch 1 on Windows Platforms
• 10.2.0.3 Patch 24 on Windows Platforms
• 10.2.0.4 (Server Patch Set)
• 11.1.0.6 (Base Release)

Symptoms:Related To:

• Internal Error May Occur (ORA-600)
• ORA-600 [kkslgbv0]

• Literal Replacement (CURSOR_SHARING)
• CURSOR_SHARING
• _OPTIM_PEEK_USER_BINDS

Description

A ORA-600 can occur while accessing a bind value while deciding whether
to shared a cursor or not when literal replacement is enabled.

Workaround
 Use CURSOR_SHARING=EXACT or set "_optim_peek_user_binds"=false so that
 bind values are not peeked.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. Always consult with Oracle Support for advice.

References

Bug:5155885 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

针对这个问题，修复bug可能会导致更严重的问题，于是准备从导致该bug的sql出手，避开该bug。功夫不负有心人，问题终于通过修改sql后得到解决。