The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.
It is very important that the scripts are run in the correct order and on right machine.
These scripts are based on a few customisations. The *.sh scripts are located in a directory called “/var/opt/oracle/dataguard” and there is another file called “set$ORACLE_SID” which is located under “/var/opt/oracle/cronjobs”. This script sets the environment for the individual Oracle instance like the $ORACLE_SID and the $ORACLE_HOME.
On the machine where the Primary Database is running we need to run the following scripts
$ cd /var/opt/oracle/dataguard
$ ./pre_switchover_check.sh
$ ./make_me_standby.sh
On the machine where the Standby Database is running we need to run the following script:
$ cd /var/opt/oracle/dataguard
$ ./make_me_primary.sh
After the switchover is completed, we need to run the following script on the machine where the former Primary (now new Standby) database is running:
$ cd /var/opt/oracle/dataguard
$ ./start_recovery.sh
pre_switchover_check.sh!/bin/kshif [ "$1" = "" ]thenecho ""echo "##################################################################"echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #"echo "# SCRIPT USAGE : pre_switchover_check.sh #"echo "##################################################################"echo ""exitfiORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/nullif [ $? != 0 ]thenecho ""echo "##################################################################"echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #"echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #"echo "##################################################################"echo ""exitfi. /var/opt/oracle/cronjobs/set$ORACLE_SIDDB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;echo ""echo "##################################################################"echo "# PERFORMING PRE-SWITCHOVER CHECKS FOR $DB #"echo "##################################################################"echo ""sqlplus -s /nolog |&print -p "connect / as sysdba"print -p "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off "print -p "set sqlprompt ''"read -p JUNKprint -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"read -p DBROLEprint -p "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"read -p FILESTATprint -p "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"read -p BKPSTATprint -p "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAPFROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)FROM V\$ARCHIVED_LOG)),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V\$ARCHIVED_LOG WHERE DEST_ID=2AND APPLIED='YES'AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"read -p LOGGAPprint -p "EXIT"echo "CHECKING CURRENT DATABASE ROLE..."if [ "$DBROLE" = "PHYSICAL STANDBY" ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! NOT A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY #"echo "##################################################################"echo ""exitelseecho ""echo " OK "echo ""fiecho "CHECKING LOG GAP BETWEEN PRIMARY & STANDBY..."if [ $LOGGAP -ne 0 ]thenecho ""echo "######################################################################"echo "# ERROR ! ! ! PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP #"echo "######################################################################"echo ""exitelseecho ""echo " OK "echo ""fiecho "CHECKING FILES OFFLINE OR RECOVER STATUS..."if [ $FILESTAT -ne 0 ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS #"echo "##################################################################"echo ""exitelseecho ""echo " OK "echo ""fiecho "CHECKING FILES IN BACKUP MODE..."if [ $BKPSTAT -ne 0 ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! ONE OR MORE FILE/S ARE IN BACKUP MODE #"echo "##################################################################"echo ""exitelseecho ""echo " OK "echo ""fiecho ""echo "##################################################################"echo "# ALL PRE-SWITCHOVER CHECKS SUCCEEDED... PROCEED WITH SWITCHOVER #"echo "##################################################################"echo ""make_me_standby.sh#!/bin/kshif [ "$1" = "" ]thenecho ""echo "##################################################################"echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #"echo "# SCRIPT USAGE : make_me_standby.sh #"echo "##################################################################"echo ""exitfiORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/nullif [ $? != 0 ]thenecho ""echo "##################################################################"echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #"echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #"echo "##################################################################"echo ""exitfi. /var/opt/oracle/cronjobs/set$ORACLE_SIDDB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;sqlplus -s /nolog |&print -p "connect / as sysdba"print -p "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"print -p "set sqlprompt ''"read -p JUNKprint -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"read -p DBROLEprint -p "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');"read -p FILESTATprint -p "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';"read -p BKPSTATprint -p "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAPFROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVEDFROM V\$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#)FROM V\$ARCHIVED_LOG)),(SELECT MAX(SEQUENCE#) LOG_APPLIEDFROM V\$ARCHIVED_LOG WHERE DEST_ID=2AND APPLIED='YES'AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;"read -p LOGGAPprint -p "EXIT"if [ "$DBROLE" = "PHYSICAL STANDBY" ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! NOT A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY #"echo "##################################################################"echo ""exitfiif [ $LOGGAP -ne 0 ]thenecho ""echo "######################################################################"echo "# ERROR ! ! ! PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP #"echo "######################################################################"echo ""exitfiecho "CHECKING FILES OFFLINE OR RECOVER STATUS..."if [ $FILESTAT -ne 0 ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS #"echo "##################################################################"echo ""exitfiif [ $BKPSTAT -ne 0 ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! ONE OR MORE FILE/S ARE IN BACKUP MODE #"echo "##################################################################"echo ""exitfiecho ""echo "##################################################################"echo "# ALL PRE-SWITCHOVER CHECKS SUCCEEDED... #"echo " SWITCHING $DB TO STANDBY ROLE, PLEASE CONFIRM ... "echo "##################################################################"echo ""echo ""echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"read ansif [ "$ans" = 'Y' -o "$ans" = 'y' ]thensqlplus -s /nolog < /tmp/make_me_standby.logconnect / as sysdba;startup force;alter database commit to switchover to standby with session shutdown;shutdown immediate;startup nomount;alter database mount standby database;select database_role from v\$database;EOFcat /tmp/make_me_standby.log$ORACLE_HOME/bin/lsnrctl stop PRIMARY_$DBelseecho "Quitting ....."exitfiecho ""echo "####################################################################"echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "STANDBY" #"echo "# #"echo "# On OLD STANDBY Host please run the following script: #"echo "# /var/opt/oracle/dataguard/make_me_primary.sh #"echo "####################################################################"echo ""make_me_primary.sh#!/bin/kshif [ "$1" = "" ]thenecho ""echo "##################################################################"echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #"echo "# SCRIPT USAGE : make_me_primary.sh #"echo "##################################################################"echo ""exitfiORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/nullif [ $? != 0 ]thenecho ""echo "##################################################################"echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #"echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #"echo "##################################################################"echo ""exitfi. /var/opt/oracle/cronjobs/set$ORACLE_SIDDB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;sqlplus -s /nolog |&print -p "connect / as sysdba"print -p "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"print -p "set sqlprompt ''"read -p JUNKprint -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"read -p DBROLEprint -p "exit"if [ "$DBROLE" = "PRIMARY" ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY #"echo "##################################################################"echo ""exitfiecho ""echo "##################################################################"echo "# SWITCHING $DB TO PRIMARY ROLE, PLEASE CONFIRM ... #"echo "##################################################################"echo ""echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"read ansif [ "$ans" = 'Y' -o "$ans" = 'y' ]thensqlplus -s /nolog < /tmp/make_me_primary.logconnect / as sysdba;alter database commit to switchover to primary with session shutdown;shutdown immediate;startup mount;alter system set log_archive_dest_state_2=enable scope=both;alter database set standby database to maximize performance;alter database open;select database_role from v\$database;EOFcat /tmp/make_me_primary.log$ORACLE_HOME/bin/lsnrctl start PRIMARY_$DBelseecho "Quitting ....."exitfiecho "####################################################################"echo "# PLEASE CONFIRM THAT THE STATUS OF DATABASE IS NOW "PRIMARY" #"echo "# #"echo "# On NEW STANDBY Host please run the following script: #"echo "# /var/opt/oracle/dataguard/start_recovery.sh #"echo "####################################################################"
start_recovery.sh#!/bin/kshif [ "$1" = "" ]thenecho ""echo "##################################################################"echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #"echo "# SCRIPT USAGE : start_recovery.sh #"echo "##################################################################"echo ""exitfiORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID;ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/nullif [ $? != 0 ]thenecho ""echo "##################################################################"echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #"echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #"echo "##################################################################"echo ""exitfi. /var/opt/oracle/cronjobs/set$ORACLE_SIDDB=`echo $1 |tr '[a-z]' '[A-Z]'`; export DB;sqlplus -s /nolog |&print -p "connect / as sysdba"print -p "set feedback off pause off pagesize 0 heading off verify off linesize 500 term off"print -p "set sqlprompt ''"read -p JUNKprint -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;"read -p DBROLEprint -p "exit"if [ "$DBROLE" = "PRIMARY" ]thenecho ""echo "##################################################################"echo "# ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY #"echo "##################################################################"echo ""exitfiecho ""echo "##################################################################"echo "# STARTING RECOVERY FOR $DB STANDBY, PLEASE CONFIRM ... #"echo "##################################################################"echo ""echo "Continue? - Please enter 'Y' to proceed & 'N' to exit"read ansif [ "$ans" = 'Y' -o "$ans" = 'y' ]thensqlplus -s /nolog < /tmp/start_recovery.logconnect / as sysdba;recover managed standby database disconnect;alter system set log_archive_dest_state_2=defer scope=both;EOFcat /tmp/start_recovery.logps -ef | grep ora_mrp0_$ORACLE_SID |grep -v grep > /dev/nullif [ $? != 0 ]thenecho ""echo "##################################################################"echo "# RECOVERY PROCESS NOT RUNNING... PLEASE CHECK #"echo "##################################################################"echo ""exitelseecho "##################################################################"echo "# MRP PROCESS SUCESSFULLY STARTED #"echo "##################################################################"echo ""echo "####################################################################"echo "# SWITCHOVER COMPLETE #"echo ""echo "* Perform Database Post-Switchover Checklist! "echo "####################################################################"fielseecho "Quitting ....."exitfi
0 0
