ANBOB™

used the gdb (GNU Debugger) to generate oracle system state dumps on Linux systems

A system state dump is trace file containing a snapshot of all processes running in a database. It is used to diagnose hang issues and has more information compared to a hanganalyze dump.

If you can connect to the database you can dump it using

oradebug dump systemstate

If you cannot connect to the database instance you can use a session less connection in 10g using

sqlplus -prelim / as sysdba
oradebug setmypid
oradebug unlimit;
oradebug dump systemstate 10

If you want only the short stack of each process in the instance
you need to use

SQL> oradebug dump systemstate 256
Statement processed.

Prior to 10g you can use a unix debugger like gdb, adb or dbx to attach to a shadow process and dump the system state using the function ksudss which is the subroutine in the Oracle source code which does the same.

[oracle@db231 ~]$ ps -ef|grep $ORACLE_SID|grep LOCAL
oracle    6921     1  0 14:26 ?        00:00:00 oracleanbob (LOCAL=NO)

[oracle@db231 ~]$ gdb
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-42.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "x86_64-redhat-linux-gnu".
For bug reporting instructions, please see:
<http://www.gnu.org/software/gdb/bugs/>.
(gdb) attach 6921
Attaching to process 6921
... -- output tracated

(gdb) print ksudss(10)
$1 = 0
(gdb) detach
Detaching from program: /u01/app/oracle/product/11.2.0/db_1/bin/oracle, process 6921
(gdb) quit
[oracle@db231 ~]$ cd -
/u01/app/oracle/diag/rdbms/anbob/anbob/trace

tip:
Since in the above case we have attached to a user server process the trace file containing the dump will be in bdump else the trace file would be in udump on 10g ,However it will be in DIAG directory on 11g .

[oracle@db231 ~]$ echo $ORACLE_BASE
/u01/app/oracle

[oracle@db231 ~]$ adrci exec=show homes
ADR Homes: 
diag/tnslsnr/db231/listener
diag/rdbms/anbob/anbob
diag/rdbms/ora11204/ora11204
diag/rdbms/ora12c/ora12c

[oracle@db231 trace]$ ll -rth|tail|grep 6921
-rw-r----- 1 oracle oinstall 167K Feb 12 14:27 anbob_ora_6921.trm
-rw-r----- 1 oracle oinstall 939K Feb 12 14:27 anbob_ora_6921.trc

Analyze systemstate tracefile using awk utility like following:

[oracle@db231 ~]$ awk -f ass1033.awk /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_6921.trc

Starting Systemstate 1
...........................
Ass.Awk Version 1.0.33
~~~~~~~~~~~~~~~~~~~~~~
Source file : /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_ora_6921.trc

System State 1  (2014-02-12 14:27:42.979)
~~~~~~~~~~~~~~   ~~~~~~~~~~~~~~~~~~~~~~~
1:                                      [DEAD] 
2:  waiting for 'pmon timer'            
3:  waiting for 'rdbms ipc message'     
...

note:
Please use these commands with caution. On busy systems with a large number of processes these commands can take quite a while. the Oracle executable needs to be re-linked on the HP-UX platform to enable the gdb debugger to generate system state dumps by attaching to an Oracle process.You can see all the gory details in Metalink Note 273324.1.

print ksdhng(3,1,0) equivalent to oradebug hanganalyze 3
print ksudps(10) equivalent to oradebug dump processstate 10
print ksdsel(10046,12) equivalent to attached process set 10046 event level 12
print skdxipc() equivalent to oradebug ipc
print skdxprst() equivalent to oradebug procstat

Generate HANGANALYZE DUMP

(gdb) attach 6921
(gdb) print ksdhng(3,1,0)
$1 = 0
(gdb) detach
Detaching from program: /u01/app/oracle/product/11.2.0/db_1/bin/oracle, process 6921
(gdb) shell

[oracle@db231 trace]$ vi anbob_ora_6921.trc

*** 2014-02-12 15:08:04.942
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): anbob.anbob
  oradebug_node_dump_level: 3
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 15:08:04 ]
      NOTE: scheduling delay has not been sampled for 0.579103 secs    0.000000 secs from [ 15:08:00 - 15:08:05 ], 5 sec avg
    0.000000 secs from [ 15:07:05 - 15:08:05 ], 1 min avg
    0.000000 secs from [ 15:03:05 - 15:08:05 ], 5 min avg
===============================================================================

Chains most likely to have caused the hang:

...

— my case oracle 11g r2 on linux OEL 5.8–

打赏

微信扫一扫，打赏作者吧～