$ guides / oracle-database ▌

ORACLE DATABASE · OPERATIONS PLAYBOOK

Oracle without the pager: redo, undo, archiving, and the shared pool

A shared-memory instance fronting per-session private memory, where every change is written twice — once as redo for recovery, once as undo for rollback and read consistency — and durability rides on LGWR, the archiver, and the storage underneath them. Here's how that machinery holds up in production, where it cracks first, what to watch as load grows, and how to work each failure when it lands.

> Start with the monitoring checklist → # Jump to the full guide list

Oracle Database is famously capable and famously unforgiving: the defaults carry a workload for years, right up until redo, undo, archiving, or the shared pool hits a hard edge that has no graceful degradation.

The defaults work. Until the archive destination fills, ARCn can't write, the online redo logs can't cycle, and every session freezes on log file switch (archiving needed) while new logins get ORA-00257 — a total outage that basic 'is it up' checks sail straight through. Until a long report needs an undo version a busy transaction already overwrote and fails with ORA-01555: snapshot too old. Until an application sends literal SQL, the shared pool fragments, and allocations start failing with ORA-04031. Until one idle session sits on an uncommitted row lock and the whole application backs up behind enq: TX - row lock contention. Until DBMS_STATS runs overnight, the optimizer picks a new plan, and a 10ms query starts doing full scans at ten minutes each.

These guides are written for engineers who already run Oracle, not for people learning what a tablespace is. The goal is to give you the mental model of how the instance actually behaves under load — SGA and PGA, the redo and undo write path, the wait-event model — the failure patterns that keep recurring, the monitoring story that catches problems before they page anyone, and the runbooks you wish someone had handed you before your last incident.

How Oracle actually runs in production

Oracle is not just a SQL engine. It is a shared-memory instance (the SGA) fronting per-session private memory (the PGA), where every change is written twice — once as redo for recovery, once as undo for rollback and read consistency — and durability depends on LGWR, the archiver, and the storage beneath them. Most production failures live between these layers, not inside any one of them.

listener + connection admission

Clients reach the TNS listener (default port 1521), which hands each connection to a dedicated server process — one OS process plus a PGA allocation. The <code>PROCESSES</code> and <code>SESSIONS</code> parameters hard-limit concurrency; hitting them raises <code>ORA-00020</code>. A listener that accepts TCP can still reject connections with <code>TNS-12516</code> / <code>TNS-12519</code> when no handler is free.

LISTENER

sessions + PGA

Each dedicated server gets private PGA for sorts, hashes, and session state, governed by <code>PGA_AGGREGATE_TARGET</code> (soft) and <code>PGA_AGGREGATE_LIMIT</code> (hard, 12c+). When PGA is short, work spills to the temp tablespace and slows down; at the hard limit Oracle kills the biggest sessions with <code>ORA-04036</code>.

PGA

shared pool + library cache

Parsed SQL plans and the data-dictionary cache live here. Every hard parse takes exclusive mutexes and CPU; literal SQL forces a hard parse per statement, fragmenting the pool until an allocation fails with <code>ORA-04031</code>. Parsing pressure shows up as <code>library cache: mutex X</code> and <code>cursor: pin S wait on X</code>.

PARSE

buffer cache + DBWn

Data blocks are cached here; a read that misses waits on <code>db file sequential read</code> (index) or <code>db file scattered read</code> (scan). DBWn writes dirty buffers back to datafiles. If DBWn falls behind, sessions can't find a clean buffer and stall on <code>free buffer waits</code> — and all DML backs up.

CACHE

transaction layer (undo + locks)

Every change writes an undo record for rollback and for the read-consistent view long queries depend on. Old undo overwritten too soon raises <code>ORA-01555</code>; a full undo tablespace raises <code>ORA-30036</code>. Row and table access is serialized by enqueues — one uncommitted transaction can block many others behind <code>enq: TX</code>.

TXN

redo + LGWR

Change vectors buffer in the redo log buffer and LGWR flushes them to the online redo logs. Every <code>COMMIT</code> waits for that flush as <code>log file sync</code> — so redo-storage latency sets the ceiling on write throughput. Compare it with <code>log file parallel write</code> to tell storage latency from LGWR scheduling.

