oracle 11.2.0.1 for windows server2008r2告警ORA-03137

2017.10.20贵州一地市oracle 11.2.0.1医保相关应用程序功能异常无法运行，查看数据库告警日志有如下报错：

Fri Oct 20 16:03:04 2017
Trace dumping is performing id=[cdmp_20171020160304]
Fri Oct 20 16:03:05 2017
Sweep [inc][140434]: completed
Sweep [inc2][140434]: completed
Fri Oct 20 16:12:40 2017
Errors in file e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4756.trc (incident=140155):
ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] []
Incident details in: e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_140155\orcl_ora_4756_i140155.trc
Fri Oct 20 16:12:41 2017
Trace dumping is performing id=[cdmp_20171020161241]

查看告警日志提示的trc文件：

Dump file e:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_140155\orcl_ora_4756_i140155.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1 
CPU : 24 - type 8664, 12 Physical Cores
Process Affinity : 0x0x0000000000000000
Memory (Avail/Total): Ph:45309M/65508M, Ph+PgF:110993M/131015M 
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 32
Windows thread id: 4756, image: ORACLE.EXE (SHAD)

*** 2017-10-20 16:12:40.170
*** SESSION ID:(515.23643) 2017-10-20 16:12:40.170
*** CLIENT ID:() 2017-10-20 16:12:40.170
*** SERVICE NAME:(orcl) 2017-10-20 16:12:40.170
*** MODULE NAME:() 2017-10-20 16:12:40.170
*** ACTION NAME:() 2017-10-20 16:12:40.170

Dump continued from file: e:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4756.trc
ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] []

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

*** 2017-10-20 16:12:40.170
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=905vd7vnpuh6n) -----
select *

from (select row_.*, rownum NumRow from (select * from (select

sid,

code,

to_char(month,‘yyyyMM‘) as month ,

hisid,

bill_no,

state,

billdate,

hospital_id,

patient_id,

patient_name,

admission_number,

admission_disease_name,

disease_name,

claim_name,

benefit_name,

bmino,

benefit_group_name,

item_date,

dept_id,

dept_name,

item_id,

item_name,

item_type,

physician_name,

bmi_convered_amount,

bmi_nopay,

reject_reson,

remrk,

version_no,

hospital_backs,

versionstate,

rule_name,

back_reson,

reback_reason,

processState,

is_approval,

nvl(version,1) as version,

nvl(trickProgress,0) as trickProgress,

nvl(is_retrick,0) as is_retrick,

PERIOD,

billex.NUMBER01 as Number01,

billex.NUMBER02 as Number02,

billex.NUMBER03 as Number03,

billex.NUMBER05 as Number05,

billex.NUMBER06 as Number06,

billex.NUMBER07 as Number07,

HOSPITAL_REMARK_DETAIL,

decode(bitand((select sum(distinct(nvl(g.rule_bit, 0))) from gz_list g where g.business_type = ‘0‘),rule_bit),0,0,1) as BUSINESS_TYPE,

REFEEDBACK_REASON_DETAIL,

(select sum(a.reject_money) from dw_opinion_details b join dw_billdetail a on a.id =b.detailid

where b.code=dw_opinions.code and b.version_no=dw_opinions.version_no and b.month=dw_opinions.month ) as sumrejectmoney

from dw_opinions left join dw_bill_ex billex on dw_opinions.hisid = billex.billid

where 1=1 and month =to_date(:ParamMonth0,‘yyyyMM‘) and hospital_id = :ParamHospitalId1 and version_no = :versionno2 order by month desc,sid)) row_ where rownum <= 10)

where NumRow > 0

查询oracle metalink了解到这是oracle 11.2.0.1自身的一个Bug 9445675（文档 ID 1361107.1），与oracle数据库的绑定变量有关  

根据trc文件提示到的sql可知，sql语句确实使用了绑定变量，解决改问题的方法有3种：

1、取消数据库的绑定变量窥探：alter system set "_optim_peek_user_binds"=false;

注意取消绑定变量窥探对数据库有影响，会改变sql的执行计划

2、对数据库打补丁

PSU 11.1.0.7.8 Patch 12419384 includes Patch:9703463

For 11.1.0.7, Patch 9243912

Patch:9703463  can also be applied individually but requires PSU 11.1.0.7.6 

Patch:8625762  may also be applicable to databases version 11.1.0.7

Patch:18841764 fixes the SQL Loader issue that may affect database version 12.1.0.2

3、升级数据库到11.2.0.3及以上版本

由于项目功能使用紧急，临时取消数据库绑定变量，应用功能能够正常使用，事后再做oracle 11.2.0.1 to 11.2.0.4的升级。

本文出自 “清风艾艾” 博客，请务必保留此出处http://3596022.blog.51cto.com/3586022/1975897

oracle 11.2.0.1 for windows server2008r2告警ORA-03137

标签：oracle   windows   bug