########################################################################### #!/bin/bash # #Purpose : This Script will put Phy Standby to SNAPSHOT Stanby #Usage : ./convert_standby_broker.sh [to_snapshot|to_physical] #
#Author : Chandra B ########################################################################### #Version - Initial Draft set -e #Configuration ORACLE_SID="db_sid" PRIMARY_DG_NAME="your_primary_dg_name" STANDBY_DG_NAME="db_name" DB_USER="sys" DB_PASSWORD="your_sys_password" ##DG_BROKER_CONFIG="/path/to/dg_broker_config_files" # Adjust to your DGMGRL config path EMAIL_TO="TO_EMAIL" EMAIL_SUBJECT="Oracle Snapshot Standby Conversion Alert" EMAIL_FROM="TO_EMAIL" if [[ -f /home/oracle/CB/Shell_convert_snapshot_standby.log ]]; then rm /home/oracle/CB/Shell_convert_snapshot_standby.log fi LOG_FILE="/home/oracle/CB/Shell_convert_snapshot_standby.log" #Set Oracle DB environment export ORACLE_HOME=/u01/app/rdbms/product/19c export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH export ORACLE_SID #Function to log messages log_message() { echo -e "\n$(date '+%Y-%m-%d %H:%M:%S'): 1" |tee −a "{LOG_FILE}" } #Function to send email send_email() { local message="$1" log_message "Email sent: ${message}" cat ${LOG_FILE} | mailx -s "${EMAIL_SUBJECT}" -r "${EMAIL_FROM}" "${EMAIL_TO}" } #Function to check for errors and exit check_error() { if [ $1 -ne 0 ]; then log_message "Error: $2" send_email "Error during snapshot standby conversion: $2" exit 1 fi } check_prompt() { while true do (1) prompt and read command line argument read -p "Do you want to proceed ...?? " answer (2) handle the input we were given case $answer in [yY]* ) echo -e "\nEntered YES, Continuing script........\n" break;; [nN]* ) echo -e "\nEntered NO ........" echo -e "\nexiting script ........\n" exit;; ) echo "just enter Y or N, please.";; esac done }
run_sql() { local sql="$1" sqlplus -s "/ as sysdba" <<-EOSQL SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF TIMING OFF $sql EXIT; EOSQL } #Pre-conversion checks log_message "Starting pre-conversion checks..." #Check if database is a physical standby check_standby_role() { local role STANDBY_ROLE=$(run_sql "select database_role from v$database;") echo "$STANDBY_ROLE" } STANDBY_ROLE=$(echo "${STANDBY_ROLE}" | tr -d '[:space:]') echo ${STANDBY_ROLE} if [ "${STANDBY_ROLE}" != "PHYSICALSTANDBY" ]; then log_message "Error: Database is not a physical standby (Role: ${STANDBY_ROLE})" send_email "Conversion failed: Database is not a physical standby (Role: ${STANDBY_ROLE})" exit 1 fi log_message "Check passed: Database is a physical standby." #Check if Data Guard Broker is enabled BROKER_STATUS=$(dgmgrl -silent / "show configuration;" | grep -A 1 "Configuration Status" |grep -v "Configuration Status" | awk '{print $1}') if [ "${BROKER_STATUS}" != "SUCCESS" ]; then log_message "Error: Data Guard Broker configuration is not healthy (Status: ${BROKER_STATUS})" send_email "Conversion failed: Data Guard Broker configuration is not healthy (Status: ${BROKER_STATUS})" exit 1 fi log_message "Check passed: Data Guard Broker is healthy." #Check if flashback database is enabled (required for snapshot standby) flashback_status () { SET HEADING OFF Timing off FEEDBACK OFF; FLASHBACK_STATUS=$(sqlplus -s / as sysdba <<EOF SELECT FLASHBACK_ON FROM V$DATABASE; EXIT; EOF )
FLASHBACK_STATUS=$(echo "${FLASHBACK_STATUS}" | tr -d '[:space:]') if [ "${FLASHBACK_STATUS}" != "YES" ]; then log_message "Error: Flashback database is not enabled" send_email "Conversion failed: Flashback database is not enabled on the standby" exit 1 fi
log_message "Check passed: Flashback database is enabled." } flashback_status(); #Check if standby is in sync with primary LAG_SECONDS=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF Timing off FEEDBACK OFF; SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME = 'apply lag'; EXIT; EOF ) LAG_SECONDS=$(echo "${LAG_SECONDS}" | tr -d '[:space:]') if [ -z "${LAG_SECONDS}" ] || [ "${LAG_SECONDS}" -gt 300 ]; then log_message "Error: Standby is not in sync with primary (Lag: ${LAG_SECONDS} seconds)" send_email "Conversion failed: Standby is not in sync with primary (Lag: ${LAG_SECONDS} seconds)" exit 1 fi log_message "Check passed: Standby is in sync with primary (Lag: ${LAG_SECONDS} seconds)." #Perform conversion to snapshot standby using DGMGRL check_prompt; convert_to_snapshot() { log_message "Starting conversion to snapshot standby..." ###---CCONVERT DATABASE ${STANDBY_DG_NAME} TO SNAPSHOT STANDBYYYYYYY; dgmgrl / < /tmp/dgmgrl_convert.log 2>&1 --##CONNECT "/ as sysdba"@${STANDBY_DG_NAME} show configuration; EXIT; EOF check_error $? "Failed to convert database to snapshot standby. Check /tmp/dgmgrl_convert.log for details." log_message "---[ Completed Conversion, Below is the Output ]--- " log_message "cat /tmp/dgmgrl_convert.log" #cat /tmp/dgmgrl_convert.log | tee -a ${LOG_FILE} } # Verify conversion SNAPSHOT_STATUS=$(sqlplus -s / as sysdba <<EOF SET HEADING OFF Timing off FEEDBACK OFF; SELECT DATABASE_ROLE FROM V$DATABASE; EXIT; EOF ) SNAPSHOT_STATUS=$(echo "${SNAPSHOT_STATUS}" | tr -d '[:space:]') if [ "${SNAPSHOT_STATUS}" != "SNAPSHOT_STANDBY" ]; then log_message "Error: Conversion failed. Database role is ${SNAPSHOT_STATUS}, expected SNAPSHOT_STANDBY" send_email "Conversion failed: Database role is ${SNAPSHOT_STATUS}, expected SNAPSHOT_STANDBY" exit 1 fi log_message "Conversion successful: Database is now a snapshot standby." # Verify read-write access sqlplus -s / as sysdba < /tmp/test_rw.log 2>&1 CREATE TABLE test_snapshot (id NUMBER); INSERT INTO test_snapshot VALUES (1); COMMIT; DROP TABLE test_snapshot; EXIT; EOF check_error $? "Failed to verify read-write access on snapshot standby. Check /tmp/test_rw.log for details." log_message "Check passed: Snapshot standby is read-write." # Send success email send_email "Successfully converted ${STANDBY_DG_NAME} to snapshot standby." log_message "Snapshot standby conversion completed successfully."
0 comments:
Post a Comment