COMMIT

checkpoints + archiving (ARCn)

CKPT and DBWn advance checkpoints; ARCn copies each filled online redo log to the archive destination. If DBWn lags a log switch you get <code>Checkpoint not complete</code>; if archiving stalls, the logs can't be reused and the instance hangs (<code>cannot allocate new log</code>, <code>ORA-00257</code>) — the most dangerous Oracle failure.

The failures you'll actually see

Most Oracle incidents fall into a small set of recurring patterns. Recognise the shape, and triage gets dramatically faster.

IMMINENT

The archive hang

The archive destination or FRA fills, or the NFS/ASM target goes away, and ARCn can't write. The online redo logs fill, LGWR can't switch, and every session needing redo freezes on log file switch (archiving needed). The instance is still OPEN and the listener still answers, so 'is it up' checks pass — but queries and DML hang indefinitely and new non-SYSDBA logins get ORA-00257. A total outage masquerading as a healthy database.

alert log: Thread N cannot allocate new log, sequence N
V$ARCHIVE_DEST_STATUS STATUS = ERROR, or archive dest / FRA near full
TPS drops to zero while session count stays stable
new non-SYSDBA connections fail with ORA-00257

Investigate →

CRITICAL

LGWR can't keep up: the slow-commit cascade

Redo log storage degrades — a failing SSD, SAN contention, or redo sharing a device with datafiles — and every COMMIT waits longer on log file sync. Because all writes commit, all writes slow uniformly, TPS declines across the board, and Checkpoint not complete starts appearing. CPU can look idle while sessions sit waiting on I/O. This is the single most common Oracle performance emergency.

log file sync average climbing past 10ms and rising
log file parallel write also elevated (confirms storage, not scheduling)
TPS declining uniformly, not on specific queries
Commit dominating the active-session wait profile

Investigate →

ACTIVE

The lock contention cascade

One session holds uncommitted DML on hot rows and goes idle. Other sessions queue behind it on enq: TX - row lock contention, and as the queue grows the connection pool spins up new sessions that also block — potentially exhausting PROCESSES/SESSIONS. The blocker is usually INACTIVE, burning no CPU, which is exactly why it hides. One stuck transaction can take down the whole application.

enq: TX - row lock contention waits growing
BLOCKING_SESSION points to an INACTIVE (idle) holder
session count stable or rising while TPS declines
process/session utilisation creeping toward the limit

Investigate →

IMMINENT

Space exhaustion at the cliff edge

A tablespace reaches max capacity and the next extent fails with ORA-01653 (table) or ORA-01654 (index). There is no graceful degradation — the workload runs perfectly at 99% and stops dead at 100%. It is easy to miss because autoextend can hit its MAXSIZE while the filesystem still shows free space, so disk-only monitoring reports healthy right up to the failure.

DBA_TABLESPACE_USAGE_METRICS used percent above 90% of max
ORA-01653 / ORA-01654 in the alert log or to the application
autoextend datafile at MAXBYTES with the filesystem not full
SYSTEM, SYSAUX, or UNDO tablespace among the offenders

Investigate →

CRITICAL

The parse storm to ORA-04031

An application sends literal SQL, so every unique statement forces a hard parse — exclusive mutexes, CPU, and shared-pool memory. At scale, sessions pile up on library cache: mutex X, the shared pool fragments, and allocations begin failing with ORA-04031. Reaching for ALTER SYSTEM FLUSH SHARED_POOL only triggers another hard-parse storm and makes it worse.

parse count (hard) rising sharply; hard-parse ratio above 5%
library cache: mutex X / cursor: pin S wait on X dominating waits
shared pool free memory declining, CPU high from parsing
ORA-04031 appearing in the alert log

Investigate →

ACTIVE

The undo pressure spiral

A large transaction generates enormous undo while long-running reports still need older versions for read consistency. Unexpired undo gets stolen and long queries fail with ORA-01555: snapshot too old; if ACTIVE undo then fills the tablespace, DML itself fails with ORA-30036. The read-path failure is annoying; the write-path failure is an outage.

