20 Real Oracle DBA Production Issues (With Short Description)

1️⃣ Archive Log Destination Full

Problem: Database hangs because archive logs fill the disk.

Error example:

ORA-00257: archiver error. Connect as SYSDBA only.

Solution:

• Delete old archive logs

• Backup archive logs using RMAN

• Increase archive destination space

---

2️⃣ Tablespace Full

Error:

ORA-01653: unable to extend table

Solution:

ALTER DATABASE DATAFILE '/data/users01.dbf'
RESIZE 5G;

Or add new datafile.

---

3️⃣ Listener Not Starting

Error:

TNS-12541: TNS:no listener

Check using Oracle Net Listener

Solution:

lsnrctl start

---

4️⃣ Database Not Starting

Error:

ORA-01034: ORACLE not available

Solution:

STARTUP;

Check alert log for root cause.

---

5️⃣ ORA-04031 Shared Pool Error

Error:

ORA-04031: unable to allocate shared memory

Cause:

• Shared pool too small

• Too many SQL statements

Solution:
Increase shared pool size.

---

6️⃣ ORA-04036 PGA Limit Exceeded

Error:

ORA-04036: PGA memory exceeds PGA_AGGREGATE_LIMIT

Solution:

• Increase PGA limit

• Tune heavy SQL queries.

---

7️⃣ High CPU Usage

Symptoms:

• Server CPU 100%

• Slow database response

Check SQL:

SELECT * FROM v$sql ORDER BY cpu_time DESC;

Tune problematic queries.

---

8️⃣ Blocking Sessions

Users complain application is stuck.

Check blocking session:

SELECT blocking_session, sid
FROM v$session
WHERE blocking_session IS NOT NULL;

Kill blocking session if necessary.

---

9️⃣ Deadlocks

Error:

ORA-00060: deadlock detected

Cause:
Two sessions locking each other.

Check trace file and optimize application transactions.

---

🔟 Datafile Autoextend Disabled

Tablespace cannot grow.

Check:

SELECT autoextensible
FROM dba_data_files;

Enable autoextend.

---

1️⃣1️⃣ Too Many Database Connections

Error:

ORA-00020: maximum number of processes exceeded

Solution:

ALTER SYSTEM SET processes=500 SCOPE=SPFILE;

Restart database.

---

1️⃣2️⃣ Temporary Tablespace Full

Error:

ORA-01652: unable to extend temp segment

Solution:

Add temp file.

ALTER TABLESPACE temp ADD TEMPFILE '/data/temp02.dbf' SIZE 2G;

---

1️⃣3️⃣ Undo Tablespace Full

Error:

ORA-30036: unable to extend segment in undo tablespace

Solution:

Increase undo tablespace.

---

1️⃣4️⃣ Database Performance Slow

Possible causes:

• Bad SQL

• Missing indexes

• Memory issues

Analyze using AWR reports.

---

1️⃣5️⃣ Corrupted Datafile

Error:

ORA-01110: data file corrupted

Solution:
Restore using RMAN.

---

1️⃣6️⃣ RMAN Backup Failure

Possible issues:

• Disk space full

• Archive logs missing

• Channel configuration problem

Check RMAN logs.

---

1️⃣7️⃣ Invalid Database Objects

After patching or upgrades.

Check:

SELECT object_name
FROM dba_objects
WHERE status='INVALID';

Recompile objects.

---

1️⃣8️⃣ Flash Recovery Area Full

Error:

ORA-19809: limit exceeded for recovery files

Solution:

Delete old backups.

---

1️⃣9️⃣ Database Lock Contention

Sessions waiting for locks.

Check:

SELECT * FROM v$lock;

Tune application transactions.

---

2️⃣0️⃣ Slow SQL Queries

Common in production systems.

Find top SQL:

SELECT sql_id, elapsed_time
FROM v$sql
ORDER BY elapsed_time DESC;

Tune SQL or create indexes.

---