Data Guard Switchover Unix shell script

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