V$UNDOSTAT SSOLDERRCNT rising (ORA-01555 occurring)
V$UNDOSTAT UNXPSTEALCNT non-zero (undo being stolen early)
ACTIVE undo growing as a share of the undo tablespace
a single large USED_UBLK transaction in V$TRANSACTION

Investigate →

The Netdata solution

Oracle Database monitoring with Netdata

Netdata monitors Oracle Database with per-second metrics and automatic dashboards. Watch wait events, redo and archive-log activity, tablespace and undo space, and session and lock activity so the failure modes in these runbooks surface before the instance hangs.

See Oracle monitoring → Start monitoring free

Choosing a tool

Best Oracle Database Monitoring Tools: 10 Ranked for 2026

A ranked review of the tools teams actually shortlist here, what each one is genuinely good at, and how the pricing behaves as you scale.

Read the buyer's guide →

Oracle monitoring maturity levels

Oracle observability works in four practical levels. Each is a complete operation, not a stepping stone. Pick the level that matches how much your database matters. Most production instances should land at the second level.

Level 1: Survival

Know that something is wrong

Survival monitoring is the floor. With these signals you can answer one question: is the database still serving? You will not learn what broke, but you will learn that something broke before users do. Survival is enough for dev instances and low-stakes workloads.

Instance status Is the instance OPEN, ACTIVE, and READ WRITE (or READ ONLY on a standby)?
Listener responsiveness Can a client actually connect over the full admission path, not just reach port 1521?
Tablespace free space Is any tablespace near max capacity (against autoextend MAXSIZE, not current size)?
Archive destination status Can the archiver still write — or is a hang minutes away?
Sessions vs PROCESSES limit Is connection admission about to hit ORA-00020?
Alert log critical errors ORA-00600, ORA-07445, corruption, and space errors as they land.

Level 2: Operational

Diagnose most incidents on your own

Operational monitoring is what most production instances should target. Survival tells you something is wrong; operational tells you what. With this coverage your team can usually diagnose an incident on its own: commit latency, I/O waits, lock contention, undo pressure, space, and the archive path.

Top wait events by time log file sync, db file sequential read, enq: TX — where is DB time going?
log file sync vs parallel write Split redo-storage latency from LGWR scheduling/CPU.
Redo log switch frequency Undersized logs and the earliest sign of checkpoint pressure.
Undo usage + ORA-01555/30036 SSOLDERRCNT, NOSPACEERRCNT, and ACTIVE undo share.
Blocking sessions and chain depth Who is at the head of the lock chain, and for how long?
FRA / archive destination space Space runway on the most dangerous blind spot in Oracle.
Session / process utilisation trend Peak below 75% of PROCESSES, with MAX_UTILIZATION watched.
RMAN backup success Has a backup actually completed inside the policy window?

Level 3: Mature

Catch problems before they become incidents

Mature monitoring catches problems before they wake anyone up. A tablespace trending toward full over weeks, SYSAUX growing from AWR, undo retention slipping under the longest query, a plan starting to drift, Data Guard lag creeping up. None of these page you on day one. They become incidents on day thirty.

TPS baseline deviation A workload-relative drop, not a static threshold.
Redo generation rate trend Is write intensity approaching archiver or Data Guard bandwidth?
PGA aggregate utilisation Over-allocation and temp spill before ORA-04036.
SGA component resize behaviour Oscillating resizes and shared-pool free below 5%.
Tablespace growth projection Days-to-full from the 30-day growth rate.
Data Guard transport / apply lag Is the standby still a viable failover target within RPO/RTO?
Checkpoint completion status Are log switches stalling on incomplete checkpoints?
Hard parse ratio trend Is parsing pressure building toward a shared-pool problem?

Level 4: Expert

Reactive instrumentation after real incidents

Expert signals enter your stack the day after a specific incident proved you needed them. Per-SQL plan stability, ASH session-level load, latch and mutex contention, RAC interconnect health, per-PDB isolation. Most teams never need every signal here. Add the ones your incident history says you do.

