11.2.0.2在数据库无响应的情况如何获取dump信息？

overmars

有个oracle 11.2.0.2的数据库无响应，sqlplus  / as sysdba也不能登录到数据库，于是
执行如下命令想分析hang信息：

$ sqlplus -prelim / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Mon Apr 16 16:05:20 2012
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug hanganalyze 3;
Statement processed.
SQL> oradebug tracefile_name;
/oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_2449638.trc
SQL> exit
Disconnected from ORACLE
$ cat /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_2449638.trc|more
Trace file /oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_2449638.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_2
System name:    AIX
Node name:      node1
Release:        1
Version:        6
Instance name: orcl1
Redo thread mounted by this instance: 1
Oracle process number: 0
Unix process pid: 2449638, image: [email=oracle@node1]oracle@node1[/email]

*** 2012-04-16 16:05:25.990
Processing Oradebug command 'setmypid'
*** 2012-04-16 16:05:25.990
Oradebug command 'setmypid' console output: <none>
*** 2012-04-16 16:05:39.556
Processing Oradebug command 'hanganalyze 3'
===============================================================================
HANG ANALYSIS:

ERROR: Can not perform hang analysis dump without a process
       state object and a session state object.
  ( process=0x0, sess=0x0 )                                这里提示无法获取hang的dump!         

===============================================================================
*** 2012-04-16 16:05:39.556
Oradebug command 'hanganalyze 3' console output: <none>
*** 2012-04-16 16:05:44.479
Processing Oradebug command 'tracefile_name'
*** 2012-04-16 16:05:44.479
Oradebug command 'tracefile_name' console output:
/oracle/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_2449638.trc


后来查看了metalink 发现11.2.0.2确实不能通过-prelim来执行hang info 的dump，请问是否有替代方法呢？

overmars

谢谢版主，涨见识了！

szkking

很多时候，只能看到一些简单的东西，比如锁，如果进程很多，系统比较大，则看起来非常麻烦，很多时候找不到真实的原因，
而且很慢，
请问版主，有什么文章介绍的是这种查看HANG的快速定位方法么？

Maclean Liu(刘相兵

[oracle@vrh8 ~]$ ps -ef|grep ora_
oracle    3545     1  0 Apr10 ?        00:01:13 ora_pmon_G10R21
oracle    3547     1  0 Apr10 ?        00:00:29 ora_psp0_G10R21
oracle    3549     1  0 Apr10 ?        00:00:31 ora_mman_G10R21
oracle    3551     1  0 Apr10 ?        00:00:51 ora_dbw0_G10R21
oracle    3553     1  0 Apr10 ?        00:01:21 ora_lgwr_G10R21
oracle    3555     1  0 Apr10 ?        00:02:25 ora_ckpt_G10R21
oracle    3557     1  0 Apr10 ?        00:00:14 ora_smon_G10R21
oracle    3559     1  0 Apr10 ?        00:00:00 ora_reco_G10R21
oracle    3561     1  0 Apr10 ?        00:01:45 ora_cjq0_G10R21
oracle    3563     1  0 Apr10 ?        00:01:35 ora_mmon_G10R21
oracle    3565     1  0 Apr10 ?        00:02:31 ora_mmnl_G10R21
oracle    3567     1  0 Apr10 ?        00:00:00 ora_d000_G10R21
oracle    3569     1  0 Apr10 ?        00:00:01 ora_s000_G10R21
oracle    3573     1  0 Apr10 ?        00:00:32 ora_rvwr_G10R21
oracle    3580     1  0 Apr10 ?        00:00:06 ora_arc0_G10R21
oracle    3582     1  0 Apr10 ?        00:00:01 ora_arc1_G10R21
oracle    3586     1  0 Apr10 ?        00:00:04 ora_qmnc_G10R21
oracle    3608     1  0 Apr10 ?        00:00:00 ora_q000_G10R21
oracle    3610     1  0 Apr10 ?        00:00:03 ora_q001_G10R21
oracle    5722  5698  0 22:34 pts/0    00:00:00 grep ora_

找一个 非 fatal的后台进程 或者LOCAL 进程都可以

[oracle@vrh8 ~]$ gdb $ORACLE_HOME/bin/oracle 3608

GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-37.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html&gt;
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/&gt;...
Reading symbols from /s01/oracle/product/10.2.0/db_1/bin/oracle...(no debugging symbols found)...done.
Attaching to program: /s01/oracle/product/10.2.0/db_1/bin/oracle, process 3608
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libskgxp10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libskgxp10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libhasgen10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libhasgen10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libskgxn2.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libskgxn2.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libocr10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libocr10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libocrb10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libocrb10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libocrutl10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libocrutl10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libjox10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libjox10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libclsra10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libclsra10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libdbcfg10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libdbcfg10.so
Reading symbols from /s01/oracle/product/10.2.0/db_1/lib/libnnz10.so...(no debugging symbols found)...done.
Loaded symbols for /s01/oracle/product/10.2.0/db_1/lib/libnnz10.so
Reading symbols from /usr/lib64/libaio.so.1...(no debugging symbols found)...done.
Loaded symbols for /usr/lib64/libaio.so.1
Reading symbols from /lib64/libdl.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libdl.so.2
Reading symbols from /lib64/libm.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libm.so.6
Reading symbols from /lib64/libpthread.so.0...(no debugging symbols found)...done.
[Thread debugging using libthread_db enabled]
Loaded symbols for /lib64/libpthread.so.0
Reading symbols from /lib64/libnsl.so.1...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnsl.so.1
Reading symbols from /lib64/libc.so.6...(no debugging symbols found)...done.
Loaded symbols for /lib64/libc.so.6
Reading symbols from /lib64/ld-linux-x86-64.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/ld-linux-x86-64.so.2
Reading symbols from /lib64/libnss_files.so.2...(no debugging symbols found)...done.
Loaded symbols for /lib64/libnss_files.so.2
0x00000035bf4d591a in semtimedop () from /lib64/libc.so.6



(gdb) print ksdhng(3,1,0)                             ==》　等价于 oradebug  hanganalyze 3
$1 = -1837233856


(gdb) print ksudss(10)                                   ==> 等价于 oradebug dump systemstate 266
$2 = -1837233856

(gdb) print ksdsel(10046,12)                        ==> 等价于 10046 trace level 12
$3 = 0


/s01/admin/G10R21/bdump/g10r21_q000_3608.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /s01/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      vrh8.oracle.com
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 21:02:33 EDT 2011
Machine:        x86_64
Instance name: G10R21
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 3608, image: oracle@vrh8.oracle.com (q000)

*** SERVICE NAME:(SYS$BACKGROUND) 2012-04-16 22:37:20.438
*** SESSION ID:(142.3) 2012-04-16 22:37:20.438
*** 2012-04-16 22:37:20.438
==============
HANG ANALYSIS:
==============
Open chains found:
Other chains found:

====================
END OF HANG ANALYSIS
====================
*** 2012-04-16 22:37:35.765
===================================================
SYSTEM STATE
------------

overmars

11.2.0.2 版本执行dump systemstate是可以的，就是不能执行hanganalyze。
出现问题时候，执行hanganalyze的开销比systemstate小很多。

Maclean Liu(刘相兵

请参考

如何使用gdb工具对Oracle系统状态(systemstate)做trace

http://www.oracledatabase12g.com ... %E5%81%9Atrace.html