Debugging any Oracle errors and events using ERRORSTACK

 

DBA has seen ORA-00600 trace file generated every time ORA-00600 or ORA-07445 occurs which has each and every detail about this error. 

Generating trace files for ORA-00600 or ORA-07445 is the default behaviour of Oracle, but the Same doesn’t happen in case of other errors.

We are using ERRORSTACK to  capture  details  for all other  oracle errors and events .  By enabling error stack we are force capturing  error  details in alert log and with its trace file details .

ERRORSTACK Parameters:

0 – Error stack only

1 – Error stack and function call stack (if implemented)

2 – As 1 plus the ProcessState

3 – As 2 plus the context area (all cursors and current cursor highlighted)

Enable

ALTER SYSTEM SET EVENT ='<ORAError> trace name errorstack level <LevelNumber>' SCOPE=spfile;

e.g To set errorstack for ORA – 1652 ,Syntax will be

 

alter system set events ‘1652 trace name errorstack level 3’;  -- system level 

alter session set events ‘1652 trace name errorstack level 3’; -- sesion level 

Disable

ALTER SYSTEM SET EVENT='<ORAError> trace name errorstack off';

ALTER SYSTEM SET EVENT='1652 trace name errorstack off';

Display Events

-- SQLPlus> show parameter event

-- oradebug eventdump session;   OR   oradebug eventdump system  

-- DBA_ENABLED_TRACES or WRI$_TRACING_ENABLED can be used to see status also:

SELECT TRACE_TYPE, PRIMARY_ID, QUALIFIER_ID1, WAITS, BINDS FROM DBA_ENABLED_TRACES;

You can also use oradebug to take errorstack for particular process (say ospid 1234) as below

sqlplus “/ as sysdba”

oradebug setospid 1234

oradebug unlimit

oradebug dump errorstack 3

References :

How to Collect Errorstacks for use in Diagnosing Performance Issues. (Doc ID 1364257.1)