MyOracleWorld.Net  .........

 

Database - Tracing/Dumps/Events

Tracing:

Dumps:

Events:

 

Generic dump/trace/events I use the most in the support environment:

Dump complete contents of the control file:

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME CONTROLF LEVEL 10’;

 

Dump a complete database block:

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME BLOCKDUMP LEVEL <DBA>;

 

Dumping all buffers in the SGA buffer cache:

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME BUFFERS LEVEL 1’;

 

Dump all locks held by the lock process when using OPS:

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME LOCKS LEVEL 5’;

 

Dump the redo log file’s header to trace:

            Level 1 – dump controfile entry of the redo info only
            Level 2 – dump the generic file header
            Any level > 2 – dump complete header to trace

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME REDOHDR LEVEL 10’;

 

Dump log history entries from the control file:

                                Level 1 – only dumps the earliest and latest log history entries

                                Level 2 – dumps the most recent 2**LEVEL (2 to the power LEVEL) entries

 

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME LOGHIST LEVEL 4’;   (2**4=16)

 

Dump all Datafile headers to trace:

 

                                Level setting the same as for REDOHDR

 

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME FILE_HDRS LEVEL 10’;

 

Dump and error stack from f.e an ORA-12547:

 

    init.ora: Eg: event="12547 trace name ERRORSTACK level 3"

    session:  Eg: alter session set events '12547 trace name ERRORSTACK level 3';

 

Dump entire system state – systemstate dump

 

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 10’;

Coalesce free space in fet$ for the specified tablespace:

                ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME COALESCE LEVEL X’;

 

To UNSET any event:

                ALTER SESSION SET EVENTS ‘<event> trace name context forever, level OFF’;