The system hang live, how to do?

If the system HANG , it is important to analyze the cause of HANG by doing a system state DUMP at this time. However, in many cases, the system hang can not log in, then how to analyze it.

Metalink doc:359536.1 offers some good ways to share with everyone here:

supported by Database version: 9.2.0.1-10.2

Platform (part of UNIX platform, partly for all platforms)

1, 10G ago, using tools such as Dbx,gdb:

Find the PID (Ps-ef|grep local) of a foreground process, and then trace with GDB $ORACLE _home/bin/oracle

Oracle@gurufl02:~> Ps-ef|grep Local
Oracle 26358 26357 0 09:52? 00:00:00 oracleora9 (description= (Local=yes) (address= (PROTOCOL=BEQ))
Oracle 27848 27810 0 10:41 pts/3 00:00:00 grep Local
oracle@gurufl02:~> gdb $ORACLE _home/bin/oracle 26358

Then execute: Print KSUDSS (10)
(gdb) Print Ksudss (10)
[Switching to Thread 4154533552 (LWP 26358)]
Warning:unable to restore previously selected frame.
$ = 181773020
Current Language:auto; Currently ASM

A trace file is generated at this time under Udump or Bdump. which contains system state DUMP

In addition to the systemstate dump via print KSUDSS (10), you can also perform the following dump

Print ksdhng (3,1,0) is equivalent to Oradebug hanganalyze 3

Print Ksudps (10) is equivalent to Oradebug dump Processstate 10

Print curdmp () is equivalent to Oradebug call CURDMP (i.e. Oradebug dump Cursordump)

Print KSDTRC (4) is equivalent to Oradebug dump events 4 (where the parameters represent Level,1–session,2–process,4–system)

Print Ksdsel (10046,12) – equivalent to attach process setting 10046 event level 12

Print SKDXIPC () – equivalent to Oradebug IPC

Print Skdxprst () – equivalent to Oradebug Procstat

Of course, if you can use Oradebug, you should use Oradebug, after all, more convenient, and more secure.

2, 10G under Sqlplus-prelim

If 10G, you can force a login using the Sqlplus-prelim option

Export ORACLE_SID=ORA9
Sqlplus-prelim '/As Sysdba '
Oradebug Setmypid
Oradebug Unlimit;
Oradebug Dump SystemState 10

3, if none of the above, you can use Pstack to take out the stack information, you can also use truss, TUSC and other tools to see what is waiting

database Hang Live is a headache, how to find the reasons for hang, is a DBA must face the subject. When databases hang, most DBAs tend to analyze them through the v$session_wait view. In fact , Oracle has a very effective tool----hanganalyze. Hanganalyze can very clearly tell the DBA about the hang information to facilitate further analysis by the DBA.

Hanganalyze is provided from Oracle 8i R2 (8.1.6) and is simple to use:

ALTER session SET EVENTS ' immediate trace name hanganalyze level <level> ';

Or

Oradebug Hanganalyze <level>

Like what:

Sql>oradebug Setmypid;

Sql>oradebug Hanganalyze 3;

For <level>:

      10     Dump All Processes (IGN state)
       5      Level 4 + Dump All processes involved in wait chains (nleaf state)
       4      Level 3 + the Dump leaf nodes (blockers) in the wait chains (LEAF,LEAF_NW , ign_dmp State)
      3      Level 2 + Dump only processes thought To is in a hang (In_hang state)
    1-2    only hanganalyze output, no process dump at a LL