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, p_w_picpath: 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=905vd7vnpuh7n) -----
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的升级。
