Connect To Hung Database In Oracle

Oracle hangs only when he is waiting for a resource.  It might be a software resource (a latch or lock), or you could be hung waiting for server resources (CPU, RAM).  

There are several ways to find the root cause of an Oracle hanging issue:

oradebug - See below for complete steps for diagnosing a database when you cannot connect to SQL*Plus.

Automatic Session History - Check ASH during the hang (if you have purchased the license):

Report - Run a couple of STATSPACK or AWR snapshot during the hang and get an elapsed time report.

Check for locking - Whenever Oracle is has a session hangs waiting on a resource, this information can be found in the v$session view in the row_wait_file# and row_wait_block#

There is a new feature in Oracle 11g SQL*Plus called the "prelim" option. This option is very useful for running oradebug and other utilities that do not require a real connection to the database.

Connect :

$ sqlplus -prelim
SQL>

or

SQL> set _prelim on

SQL> connect / as sysdba

Analyze the hang issue(using oradebug):

SQL> oradebug hanganalyze 3
Wait at least 2 minutes to give time to identify process state changes.

SQL> oradebug hanganalyze 3
Open a separate SQL session and immediately generate a system state dump.

SQL> alter session set events 'immediate trace name SYSTEMSTATE level 10';

Shutdown abort the instance now.

SQL> Shutdown abort;

Hope it worked !! 🙂