Oracle Database Troubleshooting Guide — Diagnostic Approach by Problem Type

Oracle Database Troubleshooting Guide

A systematic diagnostic approach organized by the type of problem you’re encountering. Each section links to the specific scripts and error guides that help resolve the issue.

---

Connection Problems

Symptoms: Users cannot connect, intermittent connection drops, “TNS” errors.

Diagnostic Steps

1. Check listener status:

   lsnrctl status
   lsnrctl services

2. Verify database is open:

   • db.sql — Database status check
   • vdb.sql — Database state information

3. Check session and process limits:

   • gvsess.sql — Current session count
   • pgmcnt.sql — Connections by program
   • pgmcntu.sql — Connections by username
   • vlicense.sql — Session license limits

4. Verify user account status:

   • duser.sql — User account details
   • dprofile.sql — Profile password settings

Common Errors

Error	Cause	Quick Fix
ORA-12541	Listener not running	Start the listener
ORA-12154	TNS name not found	Check tnsnames.ora
ORA-12514	Service not registered	Register service with listener
ORA-12516	No handler available	Increase sessions/processes
ORA-28000	Account locked	Unlock the account
ORA-01017	Wrong password	Reset password
ORA-00018	Max sessions exceeded	Increase SESSIONS parameter
ORA-00020	Max processes exceeded	Increase PROCESSES parameter

---

Space Problems

Symptoms: ORA-01653/01654 errors, tablespace alerts, slow inserts, jobs failing.

Diagnostic Steps

1. Check tablespace usage:

   • ddbspacea.sql — Space with autoextend info
   • ddbspaceb.sql — Space summary by tablespace

2. Identify space consumers:

   • dsegbig.sql — Largest segments
   • dstatzero.sql — Tables with zero rows but allocated blocks
   • maxshrink.sql — Shrink potential analysis

3. Check temp and undo:

   • temp.sql — Temp space usage
   • vrolluse.sql — Undo/rollback usage

4. Check FRA/archive space:

   • fra_space.sql — Flash Recovery Area usage
   • flash_space.sql — FRA space details
   • varchlog.sql — Archive log gap analysis

Common Errors

Error	Cause	Quick Fix
ORA-01653	Table can’t extend	Add datafile or enable autoextend
ORA-01654	Index can’t extend	Add space to tablespace
ORA-01652	Temp segment full	Resize temp tablespace
ORA-01688	Partition can’t extend	Add space to partition tablespace
ORA-19815	FRA full	Delete old backups or increase FRA size
ORA-00257	Archiver stuck	Free archive log destination space
ORA-30036	Undo segment full	Increase undo tablespace

---

Performance Degradation

Symptoms: Slow queries, high response times, CPU spikes, timeout errors.

Diagnostic Steps

1. Assess current state:

   • gvsess.sql — Active sessions and waits
   • vsysev.sql — System wait events
   • high_wait_times.sql — High wait sessions

2. Find problem SQL:

   • gvsqltop.sql — Top SQL statements
   • vsqltime.sql — SQL with timing
   • vsqlexe.sql — Top SQL by executions
   • ep.sql — Explain plan viewer

3. Check memory:

   • vsgastat.sql — SGA memory
   • pga.sql — PGA memory
   • vlibrary.sql — Library cache stats

4. Check I/O:

   • vfileb.sql — File I/O with timing
   • gvio.sql — I/O across RAC

5. Generate reports:

   • awrrpt.sql — AWR report
   • ashrpt.sql — ASH report
   • addmrpt.sql — ADDM advisory

Common Errors

Error	Cause	Quick Fix
ORA-04031	SGA allocation failure	Increase shared pool or SGA
ORA-04030	Process memory exhaustion	Check PGA_AGGREGATE_TARGET
ORA-01555	Undo too small for long queries	Increase undo retention
ORA-01013	User cancelled query	Optimize the query

---

Lock Contention

Symptoms: Sessions hanging, “resource busy” errors, deadlocks, application timeouts.

Diagnostic Steps

1. Identify blocking chains:

   • gvlock.sql — All locks across RAC
   • gvlockb.sql — Blocking locks only
   • vlockb2.sql — Blocking and waiting locks
   • vlockobj.sql — Locked objects

2. Examine blocking session:

   • gvsess.sql — Session details
   • sqlbysid.sql — SQL for specific session

3. Check for enqueue waits:

   • vsessenq.sql — Session enqueue waits
   • enqueue2.sql — Enqueue analysis from AWR

Common Errors

Error	Cause	Quick Fix
ORA-00054	Resource busy (DDL blocked)	Wait for DML to finish or kill session
ORA-00060	Deadlock detected	Review application locking order

Resolution Options

1. Wait — The blocking session may finish shortly
2. Contact owner — Identify who owns the blocking session
3. Kill session — Use ALTER SYSTEM KILL SESSION 'sid,serial#' as last resort
4. Application fix — Redesign to minimize lock hold time

---

Memory Errors

Symptoms: ORA-04031/04030 errors, excessive paging, slow performance.

Diagnostic Steps

1. Check SGA:

   • vsgastat.sql — SGA breakdown
   • show_sga.sql — SGA summary
   • vsga_resize.sql — SGA resize history
   • vlibcache2.sql — Library cache locks

2. Check PGA:

   • pga.sql — PGA analysis
   • gvpga.sql — PGA across RAC
   • vsortratio.sql — Memory vs. disk sorts

3. Check for memory leaks:

   • vprocess.sql — Process information
   • gvsess.sql — Session memory usage

Common Errors

Error	Cause	Quick Fix
ORA-04031	Shared pool exhaustion	Flush shared pool or increase size
ORA-04030	PGA exhaustion	Check PGA_AGGREGATE_TARGET
ORA-04036	PGA limit per process	Increase PGA_AGGREGATE_LIMIT
ORA-00845	MEMORY_TARGET not supported	Use ASMM instead of AMM
ORA-27125	OS shared memory config	Increase kernel shmmax

---

Object and Schema Issues

Symptoms: Invalid objects, compilation errors, missing privileges.

Diagnostic Steps

1. Check invalid objects:

   • dinvalid.sql — Find invalid objects
   • derror.sql — Compilation errors

2. Check privileges:

   • dprivall.sql — All user privileges
   • drole.sql — Role definitions
   • dtabprivs.sql — Table-level privileges

3. Check object status:

   • dobject.sql — Object details
   • ddepend.sql — Object dependencies

Common Errors

Error	Cause	Quick Fix
ORA-00942	Object doesn’t exist	Check spelling and schema
ORA-01031	Insufficient privileges	Grant required privileges
ORA-06508	Invalid PL/SQL unit	Recompile the object
ORA-04091	Mutating trigger	Redesign trigger logic

---

Related Resources

• Performance Tuning Checklist — Systematic tuning methodology
• Oracle Database Health Check Guide — Comprehensive health assessment
• Oracle RAC Administration Guide — RAC-specific troubleshooting
• Oracle Error Reference (101 errors) — Complete error guide library