Health Check Report for EBS and Database

Keeping an Oracle Database and Oracle E-Business Suite (EBS) environment healthy requires continuous monitoring across multiple layers—database, RAC, application services, and infrastructure. Manual checks are time-consuming and error-prone, especially in large enterprise systems.

This article explains a lightweight yet powerful automated health check framework built using Shell scripting, SQL*Plus, and HTML reporting, suitable for daily operational monitoring by DBAs and Apps DBAs.

High-Level Architecture

The health check script performs the following:

Connects to the Oracle Database (CDB / RAC aware)
Collects database and EBS metrics
Evaluates thresholds (OK / WARNING / CRITICAL)
Generates an HTML report
Sends the report via email

This makes it ideal for daily scheduled execution via cron.

# Run at 8:00 AM IST (2:30 AM UTC)

30 2 * * * /home/oracle/scripts/ebs_system_health_check.sh > /home/oracle/logs/ebs_health_ist.log 2>&1

Instead of plain text logs, the script generates a color-coded HTML report, making it easy for support teams and managers to quickly understand system health.

Status indicators:

Green (OK) – No action required
Orange (WARN) – Monitor / plan corrective action
Red (CRIT) – Immediate attention required

Script:

#!/bin/bash
. ~/EBSCDB_db.env
INST=$1
TNS_ALIAS=$INST # Use INST name as TNS alias
APPS_PWD="apps" # Set apps password here
EBS_URL="http://erpfoalabs.training:8000/OA_HTML/AppsLogin"
LOG_FILE="/tmp/ebs_health_check_${INST}.html"
DATE=$(date '+%Y-%m-%d %H:%M:%S')
MAIL_TO="support@funoracleapps.com"
MAIL_SUBJECT="EBS Health Check Report $INST - $DATE"

OK_COUNT=0
WARN_COUNT=0
CRIT_COUNT=0

# Fetch DB Name
DB_NAME=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT name FROM v\$database;
EXIT;
EOF
)

