小编点评

## Using ErrorStack to Diagnose ORA-2289 The following example demonstrates how to use the `errorstack` feature in Oracle to diagnose the `ORA-2289` error: **Step 1: Start the errorstack** ```sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 22:00:21 2023Version 19.16.0.0.0Copyright (c) 1982, 2022, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionVersion 19.16.0.0.0SQL> select s2.nextval from dual; select s2.nextval from dual *ERROR at line 1:ORA-02289: sequence does not existSQL> exit ``` **Step 2: Analyze the errorstack** The output shows the following details: - **Error number:** 2289 - **Sequence:** `null` - **SQL statement:** `select s2.nextval from dual` - **Location:** `dbkda.c@298` (this indicates the file and line where the error occurred) **Step 3: Investigate further** - Check the `demo_ora_3435.trc` file in the `Oracle database` directory. This file should contain the actual SQL statement that caused the error. - Use the `sqlplus -l` option to run the SQL statement directly. This can help you determine if the error is caused by the database or by other factors. **Possible causes of ORA-2289:** - **Database issue:** The sequence `null` could indicate an issue with the database itself. - **Application issue:** The application might be trying to access a non-existent sequence or be making a mistake in the SQL statement. - **Misconfiguration:** The application might be configured to use a specific sequence that is not available. **Additional tips:** - Use the `errorstackalter` commands to control the level and depth of the error stack. - Use the `sqlplus -V` option to get more verbose error information. By following these steps, you can diagnose the `ORA-2289` error and identify the underlying cause.

正文

有客户遇到ORA-2289的报错，同事协助去现场排查，我帮着远程共同check下。
客户只是应用端报出的错误，为了进一步定位，服务端需要开errorstack协助定位具体问题。
下面就以这个ORA-2289为例，示范下errorstack的使用方法。

--开启errorstack
alter system set events '2289 trace name errorstack level 3';

--关闭errorstack
alter system set events '2289 trace name errorstack off';
复制

开启errorstack期间，模拟一个会话发生了ORA-02289的报错：

[oracle@bogon trace]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Apr 26 22:00:21 2023
Version 19.16.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.16.0.0.0

SQL> select s2.nextval from dual;
select s2.nextval from dual
       *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> exit
复制

在alert告警日志可以看到提示：

2023-04-26T22:00:27.300427+08:00
Errors in file /u01/app/oracle/diag/rdbms/demo/demo/trace/demo_ora_3435.trc:
ORA-02289: sequence does not exist
复制

进而在trc文件中进一步查询具体报错的SQL信息等：

[oracle@bogon trace]$ vi demo_ora_3435.trc 
...
   17 *** 2023-04-26T22:00:27.300081+08:00 (CDB$ROOT(1))
   18 *** SESSION ID:(2548.56891) 2023-04-26T22:00:27.300150+08:00
   19 *** CLIENT ID:() 2023-04-26T22:00:27.300158+08:00
   20 *** SERVICE NAME:(SYS$USERS) 2023-04-26T22:00:27.300164+08:00
   21 *** MODULE NAME:(sqlplus@bogon (TNS V1-V3)) 2023-04-26T22:00:27.300170+08:00
   22 *** ACTION NAME:() 2023-04-26T22:00:27.300177+08:00
   23 *** CLIENT DRIVER:(SQL*PLUS) 2023-04-26T22:00:27.300182+08:00
   24 *** CONTAINER ID:(1) 2023-04-26T22:00:27.300189+08:00
   25 
   26 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
   27 ----- Error Stack Dump -----
   28 <error barrier> at 0x7ffed8393280 placed dbkda.c@298
   29 ORA-02289: sequence does not exist
   30 ----- Current SQL Statement for this session (sql_id=c6bu1kcbt5z3f) -----
   31 select s2.nextval from dual
   32 
   33 ----- Call Stack Trace -----
   34 calling              call     entry                argument values in hex
   35 location             type     point                (? means dubious value)
   36 -------------------- -------- -------------------- ----------------------------
   37 ksedst1()+95         call     kgdsdst()            7FFED83926E0 000000002
...
复制

我这里模拟是直接查询了不存在的序列，但是报这个错误其实未必是数据库问题，可以在MOS多搜索下相关案例，也有程序配置问题导致的情况。