Oracle Health Engine is an enterprise-grade PowerShell framework designed to perform automated health checks across multiple Oracle databases.
The engine validates database availability, connectivity, authentication, time synchronization, and executes a configurable set of SQL diagnostic checks. Results are collected, logged, packaged, and distributed automatically via email.
The framework is designed for DBA teams, operations groups, managed service providers, and enterprise monitoring environments.
- Multi-database execution
- Centralized configuration
- Oracle TNS validation
- Database connectivity validation
- Database time synchronization validation
- SQL diagnostics framework
- Query latency tracking
- Unified execution logging
- State machine monitoring
- Automatic ZIP packaging
- Email reporting
- UTF-8 / Unicode support
- Hebrew-safe output handling
- JSON-driven SQL execution
- No database-side objects required
+--------------------+
| databases.list |
+--------------------+
|
v
+--------------------+
| Oracle Health |
| Engine |
+--------------------+
|
+----------------------+
| |
v v
TNS Validation Authentication
|
v
Time Validation
|
v
SQL Health Checks
|
v
Output Generation
|
v
ZIP Package
|
v
Email Report
health_check_report
│
├── CONFIG
│ ├── databases.list
│ ├── mail.config
│ ├── oracle_health_checks.json
│ └── AI_prompt.md
│
├── SQL_OUTPUT
│
├── SQL_LOGS
│
└── health_check_report.ps1
Contains the list of Oracle databases to be checked.
Example:
PRDDB
TESTDB
DEVDB
Lines beginning with:
#
are treated as comments.
Defines SMTP and email settings.
Example:
MAIL_TO=dba_team@company.com
MAIL_CC=manager@company.com
MAIL_FROM=oracle-monitor@company.com
SMTP_SERVER=smtp.company.com
Contains all SQL tests executed by the framework.
Example:
{
"health_checks": [
{
"test_name": "Tablespace Usage",
"select_statement": "select * from dba_tablespace_usage_metrics"
}
]
}The framework dynamically loads all checks from this file.
No PowerShell code changes are required when adding new checks.
Optional AI header file.
The content is inserted at the beginning of each output report and can be used later by AI tools for analysis and summarization.
The framework loads all databases from:
databases.list
Each database is processed independently.
The framework executes:
tnsping <database>
Purpose:
- Verify Oracle Net connectivity
- Verify listener availability
- Verify TNS configuration
Failure result:
SKIPPED (TNS FAILED)
The framework validates database login using:
select 1 from dual;Purpose:
- Validate username
- Validate password
- Validate account status
Detects:
- ORA-01017
- ORA-28000
- ORA-28001
Failure result:
FAILED
The framework executes:
select to_char(
systimestamp,
'YYYY-MM-DD HH24:MI:SS.FF6')
from dual;Purpose:
- Compare Oracle server time
- Compare monitoring server time
Validation threshold:
10 minutes
Failure result:
FAILED (TIME DRIFT)
All SQL statements defined in:
oracle_health_checks.json
are executed.
Each test runs independently.
For each test the framework records:
- Test name
- Start time
- End time
- Execution latency
- SQL output
- Errors
For every SQL statement:
Latency = End Time - Start Time
Recorded in milliseconds.
Example:
QUERY=Tablespace Usage LATENCY=135ms
Benefits:
- Identify slow queries
- Identify overloaded databases
- Trend analysis
Each database receives a final state.
All validations and SQL checks completed successfully.
Database is operational but one or more non-critical issues were detected.
Examples:
- Slow SQL execution
- Missing output
- Partial results
Critical failure occurred.
Examples:
- Authentication failure
- ORA errors
- Time drift
- SQL execution failures
Database was intentionally skipped.
Examples:
- TNS failure
- No connectivity
- Missing timestamp validation
The framework produces a unified log file.
Location:
SQL_LOGS\run.log
Example:
2026-06-11 11:35:00 | INFO | PRDDB | START DB CHECK
2026-06-11 11:35:02 | INFO | PRDDB | QUERY=Tablespace Usage LATENCY=140ms
2026-06-11 11:35:03 | INFO | PRDDB | FINISHED
For each database:
SQL_OUTPUT
contains:
<database>-output.txt
Contents:
- AI header
- SQL results
- Execution statistics
- Errors
- Latency information
At completion the framework automatically creates:
DB_OUTPUT_YYYYMMDD_HHMM.zip
The ZIP contains:
- All database output reports
The ZIP does not contain:
- Logs
- Configuration files
- Credentials
The framework automatically sends an HTML email.
Includes:
- Successful databases
- Failed databases
- Skipped databases
- Warning databases
- Execution summary
Attachments:
- ZIP report package
- run.log
Preferred:
ORACLE_DB_PASSWORD
Environment variable.
Fallback:
Interactive password prompt.
Passwords are never written to:
- Logs
- Output files
- Email reports
The framework is designed for:
- English
- Hebrew
- UTF-8
- AL32UTF8 Oracle environments
Settings:
$env:NLS_LANG=".AL32UTF8"- Daily health checks
- Capacity monitoring
- Tablespace monitoring
- Performance validation
- Multi-customer monitoring
- SLA verification
- Daily reporting
- Oracle environment validation
- Change verification
- Post-maintenance checks
Oracle Health Engine v9.x
Enterprise PowerShell framework for Oracle database health monitoring, diagnostics, reporting, and operational visibility.