Oracle Database monitoring checklist: the signals every production instance needs

Oracle production instances have a wide surface area: CPU, memory (SGA plus PGA), storage I/O, network, process slots, locks, file descriptors. They also have multiple single points of failure (LGWR, the archiver, the listener) and several failure modes that masquerade as “database up” while the application is frozen.

The four-level framework below is drawn from the Signal Catalog and Maturity Levels in the Oracle Database playbook. Each level is additive: Level 2 includes everything in Level 1, and so on. Signals are grouped by the question they answer. Use this checklist to audit your own monitoring against what actually pages you at 3 a.m.

The four monitoring maturity levels

flowchart TD
    L1["Level 1 - Survival
instance, listener, tablespace, alert log, processes, archive dest"]
    L2["Level 2 - Operational
wait events, undo, sessions vs limit, redo rate, backups"]
    L3["Level 3 - Mature
blocking chains, TPS baseline, PGA, SGA resizes, Data Guard lag"]
    L4["Level 4 - Expert
SQL plan stability, ASH, latches, RAC gc, block corruption"]
    L1 --> L2 --> L3 --> L4

Level 1 - Survival

If you are missing any of these six signals, you are operating blind.

Instance status. The database must be OPEN and ACTIVE. Query V$INSTANCE for STATUS and DATABASE_STATUS; query V$DATABASE for OPEN_MODE. Any state other than OPEN, ACTIVE, and READ WRITE in a primary instance means user impact is occurring or imminent. An instance can be OPEN but effectively dead if all sessions are blocked on the same wait event; this signal alone will not catch that. Standby databases are expected in READ ONLY or READ ONLY WITH APPLY.

Listener responsiveness. The TNS Listener must accept and route new connections. Check with lsnrctl status LISTENER. Listener down means no new connections, which cascades into an application pool refresh failure within minutes. A TCP probe to port 1521 is not sufficient: a successful TCP connect does not mean the listener will accept a database connection. It may reject with TNS-12516 (no available handler) or TNS-12519 (too many connections). A full admission test is sqlplus -L user/pass@//host:1521/service_name, which exercises listener plus service registration plus handler availability plus database responsiveness. Note: credentials passed on the command line are visible in ps and process accounting; use a wallet or /usr/bin/env from a secured file where possible.

Tablespace utilization. No tablespace should exceed 85% of maximum capacity, including autoextend. Use DBA_TABLESPACE_USAGE_METRICS.USED_PERCENT, which accounts for autoextend headroom. Raw DBA_FREE_SPACE does not. Tablespace full is a cliff-edge failure: ORA-01653 for tables, ORA-01654 for indexes. There is no graceful degradation. Page at 95% with no autoextend headroom, ticket at 85%, plan at 75%.

Archive destination status. The most dangerous blind spot in Oracle monitoring. If the archiver cannot write (destination full, NFS unreachable, ASM disk group full), online redo logs fill, LGWR cannot switch, and the database hangs silently. Existing sessions freeze on log file switch (archiving needed) with no error returned to the client. New non-SYSDBA connections get ORA-00257. The instance still shows OPEN and the listener still responds, so basic availability checks pass while the database is completely frozen. Query V$ARCHIVE_DEST_STATUS for any row where STATUS is not VALID. Alert at 85% filesystem full. Page at 95%, on any ERROR status, or on cannot allocate new log in the alert log.

Alert log errors. Tail the alert log at $ORACLE_BASE/diag/rdbms/<db_unique_name>/<instance_name>/trace/alert_<instance>.log using adrci exec="show alert -tail 50", or query V$DIAG_ALERT_EXT. The critical patterns:

Sessions vs PROCESSES. Track V$RESOURCE_LIMIT for sessions and processes. Peak usage should not exceed 75% of PROCESSES. Hitting the limit causes ORA-00020, which refuses new connections. SESSIONS defaults to 1.5 * PROCESSES + 22 in 12c and later. Background processes consume roughly 40 to 70 process slots, so usable capacity for user sessions is lower than the raw parameter suggests. Check MAX_UTILIZATION as well: if it has hit the limit at any point since startup, you have already had connection refusals even if current usage is low.

Level 2 - Operational

Everything in Level 1, plus the signals that cover the most common performance failures.

Top wait events by time waited. Oracle’s primary diagnostic framework is wait events. Query V$SYSTEM_EVENT filtered by WAIT_CLASS != 'Idle'. The dominant wait class tells you where time goes. Commit means redo I/O (log file sync). User I/O means storage. Concurrency means locks or latches. Application means enqueue waits from application logic. This is more actionable than any hit ratio.

Log file sync average. The single most important latency signal. Query V$SYSTEM_EVENT for log file sync. Normal averages are under 1ms on SSD or NVMe, under 5ms on SAN. Investigate above 3ms on SSD or 10ms on SAN. Page above 10ms on SSD or 20ms on SAN, sustained for more than 5 minutes with meaningful commit volume. Compare with log file parallel write (LGWR’s actual I/O time). If sync is much higher than parallel write, the bottleneck is LGWR scheduling or CPU starvation, not storage throughput.

Undo tablespace health. Two distinct failures live here. ORA-30036 (unable to extend undo segment) is a write-path failure: DML fails. ORA-01555 (snapshot too old) is a read-path failure: long-running queries fail. Query V$UNDOSTAT for SSOLDERRCNT, NOSPACEERRCNT, and UNXPSTEALCNT. Any non-zero NOSPACEERRCNT is a page. Any non-zero SSOLDERRCNT is a ticket. UNXPSTEALCNT consistently above zero is the early warning that both are coming.

Redo log switch frequency. Query V$LOG_HISTORY.FIRST_TIME. More than 4 to 6 switches per hour with properly sized redo logs indicates high redo pressure. More than one switch per minute almost guarantees checkpoint not complete issues. This is the best early-warning signal before the archive path becomes a hard outage.

Session and process utilization trends. Track V$RESOURCE_LIMIT.CURRENT_UTILIZATION and MAX_UTILIZATION over time. Connection pool misconfiguration, connection leaks, and retry storms all push this upward. If MAX_UTILIZATION equals LIMIT_VALUE, the limit has already been hit.

RMAN backup status. Query V$RMAN_BACKUP_JOB_DETAILS for recent job status and completion time. If backups have silently failed for days, you have no recovery capability. The RPO is effectively infinite. Default to a ticket if no successful backup has completed in the last 24 hours, adjusted to your declared backup policy.

Level 3 - Mature

Everything in Level 2, plus leading indicators and proactive trend analysis.

Blocking sessions with chain depth. Monitor V$SESSION.BLOCKING_SESSION for sessions blocked on enq: TX or enq: TM. A single idle session holding an uncommitted transaction can block dozens of waiters and eventually exhaust the process limit. The blocker is typically INACTIVE, not executing SQL. enq: TM - contention is almost always caused by unindexed foreign keys on the child table.

TPS baseline deviation. Track V$SYSSTAT for user commits and user rollbacks, sampled twice to compute transactions per second. Establish a 7-day rolling baseline by hour of day. Alert on more than 50% below baseline sustained for more than 5 minutes. A TPS drop to near-zero during business hours with application load present means the database is stuck.

Redo generation rate. Track V$SYSSTAT for redo size (cumulative bytes), sampled to compute bytes per second. This rate determines how fast online redo logs fill, how much archiver bandwidth is needed, how much Data Guard transport bandwidth is required, and how much archive log storage is consumed. Supplemental logging (required for GoldenGate) can increase redo volume by 2 to 5 times.

PGA aggregate utilization. Query V$PGASTAT for total PGA allocated, aggregate PGA target parameter, and over allocation count. If total PGA allocated exceeds PGA_AGGREGATE_TARGET, work is spilling to temp tablespace. If it approaches PGA_AGGREGATE_LIMIT (12c and later), sessions receive ORA-04036 and are killed. On Linux, SGA plus PGA exceeding physical RAM triggers the OOM killer, which targets Oracle processes because they are the largest memory consumers.

SGA component resizes. Query V$SGA_DYNAMIC_COMPONENTS and V$SGA_RESIZE_OPS. Frequent oscillating resizes (grow buffer cache, shrink shared pool, then reverse) indicate SGA_TARGET is too small for the workload. Shared pool free memory below 5% of shared pool size is an ORA-04031 risk. Without hugepages on Linux, page table overhead for large SGAs can consume additional gigabytes of memory that is invisible to Oracle’s memory views.

Data Guard transport and apply lag. On the standby, query V$DATAGUARD_STATS for transport lag and apply lag. Growing lag means the standby is not a reliable failover target and data loss on failover will exceed the RPO. On the primary, query V$ARCHIVE_DEST_STATUS and compare ARCHIVED_SEQ# against APPLIED_SEQ#. Define thresholds by your RPO and RTO SLAs.

Tablespace growth rate projections. Compute daily growth from the difference in used space over time. Project days_remaining = free_space / daily_growth_rate. Check weekly. This turns a cliff-edge surprise into a capacity plan.

Checkpoint completion. Query V$SYSTEM_EVENT for events matching log file switch%. log file switch (checkpoint incomplete) means LGWR wants to reuse a redo log group but DBWn has not flushed the associated dirty buffers. Regular occurrences indicate undersized redo logs or too few redo log groups.

Level 4 - Expert

Everything in Level 3, plus the deep signals added after your third or fourth major incident.

SQL plan stability. Track V$SQL for SQL_ID, PLAN_HASH_VALUE, and BUFFER_GETS / EXECUTIONS. Alert when this ratio changes by more than 10x for a high-frequency SQL. Plan regression is the silent killer: a query goes from 10ms to 10 minutes after statistics collection, and at 100 executions per second the system drowns in logical I/O. SQL Plan Baselines (DBMS_SPM) are the intended fix, but most teams do not use them.

ASH analysis. V$ACTIVE_SESSION_HISTORY gives session-level load at 1-second sample resolution, roughly 60 minutes in memory. This requires the Diagnostics Pack license (Enterprise Edition only). Querying it without a license is a compliance violation.

Latch and mutex contention. library cache: mutex X and cursor: pin S wait on X indicate parsing pressure. Track V$SYSSTAT for parse count (hard) and parse count (total). Hard parses should be under 1% of total parses. High hard parse rates mean the application is sending literal SQL instead of using bind variables. This is CPU-intensive and fragments the shared pool.

RAC global cache waits. For RAC deployments, query V$SYSTEM_EVENT for events matching gc%. Average gc wait should be under 1ms on a dedicated interconnect. Above 3ms indicates congestion or workload not partitioned by service. Above 10ms suggests interconnect failure or silent failover to the public network (a 10 to 100x latency jump). Verify V$CLUSTER_INTERCONNECTS to confirm Oracle is using the dedicated interconnect.

Block corruption. Query V$DATABASE_BLOCK_CORRUPTION. Any row means data corruption exists and requires immediate investigation. Run BACKUP VALIDATE CHECK LOGICAL DATABASE in RMAN proactively. Distinguish NOLOGGING-induced corruption (ORA-01578 with ORA-26040, meaning the block was loaded without redo) from physical corruption. The former is a design consequence of NOLOGGING operations plus incomplete recovery, not storage failure.

Undo retention vs longest query. Compare V$UNDOSTAT.MAXQUERYLEN against UNDO_RETENTION. If queries regularly exceed retention, ORA-01555 is likely. UNDO_RETENTION is a best-effort request, not a guarantee, unless RETENTION GUARANTEE is set on the tablespace, which trades ORA-01555 for ORA-30036.

What most teams get wrong

Not monitoring archive destination space at all. The database hangs silently when archive logs cannot be written. Health checks pass. Applications freeze without an error. Teams discover the problem 30 to 60 minutes into a total outage after exhausting other theories. This is the single highest-value signal you can add.

Monitoring hit ratios instead of wait events. Buffer cache hit ratio is the most misused Oracle metric. A system doing SELECT * FROM dual in a loop has 99.99% hit ratio. A system doing massive parallel analytics might have 60% and be performing perfectly. Wait event analysis tells you where time is spent. Hit ratios do not.

No plan regression detection. Most teams discover plan regressions when the application slows catastrophically. Track BUFFER_GETS / EXECUTIONS for top SQL by execution count. Alert on 10x changes.

Relying on instance up/down as availability. An OPEN instance with a frozen archiver passes basic health checks. Health checks must exercise the write path (insert plus commit into a health check table, then delete plus commit), not just SELECT 1 FROM DUAL on an existing pooled connection. A read-only probe may not generate redo and can succeed while the database is frozen. Be aware this write-path probe consumes undo and redo on every check; size the poll interval accordingly.

Not validating backups. RMAN backups can complete successfully and still be unrestorable (missing archive logs, corrupt backup pieces, incorrect catalog entries). Test full restore and recovery on a non-production system.

Ignoring NOLOGGING in Data Guard environments. NOLOGGING operations on the primary leave blocks unrecoverable on the standby. On failover, those blocks appear as corrupt (ORA-26040). Most teams discover this during a failover drill.

How Netdata helps

• Per-second instance and listener checks catch the gap between “instance OPEN” and “actually serving connections” faster than minute-sample monitoring, which matters when the listener is up but all services are BLOCKED.
• Wait event dashboards correlate log file sync, enq: TX, and db file sequential read with CPU utilization and disk I/O latency, so you can distinguish a redo storage problem from a locking cascade in a single view rather than querying multiple views and joining by hand.
• Archive destination and tablespace utilization trended over time give you runway estimation (days_remaining = free_space / daily_growth_rate) instead of a cliff-edge alert at 97%.
• Session count vs PROCESSES as a ratio shows connection-pool creep before ORA-00020, and surfaces MAX_UTILIZATION hits even when current usage has dropped back.
• Undo pressure signals (UNXPSTEALCNT, SSOLDERRCNT, NOSPACEERRCNT) correlated with ORA-01555 and ORA-30036 in the alert log connect the read-path and write-path undo failures that most teams treat as separate incidents.
• Alert log error patterns (ORA-00600, ORA-07445, archiver destination errors, cannot allocate new log) correlated with throughput drops make it obvious when an archive hang pattern is forming rather than a generic slowdown.

Netdata’s Oracle Database monitoring with Netdata brings these signals together with per-second metrics and ML anomaly detection.