Sunday, April 27, 2025
#!/bin/bash
# Shell script to resize Oracle database redo log files (primary and standby).
# This script adds new redo log groups of a specified size to an Oracle primary database (and corresponding standby redo logs on the standby database),
# then drops the old redo log groups and standby redo log groups. It handles Oracle RAC by processing each thread,
# and Data Guard by performing actions on both primary and standby.
#
# Usage: resize_redo_logs.sh
# The param_file should be a text file containing five lines with the following values:
# 1. Database name (TNS alias or ORACLE_SID of the primary database)
# 2. SYS user name (usually 'SYS')
# 3. SYS password
# 4. New redo log size in MB (for primary online redo logs)
# 5. New standby redo log size in MB (for standby database)
#
# Example param_file:
# ORCL
# SYS
# MySysPassword
# 1024
# 1024
#
# Note: Ensure the standby database is accessible via a TNS alias derived from the DB name (e.g., ORCL_STBY),
# or adjust the script to use the correct connection string for the standby.
# The script assumes Oracle Managed Files (OMF) or ASM is configured so that explicit file paths are not required
# when adding log files. If OMF is not used, you may need to modify the ALTER DATABASE ADD LOGFILE commands
# to include file paths.
#
# The script will:
# - Connect to the primary database and retrieve existing redo log group numbers and threads.
# - Connect to the standby database and retrieve existing standby redo log group numbers and threads.
# - On the primary: Add new redo log groups for each thread with the specified size.
# - Force log switches on the primary to ensure old logs are archived and not active.
# - Drop old redo log groups on the primary.
# - On the standby: Stop Redo Apply (managed recovery) if active, and set STANDBY_FILE_MANAGEMENT to MANUAL.
# - Add new standby redo log groups for each thread with the specified size.
# - Drop old standby redo log groups on the standby.
# - Resume Redo Apply on the standby and set STANDBY_FILE_MANAGEMENT back to AUTO (if it was originally AUTO).
# - Handle errors during drops by retrying or continuing without exiting the script.
#
# Make sure ORACLE_HOME is set and SQL*Plus is in PATH for the user running this script.
# Also ensure the SYS password is correct and the user has SYSDBA privileges on both primary and standby.
#
# Begin script
# Validate arguments
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
PARAM_FILE="$1"
if [ ! -f "$PARAM_FILE" ]; then
echo "Parameter file '$PARAM_FILE' not found!"
exit 1
fi
# Read parameters from file (5 lines as specified)
DB_NAME=""
SYS_USER=""
SYS_PASS=""
NEW_REDO_MB=""
NEW_STANDBY_MB=""
{
IFS= read -r DB_NAME
IFS= read -r SYS_USER
IFS= read -r SYS_PASS
IFS= read -r NEW_REDO_MB
IFS= read -r NEW_STANDBY_MB
} < "$PARAM_FILE"
# Trim whitespace
DB_NAME=$(echo "$DB_NAME" | xargs)
SYS_USER=$(echo "$SYS_USER" | xargs)
SYS_PASS=$(echo "$SYS_PASS" | xargs)
NEW_REDO_MB=$(echo "$NEW_REDO_MB" | xargs)
NEW_STANDBY_MB=$(echo "$NEW_STANDBY_MB" | xargs)
# Validate parameters
if [ -z "$DB_NAME" ] || [ -z "$SYS_USER" ] || [ -z "$SYS_PASS" ] || [ -z "$NEW_REDO_MB" ] || [ -z "$NEW_STANDBY_MB" ]; then
echo "Parameter file must contain five values: DB_NAME, SYS_USER, SYS_PASS, NEW_REDO_MB, NEW_STANDBY_MB."
exit 1
fi
# Ensure sizes are numeric
if ! [[ "$NEW_REDO_MB" =~ ^[0-9]+$ ]]; then
echo "New redo log size (MB) must be a positive integer."
exit 1
fi
if ! [[ "$NEW_STANDBY_MB" =~ ^[0-9]+$ ]]; then
echo "New standby redo log size (MB) must be a positive integer."
exit 1
fi
# Connection strings (adjust if needed for standby)
PRIMARY_CONN="${SYS_USER}/${SYS_PASS}@${DB_NAME} as sysdba"
STANDBY_CONN="${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY as sysdba"
echo "Starting redo log resize procedure for database '$DB_NAME'..."
# Test connection to primary
echo " - Testing connection to primary database..."
sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
EXIT
EOSQL
if [ $? -ne 0 ]; then
echo "ERROR: Unable to connect to primary database '${DB_NAME}' as SYSDBA."
exit 1
fi
# Test connection to standby
echo " - Testing connection to standby database..."
sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
EXIT
EOSQL
if [ $? -ne 0 ]; then
echo "ERROR: Unable to connect to standby database '${DB_NAME}_STBY' as SYSDBA."
echo "Ensure the standby TNS alias is correct and the standby database is reachable."
exit 1
fi
# Retrieve current redo log groups from primary
echo " - Retrieving current redo log configuration from primary..."
primary_log_info=$(sqlplus -s /nolog <<-EOSQL
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
SELECT group# || ',' || thread# FROM v\\$log ORDER BY thread#, group#;
EXIT
EOSQL
)
if [ -z "$primary_log_info" ]; then
echo "ERROR: Failed to fetch redo log information from primary database."
exit 1
fi
# Retrieve current standby redo log groups from standby
echo " - Retrieving current standby redo log configuration from standby..."
standby_log_info=$(sqlplus -s /nolog <<-EOSQL
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
SELECT group# || ',' || thread# FROM v\\$standby_log ORDER BY thread#, group#;
EXIT
EOSQL
)
# Parse primary redo log info into arrays
OLD_PRIMARY_GROUPS=()
OLD_PRIMARY_THREADS=()
while IFS=',' read -r grp thr; do
if [ -n "$grp" ] && [ -n "$thr" ]; then
OLD_PRIMARY_GROUPS+=("$grp")
OLD_PRIMARY_THREADS+=("$thr")
fi
done <<< "$primary_log_info"
# Parse standby redo log info into arrays (if any)
OLD_STANDBY_GROUPS=()
OLD_STANDBY_THREADS=()
if [ -n "$standby_log_info" ]; then
while IFS=',' read -r grp thr; do
if [ -n "$grp" ] && [ -n "$thr" ]; then
OLD_STANDBY_GROUPS+=("$grp")
OLD_STANDBY_THREADS+=("$thr")
fi
done <<< "$standby_log_info"
fi
# Unique thread IDs on primary and standby
THREADS_ON_PRIMARY=($(printf "%s\n" "${OLD_PRIMARY_THREADS[@]}" | sort -n | uniq))
THREADS_ON_STANDBY=($(printf "%s\n" "${OLD_STANDBY_THREADS[@]}" | sort -n | uniq))
echo " Found ${#OLD_PRIMARY_GROUPS[@]} redo log groups on primary across ${#THREADS_ON_PRIMARY[@]} thread(s)."
if [ ${#OLD_STANDBY_GROUPS[@]} -gt 0 ]; then
echo " Found ${#OLD_STANDBY_GROUPS[@]} standby redo log groups on standby across ${#THREADS_ON_STANDBY[@]} thread(s)."
else
echo " No standby redo logs found on standby (physical standby should have standby logs)."
fi
# Determine starting group numbers for new logs to avoid conflicts
max_primary_group=0
for g in "${OLD_PRIMARY_GROUPS[@]}"; do
if [ "$g" -gt "$max_primary_group" ]; then
max_primary_group=$g
fi
done
max_standby_group=0
for g in "${OLD_STANDBY_GROUPS[@]}"; do
if [ "$g" -gt "$max_standby_group" ]; then
max_standby_group=$g
fi
done
new_primary_group=$((max_primary_group + 1))
# For standby, start after the larger of current primary or standby group numbers
new_standby_group=$(( (max_standby_group > max_primary_group ? max_standby_group : max_primary_group) + 1 ))
# Defer log shipping on primary (to standby) during changes
echo " - Deferring log transport to standby on primary..."
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
BEGIN
FOR dest IN (
SELECT dest_id FROM v\\$archive_dest
WHERE target='STANDBY' AND status='VALID' AND dest_state='ENABLE'
) LOOP
EXECUTE IMMEDIATE 'ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_' || dest.dest_id || '=DEFER SCOPE=MEMORY';
END LOOP;
END;
/
EXIT
EOSQL
# Check current STANDBY_FILE_MANAGEMENT mode on standby
orig_standby_mgmt=$(sqlplus -s /nolog <<-EOSQL
SET HEAD OFF FEEDBACK OFF
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
SELECT value FROM v\\$parameter WHERE name='standby_file_management';
EXIT
EOSQL
)
orig_standby_mgmt=$(echo "$orig_standby_mgmt" | xargs)
if [ -z "$orig_standby_mgmt" ]; then
echo "WARNING: Could not determine STANDBY_FILE_MANAGEMENT mode on standby."
fi
# Cancel managed recovery on standby (if active) and set STANDBY_FILE_MANAGEMENT to MANUAL
echo " - Preparing standby for changes (cancelling Redo Apply and setting STANDBY_FILE_MANAGEMENT to MANUAL)..."
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL'; -- ignore error if not active
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
EXIT
EOSQL
# Add new redo log groups on primary
echo " - Adding new ${NEW_REDO_MB}MB redo log group(s) on primary..."
ADD_REDO_CMDS=""
max_old_count=0
for thr in "${THREADS_ON_PRIMARY[@]}"; do
count=0
for idx in "${!OLD_PRIMARY_THREADS[@]}"; do
if [ "${OLD_PRIMARY_THREADS[$idx]}" = "$thr" ]; then
count=$((count+1))
fi
done
if [ $count -gt $max_old_count ]; then
max_old_count=$count
fi
# Create same number of new log groups for this thread
for ((i=1; i<=count; i++)); do
ADD_REDO_CMDS+="ALTER DATABASE ADD LOGFILE THREAD $thr GROUP $new_primary_group SIZE ${NEW_REDO_MB}M;\n"
new_primary_group=$((new_primary_group + 1))
done
done
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
$ADD_REDO_CMDS
EXIT
EOSQL
# Add new standby redo log groups on standby
echo " - Adding new ${NEW_STANDBY_MB}MB standby redo log group(s) on standby..."
ADD_STANDBY_CMDS=""
if [ ${#OLD_STANDBY_GROUPS[@]} -gt 0 ]; then
# Use same count of standby logs per thread as existing
for thr in "${THREADS_ON_STANDBY[@]}"; do
count=0
for idx in "${!OLD_STANDBY_THREADS[@]}"; do
if [ "${OLD_STANDBY_THREADS[$idx]}" = "$thr" ]; then
count=$((count+1))
fi
done
for ((i=1; i<=count; i++)); do
ADD_STANDBY_CMDS+="ALTER DATABASE ADD STANDBY LOGFILE THREAD $thr GROUP $new_standby_group SIZE ${NEW_STANDBY_MB}M;\n"
new_standby_group=$((new_standby_group + 1))
done
done
else
# If no standby logs present, create same number as primary's online logs per thread
for thr in "${THREADS_ON_PRIMARY[@]}"; do
count=0
for idx in "${!OLD_PRIMARY_THREADS[@]}"; do
if [ "${OLD_PRIMARY_THREADS[$idx]}" = "$thr" ]; then
count=$((count+1))
fi
done
# (Optionally add +1 here to have standby logs = primary count + 1)
for ((i=1; i<=count; i++)); do
ADD_STANDBY_CMDS+="ALTER DATABASE ADD STANDBY LOGFILE THREAD $thr GROUP $new_standby_group SIZE ${NEW_STANDBY_MB}M;\n"
new_standby_group=$((new_standby_group + 1))
done
done
fi
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
$ADD_STANDBY_CMDS
EXIT
EOSQL
# Force log switches on primary to archive old redo logs (archives all threads in RAC):contentReference[oaicite:0]{index=0}
echo " - Forcing log switch on primary to archive old redo logs..."
ARCHIVE_CMDS=""
# Perform multiple log switches (for RAC, one ALTER SYSTEM ARCHIVE LOG CURRENT covers all threads)
switch_count=$((max_old_count + 1))
for ((i=1; i<=switch_count; i++)); do
ARCHIVE_CMDS+="ALTER SYSTEM ARCHIVE LOG CURRENT;\n"
done
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
$ARCHIVE_CMDS
EXIT
EOSQL
# Drop old redo log groups on primary
echo " - Dropping old redo log groups on primary..."
for idx in "${!OLD_PRIMARY_GROUPS[@]}"; do
g=${OLD_PRIMARY_GROUPS[$idx]}
thr=${OLD_PRIMARY_THREADS[$idx]}
# Skip if group number overlaps new ones (shouldn't happen, as new_group starts above max old)
if [ "$g" -ge "$new_primary_group" ]; then
continue
fi
# Attempt to drop the redo log group
drop_output=$(sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER DATABASE DROP LOGFILE GROUP $g;
EXIT
EOSQL
)
if [ $? -ne 0 ]; then
# Drop failed
if echo "$drop_output" | grep -q -E "ORA-01623|ORA-01624"; then
echo " Redo log group $g (thread $thr) is active or current; switching log and retrying drop..."
sqlplus -s /nolog <<-EOSQL2
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER SYSTEM ARCHIVE LOG CURRENT;
EXIT
EOSQL2
drop_output2=$(sqlplus -s /nolog <<-EOSQL3
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER DATABASE DROP LOGFILE GROUP $g;
EXIT
EOSQL3
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not drop redo log group $g on primary after retry. Please remove it manually if needed."
else
echo " Dropped redo log group $g on primary (after retry)."
fi
else
echo "WARNING: Failed to drop redo log group $g on primary. Error: $(echo "$drop_output" | tr -d '\n')"
fi
else
echo " Dropped redo log group $g on primary."
fi
done
# Drop old standby redo log groups on standby
echo " - Dropping old standby redo log groups on standby..."
for idx in "${!OLD_STANDBY_GROUPS[@]}"; do
g=${OLD_STANDBY_GROUPS[$idx]}
thr=${OLD_STANDBY_THREADS[$idx]}
if [ "$g" -ge "$new_standby_group" ]; then
continue
fi
drop_output=$(sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE DROP STANDBY LOGFILE GROUP $g;
EXIT
EOSQL
)
if [ $? -ne 0 ]; then
# Drop failed
if echo "$drop_output" | grep -q "ORA-00261"; then
echo " Standby redo log group $g is being archived; clearing and retrying..."
clear_out=$(sqlplus -s /nolog <<-EOSQL2
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE CLEAR LOGFILE GROUP $g;
EXIT
EOSQL2
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not clear standby redo log group $g. Please check it manually."
else
drop_output2=$(sqlplus -s /nolog <<-EOSQL3
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE DROP STANDBY LOGFILE GROUP $g;
EXIT
EOSQL3
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not drop standby redo log group $g on standby after clearing. Please remove it manually if needed."
else
echo " Dropped standby redo log group $g on standby (after clearing)."
end of conversation
37]
## Oracle Redo Log Resizing Shell Script
```bash
#!/bin/bash
# Shell script to resize Oracle database redo log files (primary and standby).
# This script adds new redo log groups of a specified size to an Oracle primary database (and corresponding standby redo logs on the standby database),
# then drops the old redo log groups and standby redo log groups. It handles Oracle RAC by processing each thread,
# and Data Guard by performing actions on both primary and standby.
#
# Usage: resize_redo_logs.sh
# The param_file should be a text file containing five lines with the following values:
# 1. Database name (TNS alias or ORACLE_SID of the primary database)
# 2. SYS user name (usually 'SYS')
# 3. SYS password
# 4. New redo log size in MB (for primary online redo logs)
# 5. New standby redo log size in MB (for standby database)
#
# Example param_file:
# ORCL
# SYS
# MySysPassword
# 1024
# 1024
#
# Note: Ensure the standby database is accessible via a TNS alias derived from the DB name (e.g., ORCL_STBY),
# or adjust the script to use the correct connection string for the standby.
# The script assumes Oracle Managed Files (OMF) or ASM is configured so that explicit file paths are not required
# when adding log files. If OMF is not used, you may need to modify the ALTER DATABASE ADD LOGFILE commands
# to include file paths.
#
# The script will:
# - Connect to the primary database and retrieve existing redo log group numbers and threads.
# - Connect to the standby database and retrieve existing standby redo log group numbers and threads.
# - On the primary: Add new redo log groups for each thread with the specified size.
# - Force log switches on the primary to ensure old logs are archived and not active.
# - Drop old redo log groups on the primary.
# - On the standby: Stop Redo Apply (managed recovery) if active, and set STANDBY_FILE_MANAGEMENT to MANUAL.
# - Add new standby redo log groups for each thread with the specified size.
# - Drop old standby redo log groups on the standby.
# - Resume Redo Apply on the standby and set STANDBY_FILE_MANAGEMENT back to AUTO (if it was originally AUTO).
# - Handle errors during drops by retrying or continuing without exiting the script.
#
# Make sure ORACLE_HOME is set and SQL*Plus is in PATH for the user running this script.
# Also ensure the SYS password is correct and the user has SYSDBA privileges on both primary and standby.
#
# Begin script
# Validate arguments
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
PARAM_FILE="$1"
if [ ! -f "$PARAM_FILE" ]; then
echo "Parameter file '$PARAM_FILE' not found!"
exit 1
fi
# Read parameters from file (5 lines as specified)
DB_NAME=""
SYS_USER=""
SYS_PASS=""
NEW_REDO_MB=""
NEW_STANDBY_MB=""
{
IFS= read -r DB_NAME
IFS= read -r SYS_USER
IFS= read -r SYS_PASS
IFS= read -r NEW_REDO_MB
IFS= read -r NEW_STANDBY_MB
} < "$PARAM_FILE"
# Trim whitespace
DB_NAME=$(echo "$DB_NAME" | xargs)
SYS_USER=$(echo "$SYS_USER" | xargs)
SYS_PASS=$(echo "$SYS_PASS" | xargs)
NEW_REDO_MB=$(echo "$NEW_REDO_MB" | xargs)
NEW_STANDBY_MB=$(echo "$NEW_STANDBY_MB" | xargs)
# Validate parameters
if [ -z "$DB_NAME" ] || [ -z "$SYS_USER" ] || [ -z "$SYS_PASS" ] || [ -z "$NEW_REDO_MB" ] || [ -z "$NEW_STANDBY_MB" ]; then
echo "Parameter file must contain five values: DB_NAME, SYS_USER, SYS_PASS, NEW_REDO_MB, NEW_STANDBY_MB."
exit 1
fi
# Ensure sizes are numeric
if ! [[ "$NEW_REDO_MB" =~ ^[0-9]+$ ]]; then
echo "New redo log size (MB) must be a positive integer."
exit 1
fi
if ! [[ "$NEW_STANDBY_MB" =~ ^[0-9]+$ ]]; then
echo "New standby redo log size (MB) must be a positive integer."
exit 1
fi
# Connection strings (adjust if needed for standby)
PRIMARY_CONN="${SYS_USER}/${SYS_PASS}@${DB_NAME} as sysdba"
STANDBY_CONN="${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY as sysdba"
echo "Starting redo log resize procedure for database '$DB_NAME'..."
# Test connection to primary
echo " - Testing connection to primary database..."
sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
EXIT
EOSQL
if [ $? -ne 0 ]; then
echo "ERROR: Unable to connect to primary database '${DB_NAME}' as SYSDBA."
exit 1
fi
# Test connection to standby
echo " - Testing connection to standby database..."
sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
EXIT
EOSQL
if [ $? -ne 0 ]; then
echo "ERROR: Unable to connect to standby database '${DB_NAME}_STBY' as SYSDBA."
echo "Ensure the standby TNS alias is correct and the standby database is reachable."
exit 1
fi
# Retrieve current redo log groups from primary
echo " - Retrieving current redo log configuration from primary..."
primary_log_info=$(sqlplus -s /nolog <<-EOSQL
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
SELECT group# || ',' || thread# FROM v\\$log ORDER BY thread#, group#;
EXIT
EOSQL
)
if [ -z "$primary_log_info" ]; then
echo "ERROR: Failed to fetch redo log information from primary database."
exit 1
fi
# Retrieve current standby redo log groups from standby
echo " - Retrieving current standby redo log configuration from standby..."
standby_log_info=$(sqlplus -s /nolog <<-EOSQL
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
SELECT group# || ',' || thread# FROM v\\$standby_log ORDER BY thread#, group#;
EXIT
EOSQL
)
# Parse primary redo log info into arrays
OLD_PRIMARY_GROUPS=()
OLD_PRIMARY_THREADS=()
while IFS=',' read -r grp thr; do
if [ -n "$grp" ] && [ -n "$thr" ]; then
OLD_PRIMARY_GROUPS+=("$grp")
OLD_PRIMARY_THREADS+=("$thr")
fi
done <<< "$primary_log_info"
# Parse standby redo log info into arrays (if any)
OLD_STANDBY_GROUPS=()
OLD_STANDBY_THREADS=()
if [ -n "$standby_log_info" ]; then
while IFS=',' read -r grp thr; do
if [ -n "$grp" ] && [ -n "$thr" ]; then
OLD_STANDBY_GROUPS+=("$grp")
OLD_STANDBY_THREADS+=("$thr")
fi
done <<< "$standby_log_info"
fi
# Unique thread IDs on primary and standby
THREADS_ON_PRIMARY=($(printf "%s\n" "${OLD_PRIMARY_THREADS[@]}" | sort -n | uniq))
THREADS_ON_STANDBY=($(printf "%s\n" "${OLD_STANDBY_THREADS[@]}" | sort -n | uniq))
echo " Found ${#OLD_PRIMARY_GROUPS[@]} redo log groups on primary across ${#THREADS_ON_PRIMARY[@]} thread(s)."
if [ ${#OLD_STANDBY_GROUPS[@]} -gt 0 ]; then
echo " Found ${#OLD_STANDBY_GROUPS[@]} standby redo log groups on standby across ${#THREADS_ON_STANDBY[@]} thread(s)."
else
echo " No standby redo logs found on standby (physical standby should have standby logs)."
fi
# Determine starting group numbers for new logs to avoid conflicts
max_primary_group=0
for g in "${OLD_PRIMARY_GROUPS[@]}"; do
if [ "$g" -gt "$max_primary_group" ]; then
max_primary_group=$g
fi
done
max_standby_group=0
for g in "${OLD_STANDBY_GROUPS[@]}"; do
if [ "$g" -gt "$max_standby_group" ]; then
max_standby_group=$g
fi
done
new_primary_group=$((max_primary_group + 1))
# For standby, start after the larger of current primary or standby group numbers
new_standby_group=$(( (max_standby_group > max_primary_group ? max_standby_group : max_primary_group) + 1 ))
# Defer log shipping on primary (to standby) during changes
echo " - Deferring log transport to standby on primary..."
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
BEGIN
FOR dest IN (
SELECT dest_id FROM v\\$archive_dest
WHERE target='STANDBY' AND status='VALID' AND dest_state='ENABLE'
) LOOP
EXECUTE IMMEDIATE 'ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_' || dest.dest_id || '=DEFER SCOPE=MEMORY';
END LOOP;
END;
/
EXIT
EOSQL
# Check current STANDBY_FILE_MANAGEMENT mode on standby
orig_standby_mgmt=$(sqlplus -s /nolog <<-EOSQL
SET HEAD OFF FEEDBACK OFF
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
SELECT value FROM v\\$parameter WHERE name='standby_file_management';
EXIT
EOSQL
)
orig_standby_mgmt=$(echo "$orig_standby_mgmt" | xargs)
if [ -z "$orig_standby_mgmt" ]; then
echo "WARNING: Could not determine STANDBY_FILE_MANAGEMENT mode on standby."
fi
# Cancel managed recovery on standby (if active) and set STANDBY_FILE_MANAGEMENT to MANUAL
echo " - Preparing standby for changes (cancelling Redo Apply and setting STANDBY_FILE_MANAGEMENT to MANUAL)..."
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL'; -- ignore error if not active
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
ALTER SYSTEM SET standby_file_management='MANUAL' SCOPE=BOTH;
EXIT
EOSQL
# Add new redo log groups on primary
echo " - Adding new ${NEW_REDO_MB}MB redo log group(s) on primary..."
ADD_REDO_CMDS=""
max_old_count=0
for thr in "${THREADS_ON_PRIMARY[@]}"; do
count=0
for idx in "${!OLD_PRIMARY_THREADS[@]}"; do
if [ "${OLD_PRIMARY_THREADS[$idx]}" = "$thr" ]; then
count=$((count+1))
fi
done
if [ $count -gt $max_old_count ]; then
max_old_count=$count
fi
# Create same number of new log groups for this thread
for ((i=1; i<=count; i++)); do
ADD_REDO_CMDS+="ALTER DATABASE ADD LOGFILE THREAD $thr GROUP $new_primary_group SIZE ${NEW_REDO_MB}M;\n"
new_primary_group=$((new_primary_group + 1))
done
done
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
$ADD_REDO_CMDS
EXIT
EOSQL
# Add new standby redo log groups on standby
echo " - Adding new ${NEW_STANDBY_MB}MB standby redo log group(s) on standby..."
ADD_STANDBY_CMDS=""
if [ ${#OLD_STANDBY_GROUPS[@]} -gt 0 ]; then
# Use same count of standby logs per thread as existing
for thr in "${THREADS_ON_STANDBY[@]}"; do
count=0
for idx in "${!OLD_STANDBY_THREADS[@]}"; do
if [ "${OLD_STANDBY_THREADS[$idx]}" = "$thr" ]; then
count=$((count+1))
fi
done
for ((i=1; i<=count; i++)); do
ADD_STANDBY_CMDS+="ALTER DATABASE ADD STANDBY LOGFILE THREAD $thr GROUP $new_standby_group SIZE ${NEW_STANDBY_MB}M;\n"
new_standby_group=$((new_standby_group + 1))
done
done
else
# If no standby logs present, create same number as primary's online logs per thread
for thr in "${THREADS_ON_PRIMARY[@]}"; do
count=0
for idx in "${!OLD_PRIMARY_THREADS[@]}"; do
if [ "${OLD_PRIMARY_THREADS[$idx]}" = "$thr" ]; then
count=$((count+1))
fi
done
# (Optionally add +1 here to have standby logs = primary count + 1)
for ((i=1; i<=count; i++)); do
ADD_STANDBY_CMDS+="ALTER DATABASE ADD STANDBY LOGFILE THREAD $thr GROUP $new_standby_group SIZE ${NEW_STANDBY_MB}M;\n"
new_standby_group=$((new_standby_group + 1))
done
done
fi
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
$ADD_STANDBY_CMDS
EXIT
EOSQL
# Force log switches on primary to archive old redo logs (archives all threads in RAC:contentReference[oaicite:1]{index=1}】
echo " - Forcing log switch on primary to archive old redo logs..."
ARCHIVE_CMDS=""
# Perform multiple log switches (for RAC, one ALTER SYSTEM ARCHIVE LOG CURRENT covers all threads)
switch_count=$((max_old_count + 1))
for ((i=1; i<=switch_count; i++)); do
ARCHIVE_CMDS+="ALTER SYSTEM ARCHIVE LOG CURRENT;\n"
done
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
$ARCHIVE_CMDS
EXIT
EOSQL
# Drop old redo log groups on primary
echo " - Dropping old redo log groups on primary..."
for idx in "${!OLD_PRIMARY_GROUPS[@]}"; do
g=${OLD_PRIMARY_GROUPS[$idx]}
thr=${OLD_PRIMARY_THREADS[$idx]}
# Skip if group number overlaps new ones (shouldn't happen, as new_group starts above max old)
if [ "$g" -ge "$new_primary_group" ]; then
continue
fi
# Attempt to drop the redo log group
drop_output=$(sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER DATABASE DROP LOGFILE GROUP $g;
EXIT
EOSQL
)
if [ $? -ne 0 ]; then
# Drop failed
if echo "$drop_output" | grep -q -E "ORA-01623|ORA-01624"; then
echo " Redo log group $g (thread $thr) is active or current; switching log and retrying drop..."
sqlplus -s /nolog <<-EOSQL2
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER SYSTEM ARCHIVE LOG CURRENT;
EXIT
EOSQL2
drop_output2=$(sqlplus -s /nolog <<-EOSQL3
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER DATABASE DROP LOGFILE GROUP $g;
EXIT
EOSQL3
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not drop redo log group $g on primary after retry. Please remove it manually if needed."
else
echo " Dropped redo log group $g on primary (after retry)."
fi
else
echo "WARNING: Failed to drop redo log group $g on primary. Error: $(echo "$drop_output" | tr -d '\n')"
fi
else
echo " Dropped redo log group $g on primary."
fi
done
# Drop old standby redo log groups on standby
echo " - Dropping old standby redo log groups on standby..."
for idx in "${!OLD_STANDBY_GROUPS[@]}"; do
g=${OLD_STANDBY_GROUPS[$idx]}
thr=${OLD_STANDBY_THREADS[$idx]}
if [ "$g" -ge "$new_standby_group" ]; then
continue
fi
drop_output=$(sqlplus -s /nolog <<-EOSQL
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE DROP STANDBY LOGFILE GROUP $g;
EXIT
EOSQL
)
if [ $? -ne 0 ]; then
# Drop failed
if echo "$drop_output" | grep -q "ORA-00261"; then
echo " Standby redo log group $g is being archived; clearing and retrying..."
clear_out=$(sqlplus -s /nolog <<-EOSQL2
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE CLEAR LOGFILE GROUP $g;
EXIT
EOSQL2
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not clear standby redo log group $g. Please check it manually."
else
drop_output2=$(sqlplus -s /nolog <<-EOSQL3
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE DROP STANDBY LOGFILE GROUP $g;
EXIT
EOSQL3
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not drop standby redo log group $g on standby after clearing. Please remove it manually if needed."
else
echo " Dropped standby redo log group $g on standby (after clearing)."
fi
fi
elif echo "$drop_output" | grep -q -E "ORA-01623|ORA-01624"; then
echo " Standby redo log group $g is active or current; retrying drop after an extra log switch on primary..."
sqlplus -s /nolog <<-EOSQL4
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
ALTER SYSTEM ARCHIVE LOG CURRENT;
EXIT
EOSQL4
drop_output3=$(sqlplus -s /nolog <<-EOSQL5
WHENEVER SQLERROR EXIT FAILURE
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
ALTER DATABASE DROP STANDBY LOGFILE GROUP $g;
EXIT
EOSQL5
)
if [ $? -ne 0 ]; then
echo "WARNING: Could not drop standby redo log group $g on standby after retry. Please remove it manually if needed."
else
echo " Dropped standby redo log group $g on standby (after retry)."
fi
else
echo "WARNING: Failed to drop standby redo log group $g on standby. Error: $(echo "$drop_output" | tr -d '\n')"
fi
else
echo " Dropped standby redo log group $g on standby."
fi
done
# Resume managed recovery on standby and restore STANDBY_FILE_MANAGEMENT setting
echo " - Resuming Redo Apply on standby and restoring STANDBY_FILE_MANAGEMENT mode..."
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME}_STBY AS SYSDBA
$( [ "$orig_standby_mgmt" = "AUTO" ] && printf "%s" "ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH;" )
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
EXIT
EOSQL
# Re-enable log shipping from primary to standby
echo " - Re-enabling log transport to standby on primary..."
sqlplus -s /nolog <<-EOSQL
CONNECT ${SYS_USER}/${SYS_PASS}@${DB_NAME} AS SYSDBA
BEGIN
FOR dest IN (
SELECT dest_id FROM v\\$archive_dest
WHERE target='STANDBY' AND dest_state='DEFER'
) LOOP
EXECUTE IMMEDIATE 'ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_' || dest.dest_id || '=ENABLE SCOPE=MEMORY';
END LOOP;
END;
/
EXIT
EOSQL
echo "Redo log resize procedure completed."
#!/bin/bash
# Script to resize redo and standby redo logs using sqlplus & dgmgrl connections.
# Accepts input from a provided text file.
# Input file format:
# DB_NAME
# SYS_USER
# SYS_PASSWORD
# NEW_REDO_SIZE_MB
# NEW_STANDBY_REDO_SIZE_MB
INPUT_FILE=$1
if [ ! -f "$INPUT_FILE" ]; then
echo "Input file not found!"
exit 1
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"
function exec_sqlplus {
sqlplus -s /nolog <
# 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 <
