#!/bin/bash
set -euo pipefail
# Load all functions
source ./functions.sh
# Example Variables
REPORT_DIR="./reports"
LOG_DIR="./logs"
mkdir -p "$REPORT_DIR" "$LOG_DIR"
REPORT_NAME="db_healthcheck_$(date +%Y%m%d%H%M%S)"
TEXT_REPORT="$REPORT_DIR/${REPORT_NAME}.txt"
HTML_REPORT="$REPORT_DIR/${REPORT_NAME}.html"
EMAIL_TO="dba_team@example.com"
EMAIL_SUBJECT="Database Health Check Report - $REPORT_NAME"
# Step 1: Simulate generating a text report (you would run your SQLPlus spool here)
log "Generating sample text report."
echo "Database Health Check - Sample Report" > "$TEXT_REPORT"
echo "------------------------------------" >> "$TEXT_REPORT"
echo "Tablespace Usage: OK" >> "$TEXT_REPORT"
echo "Invalid Objects: 0" >> "$TEXT_REPORT"
echo "Archive Usage: 75%" >> "$TEXT_REPORT"
echo "No Locks Detected." >> "$TEXT_REPORT"
# Step 2: Convert text report to HTML
log "Converting text report to HTML."
text_to_html "$TEXT_REPORT" "$HTML_REPORT"
# Step 3: Send email with the HTML report
log "Sending email with report."
send_email "$EMAIL_TO" "$EMAIL_SUBJECT" "$HTML_REPORT"
# Step 4: Done
log "Health check report generated and emailed successfully."
text_to_html() {
local input_file=$1
local output_file=$2
{
echo "
Report"
cat "$input_file"
echo ""
} > "$output_file"
log "Converted $input_file into HTML format as $output_file."
}
send_email() {
local to_address=$1
local subject=$2
local file_to_send=$3
local file_size_kb
file_size_kb=$(du -k "$file_to_send" | cut -f1)
if [ "$file_size_kb" -lt 1024 ]; then
# Embed HTML content inside email
mailx -a "Content-type: text/html" -s "$subject" "$to_address" < "$file_to_send"
log "Sent email to $to_address with embedded HTML content."
else
# Attach the file
echo "Please see attached report." | mailx -s "$subject" -a "$file_to_send" "$to_address"
log "Sent email to $to_address with $file_to_send as attachment (file size > 1MB)."
fi
}
#!/bin/bash
# Common functions for redo and standby redo log management
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
error_exit() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] ERROR: $1" | tee -a "$LOG_FILE" >&2
exit 1
}
exec_sqlplus() {
sqlplus -s /nolog <
"$FILE"
}
capture_existing_groups() {
local CONN=$1
local VAR_REDO=$2
local VAR_STANDBY=$3
eval "$VAR_REDO=\"$(exec_sqlplus \"$CONN\" \"SELECT group# FROM v\\$log ORDER BY group#;\")\""
eval "$VAR_STANDBY=\"$(exec_sqlplus \"$CONN\" \"SELECT group# FROM v\\$standby_log ORDER BY group#;\")\""
}
add_redo_log_groups() {
local CONN=$1
local REDO_SIZE_MB=$2
local DB_ROLE
DB_ROLE=$(exec_sqlplus "$CONN" "SELECT database_role FROM v$database;")
for thr in $(exec_sqlplus "$CONN" "SELECT DISTINCT thread# FROM v$log;"); do
exec_sqlplus "$CONN" "ALTER DATABASE ADD LOGFILE THREAD $thr GROUP $NEW_GROUP_ID SIZE ${REDO_SIZE_MB}M;"
log "[$DB_ROLE] Added redo log group $NEW_GROUP_ID for thread $thr on $CONN."
NEW_GROUP_ID=$((NEW_GROUP_ID + 1))
NEW_REDO_CREATED=$((NEW_REDO_CREATED + 1))
done
}
add_standby_redo_log_groups() {
local CONN=$1
local STANDBY_REDO_SIZE_MB=$2
local DB_ROLE
DB_ROLE=$(exec_sqlplus "$CONN" "SELECT database_role FROM v$database;")
for ((thr=1; thr<=MAX_THREAD; thr++)); do
for ((i=1; i<=STANDBY_LOGS_PER_THREAD; i++)); do
exec_sqlplus "$CONN" "ALTER DATABASE ADD STANDBY LOGFILE THREAD $thr GROUP $NEW_GROUP_ID SIZE ${STANDBY_REDO_SIZE_MB}M;"
log "[$DB_ROLE] Added standby redo log group $NEW_GROUP_ID for thread $thr on $CONN."
NEW_GROUP_ID=$((NEW_GROUP_ID + 1))
NEW_STANDBY_CREATED=$((NEW_STANDBY_CREATED + 1))
done
sleep 2
done
}
drop_redo_groups_safely() {
local CONN=$1
local GROUPS=$2
for grp in $GROUPS; do
while true; do
exec_sqlplus "$CONN" "ALTER SYSTEM SWITCH LOGFILE;"
sleep 5
exec_sqlplus "$CONN" "ALTER DATABASE DROP LOGFILE GROUP $grp;"
if [ $? -eq 0 ]; then
log "Dropped redo log group $grp."
break
else
log "Retrying to drop redo log group $grp after logfile switch."
sleep 5
fi
done
done
}
drop_standby_groups_safely() {
local CONN=$1
local GROUPS=$2
for grp in $GROUPS; do
while true; do
exec_sqlplus "$CONN" "ALTER DATABASE DROP STANDBY LOGFILE GROUP $grp;"
if [ $? -eq 0 ]; then
log "Dropped standby redo log group $grp."
break
else
log "Retrying to drop standby redo group $grp after clear operation."
sleep 5
fi
done
done
}
text_to_html() {
local input_file=$1
local output_file=$2
{
echo "Report"
cat "$input_file"
echo ""
} > "$output_file"
log "Converted $input_file into HTML format as $output_file."
}
send_email() {
local to_address=$1
local subject=$2
local file_to_send=$3
local file_size_kb
file_size_kb=$(du -k "$file_to_send" | cut -f1)
if [ "$file_size_kb" -lt 1024 ]; then
mailx -a "Content-type: text/html" -s "$subject" "$to_address" < "$file_to_send"
log "Sent email to $to_address with embedded HTML content."
else
echo "Please see attached report." | mailx -s "$subject" -a "$file_to_send" "$to_address"
log "Sent email to $to_address with $file_to_send as attachment (file size > 1MB)."
fi
}
#!/bin/bash
# Main script to resize redo and standby redo logs
set -euo pipefail
INPUT_FILE=$1
LOG_FILE="./logs/redo_log_resize_$(date +%Y%m%d%H%M%S).log"
source ./functions.sh
# Initialization
NEW_REDO_CREATED=0
NEW_STANDBY_CREATED=0
if [ ! -f "$INPUT_FILE" ]; then
error_exit "Input file not found!"
fi
readarray -t PARAMS < "$INPUT_FILE"
DB_NAME=${PARAMS[0]:-}
SYS_USER=${PARAMS[1]:-}
SYS_PASSWORD=${PARAMS[2]:-}
NEW_REDO_SIZE_MB=${PARAMS[3]:-}
NEW_STANDBY_REDO_SIZE_MB=${PARAMS[4]:-}
PRIMARY_CONN="$SYS_USER/$SYS_PASSWORD@$DB_NAME as sysdba"
STANDBY_CONN="$SYS_USER/$SYS_PASSWORD@${DB_NAME}_STBY as sysdba"
trap 'error_exit "Unexpected error occurred."' ERR
# Capture initial state
log "Capturing current redo/standby logs BEFORE changes..."
save_logfile_state "$PRIMARY_CONN" "/tmp/primary_before.lst"
save_logfile_state "$STANDBY_CONN" "/tmp/standby_before.lst"
capture_existing_groups "$PRIMARY_CONN" PRIMARY_OLD_REDO_GROUPS PRIMARY_OLD_STANDBY_GROUPS
capture_existing_groups "$STANDBY_CONN" STANDBY_OLD_REDO_GROUPS STANDBY_OLD_STANDBY_GROUPS
log "Setting standby_file_management to MANUAL."
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;"
MAX_GROUP=$(exec_sqlplus "$PRIMARY_CONN" "SELECT GREATEST(MAX(group#), NVL((SELECT MAX(group#) FROM v$standby_log), 0)) FROM v$log;")
NEW_GROUP_ID=$((MAX_GROUP + 1))
MAX_THREAD=$(exec_sqlplus "$PRIMARY_CONN" "SELECT GREATEST(MAX(thread#)) FROM (SELECT thread# FROM v$log UNION SELECT thread# FROM v$standby_log);")
REDO_PER_THREAD=$(exec_sqlplus "$PRIMARY_CONN" "SELECT COUNT(1) FROM v$log WHERE thread#=1;")
STANDBY_LOGS_PER_THREAD=$((REDO_PER_THREAD + 1))
# Create redo logs and standby logs
log "Adding new redo log groups on PRIMARY."
add_redo_log_groups "$PRIMARY_CONN" "$NEW_REDO_SIZE_MB"
log "Adding new redo log groups on STANDBY."
add_redo_log_groups "$STANDBY_CONN" "$NEW_REDO_SIZE_MB"
log "Adding new standby redo log groups on PRIMARY."
add_standby_redo_log_groups "$PRIMARY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
log "Adding new standby redo log groups on STANDBY."
add_standby_redo_log_groups "$STANDBY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
# Force logfile switch
log "Switching logfile to make old redo inactive."
exec_sqlplus "$PRIMARY_CONN" "ALTER SYSTEM SWITCH LOGFILE;"
sleep 10
# Drop old groups
log "Dropping old redo logs on PRIMARY..."
drop_redo_groups_safely "$PRIMARY_CONN" "$PRIMARY_OLD_REDO_GROUPS"
log "Dropping old standby redo logs on PRIMARY..."
drop_standby_groups_safely "$PRIMARY_CONN" "$PRIMARY_OLD_STANDBY_GROUPS"
log "Dropping old redo logs on STANDBY..."
drop_redo_groups_safely "$STANDBY_CONN" "$STANDBY_OLD_REDO_GROUPS"
log "Dropping old standby redo logs on STANDBY..."
drop_standby_groups_safely "$STANDBY_CONN" "$STANDBY_OLD_STANDBY_GROUPS"
# Reset standby management
log "Setting standby_file_management back to AUTO."
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;"
# Restart managed recovery if needed
STANDBY_DB_ROLE=$(exec_sqlplus "$STANDBY_CONN" "SELECT database_role FROM v$database;")
if [[ "$STANDBY_DB_ROLE" == "PHYSICAL STANDBY" ]]; then
log "Restarting managed recovery on standby."
exec_sqlplus "$STANDBY_CONN" "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;"
fi
# Capture final state
log "Capturing current redo/standby logs AFTER changes..."
save_logfile_state "$PRIMARY_CONN" "/tmp/primary_after.lst"
save_logfile_state "$STANDBY_CONN" "/tmp/standby_after.lst"
log "Diff for PRIMARY database:"
diff /tmp/primary_before.lst /tmp/primary_after.lst | tee -a "$LOG_FILE"
log "Diff for STANDBY database:"
diff /tmp/standby_before.lst /tmp/standby_after.lst | tee -a "$LOG_FILE"
rm -f /tmp/primary_before.lst /tmp/primary_after.lst /tmp/standby_before.lst /tmp/standby_after.lst
log "Redo and Standby redo log resizing complete."
#####################
#!/bin/bash
# Common functions for redo and standby redo log management
log() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] $1" | tee -a "$LOG_FILE"
}
error_exit() {
echo "[$(date '+%Y-%m-%d %H:%M:%S')] ERROR: $1" | tee -a "$LOG_FILE" >&2
exit 1
}
exec_sqlplus() {
sqlplus -s /nolog < "$FILE"
}
capture_existing_groups() {
local CONN=$1
local VAR_REDO=$2
local VAR_STANDBY=$3
eval "$VAR_REDO=\"\$(exec_sqlplus \"$CONN\" \"SELECT group# FROM v\\$log ORDER BY group#;\")\""
eval "$VAR_STANDBY=\"\$(exec_sqlplus \"$CONN\" \"SELECT group# FROM v\\$standby_log ORDER BY group#;\")\""
}
add_redo_log_groups() {
local CONN=$1
local REDO_SIZE_MB=$2
local DB_ROLE
DB_ROLE=$(exec_sqlplus "$CONN" "SELECT database_role FROM v\$database;")
for thr in $(exec_sqlplus "$CONN" "SELECT DISTINCT thread# FROM v\$log;"); do
exec_sqlplus "$CONN" "ALTER DATABASE ADD LOGFILE THREAD $thr GROUP $NEW_GROUP_ID SIZE ${REDO_SIZE_MB}M;"
log "[$DB_ROLE] Added redo log group $NEW_GROUP_ID for thread $thr on $CONN."
NEW_GROUP_ID=$((NEW_GROUP_ID + 1))
NEW_REDO_CREATED=$((NEW_REDO_CREATED + 1))
done
}
add_standby_redo_log_groups() {
local CONN=$1
local STANDBY_REDO_SIZE_MB=$2
local DB_ROLE
DB_ROLE=$(exec_sqlplus "$CONN" "SELECT database_role FROM v\$database;")
for ((thr=1; thr<=MAX_THREAD; thr++)); do
for ((i=1; i<=STANDBY_LOGS_PER_THREAD; i++)); do
exec_sqlplus "$CONN" "ALTER DATABASE ADD STANDBY LOGFILE THREAD $thr GROUP $NEW_GROUP_ID SIZE ${STANDBY_REDO_SIZE_MB}M;"
log "[$DB_ROLE] Added standby redo log group $NEW_GROUP_ID for thread $thr on $CONN."
NEW_GROUP_ID=$((NEW_GROUP_ID + 1))
NEW_STANDBY_CREATED=$((NEW_STANDBY_CREATED + 1))
done
sleep 2
done
}
drop_redo_groups_safely() {
local CONN=$1
local GROUPS=$2
for grp in $GROUPS; do
while true; do
exec_sqlplus "$CONN" "ALTER SYSTEM SWITCH LOGFILE;"
sleep 5
exec_sqlplus "$CONN" "ALTER DATABASE DROP LOGFILE GROUP $grp;"
if [ $? -eq 0 ]; then
log "Dropped redo log group $grp."
break
else
log "Retrying to drop redo log group $grp after logfile switch."
sleep 5
fi
done
done
}
drop_standby_groups_safely() {
local CONN=$1
local GROUPS=$2
for grp in $GROUPS; do
while true; do
exec_sqlplus "$CONN" "ALTER DATABASE DROP STANDBY LOGFILE GROUP $grp;"
if [ $? -eq 0 ]; then
log "Dropped standby redo log group $grp."
break
else
log "Retrying to drop standby redo group $grp after clear operation."
sleep 5
fi
done
done
}
