使用 IPS 打包诊断信息诊断ORA-7445/ORA-600

环境描述：

操作系统： WINDOWS 2008

数据库： ORACLE DATABASE 11.2.0.4

接到客户电话说数据库无法访问，通过 VPN登录操作系统，发现数据库因为 ORA-600错误已经崩溃。

先启动数据库让客户能正常使用，然后使用 IPS 打包诊断信息进行故障诊断。

数据库告警日志错误信息如下：

Fri Dec 18 16:14:13 2015

Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1428D2D0D, opiaba()+655]

ERROR: Unable to normalize symbol name for the following short stack (at offset 213):

dbgexProcessError()+200<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+2269<-dbkePostKGE_kgsf()+77<-kgeade()+562<-kgerelv()+151<-kgerev()+45<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1862<-sss_xcpt_EvalFilter()+174<-.1.6_8+59<-00000000775E7FA8<-00000000775F91AD<-00000000775E8BAF<-000000007761DB38<-opiaba()+655<-opiprs()+334<-PGOSF522_kksParseChildCursor()+790<-rpiswu2()+3039<-kksLoadChild()+6485<-kxsGetRuntimeLock()+2547<-kksfbc()+17080<-kkspsc0()+2307<-kksParseCursor()+152<-opiosq0()+2392<-kpooprx()+365<-kpoal8()+985<-opiodr()+1631<-ttcpip()+1462<-opitsk()+2201<-opiino()+1246<-opiodr()+1631<-opidrv()+861<-sou2o()+98<-opimai_real()+158<-opimai()+191<-OracleThreadStart()+724<-00000000774C59CD<-00000000775FB981

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\zswd_ora_12204.trc (incident=12441):

ORA-07445: 鍑虹幇寮傚父閿欒?: 鏍稿績杞?偍 [opiaba()+655] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1428D2D0D] [UNABLE_TO_READ] []

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\incident\incdir_12441\zswd_ora_12204_i12441.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Fri Dec 18 16:14:22 2015

Dumping diagnostic data in directory=[cdmp_20151218161422], requested by (instance=1, osid=12204), summary=[incident=12441].

Fri Dec 18 16:14:25 2015

Sweep [inc][12441]: completed

Sweep [inc2][12441]: completed

Fri Dec 18 16:15:00 2015

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\zswd_pmon_1668.trc (incident=12017):

ORA-00600: internal error code, arguments: [17147], [0x1D65A9588], [], [], [], [], [], [], [], [], [], []

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\incident\incdir_12017\zswd_pmon_1668_i12017.trc

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Fri Dec 18 16:15:12 2015

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\zswd_pmon_1668.trc:

ORA-00600: internal error code, arguments: [17147], [0x1D65A9588], [], [], [], [], [], [], [], [], [], []

PMON (ospid: 1668): terminating the instance due to error 472

先出现ORA-7445错误，接着马上就出现了ORA-600错误导致PMON后台进程崩溃，ORA-600错误应该是ORA-7445引发的。

接下来我们先用IPS打包ORA-7445的诊断信息。

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\zswd_ora_12204.trc (incident=12441):

ORA-07445: 鍑虹幇寮傚父閿欒?: 鏍稿績杞?偍 [opiaba()+655] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1428D2D0D] [UNABLE_TO_READ] []

标红的incident=12441就是ORA-7445 的事件号。

Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\zswd_pmon_1668.trc (incident=12017):

ORA-00600: internal error code, arguments: [17147], [0x1D65A9588], [], [], [], [], [], [], [], [], [], []

Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\incident\incdir_12017\zswd_pmon_1668_i12017.trc

标红的incident=12017就是ORA-600 的事件号，打开CMD在命令行进入ORACLE_HOME/bin目录启动 adrci。

打包ORA-7445的诊断信息：

adrci> ips pack incident 12441 in d:/

打包ORA-600的诊断信息：

adrci> ips pack incident 12017 in d:/

打包好的压缩包：

解压ORA600171_20151219124622_COM_1.zip，进入

ORA600171_20151219124622_COM_1\diag\rdbms\zswd\zswd\incpkg\pkg_2\seq_1 目录，在浏览器中打开manifest_2_1.html。

Main problems --主要问题

Problem ID	Problem key	Incidents included	Incidents total
2	ORA 600 [17147]	1	1

Correlated problems --相关问题

Problem ID	Problem key	Incidents included	Incidents total
1	ORA 7445 [opiaba()+655]	1	1

Main incidents --主要事件

Incident ID	Problem ID	Error Message	Incident time
12017	2	ORA-600 [17147] [0x1D65A9588]	2015-12-18 16:15:00.184000 +08:00

Correlated incidents --相关事件

