oracle 11.2.0.1告警日志报错ORA-03137与绑定变量窥探BUG9703463

发布时间:2020-08-10 21:35:00 作者:清风艾艾
来源:ITPUB博客 阅读:198
    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):
oracle 11.2.0.1告警日志报错ORA-03137与绑定变量窥探BUG9703463
解决办法:
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问题
推荐阅读:
  1. oracle for windows 监听问题之TNS-12545
  2. oracle 11.2.0.1 for windows server2008r2告警ORA-03137

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

11.2.0.1 变量 绑定

上一篇:Linux下安装Mysql

下一篇:Linux安装MySQL8.0

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》