Oracle DB Health Check Scripts: Real-World DBA Monitoring Guide

In day-to-day Oracle Database and E-Business Suite (EBS) administration, proactive monitoring is critical to ensure system stability, performance, and availability. As part of my real-world DBA experience, I created a set of health check scripts to quickly assess the status of key database components.

---

Objective

The goal of these scripts is to provide a quick and efficient health check covering database status, instance status, tablespace usage, invalid objects, and RMAN backup status — all from a single SQL*Plus session.

---

Solution Overview

I developed a SQL-based script (hc.sql) that gathers essential health metrics from Oracle dynamic views and DBA tables. The scripts are designed to be lightweight, fast, and provide immediate actionable insights.

---

GitHub Repository

The complete script is available on GitHub:
https://github.com/Syedanwarahmed/scripts_for_healthcheck

---

Key Features

• Database and instance status check
• Tablespace usage monitoring
• Active session tracking
• Invalid object detection
• RMAN backup job verification
• Easy execution using SQL*Plus

---

How to Use

sqlplus / as sysdba
@hc.sql

---

Sample Checks Included

Database Status

SELECT NAME, OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

Tablespace Usage

SELECT tablespace_name,
       ROUND(used_space * 8192 / 1024 / 1024, 2) used_mb,
       ROUND(tablespace_size * 8192 / 1024 / 1024, 2) total_mb,
       ROUND(used_percent, 2) pct_used
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Active Sessions

SELECT COUNT(*) active_sessions FROM V$SESSION WHERE STATUS='ACTIVE';

-- Session breakdown by status
SELECT status, COUNT(*) cnt
FROM v$session
GROUP BY status
ORDER BY cnt DESC;

Invalid Objects

SELECT owner, object_type, COUNT(*) cnt
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY cnt DESC;

RMAN Backup Status

SELECT status, start_time, end_time,
       ROUND((output_bytes/1024/1024/1024),2) output_gb
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY start_time DESC
FETCH FIRST 5 ROWS ONLY;

---

Real-World Value

These scripts are derived from real-time production support scenarios where quick diagnosis is required during high CPU issues, backup failures, tablespace alerts, and performance degradation. Having a single script to validate system health saves valuable time during critical situations.

---

Future Enhancements

I plan to enhance this repository further by adding shell automation scripts, alerting mechanisms, integration with monitoring tools, and additional EBS-specific checks.

---

Conclusion

A well-designed health check script is an essential tool for every DBA. It not only helps in proactive monitoring but also ensures faster troubleshooting and improved system reliability. Feel free to explore the repository, use the scripts, and share your feedback.

---

Written by Syed Anwar Ahmed — Oracle Apps DBA with 11 years of production experience.
Connect: sdanwarahmed@gmail.com  |  LinkedIn  |  GitHub