Shulou(Shulou.com)06/01 Report--

On October 20, 2017, the oracle 11.2.0.1 medical insurance related application in Guizhou City failed to run because of abnormal functions. The following error occurred when viewing the database alarm log:

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: internal error of TTC protocol: [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]

View the trc file prompted by the alarm log:

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 purl 40.170

* SESSION ID: (515.23643) 2017-10-20 16-12 purl 40.170

* CLIENT ID: () 2017-10-20 16 purl 1240.170

* SERVICE NAME: (orcl) 2017-10-20 16-12 purl 40.170

* MODULE NAME: () 2017-10-20 16 purl 1240.170

* ACTION NAME: () 2017-10-20 16 purl 1240.170

Dump continued from file: e:\ app\ administrator\ diag\ rdbms\ orcl\ orcl\ trace\ orcl_ora_4756.trc

ORA-03137: internal error of TTC protocol: [12333] [6] [50] [48] []

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

* 2017-10-20 16 12 purl 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), 0meme 1) as BUSINESS_TYPE

REFEEDBACK_REASON_DETAIL

(select sum (a.reject_money) from dw_opinion_details b join dw_billdetail an 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 0

Query oracle metalink to learn that this is a Bug 9445675 of oracle 11.2.0.1 itself (document ID 1361107.1), which is related to the binding variables of the oracle database.

According to the sql prompted by the trc file, the sql statement does use binding variables, and there are three ways to solve the problem:

1. Unbind the database to peep: alter system set "_ optim_peek_user_binds" = false

Note that unbinding variable snooping has an impact on the database, which will change the execution plan of sql

2. Patch the database

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. Upgrade the database to version 11.2.0.3 or above

Due to the urgent use of the project function, temporarily cancel the database binding variables, the application function can be used normally, and then upgrade oracle 11.2.0.1 to 11.2.0.4 afterwards.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.