get_max_thread_by_instance() { local conns=("$@") local max_thread=0 for conn in "${conns[@]}"; do echo "[INFO] Checking instance numbers on: \"$conn\"" local thread_num thread_num=$(exec_sqlplus "$conn" " SELECT MAX(instance_number) FROM gv\\$instance; " | tr -d '[:space:]') if [[ "$thread_num" =~ ^[0-9]+$ ]] && [[ "$thread_num" -gt "$max_thread" ]]; then max_thread=$thread_num fi done echo "$max_thread" } get_largest_max_thread_from_dbs() { local conns=("$@") local max_thread=0 for conn in "${conns[@]}"; do echo "Checking max thread# on: $conn" local thread_num thread_num=$(exec_sqlplus "$conn" " SELECT MAX(thread#) FROM ( SELECT thread# FROM v\\$log UNION SELECT thread# FROM v\\$standby_log ); " | tr -d '[:space:]') if [[ "$thread_num" =~ ^[0-9]+$ ]] && [[ "$thread_num" -gt "$max_thread" ]]; then max_thread=$thread_num fi done echo "$max_thread" } # Provide all DBs as arguments MAX_THREAD=$(get_largest_max_thread_from_dbs "$PRIMARY_CONN" "${STANDBY_CONN_LIST[@]}") echo " Largest thread# across all databases: $MAX_THREAD" #!/bin/bash SYS_PASS="oracle" STANDBY_CONN="myhost-scan:1521/MYCDB" exec_sqlplus() { local conn="$1" local sql="$2" echo ">> Connecting to $conn" echo "SQL: $sql" } drop_redo_groups_safely() { local conn="$1" shift local groups=("$@") echo "DEBUG: conn=$conn" for g in "${groups[@]}"; do echo "Would drop group $g on $conn" done } groups=("11" "12" "13") drop_redo_groups_safely "$STANDBY_CONN" "${groups[@]}" GROUPS_TO_DROP=$(echo "${STANDBY_REDO_GROUPS_MAP[$STANDBY_CONN]}" | tr ' ' '\n' | awk -F',' '{print $1}') read -ra GROUP_LIST <<< "$GROUPS_TO_DROP" drop_redo_groups_safely "$STANDBY_CONN" "${GROUP_LIST[@]}" drop_redo_groups_safely() { local CONN="$1" shift local GROUPS=("$@") local DB_ROLE DB_ROLE=$(exec_sqlplus "$CONN" "SELECT database_role FROM v\\$database;") for grp in "${GROUPS[@]}"; do local dropped=0 local attempts=0 while [[ $attempts -lt 5 ]]; do ((attempts++)) if [[ "$DB_ROLE" == "PRIMARY" ]]; then echo "🔁 [${CONN}] Switching logfile before dropping group $grp (attempt $attempts)..." exec_sqlplus "$CONN" "ALTER SYSTEM SWITCH LOGFILE;" sleep 5 fi echo "⚙️ Attempting to drop redo log group $grp from $CONN (attempt $attempts)..." exec_sqlplus "$CONN" "ALTER DATABASE DROP LOGFILE GROUP $grp;" if [[ $? -eq 0 ]]; then echo "✅ Successfully dropped redo log group $grp from $CONN" dropped=1 break else echo "❌ Failed to drop redo log group $grp on attempt $attempts" sleep 5 fi done if [[ $dropped -eq 0 ]]; then echo "🛑 Could not drop redo group $grp after 5 attempts on $CONN" fi done } drop_standby_groups_safely() { local CONN="$1" shift local GROUPS=("$@") local DB_ROLE DB_ROLE=$(exec_sqlplus "$CONN" "SELECT database_role FROM v\\$database;") for grp in "${GROUPS[@]}"; do local dropped=0 for attempt in {1..5}; do if [[ "$DB_ROLE" == "PHYSICAL STANDBY" ]]; then exec_sqlplus "$CONN" "ALTER DATABASE CLEAR LOGFILE GROUP $grp;" sleep 2 fi exec_sqlplus "$CONN" "ALTER DATABASE DROP STANDBY LOGFILE GROUP $grp;" if [[ $? -eq 0 ]]; then echo "✅ Dropped standby group $grp from $CONN" dropped=1 break else echo "🔁 Failed to drop standby group $grp. Retrying..." sleep 5 fi done if [[ $dropped -eq 0 ]]; then echo "❌ Could not drop standby group $grp from $CONN after retries." fi done } #!/bin/bash # Script: recreate_redologs_enhanced.sh # Usage: ./recreate_redologs_enhanced.sh # Initialize environment INPUT_FILE=$1 LOG_FILE="redo_recreation_$(date +%Y%m%d%H%M%S).log" TMP_DIR="/tmp/redo_workdir_$(date +%s)" mkdir -p $TMP_DIR # Load configuration source $INPUT_FILE || { echo "Error loading input file"; exit 1; } # Database connections PRIMARY_CONN="${SYS_USER}/${SYS_PASSWORD}@${DB_NAME} as sysdba" STANDBY_DB=$(dgmgrl -silent ${PRIMARY_CONN} < $TMP_DIR/primary_groups.lst log "Capturing standby database configuration" capture_groups "$STANDBY_CONN" STANDBY > $TMP_DIR/standby_groups.lst # Create new groups on both databases for db_type in PRIMARY STANDBY; do conn=$([ "$db_type" = "PRIMARY" ] && echo "$PRIMARY_CONN" || echo "$STANDBY_CONN") input_file=$TMP_DIR/${db_type,,}_groups.lst # Create primary redo logs log "Creating new PRIMARY redo logs on $db_type" awk -F: '/:PRIMARY:/{print $2}' $input_file | sort -nu | while read thread; do count=$(grep ":PRIMARY:$db_type" $input_file | grep ":$thread:" | wc -l) create_new_groups "$conn" "PRIMARY" "$NEW_REDO_SIZE" "$thread" "$count" done # Create standby redo logs log "Creating new STANDBY redo logs on $db_type" awk -F: '/:STANDBY:/{print $2}' $input_file | sort -nu | while read thread; do count=$(grep ":STANDBY:$db_type" $input_file | grep ":$thread:" | wc -l) create_new_groups "$conn" "STANDBY" "$NEW_STANDBY_REDO_SIZE" "$thread" "$count" done done # Force log switches to age out old groups log "Initiating log switches to inactive old groups" for i in {1..10}; do sqlplus -S /nolog <