« 《Oracle数据库性能优化》书稿已经交付出版社 | Blog首页 | 使用toupper/tolower函数进行大小写转换 »
使用ERRORSTACK进行错误跟踪及诊断
作者：eygle |【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】链接：http://www.eygle.com/archives/2005/03/eoaerrorstackoe.html
Oracle提供接口用于诊断Oracle的错误信息。
诊断事件可以在Session级设置，也可以在系统级设置，通常如果要诊断全局错误，最好在系统级设置，以下是一个测试例子，所选事件只以示范为目的:
SQL> alter system set event='984 trace name ERRORSTACK level 10' scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 101782828 bytes
Fixed Size 451884 bytes
Variable Size 37748736 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> create table t (name varchar2(10),id number);
Table created.
SQL> insert into t values(a,1);
insert into t values(a,1)
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> !
|
此时的984错误将会被跟踪，记录到跟踪文件中。
检查udump目录，找到trace文件:
[oracle@jumper oracle]$ cd $admin
[oracle@jumper udump]$ ls -sort
total 1020
4 -rw-r--r-- 1 oracle 533 Mar 2 16:06 t.sql
4 -rw-r--r-- 1 oracle 522 Mar 3 09:44 d.sql
20 -rw-r--r-- 1 oracle 17445 Mar 8 11:06 a.log
4 -rw-r----- 1 oracle 3254 Mar 14 23:15 conner_ora_30683.trc
4 -rw-r----- 1 oracle 1645 Mar 14 23:15 conner_ora_30701.trc
4 -rw-r----- 1 oracle 1638 Mar 14 23:16 conner_ora_30719.trc
4 -rw-r----- 1 oracle 1645 Mar 16 09:05 conner_ora_18565.trc
976 -rw-r----- 1 oracle 993555 Mar 16 09:06 conner_ora_18589.trc
[oracle@jumper udump]$ vi conner_ora_18589.trc
/opt/oracle/admin/conner/udump/conner_ora_18589.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0
System name: Linux
Node name: jumper.hurray.com.cn
Release: 2.4.21-15.EL
Version: #1 Thu Apr 22 00:27:41 EDT 2004
Machine: i686
Instance name: conner
Redo thread mounted by this instance: 1
Oracle process number: 10
Unix process pid: 18589, image: oracle@jumper.hurray.com.cn (TNS V1-V3)
*** 2005-03-16 09:06:56.178
ksedmp: internal or fatal error
ORA-00984: column not allowed here
Current SQL statement for this session:
insert into t values(a,1)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+269 call ksedst()+0 0 ? 0 ? 0 ? 0 ? 922C89F ?
AA642A0 ?
ksddoa()+446 call ksedmp()+0 A ? AABDCA8 ? B70100B0 ?
3D8 ? 1 ? B7010114 ?
ksdpcg()+521 call ksddoa()+0 B70100B0 ? AABDCA8 ?
ksdpec()+220 call ksdpcg()+0 3D8 ? BFFF3D20 ? 1 ?
ksfpec()+133 call ksdpec()+0 3D8 ? 3D8 ? AABAE7C ?
BFFF3D54 ? 9835E89 ?
AA642A0 ?
[oracle@jumper udump]$
|
有了这个跟踪文件就容易定位和诊断错误了。
-----
这篇 【使用ERRORSTACK进行错误跟踪及诊断】来自 www.eygle.com | CSDN技术网摘| del.icio.us|365Key
By eygle on 2005-03-16 09:38 | Comments (5) | Posted to Internal | Edit |Pageviews:
留言 (5)
如果trace文件里边的内容如下：
应该从何诊断呢？
连ORA编号的错误都没有给出。
这样的一个error trace文件是不是只对Oracle Support的人员有意义呢？
*** 2006-11-21 10:36:33.154
*** SESSION ID:(54.49387) 2006-11-21 10:36:33.154
Exception signal: 11 (SIGSEGV)
*** 2006-11-21 10:36:33.212
ksedmp: internal or fatal error
Current SQL statement for this session:
SELECT MAX (aa)
FROM (SELECT bblack || black || red || signoff aa, spnum bb
FROM block_users
WHERE mobile = '137' AND spnum = 'S1'
UNION ALL
SELECT DECODE (vip,
'hh', '1000',
'h1', '0100',
'red', '0010',
'signoff', '0001',
'0000'
) aa,
viptype bb
FROM CALL
WHERE mobile = '123' AND vip LIKE 'h%' AND viptype = 'S1'
ORDER BY aa DESC)
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+142 CALL ksedst()+0
ssexhd()+198 CALL ksedmp()+0 0 ?
Posted by: echo at November 21, 2006 11:06 AM
你这个错误是说明这个SQL存在问题或引发了问题。
可以仔细检查一下这个SQL
Posted by: eygle at November 21, 2006 11:31 AM
嗯，的确是这个sql有问题。去掉order by子句问题就解决了。主要里边有一个查询用的dblink，莫名奇妙的错误。
Posted by: echo at November 21, 2006 7:17 PM
eyegle ã9iطԼҼƪ£һֱûʵɹҵIJ
os:redhat 4.2 oracle9.2.0.4
15:40:18 SQL> create user test identified by test;
User created.
15:41:05 SQL> grant connect,resource to test;
Grant succeeded.
15:41:24 SQL> grant execute on dbms_flashback to test;
Grant succeeded.
15:41:49 SQL> conn test/test
Connected.
15:42:02 SQL>
15:42:05 SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist
15:42:25 SQL> create table test (id number);
Table created.
15:42:47 SQL> insert into test values (1);
1 row created.
15:43:10 SQL> insert into test values (2);
1 row created.
15:43:23 SQL> commit;
Commit complete.
15:43:28 SQL> select * from test;
ID
----------
1
2
15:43:33 SQL>
,ǰscn
15:52:29 SQL> select dbms_flashback.get_system_change_number fscn from dual;
FSCN
----------
31003610
15:52:39 SQL> insert into test values (3);
1 row created.
15:53:00 SQL> commit;
Commit complete.
15:53:02 SQL> select * from test;
ID
----------
1
2
3
15:53:07 SQL>
ȴӣʹǰscnأʧܣ
16:00:24 SQL> select * from test as of scn 31003610;
select * from test as of scn 31003610
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
ʱ仹DzС
16:00:43 SQL> select * from test as of timestamp to_timestamp ('2006-12-12 15:43:28','YYYY-MM-DD HH24:MI:SS');
no rows selected
16:02:03 SQL> select * from test as of timestamp to_timestamp ('2006-12-12 15:43:20','YYYY-MM-DD HH24:MI:SS');
no rows selected
ҿѵ9iʲôڽԸʱ˳
[oracle@test ~]$ sqlplus " /as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Dec 12 16:08:38 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
Posted by: ghbspecial at December 13, 2006 10:59 AM
上面那个是我发的，请教关于oracle9.2.0.4闪回失败的问题的，不知怎么跳到这个页面了，还出现了乱码，所以解释一下。
Posted by: ghbspecial at December 13, 2006 11:19 AM
