ORADEBUG使用詳解

SQL> oradebug setmypid //This will set the current session's pid.

SQL> oradebug dump events 1 //This will dump the events information in the tracefile based on the level

SQL> oradebug tracefile_name //This will return the trace file generated for this event.

[@more@]

Tools

ORADEBUG

ORADEBUG is an undocumented debugging utility supplied with Oracle

For more general information see ORADEBUG introduction

In Oracle 9.2 commands include

HELP SETMYPID SETORAPID SETOSPID TRACEFILE_NAME UNLIMIT FLUSH CLOSE_TRACE SUSPEND RESUME WAKEUP DUMPLIST DUMP EVENT SESSION_EVENT DUMPSGA DUMPVAR PEEK POKE IPC 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 9.2.0.1 (Windows 2000) the command

  ORADEBUG HELP

returns the following

Command	Arguments	Description
HELP	[command]	Describe one or all commands
SETMYPID		Debug current process
SETOSPID		Set OS pid of process to debug
SETORAPID	['force']	Set Oracle pid of process to debug
DUMP	[addr]	Invoke named dump
DUMPSGA	[bytes]	Dump fixed SGA
DUMPLIST		Print a list of available dumps
EVENT		Set trace event in process
SESSION_EVENT		Set trace event in session
DUMPVAR	[level]	Print/dump a fixed PGA/SGA/UGA variable
SETVAR		Modify a fixed PGA/SGA/UGA variable
PEEK	[level]	Print/Dump memory
POKE		Modify memory
WAKEUP		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
LKDEBUG		Invoke global enqueue service debugger
NSDBX		Invoke CGS name-service debugger
-G		Parallel oradebug command prefix
-R		Parallel oradebug prefix (return output)
SETINST		Set instance list in double quotes
SGATOFILE		Dump SGA to file; dirname in double quotes
DMPCOWSGA		Dump & map SGA as COW; dirname in double quotes
MAPCOWSGA		Map SGA as COW; dirname in double quotes
HANGANALYZE	[level]	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		Helps translate PCs to names
WATCH		Watch a region of memory
DELETE	watchpoint	Delete a watchpoint
SHOW	watchpoints	Show watchpoints
CORE		Dump core without crashing process
UNLIMIT		Unlimit the size of the trace file
PROCSTAT		Dump process statistics
CALL	[arg1] ... [argn]	Invoke function with arguments

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

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';

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

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)

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

    ORADEBUG DUMPLIST pid 

For example in Oracle 9.2 (Windows 2000) this command returns the following

Dump Name 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_BUFFER 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

DUMP command

To perform a dump use

    ORADEBUG DUMP dumpname level 

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

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 

e.g.

    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 e.g.

    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

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

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

This works in Oracle 9.0.1 and 9.2.0 on Solaris, but fails in both versions in Windows 2000