Automate Oracle DR Physical Standby Database (Incremental Backup and Restore)

Hey,

There was a requirement to keep the DR in sync using the incremental backup and restore. As because the bandwidth between the two datacenters was not sufficient to cope up with the LWGR and ARCH Transfer method during busy business hours.

So, I had to do this task repeatedly every day. Soon, I thought to automate this as it was becoming the daily task for synchronizing the DR database using the production incremental backups.

Here is how it goes to automate the DR Sync Using Production Incremental Backups.

Steps to Automate this using KSH Scripts and sshpass command on Linux OS.

On FSPRD: 

Scripts to be placed in the /home/oracle/script/

/home/oracle/scripts/syncdrincr.ksh
/home/oracle/scripts/getdrscn.sql

#!/bin/ksh

set -vx
##########################################################################################
# Client  : XYZ 
# Name    : SYNC DR Using Incremental Backup and Restore.
# Author  : zafrulla khan (Manager PwC SDC Bangalore)
# Date    : Jul-13-2015
# Usage   : syncdrincr.ksh
# Deployed: Jul-13-2015
# Version : 2.0
##########################################################################################


# ORACLE ENV Settings.

ORACLE_HOSTNAME=$(hostname); export ORACLE_HOSTNAME
ORACLE_UNQNAME=FSPRD; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
LOG_DATE=`date +%d%m%Y`


# Get the Lowest SCN Number from FSPRDDR Database to take the RMAN backups from FSPRD

sqlplus -s sys/<password>@FSPRDDR as sysdba @/home/oracle/scripts/getdrscn.sql > /home/oracle/scripts/drscn.log
error_count=$(cat /home/oracle/scripts/drscn.log | grep ORA- | wc -l);

# Check if there was any issues getting the SCN details from FSPRDDR

if  [ $error_count -gt 0 ]; then
mailx -s "RMAN Not able to get connected to DR Database FSPRDDR to Fetch SCN ${LOG_DATE}" -r noreply@xyz.com  emailaddr@pwc.com < /home/oracle/scripts/drscn.log
exit 1;
fi

# Remove any Newlines and extra blank lines from the results.

export DRSCN=$(cat /home/oracle/scripts/drscn.log| grep -v "^\$")
echo $DRSCN

# remove the old backups from the folder /backups/rman/standby on production database server (FSPRD)

export ORACLE_SID=FSPRD
cd /backups/rman/standby && rm -f Standby_Inc_*


# Run the RMAN Backups from the SCN which you obtained above which is avaialble in the variable DRSCN

rman target / USING $DRSCN trace=/tmp/incremental_fsprd.trc log=/tmp/incremental_fsprd.log <<EOF
run {
BACKUP INCREMENTAL FROM SCN &1 DATABASE FORMAT '/backups/rman/standby/Standby_Inc_%U' tag 'STANDBY_INC';
}
EOF

# Check if there was any issues completing the RMAN Incremental backups 

error_count=$(cat /tmp/incremental_fsprd.trc | grep ORA- | wc -l);

if  [ $error_count -gt 0 ]; then

mailx -s "RMAN Incr Level from SCN $DRSCN BACKUP FAILED FOR FSPRD ${LOG_DATE}" -r noreply@xyz.com  emailaddr@pwc.com < /tmp/incremental_fsprd.log 

else

 mailx -s "RMAN Incr Level from SCN $DRSCN BACKUP SUCCESS FOR FSPRD ${LOG_DATE}" -r noreply@xyz.com  emailaddr@pwc.com < /tmp/incremental_fsprd.log

 # Script recoverdr.ksh will execute on DR System (FSPRDDR) invoked using sshpass and ssh parameter as shown.

 sshpass -p '<password>' ssh -q -t oracle@tt.zz.yy.xx /home/oracle/scripts/recoverdr.ksh 1>&2 > /tmp/scp_transfer_dr.log

fi



getdrscn.sql

set heading off;
select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY'; 

exit