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

Popular posts from this blog

SQL Loader V3

Db Unix