SQL plan stability (per SQL_ID) Multi-plan SQL_IDs and buffer_gets/exec regression.
ASH session-level analysis Where load actually went over time (needs Diagnostics Pack).
Latch / mutex contention V$LATCH_MISSES and V$LATCH_HOLDER for the hot structure.
RAC interconnect + gc waits gc block transfer times and buffer-busy across instances.
Per-PDB resource isolation Multitenant Resource Manager plans and per-PDB space.
Undo retention vs longest query Is MAXQUERYLEN outrunning effective UNDO_RETENTION?
Block corruption proactive checks Scheduled RMAN VALIDATE / DBVERIFY, not just reactive errors.
Cursor invalidations V$SQL.INVALIDATIONS as a plan-stability threat.

Operating mistakes worth avoiding

The traps Oracle teams keep falling into. Each has a clear, well-known fix. Most teams only learn it after an incident.

Monitoring hit ratios instead of wait events

The buffer cache hit ratio is the single most misused Oracle metric. A 99% ratio means nothing if <code>log file sync</code> is 50ms, and direct-path reads inflate it. Oracle's own methodology has been wait-event based since 10g — measure where time is spent, not cache-hit percentages.

Ignoring the archive destination

When the archive destination or FRA fills, the instance hangs — and it masquerades as 'database up' because the instance is OPEN and the listener answers. Many teams watch tablespaces but forget the FRA or archive filesystem. It is the most dangerous blind spot in Oracle.

Not monitoring the listener separately

The database can be perfectly healthy while <code>tnslsnr</code> is down and nobody can connect. And a TCP probe that reaches port 1521 proves nothing — the listener can still reject with <code>TNS-12516</code>/<code>TNS-12519</code>. Test the full admission path independently.

Treating all ORA- errors the same

<code>ORA-00600</code> and <code>ORA-07445</code> are always critical and need Oracle Support with the exact arguments. <code>ORA-01555</code> is a tuning signal. <code>ORA-04031</code> is urgent but not corruption. Triage by error code — alerting on 'any ORA-' is noise.

Static thresholds on baseline-dependent metrics

TPS, logical reads, and redo generation are all workload-shaped. Static thresholds generate constant false alarms or miss real regressions. Alert on deviation from a 7-day rolling baseline by hour of day instead.

Forgetting about plan regression

The optimizer can destroy performance in seconds after a stats gather. Teams that don't use SQL Plan Baselines (DBMS_SPM) and don't watch per-execution metrics get blindsided by the same class of incident repeatedly.

Ignoring the undo pressure early warning

<code>V$UNDOSTAT.UNXPSTEALCNT</code> going non-zero is the quiet signal that <code>ORA-01555</code> — and eventually <code>ORA-30036</code> — are coming. Most teams react only after the error fires, in the middle of a failing report or DML.

Not validating backups

RMAN backups can fail silently for days. If <code>V$RMAN_BACKUP_JOB_DETAILS</code> shows failures and nobody is watching, the effective RPO is infinite. A backup you have never restore-tested is a hope, not a recovery plan.

Oracle Database runbooks in this section

Each guide is a focused runbook for one symptom or topic. Pick one when you have an incident, or use the categories to learn the area.

▸

Start here

▸

Redo, LGWR, and the commit path

▸

Archiving, the FRA, and the archive hang

▸

Tablespaces, datafiles, and space exhaustion

▸

Undo, read consistency, and long queries

▸

Temp space, PGA, and memory spills

▸

Locks, enqueues, and blocking sessions

▸

Shared pool, parsing, and the library cache

▸

SQL performance and plan stability

▸

Buffer cache, DBWn, and physical I/O

▸

Memory, sessions, and the listener

▸

Availability, corruption, Data Guard & RAC

WHERE TO GO NEXT

Setting up Oracle monitoring, or putting out a fire?

If you're starting from scratch, the monitoring checklist is the path of least regret. If you're mid-incident, jump straight to the symptom that matches what you're seeing.

> Start with the checklist > Back to Operations Guides