使用ErrorStack进行错误跟踪及诊断
在使用
是 提供的一种对于错误堆栈进行跟踪的方法，通过设置跟踪可以将一些错误的后台信息详尽地转储出来，写入跟踪文件，对于错误的研究与诊断非常有效。
ErrorStack 4 0 1 2 Level1 + ProcessState
（显示所有 ，着重显示当前 ）
可以在实例级或会话级别设置，也可以在参数文件中设置，这个设置仅当某个特定的错误出现时才被触发，如设置 事件的跟踪：
一个客户曾经出现如下 错误，提示数据的精度超过允许值，是后台 调度的任务：
Errors in file /admin/erpdb/bdump/erpdb1_j000_447020.trc: ORA-01438: value larger than specified precision allowed for this column ORA-06512: at line 1
SQL ErrorStack SQL alter system set events='1438 trace name errorstack forever,level 3';
alter system set events='1438 trace name errorstack off';
Oracle 10g Mon Jul 13 10:48:392009 Mon Jul 13 10:56:06 2009 ORA-01438: value larger than specified precision allowed for this column Trace dumping is performing id=[cdmp_20090713105608] OS Pid: 541528 executed alter system set events '1438 trace name Errorstackoff'
SQL SQL SQL Number ***SESSION ID:(857.16304) 2009-07-13 10:56:06.429 ksedmp: internal or fatal error Current SQL statement for this session: ----- PL/SQL Call Stack ----- handle number name 700000336a1a070 236 procedure ERP.PROC_AUTOBATPROC 700000342eb7c20 1 anonymous block
可以很容易地测试这一功能的使用，比如使用如下代码中的测试过程：
SQL> connect eygle/eygle Table created. insert into t values(a,1) ERROR at line 1: SQL> alter system set events '984 trace name errorstack off';
Mon Jul 13 22:55:592009 Mon Jul 13 22:59:12 2009 ORA-00984: column not allowed here OS Pid: 2431 executed alter system set events '984 trace name errorstack off'
insert *** 2009-07-13 22:59:12.928 ORA-00984: column not allowed here insert into t values(a,1) calling call entry argument values in hex -------------------- -------- -------------------- ----------------------------
以下引用在ITPUB上的一个讨论，是使用ErrorStack跟踪解决问题的典型案例（原文链接指向ITPUB）
Import: Release 10.2.0.1.0 - Production on Tue Mar 18 14:19:49 2008
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
Export file created by EXPORT:V10.02.01 via conventional path
. importing DBA_MGR's objects into DBA_MGR
IMP-00058: ORACLE error 1438 encountered
IMP-00028: partial import of previous table rolled back: 20501 rows rolled back
System altered
然后重新执行IMP；
2、查看UDUMP下日志,如下:
/opt/oracle/admin/orcl/udump/orcl_ora_32355.trcWith the Partitioning, OLAP and Data Mining options
System name:Linux
Release:2.6.9-22.ELsmp
Machine:x86_64
Redo thread mounted by this instance: 1
Unix process pid: 32355, image: oracle@zhx25.cccc.com (TNS V1-V3)
*** SESSION ID135.949) 2008-03-18 17:11:02.014
ksedmp: internal or fatal error
Current SQL statement for this session:
("PRTNO", "SEQNO", "SEQDESC", "PRTEFLAG", "SEQCC", "SEQNXTNO", "SEQDEPT", "WKCCODE", "WKCALTCODE", "SEQSETHR", "LAB_SEQSETHR", "SEQRUNHR", "LAB_SEQRUNHR", "SEQQUEHR", "LAB_SEQQUEHR", "SEQTRANHR", "LAB_SEQTRANHR", "SEQNOQLTRAT", "SEQCHGDAT", "SEQCHGRSN", "SEQCHGCTLR", "MACHCODE", "MACHALTCODE", "RESCODE", "KEYSEQ", "SEQCRYLOT", "INITOVERRATE", "COMWORK", "SEQLOTFLAG", "SEQEFDAT", "SEQIEFDAT", "SEQEFLOTNO", "SEQIEFLOTNO", "SEQECONO", "PRTSEQCST", "SEQSELFCST", "INVYORN", "SEQQTY", "SEQNOTE", "NOPRTAVELZ", "OUTSEQFLAG", "WXCST", "SEQGSCHGCTLR", "SEQGSCHGDAT", "SEQSHCTL", "SEQSHDAT", "SEQSHSIGN", "PRICE", "ACCNO", "ROUSTAT", "FIXMFLAG", "BANCL" (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52)
calling
entry
location
point
…………
Offsi = 48, Offsi = 1440
Bind Twotask Scalar Sql In (may be out) Copy
Offsi = 48, Offsi = 1472
Bind Twotask Scalar Sql In (may be out) Copy
Offsi = 48, Offsi = 1504
Bind Twotask Scalar Sql In (may be out) Copy
Offsi = 48, Offsi = 1536
Bind Twotask Scalar Sql In (may be out) Copy
Offsi = 48, Offsi = 1568
Bind Twotask Scalar Sql In (may be out) Copy
Offsi = 48, Offsi = 1600
Bind Twotask Scalar Sql In (may be out) Copy
Offsi = 48, Offsi = 1632
Bind#0
oacflg=03 fl2=1000010 frm=01 csi=852 siz=3344 off=0
bln=32
flg=05
<span "="" style="margin: 0px; padding: 0px; max-width: 100%; box-sizing: border-box !important;">value="?
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
kxsbbbfp=2a974dc3b8
value=###
Dump of memory from 0x0000002A974CDE88 to 0x0000002A974CDE9E
2A974CDE90 C9BBC6C8 52024D01 30310248 00003304[.....M.RH.10.3..]
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
kxsbbbfp=2a974dc3d0
avl=00
Bind#3
oacflg=03 fl2=1000010 frm=01 csi=852 siz=0 off=88
bln=32
flg=01
…………
从跟踪文件可以看到，问题是系统认为地址为2A974CDE88的数值非法，在导入会提示ORA-1438错误。 。
3、找到DMP中相关数据
用winhex打开dmp文件，搜索十六进制串“2E313133 02C10604 C8C6BBC9 014D0252 48023130 0433F3FF”（对trace文件中的值要进行顺序调整）
5、对数据库相关数据项的类型进行调整
把数据项的类型由number(*)改为number(38)；
6、重新进行数据导入
[oracle@zhx25 ~]$ imp system/ceshi fromuser=dba_mgr touser=dba_mgr tables=t_routdef ignore=y file=1.dmp
Copyright (c) 1982, 2005, Oracle. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
Export file created by EXPORT:V10.02.01 via conventional path
. importing DBA_MGR's objects into DBA_MGR
"T_ROUTDEF"
本文通过介绍ErrorStack的应用，将这一常用的诊断工具介绍给大家，作为DBA，在遇到复杂问题时，通过这些手段深入分析，不仅有助于问题的解决，还将完善自己的知识体系，祝大家学习能够不断进步、深入。
本文是转载文章
