ORA-03137 TTC協定内部錯誤問題排查

一、故障背景

某項目頁面查詢子產品打開報錯，通信通道的檔案結束，生産業務受到影響。

二、排查過程

1、檢視系統其它子產品打開正常，受影響的隻有1個功能子產品，随即檢視oracle告警日志，發現其中有大量報錯如下：

Wed Aug 29 07:28:04 2018

Errors in file e:\app\administrator\diag\rdbms\gqdb\gqdb\trace\gqdb_ora_28448.trc (incident=16177):

ORA-03137: TTC 協定内部錯誤: [12333] [64] [0] [98] [] [] [] []

Incident details in: e:\app\administrator\diag\rdbms\gqdb\gqdb\incident\incdir_16177\gqdb_ora_28448_i16177.trc

Wed Aug 29 07:28:08 2018

Trace dumping is performing id=[cdmp_20180829072808]

Wed Aug 29 07:28:38 2018

Errors in file e:\app\administrator\diag\rdbms\gqdb\gqdb\trace\gqdb_ora_45000.trc (incident=16089):

ORA-03137: TTC 協定内部錯誤: [12333] [64] [0] [98] [] [] [] []

Incident details in: e:\app\administrator\diag\rdbms\gqdb\gqdb\incident\incdir_16089\gqdb_ora_45000_i16089.trc

Wed Aug 29 07:28:41 2018

Trace dumping is performing id=[cdmp_20180829072841]

Wed Aug 29 07:29:43 2018

Thread 1 cannot allocate new log, sequence 21462

Private strand flush not complete

Current log# 2 seq# 21461 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\GQDB\REDO02.LOG

Thread 1 advanced to log sequence 21462 (LGWR switch)

Current log# 3 seq# 21462 mem# 0: E:\APP\ADMINISTRATOR\ORADATA\GQDB\REDO03.LOG

Wed Aug 29 07:30:00 2018

Errors in file e:\app\administrator\diag\rdbms\gqdb\gqdb\trace\gqdb_j000_40352.trc:

ORA-12012: 自動執行作業 12692 出錯

ORA-06550: 第 1 行, 第 729 列:

PLS-00905: 對象 GQZWFW.ASP_REFRESHLEFTTIME 無效

ORA-06550: 第 1 行, 第 729 列:

PL/SQL: Statement ignored

Wed Aug 29 07:33:10 2018

Errors in file e:\app\administrator\diag\rdbms\gqdb\gqdb\trace\gqdb_ora_36984.trc (incident=16178):

ORA-03137: TTC 協定内部錯誤: [12333] [64] [0] [98] [] [] [] []

Incident details in: e:\app\administrator\diag\rdbms\gqdb\gqdb\incident\incdir_16178\gqdb_ora_36984_i16178.trc

2、根據告警提供的trace檔案，詳細檢視報錯資訊

*** 2018-08-29 14:04:34.940

*** SESSION ID:(69.59095) 2018-08-29 14:04:34.940

*** CLIENT ID:() 2018-08-29 14:04:34.940

*** SERVICE NAME:(gqdb) 2018-08-29 14:04:34.940

*** MODULE NAME:(w3wp.exe) 2018-08-29 14:04:34.940

*** ACTION NAME:() 2018-08-29 14:04:34.940

Dump continued from file: e:\app\administrator\diag\rdbms\db\db\trace\db_ora_47708.trc

ORA-03137: TTC 協定内部錯誤: [12333] [254] [64] [0] [] [] [] []

========= Dump for incident 16305 (ORA 3137 [12333]) ========

*** 2018-08-29 14:04:34.946

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=gy425phpm9wq8) -----

SELECT AUDIT_PROJECT.RowGuid from AUDIT_PROJECT,AUDIT_TASK where AUDIT_PROJECT.TASKGUID=AUDIT_TASK.RowGuid and ITEM_ID in (select AUDIT_TASK.ITEM_ID from AUDIT_WINDOW_TASK,AUDIT_WINDOW_USER,AUDIT_TASK where AUDIT_WINDOW_TASK.WINDOWGUID=AUDIT_WINDOW_USER.WINDOWGUID and AUDIT_WINDOW_TASK.TASKGUID=AUDIT_TASK.RowGuid and USERGUID=:UserGuid ) and STATUS=:status

可以看到引起報錯的為一條SQL，正好涉及到故障頁面查詢。

三、查閱資料

根據報錯代碼，查閱MOS文檔

Troubleshooting ORA-3137 [12333]

Errors Encountered When Using Oracle JDBC Driver (文檔 ID 1361107.1)

1

2

此報錯資訊來源于11.2.0.1其中一個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.

四、解決方案

1、禁用Bind Peeking

SQL> alter system set "_optim_peek_user_binds"=false;

此參數為oracle的隐含參數

2、更新資料庫版本

此bug已在11.2.0.3以上版本修複，可更新此版本或更高

SQL> col ksppinm for a20

SQL> col ksppinm for a30

SQL> col ksppstvl for a30

SQL> col ksppdesc for a30

SQL> SELECT ksppinm, ksppstvl, ksppdesc

FROM x$ksppi x, x$ksppcv y

WHERE x.indx = y.indx AND ksppinm = '_optim_peek_user_binds';

KSPPINM KSPPSTVL KSPPDESC

------------------------------ ------------------------------ ------------------------------

_optim_peek_user_binds TRUE enable peeking of user binds

檢視隐含參數，此參數為開啟狀态