ORADEBUG

ORADEBUG is an undocumented debugging utility supplied with Oracle

For more general information see ORADEBUG introduction

ORADEBUG commands include:

HELP
DOC
SETMYPID
SETOSPID
SETORAPID
SETORAPNAME
TRACEFILE_NAME
SETTRACEFILEID
UNLIMIT
FLUSH
CLOSE_TRACE
SUSPEND
RESUME
WAKEUP
DUMPLIST
DUMP
PDUMP
EVENT
SESSION_EVENT
DUMPSGA
DUMPVAR
PEEK
POKE
IPC
PROCSTAT
Dumping the SGA

HELP command

The ORADEBUG HELP command lists the commands available within ORADEBUG

These vary by release and platform. Commands appearing in this help do not necessarily work for the release/platform on which the database is running

For example in Oracle 12.1.0.2 ORADEBUG HELP returns the following:

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
SETORAPNAME    <orapname>                Set Oracle process name to debug
SHORT_STACK                              Get abridged OS stack
CURRENT_SQL                              Get current SQL
DUMP           <dump_name> <lvl> [addr]  Invoke named dump
PDUMP          [interval=<interval>     Invoke named dump periodically
               [ndumps=<count>  <dump_name> <lvl> [addr]
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
DUMPTYPE       <address> <type> <count>  Print/dump an address with type info
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
FLUSH                                    Flush pending writes to trace file
CLOSE_TRACE                              Close trace file
TRACEFILE_NAME                           Get name of trace file
SETTRACEFILEID <identifier name>         Set tracefile identifier
LKDEBUG                                  Invoke global enqueue service debugger
NSDBX                                    Invoke CGS name-service debugger
-G             <Inst-List | def | all | cluster > Nodes involved
-R             <Inst-List | def | all | cluster > Nodes involved (return output)
SETINST        <instance# .. | all>      Set instance list in double quotes
SGATOFILE      <SGA dump dir>         Dump SGA to file; dirname in double quotes
DMPCOWSGA      <SGA dump dir> Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA      <SGA dump dir>         Map SGA as COW; dirname in double quotes
HANGANALYZE    [level] [syslevel]        Analyze system hang
FFBEGIN                                  Flash Freeze the Instance
FFDEREGISTER                             FF deregister instance from cluster
FFTERMINST                               Call exit and terminate instance
FFRESUMEINST                             Resume the flash frozen instance
FFSTATUS                                 Flash freeze status of instance
SKDSTTPCS      <ifname>  <ofname>        Helps translate PCs to names
WATCH          <address> <len> <self|exist|all|target>  Watch a region of memory
DELETE         <local|global|target> watchpoint <id>    Delete a watchpoint
SHOW           <local|global|target> watchpoints        Show  watchpoints
DIRECT_ACCESS  <set/enable/disable command | select query> Fixed table access
IPC                                      Dump ipc information
IPC_TRACE      <module> <trace_flags> <trace_level>
                                         Modify IPC trace flags
IPC_CHECKSUM   <light/medium/full>
                                        Enable/Disable IPC Checksumming
UNLIMIT                                  Unlimit the size of the trace file
CALL           [-t count] <func> [arg1]...[argn]  Invoke function with arguments
CORE                                     Dump core without crashing process
PROCSTAT                                 Dump process statistics

SETMYPID command

Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected

The SETMYPID command selects the current process as the ORADEBUG process

For example:

ORADEBUG SETMYPID

ORADEBUG SETMYPID can be used to select the current process to run systemwide commands such as dumps

Do not use ORADEBUG SETMYPID if you intend to use the ORADEBUG SUSPEND command

SETOSPID command

Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process.

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected.

The SETOSPID command selects the another process using the operating system PID as the ORADEBUG process

The syntax is:

ORADEBUG SETOSPID pid

where pid is the operating system process ID of the target process

For example:

ORADEBUG SETOSPID 34345

The operating system process ID is the PID on Unix systems and the thread number on Windows NT/2000 systems

On Unix the PID of interest may have been identified using a top or ps command.

SETORAPID command

Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process.

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected.

The SETORAPID command selects another process using the Oracle PID as the ORADEBUG process

The syntax is:

ORADEBUG SETORAPID pid

where pid is the Oracle process ID of the target process

For example:

ORADEBUG SETORAPID 9

The Oracle process id for a process can be found in V$PROCESS.PID

To obtain the Oracle process ID for a foreground process use:

SELECT pid FROM v$process 
WHERE addr =
(
  SELECT paddr FROM v$session
  WHERE sid = DBMS_SUPPORT.MYSID
);

Alternatively, if the DBMS_SUPPORT package is not available use:

SELECT pid FROM v$process 
WHERE addr =
(
  SELECT paddr FROM v$session
  WHERE sid = 
  (
    SELECT sid FROM v$mystat WHERE ROWNUM = 1
  )
);

To obtain the process ID for a background process e.g. SMON use:

SELECT pid FROM v$process 
WHERE addr =
(
  SELECT paddr FROM v$bgprocess
  WHERE name = 'SMON'
);

To obtain the process ID for a dispatcher process e.g. D000 use:

SELECT pid FROM v$process 
WHERE addr =
(
  SELECT paddr FROM v$dispatcher
  WHERE name = 'D000'
);

To obtain the process ID for a shared server process e.g. S000 use:

SELECT pid FROM v$process 
WHERE addr =
(
  SELECT paddr FROM v$shared_server
  WHERE name = 'S000'
);

To obtain the process ID for a job queue process e.g. job 21 use:

SELECT pid FROM v$process 
WHERE addr =
(
  SELECT paddr FROM v$session
  WHERE sid = 
  (
    SELECT sid FROM dba_jobs_running WHERE job = 21
  )
);
To obtain the process ID for a parallel execution slave e.g. P000 use:
SELECT pid FROM v$px_process 
WHERE server_name = 'P000';

SETORAPNAME command

Before using ORADEBUG commands, a process must be selected. Depending on the commands to be issued, this can either be the current process or another process.

Once a process has been selected, this will be used as the ORADEBUG process until another process is selected.

The SETORAPNAME command selects a background process using the Oracle name

The syntax is:

ORADEBUG SETORAPNAME <pname>

where pname is the Oracle process name of the background process.

For example to debug the PMON background process use:

ORADEBUG SETORAPNAME PMON

CURRENT_SQL command

This command prints the currently executing statement (SQL or PL/SQL)

ORADEBUG CURRENT_SQL

TRACEFILE_NAME command

This command prints the name of the current trace file e.g.:

ORADEBUG TRACEFILE_NAME

For example:

/export/home/admin/SS92003/udump/ss92003_ora_14917.trc

This command does not work on Windows 2000 (Oracle 9.2)

SETTRACEFILEID command

This command sets the TRACEFILE_IDENTIFIER parameter:

ORADEBUG SETTRACEFILEID <suffix>

For example to set the TRACEFILE_IDENTIFIER to TEST use:

ORADEBUG SETTRACEFILEID TEST

UNLIMIT command

In Oracle 8.1.5 and below the maximum size of the trace file is restricted by default. This means that large dumps (LIBRARY_CACHE, BUFFERS) may fail.

To remove the limitation on the size of the trace file use:

ORADEBUG UNLIMIT

In Oracle 8.1.6 and above the maximum size of the trace file defaults to UNLIMITED

FLUSH command

To flush the current contents of the trace buffer to the trace file use

ORADEBUG FLUSH

CLOSE_TRACE command

To close the current trace file use:

ORADEBUG CLOSE_TRACE

SUSPEND command

This command suspends the current process

First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process

For example the command:

ORADEBUG SUSPEND

suspends the current process.

The command:

ORADEBUG RESUME

resumes the current process.

While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

This example demonstrates how to take a heap dump during a large (sorting) query

This example requires two sessions, session 1 logged on SYS AS SYSDBA and session 2 which executes the query.

In session 2 identify the PID using:

SELECT pid FROM v$process
WHERE addr IN
(
    SELECT paddr FROM v$session
    WHERE sid = dbms_support.mysid
);

In this example the PID was 12.

In session 1 set the Oracle PID using:

ORADEBUG SETORAPID 12

In session 2 start the query:

SELECT ... FROM t1 ORDER BY ....

In session 1 suspend session 2:

ORADEBUG SUSPEND

The query in session 2 will be suspended.

In session 1 run the heap dump:

ORADEBUG DUMP HEAPDUMP 1

The heapdump will show the memory structures allocated for the sort. At this point further dumps e.g. subheap dumps can be taken.

In session 1 resume session 2:

ORADEBUG RESUME

The query in session 2 will resume execution.

RESUME command

This command resumes the current process.

First select a process using SETORAPID or SETOSPID

Do not use SETMYPID as the current ORADEBUG process will hang and cannot be resumed even from another ORADEBUG process

For example the command:

ORADEBUG SUSPEND

suspends the current process.

The command:

ORADEBUG RESUME

resumes the current process.

While the process is suspended ORADEBUG can be used to take dumps of the current process state e.g. global area, heap, subheaps etc.

See SUSPEND for an example of use of the SUSPEND and RESUME commands.

WAKEUP command

To wake up a process use:

ORADEBUG WAKEUP pid

For example to wake up SMON, first obtain the PID using:

SELECT pid FROM v$process
WHERE addr = 
(
    SELECT paddr FROM v$bgprocess
    WHERE name = 'SMON'
);

If the PID is 6 then send a wakeup call using:

ORADEBUG WAKEUP 6

DUMPLIST command

To list the dumps available in ORADEBUG use the DUMPLIST command.

For example in Oracle 12.1.0.2, ORADEBUG DUMPLIST returns the following:

SQL> ORADEBUG DUMPLIST
TRACE_BUFFER_ON
TRACE_BUFFER_OFF
LATCHES
PROCESSSTATE
SYSTEMSTATE
INSTANTIATIONSTATE
REFRESH_OS_STATS
SESSION_STATS_FREELIST
PDBSTATS
SQLNET_SERVER_TRACE
CONTEXTAREA
HANGDIAG_HEADER
HEAPDUMP
HEAPDUMP_ADDR
POKE_ADDRESS
POKE_LENGTH
POKE_VALUE
POKE_VALUE0
GLOBAL_AREA
REALFREEDUMP
DUMP_SGA_METADATA
FLUSH_JAVA_POOL
POOL_SIMULATOR
PGA_DETAIL_GET
PGA_DETAIL_DUMP
PGA_DETAIL_CANCEL
PGA_SUMMARY
MODIFIED_PARAMETERS
CROSSIC
EVENT_TSM_TEST
ERRORSTACK
CALLSTACK
TEST_STACK_DUMP
TEST_GET_CALLER
RECORD_CALLSTACK
EXCEPTION_DUMP
BG_MESSAGES
ENQUEUES
KSTDUMPCURPROC
KSTDUMPALLPROCS
KSTDUMPALLPROCS_CLUSTER
SIMULATE_EOV
KSFQP_LIMIT
KSKDUMPTRACE
DBSCHEDULER
LDAP_USER_DUMP
LDAP_KERNEL_DUMP
DUMP_ALL_OBJSTATS
DUMPGLOBALDATA
HANGANALYZE
HANGANALYZE_PROC
HNGDET_MEM_USAGE_DUMP
DEAD_CLEANUP_STATE
HANGANALYZE_GLOBAL
CBDB_ENTRIES
GES_STATE
RACDUMP
CGS
OCR
CSS
CRS
SYSTEMSTATE_GLOBAL
GIPC
MMAN_ALLOC_MEMORY
MMAN_CREATE_DEF_REQUEST
MMAN_CREATE_IMM_REQUEST
MMAN_IMM_REQUEST
DUMP_ALL_COMP_GRANULE_ADDRS
DUMP_ALL_COMP_GRANULES
DUMP_ALL_REQS
DUMP_TRANSFER_OPS
DUMP_ADV_SNAPSHOTS
ADJUST_SCN
NEXT_SCN_WRAP
CONTROLF
FLUSH_CACHE
FULL_DUMPS
BUFFERS
RECOVERY
SET_TSN_P1
GLOBAL_BUFFER_DUMP
BUFFER
PIN_BLOCKS
BC_SANITY_CHECK
PIN_RANDOM_BLOCKS
SET_NBLOCKS
CHECK_ROREUSE_SANITY
DUMP_PINNED_BUFFER_HISTORY
KCBO_OBJ_CHECK_DUMP
KCB_WORKING_SET_DUMP
KCBS_ADV_INT_DUMP
KCBI_DUMP_FREELIST
SET_AFN
SET_ISTEMPFILE
FLUSH_BUFFER
FLUSH_OBJECT
SCN_AUTO_ROLLOVER_TS_OVERRIDE
REDOLOGS
ARCHIVE_ERROR
LOGHIST
REDOHDR
LOGERROR
OPEN_FILES
DATA_ERR_ON
DATA_READ_ERR_ON
DATA_ERR_OFF
BLK0_FMTCHG
UPDATE_BLOCK0_FORMAT
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_CORRUPT_REPEAT
KRB_CORRUPT_OFFSET
KRB_PIECE_FAIL
KRB_OPTIONS
KRB_FAIL_INPUT_FILENO
KRB_SIMULATE_NODE_AFFINITY
KRB_BSET_DAYS
KRB_SET_TIME_SWITCH
KRB_OVERWRITE_ACTION
KRB_CORRUPT_SPHEADER_INTERVAL
KRB_CORRUPT_SPHEADER_REPEAT
KRB_CORRUPT_SPBITMAP_INTERVAL
KRB_CORRUPT_SPBITMAP_REPEAT
KRB_CORRUPT_SPBAD_INTERVAL
KRB_CORRUPT_SPBAD_SIGNAL
KRB_UNUSED_OPTION
KRBMRSR_LIMIT
KRBMROR_LIMIT
KBRS_TRACE
KRDRSBF
KRA_OPTIONS
FBTAIL
FBINC
FBHDR
FLASHBACK_GEN
KTPR_DEBUG
IMDB_PINNED_BUFFER_HISTORY
DUMP_TEMP
DROP_SEGMENTS
TEST_SPACEBG
TREEDUMP
LONGF_CREATE
KDLIDMP
ROW_CACHE
LIBRARY_CACHE
LIBRARY_CACHE_OBJECT
CURSORDUMP
CURSORTRACE
CURSOR_STATS
XS_SESSION_STATE
SHARED_SERVER_STATE
LISTENER_REGISTRATION
JAVAINFO
KXFPCLEARSTATS
KXFPDUMPTRACE
KXFPBLATCHTEST
KXFXSLAVESTATE
KXFXCURSORSTATE
KXFRHASHMAP
WORKAREATAB_DUMP
KUPPLATCHTEST
OBJECT_CACHE
SAVEPOINTS
RULESETDUMP
RULESETDUMP_ADDR
FAILOVER
OLAP_DUMP
ASMDISK_ERR_ON
ASMDISK_READ_ERR_ON
ASMDISK_ERR_OFF
IOERREMUL
IOERREMULRNG
SELFTESTASM
ALRT_TEST
AWR_TEST
AWR_FLUSH_TABLE_ON
AWR_FLUSH_TABLE_OFF
ASHDUMP
ASHDUMPSECONDS
MMON_TEST
ATSK_TEST
HM_FW_TRACE
HM_FDG_VERS
IR_FW_TRACE
KSDTRADV_TEST
GWM_TRACE
GWM_TEST

DUMP command

To perform a dump use:

ORADEBUG DUMP <dumpname> <level> [address]

The address parameter is optional.

For example for a level 4 dump of the library cache use:

ORADEBUG SETMYPID
ORADEBUG DUMP LIBRARY_CACHE 4

EVENT command

To set an event in a process use:

ORADEBUG EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

For example to set event 10046, level 12 in Oracle process 8 use:

ORADEBUG SETORAPID 8
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

PDUMP command

To perform a dump periodically use:

ORADEBUG PDUMP [interval=<interval>]
[ndumps=<count> <dumpname> <level> [address]

The interval, ndumps and address parameters are optional.

SESSION_EVENT command

To set an event in a session use:

ORADEBUG SESSION_EVENT event TRACE NAME CONTEXT FOREVER, LEVEL level

For example:

ORADEBUG SESSION_EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12

DUMPSGA

To dump the fixed SGA use:

ORADEBUG DUMPSGA

DUMPVAR

To dump an SGA variable use:

ORADEBUG DUMPVAR SGA variable_name

For example:

    ORADEBUG DUMPVAR SGA kcbnhb

which returns the number of hash buckets in the buffer cache.

The names of SGA variables can be found in X$KSMFSV.KSMFSNAM. Variables in this view are suffixed with an underscore. For example:

kcbnhb_

PEEK

To peek memory locations use:

ORADEBUG PEEK address length

where address can be decimal or hexadecimal and length is in bytes.

For example:

ORADEBUG PEEK 0x20005F0C 12

returns 12 bytes starting at location 0x20005f0c.

POKE

To poke memory locations use:

ORADEBUG POKE address length value

where address and value can be decimal or hexadecimal and length is in bytes.

For Example:

ORADEBUG POKE 0x20005F0C 4 0x46495845
ORADEBUG POKE 0x20005F10 4 0x44205349
ORADEBUG POKE 0x20005F14 2 0x5A45

WARNING Do not use the POKE command on a production system

IPC Command

To dump information about operating system shared memory and semaphores configuration use the command:

ORADEBUG IPC

This command does not work on Windows NT or Windows 2000 (Oracle 9.2).

On Solaris, similar information can be obtained using the operating system command:

ipcs -b

PROCSTAT Command

To dump operating system process statistics use ORADEBUG PROCSTAT.

For example to dump operating system process statistics for the PMON background process use:

ORADEBUG SETORAPNAME PMON
ORADEBUG PROCSTAT

Sample output:

----- Dump of Process Statistics -----
User time used = 65
System time used = 30
Maximum resident set size = 28452
Integral shared text size = 0
Integral unshared data size = 0
Integral unshared stack size = 0
Page reclaims = 7480
Page faults = 7
Swaps = 0
Block input operations = 1472
Block output operations = 32
Socket messages sent = 0
Socket messages received = 0
Signals received = 0
Voluntary context switches = 2555
Involuntary context switches = 387

Dumping the SGA

In some versions it is possible to dump the entire SGA to a file.

Freeze the instance using:

ORADEBUG FFBEGIN

Dump the SGA to a file using:

ORADEBUG SGATOFILE directory

Unfreeze the instance using:

ORADEBUG FFRESUMEINST

The above commands work in Oracle 9.0.1 and 9.2.0 on Solaris, but fail in both versions in Windows 2000.