Oracle provides various diagnostic tools for debugging the RDBMS. Certain events can be turned on to dump diagnstic information to trace files. Next, some INIT.ORA parameters are available that can be used while diagnosing memory and disk corruptions. There parameters are not set during normal operation of the database, due they affect the performance. In the following article, some useful events will be closer explained.

Database hang problems

If you experience database hang problems during grants of roles or privileges a state dump may help you to solve the problem. You can get a state dump by doing the following:

Cleanup not needed TRACE Files

$ sqlplus /nolog
SQL> connect sys/manager as sysdba
SQL> alter session set events
     'immediate trace name systemstate level 10';
SQL> exit;

Log out, otherwise subsequent trace dumps overwrite the existing trace file.

Generally if a session is executing DBMS_PIPE, DBMS_SQL or DBMS_SHARED_POOL at the time a grant is issued on any of these, a hang can happen. By killing the session using these packages (or shutting down the process if possible) the hang can be resolved.

Out of sync file headers and controlfiles

In some shutdown scenarios (such as a system crash) file headers and controlfiles may get out of sync. If you get "ORA-01207: file is more recent than controlfile - old controlfile " type errors, you can troubleshoot using the following events:

$ sqlplus /nolog
SQL> connect sys/manager as sysdba
SQL> startup mount
SQL> alter session set events
     'immediate trace name controlf level 10';
SQL> alter session set events
     'immediate trace name file_hdrs level 10';
SQL> alter session set events
     'immediate trace name redohdr level 10';

These commands will generate a single trace file located in your user_dump_dest directory containing information about the controlfile and data/redolog files. In an emergency, you can use the recover clause "using backup controlfile" to possibly recover from this scenario. However, make sure you do a full cold backup before attempting recovery.

Tracing ORA-60x Severity Errors

If you find any ORA-60x errors in you ALERT.ORA system file, you should check, why the database dumps an errorstack to the trace file. The following example dumps the error stack every time a process encounters the ORA-604 error.

SQL> alter session set events
     '604 trace name errorstack forever';

Useful Events set in INIT.ORA in case of an emergency

Oracle can use event setpoints in the initialization file for tracking and validation of objects. The last event setting (10231) will allow export to extract all data, except the corrupted block, from a table. You should only use these under guidance from Oracle support.

# Sets integrity checking for tables
event = "10210 trace name context forever, level 10"

# Sets integrity checking for indexes
event = "10211 trace name context forever, level 10"

# Allows export to extract all data, except the corrupted blocks
event = "10231 trace name context forever, level 10"

# Identify corrupted rollback segment when database cannot be started
event = "10015 trace name context forever"

Where to find Event Numbers ?

Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg

cd $ORACLE_HOME/rdbms/mesg
vi oraus.msg
/10015

10015, 00000, "Undo Segment Recovery"
// *Cause:
// *Action: