#!/bin/bash # Oracle DB Health Check with HTML Email using mail command # Configuration DB_USER="sys as sysdba" DB_PASS="your_password" DB_SID="ORCL" REPORT_FILE="/tmp/db_health_$(date +%Y%m%d).html" EMAIL_TO="dba@company.com" EMAIL_FROM="dba@company.com" EMAIL_SUBJECT="DB Health Report - $(date +%F)" # Function: Send HTML email using mail send_email() { local report_file="$1" local subject="$2" local recipient="$3" local sender="$4" if ! command -v mail >/dev/null; then echo "Error: mail command not found!" return 1 fi # Send email with HTML headers mail -s "$subject" -a "From: $sender" -a "Content-Type: text/html" "$recipient" < "$report_file" return $? } # Function: Run SQL and format as HTML table run_sql_html() { local sql_query="$1" local section_title="$2" echo "

$section_title

" >> "$REPORT_FILE" sqlplus -S /nolog << EOF >> "$REPORT_FILE" connect $DB_USER/$DB_PASS@$DB_SID set markup html on table "class='sql-table'" set pagesize 500 set linesize 200 set feedback off set heading on $sql_query exit EOF } # Generate HTML Report { echo "

Database Health Report

Generated: $(date)

" } > "$REPORT_FILE" # Run health checks run_sql_html "SELECT name, open_mode, log_mode, created FROM v\$database;" "Database Status" run_sql_html "SELECT inst_id, instance_name, status, host_name FROM gv\$instance;" "Instance Status" run_sql_html "SELECT tablespace_name, round(used_space/1024/1024) used_gb, round(tablespace_size/1024/1024) total_gb, round(used_percent) pct_used FROM dba_tablespace_usage_metrics ORDER BY pct_used DESC;" "Tablespace Usage" run_sql_html "SELECT event, total_waits, round(time_waited_micro/1000000) wait_sec FROM ( SELECT event, total_waits, time_waited_micro FROM v\$system_event WHERE wait_class != 'Idle' ORDER BY time_waited_micro DESC ) WHERE rownum <= 5;" "Top 5 Wait Events" echo "" >> "$REPORT_FILE" # Send email with error handling if send_email "$REPORT_FILE" "$EMAIL_SUBJECT" "$EMAIL_TO" "$EMAIL_FROM"; then echo "Health report sent successfully to $EMAIL_TO" else echo "Failed to send health report" >&2 exit 1 fi # Cleanup rm -f "$REPORT_FILE"