遭遇ORA-17410及ORA-03137:TTC协议内部错误:[12333]

早上,项目组同事发过来一则应用系统连接数据库错误的请求技术支持,具体信息可见下述报错截图:

ora-17410

ora-17410

这则错误信息对于我还真是大姑娘上轿–头一回遇到。下面简单记录下处理错误的思路和方法:

环境说明:一套Linux X86_64位环境的Oracle 11gR2【11.2.0.1.0】的单实例数据库。应用系统通过调用Tomcat的JDBC驱动来访问数据,如下:

OS:

[oracle@oracle11g ~]$ uname -rm
2.6.18-194.el5 x86_64
[oracle@oracle11g ~]$

数据库:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>

同事说,上周五的时候,通过登录应用系统来访问数据库还一切正常,今天早上登录系统就报上述错误,更加觉得蹊跷。

1 首先,让同事登录应用系统,重现错误。然后查看alert日志文件,看到下述错误信息的线索:

Mon Sep 16 09:43:24 2013
Errors in file /u01/app/oracle/diag/rdbms/glndb/GLNDB/trace/GLNDB_ora_11692.trc  (incident=78959):
ORA-03137: TTC 协议内部错误: [12333] [6] [88] [77] [] [] [] []
Incident details in: /u01/app/oracle/diag/rdbms/glndb/GLNDB/incident/incdir_78959/GLNDB_ora_11692_i78959.trc
Mon Sep 16 09:43:27 2013
Trace dumping is performing id=[cdmp_20130916094327]
Mon Sep 16 09:43:28 2013
Sweep [inc][78959]: completed
Sweep [inc2][78959]: completed

2 根据alert日志提示,继续查看/u01/app/oracle/diag/rdbms/glndb/GLNDB/trace/GLNDB_ora_11692.trc文件,看到:

*** 2013-09-16 09:43:24.912
*** SESSION ID:(410.54431) 2013-09-16 09:43:24.912
*** CLIENT ID:() 2013-09-16 09:43:24.912
*** SERVICE NAME:(SYS$USERS) 2013-09-16 09:43:24.912
*** MODULE NAME:(JDBC Thin Client) 2013-09-16 09:43:24.912
*** ACTION NAME:() 2013-09-16 09:43:24.912

--- PROTOCOL VIOLATION DETECTED ---
...
...
...
  NamespaceDump:  
            Child Cursor:  Heap0=0x9fae5148 Heap6=0xb8bc6b48 Heap0 
Load Time=09-16-2013 09:43:24 Heap6 Load Time=09-16-2013 09:43:24 
  NamespaceDump:  
    Parent Cursor:  sql_id=4x3cjtfs37n4v parent=0xb21328e8 maxchild=5 plk=y ppn=n 
      Current Cursor Sharing Diagnostics Nodes:  
        Child Node: 3  ID=40 reason=Bind mismatch(33) size=2x4 
          init ranges in first pass: 1 
          selectivity: 0 
        Child Node: 2  ID=37 reason=Authorization Check failed(4) size=5x4 
          translation table position: 0 
          original handle: 3055622408 
          temp handle: 2705674440 
          schema: 161 
          synonym object number: 0 
        Child Node: 1  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 1 
      Aged Out Cursor Sharing Diagnostic Nodes:  
        Child Node: 3  ID=37 reason=Authorization Check failed(4) size=5x4 
          translation table position: 0 
          original handle: 2705674440 
          temp handle: 3055622408 
          schema: 160 
          synonym object number: 0 
        Child Node: 2  ID=40 reason=Bind mismatch(25) size=0x0 
          extended cursor sharing:   
        Child Node: 1  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 1 
        Child Node: 0  ID=37 reason=Authorization Check failed(4) size=5x4 
          translation table position: 0 
          original handle: 3055622408 
          temp handle: 2705674440 
          schema: 161 
          synonym object number: 0 
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 0 
        Child Node: 3  ID=37 reason=Authorization Check failed(4) size=5x4 
          translation table position: 0 
          original handle: 3055622408 
          temp handle: 2705674440 
          schema: 161 
          synonym object number: 0 
        Child Node: 2  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 2 
        Child Node: 1  ID=37 reason=Authorization Check failed(4) size=5x4 
          translation table position: 0 
          original handle: 2705674440 
          temp handle: 3055622408 
          schema: 160 
          synonym object number: 0 
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 0 
        Child Node: 1  ID=37 reason=Authorization Check failed(4) size=5x4 
          translation table position: 0 
          original handle: 2705674440 
          temp handle: 3055622408 
          schema: 160 
          synonym object number: 0 
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 0 
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 0 
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 0 
        Child Node: 0  ID=3 reason=Optimizer mismatch(13) size=3x4 
          kxscflg: 32 
          kxscfl4: 4194560 
          dnum_kksfcxe: 0    kkscs=0xb2132df0 nxt=0xb21331f8 flg=11 cld=0 