Incident ID	Problem ID	Error Message	Incident time
12441	1	ORA-7445 [opiaba()+655] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1428D2D0D] [UNABLE_TO_READ]	2015-12-18 16:14:14.861000 +08:00

从这里也证实了我的猜想，ORA-600是ORA-7445触发的。接下来我们先分析ORA-7445的TRACE 文件。

打开ORA-7445的TRACE文件ORA600171_20151219124622_COM_1\diag\rdbms\zswd

\zswd\incident\incdir_12441\zswd_ora_12204_i12441.trc。

Dump continued from file: D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\zswd_ora_12204.trc

ORA-07445: 出现异常错误: 核心转储 [opiaba()+655] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1428D2D0D] [UNABLE_TO_READ] []

========= Dump for incident 12441 (ORA 7445 [opiaba()+655]) ========

----- Beginning of Customized Incident Dump(s) -----

Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x1428D2D0D, opiaba()+655]

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Process Id: 0x000005b4 Thread Id : 0x00002fac Time : Fri Dec 18 16:14:15

Excp. Code: 0xc0000005 Excp. Type: ACCESS_VIO Flags: 0x00000000

------------------- Registers ----------------------------

ip=00000001428D2D0D sp=00000000398B5730 rp=00000001C9415108

r1=00000002DBE7EDD8 r2=0000000000000000 r3=0000000000000000

r4=00000001C7197188 r5=00000000398B5730 r6=00000001C9415108 r7=00000001B176AD20

r8=0000000000000000 r9=0000000000000005 r10=0000000000003200 r11=000000000002F136

r12=0000000000000000 r13=0000000000000000 r14=00000001C7197150 r15=0200000002DBE7ED

------------------- End of Registers ---------------------

*** 2015-12-18 16:14:15.054

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=bwr8acf2c0wcq) -----

BEGIN INSERT INTO T_INFO (INDEXID,PERNR,ITCODE,NACHN,ORGEH,ORGEH_TEXT,PLANS_TEXT,EMSEX,GBDAT,TELNO,MOBILE,EMAIL,ACT_FLAG,REMARK,MIDNM, PERSA,PERSA_TEXT,BTRTL,BEXT,OFFICE_LOCATION) VALUES (:1,:2,:3,:4,:5,:6,:7, :8,:9,:10,:11,:12,:13,:14, :15,:16,:17,:18,:19,:20) ;

.........

--这样的 INSERT INO 语句有几千条，总共使用了将近一万一千个绑定变量

END;

到metalinke使用ORA-7445/ORA-600 Error Look-up Tool 搜索

打开文档 737378.1

ORA-7445 [opiaba] (文档 ID 737378.1)

Note: For additional ORA-7445 related information please read Note:211909.1

PURPOSE:

This article provides information on one or more bugs that are

known to result in the given ORA-7445 error.

SUGGESTIONS:

If the Known Issues section below does not help in terms of identifying

a solution, please submit the trace files and alert.log to Oracle

Support Services for further analysis.

Known Issues:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:

The list below is restricted to show only bugs believed to affect version 11.2.0.4.
Other bugs may affect this version but have not been confirmed as being relevant yet.

There is 1 bug listed.

NB	Bug	Fixed	Description
	12578873	11.2.0.4.BP15, 12.1.0.2	ORA-7445 [opiaba] when using more than 65535 bind variables

· '*' indicates that an alert exists for that issue.

· '+' indicates a particularly notable issue / bug.

· See Note:1944526.1 for details of other symbols used

绑定变量超过 655535 字节触发了 ORA-7445。

接下来我们分析ORA-600错误，打开ORA-600的TRACE文件ORA600171_20151219124622_COM_1\diag\rdbms\zswd\zswd\incident\incdir_12017\zswd_pmon_1668_i12017.trc

Dump continued from file: D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\trace\

zswd_pmon_1668.trc >>>> 此处包含另一个TRACE文件

ORA-00600: internal error code, arguments: [17147], [0x1D65A9588], [], [], [], [], [], [], [], [], [], []

打开 ORA600171_20151219124622_COM_1\diag\rdbms\zswd\zswd\trace\zswd_pmon_

1668.trc

*** 2015-12-18 16:15:00.281

Incident 12017 created, dump file: D:\APP\ADMINISTRATOR\diag\rdbms\zswd\zswd\incident\incdir_12017\zswd_pmon_1668_i12017.trc

ORA-00600: internal error code, arguments: [17147], [0x1D65A9588], [], [], [], [], [], [], [], [], [], []

PMON: fatal error while deleting s.o. 0000000214EBE1C8 in this tree:

----------------------------------------

SO: 0x0000000214EBE560, type: 78, owner: 0x00000002FD845CE0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3

