Fix Oracle Hanging

What causes Oracle to hang?  How can I detect the cause of an Oracle hang?  How do I fix an Oracle hang problem?

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

1.       Oracle path not properly set like $ORACLE_HOME.

2.       Listener is down/hanging. Check tns/listener is working fine or not (lsnrctl status)

3.       External hanging like network issue or firewall issue to make connection to DB. You can test this command. Set sqlnet.authentication_services=(none) in your sqlnet.ora file and retry connecting.

4.       There is not enough RAM to spawn another connection to Oracle

Debugging a hung database

There is a new feature in Oracle 11g SQL*Plus called the “prelim” option

root> sqlplus –prelim

SQL>

or

SQL> set _prelim on

SQL> connect / as sysdba

To run the oradebug commands to diagnose a hung database issue using the new hanganalyze utility:

1. SQL> oradebug hanganalyze 3

Wait at least 2 minutes to give time to identify process state changes.

2. 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';