hd=0xa1a00330 par=0xb21328e8
   Mutex 0xb2132df0(0, 0) idn 3000000000
   ct=19 hsh=0 unp=(nil) unn=0 hvl=b2133798 nhv=0 ses=(nil)
   hep=0xb2132e80 flg=80 ld=0 ob=(nil) ptr=(nil) fex=(nil)
   kkscs=0xb21331f8 nxt=0xb9a94588 flg=1a cld=1 hd=0xaf246848 par=0xb21328e8
   Mutex 0xb21331f8(0, 0) idn 0
   ct=12 hsh=0 unp=(nil) unn=0 hvl=b21336e0 nhv=0 ses=(nil)
   hep=0xb2133288 flg=80 ld=1 ob=0xb2c09860 ptr=0xab22a250 fex=0xab2295f0
   kkscs=0xb9a94588 nxt=0xb9a94930 flg=18 cld=2 hd=0xaf3c53e8 par=0xb21328e8
   Mutex 0xb9a94588(0, 0) idn 0
   ct=13 hsh=0 unp=(nil) unn=0 hvl=b21336a0 nhv=0 ses=(nil)
   hep=0xb9a94618 flg=80 ld=1 ob=0xb97655a0 ptr=0xa4e14f58 fex=0xa4e142f8
   kkscs=0xb9a94930 nxt=0xa840b1b0 flg=14 cld=3 hd=0xa1515da8 par=0xb21328e8
   Mutex 0xb9a94930(0, 0) idn 0
   ct=14 hsh=0 unp=(nil) unn=0 hvl=b2133638 nhv=1 ses=0xc0467f40
   hsv[0]=0
   hep=0xb9a949c0 flg=80 ld=1 ob=0xa81e1108 ptr=0xb16c2ea0 fex=0xb16c2240
   kkscs=0xa840b1b0 nxt=(nil) flg=18 cld=4 hd=0xaf3038c8 par=0xb21328e8
   Mutex 0xa840b1b0(0, 0) idn 0
   ct=19 hsh=0 unp=(nil) unn=0 hvl=b2133660 nhv=0 ses=(nil)
   hep=0xa840b240 flg=80 ld=1 ob=0x9fae5060 ptr=0xb8bc6b48 fex=0xb8bc5ee8
cursor instantiation=0x2ac85fded0d8 used=1379295804 exec_id=16777397 exec=1
 child#4(0xaf3038c8) pcs=0xa840b1b0
  clk=0xb6c31f68 ci=0x9fae5148 pn=0xaf984ba0 ctx=0xb8bc6b48
 kgsccflg=0 llk[0x2ac85fded0e0,0x2ac85fded0e0] idx=0
 xscflg=c0110676 fl2=5d000008 fl3=42222008 fl4=180
 sharing failure(s)=62000
----- Bind Info (kkscoacd) -----
 Bind#0
  oacdty=01 mxl=32(24) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=873 siz=32 off=0
  kxsbbbfp=2ac85fdecae0  bln=32  avl=00  flg=05
 Frames pfr 0x2ac85fdecea8 siz=27104 efr 0x2ac85fdecd98 siz=27088
 kxscphp=0x2ac85fdc03d8 siz=984 inu=456 nps=408
 kxscbhp=0x2ac85fdc01f8 siz=984 inu=176 nps=56
 kxscwhp=0x2ac85fdc0798 siz=16352 inu=15344 nps=5160
