Steps to perform hang analysis in Oracle when database is inaccessible or hung state

Today, we had an issue where in which one of our rac database was hung.since the database was not responding to any of the commands the only option was to terminate the instance by the operating system commands. Before aborting the database we decided to collect as much disinformation possible so that or it will support service can find the root cause. In this can of situations Oracle recommends to collect hang analysis data for further troubleshooting.

Steps to perform an hang analysis
=========================

 Step 1
=========

   SQL> oradebug setmypid
   SQL> oradebug unlimit;
   SQL> oradebug hanganalyze 3

Step 2
========

   SQL> oradebug setmypid
   SQL> oradebug unlimit;
   SQL> oradebug dump systemstate 266

Step 3
========
   SQL> oradebug hanganalyze 3

Step 4
========
   SQL> oradebug setmypid
   SQL> oradebug unlimit;
   SQL> oradebug dump systemstate 266

Please refer metalink note 61552.1   for further details on the steps.


 Problem we faced
================

 we could not performed the above steps because the database was not accepting a new collection

 

Solution

======

 

When the database is not accepting the new collection is due to the hung status, start a preliminary correction to the database as follows

 

sqlplus -prelim " / as sysdba"

 

Then perform the steps mentioned above.

  

 

 After collecting the hang analysis details we terminated the database instance resolve the issue