#!/bin/bash
# Configuration
ORACLE_USER="remote_user"
ORACLE_PASS="password"
RAC_NODE="rac_node1"
EMAIL_RECIPIENT="admin@example.com"
REPORT_FILE="/tmp/oracle_health_report.html"
# Create HTML Report Header
cat << EOF > $REPORT_FILE
Oracle RAC Health Check Report
Oracle RAC Health Check Report
Generated on: $(date)
EOF
# Run Database Checks
sqlplus -s /nolog << EOF >> $REPORT_FILE
connect $ORACLE_USER/$ORACLE_PASS@$RAC_NODE
set markup html on pre off entmap off
set pagesize 500 linesize 200
set feedback off heading on
prompt
Recent ORA Errors (Last 5 Minutes)
SELECT
INST_ID,
TO_CHAR(ORIGINATING_TIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP,
MESSAGE_TEXT
FROM GV\$DIAG_ALERT_EXT
WHERE
MESSAGE_TEXT LIKE 'ORA-%'
AND ORIGINATING_TIMESTAMP >= SYSDATE - 5/1440
ORDER BY ORIGINATING_TIMESTAMP DESC;
prompt
Top Waiting Events (Last 5 Minutes)
SELECT
INST_ID,
EVENT,
COUNT(*) AS WAIT_COUNT,
ROUND(COUNT(*)*100/SUM(COUNT(*)) OVER(), 2) AS PCT
FROM GV\$ACTIVE_SESSION_HISTORY
WHERE
SAMPLE_TIME >= SYSDATE - 5/1440
AND EVENT IS NOT NULL
GROUP BY INST_ID, EVENT
ORDER BY WAIT_COUNT DESC;
exit
EOF
# Add HTML Footer
cat << EOF >> $REPORT_FILE
EOF
# Send Email with HTML Report
mailx -s "$(echo -e "Oracle RAC Health Check Report\nContent-Type: text/html")" \
$EMAIL_RECIPIENT < $REPORT_FILE
# Cleanup
rm -f $REPORT_FILE
####
#!/bin/bash
set -eo pipefail
trap 'echo "Error at line $LINENO"; exit 1' ERR
# Configuration
REMOTE_USER="oracleuser"
REMOTE_HOSTS=("rac-node1" "rac-node2") # Multiple RAC nodes
ORACLE_SID="ORCLCDB"
ORACLE_HOME="/u01/app/oracle/product/19.0.0/dbhome_1"
EMAIL_RECIPIENT="dba@example.com"
EMAIL_SUBJECT="Oracle RAC Health Check Report - $(date +%Y%m%d)"
REPORT_FILE="/tmp/rac_health_report.html"
LOG_FILE="/tmp/rac_health_check.log"
AWK_SCRIPT="/tmp/rac_stats.awk"
declare -A THRESHOLDS=(
[TABLESPACE]=90
[WAIT_TIME]=1000
[CPU_PER_SESSION]=75
[ASM_FREE]=10
)
# Initialize files
: > "$REPORT_FILE"
: > "$LOG_FILE"
# Create HTML template
cat >> "$REPORT_FILE" << EOF
Oracle RAC Health Check Report
Oracle RAC Health Check Report
Generated on: $(date "+%Y-%m-%d %H:%M:%S")
EOF
# Function to run remote SQL
run_remote_sql() {
local host=$1
local sql=$2
ssh -q "$REMOTE_USER@$host" "
export ORACLE_SID=$ORACLE_SID
export ORACLE_HOME=$ORACLE_HOME
\$ORACLE_HOME/bin/sqlplus -S / as sysdba << SQLEND
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
$sql
SQLEND"
}
# Cluster Health Check
echo "
► Cluster Health
" >> "$REPORT_FILE"
echo "
" >> "$REPORT_FILE"
for host in "${REMOTE_HOSTS[@]}"; do
echo "
Node: $host
" >> "$REPORT_FILE"
CLUSTER_STATUS=$(ssh -q "$REMOTE_USER@$host" "crsctl check cluster -all; crsctl stat res -t")
echo "
$CLUSTER_STATUS
" >> "$REPORT_FILE"
done
echo "
" >> "$REPORT_FILE"
# AWR/ASH Report Generation
echo "
► AWR/ASH Reports
" >> "$REPORT_FILE"
echo "
" >> "$REPORT_FILE"
for host in "${REMOTE_HOSTS[@]}"; do
AWR_REPORT=$(run_remote_sql "$host" "
DECLARE
dbid NUMBER;
inst NUMB := 1;
bid NUMBER;
eid NUMBER;
BEGIN
SELECT dbid INTO dbid FROM v\$database;
SELECT max(snap_id)-1 INTO bid FROM dba_hist_snapshot;
SELECT max(snap_id) INTO eid FROM dba_hist_snapshot;
DBMS_WORKLOAD_REPOSITORY.create_awr_report_html(
l_dbid => dbid,
l_inst_num => inst,
l_bid => bid,
l_eid => eid,
l_options => 0
);
END;
/")
echo "
$host AWR Report
$AWR_REPORT
" >> "$REPORT_FILE"
ASH_REPORT=$(run_remote_sql "$host" "
DECLARE
dbid NUMBER;
ash_report CLOB;
BEGIN
SELECT dbid INTO dbid FROM v\$database;
ash_report := DBMS_WORKLOAD_REPOSITORY.ash_report_html(
l_dbid => dbid,
l_inst_num => NULL,
l_btime => SYSDATE-1/24,
l_etime => SYSDATE
);
DBMS_OUTPUT.put_line(ash_report);
END;
/")
echo "
$host ASH Report
$ASH_REPORT
" >> "$REPORT_FILE"
done
echo "
" >> "$REPORT_FILE"
# Historical Trend Analysis
echo "
► Historical Trends
" >> "$REPORT_FILE"
echo "
" >> "$REPORT_FILE"
for host in "${REMOTE_HOSTS[@]}"; do
TRENDS=$(run_remote_sql "$host" "
SELECT
metric_name || '|' ||
ROUND(AVG(average),2) || '|' ||
ROUND(MAX(maxval),2) || '|' ||
ROUND(MIN(minval),2)
FROM dba_hist_sysmetric_summary
WHERE group_id = 2
AND metric_name IN ('CPU Usage Per Sec', 'Database CPU Time Ratio', 'Database Wait Time Ratio')
GROUP BY metric_name
ORDER BY metric_name")
echo "
$host Metrics (7-day avg)
" >> "$REPORT_FILE"
echo "
| Metric | Avg | Max | Min |
|---|
" >> "$REPORT_FILE"
while IFS='|' read -r metric avg max min; do
echo "| $metric | $avg | $max | $min |
" >> "$REPORT_FILE"
done <<< "$TRENDS"
echo "
" >> "$REPORT_FILE"
done
echo "
" >> "$REPORT_FILE"
# Backup Status Check
echo "
► Backup Status
" >> "$REPORT_FILE"
echo "
" >> "$REPORT_FILE"
for host in "${REMOTE_HOSTS[@]}"; do
BACKUP_STATUS=$(run_remote_sql "$host" "
SELECT
job_id || '|' ||
TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI') || '|' ||
status || '|' ||
ROUND(input_bytes/1024/1024) || '|' ||
ROUND(output_bytes/1024/1024)
FROM v\$rman_backup_job_details
ORDER BY start_time DESC
FETCH FIRST 5 ROWS ONLY")
echo "
$host Recent Backups
" >> "$REPORT_FILE"
echo "
| Job ID | Start Time | Status | Input MB | Output MB |
|---|
" >> "$REPORT_FILE"
while IFS='|' read -r job_id start_time status input output; do
[[ "$status" != "COMPLETED" ]] && class="critical" || class=""
echo "| $job_id | $start_time | $status | $input | $output |
" >> "$REPORT_FILE"
done <<< "$BACKUP_STATUS"
echo "
" >> "$REPORT_FILE"
done
echo "
" >> "$REPORT_FILE"
# JavaScript for collapsible sections
cat >> "$REPORT_FILE" << EOF
EOF
# Send email with mailx
(
echo "From: Oracle Health Check
"
echo "To: $EMAIL_RECIPIENT"
echo "Subject: $EMAIL_SUBJECT"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo
cat "$REPORT_FILE"
) | mailx -s "$EMAIL_SUBJECT" -a "$REPORT_FILE" "$EMAIL_RECIPIENT"
# Cleanup
rm -f "$REPORT_FILE" "$LOG_FILE"
#!/bin/bash
# Configuration (Modify these parameters)
DB_USER="your_db_user"
DB_PASS="your_db_password"
DB_HOST="your_db_host"
DB_SERVICE="your_db_service"
EMAIL_TO="your_email@example.com"
EMAIL_SUBJECT="Oracle RAC Health Check Report"
HTML_REPORT="/tmp/oracle_rac_health_check.html"
# Start HTML Output
echo "Oracle RAC Health Check" > $HTML_REPORT
echo "Oracle RAC Database Health Check Report
" >> $HTML_REPORT
echo "
" >> $HTML_REPORT
# Function to run SQL and format output as HTML
run_sql() {
local title=$1
local sql_query=$2
echo "$title
" >> $HTML_REPORT
echo "" >> $HTML_REPORT
sqlplus -s "$DB_USER/$DB_PASS@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=$DB_HOST)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=$DB_SERVICE)))" <> $HTML_REPORT
SET LINESIZE 200
SET PAGESIZE 100
SET TRIMSPOOL ON
SET HEADING OFF
$sql_query
EXIT;
EOF
echo "
" >> $HTML_REPORT
}
# 1) All Instance / DB Status
run_sql "RAC Instance and Database Status" "SELECT inst_id, instance_name, status, database_status FROM GV\$INSTANCE;"
# 2) Long Running Sessions (active > 1 hour)
run_sql "Long Running Sessions (per instance)" "
SELECT inst_id, sid, serial#, username, status, sql_id, last_call_et
FROM GV\$SESSION
WHERE status='ACTIVE' AND last_call_et > 3600 ORDER BY last_call_et DESC;"
# 3) DB Blocking
run_sql "Blocking Sessions Across Instances" "
SELECT inst_id, blocking_session, sid, serial#, wait_class, seconds_in_wait
FROM GV\$SESSION WHERE blocking_session IS NOT NULL;"
# 4) DB Locking
run_sql "Database Locks" "
SELECT inst_id, sid, type, id1, id2, lmode, request, block
FROM GV\$LOCK WHERE block != 0;"
# 5) DB Load Last 1 Hour
run_sql "Database Load Last 1 Hour" "
SELECT inst_id, TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS'), average_active_sessions
FROM GV\$SYSMETRIC_HISTORY
WHERE metric_name = 'Average Active Sessions' AND group_id = 2
ORDER BY begin_time DESC FETCH FIRST 12 ROWS ONLY;"
# 6) DB Load Last 5 Minutes
run_sql "Database Load Last 5 Minutes" "
SELECT inst_id, TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI:SS'), average_active_sessions
FROM GV\$SYSMETRIC_HISTORY
WHERE metric_name = 'Average Active Sessions' AND group_id = 2
ORDER BY begin_time DESC FETCH FIRST 1 ROW ONLY;"
# 7) DB Parallel Processing
run_sql "Parallel Execution Processes" "
SELECT inst_id, degree, req_degree, dop, requested_dop
FROM GV\$PX_PROCESS;"
# 8) Invalid Objects
run_sql "Invalid Objects Across All Nodes" "
SELECT inst_id, owner, object_name, object_type
FROM GV\$DBA_OBJECTS WHERE status <> 'VALID';"
# 9) Session Failures
run_sql "Failed User Sessions" "
SELECT inst_id, username, machine, terminal, logon_time
FROM GV\$AUDIT_SESSION WHERE returncode != 0;"
# 10) Active Session Count
run_sql "Active Sessions Count per Instance" "
SELECT inst_id, COUNT(*) AS active_sessions
FROM GV\$SESSION WHERE status = 'ACTIVE' GROUP BY inst_id;"
# 11) Database Service Info
run_sql "Database Services Across All Instances" "
SELECT inst_id, name, pdb FROM GV\$ACTIVE_SERVICES;"
# 12) ORA-ERRORs in Alert Logs (Last 24 Hours)
run_sql "Recent ORA-Errors in Alert Logs" "
SELECT inst_id, originating_timestamp, message_text
FROM GV\$DIAG_ALERT_EXT
WHERE message_text LIKE 'ORA-%' AND originating_timestamp > SYSDATE - 1
ORDER BY originating_timestamp DESC;"
# Close HTML Output
echo "" >> $HTML_REPORT
# Send Email with the HTML report
mailx -a "$HTML_REPORT" -s "$EMAIL_SUBJECT" "$EMAIL_TO" < $HTML_REPORT
echo "RAC Health Check Report Sent to $EMAIL_TO"
###
#!/bin/bash
set -eo pipefail
# Configuration
DB_USER="sys as sysdba"
DB_PASSWORD="your_password"
DB_HOST="rac-scan.example.com"
DB_PORT=1521
SERVICE_NAME="ORCLCDB"
EMAIL_TO="dba@example.com"
REPORT_FILE="/tmp/rac_health_report.html"
# Oracle Connection String
CONN_STR="${DB_USER}/${DB_PASSWORD}@//${DB_HOST}:${DB_PORT}/${SERVICE_NAME}"
# HTML Header
cat > $REPORT_FILE <
Oracle RAC Health Report
Oracle RAC Health & Performance Report
Generated: $(date "+%Y-%m-%d %H:%M:%S")
EOF
# Function to run SQL and format output
run_sql() {
local sql=$1
local title=$2
local critical=$3
echo "$title
" >> $REPORT_FILE
output=$(sqlplus -S -L "${CONN_STR}" <No issues found" >> $REPORT_FILE
else
echo "" >> $REPORT_FILE
while IFS= read -r line; do
echo "| $line |
" >> $REPORT_FILE
done <<< "$output"
echo "
" >> $REPORT_FILE
fi
}
# 1. Instance/DB Status
run_sql "
SELECT 'Instance ' || instance_name || ' - ' || status ||
' | Version: ' || version || ' | Uptime: ' ||
ROUND((SYSDATE - startup_time)*24) || ' hours'
FROM gv\$instance;" "Cluster Instance Status"
# 2. Long Running Sessions (>30 minutes)
run_sql "
SELECT 'SID: ' || sid || ', Inst: ' || inst_id ||
', User: ' || username || ', SQL_ID: ' || sql_id ||
', Elapsed: ' || ROUND((SYSDATE - logon_time)*1440) || ' mins'
FROM gv\$session
WHERE status = 'ACTIVE' AND (SYSDATE - logon_time)*1440 > 30;" "Long Running Sessions" "warning"
# 3. DB Blocking/Locking
run_sql "
SELECT 'Blocker: ' || b.inst_id || ':' || b.sid ||
' | Waiter: ' || w.inst_id || ':' || w.sid ||
' | Object: ' || o.object_name ||
' | Wait: ' || ROUND((SYSDATE - w.logon_time)*1440) || ' mins'
FROM gv\$lock b, gv\$lock w, dba_objects o
WHERE b.block = 1 AND w.request > 0
AND b.id1 = w.id1 AND b.id2 = w.id2
AND o.object_id = b.id1;" "Blocking Sessions" "critical"
# 4. DB Load Analysis
run_sql "
SELECT 'Last Hour: CPU ' || ROUND(AVG(cpu_percent)) || '%' ||
' | Active: ' || AVG(active_sessions) ||
' | Parallel: ' || AVG(parallel_servers) ||
' | Last 5m: ' || ROUND(5min_cpu) || '%'
FROM (
SELECT h.metric_value cpu_percent,
(SELECT COUNT(*) FROM gv\$session WHERE status = 'ACTIVE') active_sessions,
(SELECT SUM(servers_in_use) FROM gv\$px_process_sysstat) parallel_servers,
LEAD(h.metric_value, 12) OVER (ORDER BY h.begin_time) 5min_cpu
FROM gv\$sysmetric_history h
WHERE h.metric_name = 'CPU Usage Per Sec'
AND h.begin_time > SYSDATE - 1/24
);" "Load Analysis"
# 5. Parallel Processes
run_sql "
SELECT 'Inst ' || inst_id ||
' | Used: ' || servers_in_use ||
' | Available: ' || servers_available ||
' | Status: ' || status
FROM gv\$px_process_sysstat;" "Parallel Execution Status"
# 6. Invalid Objects
run_sql "
SELECT owner || '.' || object_name || ' (' || object_type || ')'
FROM dba_objects
WHERE status != 'VALID' AND owner NOT IN ('SYS','SYSTEM');" "Invalid Objects" "warning"
# 7. Session Failures
run_sql "
SELECT 'Inst ' || inst_id ||
' | User: ' || username ||
' | Count: ' || COUNT(*) ||
' | Last: ' || MAX(timestamp)
FROM gv\$session
WHERE status = 'FAILED'
GROUP BY inst_id, username;" "Session Failures" "critical"
# 8. Active Session Count
run_sql "
SELECT 'Inst ' || inst_id ||
' | Active: ' || COUNT(*) ||
' | CPU: ' || SUM(CASE WHEN state = 'ON CPU' THEN 1 ELSE 0 END)
FROM gv\$session
WHERE status = 'ACTIVE'
GROUP BY inst_id;" "Active Sessions"
# 9. Database Services
run_sql "
SELECT name || ' | ' || status ||
' | Node: ' || node ||
' | Created: ' || TO_CHAR(creation_date, 'YYYY-MM-DD HH24:MI')
FROM gv\$services;" "Database Services"
# 10. ORA-Errors
run_sql "
SELECT 'Inst ' || inst_id ||
' | ' || TO_CHAR(timestamp, 'YYYY-MM-DD HH24:MI') ||
' | ' || REGEXP_SUBSTR(message_text, 'ORA-[0-9]+:.*')
FROM gv\$diag_alert_ext
WHERE message_type = 'ERROR'
AND timestamp > SYSDATE - 1;" "Recent ORA Errors" "critical"
# Complete HTML
cat >> $REPORT_FILE <
EOF
# Send Email
(
echo "From: Oracle Monitor "
echo "To: $EMAIL_TO"
echo "Subject: Oracle RAC Health Report"
echo "MIME-Version: 1.0"
echo "Content-Type: text/html"
echo
cat $REPORT_FILE
) | sendmail -t
# Cleanup
rm -f $REPORT_FILE
