How to tell Oracle to take a dump

It is often useful to dump the internal contents of Oracle internal structures such as the control files, library cache, and redo log headers. The following undocumented Oracle commands can be used for this purpose.



For each of these commands, a trace file will be generated in your UDUMP directory. After issuing the command, you can go to your UDUMP directory to view the contents of the dump.



1 – SCN Dump - This command dumps the control file contents to see SCN information. The SCN information is especially useful when doing a database recovery.



alter session set events
'immediate trace name CONTROLF level 10';





2 – File Header Dump - Here is the command to dump file headers. This dump is also quite useful if you have a corrupt database or a failure from rman in doing a database recovery.



alter session set events
'immediate trace name FILE_HDRS level 10';



3 - Dump redo log headers – This command will dump all of the redo log header information.



alter session set events
'immediate trace name REDOHDR level 10';



4 – System state dump – This command will dump the system state to your trace file.



alter session set events
'immediate trace name SYSTEMSTATE level 10';



5 – Process state dump – This command will dump the process state for all active processes in your database:



alter session set events
'immediate trace name PROCESSSTATE level 10';



6 – Library Cache Dump – This command is very useful for examining library Cache details, especially when you are having high library cache reloads or SQL access contention:





alter session set events
'immediate trace name library_cache level 10';



Here is a partial listing from this dump. As you can see, Oracle provides a wealth of detailed information about the internals of the library cache:



Dump file /u01/app/oracle/admin/testc1/udump/testc1_ora_16167.trc

Oracle8i Enterprise Edition Release 8.1.6.1.0 - 64bit Production

With the Partitioning option

JServer Release 8.1.6.1.0 - 64bit Production

ORACLE_HOME = /u01/app/oracle/product/8.1.6_64

System name: SunOS

Node name: sting

Release: 5.8

Version: Generic_108528-03

Machine: sun4u

Instance name: testc1

Redo thread mounted by this instance: 1

Oracle process number: 10

Unix process pid: 16167, image: oracle@sting (TNS V1-V3)



*** SESSION ID:(10.6858) 2001-07-05 10:29:17.148

LIBRARY CACHE STATISTICS:

gets hit ratio pins hit ratio reloads invalids namespace

---------- --------- ---------- --------- ---------- ---------- ---------

48475 0.9905518 273499 0.9975868 20 13 CRSR

5043 0.9569700 151734 0.9973045 0 0 TABL/PRCD/TYPE

4044 0.9943126 4051 0.9923476 0 0 BODY/TYBD

1 0.0000000 1 0.0000000 0 0 TRGR

29 0.0000000 29 0.0000000 0 0 INDX