ricky

158 积分	1 好友	8 主题

发消息

怎么追踪udump里面产生的trace文件来源？

1^#

发表于 2012-5-8 11:16:35 | 查看: 6683| 回复: 4

环境：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.0
System name: AIX
Node name:    P595C_ACCT_DB
Release:       3
Version:       5
Machine:       00CC2BF54C00
Instance name: acct

问题描述：
今天df检查/oracle的目录，发现使用率达到95%以上，昨天才80%不到，检查是哪里产生大量的文件。
一查发现是udump里面产生大量trc文件，每个文件差不多20m，产生文件的内容大致是相同的。
我查了trace文件里面的sql。发现如下：
******************************************************
----- PL/SQL Runtime State -----
PROCEDURE CRM_INTERFACE.P_QS_MOVEFIXPHONE:
library unit=700001017feeb98 line=22 opcode=116 static link=0 scope=0
FP=1103d40f8 PC=70000116739c25c Page=0 AP=1103d4010 ST=1103d4400
DL0=1103b5a98 GF=1103b5af0 DL1=1103b5ab8 DPF=1103b5ae0 DS=70000116739c2f0
DON library unit variable list instantiation
------ ------------ ------------- -------------

*** 2012-05-08 02:00:12.302
ksedmp: internal or fatal error
Current SQL statement for this session:
DELETE FROM STOP_OPEN_ORDER WHERE OPER_ID = :B2 AND ACC_NBR =:B1
----- PL/SQL Call Stack -----
  object    line  object
  handle number  name
700001017feeb98       22  procedure CRM_INTERFACE.P_QS_MOVEFIXPHONE
70000107de0d830       1  anonymous block
----- Call Stack Trace -----

请问我下一步怎么跟踪？这个sql是停开机（电信业务）的一个操作。

请给点方向和指点，下一步怎么分析啊？
trace文件太大，传不上来。

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

5^#

发表于 2012-5-8 15:21:18

ODM FINDING:

使用 stack call kdimod0=> kdiknf => ksedmp 在Mos 上可以找到相关Note:

Getting An Enromous Amount Of Trace Files Generated Showing A SQL Being Run And A Call Stack Trace.

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.5 and later [Release: 10.2 and later ]
Information in this document applies to any platform.
Symptoms
You could be getting a lot of traces generated, filling up the alert log as below:

Fri Sep 3 05:05:48 2010
Errors in file /u01/app/oracle/product/db10g/admin/orcl/udump/orcl_ora_15618.trc:
Fri Sep 3 05:05:52 2010
Errors in file /u01/app/oracle/product/db10g/admin/orcl/udump/orcl_ora_15618.trc:
Fri Sep 3 05:05:54 2010
Errors in file /u01/app/oracle/product/db10g/admin/orcl/udump/orcl_ora_15618.trc:
Fri Sep 3 05:05:55 2010
Errors in file /u01/app/oracle/product/db10g/admin/orcl/udump/orcl_ora_15653.trc:

And the trace itself has the following call stack trace:

ksedst <- ksedmp <- kdiknf <- kdimod0 <- kauupd <- updrow <- qerupRow <- Procedure <- qerupFetch <- updaul <- updThreePhaseExe <- 277 <- updexe <- opiexe <- opipls <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <- psddr0 <- psdnal <- pevm_EXECC <- pfrinstr_
<- EXECC <- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe <- opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv <- sou2o <- opimai_real <- main <- libc_start_main

The trace file will also show a part like the below:

*** SESSION ID:(113.1417) 2010-09-06 11:34:27.434
oer 8102.2 - obj# 60445, rdba: 0x01405e5e(afn 5, blk# 24158)
kdk key 8102.2:
ncol: 4, len: 22
key: (22):
Cause
The message 'oer 8102.2 - obj# 60445, rdba: 0x01405e5e(afn 5, blk# 24158)' in the trace file is indicating that the object with the ID 60445 is giving an ORA-08102 error, and that error is described as below:

OERR: ORA-8102 "index key not found, obj# %s, file %s, block %s (%s)"
Error: ORA-08102 (ORA-8102)
Text: index key not found, obj# %s, file %s, block %s (%s)
---------------------------------------------------------------------------
Cause: Internal error: possible inconsistency in index
Action: Send trace file to your customer support representative, along with information on reproducing the error
Solution
1. Find the object which ID is given in the error 'oer 8102.2 - obj# 60445, rdba: 0x01405e5e(afn 5, blk#
24158)' message from the trace file, in this case, the object with the ID 60445.
2. Drop and recreate this object.

