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
ALTER SYSTEM SET
           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.
SELECT *
FROM gv$parameter
WHERE name = 'event'

Disabling the event

alter session set events '10053 trace name context off';

To remove all events, use:
ALTER SYSTEM RESET EVENT SCOPE=SPFILE SID='*' ; 

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

Determine the values for database events that have been set

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;

/ 

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

Ref:160178.1