Oracle Event Tracing
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
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
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
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
Where to find Event Numbers ?
Event numbers can be found in $ORACLE_HOME/rdbms/mesg/oraus.msg