NOTE: This issue could be similar to many other issues, but the key symptom here is the 'oer 8102.x' error given in the trace file. If you can't find that, then you are NOT hitting this issue, and please log an SR with Oracle Support for diagnosis.

说明STOP_OPEN_ORDER 上的索引index (obj# 5046310) 肯能存在 logical corruption 导致 DELETE时引发ORA-08102错误，进程做 stack call TRACE 消耗磁盘空间。

建议：

1. 对STOP_OPEN_ORDER 表和索引做 analyze .. validate structure cascade 确认是否存在逻辑讹误。

或者

2. 选择忽略该问题，设置 max_dump_file_size 限制TRACE文件的大小

SQL> alter system set max_dump_file_size=2000;

System altered.

但是该动态参数对已启动的Server process服务进程可能无效。

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-5-8 15:14:07

ODM　DATA:

oer 8102.2 - obj# 5046310, rdba: 0xc5404ed0
kdk key 8102.2:
  ncol: 2, len: 19
  key: (19):  0b 30 38 37 39 36 38 37 39 39 32 33 06 c5 82 a2 29 00 06
  mask: (4096):

Current SQL statement for this session:
DELETE FROM STOP_OPEN_ORDER WHERE OPER_ID = :B2 AND ACC_NBR =:B1
----- PL/SQL Call Stack -----
  object    line  object
  handle number  name
700001017feeb98       22  procedure CRM_INTERFACE.P_QS_MOVEFIXPHONE
70000107de0d830       1  anonymous block
----- Call Stack Trace -----
calling             call    entry             argument values in hex
location          type    point             (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148       bl    ksedst             1028CFE8C ?
kdiknf+00f8       bl    ksedmp             302A067A4 ?
kdimod0+0b50       bl    kdiknf             C5C0107C00000000 ?
                                                70000116C777614 ?
                                                FFFFFFFFFFF27A0 ?
                                                FFFFFFFFFFF1608 ?
                                                FFFFFFFFFFF1470 ?
kaudel+0b2c       bl    kdimod0             70000116C777610 ? 102992AB0 ?
                                                0103F1734 ? 000000000 ?
                                                200101EC85320 ?
                                                FFFFFFFFFF28B8 ?
                                                100000000000000 ? 000000000 ?
delrow+0ce8       bl    kaudel             11039EA50 ? 70000116C777590 ?
                                                11039DFA0 ? 70000116C776C98 ?
                                                1400011039F418 ?
                                                4AD3161039FC10 ?
qerdlFetch+027c    bl    delrow             70000116C7770C0 ?
                                                7FFF00000000 ?

stack call => qerdlFetch => delrow => kaudel => kdimod0=> kdiknf => ksedmp 生成TRACE

rdba: 0xc5404ed0 指向的 block buffer  是一个 index block  (789/20176)

         SO: 700000fff9cc448, type: 24, owner: 700001056027330, flag: INIT/-/-/0x00
         (buffer) PR: 0x700001000a25a30 FLG: 0x0
         lock rls: 0x0, class bit: 0x2
         kcbbfbp: [BH: 0x700000914fe7b00, LINK: 0x700000fff9cc488]
         where: kdiwh22: kdifind, why: 0
      buffer tsn: 47 rdba: 0xc5404ed0 (789/20176)
      scn: 0x0b95.0ddd16cf seq: 0x01 flg: 0x02 tail: 0x16cf0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump:  0xc5404ed0
Object id on Block? Y
seg/obj: 0x1505a54  csc: 0xb95.ddd16cb  itc: 90  flg: E  typ: 2 - INDEX
   brn: 0  bdba: 0xc5404e8a ver: 0x01
   inc: 0  exflg: 0

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0x0003.003.04f0e0db  0xb501368f.7738.01  CB-- 0  scn 0x0b8a.80910f18
0x02 0x0018.017.00efaf8e  0xb2808fdf.16df.0c  C--- 0  scn 0x0b94.7ba2d68b
...............................

Leaf block dump
===============
header address 504403197269076132=0x700000914cb68a4
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 64
kdxcofbo 164=0xa4
kdxcofeo 1930=0x78a
kdxcoavs 4412
kdxlespl 0
kdxlende 1
kdxlenxt 3309345040=0xc5409910
kdxleprv 3296744541=0xc480545d
kdxledsz 0
kdxlebksz 5920

回复只看该作者道具举报