Home / Databases / Oracle

DatabasesRunbookAdvanced

Sev1: Oracle database hung — nobody can log in. Prelim connection, hanganalyze and a safe way out

The Oracle database stops responding and normal SYSDBA logins hang. Use a prelim connection to collect hanganalyze and systemstate, find the final blocker, kill it, and only then consider shutdown abort.

Deepak Bhardwaj3 min read

Applies toOracle Database 19c and 23ai (single instance and RAC) on Linux

Symptoms

  • Applications freeze; new sessions hang at login.
  • sqlplus / as sysdba hangs too.
  • The host itself is responsive (SSH works).

1. Quick host checks

uptime; free -m; df -h
ps -eo pid,stat,pcpu,pmem,cmd --sort=-pcpu | head -15
tail -100 $ORACLE_BASE/diag/rdbms/orcl/ORCL/trace/alert_ORCL.log

The alert log often names the cause directly: archiver stuck (ORA-00257), Checkpoint not complete, ORA-04031, or errors writing to the redo log. A full archive destination or FRA is the single most common "hang".

2. Get in with a prelim connection

A prelim connection attaches to the SGA without creating a full session, so it works when normal logins hang:

sqlplus -prelim / as sysdba

oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
-- wait about a minute, then run it again to see if the chain is stable
oradebug hanganalyze 3
oradebug tracefile_name

For RAC, run it cluster-wide:

oradebug setinst all
oradebug -g def hanganalyze 3

If Oracle Support may need it, collect a systemstate dump (it can be large and slow on big SGAs):

oradebug dump systemstate 258

Warning

Higher systemstate levels (for example 266, which includes short stacks) take much longer on busy systems and can make the hang worse. Use 258 unless Support asks for more.

3. Read the chain

Open the trace file named by tracefile_name. The hanganalyze output lists chains: who waits on whom, ending in the final blocker with its wait event, SID and OS process ID.

Final blocker waits on Likely cause
log file switch (archiving needed) Archive destination full
log file switch (checkpoint incomplete) Redo logs too small/few, slow DBWR
enq: TX - row lock contention at the head Application lock held by an idle session
library cache lock / cursor: pin S wait on X DDL, stats gathering or hard parse storm
Nothing (on CPU) Runaway process or OS starvation

If you can open a normal session, the same picture is available from:

SELECT chain_id, sid, sess_serial#, blocker_sid, wait_event_text, in_wait_secs
FROM v$wait_chains ORDER BY chain_id, in_wait_secs DESC;

4. Fix the cause

  • Archiver stuck: free space in the archive destination or FRA (back up and delete archivelogs with RMAN, or raise db_recovery_file_dest_size):
ALTER SYSTEM SET db_recovery_file_dest_size = 800G SCOPE=BOTH;
  • Single blocking session: kill it. With a prelim connection you can't run SQL, so kill the OS process found in the trace:
kill -9 <os_pid_of_final_blocker>

Never kill background processes (names like ora_pmon_, ora_lgwr_, ora_dbw0_); that crashes the instance.

When a normal session works:

ALTER SYSTEM KILL SESSION '123,4567' IMMEDIATE;

5. Last resort: restart

If the hang persists and the business impact is greater than the risk:

shutdown abort
startup

shutdown abort is safe for data integrity (instance recovery runs on startup) but rolls back all in-flight transactions and drops every connection. Collect the hanganalyze trace before doing this, or the root cause is lost.

Prevent it next time

  • Alert on FRA/archive destination usage above 80%.
  • Size redo logs so switches happen about every 15-20 minutes at peak.
  • Set an idle session limit for application users that leave transactions open.
  • Keep the prelim/hanganalyze procedure in the on-call runbook and practise it.

Related fixes