Oracle Database Health Check & Monitoring Tool
A multi-instance Oracle monitoring solution that automatically discovers all Oracle Homes on the server - using both predefined paths and dynamic search - and generates a structured, color-coded HTML report with a full Table of Contents and a consolidated Summary Issues panel. For each Oracle Home, the script dynamically detects listeners from listener.ora, checks their status, and flags instances that are not READY or have zero handlers. For each discovered SID, it collects: database info & role, active sessions, pluggable databases (PDBs), user account status (locked/expired), tablespace usage, Data Guard status, RMAN backup history, alert log errors, and more. The report highlights critical issues inline (disk space, ORA- errors, failed backups) and aggregates them into a top-level Summary Issues section for quick triage. An email is automatically sent to the DBA team containing a plain-text summary of uptime, last backup status per database, and all actionable issues - with the full HTML report attached. A configurable exclusion file filters out known false positives before the email is dispatched. A built-in timeout (1000 seconds) ensures the script never hangs indefinitely in production environments.
Files
(2 files)Usage Instructions
Prerequisites
Must be executed as the oracle OS user (the script connects via / as sysdba).
sqlplus, lsnrctl, and optionally dgmgrl must be accessible under the detected Oracle Home(s).
mailx must be installed and configured on the server to enable email delivery.
The ~/.bash_profile of the oracle user should have the standard Oracle environment variables (ORACLE_BASE, LD_LIBRARY_PATH, etc.) so the script sources them on startup.
Installation
bash# 1. Place the script in the standard scripts directory
cp oracle_monitor_linux.sh /home/oracle/scripts/
# 2. Make it executable
chmod +x /home/oracle/scripts/oracle_monitor_linux.sh
# 3. Place the exclusion file in the same directory
cp oracle_monitor_exclusions.txt /home/oracle/scripts/
Configuration
Before first run, review and update the following variables inside the script:
1. Oracle Homes (if auto-detection fails)
The script searches a predefined list of paths first. Add any non-standard Oracle Home paths to the array near the top of the script:
bashORACLE_POSSIBLE_HOMES=(
"/u01/app/oracle/product/19.3.0.0/dbhome_1"
"/u01/app/oracle/product/12.2.0.1/dbhome_2"
# Add your paths here
)
If none of the predefined paths match, the script falls back to a dynamic find search under /u01 and /opt automatically.
2. Email Settings
bashTO="[email protected]"
FROM="[email protected]"
SUBJECT="Oracle Monitor Report - $(basename "$OUTPUT_FILE") - $(hostname)"
3. Exclusion File Path
bashEXCLUSION_FILE="/home/oracle/scripts/oracle_monitor_exclusions.txt"
4. Data Guard Password
The script uses a hardcoded credential for dgmgrl. Update it to match your environment:
bash# Search for this pattern in the script and update:
dgmgrl sys/Ora2022_2022
5. Execution Timeout
Default is 1000 seconds. Increase if you have many databases or slow connections:
bashTIMEOUT=1000
Managing the Exclusion File
The exclusion file filters out known false positives from the email summary. Add one pattern per line — partial, case-insensitive matches are supported. Lines starting with # are treated as comments.
bash# /home/oracle/scripts/oracle_monitor_exclusions.txt
# Listener config noise
SID_LIST_LISTENER
SECURE_REGISTER_LISTENER
# Known non-Oracle instances registered in listener
MSSQLmang
MSSQLPAN
# Known benign alert log patterns
Tns error struct
# Suppress a specific ORA- error across all DBs
# ORA-00001
# Suppress a specific tablespace from alerts
# TEMP tablespace
Note: The full HTML report always contains all findings. The exclusion file only affects what appears in the email body, not the report itself.
Running the Script
bash# Run manually
/home/oracle/scripts/oracle_monitor_linux.sh
# Run and capture stdout/stderr to a log file
/home/oracle/scripts/oracle_monitor_linux.sh >> /home/oracle/scripts/monitor_run.log 2>&1
The script will print progress to stdout as it processes each Oracle Home and SID.
Scheduling with Cron
bashcrontab -e # as oracle user
cron# Run every day at 7:00 AM
0 7 * * * /home/oracle/scripts/oracle_monitor_linux.sh >> /home/oracle/scripts/monitor_run.log 2>&1
# Run twice daily — 7:00 AM and 7:00 PM
0 7,19 * * * /home/oracle/scripts/oracle_monitor_linux.sh >> /home/oracle/scripts/monitor_run.log 2>&1