Thursday, April 17, 2025
#!/bin/bash
# Configuration Variables
############################################
ONLINE_REDO_SIZE="512M" # New size for online redo logs
STANDBY_REDO_SIZE="512M" # New size for standby redo logs
ONLINE_GROUPS_PER_THREAD=4 # Online log groups per RAC thread
STANDBY_GROUPS_PER_THREAD=5 # Standby log groups per RAC thread
MAX_RETRIES=5 # Max attempts to switch logs
RETRY_INTERVAL=5 # Seconds between retries
############################################
# Usage: ./safe_redolog_resize.sh
# Input file format:
# primary_scan primary_db
# standby_scan standby_db
# Check input parameters
if [ $# -ne 1 ]; then
echo "Usage: $0 "
exit 1
fi
INPUT_FILE="$1"
PRIMARY_HOST=$(awk 'NR==1 {print $1}' "$INPUT_FILE")
PRIMARY_DB=$(awk 'NR==1 {print $2}' "$INPUT_FILE")
STANDBY_HOST=$(awk 'NR==2 {print $1}' "$INPUT_FILE")
STANDBY_DB=$(awk 'NR==2 {print $2}' "$INPUT_FILE")
DG_USER="sys"
LOG_FILE="redolog_resize_$(date +%Y%m%d_%H%M).log"
# Validate input
if [[ -z "$PRIMARY_HOST" || -z "$PRIMARY_DB" ||
-z "$STANDBY_HOST" || -z "$STANDBY_DB" ]]; then
echo "Invalid input file format. Expected:"
echo "primary_scan primary_db"
echo "standby_scan standby_db"
exit 1
fi
# Get SYS password
read -s -p "Enter SYS password for ${PRIMARY_HOST}/${PRIMARY_DB}: " SYS_PASSWORD
echo
# Function to log messages
log() {
echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" | tee -a "$LOG_FILE"
}
# Verify database role
verify_role() {
local host=$1
local db=$2
local expected_role=$3
role=$(sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
)
if [[ "$role" != "$expected_role" ]]; then
log "Error: ${host}/${db} is not a ${expected_role} (Role: ${role})"
exit 1
fi
log "${expected_role} database confirmed"
}
# Add new redo logs (common function)
add_new_logs() {
local host=$1
local db=$2
local log_type=$3
local groups_per_thread=$4
local size=$5
log "Adding ${groups_per_thread} new ${log_type} log groups/thread (${size}) to ${db}"
sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF >> "$LOG_FILE" 2>&1
SET SERVEROUTPUT ON
DECLARE
TYPE thread_t IS TABLE OF NUMBER;
threads thread_t;
BEGIN
-- Get active threads
SELECT thread# BULK COLLECT INTO threads FROM v\$thread;
-- Add new groups
FOR i IN 1..threads.COUNT LOOP
FOR j IN 1..${groups_per_thread} LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE ADD ${log_type} LOGFILE THREAD ' ||
threads(i) || ' SIZE ${size}';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error adding group: ' || SQLERRM);
END;
END LOOP;
END LOOP;
END;
/
EXIT;
EOF
}
# Drop old redo logs (role-specific)
drop_old_logs() {
local host=$1
local db=$2
local log_type=$3
log "Dropping old ${log_type} logs from ${db}"
sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${host}:1521/${db} as sysdba" << EOF >> "$LOG_FILE" 2>&1
SET SERVEROUTPUT ON
DECLARE
TYPE log_info IS RECORD (group# NUMBER, thread# NUMBER, status VARCHAR2(15));
TYPE log_list IS TABLE OF log_info;
logs log_list;
db_role VARCHAR2(16);
BEGIN
SELECT database_role INTO db_role FROM v\$database;
-- Get existing logs that don't match new size
SELECT group#, thread#, status BULK COLLECT INTO logs
FROM v\$${log_type}_log
WHERE bytes != (SELECT bytes FROM v\$${log_type}_log WHERE ROWNUM = 1);
FOR log_rec IN 1..logs.COUNT LOOP
BEGIN
IF db_role = 'PRIMARY' AND log_type = 'LOG' THEN
-- Handle active logs on primary
IF logs(log_rec).status IN ('CURRENT', 'ACTIVE') THEN
FOR i IN 1..$MAX_RETRIES LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE THREAD ' || logs(log_rec).thread#;
DBMS_LOCK.SLEEP($RETRY_INTERVAL);
EXIT WHEN logs(log_rec).status = 'INACTIVE';
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END LOOP;
IF logs(log_rec).status != 'INACTIVE' THEN
EXECUTE IMMEDIATE 'ALTER DATABASE CLEAR LOGFILE GROUP ' || logs(log_rec).group#;
END IF;
END IF;
ELSE
-- Force clear on standby
EXECUTE IMMEDIATE 'ALTER DATABASE CLEAR LOGFILE GROUP ' || logs(log_rec).group# || ' UNARCHIVED';
END IF;
EXECUTE IMMEDIATE 'ALTER DATABASE DROP ${log_type} LOGFILE GROUP ' || logs(log_rec).group#;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping group ' || logs(log_rec).group# || ': ' || SQLERRM);
END;
END LOOP;
END;
/
EXIT;
EOF
}
# DG Broker MRP control
manage_mrp() {
local action=$1
log "${action} MRP on ${STANDBY_DB}..."
echo -e "connect ${DG_USER}/${SYS_PASSWORD}@${PRIMARY_DB}\n"\
"edit database ${STANDBY_DB} set state='APPLY-${action}';" | dgmgrl / >> "$LOG_FILE" 2>&1
if [ $? -ne 0 ]; then
log "Failed to ${action} MRP on ${STANDBY_DB}"
exit 1
fi
log "MRP ${action} successfully on ${STANDBY_DB}"
}
# Main execution
{
log "Starting Safe Redo Log Resize Operation"
verify_role "$PRIMARY_HOST" "$PRIMARY_DB" "PRIMARY"
verify_role "$STANDBY_HOST" "$STANDBY_DB" "PHYSICAL STANDBY"
# Process standby first
manage_mrp "OFF"
# Add new logs on standby
add_new_logs "$STANDBY_HOST" "$STANDBY_DB" "LOG" "$ONLINE_GROUPS_PER_THREAD" "$ONLINE_REDO_SIZE"
add_new_logs "$STANDBY_HOST" "$STANDBY_DB" "STANDBY" "$STANDBY_GROUPS_PER_THREAD" "$STANDBY_REDO_SIZE"
# Drop old logs on standby
drop_old_logs "$STANDBY_HOST" "$STANDBY_DB" "LOG"
drop_old_logs "$STANDBY_HOST" "$STANDBY_DB" "STANDBY"
manage_mrp "ON"
# Process primary
add_new_logs "$PRIMARY_HOST" "$PRIMARY_DB" "LOG" "$ONLINE_GROUPS_PER_THREAD" "$ONLINE_REDO_SIZE"
add_new_logs "$PRIMARY_HOST" "$PRIMARY_DB" "STANDBY" "$STANDBY_GROUPS_PER_THREAD" "$STANDBY_REDO_SIZE"
drop_old_logs "$PRIMARY_HOST" "$PRIMARY_DB" "LOG"
drop_old_logs "$PRIMARY_HOST" "$PRIMARY_DB" "STANDBY"
# Verification
log "Final Configuration - Primary DB:"
sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${PRIMARY_HOST}:1521/${PRIMARY_DB} as sysdba" << EOF | tee -a "$LOG_FILE"
SET LINES 200 PAGES 1000
COL group# FOR 999
COL thread# FOR 999
COL bytes FOR a15
SELECT 'Online' type, group#, thread#, bytes/1024/1024||'M' bytes, status FROM v\$log
UNION ALL
SELECT 'Standby', group#, thread#, bytes/1024/1024||'M', status FROM v\$standby_log
ORDER BY 1,2;
EXIT;
EOF
log "Final Configuration - Standby DB:"
sqlplus -S "${DG_USER}/${SYS_PASSWORD}@//${STANDBY_HOST}:1521/${STANDBY_DB} as sysdba" << EOF | tee -a "$LOG_FILE"
SET LINES 200 PAGES 1000
COL group# FOR 999
COL thread# FOR 999
COL bytes FOR a15
SELECT 'Online' type, group#, thread#, bytes/1024/1024||'M' bytes, status FROM v\$log
UNION ALL
SELECT 'Standby', group#, thread#, bytes/1024/1024||'M', status FROM v\$standby_log
ORDER BY 1,2;
EXIT;
EOF
log "Operation completed successfully"
} | tee -a "$LOG_FILE"
echo "Detailed log saved to: ${LOG_FILE}"
#####
Tim Zhang
Mon, Apr 14, 10:00 PM (3 days ago)
to me
#!/bin/bash
# === CONFIGURATION ===
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=PROD1 # Change for each node
LOG_BASE_DIR="/u01/app/oracle/oradata/PROD" # Or use ASM e.g. +DATA
THREAD_COUNT=2
NEW_GROUPS_PER_THREAD=4
REDO_LOG_SIZE_MB=500
SQL_SCRIPT="/tmp/refresh_redo.sql"
LOGFILE="/tmp/refresh_redo_$(date +%Y%m%d_%H%M%S).log"
# === Get DB Role and Max Group Number ===
get_role_and_max_group() {
read -r DB_ROLE MAX_GROUP <<<$(sqlplus -s / as sysdba < "$SQL_SCRIPT" <> "$SQL_SCRIPT"
else
echo "ALTER DATABASE ADD STANDBY LOGFILE THREAD $thread ('$log_file') SIZE ${REDO_LOG_SIZE_MB}M;" >> "$SQL_SCRIPT"
fi
group_id=$((group_id + 1))
done
done
cat >> "$SQL_SCRIPT" <<'EOF'
PROMPT === Begin switch/drop logic ===
DECLARE
v_role VARCHAR2(30);
retry NUMBER := 0;
max_retries CONSTANT NUMBER := 20;
all_inactive BOOLEAN := FALSE;
FUNCTION are_all_inactive RETURN BOOLEAN IS
cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO cnt FROM v$log
WHERE group# < &start_group AND status != 'INACTIVE';
RETURN cnt = 0;
END;
PROCEDURE force_log_switch IS
BEGIN
FOR t IN (SELECT DISTINCT thread# FROM v$log) LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
BEGIN
SELECT database_role INTO v_role FROM v$database;
IF v_role = 'PRIMARY' THEN
LOOP
EXIT WHEN are_all_inactive OR retry >= max_retries;
force_log_switch;
DBMS_LOCK.SLEEP(2);
retry := retry + 1;
END LOOP;
IF NOT are_all_inactive THEN
RAISE_APPLICATION_ERROR(-20001, 'Some old redo logs are still ACTIVE or CURRENT.');
END IF;
FOR r IN (SELECT group# FROM v$log WHERE group# < &start_group AND status = 'INACTIVE') LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE DROP LOGFILE GROUP ' || r.group#;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not drop group ' || r.group# || ': ' || SQLERRM);
END;
END LOOP;
ELSIF v_role = 'PHYSICAL STANDBY' THEN
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL';
EXCEPTION WHEN OTHERS THEN NULL;
END;
DBMS_LOCK.SLEEP(3);
FOR r IN (SELECT group# FROM v$standby_log WHERE group# < &start_group) LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE DROP STANDBY LOGFILE GROUP ' || r.group#;
EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Could not drop standby group ' || r.group# || ': ' || SQLERRM);
END;
END LOOP;
BEGIN
EXECUTE IMMEDIATE 'ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END IF;
END;
/
EXIT;
EOF
# Replace &start_group manually since sqlplus won't accept bind here
sed -i "s/&start_group/$start_group/g" "$SQL_SCRIPT"
}
# === MAIN ===
get_role_and_max_group
generate_sql
echo "Executing SQL script..."
sqlplus -s / as sysdba @"$SQL_SCRIPT" | tee "$LOGFILE"
echo "Done. Log written to $LOGFILE"
##################
# Data Guard Synchronization Check
rl_check_dg_sync() {
local primary_db="$1"
local standby_db="$2"
local sys_password="$3"
echo "Checking Data Guard Synchronization Status..."
dgmgrl -silent / <&1 | tee -a "${RL_CONF[LOG_FILE]}"
connect sys/${sys_password}@${primary_db}
show database ${standby_db}
exit
DGCMDS
# Additional SQL check for comprehensive status
sqlplus -S "sys/${sys_password}@//${primary_db} as sysdba" << EOF | tee -a "${RL_CONF[LOG_FILE]}"
SET LINES 200 PAGES 1000
COLUMN name FORMAT A30
COLUMN value FORMAT A50
COLUMN time_computed FORMAT A30
SELECT
name,
value,
TO_CHAR(time_computed, 'YYYY-MM-DD HH24:MI:SS') AS time_computed
FROM
v\$dataguard_stats
WHERE
name IN ('apply lag', 'transport lag', 'apply finish time', 'transport status');
SELECT
protection_mode,
protection_level,
remote_archive,
dataguard_broker,
guard_status
FROM
v\$database;
EXIT;
EOF
}
# Add this function to redolog_lib.sh
rl_count_logs() {
local host="$1"
local db="$2"
local sys_password="$3"
echo "Current Log Group Configuration for ${db}:"
# Count online redo logs
sqlplus -S "sys/${sys_password}@//${host}:1521/${db} as sysdba" << EOF
SET LINES 200 PAGES 1000
COLUMN "Thread#" FORMAT 999
COLUMN "Online Groups" FORMAT 999
COLUMN "Standby Groups" FORMAT 999
PROMPT Online Redo Log Groups per Thread:
SELECT thread# "Thread#", COUNT(*) "Online Groups"
FROM v\$log
GROUP BY thread#
ORDER BY thread#;
PROMPT Standby Redo Log Groups per Thread:
SELECT thread# "Thread#", COUNT(*) "Standby Groups"
FROM v\$standby_log
GROUP BY thread#
ORDER BY thread#;
EXIT;
EOF
}
#!/bin/bash
# Oracle RAC Redo Log Management Library
# Requires: sqlplus, dgmgrl
declare -A RL_CONF
rl_load_config() {
source redolog.conf || return 1
}
rl_init_logging() {
local LOG_FILE="$1"
exec > >(tee -a "$LOG_FILE") 2>&1
}
rl_verify_role() {
local host="$1"
local db="$2"
local expected_role="$3"
local sys_password="$4"
local role=$(sqlplus -S "sys/${sys_password}@//${host}:1521/${db} as sysdba" << EOF
SET HEADING OFF FEEDBACK OFF PAGESIZE 0
SELECT database_role FROM v\$database;
EXIT;
EOF
)
[[ "$role" == "$expected_role" ]] || return 1
}
rl_add_logs() {
local host="$1"
local db="$2"
local log_type="$3"
local sys_password="$4"
sqlplus -S "sys/${sys_password}@//${host}:1521/${db} as sysdba" << EOF >> ${RL_CONF[LOG_FILE]} 2>&1
SET SERVEROUTPUT ON
DECLARE
TYPE thread_t IS TABLE OF NUMBER;
threads thread_t;
BEGIN
SELECT thread# BULK COLLECT INTO threads FROM v\$thread;
FOR i IN 1..threads.COUNT LOOP
FOR j IN 1..${RL_CONF[${log_type}_GROUPS_PER_THREAD]} LOOP
EXECUTE IMMEDIATE 'ALTER DATABASE ADD ${log_type} LOGFILE THREAD ' ||
threads(i) || ' SIZE ${RL_CONF[${log_type}_REDO_SIZE]}';
END LOOP;
END LOOP;
END;
/
EXIT;
EOF
}
rl_drop_old_logs() {
local host="$1"
local db="$2"
local log_type="$3"
local sys_password="$4"
sqlplus -S "sys/${sys_password}@//${host}:1521/${db} as sysdba" << EOF >> ${RL_CONF[LOG_FILE]} 2>&1
SET SERVEROUTPUT ON
DECLARE
CURSOR c_logs IS SELECT group#, thread#, status FROM v\$${log_type}_log;
db_role VARCHAR2(16);
BEGIN
SELECT database_role INTO db_role FROM v\$database;
FOR rec IN c_logs LOOP
BEGIN
IF db_role = 'PRIMARY' AND rec.status IN ('CURRENT','ACTIVE') THEN
FOR i IN 1..${RL_CONF[MAX_RETRIES]} LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE THREAD ' || rec.thread#;
DBMS_LOCK.SLEEP(${RL_CONF[RETRY_INTERVAL]});
EXIT WHEN rec.status = 'INACTIVE';
EXCEPTION WHEN OTHERS THEN NULL;
END;
END LOOP;
END IF;
EXECUTE IMMEDIATE 'ALTER DATABASE DROP ${log_type} LOGFILE GROUP ' || rec.group#;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error dropping group ' || rec.group# || ': ' || SQLERRM);
END;
END LOOP;
END;
/
EXIT;
EOF
}
rl_manage_mrp() {
local primary_db="$1"
local standby_db="$2"
local action="$3"
local sys_password="$4"
dgmgrl -silent / <> ${RL_CONF[LOG_FILE]} 2>&1
connect sys/${sys_password}@${primary_db}
edit database ${standby_db} set state='APPLY-${action^^}';
show database ${standby_db}
exit
DGCMDS
return $?
}
# Oracle Redo Log Configuration
declare -A RL_CONF=(
[LOG_FILE]="redolog_operation.log" # Main log file
[ONLINE_REDO_SIZE]="512M" # New online redo log size
[STANDBY_REDO_SIZE]="512M" # New standby redo log size
[ONLINE_GROUPS_PER_THREAD]=4 # Online log groups per thread
[STANDBY_GROUPS_PER_THREAD]=5 # Standby log groups per thread
[MAX_RETRIES]=5 # Max log switch attempts
[RETRY_INTERVAL]=5 # Seconds between retries
)
#!/bin/bash
# Oracle RAC Redo Log Management Script
# Usage: ./main_redolog.sh
# Load configuration and library
source redolog.conf || exit 1
source redolog_lib.sh || exit 1
# Initialize environment
INPUT_FILE="$1"
[[ -f "$INPUT_FILE" ]] || { echo "Input file missing"; exit 1; }
PRIMARY_HOST=$(awk 'NR==1 {print $1}' "$INPUT_FILE")
PRIMARY_DB=$(awk 'NR==1 {print $2}' "$INPUT_FILE")
STANDBY_HOST=$(awk 'NR==2 {print $1}' "$INPUT_FILE")
STANDBY_DB=$(awk 'NR==2 {print $2}' "$INPUT_FILE")
rl_load_config || exit 1
rl_init_logging "${RL_CONF[LOG_FILE]}"
# Get SYS password
read -sp "Enter SYS password: " SYS_PASSWORD
echo
# Verify database roles
if ! rl_verify_role "$PRIMARY_HOST" "$PRIMARY_DB" "PRIMARY" "$SYS_PASSWORD"; then
echo "Primary database verification failed" | tee -a "${RL_CONF[LOG_FILE]}"
exit 1
fi
if ! rl_verify_role "$STANDBY_HOST" "$STANDBY_DB" "PHYSICAL STANDBY" "$SYS_PASSWORD"; then
echo "Standby database verification failed" | tee -a "${RL_CONF[LOG_FILE]}"
exit 1
fi
# Main operation sequence
{
echo "=== Starting Redo Log Management Operation ==="
date
# Process standby database
echo "Stopping MRP on standby..."
rl_manage_mrp "$PRIMARY_DB" "$STANDBY_DB" "OFF" "$SYS_PASSWORD" || exit 1
echo "Adding new logs to standby..."
rl_add_logs "$STANDBY_HOST" "$STANDBY_DB" "ONLINE" "$SYS_PASSWORD"
rl_add_logs "$STANDBY_HOST" "$STANDBY_DB" "STANDBY" "$SYS_PASSWORD"
echo "Removing old logs from standby..."
rl_drop_old_logs "$STANDBY_HOST" "$STANDBY_DB" "LOG" "$SYS_PASSWORD"
rl_drop_old_logs "$STANDBY_HOST" "$STANDBY_DB" "STANDBY" "$SYS_PASSWORD"
echo "Restarting MRP on standby..."
rl_manage_mrp "$PRIMARY_DB" "$STANDBY_DB" "ON" "$SYS_PASSWORD" || exit 1
# Process primary database
echo "Updating primary database logs..."
rl_add_logs "$PRIMARY_HOST" "$PRIMARY_DB" "ONLINE" "$SYS_PASSWORD"
rl_add_logs "$PRIMARY_HOST" "$PRIMARY_DB" "STANDBY" "$SYS_PASSWORD"
echo "Removing old logs from primary..."
rl_drop_old_logs "$PRIMARY_HOST" "$PRIMARY_DB" "LOG" "$SYS_PASSWORD"
rl_drop_old_logs "$PRIMARY_HOST" "$PRIMARY_DB" "STANDBY" "$SYS_PASSWORD"
echo "=== Operation Completed Successfully ==="
date
} | tee -a "${RL_CONF[LOG_FILE]}"
echo "Detailed log saved to: ${RL_CONF[LOG_FILE]}"
