ORA-600(15214)错误--No current SQL statement being executed.

ricky

环境：Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/9.2
System name:    AIX
Node name:      P595A_ACCOUNT_DB
Release:        3
Version:        5
Machine:        00C566624C00
Instance name: bill


alert日志信息：
  Current log# 3 seq# 320889 mem# 1: /dev/rlv_bi_redo_06
Thu Jun 14 18:02:43 2012
Thread 1 advanced to log sequence 320890
  Current log# 1 seq# 320890 mem# 0: /dev/rlv_bi_redo_01
  Current log# 1 seq# 320890 mem# 1: /dev/rlv_bi_redo_02
Thu Jun 14 18:06:29 2012
Thread 1 advanced to log sequence 320891
  Current log# 2 seq# 320891 mem# 0: /dev/rlv_bi_redo_03
  Current log# 2 seq# 320891 mem# 1: /dev/rlv_bi_redo_04
Thu Jun 14 18:11:25 2012
Thread 1 advanced to log sequence 320892
  Current log# 4 seq# 320892 mem# 0: /dev/rlv_bi_redo_07
  Current log# 4 seq# 320892 mem# 1: /dev/rlv_bi_redo_08
Thu Jun 14 18:22:34 2012
ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/bill/udump/bill_ora_512196.trc.
Thu Jun 14 18:22:39 2012
ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/bill/udump/bill_ora_1889652.trc.
Thu Jun 14 18:23:34 2012
ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/bill/udump/bill_ora_943438.trc.
Thu Jun 14 18:23:53 2012
Thread 1 advanced to log sequence 320893
  Current log# 5 seq# 320893 mem# 0: /dev/rlv_bi_redo_09
  Current log# 5 seq# 320893 mem# 1: /dev/rlv_bi_redo_10
Thu Jun 14 18:24:22 2012
ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/bill/udump/bill_ora_1889652.trc.
Thu Jun 14 18:24:34 2012
ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/bill/udump/bill_ora_1889652.trc.
Thu Jun 14 18:27:33 2012
Errors in file /oracle/app/oracle/admin/bill/udump/bill_ora_746806.trc:
ORA-00600: internal error code, arguments: [15214], [0], [554], [], [], [], [], []
Thu Jun 14 18:59:55 2012
Thread 1 advanced to log sequence 320894
  Current log# 3 seq# 320894 mem# 0: /dev/rlv_bi_redo_05
  Current log# 3 seq# 320894 mem# 1: /dev/rlv_bi_redo_06


trace.rar (1.39 MB, 下载次数: 1230)

2012-6-15 16:03 上传

点击文件名下载附件

在今天的日常检查中发现这个600错误，百度之后发现yangtingkun空间记载的是并行引起的。
http://space.itpub.net/4227/viewspace-716866
我的没有当前sql，版本也不对，想知道为什么引起的啊？

[ 本帖最后由 ricky 于 2012-6-15 16:05 编辑 ]

yshappy

学习中，学习中。。。。。。

ricky

谢谢刘大，分析的很好，我也学着你读读trace文件，我先试着调优一下sql试试

Maclean Liu(刘相兵

9.2.0.6.0 +  AIX

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15214], [0], [554], [], [], [], [], []
No current SQL statement being executed.
----- Call Stack Trace -----

kpoal8=> kpooprx => opiosq0=> kkspsc0=> kksfbc=> 报错


15200        dict/shrdcurs        used to in creating sharable context area associated with shared cursors



===================================================
Files currently opened by this process:
===================================================
PROCESS STATE
-------------
Process global information:
     process: 70000052f4eec98, call: 70000054e908418, xact: 0, curses: 7000005314c4988, usrses: 7000005314c4988
  ----------------------------------------
  SO: 70000052f4eec98, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=162, calls cur/top: 70000054e908418/70000054e908418, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 197 0 4
              last post received-location: kslpsr
              last process to post me: 70000052f4beb70 1 6
              last post sent: 504403180586499624 98 16
              last post sent-location: ksasnd
              last process posted by me: 70000052f4beb70 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000005344bbb50

    SO: 70000054e908418, type: 3, owner: 70000052f4eec98, flag: INIT/-/-/0x00
    (call) sess: cur 7000005314c4988, rec 0, usr 7000005314c4988; depth: 0
      ----------------------------------------
      SO: 7000005acaaee48, type: 52, owner: 70000054e908418, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=7000005acaaee48 handle=70000059890cac0 mode=S lock=7000005ad6d1f48
      user=7000005314c4988 session=7000005314c4988 count=1 mask=0001 savepoint=327619 flags=[00]



这个进程的 current call  只想 library object pin



这个pin 指向 一句 SQL ， 这个SQL parent cursor 拥有559个子游标

    SO: 7000005ad6d1f48, type: 51, owner: 7000005314c4988, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7000005ad6d1f48 handle=70000059890cac0 mode=N
      call pin=7000005acaaee48 session pin=0
      htl=7000005ad6d1fb8[70000057e7d2b00,7000005780ebe80] htb=7000005780ebe80
      user=7000005314c4988 session=7000005314c4988 count=1 flags=PNC/[04] savepoint=327618
      LIBRARY OBJECT HANDLE: handle=70000059890cac0
      name= DELETE from B_RT_STOP_DETAIL WHERE ID=:"SYS_B_0" AND TYPE=:"SYS_B_1" AND STOP_STEP_ID<=:"SYS_B_2"
      hash=51589a2b timestamp=06-14-2012 18:27:28
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=S latch#=63
      lwt=70000059890caf0[70000059890caf0,70000059890caf0] ltm=70000059890cb00[70000059890cb00,70000059890cb00]
      pwt=70000059890cb20[70000059890cb20,70000059890cb20] ptm=70000059890cbb0[70000059890cbb0,70000059890cbb0]
      ref=70000059890cad0[70000059890cad0, 70000059890cad0] lnd=70000059890cbc8[70000059890cbc8,70000059890cbc8]
        LIBRARY OBJECT: object=7000005b4654c00
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        CHILDREN: size=560
        child#    table reference   handle
        ------ -------- --------- --------

            0 7000005b4654e60 70000059fba8838 700000598feb0f8
......................
           557 70000059f23cc78 70000056e4e6de0 70000058aa3b478
           558 70000059f23cc78 70000056e4e6e48 700000575c6c1c0
           559 70000059f23cc78 70000056e4e6eb0 7000005758bcca0
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 70000059f6487b0 7000005b4654cf8 I/P/A     0 NONE  



过多的child cursor (version count)可能是引发该BUG 的主要原因



ORA-600 [15214]

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.7 to 10.2.0.4
This problem can occur on any platform.
Symptoms

You are seeing intermittent ORA-600[15214] from your applications.

Stack function including kksfbc.

Cause

Bug 6404447 (80) ORA-600 [15214] ERROR OCCURED
Fixed In Ver: 11g

REDISCOVERY INFORMATION:
If you are seeing an Ora-600[15214] and you have not got this fix applied then you are probably
seeing the problem.



Solution


Please download and apply one-off patch for Bug 6404447 from PATCH 6404447.



Advice


1.  apply  patch 6404447

2. 调优SQL 减少version count