Fix a hung Database

The SQL*Plus utility has an undocumented argument invoked as (-prelim) to allow you to connect to a hung Oracle database that is not accepting new connections. The “sqlplus –prelim” option allows you to access a hung database to quickly run an oradebug trace report. 

This –prelim utility is not the same as the hang manager (hangman) utility, a tool introduced in Oracle 11g to detect database bottlenecks.  An extension of the dba_waiters and dba_blockers views, the hangman tables have a “hang chain” that allow the DBA to find the source of hangs, such as the “deadly embrace” where mutually blocking locks or latches hang a process.       

Note:  Using the “sqlplus – prelim” should be done very quickly since you will want to quickly re-start your instance so as to minimize the downtime of your user community.  You never want to keep hundreds of users waiting, and a 2 minute bounce (nuke and re-start) is always faster than trying to resurrect connectivity to a hung instance.  

Here is how to quickly generate a hang analysis immediately prior to re-starting a hung production instance:

Read on, details on how to fix a hung Database



Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link: