Oracle Oradebug Utility Tips And Tricks

 Oradebug

Introduction

Oracle utility is called oradebug. This tool is primarily used by Oracle worldwide customer support .With oradebug utility you can literally see the database engine.

The oradebug is especially useful when things go very bad – e.g. the database just stops, hangs, or the database keeps crashing with the ORA-0600 error!

To run this tool you must have administrator privileges.
Among the many useful things that can be done with oradebug are:
– enabling/disabling the SQL tracing for another user’s session.
– suspending intensive processes
– finding information about shared memory and semaphores
– closing the trace file so that new one can be generated
– manipulating and dumping internal structures
– wake up processes etc.

 

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

 

 

Background Processes List

SELECT b.name, p.pid FROM gv$bgprocess b, gv$process p WHERE b.paddr = p.addr ORDER BY 1;

Dispatcher Processes List

SELECT d.name, p.pid FROM gv$dispatcher d, gv$process p WHERE d.paddr = p.addr;

Job Queue Process List

SELECT s.paddr, s.sid, j.job FROM gv$session s, dba_jobs_running j WHERE s.sid = j.sid;

SELECT pid FROM gv$process  WHERE addr = ’46’;

Parallel Execution Slave  Processes List

SELECT pid, server_name, status FROM gv$px_process;

Shared Server Processes List

SELECT s.name, p.pid FROM gv$shared_server s, gv$process p WHERE s.paddr = p.addr;

SGA Variables List

SELECT ksmfsnam FROM x$ksmfsv WHERE ksmfsnam LIKE ‘%\_’ ESCAPE ‘\’;

Switches

CALL

Invoke function with arguments

oradebug call [-t count] <func> [arg1]…[argn]ora

SQL> oradebug call ksmget_sgamaxalloc
CLOSE_TRACE

Close the trace file

oradebug close_trace

SQL> oradebug close_trace
CORE

Dump core without crashing process

oradebug core

SQL> oradebug core
DUMPSGA

Dump fixed SGA

oradebug dumpsga [<bytes>]

SQL> oradebug dumpsga 

c:\oracle\diag\rdbms\orabase\orabase\trace\orabase_ora_2120.trc
DUMPTYPE

Print/dump an address with type info

oradebug dumptype <address> <type> <count>
TBD
DUMPVAR

Print/dump a fixed PGA/SGA/UGA variable

oradebug dumpvar <pga|sga|uga> <name> [level]

SQL> oradebug setmypid

SQL> oradebug dumpvar SGA kcbnbh
EVENT

Set trace event in process

oradebug EVENT <event> TRACE NAME CONTEXT FOREVER, LEVEL <level>

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
FFBEGIN

Flash Freeze the Instance

oradebug ffbegin

SQL> oradebug ffbegin
FFDEREGISTER

FF deregister instance from cluster

oradebug ffderegistger

SQL> oradebug ffderegister
FFRESUMEINST

Resume a flash frozen instance

oradebug ffresumeinst

SQL> oradebug ffresumeinst
FFSTATUS

Flash freeze status of instance

oradebug ffstatus

SQL> oradebug ffstatus

FFTERMINST

Call exit and terminate instance

oradebug ffterminst

SQL> oradebug ffterminst
FLUSH

Flush pending writes to trace file

oradebug flush

SQL> oradebug flush
HANGANALYZE

Analyze system hang for stand-alone

oradebug hanganalzye [level] [syslevel]

SQL> oradebug setmypid

SQL> oradebug unlimit

SQL> oradebug tracefile_name

SQL> oradebug hanganalyze 4

Analyze system hang for RAC

SQL> oradebug setmypid

SQL> oradebug -g def hanganalyze 1

SQL> oradebug flush
HELP

Describe one or all commands

oradebug help [<command>]

SQL> oradebug help

SQL> oradebug help flush
IPC

Dump IPC information

oradebug ipc

SQL> oradebug setmypid
Statement processed.

SQL> oradebug ipc
Information written to trace file C:\ORACLE\diag\rdbms\orabase\orabase\trace\orabase_ora_10988.trc
IPC_CHECKSUM

Enable/Disable IPC Checksumming

oradebug ipc_checksum<light/medium/full>

SQL> oradebug ipc_checksum full
IPC_TRACE

Modify IPC trace flags