# HTML Header
echo "<html><head><style>
body { font-family: Arial; }
table { border-collapse: collapse; width: 100%; }
th, td { border: 1px solid #ddd; padding: 8px; }
th { background-color: #f2f2f2; }
.ok { color: green; font-weight: bold; }
.warn { color: orange; font-weight: bold; }
.crit { color: red; font-weight: bold; }
.summary { font-size: 16px; margin-bottom: 10px; }
</style></head><body>" > $LOG_FILE
echo "<h2>EBS Health Check Report</h2>" >> $LOG_FILE
echo "<p><b>Instance:</b> $INST | <b>DB:</b> $DB_NAME | <b>Timestamp:</b> $DATE</p>" >> $LOG_FILE
echo "<div class='summary'><b>Summary:</b> OK: <span class='ok'>0</span> | WARN: <span class='warn'>0</span> | CRIT: <span class='crit'>0</span></div>" >> $LOG_FILE

# -------------------------------
# 1. EBS Login Page
# -------------------------------
echo "<h3>EBS Login Page</h3>" >> $LOG_FILE
#echo "<p>URL: $EBS_URL$EBS_URL</a></p>" >> $LOG_FILE

status=$(curl -s -o /dev/null -w "%{http_code}" $EBS_URL)

if [ "$status" -eq 200 ]; then
echo "<p class='ok'>Login Page Accessible (URL: $EBS_URL)</p>" >> $LOG_FILE
((OK_COUNT++))
else
echo "<p class='crit'>Login Page DOWN (URL: $EBS_URL, HTTP Status: $status)</p>" >> $LOG_FILE
((CRIT_COUNT++))
fi

# -------------------------------
# 2. Concurrent Managers + Pending Requests
# -------------------------------
echo "<h3>Concurrent Managers (Actual vs Target Processes)</h3><table><tr><th>Manager</th><th>Node</th><th>Actual</th><th>Target</th><th>Status</th></tr>" >> $LOG_FILE

output=$(sqlplus -s apps/$APPS_PWD@$TNS_ALIAS <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT b.user_concurrent_queue_name || '|' || a.target_node || '|' || a.running_processes || '|' || a.max_processes || '|' ||
DECODE(b.control_code,
'D', 'Deactivating',
'E', 'Deactivated',
'N', 'Node unavailable',
'A', 'Activating',
'X', 'Terminated',
'T', 'Terminating',
'V', 'Verifying',
'O', 'Suspending',
'P', 'Suspended',
'Q', 'Resuming',
'R', 'Restarting', 'Running')
FROM apps.fnd_concurrent_queues a, apps.fnd_concurrent_queues_vl b
WHERE a.concurrent_queue_id = b.concurrent_queue_id
AND a.max_processes != 0
ORDER BY a.max_processes DESC;
EXIT;
EOF
)

IFS=$'\n'
for line in $output; do
mgr=$(echo "$line" | cut -d'|' -f1)
node=$(echo "$line" | cut -d'|' -f2)
actual=$(echo "$line" | cut -d'|' -f3)
target=$(echo "$line" | cut -d'|' -f4)
status=$(echo "$line" | cut -d'|' -f5)

# Highlight red if actual != target
if [[ "$actual" =~ ^[0-9]+$ ]] && [[ "$target" =~ ^[0-9]+$ ]] && [ "$actual" -ne "$target" ]; then
color="crit"; ((CRIT_COUNT++))
else
color="ok"; ((OK_COUNT++))
fi

echo "<tr><td>$mgr</td><td>$node</td><td class='$color'>$actual</td><td>$target</td><td>$status</td></tr>" >> $LOG_FILE
done

echo "</table>" >> $LOG_FILE

# -------------------------------
# 3. Workflow Mailer & Service Components
# -------------------------------
echo "<h3>Workflow Mailer & Service Components</h3><table><tr><th>Component</th><th>Status</th></tr>" >> $LOG_FILE
output=$(sqlplus -s apps/$APPS_PWD@$TNS_ALIAS <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT component_name||'|'||component_status
FROM fnd_svc_components
WHERE component_name IN ('Workflow Notification Mailer','Workflow Agent Listener','Workflow Deferred Agent Listener');
EXIT;
EOF
)
for line in $output; do
comp=$(echo "$line" | cut -d'|' -f1)
status=$(echo "$line" | cut -d'|' -f2)
color="ok"
if [ "$status" != "RUNNING" ]; then color="crit"; ((CRIT_COUNT++)); else ((OK_COUNT++)); fi
echo "<tr><td>$comp</td><td class='$color'>$status</td></tr>" >> $LOG_FILE
done
echo "</table>" >> $LOG_FILE

# -------------------------------
# 4. RAC Node Status
# -------------------------------
echo "<h3>RAC Node Status</h3><table><tr><th>Instance</th><th>Status</th></tr>" >> $LOG_FILE
output=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT instance_name||'|'||status FROM gv\$instance;
EXIT;
EOF
)
for line in $output; do
inst=$(echo "$line" | cut -d'|' -f1)
status=$(echo "$line" | cut -d'|' -f2)
color="ok"
if [ "$status" != "OPEN" ]; then color="crit"; ((CRIT_COUNT++)); else ((OK_COUNT++)); fi
echo "<tr><td>$inst</td><td class='$color'>$status</td></tr>" >> $LOG_FILE
done
echo "</table>" >> $LOG_FILE

# -------------------------------
# 5. Tablespace Usage (MAX USED PERCENTAGE >= 70)
# -------------------------------
echo "<h3>Tablespace Usage (MAX USED PERCENTAGE >= 70)</h3><table border='1'>
<tr>
<th>Tablespace</th>
<th>Auto Ext</th>
<th>Max Size (MB)</th>
<th>Free Size (MB)</th>
</tr>" >> $LOG_FILE

output=$(sqlplus -s apps/$APPS_PWD@$TNS_ALIAS <<EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 999 LINESIZE 400
SELECT tablespace_name||'|'||auto_ext||'|'||max_ts_size||'|'||free_ts_size
FROM (
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used
FROM dba_free_space fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_data_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
max(df.autoextensible) auto_ext,
round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
round((sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used
FROM (select tablespace_name, bytes_used bytes
from V\$temp_space_header
group by tablespace_name, bytes_free, bytes_used) fs,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
max(autoextensible) autoextensible
from dba_temp_files
group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
) WHERE max_ts_pct_used >= 70
ORDER BY max_ts_pct_used DESC;
EXIT;
EOF
)

if [ -z "$output" ]; then
echo "<tr><td colspan='4' class='ok'>No Data</td></tr>" >> $LOG_FILE
else
for line in $output; do
ts=$(echo "$line" | cut -d'|' -f1)
auto_ext=$(echo "$line" | cut -d'|' -f2)
max_size=$(echo "$line" | cut -d'|' -f3)
free_size=$(echo "$line" | cut -d'|' -f4)

# Sanitize and check free size
free_clean=$(echo "$free_size" | sed 's/[^0-9.]//g')
if [ -z "$free_clean" ]; then free_clean=0; fi

if (( $(echo "$free_clean < 500" | bc -l) )); then
color="crit"; ((CRIT_COUNT++))
elif (( $(echo "$free_clean < 1024" | bc -l) )); then
color="warn"; ((WARN_COUNT++))
else
color="ok"; ((OK_COUNT++))
fi

echo "<tr>
<td>$ts</td>
<td>$auto_ext</td>
<td>$max_size</td>
<td class='$color'>$free_size</td>
</tr>" >> $LOG_FILE
done
fi

echo "</table>" >> $LOG_FILE

# -------------------------------
# 6 Blocking Sessions (Apps User, PDB Level)
# -------------------------------
echo "<h3>Blocking Sessions (Including RAC & PDB)</h3><table border='1'>
<tr><th>Blocking Status</th></tr>" >> $LOG_FILE

output=$(sqlplus -s apps/$APPS_PWD@$TNS_ALIAS <<EOF
SET HEADING OFF FEEDBACK OFF LINESIZE 200
COL blocking_status FOR A120
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine
|| ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM gv\$lock l1, gv\$session s1, gv\$lock l2, gv\$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.block=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
EXIT;
EOF
)

if [ -z "$output" ]; then
echo "<tr><td class='ok'>No Blocking Sessions</td></tr>" >> $LOG_FILE
((OK_COUNT++))
else
for line in $output; do
echo "<tr><td class='crit'>$line</td></tr>" >> $LOG_FILE
((CRIT_COUNT++))
done
fi

echo "</table>" >> $LOG_FILE

# -------------------------------
# 7. Expired Users
# -------------------------------
echo "<h3>Expired Users</h3><table><tr><th>User</th><th>Status</th></tr>" >> $LOG_FILE
output=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT username||'|'||account_status FROM dba_users WHERE account_status LIKE '%EXPIRED%';
EXIT;
EOF
)
for line in $output; do
user=$(echo "$line" | cut -d'|' -f1)
status=$(echo "$line" | cut -d'|' -f2)
echo "<tr><td>$user</td><td class='warn'>$status</td></tr>" >> $LOG_FILE
((WARN_COUNT++))
done
echo "</table>" >> $LOG_FILE

# -------------------------------
# 8. Alert Log Errors (Last 24 hrs)
# -------------------------------
echo "<h3>Alert Log Errors (Last 24 hrs)</h3><table border='1'><tr><th>Error</th></tr>" >> $LOG_FILE

output=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT message_text FROM X\$DBGALERTEXT WHERE originating_timestamp > SYSDATE-1 AND message_text LIKE 'ORA-%';
EXIT;
EOF
)

if [ -z "$output" ]; then
echo "<tr><td class='ok'>No Data</td></tr>" >> $LOG_FILE
else
for line in $output; do
if echo "$line" | grep -q "ORA-00600\|ORA-07445"; then
color="crit"; ((CRIT_COUNT++))
else
color="warn"; ((WARN_COUNT++))
fi
echo "<tr><td class='$color'>$line</td></tr>" >> $LOG_FILE
done
fi

echo "</table>" >> $LOG_FILE

# -------------------------------
# 9. Database Backup Status (Last 1 day)
# -------------------------------
echo "<h3>Database Backup Status</h3>" >> $LOG_FILE

backup_status=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF
SELECT COUNT(*) FROM v\$rman_backup_job_details WHERE start_time > SYSDATE-1 AND status='COMPLETED';
EXIT;
EOF
)

# Trim spaces
backup_status=$(echo "$backup_status" | xargs)

if [ "$backup_status" -gt 0 ]; then
echo "<p class='ok'>Backup Completed in Last 24 hrs</p>" >> $LOG_FILE
((OK_COUNT++))
else
echo "<p class='crit'>No Backup Completed in Last 24 hrs</p>" >> $LOG_FILE
((CRIT_COUNT++))
fi

echo "</table>" >> $LOG_FILE

# -------------------------------
# Overall Health Indicator
# -------------------------------
if [ "$CRIT_COUNT" -gt 5 ]; then
health="<span class='crit'>CRITICAL</span>"
elif [ "$CRIT_COUNT" -ge 1 ] && [ "$CRIT_COUNT" -le 5 ]; then
health="<span class='warn'>WARNING</span>"
else
health="<span class='ok'>HEALTHY</span>"
fi
sed -i "1i <h3>Overall Health: $health</h3>" $LOG_FILE

# Update Summary
sed -i "s/OK: <span class='ok'>0<\/span>/OK: <span class='ok'>$OK_COUNT<\/span>/; s/WARN: <span class='warn'>0<\/span>/WARN: <span class='warn'>$WARN_COUNT<\/span>/; s/CRIT: <span class='crit'>0<\/span>/CRIT: <span class='crit'>$CRIT_COUNT<\/span>/" $LOG_FILE

# HTML Footer
echo "</body></html>" >> $LOG_FILE

# -------------------------------
# Send Email using sendmail
# -------------------------------
{
echo "Subject: $MAIL_SUBJECT"
echo "To: $MAIL_TO"
echo "Content-Type: text/html"
echo ""
cat "$LOG_FILE"
} | /usr/sbin/sendmail -t