如何处理HANG住的DB

如何处理HANG住的DB

1. 生成 systemstate dump:
SQL>sqlplus -prelim / as sysdba
SQL> alter session set max_dump_file_size = unlimited;
SQL> alter session set events 'immediate trace name systemstate level 10';
Wait for some 30 seconds
SQL> alter session set events 'immediate trace name systemstate level 10';
Wait for some 30 seconds
SQL> alter session set events 'immediate trace name systemstate level 10;

2. 开一个新的SESSION:
SQL>sqlplus -prelim / as sysdba
SQL>oradebug setmypid
SQL>oradebug unlimit;
SQL>oradebug dump hanganalyze 3
Wait for 30 seconds
SQL>oradebug dump hanganalyze 3
Wait for 30 seconds
SQL>oradebug dump hanganalyze 3

对生成的文件进行分析。

附：system state dump when connection to the instance is not possible
There are several ways of getting more information when connection to an instance is not possible by sqlplus. It is useful to collect as much information about a hang before clearing the instance.