1. Home
  2. Knowledge Base
  3. High Availability
  4. Data Guard Switchover Unix shell script
  1. Home
  2. Knowledge Base
  3. Scripts
  4. Data Guard Switchover Unix shell script
  1. Home
  2. Knowledge Base
  3. Unix
  4. Data Guard Switchover Unix shell script

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


The following two tabs change content below.

Gavin Soorma

Latest posts by Gavin Soorma (see all)

Updated on June 2, 2021

Was this article helpful?

Related Articles

Comments

Leave a Comment