Hanganalyze and Systemstate

Hanganalyze and Systemstate

 

 

Collection commands for Hanganalyze and Systemstate: Non-RAC:
Sometimes, database may actually just be very slow and not actually hanging. It is therefore recommended,  where possible to get 2 hanganalyze and 2 systemstate dumps in order to determine whether processes are moving at all or whether they are "frozen".

Hanganalyze
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- Wait one minute before getting the second hanganalyze
oradebug hanganalyze 3
oradebug tracefile_name
exit

Systemstate
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 266
oradebug dump systemstate 266
oradebug tracefile_name
exit

 

Collection commands for Hanganalyze and Systemstate: RAC
There are 2 bugs affecting RAC that without the relevant patches being applied on your system, make using level 266 or 267 very costly. Therefore without these fixes in place it highly unadvisable to use these level

For information on these patches see:
Document 11800959.8 Bug 11800959 - A SYSTEMSTATE dump with level >= 10 in RAC dumps huge BUSY GLOBAL CACHE ELEMENTS - can hang/crash instances 
Document 11827088.8 Bug 11827088 - Latch 'gc element' contention, LMHB terminates the instance 
  
Note:  both bugs are fixed in 11.2.0.3.
  
Collection commands for Hanganalyze and Systemstate: RAC with fixes for bug 11800959 and bug 11827088
For 11g:
sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug  unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 266
oradebug -g all dump systemstate 266
exit
Collection commands for Hanganalyze and Systemstate: RAC without fixes for Bug 11800959 and Bug 11827088
sqlplus '/ as sysdba'
oradebug setorapname reco
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
exit
For 10g, run oradebug setmypid instead of oradebug setorapname reco:
sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
oradebug -g all hanganalyze 3
oradebug -g all dump systemstate 258
oradebug -g all dump systemstate 258
exit
In RAC environment, a dump will be created for all RAC instances in the DIAG trace file for each instance.

 

 

 

数据库hang住如何收集信息

 

数据库hang的时候，建议尽量收集以下信息：

1.hanganalyze和systemstate dumps

2.AWR报告

3.最近的RDA

如果是CDB环境，要确认是CDB级别的hang还是PDB级别的hang。如果是PDB级别的hang，只需要收集PDB的信息。如果无法确认，建议收集一下CDB的信息。

 

hanganalyze：确认数据库是否真的hang或只是运行慢。提供了hang涉及的所有进程信息。

systemstate dump：收集数据库中所有进程正在做什么。

 

如果不能登陆，使用sqlplus -prelim '/as sysdba'登陆

从11.2.0.2起，使用"sqlplus -prelim"登陆后，hanganalyze不再有内容输出，因为需要一个进程状态对象和一个会话状态对象。所以执行hanganalyze分析后，只是显示执行成功。如下示例：

1 2	SQL> oradebug hanganalyze 3 Statement processed.

并且trace文件中会包含以下内容：

1 2 3 4

HANG ANALYSIS:   ERROR: Can not perform hang analysis dump without a process state object and a session state object. ( process=(nil), sess=(nil) )

 

1.非RAC环境
执行hanganalyze分析（有时候可能只是运行较慢，并非hang了，所以键值执行两次hanganalyze）

1 2 3 4 5 6 7

sqlplus '/as sysdba' oradebug setmypid oradebug unlimt oradebug hanganalyze 3   #level 3表示收集hang chain中涉及的所有进程的栈信息 --等一分钟后再次执行hanganalyze分析 oradebug hanganalyze 3 oradebug tracefile_name

执行systemstate dump分析

1 2 3 4 5 6

sqlplus '/as sysdba' oradebug setmypid oradebug unlimit oradebug dump systemstate 266 oradebug dump systemstate 266 oradebug tracefile_name

 

通常是hanganalyze和systemstate dump一起做：

1 2 3 4 5 6 7 8 9

sqlplus '/as sysdba' oradebug setmypid oradebug unlimt oradebug hanganalyze 3 --等一分钟后再次执行hanganalyze分析 oradebug hanganalyze 3 oradebug dump systemstate 266 oradebug dump systemstate 266 oradebug tracefile_name

 

2.RAC环境
在RAC环境，会在每个实例的diag trace目录下创建dump文件

 

RAC10G,使用setmypid

1 2 3 4 5 6 7

sqlplus '/as sysdba' oradebug setmypid oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 258 oradebug -g all dump systemstate 258

 

在RAC11G环境，因为有两个bug(bug 11800959 、bug 11827088)，导致执行hanganalyze和systemstate dump时，使用266、267级别时成本很高。所以在没有安装这两个补丁前，建议慎用。在11.2.0.3中补丁已被修复。

RAC11G,如果已经安装了上面的两个补丁：

1 2 3 4 5 6 7

sqlplus '/as sysdba' oradebug setorapname reco oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 266 oradebug -g all dump systemstate 266

RAC11G,如果没有安装了上面的两个补丁：

1 2 3 4 5 6 7

sqlplus '/as sysdba' oradebug setorapname reco oradebug unlimit oradebug -g all hanganalyze 3 oradebug -g all hanganalyze 3 oradebug -g all dump systemstate 258 oradebug -g all dump systemstate 258