What to collect if your database is hanging...

Has your database hung? Hopefully, it has not and it never will. But if it does, support needs following information:

1. Alert log at the time of the hang

2. Snapshot of AWR during the time of the hang

3. Ash report during the time of the hang

4. If possible, generate system states and hanganalyze at the time of the issue:

(a) Non-RAC

        $ sqlplus '/ as sysdba'

          oradebug setmypid

          oradebug unlimit

          oradebug dump systemstate 266

          wait 90 seconds

          oradebug dump systemstate 266

          wait 90 seconds

          oradebug dump systemstate 266

          quit

(b) Wait at least 1 minute and then repeat step 1 to take a second set of dumps.

Run hanganalyze as following: