Data Guard Health Report
Primary Database: $primary_db
Generated: $(date '+%Y-%m-%d %H:%M:%S')
EOF
# Configuration Status
echo "
"
generate_html_footer
} > "$report_file"
echo "✓ Data Guard health report generated: $report_file"
log_message "SUCCESS" "Data Guard health report created: $report_file"
}
################################################################################
# Function: check_dg_sync_status
# Description: Check if standby is in sync with primary
################################################################################
check_dg_sync_status() {
local primary_db=$1
local standby_db=$2
echo ""
echo "Checking synchronization between $primary_db and $standby_db"
echo "============================================================="
# Check current SCN on primary
echo ""
echo "Primary Database Current SCN:"
sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SELECT 'Current SCN: ' || CURRENT_SCN FROM v\$database;
EXIT;
EOF
# Check apply progress on standby (would need to connect to standby)
echo ""
echo "For standby database $standby_db:"
echo "Please verify the following on the standby:"
echo " 1. MRP (Managed Recovery Process) is running"
echo " 2. Apply lag is within acceptable limits"
echo " 3. No apply errors in alert log"
echo ""
}
################################################################################
# Function: validate_dg_configuration
# Description: Validate Data Guard configuration
################################################################################
validate_dg_configuration() {
local primary_db=$1
log_message "INFO" "Validating Data Guard configuration for $primary_db"
echo ""
echo "=========================================="
echo "Data Guard Configuration Validation"
echo "=========================================="
echo ""
# Validate configuration
echo "Running configuration validation..."
dgmgrl / << EOF
VALIDATE DATABASE VERBOSE $primary_db;
EXIT;
EOF
echo ""
# Check for configuration warnings
echo "Checking for configuration warnings..."
dgmgrl / << EOF
SHOW CONFIGURATION LAG;
EXIT;
EOF
echo ""
log_message "INFO" "Data Guard configuration validation completed"
}
################################################################################
# Function: show_dg_lag_summary
# Description: Display lag summary for all standby databases
################################################################################
show_dg_lag_summary() {
local primary_db=$1
echo ""
echo "Data Guard Lag Summary"
echo "======================"
echo ""
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
COLUMN name FORMAT A20
COLUMN value FORMAT A20
COLUMN unit FORMAT A10
COLUMN time_computed FORMAT A20
SELECT
name,
value,
unit,
TO_CHAR(time_computed, 'YYYY-MM-DD HH24:MI:SS') as time_computed
FROM v\$dataguard_stats
WHERE name IN ('apply lag', 'transport lag')
ORDER BY name;
EXIT;
EOF
echo ""
}
################################################################################
# Function: check_mrp_status
# Description: Check Managed Recovery Process status on standby
################################################################################
check_mrp_status() {
echo ""
echo "Managed Recovery Process (MRP) Status:"
echo "======================================"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
SELECT
process,
status,
thread#,
sequence#,
block#,
blocks
FROM v\$managed_standby
WHERE process LIKE 'MRP%'
OR process LIKE 'RFS%'
ORDER BY process;
EXIT;
EOF
echo ""
}
################################################################################
# Function: check_dg_gaps
# Description: Check for archive log gaps
################################################################################
check_dg_gaps() {
echo ""
echo "Archive Log Gap Detection:"
echo "=========================="
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
SELECT
thread#,
low_sequence#,
high_sequence#,
TO_CHAR(gap_time, 'YYYY-MM-DD HH24:MI:SS') as gap_time
FROM v\$archive_gap;
EXIT;
EOF
local gap_count=$(sqlplus -s / as sysdba << EOF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT COUNT(*) FROM v\$archive_gap;
EXIT;
EOF
)
gap_count=$(echo "$gap_count" | xargs)
if [[ $gap_count -gt 0 ]]; then
echo ""
echo "⚠ WARNING: $gap_count archive log gap(s) detected!"
log_message "WARN" "Archive log gaps detected: $gap_count"
else
echo ""
echo "✓ No archive log gaps detected"
log_message "INFO" "No archive log gaps"
fi
echo ""
}
################################################################################
# Function: show_dg_statistics
# Description: Show detailed Data Guard statistics
################################################################################
show_dg_statistics() {
local primary_db=$1
echo ""
echo "Data Guard Statistics for $primary_db"
echo "======================================"
echo ""
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
COLUMN name FORMAT A30
COLUMN value FORMAT A20
COLUMN unit FORMAT A15
COLUMN time_computed FORMAT A20
SELECT
name,
value,
unit,
TO_CHAR(time_computed, 'YYYY-MM-DD HH24:MI:SS') as time_computed,
datum_time
FROM v\$dataguard_stats
ORDER BY name;
EXIT;
EOF
echo ""
}
################################################################################
# Function: analyze_dg_performance
# Description: Analyze Data Guard performance metrics
################################################################################
analyze_dg_performance() {
local primary_db=$1
log_message "INFO" "Analyzing Data Guard performance for $primary_db"
echo ""
echo "Data Guard Performance Analysis"
echo "==============================="
echo ""
# Redo generation rate
echo "Redo Generation Rate (Last Hour):"
echo "---------------------------------"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
SELECT
TO_CHAR(first_time, 'YYYY-MM-DD HH24') as hour,
COUNT(*) as log_switches,
ROUND(SUM(blocks * block_size)/1024/1024, 2) as redo_mb
FROM v\$archived_log
WHERE first_time > SYSDATE - 1/24
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour DESC;
EXIT;
EOF
echo ""
# Network throughput
echo "Network Throughput to Standby:"
echo "------------------------------"
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 100
SELECT
dest_name,
ROUND(SUM(bytes)/1024/1024, 2) as total_mb_sent,
COUNT(*) as archives_sent
FROM v\$archived_log
WHERE dest_id > 1
AND first_time > SYSDATE - 1
GROUP BY dest_name
ORDER BY dest_name;
EXIT;
EOF
echo ""
log_message "INFO" "Data Guard performance analysis completed"
}
################################################################################
# Function: quick_dg_check
# Description: Quick Data Guard status check
################################################################################
quick_dg_check() {
local primary_db=$1
echo ""
echo "Quick Data Guard Status Check"
echo "=============================="
echo ""
# Configuration summary
dgmgrl / << EOF
SHOW CONFIGURATION;
EXIT;
EOF
echo ""
# Key metrics
sqlplus -s / as sysdba << EOF
SET LINESIZE 200
SET PAGESIZE 50
PROMPT Database Role and Status:
PROMPT ========================
SELECT
name,
database_role,
open_mode,
protection_mode,
switchover_status
FROM v\$database;
PROMPT
PROMPT Current Lag Status:
PROMPT ==================
SELECT
name,
value,
unit,
TO_CHAR(time_computed, 'HH24:MI:SS') as time
FROM v\$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');
PROMPT
PROMPT Archive Gaps (if any):
PROMPT =====================
SELECT
thread#,
low_sequence#,
high_sequence#
FROM v\$archive_gap;
EXIT;
EOF
echo ""
}
################################################################################
# Export functions
################################################################################
export -f show_dg_status
export -f check_standby_databases
export -f check_apply_lag
export -f check_transport_lag
export -f check_dg_sync_status
export -f validate_dg_configuration
export -f check_mrp_status
export -f check_dg_gaps
export -f quick_dg_check
log_message "INFO" "Data Guard health functions loaded"
Configuration Status
" echo ""
dgmgrl / << DGEOF
SHOW CONFIGURATION;
EXIT;
DGEOF
echo ""
# Database Details
echo "