Using The Oracle Oradebug Utility - Mike Ault
Using The Oracle Oradebug Utility - Mike Ault
Using The Oracle Oradebug Utility - Mike Ault
Mike Ault
MONOGRAPH
Notice
While the author & Rampant TechPress makes every effort to ensure the
information presented in this white paper is accurate and without error, Rampant
TechPress, its authors and its affiliates takes no responsibility for the use of the
information, tips, techniques or technologies contained in this white paper. The
user of this white paper is solely responsible for the consequences of the
utilization of the information, tips, techniques or technologies reported herein.
Oracle, Oracle7, Oracle8, Oracle8i, and Oracle9i are trademarks of Oracle Corporation.
Oracle In-Focus is a registered Trademark of Rampant TechPress.
Many of the designations used by computer vendors to distinguish their products are
claimed as Trademarks. All names known to Rampant TechPress to be trademark names
appear in this text as initial caps.
The information provided by the authors of this work is believed to be accurate and
reliable, but because of the possibility of human error by our authors and staff, Rampant
TechPress cannot guarantee the accuracy or completeness of any information included in
this work and is not responsible for any errors, omissions, or inaccurate results obtained
from the use of information or scripts in this work.
ISBN: 0-9740716-7-6
MONOGRAPH PAGE IV
Table Of Contents
Notice ............................................................................................ iii
Using the Oracle oradebug Utility – Publication Information .. iv
Table Of Contents ......................................................................... v
Introduction ................................................................................... 1
Invoking Oradebug ....................................................................... 1
Using Debug.................................................................................. 3
Monitoring the Current Session With the oradebug setmypid ............ 4
Using DUMP ....................................................................................... 5
Commands not requiring SPID to be set ............................................. 7
Using Oradebug for System Hangs: .................................................. 10
Getting a PROCESSTATE DUMP ................................................... 11
Getting a ERRORSTACKS DUMP .................................................. 12
Using ORADEBUG to Trace A Sessions SQL ................................. 12
How to Find the Right PID for oradebug setospid ............................ 16
Tracing Errors Using ORADEBUG .................................................. 17
Using ORADEBUG to Find Semaphore and Memory Segments ..... 19
Finding Parallel SQL Processes Using ORADEBUG....................... 21
Tracking down ORA-04030 errors.................................................... 22
Using Oradebug to Debug Spinning Processes ................................. 22
Oracle 8 IDLM and ORADEBUG .................................................... 23
How to determine the Events Set in a System................................... 24
Using ORADEBUG to Release DDL locks ...................................... 24
How to Trace Trigger Actions Using Oradebug................................ 26
Checking on Temporary Segment Usage with Oradebug ................. 26
Suspending a process using Oradebug .............................................. 27
MONOGRAPH PAGE V
MONOGRAPH PAGE VI
Introduction
Beginning in Oracle7 with ORADBX, the ORADEBUG utility allows
DBAs to start and stop tracing for any session, dump SGA and other
memory structures, wakeup oracle processes such as SMON or PMON,
suspend and resume processing in a SID, debug enqueue services, debug
the CGS name service, dump core files and IPC information, in fact many
useful operations that aren't usually available. Unfortunately this utility,
other than a terse paragraph in the administrator's manuals, is virtually
undocumented. In this paper I have attempted to provide as much
reference material as can be found or developed about ORADEBUG from
various lists, Metalink and the Pipelines at Quest RevealNet labs.
Invoking Oradebug
Oradebug is invoked from SVRMGRL in pre-9i instances and from
SQLPLUS in 9i and greater versions. The main commands of oradebug
can be displayed by entering oradebug help from the svrmgrl or
SQLPLUS command line as is shown in Figure 1.
SQL> oradebug help
HELP [command] Describe one or all commands
SETMYPID Debug current process
SETOSPID <ospid> Set OS pid of process to debug
SETORAPID <orapid> ['force'] Set Oracle pid of process to
debug
DUMP <dump_name> <level> Invoke named dump
DUMPSGA [bytes] Dump fixed SGA
DUMPLIST Print a list of available dumps
EVENT <text> Set trace event in process
SESSION_EVENT <text> Set trace event in session
DUMPVAR <p|s|uga> <name> [level] Print/dump a fixed PGA/SGA/UGA
variable
SETVAR <p|s|uga> <name> <value> Modify a fixed PGA/SGA/UGA
variable
PEEK <addr> <len> [level] Print/Dump memory
POKE <addr> <len> <value> Modify memory
WAKEUP <orapid> Wake up Oracle process
SUSPEND Suspend execution
RESUME Resume execution
MONOGRAPH PAGE 1
Once a command is entered into the oradebug utility, almost all of the
results are generated into trace files in the udump destination location. The
udump location can be identified by reviewing the initialization parameter
file for the user_dump_dest parameter value, reviewing initialization
parameter settings using Oracle Enterprise Manager (OEM), or, by issuing
the SHOW PARAMETER USER_DUMP_DEST command in SQLPLUS.
In Oradebug the TRACEFILE_NAME command will display the current
processes dumpfile name. The trace files generated from the oradebug
utility will be named for the spid of the process from where the oradebug
is executed.
MONOGRAPH PAGE 2
Using Debug
In order to use oradebug, for most commands, you must first tell oradebug
what SPID you wish to execute the debug commands against. You see, the
oradebug utility can be run from a privileged user against any other
process providing you know the spid of the other process. The SPIDs of
the various processes can be found with a simple select, as shown in
Figure 2.
SQL> SELECT a.username, a.sid, a.serial#, b.spid
2 FROM v$session a, v$process b
3 WHERE a.paddr=b.addr;
Of course, if you know the username you are looking for, you can modify
the select command shown in Figure 2 to reflect this username restriction.
If you are in a system where all users use the same username, you can also
restrict the command by terminal being used, operating system userid or
program being executed as all of this is also stored in the V$SESSION
view.
Once you have the SPID you set the value using the command shown in
figure 3.
$ sqlplus /nolog
SQL> connect sys as sysdba
Password: xxxxx
Connected.
MONOGRAPH PAGE 3
Note in Figure 3 the use of the unlimit command, this removes any
restriction on trace file size imposed by the initialization parameter
settings for the instance for this session.
MONOGRAPH PAGE 4
Using DUMP
The DUMP command allows various structures of a process to be dumped
to trace file for examination. The types of dump available for a process are
listed in Figure 5. These can be listed at anytime by using the oradebug
dumplist command.
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
MONOGRAPH PAGE 5
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 *
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
MONOGRAPH PAGE 6
KRB_TRACE
KRB_BSET_DAYS
DROP_SEGMENTS *
TREEDUMP <OBJ_ID>
LONGF_CREATE
ROW_CACHE
LIBRARY_CACHE
SHARED_SERVER_STATE
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE <cursor_id>
WORKAREATAB_DUMP
OBJECT_CACHE *
SAVEPOINTS *
MONOGRAPH PAGE 7
MONOGRAPH PAGE 8
MONOGRAPH PAGE 9
DATA_ERR_OFF
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
MONOGRAPH PAGE 10
This creates a large trace file in the user_dump_dest (30M or more is not
unusual).
Do this step for sure if the entire database is frozen or nearly frozen and if
this condition came on suddenly and there are no archive errors in the alert
log.
You should also trace the process from the os level using various os level
tools as specific to your os.
MONOGRAPH PAGE 11
When you get processstate dumps you should get them 3 times. This
generates a trace file in your user_dump_dest (from SVRMGRL or
sqlplus: show parameter user_dump_dest).
First you will need to get SID, SERIAL#, PADDR from V$SESSION, for
example:
MONOGRAPH PAGE 12
Once you know the SID and the SERIAL#, you can enable the trace for
each session running the command:
EXECUTE DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(<SID>, <SERIAL#>, TRUE)
Everything the user does will now be traced at trace level 1 until you
execute the SQL command again, but replacing 'TRUE' with 'FALSE'.
But what if you want a more detailed trace? Say a level 4? Obviously,
since there is no way to set the level using DBMS_SYSTEM, we should
use oradegub instead. Let's look at that example next.
Sometimes the trace at level 1 isn't enough, because in the sql statements
there are some bind variables. You need their values before you run the
query into SQLPLUS. In this case you have to perform trace at level 4, so
that you have the value of each bind variable in the .trc file.
Enabling the level 4 trace for a Discoverer user, first get SID, SERIAL#,
PADDR from V$SESSION. For example:
SQL> select username, sid, serial#, paddr from v$session
where username='VIDEO31';
MONOGRAPH PAGE 13
SELECT ADDR, PID, SPID FROM V$PROCESS WHERE ADDR = <PADDR from V$SESSION>;
For example:
SQL> SELECT ADDR, PID, SPID FROM V$PROCESS WHERE ADDR = '820532C8';
SQL> SELECT ADDR, PID, SPID FROM V$PROCESS WHERE ADDR = '82053EC8';
You can then enable the level 4 trace from a DBA group user using the
commands:
Sqlplus /nolog
CONNECT / as sysdba
ORADEBUG SETOSPID <SPID from the above query>
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4
This should now trace at level 4 everything the Discoverer user does.
Once you have completed gathering the information you need you turn off
the SQL trace for the session like so:
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF
MONOGRAPH PAGE 14
- or -
MONOGRAPH PAGE 15
Statement processed.
MONOGRAPH PAGE 16
Look for the terminal that matches your terminal from the 'who am i'
command. Note the pid that goes with it, in this case 3705. There will
be another process connected with this terminal, too, but that is the
grep, not server manager.
4. Issue this command from SQLPLUS /nolog connected as SYS:
!ps -ef | grep 3705
5. Look for a process that has a parent process of 3705. There are 2 here.
One is 3706, a sqlnet connection, and 3856, a shell. In this case, I'm
searching for the sqlnet connection that is running sqlplus, process
3706. This is the pid I would use for my oradebug setospid command:
oradebug setodpid 3706
..which will only produce anything if this session hits an ORA-942 error.
The output from oradebug is a raw trace file. Some experienced DBAs can
read these trace files in their raw state, however, I find it much easier to
MONOGRAPH PAGE 17
use another Oracle utility, tkprof, to format the output into human readable
output.
The 'raw' Trace File is the opposite of the tkprof'd version, in that it shows
you the exact sequence in which the various pieces of SQL were run.
Just bear in mind the following: before it is actually executed, any piece of
SQL is parsed into the SGA. It is allocated a CURSOR # at this point.
This CURSOR # will remain in memory, containing the same piece of
SQL code, until another piece of SQL needs to overwrite the memory, at
which point the CURSOR # becomes available for re-use as well.
Note that CURSOR #1 has now been over-written with a new SQL
statement, so any further EXEC statements for that cursor will relate to the
'new' SQL.
Once you have got the hang of this, using Level 4 Trace to extract 'BIND
VARIABLES' from the Trace File is straightforward. In the example
above, the ':b0' in the penultimate line is a bind variable, and if Level 4
Trace were switched on, there would be an extra set of lines before each
EXEC line which would say (in the example):
BINDS #1
This would be followed by a list of bind variables (:b0, :b1 etc) with some
information about each. The last piece of information given against each
bind variable is the actual value passed to the SQL statement.
MONOGRAPH PAGE 18
First, run the "ipcs -b" command to show the memory and semaphore
listings for the Unix box. An example output from the ipcs -b command on
an HPUX system is shown in Figure 13.
gpsp083:/dwqpkg/orasw > ipcs -b
IPC status from /dev/kmem as of Sun Sep 22 12:54:16 2002
T ID KEY MODE OWNER GROUP QBYTES
Message Queues:
q 0 0x3c1827b6 -Rrw--w--w- root root 16384
q 1 0x3e1827b6 --rw-r--r-- root root 264
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 7176 0x80bfe0c4 --rw-r----- oracle dba 1064779776
m 18953 0x94644538 --rw-r----- oracle dba 247726080
m 22026 0x00000000 --rw-r----- oracle dba 3221225472
m 14347 0x00000000 --rw-r----- oracle dba 3221225472
T ID KEY MODE OWNER GROUP NSEMS
Semaphores:
s 3304021 0x8d6f79a0 --ra-r----- oracle dba 2048
MONOGRAPH PAGE 19
As you can see, the output is not very user friendly, it doesn't give the
database sid information but only an internal id for semaphores or memory
segments.
Connected.
This will show the shared memory segment and semaphore that each
instance has attached/in use.
The Seg Id shows 22026 for the shared memory that is attached to the
RUNNING instance. If you were looking to remove a memory segment
from a crashed instance (for example from a broken pipe in Unix) you
would now know that this shared memory segment is not the one to
remove.
Once you have noted ALL of the identifiers for ALL of the instances
which are up and running, compare these id numbers to those in the "ipcs -
b" listing. The entry that does not have a running instance to match is the
orphaned entry.
MONOGRAPH PAGE 21
Then do whatever you want to trace that uses the parallel query slaves and
the sql trace info will appear in files in background_dump_dest,
For a Spin situation it is important to determine which area of the code the
session is spinning in. Some indication of this may be derived from the
event however it is usually necessary to produce an errorstack of the
process a few times for analysis by support:
>sqlplus /nolog
SQL> connect sys/sys as sysdba
Password: xxxxxxx
Connected.
MONOGRAPH PAGE 22
Where the SPID is the operating system process identifier, you can get it
from v$process.
The systemstate dump includes information for each process, search for
'PROCESS id' and look up the wait event by doing a search on 'waiting
for'.
The database name parameter is not now required as you will already be
connected to the database in question in svrmgrl when running:
oradebug lkdebug -O 0x0 0x0 ST 2
The output does not go to the screen but rather to the user_dump_dest
location for your svrmgrl session. This means that the best tip for using
lkdebug is to tail the trace file in another window.
MONOGRAPH PAGE 23
1 for session
2 for process
4 for system
The output will give a typical trace file header and then will list the
information on all events that have been set at the particular level in the
instance you asked for, in this case, system wide.
Eventually, PMON will wakeup and notice that it needs to clean up those
locks and then the resources will be freed. However, using oradebug, you
MONOGRAPH PAGE 24
That tells you the Oracle pid of the PMON process. Then, just:
oradebug wakeup <pid>
So, we come to our scenario: Alter system kill session, then kill server
process in the OS, (SIGKILL is effective), now, the process has stopped
executing, and PMON can start clean up immediately. It will free the
library cache pin immediately, and take care of rollback after that, so, your
stored object is freed. One other point: PMON wakes up every 3 seconds,
but, it only checks for dead processes every 20th wake up, or, every 60
seconds. I have demonstrated this by running truss on the PMON process.
So, if you alter system kill session, but you don't go after the server
process by killing via OS mechanism, if could take several minutes to
clean up, and then PMON would take an additional minute to wake up and
clean things up.
$ sqlplus -s /nolog
SQL> connect / as sysdba
Connected.
SQL> oradebug event 10309 trace name context forever, level 1
Statement Porcessed.
Don't forget to turn it off using context off when you are done.
You can check whether such action is active using ORADEBUG in the
following manner:
1. connect internal or in Oracle9i sys as sysdba
2. SELECT owner FROM dba_segments WHERE
segment_name=<temporary_segment>;
3. SELECT pid FROM v$process WHERE username=<owner of
segment>;
4. oradebug setorapid <pid>
5. oradebug dump errorstack 3
6. This will generate trace files, which contain the "current sql
statement".
7. If this is a CTAS or alter index ... rebuild, then you will have to wait
for the DDL action to finish.
MONOGRAPH PAGE 26
If you do not find active processes for the segment owner, or none of them
has a DDL action, it is possible that someone started this DDL statement
and the session died for some reason. The process PMON will clean up
stray processes after a crashed session and call SMON to remove this
temporary segment. But temporary segments can be very large and
removing them can take a long time, in some cases days. The temporary
segment can also be removed by a database shutdown, or shutdown
immediate. You can also force SMON to remove it by:
ALTER TABLESPACE <permanent tablespace> coalesce;
Connected.
MONOGRAPH PAGE 27
The output in the trace file area for udump gives various details:
Dump file /opt/oracle/oradata10/dumparea/KLF/udump/ora_21335.trc
Oracle7 Server Release 7.3.3.5.0 - Production Release
With the distributed, replication and parallel query options
PL/SQL Release 2.3.3.5.0 - Production
ORACLE_HOME = /opt/oracle/app/oracle/product/7.3.3
System name: DYNIX/ptx
Node name: fes4
Release: 4.0
Version: V4.4.2
Machine: i386
Instance name: KLF
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 21335, image: oracleKLF
MONOGRAPH PAGE 28
Notice how the current sql statement is displayed. This is useful for
identifying what a hanging/long running program is doing. By suspending
the process and dumping it, you can see what it is doing.
oradebug dump errorstack 2
If you aren't sure what you want to dump, you can get a list of things to
dump:
SVRMGR> oradebug dumplist
This gives the following list (the full list is found elsewhere):
MONOGRAPH PAGE 29
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
CROSSIC
MONOGRAPH PAGE 30
..
LIBRARY CACHE STATISTICS:
gets hit ratio pins hit ratio reloads invalids namespace
---------- --------- ---------- --------- ---------- ---------- ---------
51513 0.9595830 181042 0.9733432 700 1454 CRSR
8425 0.8227893 15429 0.8210512 987 0 TABL/PRCD
260 0.9730769 260 0.9730769 0 0 BODY
307 0.8469055 333 0.7717718 4 0 TRGR
609 0.0098522 642 0.0514019 6 0 INDX
27 0.4444444 15 0.3333333 0 0 CLST
0 0.0000000 0 0.0000000 0 0 OBJE
0 0.0000000 0 0.0000000 0 0 PIPE
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
0 0.0000000 0 0.0000000 0 0 ?
61141 0.9305376 197721 0.9580773 1697 1454 CUMULATIVE
MONOGRAPH PAGE 31
For example, the following syntax could be used to get a text dump on the
controlfile in the trace file of the current process showing all the
controlfile record types but only the oldest and most recent of the circular
reuse records.
oradebug setmypid
oradebug dump controlf 3
If you would like to play around with this some more, the commands to
dump the controlfile and file headers to your process trace file are as
follows.
oradebug setmypid
oradebug dump controlf 10
oradebug dump file_hdrs 10
MONOGRAPH PAGE 32
Summary
The ORADEBUG utility is a powerful and complex utility which allows
you to easily control the dumping and tracing of virtually any database
information relating processes. We have only touched the surface in this
paper. Whenever using ORADEBUG be sure to try the options on a test
environment before attempting them in a production situation.
MONOGRAPH PAGE 33