Starting SQL statement dump
SQL Information
user_id=161 user_name=WX_TMS_TEST module=JDBC Thin Client action=
sql_id=4x3cjtfs37n4v plan_hash_value=-785040234 problem_type=4
----- Current SQL Statement for this session (sql_id=4x3cjtfs37n4v) -----
select distinct viewbutton5_.SYS_VIEW_BUTTON_ID as SYS1_155_, 
viewbutton5_.RECORD_VERSION as RECORD2_155_, viewbutton5_.BTN_NAME as BTN3_155_, viewbutton5_.BTN_TITLE_KEY as BTN4_155_, viewbutton5_.BTN_MSG_KEY as BTN5_155_,
viewbutton5_.BUTTON_URL as BUTTON6_155_, viewbutton5_.SYS_MENU_ITEM_ID as SYS7_155_ 
from SYS_ROLE_MENU_BUTTON rolemenubu0_ left outer join SYS_ROLE_MENU_ITEM rolemenuit1_ 
on rolemenubu0_.SYS_ROLE_MENU_ITEM_ID=rolemenuit1_.SYS_ROLE_MENU_ITEM_ID left outer 
join SYS_ROLE role2_ on rolemenuit1_.SYS_ROLE_ID=role2_.SYS_ROLE_ID left outer join 
SYS_ROLE_USER roleusers3_ on role2_.SYS_ROLE_ID=roleusers3_.SYS_ROLE_ID left 
outer join SYS_USER user4_ on roleusers3_.USER_ID=user4_.USER_ID inner join 
SYS_VIEW_BUTTON viewbutton5_ on 
rolemenubu0_.SYS_VIEW_BUTTON_ID=viewbutton5_.SYS_VIEW_BUTTON_ID where user4_.USER_CODE=:1
sql_text_length=837
sql=select distinct viewbutton5_.SYS_VIEW_BUTTON_ID as SYS1_155_, viewbutton5_.RECORD_VERSION as RECORD2_155_, viewbutton5_.BTN_NAME as BTN3_155_, viewbutton5_.BTN_TITLE_KEY as BTN4_155_, viewbutton5_.BTN_MSG_KEY as BTN5_155_, viewbutton5_.BUTTON_URL as BUTTON
sql=6_155_, viewbutton5_.SYS_MENU_ITEM_ID as SYS7_155_ from SYS_ROLE_MENU_BUTTON rolemenubu0_ 
left outer join SYS_ROLE_MENU_ITEM rolemenuit1_ on rolemenubu0_.SYS_ROLE_MENU_ITEM_ID=rolemenuit1_.SYS_ROLE_MENU_ITEM_ID left outer 
join SYS_ROLE role2_ on rolemenuit
sql=1_.SYS_ROLE_ID=role2_.SYS_ROLE_ID left outer join SYS_ROLE_USER roleusers3_ on role2_.SYS_ROLE_ID=roleusers3_.SYS_ROLE_ID left outer join SYS_USER user4_ on roleusers3_.USER_ID=user4_.USER_ID inner join SYS_VIEW_BUTTON viewbutton5_ on 
rolemenubu0_.SYS_VIEW
sql=_BUTTON_ID=viewbutton5_.SYS_VIEW_BUTTON_ID where user4_.USER_CODE=:1
...
...

3 看到这里,没有太好的解决思路。同事给出提示信息说,同一套库,另外一套应用环境下,通过WebLogic来访问数据库时,则不会报错。难道是因为JDBC的不同驱动导致的?寻求MetaLink,Troubleshooting ORA-3137 [12333] Errors Encountered When Using Oracle JDBC Driver (Doc ID 1361107.1)

通过Metalink上的提示,得到造成该错误的可能原因:

① 部分版本的JDBC驱动会引起该错误:

Bug 9445675  NO MORE DATA TO READ FROM SOCKET WHEN USING END-TO-END METRICS

This bug does affect the JDBC driver.  This bug may be the cause when all of the following conditions are met:

  1. You are using the 10.1.x.x or the 11.2.0.1 JDBC driver; the bug does not affect 10.2.x.x, or 11.1.x.x versions of the driver, nor versions 11.2.0.2 or above
  2. You are using end-to-end metrics in your Java code
  3. The server side ORA-3137 [12333] error is accompanied by the client side Java exception “No more data to read from socket”

This bug is fixed in the 11.2.0.2 version of the JDBC driver and above.  It is discussed in the following notes:

Note 9445675.8 Bug 9445675 – “No more data” / ORA-3137 using end to end metrics with JDBC Thin

Note 1081275.1 “java.sql.SQLRecoverableException: No more data to read from socket” is Thrown When End-to-end Metrics is Used

② 数据库自身的BUG也会导致该错误:

Unpublished Bug:9703463 – ORA-3137 [12333] or ORA-600 [kpobav-1] When Using Bind Peeking

This bug affects versions 11.1.0.6, 11.1.0.7, and 11.2.0.1 of the RDBMS.  It is fixed in version 11.2.0.2 of the database.

It can also occur intermittently; similarly to unpublished Bug:8625762, this is a bind peeking bug.

==================================================

Unpublished Bug 9373370  DATA BASE RETURNS WRONG CURSORID WHEN THERE IS AN ORA-01013

This bug affects the 10.2, 11.1, 11.2.0.1, and 11.2.0.2 databases.  It is discussed in the following note:

Note 9373370.8 Bug 9373370 – The wrong cursor may be executed by JDBC thin following a query timeout / ORA-3137 [12333]

While the bug primarily manifests in ORA-1006 or ORA-1008 errors, the problem may also result in ORA-600 [12333] or ORA-3137 [12333] errors appearing on the server side.

判断本案例的错误应该是 Bug:9703463导致的,从trace文件中看到该SQL语句的确用到了绑定变量,且数据库版本是11.2.0.1.0。

解决思路:

①  可以通过更换不同版本的JDBC驱动来避免该错误,也说明为什么同事在另外一套环境下,通过Weblogic的JDBC来访问数据库时,则不会遇到该错误;

② 给数据库打patch,初步认为可以通过打Patch:9703463 来解决;

③ 通过修改数据库参数来规避该错误:

修改之后,同事用之前的Tomcat那个版本的驱动来重新访问数据库时,则不再报错;

④ 直接升级数据库版本至11.2.0.3.0,通过导出导入的方式将刚项目组下的schema数据复制一份到一套11.2.0.3.0的库上,重新使用Tomcat那个版本的驱动来重新访问数据库时,亦不再报错。

发表评论

邮箱地址不会被公开。 必填项已用*标注