2017年12月份第二次oracle数据库巡检中,发现某一地市oracle数据库发现SQL语句触发特定版本BUG,详细信息如下:
操作系统版本:windows server 2008R2
数据库版本:oracle 11.2.0.1
问题描述:2017年12月份第二次巡检中,发现告警日志报错,报错信息如下:
19/12/2017 08:27:35 Tue Dec 19 08:27:35 2017
ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] []
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_5480.trc (incident=36699):
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_36699\orcl_ora_5480_i36699.trc
18/12/2017 17:19:56 Mon Dec 18 17:19:56 2017
ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_36554\orcl_ora_4572_i36554.trc
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_4572.trc (incident=36554):
18/12/2017 16:18:58 ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] [] [] [] []
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3968.trc (incident=36547):
Mon Dec 18 16:18:58 2017
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_36547\orcl_ora_3968_i36547.trc
根据orcl_ora_5480_i36699.trc文件发现触发ORA-03137的应用SQL语句确实使用绑定变量:
-----sql_id=cjx2sya2mu4zm
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
and bill_no = :ParamBillNo3
order by month desc, sid)) row_
where rownum <= 10)
where NumRow > 0 ;
针对ORA03137与oracle 11.2.0.1 for windows server 2008R2查看oracle metalink,ORA-03137: TTC 协议内部错误: [12333] [6] [50] [48] 与应用SQL语句使用绑定变量有关,
是非公共Bug:9703463(文档 ID 1615363.1):
解决办法:
1、解决Oracle 11.2.0.1 因绑定变量触发ora-03137错误的补丁已包含在PSU补丁集Patch:10245351中,需要对数据库应用补丁集Patch:10245351
风险:oracle数据库应用补丁集可能引入新的未知BUG
2、关闭oracle 11.2.0.1绑定变量功能:alter system set "_optim_peek_user_binds"=false;
风险:将导致数据库不稳定,引起应用sql语句执行计划不准确
3、将数据库版本升级到11.2.0.3以上版本可解决ORA-03137问题