Data Guard Switchover Unix shell script
- Posted by Gavin Soorma
- On July 6, 2009
- 1 Comments
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/ksh if [ "$1" = "" ] then echo "" echo "##################################################################" echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #" echo "# SCRIPT USAGE : pre_switchover_check.sh #" echo "##################################################################" echo "" exit fi ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID; ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null if [ $? != 0 ] then echo "" echo "##################################################################" echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #" echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #" echo "##################################################################" echo "" exit fi . /var/opt/oracle/cronjobs/set$ORACLE_SID DB=`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 JUNK print -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;" read -p DBROLE print -p "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');" read -p FILESTAT print -p "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';" read -p BKPSTAT print -p "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM 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_APPLIED FROM V\$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;" read -p LOGGAP print -p "EXIT" echo "CHECKING CURRENT DATABASE ROLE..." if [ "$DBROLE" = "PHYSICAL STANDBY" ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! NOT A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY #" echo "##################################################################" echo "" exit else echo "" echo " OK " echo "" fi echo "CHECKING LOG GAP BETWEEN PRIMARY & STANDBY..." if [ $LOGGAP -ne 0 ] then echo "" echo "######################################################################" echo "# ERROR ! ! ! PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP #" echo "######################################################################" echo "" exit else echo "" echo " OK " echo "" fi echo "CHECKING FILES OFFLINE OR RECOVER STATUS..." if [ $FILESTAT -ne 0 ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS #" echo "##################################################################" echo "" exit else echo "" echo " OK " echo "" fi echo "CHECKING FILES IN BACKUP MODE..." if [ $BKPSTAT -ne 0 ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! ONE OR MORE FILE/S ARE IN BACKUP MODE #" echo "##################################################################" echo "" exit else echo "" echo " OK " echo "" fi echo "" echo "##################################################################" echo "# ALL PRE-SWITCHOVER CHECKS SUCCEEDED... PROCEED WITH SWITCHOVER #" echo "##################################################################" echo ""
make_me_standby.sh #!/bin/ksh if [ "$1" = "" ] then echo "" echo "##################################################################" echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #" echo "# SCRIPT USAGE : make_me_standby.sh #" echo "##################################################################" echo "" exit fi ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID; ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null if [ $? != 0 ] then echo "" echo "##################################################################" echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #" echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #" echo "##################################################################" echo "" exit fi . /var/opt/oracle/cronjobs/set$ORACLE_SID DB=`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 JUNK print -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;" read -p DBROLE print -p "SELECT COUNT(FILE#) FILESTAT FROM V\$DATAFILE WHERE STATUS IN ('RECOVER', 'OFFLINE');" read -p FILESTAT print -p "SELECT COUNT(FILE#) BKPSTAT FROM V\$BACKUP WHERE STATUS = 'ACTIVE';" read -p BKPSTAT print -p "SELECT LOG_ARCHIVED-LOG_APPLIED LOGGAP FROM (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM 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_APPLIED FROM V\$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' AND RESETLOGS_CHANGE#=(SELECT MAX(RESETLOGS_CHANGE#) FROM V\$ARCHIVED_LOG)) ;" read -p LOGGAP print -p "EXIT" if [ "$DBROLE" = "PHYSICAL STANDBY" ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! NOT A PRIMARY DATABASE, PLEASE RUN IT ON PRIMARY #" echo "##################################################################" echo "" exit fi if [ $LOGGAP -ne 0 ] then echo "" echo "######################################################################" echo "# ERROR ! ! ! PRIMARY & STANDBY ARE NOT IN SYNC, LOG GAP = $LOGGAP #" echo "######################################################################" echo "" exit fi echo "CHECKING FILES OFFLINE OR RECOVER STATUS..." if [ $FILESTAT -ne 0 ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! ONE OR MORE FILE/S OFFLINE OR IN RECOVER STATUS #" echo "##################################################################" echo "" exit fi if [ $BKPSTAT -ne 0 ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! ONE OR MORE FILE/S ARE IN BACKUP MODE #" echo "##################################################################" echo "" exit fi echo "" 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 ans if [ "$ans" = 'Y' -o "$ans" = 'y' ] then sqlplus -s /nolog < /tmp/make_me_standby.log connect / 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; EOF cat /tmp/make_me_standby.log $ORACLE_HOME/bin/lsnrctl stop PRIMARY_$DB else echo "Quitting ....." exit fi echo "" 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/ksh if [ "$1" = "" ] then echo "" echo "##################################################################" echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #" echo "# SCRIPT USAGE : make_me_primary.sh #" echo "##################################################################" echo "" exit fi ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID; ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null if [ $? != 0 ] then echo "" echo "##################################################################" echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #" echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #" echo "##################################################################" echo "" exit fi . /var/opt/oracle/cronjobs/set$ORACLE_SID DB=`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 JUNK print -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;" read -p DBROLE print -p "exit" if [ "$DBROLE" = "PRIMARY" ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY #" echo "##################################################################" echo "" exit fi echo "" echo "##################################################################" echo "# SWITCHING $DB TO PRIMARY ROLE, PLEASE CONFIRM ... #" echo "##################################################################" echo "" echo "Continue? - Please enter 'Y' to proceed & 'N' to exit" read ans if [ "$ans" = 'Y' -o "$ans" = 'y' ] then sqlplus -s /nolog < /tmp/make_me_primary.log connect / 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; EOF cat /tmp/make_me_primary.log $ORACLE_HOME/bin/lsnrctl start PRIMARY_$DB else echo "Quitting ....." exit fi echo "####################################################################" 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/ksh if [ "$1" = "" ] then echo "" echo "##################################################################" echo "# PLEASE ENTER ORACLE_SID AS THE FIRST COMMAND LINE ARGUMENT #" echo "# SCRIPT USAGE : start_recovery.sh #" echo "##################################################################" echo "" exit fi ORACLE_SID=`echo $1 |tr '[A-Z]' '[a-z]'`; export ORACLE_SID; ls -l /var/opt/oracle/cronjobs/set$ORACLE_SID > /dev/null if [ $? != 0 ] then echo "" echo "##################################################################" echo "# ENVIRONMENT SETTING SET FILE NOT FOUND...!!! #" echo "# /var/opt/oracle/cronjobs/set$ORACLE_SID #" echo "##################################################################" echo "" exit fi . /var/opt/oracle/cronjobs/set$ORACLE_SID DB=`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 JUNK print -p "SELECT DATABASE_ROLE DBROLE FROM V\$DATABASE;" read -p DBROLE print -p "exit" if [ "$DBROLE" = "PRIMARY" ] then echo "" echo "##################################################################" echo "# ERROR ! ! ! NOT STANDBY DATABASE, PLEASE RUN IT ON STANDBY #" echo "##################################################################" echo "" exit fi echo "" echo "##################################################################" echo "# STARTING RECOVERY FOR $DB STANDBY, PLEASE CONFIRM ... #" echo "##################################################################" echo "" echo "Continue? - Please enter 'Y' to proceed & 'N' to exit" read ans if [ "$ans" = 'Y' -o "$ans" = 'y' ] then sqlplus -s /nolog < /tmp/start_recovery.log connect / as sysdba; recover managed standby database disconnect; alter system set log_archive_dest_state_2=defer scope=both; EOF cat /tmp/start_recovery.log ps -ef | grep ora_mrp0_$ORACLE_SID |grep -v grep > /dev/null if [ $? != 0 ] then echo "" echo "##################################################################" echo "# RECOVERY PROCESS NOT RUNNING... PLEASE CHECK #" echo "##################################################################" echo "" exit else echo "##################################################################" echo "# MRP PROCESS SUCESSFULLY STARTED #" echo "##################################################################" echo "" echo "####################################################################" echo "# SWITCHOVER COMPLETE #" echo "" echo "* Perform Database Post-Switchover Checklist! " echo "####################################################################" fi else echo "Quitting ....." exit fi
1 Comments