oradebug ipc_trace<module> <trace_flags> <trace_level>
TBD
LKDEBUG

Invoke global enqueue service debugger

oradebug lkdebug

SQL> oradebug lkdebug
MAPCOWSGA

Map SGA as COW

oradebug mapcowsga <SGA dump dir>

SQL> oradebug mapcowsga "c:\temp"
NSDBX

Invoke CGS name-service debugger

oradebug nsdbx

SQL> oradebug nsdbx
PDUMP

Invoke named dump periodically

PDUMP [interval=<interval>] [ndumps=<count>] <dump_name> <lvl> [addr]
TBD
PEEK

Print/Dump memory

oradebug peek <addr> <len> [level]

SQL> oradebug peek oradebug peek 0x075731F8 12
POKE

Modify memory. Never perform this function on a production database!

oradebug poke <addr> <len> <value>

SQL> oradebug poke 0x20005F0C 4 0x46495845
PROCSTAT

Dump process statistics oradebug procstat

SQL> oradebug setmypid

SQL> oradebug procstat
SQL> select pid,name from v$process p, v$bgprocess b where b.paddr = p.addr;

SQL> 

PID NAME
-- -----
2   PMON
3   CLMN
4   PSP0
5   VKTM
6   GEN0
7   MMAN
8   OFSD
9   GEN1
10  SCMN
11  DIAG
12  SCMN

PID NAME
-- -----
13  DBRM
14  VKRM
15  SVCB
16  PMAN
17  DIA0
18  DBW0
19  LGWR
20  CKPT
21  SMON
22  SMCO
23  RECO

PID NAME
-- -----
24  W001
25  LREG
27  PXMN
28  FENC
29  MMON
30  MMNL
32  TMON
33  W003
34  TT00
35  ARC0
36  TT01

PID NAME
-- -----
37  ARC1
38  ARC2
39  ARC3
40  TT02
41  AQPC
42  W005
43  QM02
44  W007
45  W000
46  Q003
47  CJQ0

PID NAME
-- -----
49  Q002
54  W004
55  W006

47 rows selected.

SQL>
RESUME

Resume execution oradebug resume

SQL> oradebug resume
SESSION_EVENT

Set trace event in session

oradebug session_event <text>

SQL> oradebug session_event 10053 TRACE NAME CONTEXT FOREVER, LEVEL 1
SETINST

Set instance list

oradebug setinst <instance# .. | all>

SQL> oradebug setinst "1"
SETMYPID

Sets the oradebug PID to the current process

oradebug setmypid

SQL> oradebug setmypid
SETORAPID

Set PID of Oracle process to debug

oradebug setorapid <orapid> ['force']

 

SETORAPNAME

Set Oracle process name to debug

oradebug setorapname <orapname>

 

 

SETOSPID

Set OS pid of process to debug. The operating system process ID is the PID on Unix systems and the thread number for Windows systems

oradebug setospid <ospid>

Do not use as it often fails. Use setorapid instead.
SETTRACEFILEID

Set tracefile identifier oradebug settracefileid <identifier name>

SQL> oradebug settracefileid odebug
SETVAR

Modify a fixed PGA/SGA/UGA variable

oradebug setvar <pga|sga|uga> <name> <value>

SQL> oradebug setvar SGA kcfdfk 200
SGATOFILE

Dump SGA to file oradebug sgatofile <SGA dump dir>

SQL> oradebug ffbegin

SQL> oradebug sgatofile "c:\temp"

SQL> oradebug ffresumeinst
SHORT_STACK

Get abridged OS stack

oradebug short_stack

SQL> oradebug short_stack
SHOW

Show watchpoints

oradebug show <local|global|target> watchpoint <id>

SQL> oradebug show global watchpoints
SKDSTTPCS

Helps translate PCs to names

oradebug skdsttpcs <ifname> <ofname>
TBD
SUSPEND

Suspends the current process

oradebug suspend

SQL> oradebug suspend
TRACEFILE_NAME

Get trace file name

Will not return a value on Windows systems

oradebug tracefile_name

SQL> oradebug tracefile_name
UNLIMIT

Unlimit the size of the trace file

oradebug unlimit

SQL> oradebug unlimit
WAKEUP

Wake up Oracle process

oradebug wakeup <orapid>

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

 

Leave a Reply

Your email address will not be published. Required fields are marked *