What to do when you cannot connect to Oracle
Oracle Database Tips by Donald BurlesonJanuary 8, 2015
Question: My database is hung, and I cannot
connect to Oracle with Enterprise Manager, it just hangs. I cannot connect
via SQL*Plus either. How to I fix a hanging database when I cannot connect
with OEM? I see no messages in the alter log.
Answer: Oracle can "hang" for many reasons.
In addition to the alert log, you need to check server-side logs (/etc/syslog,
/var/adm/syslog), and check the listener log files as well as the bdump,
cdump and pfile directories for trace files.
For more complete details on diagnosing a hung database
with Oracle Utilities, see the book "Advanced Oracle Utilities: The
Back in Oracle 10g a hung database was a real problem,
especially if the DBA could not connect via SQL*Plus to release the source
of the hanging. In these cases,
the DBA had few options other than to force-down the instance and warmstart
Gathering a trace file in a hung 11g database
There is a new feature in Oracle 11g SQL*Plus called
the ?prelim? option. This option is very useful for running oradebug and
other utilities that do not require a real connection to the database.
root> sqlplus ?prelim
SQL> connect / as sysdba
At this point, you are free to run the oradebug
commands to diagnose a hung database issue using the new
SQL> oradebug hanganalyze 3
Wait at least 2 minutes to give time to
identify process state changes.
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';
If the instance was unavailable due to a crash, you might
not see an entry in the alert log, so start by checking that the instance is
running (ps -ef|grep ora|grep pmon).
In some cases where Oracle us hanging and no users can
connect, you have no choice but to bounce the instance. Other
possibilities for Oracle hanging include:
# First, we must set
the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
External issues - The network being down,
Kerberos security issues, SSO or a firewall issue can cause an Oracle
connection to hang. One way to test this is to set
sqlnet.authentication_services=(none) in your sqlnet.ora file and
Listener is not running - Start by checking the
listener (check lsnrctl stat). Also, see my notes on
diagnosing Oracle network connectivity issues.
No RAM - Over allocation of server resources,
usually RAM, whereby there is not enough RAM to spawn another connection to
Contention - It is not uncommon for an end-user
session to ?hang? when they are trying to grab a shared data resource that
is held by another end-user. The end-user often calls the help desk trying
to understand why they cannot complete their transaction, and the Oracle
professional must quickly identify the source of the contention.
Whenever Oracle is has a session waiting on a resource, this information 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;
b.row_wait_file# = a.file_id
c.file_id = row_wait_file#
row_wait_block# between c.block_id and c.block_id + c.blocks - 1
row_wait_file# <> 0
If you like Oracle tuning, you
might enjoy my book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get instant
access to the code depot of Oracle tuning scripts.