Saturday, April 19, 2025
#!/bin/bash
# Main Redo Log Management Script
# Usage: ./main_redolog.sh
# Load configuration and library
source redolog.conf
source redolog_lib.sh
# Initialize environment
INPUT_FILE="$1"
LOG_FILE="redolog_manage_$(date +%Y%m%d_%H%M).log"
rl_init_logging "$LOG_FILE"
# Read connection details
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")
# 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"
exit 1
fi
if ! rl_verify_role "$STANDBY_HOST" "$STANDBY_DB" "PHYSICAL STANDBY" "$SYS_PASSWORD"; then
echo "Standby database verification failed"
exit 1
fi
# Main operations
(
echo "=== Starting Redo Log Management ==="
# Process standby
rl_manage_mrp "$PRIMARY_DB" "$STANDBY_DB" "OFF" "$SYS_PASSWORD"
rl_add_logs "$STANDBY_HOST" "$STANDBY_DB" "ONLINE" "$SYS_PASSWORD"
rl_add_logs "$STANDBY_HOST" "$STANDBY_DB" "STANDBY" "$SYS_PASSWORD"
rl_drop_old_logs "$STANDBY_HOST" "$STANDBY_DB" "LOG" "$SYS_PASSWORD"
rl_drop_old_logs "$STANDBY_HOST" "$STANDBY_DB" "STANDBY" "$SYS_PASSWORD"
rl_manage_mrp "$PRIMARY_DB" "$STANDBY_DB" "ON" "$SYS_PASSWORD"
# Process primary
rl_add_logs "$PRIMARY_HOST" "$PRIMARY_DB" "ONLINE" "$SYS_PASSWORD"
rl_add_logs "$PRIMARY_HOST" "$PRIMARY_DB" "STANDBY" "$SYS_PASSWORD"
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 ==="
) | tee -a "$LOG_FILE"
echo "Detailed log saved to: $LOG_FILE"
#!/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}"
