What is ORADEBUG:
ORADEBUG is a debugger tool, can be used for (tracing any session, dump DB memory structure, suspend/resume a session and the most useful use is to analyze the hang when an instance is hanging by creating a report shows the blocked/blocker sessions).
How to use ORADEBUG:
If you are lazy to do it yourself, there is a script can do it for you: 👍
If you want to do it yourself then follow these steps:
Login to SQLPLUS AS SYSDBA or in case you cannot login in a normal way (e.g. database is hanging and it's not accepting new sessions) you can use the prelim option which will allow you to connect to SGA without opening a session. Note: prelim option cannot be used in an already connected session.
# sqlplus /nolog
SQL> set _prelim on
SQL> conn / as sysdba
# sqlplus -prelim "/ as sysdba"
Second set your PID:
SQL> oradebug setmypid
To perform a DB cluster wide analysis:
SQL> oradebug setinst all
To make the tracefile size unlimited:
SQL> oradebug unlimit
To Run the Hang Analysis: <The most useful use for ORADEBUG>
SQL> oradebug -g def hanganalyze <level#>
-> This will create a logfile and will give you it's path.
-Available levels are:
10 Dump all processes (IGN state)
5 Level 4 + Dump all processes involved in wait chains (NLEAF state)
4* Level 3 + Dump leaf nodes (blockers) in wait chains (LEAF,LEAF_NW,IGN_DMP state)
3 Level 2 + Dump only processes thought to be in a hang (IN_HANG state)
1-2 Only HANGANALYZE basic output, no process dump at all
*Level 4 and above are resource intensive + large output, which can impact the instance performance.
and thus Oracle recommends to use level 3:
SQL> ORADEBUG -g def hanganalyze 3
How to read HANGANALYZE trace:
Generally: Read the log and search for these keywords "final blocker" if not found search for "blocked by" & "blocking".
Note: Open chains consist of processes waiting for a resource held by a blocker. These are not true hangs and can be resolved by killing the holding session and allowing the blocked processes to proceed. This can be useful when a process is holding a latch, a frequent example is the library latch, and other processes are waiting for the latch.
Check section "State of ALL nodes" At the end of the HANGANALYZE log :
The data under this line means:
"cnode" column means: cluster node number
"sid" is the Oracle session ID!
"sess_srno" column means: SERIAL#
"ospid" The operating system process ID
"state" columns have the following values:
A session with status "LEAF" or "LEAF_NW" means a session blocking others "Good candidate for kill" specially "LEAF".
Session with status "NLEAF" means waiting session and appear to be hanging.
Session with status "IN_HANG" means there is a problem!
Session with status "IGN" or "IGN_DMP" means the session is idle.
Caution: Do not kill critical processes like SMON or PMON or the instance will get terminated.
To Check the current trace file name:
SQL> oradebug tracefile_name
Flush any pending writes to the trace file and close it:
SQL> oradebug flush
SQL> oradebug close_trace
To trace a specific session with it's OS PID:
SQL> oradebug setospid
To trace a specific session with it's Oracle PID:
SQL> oradebug setorapid
I've created a script that can make this job easy:
Using ORADEBUG to freeze/suspend a session:
First, you have to get the PID for that session and set ORADEBUG as it:
SQL> oradebug setospid 12518
*12518 is the PID for the session you want to kill
Second Freeze/Hang the session even it running in a middle of something it will freeze:
SQL> oradebug suspend
To UnFreeze/Resume the session to continue its work:
SQL> oradebug resume
Trace Oracle Clusterware:
Trace CRS events:
SQL> oradebug dump crs 3
Trace CSS behaviour:
SQL> oradebug dump css 3
SQL> oradebug dump ocr 3
Using ORADEBUG to poke/wake up the SMON or PMON:
First: you need to know the PID of SMON process:
SQL> select pid from v$process p, v$bgprocess b where b.paddr = p.addr and name='SMON';
Second: Wake up the SMON using its pid:
SQL> oradebug wakeup 13
Same thing for PMON, if the PMON is not timely cleaning up the killed sessions traces & locks, you can poke it using oradebug:
First: Get the PMON's PID using this statement:
SQL> select pid from v$process p, v$bgprocess b where b.paddr = p.addr and name='PMON';
Second: Wake up the PMON using its PID:
SQL> oradebug wakeup 2
-You can execute oradebug from more than one sqlplus sessions.
-ORADEBUG utility is poorly documented by Oracle, because it's dangerous and can do a potential damage to the database, this is why it should be used under Oracle support supervision.