Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Saturday, April 28, 2012

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 because they affect the performance. But in some circumstances it becomes necessary to turn them on to diagnose the issues with RDBMS, you can think it as extended SQL trace.

Event types
Dump events
There are two dump event types: those that immediatly dump information (such as systemstate, errorstack, controlf, file_hdrs and redohdr) and thos that only dump if an error occurs.
The information dumped is written to a trace file.
Trace events
A trace events continuously writes information while the session is running.
The most famous trace event is probably the 10046 event.

Setting the events
You can set the events at system or session level. You can set it through the event init parameter or spfile.
alter system set events '10795 trace name context forever, level 2';
alter system set events '15055 trace name errorstack forever, level 3'
alter system set events 'trace[KFNC] memory=medium';
 You can set multiple events as below using spfile
           EVENT='10795 trace name context forever, level 2',
           '15055 trace name errorstack forever, level 3' ,
           'trace[KFNC] memory=medium'
           COMMENT='Debug tracing of ASM Communication Error' SCOPE=SPFILE SID='*';
You will need to restart the instance to take the effects, if it is RAC system use the below one command.
srvctl stop instance -d mydb -i mydb2
srvctl start instance -d mydb -i mydb2
you can verify that your events have been set as below.
FROM gv$parameter
WHERE name = 'event'

Disabling the event
alter session set events '10053 trace name context off';
To remove all events, use:

Check the diagnostics events messages
    err_msg VARCHAR2(120);
    dbms_output.enable (1000000);
    FOR err_num IN 10000..10999
    err_msg := SQLERRM (-err_num);
    IF err_msg NOT LIKE '%Message '||err_num||' not found%' THEN
    dbms_output.put_line (err_msg);
   END IF;

Determine the values for database events that have been set

event_level number;
for i in 10000..10999 loop
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
end if;
end loop;

Here are some useful events given.
Database hang problems
If you experience database hang problems during grants of roles or privileges or some other particular action 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/sys 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. 
You can check the alert log and open the trace file to analyze being mentioned in it.

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';


No comments: