# Function to compare DBA_REGISTRY components
compare_dba_registry() {
local src_conn=$1
local tgt_conn=$2
local html_file=$3
echo "
" >> "$html_file"
echo "
Database Component Comparison (DBA_REGISTRY)
" >> "$html_file"
# Get components from both databases
sqlplus -s /nolog << EOF > /tmp/src_components.txt
connect $src_conn
set pagesize 0 feedback off linesize 200
SELECT comp_name || '|' || version || '|' || status FROM dba_registry ORDER BY comp_name;
EOF
sqlplus -s /nolog << EOF > /tmp/tgt_components.txt
connect $tgt_conn
set pagesize 0 feedback off linesize 200
SELECT comp_name || '|' || version || '|' || status FROM dba_registry ORDER BY comp_name;
EOF
# Clean SQL*Plus output
sed -i '/^Disconnected/d' /tmp/src_components.txt /tmp/tgt_components.txt
sed -i '/^$/d' /tmp/src_components.txt /tmp/tgt_components.txt
echo "
| Component |
Source Version |
Source Status |
Target Version |
Target Status |
Status |
" >> "$html_file"
awk -F'|' '
BEGIN {
print ""
}
NR==FNR {
src_comp[$1] = $2 "|" $3
delete tgt_comp[$1]
next
}
{
comp=$1
if (comp in src_comp) {
split(src_comp[comp], src, "|")
if ($2 != src[1]) {
status = "version-mismatch"
msg = "Version mismatch"
} else if ($3 != src[2]) {
status = "status-mismatch"
msg = "Status mismatch"
} else {
status = "match"
msg = "OK"
}
printf "| %s | %s | %s | %s | %s | %s |
\n",
status, comp, src[1], src[2], $2, $3, msg
delete src_comp[comp]
} else {
tgt_comp[$1] = $0
}
}
END {
# Print components only in source
for (comp in src_comp) {
split(src_comp[comp], src, "|")
printf "| %s | %s | %s | Not Found | Missing in target |
\n",
comp, src[1], src[2]
}
# Print components only in target
for (comp in tgt_comp) {
split(tgt_comp[comp], tgt, "|")
printf "| %s | Not Found | %s | %s | Missing in source |
\n",
comp, tgt[2], tgt[3]
}
print ""
}
' /tmp/src_components.txt /tmp/tgt_components.txt >> "$html_file"
echo "
" >> "$html_file"
echo "
" >> "$html_file"
rm -f /tmp/src_components.txt /tmp/tgt_components.txt
}
init_html_report() {
local html_file="$1"
local migration="$2"
cat << EOF > "$html_file"
PDB Compatibility Report: $migration
PDB Compatibility Report: $migration
Generated at: $(date)
EOF
}
#!/bin/bash
# Configuration
REPORT_FILE="firewall_report.html"
EMAIL_FROM="dba@example.com"
EMAIL_TO="admin@example.com"
EMAIL_SUBJECT="Firewall Policy Validation Report"
TNS_FILE="${1}"
# Check if tnsnames.ora file is provided
if [ -z "${TNS_FILE}" ] || [ ! -f "${TNS_FILE}" ]; then
echo "Usage: $0
"
echo "Please provide valid tnsnames.ora file path"
exit 1
fi
# Check for dependencies
command -v nc >/dev/null 2>&1 || { echo "netcat (nc) is required but not installed. Aborting."; exit 1; }
# Initialize HTML report
initialize_report() {
cat << EOF > "${REPORT_FILE}"
Firewall Policy Validation Report
Firewall Policy Validation Report
Generated at: $(date)
| DB Name |
Hostname |
Port 1521 Status |
EOF
}
# Extract dbname and hosts from tnsnames.ora
extract_entries() {
awk -v RS= '{
dbname = ""; host = ""
# Extract dbname from first line
if (match($0, /^[^=]+/)) {
dbname = substr($0, RSTART, RLENGTH)
gsub(/^[ \t]+|[ \t]+$/, "", dbname)
}
# Extract all HOST entries
remaining = $0
while (match(remaining, /HOST\s*=\s*[^ \t\)]+/i)) {
host_line = substr(remaining, RSTART, RLENGTH)
split(host_line, arr, "=")
host = arr[2]
gsub(/^[ \t]+|[ \t]+$/, "", host)
print dbname "|" host
remaining = substr(remaining, RSTART + RLENGTH)
}
}' "${TNS_FILE}" | sort -u
}
# Test port function
test_port() {
host=$1
port=1521
timeout 3 nc -zv "${host}" "${port}" > /dev/null 2>&1
if [ $? -eq 0 ]; then
echo "success"
else
echo "failure"
fi
}
# Complete HTML report
finalize_report() {
cat << EOF >> "${REPORT_FILE}"
EOF
}
# Email function
send_email() {
local report_size=$(du -b "${REPORT_FILE}" | awk '{print $1}')
if [ "${report_size}" -lt 1048576 ]; then
mailx -s "${EMAIL_SUBJECT}" -a "Content-type: text/html" -r "${EMAIL_FROM}" "${EMAIL_TO}" < "${REPORT_FILE}"
else
echo "Please find attached the firewall validation report" | \
mailx -s "${EMAIL_SUBJECT}" -a "${REPORT_FILE}" -r "${EMAIL_FROM}" "${EMAIL_TO}"
fi
}
# Main execution
initialize_report
while IFS="|" read -r dbname host; do
[ -z "$host" ] && continue # Skip invalid entries
status=$(test_port "${host}")
if [ "${status}" = "success" ]; then
class="success"
status_text="Open"
else
class="failure"
status_text="Closed"
fi
echo "Processing DB: ${dbname} - Host: ${host} - Status: ${status_text}"
cat << EOF >> "${REPORT_FILE}"
| ${dbname} |
${host} |
${status_text} |
EOF
done < <(extract_entries)
finalize_report
# Send email
send_email
echo "Report generated: ${REPORT_FILE}"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
IFS=$'\n' read -rd '' -a lines <<< "${STANDBY_REDO_GROUPS_MAP[$STANDBY_CONN]}"
GROUPS_TO_DROP=""
for entry in "${lines[@]}"; do
[[ -z "$entry" ]] && continue
grp="${entry%%,*}"
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
done
drop_redo_groups_safely "$STANDBY_CONN" "$GROUPS_TO_DROP"
IFS=$'\n' read -rd '' -a lines <<< "${STANDBY_STANDBY_GROUPS_MAP[$STANDBY_CONN]}"
GROUPS_TO_DROP=""
for entry in "${lines[@]}"; do
[[ -z "$entry" ]] && continue
grp="${entry%%,*}"
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
done
drop_standby_groups_safely "$STANDBY_CONN" "$GROUPS_TO_DROP"
done
STANDBY_REDO_GROUPS="101,1
102,1
103,2
104,2"
# Set IFS to split on newline
IFS=$'\n' read -rd '' -a lines <<< "$STANDBY_REDO_GROUPS"
GROUPS_TO_DROP=""
for entry in "${lines[@]}"; do
[[ -z "$entry" ]] && continue
grp="${entry%%,*}" # part before comma
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
done
echo "Result GROUPS_TO_DROP = '$GROUPS_TO_DROP'"
STANDBY_REDO_GROUPS="101,1
102,1
103,2
104,2"
# Set IFS to split on newline
IFS=$'\n' read -rd '' -a lines <<< "$STANDBY_REDO_GROUPS"
GROUPS_TO_DROP=""
for entry in "${lines[@]}"; do
[[ -z "$entry" ]] && continue
grp="${entry%%,*}" # part before comma
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
done
echo "Result GROUPS_TO_DROP = '$GROUPS_TO_DROP'"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
GROUPS_TO_DROP=""
while IFS= read -r entry; do
[[ -z "$entry" ]] && continue
grp=$(echo "$entry" | cut -d',' -f1)
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
done < <(echo "${STANDBY_REDO_GROUPS_MAP[$STANDBY_CONN]}")
drop_redo_groups_safely "$STANDBY_CONN" "$GROUPS_TO_DROP"
GROUPS_TO_DROP=""
while IFS= read -r entry; do
[[ -z "$entry" ]] && continue
grp=$(echo "$entry" | cut -d',' -f1)
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
done < <(echo "${STANDBY_STANDBY_GROUPS_MAP[$STANDBY_CONN]}")
drop_standby_groups_safely "$STANDBY_CONN" "$GROUPS_TO_DROP"
done
#!/bin/bash
# Script to resize redo and standby redo logs using sqlplus & dgmgrl connections.
# Accepts input from a provided text file.
set -euo pipefail
INPUT_FILE=$1
LOG_FILE="redo_log_resize_$(date +%Y%m%d%H%M%S).log"
NEW_REDO_CREATED=0
NEW_STANDBY_CREATED=0
OLD_REDO_DROPPED=0
OLD_STANDBY_DROPPED=0
source ./functions.sh
save_redo_logfile_state() {
local CONN=$1
local FILE=$2
exec_sqlplus "$CONN" "
SET PAGESIZE 500
SET LINESIZE 300
COL GROUP# FOR 9999
COL THREAD# FOR 99
COL SIZE_MB FOR 99999
COL MEMBER FOR A100
SELECT l.group#, l.thread#, l.bytes/1024/1024 AS size_mb, lf.member
FROM v\$log l
JOIN v\$logfile lf ON l.group# = lf.group#
ORDER BY l.thread#, l.group#;
" > "$FILE"
log "Saved REDO log file state to $FILE"
}
save_standby_logfile_state() {
local CONN=$1
local FILE=$2
exec_sqlplus "$CONN" "
SET PAGESIZE 500
SET LINESIZE 300
COL GROUP# FOR 9999
COL THREAD# FOR 99
COL SIZE_MB FOR 99999
COL MEMBER FOR A100
SELECT sl.group#, sl.thread#, sl.bytes/1024/1024 AS size_mb, lf.member
FROM v\$standby_log sl
JOIN v\$logfile lf ON sl.group# = lf.group#
ORDER BY sl.thread#, sl.group#;
" > "$FILE"
log "Saved STANDBY log file state to $FILE"
}
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]:-}
STANDBY_DB_NAMES=(${PARAMS[@]:5})
PRIMARY_CONN="$SYS_USER/$SYS_PASSWORD@$DB_NAME as sysdba"
STANDBY_CONNS=()
for standby_db in "${STANDBY_DB_NAMES[@]}"; do
STANDBY_CONNS+=("$SYS_USER/$SYS_PASSWORD@$standby_db as sysdba")
fi
trap 'error_exit "Unexpected error occurred."' ERR
save_redo_logfile_state "$PRIMARY_CONN" "/tmp/primary_redo_before.lst"
save_standby_logfile_state "$PRIMARY_CONN" "/tmp/primary_standby_before.lst"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
conn_clean=$(echo "$STANDBY_CONN" | sed 's/[^a-zA-Z0-9]/_/g')
save_redo_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_redo_before.lst"
save_standby_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_standby_before.lst"
done
log "Setting standby_file_management to MANUAL."
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;"
done
log "Capturing current redo and standby log groups."
PRIMARY_REDO_GROUPS=$(exec_sqlplus "$PRIMARY_CONN" "SELECT group# FROM v\$log ORDER BY group#;")
PRIMARY_STANDBY_GROUPS=$(exec_sqlplus "$PRIMARY_CONN" "SELECT group# FROM v\$standby_log ORDER BY group#;")
declare -A STANDBY_REDO_GROUPS_MAP
declare -A STANDBY_STANDBY_GROUPS_MAP
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
STANDBY_REDO_GROUPS_MAP["$STANDBY_CONN"]=$(exec_sqlplus "$STANDBY_CONN" "SELECT group#||','||thread# FROM v\$log ORDER BY group#;")
STANDBY_STANDBY_GROUPS_MAP["$STANDBY_CONN"]=$(exec_sqlplus "$STANDBY_CONN" "SELECT group#||','||thread# FROM v\$standby_log ORDER BY group#;")
done
MAX_GROUP=$(exec_sqlplus "$PRIMARY_CONN" "
SELECT MAX(max_group#) FROM (
SELECT MAX(group#) AS max_group# FROM v\$log
UNION ALL
SELECT MAX(group#) FROM v\$standby_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))
log "Creating new redo log groups."
add_redo_log_groups "$PRIMARY_CONN" "$NEW_REDO_SIZE_MB"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
add_redo_log_groups "$STANDBY_CONN" "$NEW_REDO_SIZE_MB"
done
log "Creating new standby redo log groups."
add_standby_redo_log_groups "$PRIMARY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
add_standby_redo_log_groups "$STANDBY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
done
log "Forcing log switch."
exec_sqlplus "$PRIMARY_CONN" "ALTER SYSTEM SWITCH LOGFILE;"
sleep 10
log "Dropping old redo and standby redo log groups."
drop_redo_groups_safely "$PRIMARY_CONN" "$PRIMARY_REDO_GROUPS"
drop_standby_groups_safely "$PRIMARY_CONN" "$PRIMARY_STANDBY_GROUPS"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
GROUPS_TO_DROP=""
while IFS= read -r entry; do
grp="${entry%%,*}"
if [[ -n "$grp" ]]; then
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
fi
done <<< "${STANDBY_REDO_GROUPS_MAP[$STANDBY_CONN]}"
drop_redo_groups_safely "$STANDBY_CONN" "$GROUPS_TO_DROP"
GROUPS_TO_DROP=""
while IFS= read -r entry; do
grp="${entry%%,*}"
if [[ -n "$grp" ]]; then
GROUPS_TO_DROP="$GROUPS_TO_DROP $grp"
fi
done <<< "${STANDBY_STANDBY_GROUPS_MAP[$STANDBY_CONN]}"
drop_standby_groups_safely "$STANDBY_CONN" "$GROUPS_TO_DROP"
done
log "Setting standby_file_management back to AUTO."
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;"
done
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
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 $STANDBY_CONN."
exec_sqlplus "$STANDBY_CONN" "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;"
fi
done
save_redo_logfile_state "$PRIMARY_CONN" "/tmp/primary_redo_after.lst"
save_standby_logfile_state "$PRIMARY_CONN" "/tmp/primary_standby_after.lst"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
conn_clean=$(echo "$STANDBY_CONN" | sed 's/[^a-zA-Z0-9]/_/g')
save_redo_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_redo_after.lst"
save_standby_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_standby_after.lst"
done
log "Validating Data Guard configuration."
echo "show configuration;" | dgmgrl / "$SYS_USER/$SYS_PASSWORD@$DB_NAME" | tee -a "$LOG_FILE"
log "Redo and Standby redo log resizing complete."
log "Summary:"
echo " New redo log groups created: $NEW_REDO_CREATED" | tee -a "$LOG_FILE"
echo " New standby redo log groups created: $NEW_STANDBY_CREATED" | tee -a "$LOG_FILE"
echo " Old redo log groups dropped: $OLD_REDO_DROPPED" | tee -a "$LOG_FILE"
echo " Old standby redo log groups dropped: $OLD_STANDBY_DROPPED" | tee -a "$LOG_FILE"
log "Summary also saved to $LOG_FILE"
#!/bin/bash
# Script to resize redo and standby redo logs using sqlplus & dgmgrl connections.
# Accepts input from a provided text file.
set -euo pipefail
INPUT_FILE=$1
LOG_FILE="redo_log_resize_$(date +%Y%m%d%H%M%S).log"
NEW_REDO_CREATED=0
NEW_STANDBY_CREATED=0
OLD_REDO_DROPPED=0
OLD_STANDBY_DROPPED=0
source ./functions.sh
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]:-}
STANDBY_DB_NAMES=(${PARAMS[@]:5})
PRIMARY_CONN="$SYS_USER/$SYS_PASSWORD@$DB_NAME as sysdba"
STANDBY_CONNS=()
for standby_db in "${STANDBY_DB_NAMES[@]}"; do
STANDBY_CONNS+=("$SYS_USER/$SYS_PASSWORD@$standby_db as sysdba")
fi
trap 'error_exit "Unexpected error occurred."' ERR
save_redo_logfile_state "$PRIMARY_CONN" "/tmp/primary_redo_before.lst"
save_standby_logfile_state "$PRIMARY_CONN" "/tmp/primary_standby_before.lst"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
conn_clean=$(echo "$STANDBY_CONN" | sed 's/[^a-zA-Z0-9]/_/g')
save_redo_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_redo_before.lst"
save_standby_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_standby_before.lst"
done
log "Setting standby_file_management to MANUAL."
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;"
done
log "Capturing current redo and standby log groups."
PRIMARY_REDO_GROUPS=$(exec_sqlplus "$PRIMARY_CONN" "SELECT group#||','||thread# FROM v\$log;")
PRIMARY_STANDBY_GROUPS=$(exec_sqlplus "$PRIMARY_CONN" "SELECT group#||','||thread# FROM v\$standby_log;")
declare -A STANDBY_REDO_GROUPS_MAP
declare -A STANDBY_STANDBY_GROUPS_MAP
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
STANDBY_REDO_GROUPS_MAP["$STANDBY_CONN"]=$(exec_sqlplus "$STANDBY_CONN" "SELECT group#||','||thread# FROM v\$log;")
STANDBY_STANDBY_GROUPS_MAP["$STANDBY_CONN"]=$(exec_sqlplus "$STANDBY_CONN" "SELECT group#||','||thread# FROM v\$standby_log;")
done
MAX_GROUP=$(exec_sqlplus "$PRIMARY_CONN" "
SELECT MAX(max_group#) FROM (
SELECT MAX(group#) AS max_group# FROM v\$log
UNION ALL
SELECT MAX(group#) FROM v\$standby_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))
log "Creating new redo log groups."
add_redo_log_groups "$PRIMARY_CONN" "$NEW_REDO_SIZE_MB"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
add_redo_log_groups "$STANDBY_CONN" "$NEW_REDO_SIZE_MB"
done
log "Creating new standby redo log groups."
add_standby_redo_log_groups "$PRIMARY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
add_standby_redo_log_groups "$STANDBY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
done
log "Forcing log switch."
exec_sqlplus "$PRIMARY_CONN" "ALTER SYSTEM SWITCH LOGFILE;"
sleep 10
log "Dropping old redo and standby redo log groups."
drop_redo_groups_safely "$PRIMARY_CONN" "$PRIMARY_REDO_GROUPS"
drop_standby_groups_safely "$PRIMARY_CONN" "$PRIMARY_STANDBY_GROUPS"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
drop_redo_groups_safely "$STANDBY_CONN" "${STANDBY_REDO_GROUPS_MAP[$STANDBY_CONN]}"
drop_standby_groups_safely "$STANDBY_CONN" "${STANDBY_STANDBY_GROUPS_MAP[$STANDBY_CONN]}"
done
log "Setting standby_file_management back to AUTO."
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;"
done
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
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 $STANDBY_CONN."
exec_sqlplus "$STANDBY_CONN" "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;"
fi
done
save_redo_logfile_state "$PRIMARY_CONN" "/tmp/primary_redo_after.lst"
save_standby_logfile_state "$PRIMARY_CONN" "/tmp/primary_standby_after.lst"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
conn_clean=$(echo "$STANDBY_CONN" | sed 's/[^a-zA-Z0-9]/_/g')
save_redo_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_redo_after.lst"
save_standby_logfile_state "$STANDBY_CONN" "/tmp/${conn_clean}_standby_after.lst"
done
log "Validating Data Guard configuration."
echo "show configuration;" | dgmgrl / "$SYS_USER/$SYS_PASSWORD@$DB_NAME" | tee -a "$LOG_FILE"
log "Redo and Standby redo log resizing complete."
log "Summary:"
echo " New redo log groups created: $NEW_REDO_CREATED" | tee -a "$LOG_FILE"
echo " New standby redo log groups created: $NEW_STANDBY_CREATED" | tee -a "$LOG_FILE"
echo " Old redo log groups dropped: $OLD_REDO_DROPPED" | tee -a "$LOG_FILE"
echo " Old standby redo log groups dropped: $OLD_STANDBY_DROPPED" | tee -a "$LOG_FILE"
log "Summary also saved to $LOG_FILE"
#!/bin/bash
# Script to resize redo and standby redo logs using sqlplus & dgmgrl connections.
# Accepts input from a provided text file.
set -euo pipefail
INPUT_FILE=$1
LOG_FILE="redo_log_resize_$(date +%Y%m%d%H%M%S).log"
NEW_REDO_CREATED=0
NEW_STANDBY_CREATED=0
OLD_REDO_DROPPED=0
OLD_STANDBY_DROPPED=0
source ./functions.sh
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]:-}
STANDBY_DB_NAMES=(${PARAMS[@]:5})
PRIMARY_CONN="$SYS_USER/$SYS_PASSWORD@$DB_NAME as sysdba"
STANDBY_CONNS=()
for standby_db in "${STANDBY_DB_NAMES[@]}"; do
STANDBY_CONNS+=("$SYS_USER/$SYS_PASSWORD@$standby_db as sysdba")
fi
trap 'error_exit "Unexpected error occurred."' ERR
log_current_logfile_state "$PRIMARY_CONN" "PRIMARY (Before)"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
log_current_logfile_state "$STANDBY_CONN" "STANDBY (Before)"
done
log "Setting standby_file_management to MANUAL."
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;"
done
log "Capturing current redo and standby log groups."
PRIMARY_REDO_GROUPS=$(exec_sqlplus "$PRIMARY_CONN" "SELECT group#||','||thread# FROM v\$log;")
PRIMARY_STANDBY_GROUPS=$(exec_sqlplus "$PRIMARY_CONN" "SELECT group#||','||thread# FROM v\$standby_log;")
declare -A STANDBY_REDO_GROUPS_MAP
declare -A STANDBY_STANDBY_GROUPS_MAP
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
STANDBY_REDO_GROUPS_MAP["$STANDBY_CONN"]=$(exec_sqlplus "$STANDBY_CONN" "SELECT group#||','||thread# FROM v\$log;")
STANDBY_STANDBY_GROUPS_MAP["$STANDBY_CONN"]=$(exec_sqlplus "$STANDBY_CONN" "SELECT group#||','||thread# FROM v\$standby_log;")
done
MAX_GROUP=$(exec_sqlplus "$PRIMARY_CONN" "
SELECT MAX(max_group#) FROM (
SELECT MAX(group#) AS max_group# FROM v\$log
UNION ALL
SELECT MAX(group#) FROM v\$standby_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))
log "Creating new redo log groups."
add_redo_log_groups "$PRIMARY_CONN" "$NEW_REDO_SIZE_MB"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
add_redo_log_groups "$STANDBY_CONN" "$NEW_REDO_SIZE_MB"
done
log "Creating new standby redo log groups."
add_standby_redo_log_groups "$PRIMARY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
add_standby_redo_log_groups "$STANDBY_CONN" "$NEW_STANDBY_REDO_SIZE_MB"
done
log "Forcing log switch."
exec_sqlplus "$PRIMARY_CONN" "ALTER SYSTEM SWITCH LOGFILE;"
sleep 10
log "Dropping old redo and standby redo log groups."
drop_redo_groups_safely "$PRIMARY_CONN" "$PRIMARY_REDO_GROUPS"
drop_standby_groups_safely "$PRIMARY_CONN" "$PRIMARY_STANDBY_GROUPS"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
drop_redo_groups_safely "$STANDBY_CONN" "${STANDBY_REDO_GROUPS_MAP[$STANDBY_CONN]}"
drop_standby_groups_safely "$STANDBY_CONN" "${STANDBY_STANDBY_GROUPS_MAP[$STANDBY_CONN]}"
done
log "Setting standby_file_management back to AUTO."
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
exec_sqlplus "$STANDBY_CONN" "ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;"
done
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
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 $STANDBY_CONN."
exec_sqlplus "$STANDBY_CONN" "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;"
fi
done
log_current_logfile_state "$PRIMARY_CONN" "PRIMARY (After)"
for STANDBY_CONN in "${STANDBY_CONNS[@]}"; do
log_current_logfile_state "$STANDBY_CONN" "STANDBY (After)"
done
log "Validating Data Guard configuration."
echo "show configuration;" | dgmgrl / "$SYS_USER/$SYS_PASSWORD@$DB_NAME" | tee -a "$LOG_FILE"
log "Redo and Standby redo log resizing complete."
log "Summary:"
echo " New redo log groups created: $NEW_REDO_CREATED" | tee -a "$LOG_FILE"
echo " New standby redo log groups created: $NEW_STANDBY_CREATED" | tee -a "$LOG_FILE"
echo " Old redo log groups dropped: $OLD_REDO_DROPPED" | tee -a "$LOG_FILE"
echo " Old standby redo log groups dropped: $OLD_STANDBY_DROPPED" | tee -a "$LOG_FILE"
log "Summary also saved to $LOG_FILE"
#!/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
}
