Thursday, April 17, 2025
#!/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
}
###
# 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
}
###
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 $?
}
# 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
}
####
# 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"
# Add after MRP restart in main operations section
echo "Verifying Data Guard Synchronization..."
rl_check_dg_sync "$PRIMARY_DB" "$STANDBY_DB" "$SYS_PASSWORD"
{
echo "=== Final Data Guard Status Check ==="
rl_check_dg_sync "$PRIMARY_DB" "$STANDBY_DB" "$SYS_PASSWORD"
} | tee -a "${RL_CONF[LOG_FILE]}"
echo "=== Operation Completed Successfully ==="
date
} | tee -a "${RL_CONF[LOG_FILE]}"
echo "Detailed log saved to: ${RL_CONF[LOG_FILE]}"
####
# 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
}
###
# Add these lines before the final echo statement
{
echo "=== Final Log Group Verification ==="
echo "Primary Database:"
rl_count_logs "$PRIMARY_HOST" "$PRIMARY_DB" "$SYS_PASSWORD"
echo "Standby Database:"
rl_count_logs "$STANDBY_HOST" "$STANDBY_DB" "$SYS_PASSWORD"
} | tee -a "${RL_CONF[LOG_FILE]}"
