Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Utilities Tips


ORADEBUG Dumps

The oradebug utility provides many options for dumping database information to trace files. In order to know what data can be dumped, the dumplist command will return the available options.

EVENTS
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
HANGanalyze
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
CROSSIC
CONTEXTAREA
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
MEMORY_LOG
REALFREEDUMP
ERRORSTACK
HANGANALYZE_PROC
TEST_STACK_DUMP
BG_MESSAGES
ENQUEUES
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER GRANULELIST
GRANULELISTCHK
SCOREBOARD
GES_STATE
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
FLUSH_CACHE
LOGHIST
ARCHIVE_ERROR
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
TR_SET_BLOCK
TR_SET_ALL_BLOCKS
TR_SET_SIDE
TR_CRASH_AFTER_WRITE
TR_READ_ONE_SIDE
TR_CORRUPT_ONE_SIDE TR_RESET_NORMAL
TEST_DB_ROBUSTNESS
LOCKS
GC_ELEMENTS
FILE_HDRS
KRB_CORRUPT_INTERVAL
KRB_CORRUPT_SIZE
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_SIMULATE_NODE_AFFINITY
KRB_TRACE
KRB_BSET_DAYS
DROP_SEGMENTS
TREEDUMP
LONGF_CREATE
ROW_CACHE
LIBRARY_CACHE
SHARED_SERVER_STATE
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
WORKAREATAB_DUMP
OBJECT_CACHE
SAVEPOINTS
OLAP_DUMP

One scenario in which a dump may aid in diagnosing the problem is when dealing with a system hang. If no errors exist in the alert log and the database appears to be hung, connect as SYSDBA and execute the following:

SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug dump systemstate 10

This creates a very large trace file from the system dump. For this reason, the oradebug unlimit option should be used to override the maximum trace file size as specified in init.ora.

oradebug also has the capability to only produce trace output if a particular error is encountered. This command is especially useful when certain errors are encountered inconsistently. Rather than generate enormous trace files, the trace data will only be generated when the particular error occurs. The following command will monitor a particular session and only generate trace data when the ORA-00942 error is hit:

SQL> oradebug event 942 trace name errorstack level 3

oradebug can also be used to suspend and resume any user database connection. When suspended, the user session will simply hang until the resume command is processed. During the suspension period, v$session_wait will indicate that the session is waiting on the debugger.

Only a few of the oradebug options were discussed. oradebug is a powerful utility to be used only by expert DBAs when diagnosing serious database issues. oradebug can be used in tandem with tkprof to get more detailed information in trace files. In addition, the tool can be used to generate system or process state dumps which can be vital when diagnosing system hang scenarios.

The last tracing utility to be explained in this chapter is tightly coupled with the OEM product – Oracle Trace.
 



For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.



  ”call


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.