Standby redo log add
*********
#!/bin/bash
# Function to run SQL*Plus commands
run_sql() {
sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF
$1
EXIT
EOF
}
# Get the max group, max thread, and max size from the v$log and v$logfile views
redo_info=$(run_sql "SELECT MAX(l.group#), MAX(l.thread#), MAX(l.bytes/1024/1024)
FROM v\$log l, v\$logfile f
WHERE f.group# = l.group#;")
# Extract the max group, max thread, and max size values
max_group=$(echo "$redo_info" | awk '{print $1}')
max_thread=$(echo "$redo_info" | awk '{print $2}')
max_size=$(echo "$redo_info" | awk '{print $3}')
# Get the number of redo groups per thread
groups_per_thread=$(run_sql "SELECT COUNT(DISTINCT group#) FROM v\$log WHERE thread#=1;")
# Get the recovery file destination
reco_dest=$(run_sql "SHOW PARAMETER db_recovery_file_dest")
reco_dest=$(echo "$reco_dest" | awk 'NR==2 {print $3}' | xargs)
# Initialize group number
group_num=$max_group
# Loop through each thread and add standby redo logs
for ((thread=1; thread<=max_thread; thread++)); do
for ((group=1; group<=groups_per_thread; group++)); do
group_num=$((group_num + 1))
# Define the primary location for standby redo log
primary_dest="/u01/app/oracle/oradata/standby_redo${group_num}_a.log"
# Define the multiplexed location in recovery destination
multiplexed_dest="$reco_dest/standby_redo${group_num}_b.log"
# Create the ALTER DATABASE statement to add the standby redo logs with multiplexing
add_standby_log_sql="
ALTER DATABASE ADD STANDBY LOGFILE THREAD $thread GROUP $group_num
('$primary_dest', '$multiplexed_dest') SIZE ${max_size}M;"
# Print the SQL command for review
echo "Generated SQL command for Thread $thread, Group $group_num:"
echo "$add_standby_log_sql"
# Run the SQL command to add the standby redo logs
run_sql "$add_standby_log_sql"
done
done
echo "Standby redo log files added successfully."
**********
#!/bin/bash
# Function to run SQL*Plus commands
run_sql() {
sqlplus -s / as sysdba <<EOF
SET HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF
$1
EXIT
EOF
}
# Get the max group, max thread, and max size from the v$log and v$logfile views
redo_info=$(run_sql "SELECT MAX(l.group#), MAX(l.thread#), MAX(l.bytes/1024/1024) FROM v\$log l, v\$logfile f WHERE f.group# = l.group#;")
# Extract the max group, max thread, and max size values
max_group=$(echo "$redo_info" | awk '{print $1}')
max_thread=$(echo "$redo_info" | awk '{print $2}')
max_size=$(echo "$redo_info" | awk '{print $3}')
# Get the number of redo groups per thread
groups_per_thread=$(run_sql "SELECT COUNT(DISTINCT group#) FROM v\$log WHERE thread#=1;")
# Get the recovery file destination
reco_dest=$(run_sql "SHOW PARAMETER db_recovery_file_dest")
reco_dest=$(echo "$reco_dest" | awk 'NR==2 {print $3}' | xargs)
# Initialize group number
group_num=$max_group
# Loop through each thread and add standby redo logs
for ((thread=1; thread<=max_thread; thread++)); do
for ((group=1; group<=groups_per_thread; group++)); do
group_num=$((group_num + 1))
# Define the primary location for standby redo log
primary_dest="/u01/app/oracle/oradata/standby_redo${group_num}_a.log"
# Define the multiplexed location in recovery destination
multiplexed_dest="$reco_dest/standby_redo${group_num}_b.log"
# Create the ALTER DATABASE statement to add the standby redo logs with multiplexing
add_standby_log_sql="
ALTER DATABASE ADD STANDBY LOGFILE THREAD $thread GROUP $group_num
('$primary_dest', '$multiplexed_dest') SIZE ${max_size}M;
"
# Print the SQL command for review
echo "Generated SQL command for Thread $thread, Group $group_num:"
echo "$add_standby_log_sql"
# Run the SQL command to add the standby redo logs
run_sql "$add_standby_log_sql"
done
done
echo "Standby redo log files added successfully."
Comments
Post a Comment