How to Force Oracle To Capture Error In Alertlog

<<Back to DB Administration Main Page

There are situations when oracle triggers an error which is probably captured at application level but not recorded in alterlog. For example a very simple error ORA-00942: table or view does not exist
which probably means you don't have permission to access the object or there is typo or or...or...
But there are such error which you must want to capture in the alertlog and generate the trace file for further troubleshooting. In such occasions you force oracle to write the error message and generate the trace file by enabling the errorstack.

How to Enable Orale ErrorStack

You can enable error stack either at session level or system level.

Enabling ErrorStack at Session Level
$ sqlplus / as sysdba
SQL> alter system set max_dump_file_size=unlimited;
SQL> alter  session set events '942 trace name errorstack level 3';

SQL> exit

Enabling ErrorStack at System Level

$ sqlplus / as sysdba
SQL> alter system set max_dump_file_size=unlimited;
SQL> alter system set events '942 trace name errorstack level 3';
SQL> exit

Using Oradebug utility

At System Level
SQL> oradebug event 942 trace name errorstack level 3

At Session Level

SQL> oradebug session_event 942 trace name errorstack level 3

Once you have enabled the error stack the next occurrence of the error is recorded in the database alertlog and corresponding trace file is generated.

NOTE1: If you are setting the event for RAC database ensure to enable for each instance

NOTE2:- As you might have noticed I just omitted preceding 0's from the ORA-ERROR NUMBER while enabling the error stack for this error (this is defined syntax). You can enable error stack for as many as errors you want at the same time. 

Once you have generated the trace file for the error you might want to disable the error stack and you can do so as following 

How to Disable Oracle ErrorStack

Disabling ErrorStack at Session Level
$ sqlplus / as sysdba
SQL> alter  session set events '942 trace name context off';
SQL> exit

Disabling ErrorStack at System Level

$ sqlplus / as sysdba
SQL> alter system set events '942 trace name context off';
SQL> exit

Using Oradebug utility

At System Level

SQL>oradebug event 942 trace name context off;

At Session Level

SQL>oradebug session_event 942 trace name context off;

NOTE3: If you have to disable the event for RAC database ensure to disable for each instance

At any moment if you want to see the events se in your database you can use following query

How to List the Events Set in Oracle Database

SQL> oradebug eventdump session;
942 trace name errorstack level 3
SQL> oradebug eventdump system;
942 trace name errorstack level 3

oradebug eventdump Syntax

SQL> oradebug doc event action eventdump
eventdump
        - list events that are set in the group
Usage
-------
eventdump( group           < system | process | session >)