proc=0x00000002FD543A60, name=LIBRARY OBJECT LOCK, file=kgl.h LINE:8751, pg=0

LibraryObjectLock: Address=0000000214EBE560 Handle=00000002124160D0 Mode=N CanBeBrokenCount=1 Incarnation=1 ExecutionCount=0

User=00000002FD845CE0 Session=00000002FD845CE0 ReferenceCount=1 Flags=CNB/[0001] SavepointNum=5673bebf

LibraryHandle: Address=00000002124160D0 Hash=84c07196 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD

ObjectName: Name=BEGIN INSERT INTO T_INFO (INDEXID,PERNR,ITCODE,NACHN,ORGEH,ORGEH_TEXT,PLANS_TEXT,EMSEX,GBDAT,TELNO,MOBILE,EMAIL,ACT_FLAG,REMARK,MIDNM, PERSA,PERSA_TEXT,BTRTL,BEXT,OFFICE_LOCATION) VALUES (:1,:2,:3,:4,:5,:6,:7, :8,:9,:10,:11,:12,:13,:14, :15,:16,:17,:18,:19,:20) ; INSERT INTO T_INFO (INDEXID,PERNR,ITCODE,NACHN,ORGEH,ORGEH_TEXT,PLANS_TEXT,EMSEX,GBDAT,TELNO,MOBILE,EMAIL,ACT_FLAG,REMARK,MIDNM, PERSA,PERSA_TEXT,BTRTL,BEXT,OFFICE_LOCATION) VALUES

FullHashValue=8a21c22a581d33fabe5d0a6384c07196 Namespace=SQL AREA(00) Type=CURSOR(00) Identifier=2227204502 OwnerIdn=84

Statistics: InvalidationCount=0 ExecutionCount=0 LoadCount=2 ActiveLocks=1 TotalLockCount=2 TotalPinCount=1

Counters: BrokenCount=1 RevocablePointer=1 KeepDependency=1 Version=0 BucketInUse=0 HandleInUse=0 HandleReferenceCount=0

Concurrency: DependencyMutex=0000000212416180(0, 1, 0, 0) Mutex=0000000212416210(0, 22, 0, 0)

Flags=RON/PIN/TIM/PN0/DBN/[10012841]

WaitersLists:

Lock=0000000212416160[0000000212416160,0000000212416160]

Pin=0000000212416140[0000000212416140,0000000212416140]

LoadLock=00000002124161B8[00000002124161B8,00000002124161B8]

Timestamp: Current=12-18-2015 16:07:27

HandleReference: Address=0000000212416480 Handle=0000000000000000 Flags=[00]

LibraryObject: Address=00000001C71970B0 HeapMask=0000-0001-0001-0000 Flags=EXS[0000] Flags2=[0000] PublicFlags=[0000]

ChildTable: size='16'

Child: id='0' Table=00000001C7197F60 Reference=00000001C7197998 Handle=00000002FC8CDA80

NamespaceDump:

Parent Cursor: sql_id=bwr8acf2c0wcq parent=00000001C7197150 maxchild=1 plk=y ppn=n

表红的部分就是触发ORA-7445错误的SQL，由此可以判定该ORA-600也是该SQL引发的。

到metalinke使用ORA-7445/ORA-600 Error Look-up Tool 搜索

    打开文档 138580.1。 ORA-600 [17147] (文档 ID 138580.1)

Note: For additional ORA-600 related information please read Note:146580.1

PURPOSE:

  This article discusses the internal error "ORA-600 [17147]", what

  it means and possible actions. The information here is only applicable

  to the versions listed and is provided only for guidance.

ERROR:

Format: ORA-600 [17147] [a]

VERSIONS:

  versions 7.0 and above

DESCRIPTION:

  Oracle has detected that the magic number in a memory chunk header has

  been overwritten.

  This is a heap (in memory) corruption and there is no underlying data

  corruption.

  The error may occur in the one of the process specific heaps

  (the Call heap, PGA heap, or session heap) or in the shared heap (SGA).

ARGUMENTS:

  Arg [a] Pointer to chunk header

FUNCTIONALITY:

  HEAP MANAGEMENT

IMPACT:

  PROCESS FAILURE

  MEMORY CORRUPTION, POSSIBLE INSTANCE FAILURE.

  NON DATA CORRUPTIVE - No underlying data corruption.

SUGGESTIONS:

  Many are one time occurrances.

  Please see if the problem is reproducable.

  Have the hardware vendor check all system memory for possible errors.

  For repeatable occurrances, please submit the trace

  files and alert.log to Oracle Support Services for further analysis.

    该文档的大概意思是，进程的堆内存（调用堆，PGA堆或会话堆）或者SGA内存数据损坏，会导致进程故障或实例故障。