Effective Tips for Hang Databases!

Page: 1 2

It is very frustrating when you find your database hang. It is even more frustrating when there are no messages in the alert log and still you are unable to connect to the database. We hear complains when one tries to connect to Oracle with Enterprise Manager and it just hangs.  Sometimes one is unable to cannot connect via SQL*Plus either.  Oracle can hang for many reasons. In this article I will give some effective tips that can help you fix the hung databases.

Logs and Files:

Alert Log:

First of all analyze the alert log for any errors or messages.

Server-side Logs:

In addition to the alert log, you need to check server-side logs as well.

/etc/syslog, /var/adm/syslog

Listener Log:

Check the listener log files.

Trace Files:

Check the bdump, cdump and pfile directories for trace files. 

Instance Availability:

You might not see an entry in the alert log if the instance is unavailable due to a crash. You can check that the instance is running and hence available by using below statement.

ps -ef|grep ora|grep pmon  

Server Resources:

Databases mostly hang if server resources are over allocated and there is not enough RAM to spawn another connection to Oracle.

External issues:

There are some external issues that can make your database hang. The network being down, Kerberos security issues, SSO or a firewall issue can cause an Oracle connection to hang.  You can test this by setting

sqlnet.authentication_services=(none)

in your sqlnet.ora file and then retry connecting. Continued...

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Disk I/O Tuning with Oracle RAID Stripe Size!!
   Oracle Performance Enhancement using Parallel Execution Tips
   Database Benchmarking – Some Facts!
   Beware of PL/SQL Performance Problems!!