Author: Burleson
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.
Listener:
Sometimes Oracle gets hang if listener is not running. Make sure that the listener is running. Check the lsnrctl statistics.
Oracle Hanging:
Oracle sets locks in order to manage concurrent updates and ensure that the database maintains its internal integrity. In some cases Oracle use hanging and no users can connect to the database. In such scenarios you have no choice other than bouncing the instance.
Shared Data:
Mostly the end-user session hangs when a shared data resource held by another end-user is accessed. The information about when Oracle has a session waiting on a resource can be found in the v$session view in the row_wait_file# and row_wait_block#. The file number and block number can then be cross-referenced into the dba_extents view to see the name of the table where the session is waiting on a block.
Column host format a6;
Column username format a10;
Column os_user format a8;
Column program format a30;
Column tsname format a12;
select
b.machine host,
b.username username,
b.server,
b.osuser os_user,
b.program program,
a.tablespace_name ts_name,
row_wait_file# file_nbr,
row_wait_block# block_nbr,
c.owner,
c.segment_name,
c.segment_type
from
dba_data_files a,
v$session b,
dba_extents c
where
b.row_wait_file# = a.file_id
and
c.file_id = row_wait_file#
and
row_wait_block# between c.block_id and c.block_id + c.blocks – 1
and
row_wait_file# <> 0
and
type=’USER’
;
ANALYZE command hanging:
The ANALYZE command may hang if there is not enough space in the Temp Tablespace. Create a big Temporary Tablespace to avoid this problem.
The ANALYZE command may also hang if the table is partitioned and a utility (imp or loader) is inserting rows in the table, and at the same time ANALYZE command starts working on the table. You can fix this problem by analyze be done in night when there is a low DML activities.
