errorstack 工具介绍

原文：http://www.orafan.net/blog/223.html/comment-page-1#comment-13

1.alter system set event ：此方法对spfile生效，无法对memory生效

ALTER system SET event='1461 trace name errorstack level 3'
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
 
ALTER system SET event='1461 trace name errorstack level 3' scope=spfile;
System altered.

验证是否已成功更改：

CREATE pfile='/home/oracle/1.ora' FROM spfile;
File created.
[oracle@rac01 ~]$ grep event 1.ora
*.event='1461 trace name errorstack level 3'

这种方法的话，需要重启DB。下面Rollback这个参数更改

ALTER system SET event='1461 trace name errorstack off' scope=spfile;
System altered.
CREATE pfile='/home/oracle/2.ora' FROM spfile;
File created.
[oracle@rac01 ~]$ grep event 2.ora
*.event='1461 trace name errorstack off'

2. alter system set events：此方法对memory生效，无法对spfile生效

 ALTER system SET events '1461 trace name errorstack level 3';
System altered.

验证：

[oracle@rac01 ~]$ tail -n1 /oracle/admin/strm1/bdump/alert_strm1.log
OS Pid: 9070 executed alter system set events '1461 trace name errorstack level 3'
这个参数更改没有反应到spfile中

CREATE pfile='/home/oracle/3.ora' FROM spfile;
File created.
[oracle@rac01 ~]$ grep event 3.ora
*.event='1461 trace name errorstack off'

其实这里的1461是个BUG 6085625，Metalink上可以参照NOTE: 461911.1
另外需要注意的是，RAC环境中用方法2动态设置event的话需要在所有instance设置。不过理解了上面的区别的话这个也就很容易明白了。
注意：设置1461 event无法对已经存在的进程生效，对于设置之后新建立的进程才会有效果。所以，不得不，我们还是得重启DB，让其对所有进程生效。

使用errorstack步骤：

设置ErrorStack主要有4个级别:
0 仅转储错误堆栈(0级已经被废弃)
1 转储错误堆栈和函数调用堆栈
2 Level 1 + ProcessState
3 Level 2 + Context area(显示所有cursors,着重显示当前cursor)
ErrorStack可以在实例级或会话级别设置，也可以在参数文件中设置，这个设置仅当某个特定的错误出现时才被触发
1. 启用：
SQL> alter system set events='984 trace name errorstack forever,level 3';
2. 执行相关的SQL，获取跟踪文件(ORA-00984错误)
3. 关闭：
SQL> alter system set events='984 trace name errorstack off';

或者使用oradebug：
--启用
SQL> oradebug event 1000 trace name errorstack level 10;
Statement processed.
--关闭
SQL> oradebug event 1000 trace name errorstack off;
Statement processed.

ORA-02049 : 超时: 分布式事务处理等待锁

1、alter system set events '2049 trace name errorstack level 3';
2、执行SQL
3、alter system set events='2049 trace name errorstack off';

以ORA-00942: table or view does not exist为例：
1、Session级别打开：
SQL> alter session set events '942 trace name errorstack level 3';

Session altered.

SQL> select * from dual1;
select * from dual1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>
查看alter log:
Errors in file /app/oracle/admin/EAITEST/udump/eaitest_ora_6370.trc:
ORA-00942: table or view does not exist

2、全局打开：
SQL> alter system set events '942 trace name errorstack level 3';

System altered.

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string
SQL>

SQL> select * from dual1;
select * from dual1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> 

查alert log，没有相关记录，只记有：
OS Pid: 6370 executed alter system set events '942 trace name errorstack level 3'

重开一个session：
SQL> select * from dual1;
select * from dual1
*
ERROR at line 1:
ORA-00942: table or view does not exist

查看alter log：
Errors in file /app/oracle/admin/EAITEST/udump/eaitest_ora_6995.trc:
ORA-00942: table or view does not exist

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 639068368 bytes
Fixed Size 735440 bytes
Variable Size 603979776 bytes
Database Buffers 33554432 bytes
Redo Buffers 798720 bytes
Database mounted.
Database opened.
SQL>

SQL> show parameter event

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
event string

SQL> select * from dual1;
select * from dual1
*
ERROR at line 1:
ORA-00942: table or view does not exist

查alert log，没有相关记录，说明更改没有记录到spfile中，要使更改记录到spfile中，

使用：
alter system set event='942 trace name errorstack level 3' scope=spfile;
关闭error trace：

alter system set events '942 trace name context off';

 

 

Truss看看有什么系统调用
先不连接进入Sqlplus：
$ sqlplus /nolog    
SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 27 17:24:00 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
SQL>

然后查一下sqlplus的进程号：
$ ps -ef|grep sql
  oracle   96911   872   0 17:24:01 pts/2       0:00 sqlplus /nolog

$ truss -o /tmp/sqlplus.log -p 96911
然后在sqlplus /nolog中connect连接：
SQL> conn / as sysdba
ERROR: