#!/bin/bash
################################################################################
# Oracle 19c Database Health Check Functions
# Description: Functions for performing database health checks
# Version: 2.0 (Integrated + Enhanced)
# Created: 2025-11-02
# Updated: 2025-11-14 - Merged with enhanced version and database loading fix
################################################################################
# NOTE: This file assumes functions_common.sh has already been sourced
# by the calling script (oracle_rac_admin.sh)
################################################################################
# Function: perform_db_health_check
# Description: Performs health check on a single database
# Parameters: $1 - Database name
# $2 - SCAN address
# $3 - Service name
################################################################################
perform_db_health_check() {
local db_name=$1
local scan=$2
local service=$3
log_message "INFO" "Starting health check for database: ${db_name}"
# Validate database connection first
if ! test_db_connection "${scan}" "${service}"; then
log_message "ERROR" "Cannot connect to database ${db_name}"
echo "ERROR: Cannot connect to database ${db_name}"
return 1
fi
# Generate report filename
local timestamp=$(date '+%Y%m%d_%H%M%S')
local report_file="${REPORT_BASE_DIR}/health_check_${db_name}_${timestamp}.html"
# Create HTML report
{
generate_health_report_header "$db_name"
echo "
"
echo "
Health Check Summary
"
echo "
Database: $db_name
"
echo "
SCAN Host: $scan
"
echo "
Service: $service
"
echo "
Check Time: $(date)
"
# Run health check sections
check_database_status "$db_name"
check_instance_status "$db_name"
check_tablespace_usage "$db_name"
check_invalid_objects "$db_name"
check_asm_diskgroups "$db_name"
check_database_size "$db_name"
check_alert_log_errors "$db_name"
check_backup_status "$db_name"
check_archive_log_status "$db_name"
echo "
"
generate_html_footer
} > "$report_file"
log_message "SUCCESS" "Health check report generated: $report_file"
echo ""
echo "✓ Health check completed successfully"
echo " Report: $report_file"
echo ""
# Display summary on console
display_health_summary "$db_name"
return 0
}
################################################################################
# Function: generate_health_report_header
# Description: Generate HTML header for health check report
################################################################################
generate_health_report_header() {
local db_name=$1
cat << EOF
Health Check Report - $db_name
EOF
}
################################################################################
# Function: check_database_status
# Description: Check database status and role
################################################################################
check_database_status() {
local db_name=$1
echo "
Database Status
"
local status=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT name||'|'||db_unique_name||'|'||database_role||'|'||open_mode||'|'||log_mode||'|'||flashback_on||'|'||force_logging
FROM v\$database;
EXIT;
EOF
)
if [[ -n "$status" ]]; then
IFS='|' read -r name unique_name role open_mode log_mode flashback force_log <<< "$status"
echo "
"
echo "| Metric | Value | Status |
|---|
"
echo "| Database Name | $name | OK |
"
echo "| Unique Name | $unique_name | OK |
"
echo "| Database Role | $role | OK |
"
echo "| Open Mode | $open_mode | OK |
"
echo "| Archive Log Mode | $log_mode | OK |
"
echo "| Flashback Database | $flashback | OK |
"
echo "| Force Logging | $force_log | OK |
"
echo "
"
log_message "INFO" "Database status check completed for $db_name"
else
echo "
ERROR: Unable to retrieve database status
"
log_message "ERROR" "Failed to retrieve database status for $db_name"
fi
}
################################################################################
# Function: check_instance_status
# Description: Check RAC instance status
################################################################################
check_instance_status() {
local db_name=$1
echo "
Instance Status
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
instance_name,
host_name,
version,
status,
database_status,
instance_role,
TO_CHAR(startup_time, 'YYYY-MM-DD HH24:MI:SS') as startup_time,
ROUND((SYSDATE - startup_time)*24, 2) as uptime_hours
FROM gv\$instance
ORDER BY instance_name;
EXIT;
EOF
log_message "INFO" "Instance status check completed for $db_name"
}
################################################################################
# Function: check_tablespace_usage
# Description: Check tablespace usage and alert on high usage
################################################################################
check_tablespace_usage() {
local db_name=$1
echo "
Tablespace Usage
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
WITH ts_usage AS (
SELECT
tablespace_name,
ROUND(used_space * 8192 / 1024 / 1024 / 1024, 2) AS used_gb,
ROUND(tablespace_size * 8192 / 1024 / 1024 / 1024, 2) AS total_gb,
ROUND(used_percent, 2) AS used_percent,
CASE
WHEN used_percent >= 90 THEN 'CRITICAL'
WHEN used_percent >= 80 THEN 'WARNING'
ELSE 'OK'
END AS status
FROM dba_tablespace_usage_metrics
)
SELECT * FROM ts_usage
ORDER BY used_percent DESC;
EXIT;
EOF
log_message "INFO" "Tablespace usage check completed for $db_name"
}
################################################################################
# Function: check_invalid_objects
# Description: Check for invalid database objects
################################################################################
check_invalid_objects() {
local db_name=$1
echo "
Invalid Objects
"
local invalid_count=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';
EXIT;
EOF
)
invalid_count=$(echo "$invalid_count" | xargs)
if [[ $invalid_count -gt 0 ]]; then
echo "
Found $invalid_count invalid objects
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
owner,
object_type,
COUNT(*) as invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner, object_type
ORDER BY invalid_count DESC;
EXIT;
EOF
log_message "WARN" "Found $invalid_count invalid objects in $db_name"
else
echo "
✓ No invalid objects found
"
log_message "INFO" "No invalid objects found in $db_name"
fi
}
################################################################################
# Function: check_asm_diskgroups
# Description: Check ASM diskgroup status and usage
################################################################################
check_asm_diskgroups() {
local db_name=$1
echo "
ASM Diskgroup Status
"
# Check if ASM is being used
local asm_check=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM v\$asm_diskgroup;
EXIT;
EOF
)
asm_check=$(echo "$asm_check" | xargs)
if [[ $asm_check -gt 0 ]]; then
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
name,
state,
type,
total_mb,
free_mb,
ROUND((total_mb - free_mb) / total_mb * 100, 2) as used_percent,
offline_disks
FROM v\$asm_diskgroup
ORDER BY name;
EXIT;
EOF
log_message "INFO" "ASM diskgroup check completed for $db_name"
else
echo "
ASM not configured or not accessible
"
log_message "INFO" "ASM not configured for $db_name"
fi
}
################################################################################
# Function: check_database_size
# Description: Check database size and growth
################################################################################
check_database_size() {
local db_name=$1
echo "
Database Size
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_size_gb,
ROUND(SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)/1024/1024/1024, 2) AS max_size_gb
FROM dba_data_files
UNION ALL
SELECT
ROUND(SUM(bytes)/1024/1024/1024, 2) AS total_size_gb,
ROUND(SUM(CASE WHEN autoextensible = 'YES' THEN maxbytes ELSE bytes END)/1024/1024/1024, 2) AS max_size_gb
FROM dba_temp_files;
EXIT;
EOF
log_message "INFO" "Database size check completed for $db_name"
}
################################################################################
# Function: check_alert_log_errors
# Description: Check for recent errors in alert log
################################################################################
check_alert_log_errors() {
local db_name=$1
echo "
Recent Alert Log Errors (Last 24 Hours)
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') as error_time,
message_text
FROM v\$diag_alert_ext
WHERE message_text LIKE '%ORA-%'
AND originating_timestamp > SYSDATE - 1
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;
EXIT;
EOF
log_message "INFO" "Alert log error check completed for $db_name"
}
################################################################################
# Function: check_backup_status
# Description: Check RMAN backup status
################################################################################
check_backup_status() {
local db_name=$1
echo "
RMAN Backup Status
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
TO_CHAR(start_time, 'YYYY-MM-DD HH24:MI:SS') as backup_time,
input_type,
status,
ROUND(elapsed_seconds/3600, 2) as hours,
ROUND(input_bytes/1024/1024/1024, 2) as input_gb,
ROUND(output_bytes/1024/1024/1024, 2) as output_gb
FROM v\$rman_backup_job_details
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;
EXIT;
EOF
log_message "INFO" "Backup status check completed for $db_name"
}
################################################################################
# Function: check_archive_log_status
# Description: Check archive log generation and disk usage
################################################################################
check_archive_log_status() {
local db_name=$1
echo "
Archive Log Status (Last 7 Days)
"
sqlplus -s / as sysdba << EOF
SET MARKUP HTML ON
SET HEADING ON
SET FEEDBACK OFF
SELECT
TO_CHAR(first_time, 'YYYY-MM-DD') as log_date,
COUNT(*) as log_count,
ROUND(SUM(blocks * block_size)/1024/1024/1024, 2) as total_gb
FROM v\$archived_log
WHERE first_time > SYSDATE - 7
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')
ORDER BY log_date DESC;
EXIT;
EOF
log_message "INFO" "Archive log status check completed for $db_name"
}
################################################################################
# Function: display_health_summary
# Description: Display health check summary on console
################################################################################
display_health_summary() {
local db_name=$1
echo "=========================================="
echo "Health Check Summary for $db_name"
echo "=========================================="
# Get key metrics
local db_status=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT database_role||'|'||open_mode FROM v\$database;
EXIT;
EOF
)
IFS='|' read -r role open_mode <<< "$db_status"
echo "Database Role: $role"
echo "Open Mode: $open_mode"
# Check tablespace usage
local critical_ts=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM dba_tablespace_usage_metrics WHERE used_percent >= 90;
EXIT;
EOF
)
critical_ts=$(echo "$critical_ts" | xargs)
if [[ $critical_ts -gt 0 ]]; then
echo "Tablespaces: ⚠ WARNING - $critical_ts tablespace(s) above 90%"
else
echo "Tablespaces: ✓ OK"
fi
# Check invalid objects
local invalid=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM dba_objects WHERE status = 'INVALID';
EXIT;
EOF
)
invalid=$(echo "$invalid" | xargs)
if [[ $invalid -gt 0 ]]; then
echo "Invalid Objects: ⚠ WARNING - $invalid invalid objects"
else
echo "Invalid Objects: ✓ OK"
fi
echo "=========================================="
}
################################################################################
# Function: quick_health_check
# Description: Quick health check for console display only
################################################################################
quick_health_check() {
local db_name=$1
echo ""
echo "Quick Health Check for $db_name"
echo "========================================"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
PROMPT
PROMPT Database Status:
PROMPT ================
SELECT name, database_role, open_mode, log_mode
FROM v\$database;
PROMPT
PROMPT Instance Status:
PROMPT ================
SELECT instance_name, host_name, status, database_status
FROM gv\$instance
ORDER BY instance_name;
PROMPT
PROMPT Tablespace Usage (>80%):
PROMPT ========================
SELECT
tablespace_name,
ROUND(used_percent, 2) as used_percent
FROM dba_tablespace_usage_metrics
WHERE used_percent > 80
ORDER BY used_percent DESC;
PROMPT
PROMPT Invalid Objects by Owner:
PROMPT ========================
SELECT owner, COUNT(*) as invalid_count
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY owner
ORDER BY invalid_count DESC;
EXIT;
EOF
echo ""
echo "========================================"
}
################################################################################
# Export functions
################################################################################
export -f run_health_check
export -f quick_health_check
export -f display_health_summary
log_message "INFO" "Database health check functions loaded"
