Tracing for a Specific ORA-nnnn Error

Overview

In troubleshooting tough recurring ORA-nnnn errors you can configure your database to provide detailed information using event signaling. To do this at the database level you need to create an errorstack EVENT parameter entry and bounce the database.

Once set, an alert log entry and corresponding detailed trace file will be generated for future ORA- errors matching the error number. The trace file is created in your udump directory.

This method of tracing, though exhaustively used by Oracle support, is not "officially" documented by Oracle. Furthermore, event types like 10046 and 10053 should not be used unless directed by Oracle support.

Trace Levels

1  - Enable standard SQL_TRACE functionality (default).
4  - Level 1 PLUS trace bind values.
8  - Level 1 PLUS trace waits.
12 - Level 1 PLUS both trace bind values and waits.

Enable/Disable

Assuming the error you want detailed tracing on is: ORA-2291

Enable

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

ALTER SYSTEM SET EVENT ='2291 trace name errorstack level 4' SCOPE=spfile;

Disable

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

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

Display Events

SQLPlus> show parameter event

NAME     TYPE        VALUE
-------- ----------- ----------------------------------
event    string      2291 trace name errorstack level 4

If above does not show events try this script:

declare
  event_level number;
begin
  for i in 10000..10999 loop
     sys.dbms_system.read_ev (i, event_level);
     if ( event_level > 0 ) then
        dbms_output.put_line ('Event '||to_char(i)||' set at level '||to_char(event_level));
     end if;
  end loop;
end;
/

If the above script does not show your event, try this from SQLPlus:

 oradebug setmypid
 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;

 TRACE_TYPE            PRIMARY_ID QUALIFIER_ WAITS BINDS
 --------------------- ---------- ---------- ----- -----
 SERVICE_MODULE_ACTION Service S  Module M   TRUE  FALSE

Change Session

 -- Turn On
 ALTER SYSTEM SET events '4022 trace name hanganalyze_global level 3;name systemstate_global level 258';
 System altered.

 -- Check Status
 oradebug setmypid
 Statement processed.
 oradebug eventdump system
 4022 trace name hanganalyze_global level 3;name systemstate_global level 258

 -- Turn Off
 ALTER SYSTEM SET events '4022 trace name hanganalyze_global off;name systemstate_global off';
 System altered.

 -- Check Status
 oradebug setmypid
 Statement processed.
 oradebug eventdump system
 Statement processed.

References:

• How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1).
• SQL TRACE GENERATING TO TRACE FILES NEVER STOPS (Doc ID 556756.1).
• How To List All The Named Events Set For A Database (Doc ID 436036.1).