A practical toolkit of PowerShell, Python, and SQL*Plus automation for Oracle DBA operations — scheduled health checks, RMAN and capacity reporting, alert-log parsing, log collection and rotation, and HTML/email report delivery across many databases. The repeatable tooling that turns manual morning checks into an unattended, alert-driven routine.
This is the Database Reliability Engineering side of the DBA role: less typing the same checks every day, more building automation that runs itself and pages a human only when something needs attention.
A DBA managing several databases can't hand-run the same checks on each one every morning — it doesn't scale, and it's exactly where things get missed. This toolkit packages those checks as scripts that:
- run on a schedule (Windows Task Scheduler or Linux cron / systemd),
- query many targets from one config-driven run,
- classify findings OK / WARN / ALERT against tunable thresholds,
- render a single HTML report and email it (optionally only on alert), and
- exit non-zero on real problems so a scheduler can branch or page.
Everything authenticates through an Oracle wallet and OS environment variables — no passwords in the repo, config, or command line (see the security doc).
| Component | Supported |
|---|---|
| Oracle Database | 19c, 21c (single-instance & RAC; multitenant-aware) |
| Operating systems | Windows Server and Linux |
| PowerShell | Windows PowerShell 5.1 and PowerShell 7+ (cross-platform) |
| Python | 3.8+ — standard library only (no third-party packages) |
| Oracle client | SQL*Plus / tnsping on PATH; wallet (external password store) |
| Area | Covered |
|---|---|
| Health checks | Instance, ARCHIVELOG, PDB open state, backup, invalids — scheduled |
| Capacity | Tablespace (% of max), TEMP, FRA, DB size — threshold alerts |
| Performance | Active sessions, cross-instance blocking, top SQL |
| Backups | RMAN job status, last-success-by-type, FRA usage, age alerts |
| Diagnostics | Alert-log parsing (ORA-/events), evidence log-bundle collection |
| Delivery | Self-contained HTML report + SMTP email (alert-only option) |
| Operations | Log rotation/pruning; least-privilege, wallet-based credentials |
All scripts, configs, sample outputs, and docs are fully sanitized. They
contain no real hostnames, IPs, ports, service names, DBIDs, SIDs,
schema/user names, credentials, or employer/company data. Names such as
ORADEMO, SALES_PDB, APPDB01, APPDB02, monitor_user, and
demo.example.local are fictional examples. Only the *.example.json
config templates are committed — real configs and any wallet/secret are
git-ignored. Test in a non-production environment and apply under your own
change-control process.
oracle-automation-toolkit/
├── README.md
├── LICENSE
├── .gitignore
├── banner.png
├── powershell/ # 6 PowerShell automation scripts
├── python/ # 5 Python report/parsing scripts (stdlib only)
├── sqlplus/ # 3 spoolable SQL*Plus wrapper queries
├── config/ # 3 *.example.json templates (copy + edit; real ones ignored)
├── docs/ # scheduling, configuration, troubleshooting, security
├── sample_outputs/ # fictional, sanitized example output for the scripts
└── screenshots/ # sanitized captures — see "Operational Screenshots"
| Script | What it does |
|---|---|
Invoke-OracleHealthCheck.ps1 |
Run the health pass across targets; classify OK/WARN/ALERT; optional HTML |
Invoke-RmanReport.ps1 |
RMAN backup-health per target; flag stale backups vs threshold |
Check-OracleListener.ps1 |
tnsping + optional deep connect probe per target (frequent schedule) |
Collect-OracleLogs.ps1 |
Zip an evidence bundle: alert-log tail + recent traces + listener log |
Send-HealthCheckReport.ps1 |
Run the check and email the HTML report (alert-only option) |
Rotate-OracleLogs.ps1 |
Compress/prune generated reports & logs (WhatIf-safe) |
| Script | What it does |
|---|---|
oracle_health_report.py |
Multi-target health/capacity/perf → one dark-theme HTML report |
parse_alert_log.py |
Scan an alert log for ORA-/events in a window; JSON or summary; exit code |
tablespace_threshold_report.py |
Tablespace % of max across targets → CSV + WARN/ALERT; exit on ALERT |
failed_jobs_report.py |
DBMS_SCHEDULER FAILED jobs in a window across targets |
blocking_session_report.py |
Cross-instance blocking chains; flag long blocks; exit on ALERT |
| Script | What it does |
|---|---|
run_health_checks.sql |
Availability/recoverability snapshot as pipe-delimited lines |
run_capacity_checks.sql |
Tablespace/TEMP/FRA/DB-size snapshot |
run_performance_checks.sql |
Sessions, blocking, top SQL snapshot |
Sanitized, annotated sample output for the scripts is in
sample_outputs/.
db_targets.json ──┐
thresholds.json ──┤ ┌─ sqlplus/*.sql (spoolable, pipe-delimited) ─┐
email_settings ───┘ │ ▼
│ │ PowerShell / Python parse + classify (OK/WARN/ALERT)
▼ │ │
scheduler (Task Sched / cron / systemd) ▼
│ HTML report ──► SMTP email
└──────────────► runs unattended ──────────────────────► (alert-only optional)
credentials: Oracle WALLET (/@alias) + env vars (never in the repo)
1. Configure (copy the templates; real names are git-ignored):
cp config/db_targets.example.json config/db_targets.json
cp config/thresholds.example.json config/thresholds.json
cp config/email_settings.example.json config/email_settings.json
Set up a wallet alias per target so sqlplus -s /@<alias> works without a password
(see Security and Credential Handling).
2. Run on demand:
# Windows / PowerShell
.\powershell\Invoke-OracleHealthCheck.ps1 -Target APPDB01 -Html
.\powershell\Send-HealthCheckReport.ps1 -AlertOnly# Linux / Python
python3 python/oracle_health_report.py --config config --out reports
python3 python/tablespace_threshold_report.py --config config
python3 python/parse_alert_log.py /path/alert_ORADEMO.log --hours 24Windows Task Scheduler (daily 07:00):
$a = New-ScheduledTaskAction -Execute 'pwsh.exe' `
-Argument '-NoProfile -File "C:\oracle-automation-toolkit\powershell\Send-HealthCheckReport.ps1" -AlertOnly'
$t = New-ScheduledTaskTrigger -Daily -At 7:00am
Register-ScheduledTask -TaskName 'Oracle Health Report' -Action $a -Trigger $t -User 'DEMO\oracle_svc'Linux cron (daily 07:00 + 5-minute blocking probe):
0 7 * * * /opt/oracle-automation-toolkit/run_health.sh >> /var/log/ora_health.log 2>&1
*/5 * * * * /opt/oracle-automation-toolkit/python/blocking_session_report.py --config /opt/oracle-automation-toolkit/config >> /var/log/ora_block.log 2>&1Full setup in Windows Task Scheduler Setup and Linux Cron Setup.
Scripts describe intent; these show the work. Real terminal runs and the rendered
report from a sanitized lab (APPDB01 / APPDB02, ORADEMO), tracing the loop:
run across many databases → classify → render → schedule → deliver. Every
value is fictional — no hostnames, services, or company data. The point is the
engineering judgment: make checks repeatable, threshold-driven, unattended,
and credential-safe.
What it demonstrates. One command checks two databases, classifies each finding OK/WARN/ALERT, returns a per-target severity rollup, and writes an HTML report.
Why a senior DBA cares. This is automation, not a one-off script — it's config-driven (add a target in JSON, not a code change) and severity-aware, which is what makes it safe to schedule and trust.
Action that should follow. Investigate the WARN (here, 3 invalid objects — recompile and re-check); schedule the script to run unattended and email on alert.
What it demonstrates. The generated self-contained HTML report — color-coded OK/WARN/ALERT across both databases — produced identically by the PowerShell and Python paths.
Why a senior DBA cares. Raw script output isn't a deliverable; a clean, skimmable report is. A team can read it in five seconds and know exactly what needs attention — and it's one file, easy to email or archive.
Action that should follow. Wire it into the daily email (Send-HealthCheckReport.ps1
or the cron Python job) and trend the WARN/ALERT rows for capacity planning.
What it demonstrates. The health report registered as a daily 07:00 task under a dedicated low-privilege service account — plus a frequent listener probe and a weekly log rotation.
Why a senior DBA cares. "Runs unattended" is the entire point of automation. Running under a dedicated service account (not a personal admin login) with no human in the loop is what separates a script from a reliability system.
Action that should follow. Confirm the task runs whether the user is logged on or not, set a max runtime, and check Task Scheduler / cron history for missed runs. See Windows Task Scheduler Setup.
What it demonstrates. A multi-MB alert log reduced to a short, actionable summary for a time window — counts, top ORA- codes, samples — with a non-zero exit when an archiver/corruption/tablespace event appears.
Why a senior DBA cares. Nobody reads the whole alert log daily; automation that surfaces only what matters — and signals it via exit code — is how you catch an ORA-00257 the morning it happens instead of during the next outage.
Action that should follow. Schedule it; branch on the exit code to page
specifically on archiver/corruption/space events. Pair with Collect-OracleLogs.ps1
to bundle evidence when something fires.
What it demonstrates. The report arriving in the team inbox with a severity in the subject line — detection → report → delivery, fully automated.
Why a senior DBA cares. A report nobody sees is worthless. Delivery (with an alert-only option to avoid noise) is what makes the automation operational — and the SMTP password came from an environment variable, never the repo.
Action that should follow. Use -AlertOnly once the baseline is quiet so the
team is paged only on WARN/ALERT, and route alerts to the on-call channel.
All screenshots are fully sanitized and fictional.
APPDB01,APPDB02,ORADEMO,SALES_PDB,monitor_user,demo.example.local, and all values are illustrative demo data — no production, employer, or confidential information is shown. Each capture mirrors the annotated output insample_outputs/.
oracledb(python-oracledb) thin-mode option to query without a local client, alongside the SQL*Plus path.- Pushgateway / Prometheus exporter so metrics feed an existing monitoring stack, not just email.
- Slack / Teams webhook delivery in addition to SMTP.
- Trend store — append capacity metrics to a small table/CSV for growth charts.
- Self-test /
--dry-runmode that validates config and connectivity without querying. - Data Guard & RAC report sections wired into the same scheduled run.
Part of a broader Oracle DBA portfolio (github.com/uusen01):
oracle-rman-scripts · oracle-patching-runbooks · oracle-performance-tuning ·
oracle-cdb-pdb-administration · oracle-health-checks · oracle-dba-runbooks ·
oracle-goldengate-configs · oracle-dataguard-runbooks · oracle-rac-administration.
Released under the MIT License — free to use, adapt, and share with attribution.