Oracle Database Health Check - Complete DBA Checklist with Scripts

Oracle Database Health Check - Complete DBA Guide

A comprehensive Oracle database health check identifies potential issues before they impact production. This guide provides ready-to-run scripts and a systematic checklist for assessing database health across all critical areas.

Quick Health Check Scripts

Get started immediately with our most popular health check scripts:

Check	Script	What It Analyzes
Overall Health	health.sql	Comprehensive framework running multiple checks
Lock Analysis	health2.sql	Blocking locks and Streams capture
I/O Performance	health3.sql	DB file sequential read waits
Redo Performance	health4.sql	Log file sync analysis
Composite Check	health5.sql	Combined diagnostic suite

---

Database Health Check Checklist

1. Instance and Alert Log Status

-- Check instance status
SELECT instance_name, status, database_status,
       host_name, startup_time
FROM v$instance;

-- Check for recent errors in alert log (12c+)
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1
  AND message_text LIKE '%ORA-%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

What to look for:

• Instance status should be “OPEN”
• No recent ORA- errors in alert log
• Startup time indicates last restart

2. Tablespace Space Analysis

-- Tablespace usage summary
SELECT
  tablespace_name,
  ROUND(used_space * 8192 / 1024 / 1024 / 1024, 2) AS used_gb,
  ROUND(tablespace_size * 8192 / 1024 / 1024 / 1024, 2) AS total_gb,
  ROUND(used_percent, 2) AS pct_used
FROM dba_tablespace_usage_metrics
WHERE used_percent > 80
ORDER BY used_percent DESC;

Thresholds:

• 🟢 < 80% - Healthy
• 🟡 80-90% - Monitor
• 🔴 > 90% - Action required

Related scripts: ddbspacea.sql | ddbspaceb.sql

3. Performance Wait Events

-- Top wait events (last hour)
SELECT
  event,
  total_waits,
  ROUND(time_waited_micro/1000000, 2) AS time_waited_sec,
  ROUND(average_wait/1000, 2) AS avg_wait_ms
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

Critical waits to watch:

• db file sequential read - Index I/O issues
• log file sync - Commit performance
• buffer busy waits - Hot blocks
• enq: TX - Row lock contention

Related scripts: vsysev.sql | high_wait_times.sql

4. Active Session Analysis

-- Current active sessions
SELECT
  COUNT(*) AS total_sessions,
  SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active,
  SUM(CASE WHEN status = 'INACTIVE' THEN 1 ELSE 0 END) AS inactive,
  SUM(CASE WHEN blocking_session IS NOT NULL THEN 1 ELSE 0 END) AS blocked
FROM v$session
WHERE type = 'USER';

Related scripts: gvsess.sql | gvlockb.sql

5. Memory (SGA/PGA) Health

-- SGA component sizes
SELECT component, current_size/1024/1024 AS size_mb
FROM v$sga_dynamic_components
WHERE current_size > 0
ORDER BY current_size DESC;

-- PGA usage
SELECT
  ROUND(value/1024/1024, 2) AS pga_target_mb
FROM v$pgastat
WHERE name = 'aggregate PGA target parameter';

Related scripts: vsga.sql | pga.sql

6. Invalid Objects

-- Count invalid objects by type
SELECT object_type, COUNT(*) AS invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY object_type
ORDER BY COUNT(*) DESC;

Action: Run UTL_RECOMP.RECOMP_PARALLEL to fix compilation issues.

Related script: dinvalid.sql

7. Backup Verification

-- Recent backup status
SELECT
  input_type,
  status,
  TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') AS started,
  TO_CHAR(end_time, 'YYYY-MM-DD HH24:MI') AS ended
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;

What to verify:

• Full backup within last week
• Incremental backups running daily
• Archive log backups current
• No FAILED status entries

Related scripts: rman_progress.sql | FRA space

8. Security Quick Check

-- Accounts with default passwords (security risk)
SELECT username, account_status
FROM dba_users_with_defpwd
WHERE account_status = 'OPEN';

-- Failed login attempts (last 24 hours)
SELECT username, COUNT(*) AS failed_attempts
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND returncode != 0
  AND timestamp > SYSDATE - 1
GROUP BY username
HAVING COUNT(*) > 5;

Related scripts: duser.sql | drole.sql

---

Automated Health Check Framework

Our health.sql script provides a complete automated framework:

-- Run comprehensive health check
@health.sql

This executes:

1. random_io - Random I/O performance
2. scattered_io - Multi-block read efficiency
3. cpu_health - CPU utilization patterns
4. interconnect_health - RAC interconnect (if applicable)
5. enqueue - Lock contention analysis

---

Health Check Schedule Recommendations

Check Type	Frequency	Scripts
Quick Status	Daily	health.sql, gvsess.sql
Space Analysis	Weekly	ddbspacea.sql, dsegbig.sql
Performance Deep Dive	Weekly	AWR reports, vsysev.sql
Security Audit	Monthly	duser.sql, dprivall.sql
Full Health Assessment	Quarterly	All scripts + manual review

---

Related Resources

Performance Analysis

• AWR Report Generation - Detailed performance reports
• Performance Scripts (172+) - Complete monitoring toolkit

Administration

• Administration Scripts (46+) - DBA maintenance tools
• Space Management - Tablespace monitoring

Troubleshooting

• Common Oracle Errors - 100+ error resolution guides
• Best Practices - Proactive maintenance tips