Data Guard Switchover Unix shell script

Posted by Gavin Soorma
On July 6, 2009
1 Comments
data guard switchover, switchover

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

Sahil on Data Guard Switchover Unix shell scriptVery helpful...appropriate
gjorgjiz96802300 on Installing the Oracle GoldenGate monitoring plug-in (13.2.1.0.0) for Cloud Control 13c Release 2Hello Gavin, This is Kent. I work with Gjorgji at Paymentus. We're testing GoldenGate, and followed your great instruction installed
Magwai on RMAN Backup and Recovery scenarios involving loss of SPFILEHello thank for very nice explanantion......could you please post the same for 12c?
Gaurav on ASH and AWR Performance Tuning Scriptsvery much useful
Bala Guntipalli on Oracle 11g Advanced CompressionReally Thanks For Posting Such a Useful and informative article. I would like to share this blog with my friends
avi on Oracle 12c Release 2 New Feature Real-Time Refreshable Materialized ViewsVery good informative article. Thanks for sharing such nice article, keep on updating such good articles. Internet Of Things(?IOT) Services
sam on Oracle 12c Release 2 New Feature Real-Time Refreshable Materialized ViewsGreat article, really very helpful content you made. Thank you, keep sharing. Quality Managment Services | Austere Technologies
austere on Oracle 12c Release 2 New Feature Real-Time Refreshable Materialized ViewsReally great blog, it's very helpful and has great knowledgeable information. Thanks for sharing, keep updating such a good informative
eric.l.yen45516600 on Oracle GoldenGate 12c Release 3 New Feature Parallel ReplicatThanks Gavin for this write up
Prashant on 12c RMAN New Feature – Cross Platform Data Transport Using Incremental BackupsHi Gavin, Thanks for the document . Can you please let us know, in this case as well we need

News

Data Guard Switchover Unix shell script

1 Comments

Leave Reply Cancel reply

Script - Latch Contention (top 5 latches)

Script - Tablespace free space and fragmentation