Skip to content

Daily Health Check Procedure.md

Latest commit

 

History

History
125 lines (81 loc) · 4.95 KB

Daily Health Check Procedure.md

File metadata and controls

125 lines (81 loc) · 4.95 KB
Raw

Daily Oracle Health Check Procedure

A repeatable morning routine for confirming an Oracle 19c/21c database is healthy, recoverable, and performing well. The goal is a consistent 10–15 minute pass that catches problems before users or applications report them.

Scope: Single-instance and RAC, on-prem or cloud. Run from a DBA account with SELECT_CATALOG_ROLE (or equivalent SELECT on the V$/DBA views each script uses). All scripts are read-only except where a clearly commented action template is shown.

When to run

Run once at the start of each business day, before peak load. In environments with overnight batch, run after the batch window completes so you catch any failures it produced. Re-run individual scripts on demand during an incident.

The checklist (run in this order)

The order is deliberate: confirm the database is up and recoverable first, then look at space, then at workload. Each step lists what "good" looks like and the red flag that means stop and investigate.

1. Availability and recoverability — db_status.sql

Confirms the instance is OPEN, the database role and open mode are expected, and the database is in ARCHIVELOG mode.

  • Good: STATUS = OPEN, OPEN_MODE = READ WRITE (primary), LOG_MODE = ARCHIVELOG, uptime consistent with the last planned restart.
  • Red flag: unexpected short uptime (unplanned restart), NOARCHIVELOG mode, or a RAC node not OPEN. Investigate the alert log immediately.

2. Archiving health — archive_log_status.sql

The single most important "will this database stay up" check. A full Fast Recovery Area stalls the archiver and hangs the database (ORA-00257).

  • Good: all archive destinations VALID, FRA PCT_USED comfortably below 90%, archive generation in line with normal daily volume.
  • Red flag: any destination in ERROR, FRA above 90%, or a sudden spike/drop in archive volume. Act before it becomes an outage.

3. Permanent space — tablespace_usage.sql

Catches tablespaces approaching their autoextend ceiling.

  • Good: no tablespace flagged ALERT (default threshold 85% of max).
  • Red flag: any ALERT row. Add a datafile or raise MAXSIZE the same day.

4. Temp space — temp_usage.sql

Confirms TEMP has headroom and identifies any session monopolizing it.

  • Good: TEMP PCT_USED moderate; no single session holding an outsized amount.
  • Red flag: TEMP near full or one session holding many GB — tune the offending SQL rather than just enlarging TEMP.

5. Footprint and growth — database_size.sql

Capture for trending. Not an alarm on any given day, but the data that drives capacity planning.

  • Good: growth in line with the trend.
  • Red flag: a step change in size — investigate what loaded or stopped purging.

6. Object validity — invalid_objects.sql

Surfaces broken packages/views/triggers, common after a deployment or patch.

  • Good: no invalid application objects.
  • Red flag: invalids that fail to recompile — a real dependency break, not noise.

7. Scheduled jobs — failed_jobs.sql

Confirms automated maintenance (stats, purges, backups, ETL) actually ran.

  • Good: no failed runs in the lookback window; no broken/unexpectedly-disabled jobs.
  • Red flag: repeated failures or a job about to go BROKEN. Fix root cause and re-run.

8. Concurrency — session_count.sql

Checks session/process headroom and watches for connection-pool leaks.

  • Good: current well below the processes/sessions limit; inactive count stable.
  • Red flag: utilization creeping toward the limit, or a steadily climbing INACTIVE count from one pool.

9. Locking — blocking_sessions.sql

Identifies blocker→waiter chains behind "the app is hung" reports.

  • Good: no rows (no blocking).
  • Red flag: a root blocker holding locks for minutes. Confirm with the app owner, then commit/kill the blocker.

10. Live workload — top_sessions.sql

Shows the heaviest active sessions and the dominant wait event.

  • Good: waits dominated by ON CPU/normal I/O; no single runaway session.
  • Red flag: a session with massive logical I/O (often a full scan / missing index), or waits dominated by contention. Capture the SQL_ID for tuning.

Recording results

Keep a short daily log (date, who ran it, anything flagged, action taken). Even one line per check builds the trend data that turns reactive firefighting into proactive capacity and performance management. The flagged items become your work queue for the day; the clean checks become evidence the environment is under control.

Escalation

If any check exposes an availability or recoverability risk you cannot resolve quickly — archiver stuck, FRA full, a tablespace that cannot extend, a database not open — treat it as an incident: notify stakeholders, open a record, and work the issue rather than continuing the routine. See Troubleshooting Guide.md for the common scenarios and their fixes.