RAC Survival Kit: Troubleshooting A Hung Database

This document is an troubleshooting guide for debugging a hung database in a Real Application Clusters environment.

 

This article is linked from Note 203226.1. Please use that note as a starting point.

 

to collect diagnostic information

 

If on 9i run the following on one instance 3 times - 1 minute apart in sqlplus:

---------------------------------------------------------------------------

SQL> oradebug setmypid 

SQL> oradebug unlimit 

SQL> oradebug -g all hanganalyze 3

 

To collect more diagnostic information, you might want to add systemstate dump debugging but it might be time consuming: 

 

SQL> oradebug -g all dump systemstate 10

 

These commands might take some time to finish.  

 

Does the hanganalyze finish?

 

Yes, the hanganalyze finishes

No, I can't login to sqlplus or the hanganalyze never finishes

 

 

---

 

 

 

If you cannot login to sqlplus or the hanganalyze never finishes, there may be problems on the operating system.  

 

Do normal operating system commands finish?  

 

Yes, operating system commands run fine

No, operating system commands hang - Please contact your system administrator or OS vendor to resolve the operating system issue

 

 

---

 

 

At this point you will need to use an OS debugger like dbx or gdb and manually get a systemstate dump.  There is an example of how to do this in Note 121779.1.  Do this 3 times, 1 minute apart on each node.  Once this is done, confirm that the files were written to the user_dump_dest or background_dump_dest.  Depending on system availability requirements, at this point you may want to abort the hung instance(s) to quickly get the system back on line.  Then open a service request to see if a root cause can be determined.  To do this click here.