1. Using backup scirpt it will take backup of database specified after trigger file found and it will create a trigger file on dev to start restore P
MACDW database.
crontab Entry try every 15 min and wait for trigger file
00,15,30,45 4-10 * * 6 /export/home/nz/scripts/refresh/full_backup_refresh.sh PGCIDWSEC > /dev/null 2>&1
#############################Full Backup script#################################
#!/bin/sh
##Pass Database name with space
cd /export/home/nz
. ~/.bashrc
export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin
email_list="EDW_Support@
gmail.com"
DBA="NZRed@
gmail.com,EDW_Support@
gmail.com"
echo "Please call DBA , If It's not acknowledged by DBA" > /tmp/massage.log
HOST=`hostname`
if [ $# > 0 ]; then
i=$1
DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1|cut -d " " -f2)
if [ $i = "$DBNAME" ]; then
DBNAME=$i
DB=$DBNAME
DB=`echo "${DB,,}"| cut -c 2-`
BKTP=FULL
DB_TYPE=PROD
LOG_DIR=/export/home/nz/scripts/log
LOG=$LOG_DIR/$DBNAME_backup_`hostname`_`date +%y%m%d%H%M%S`.log
BKP_LOG_DIR=/nz/kit/log/backupsvr/
file1=/nz/$DB/triggers/$DB.done
file1a=/nz/$DB/triggers/$DB.wip
file1b=/nz/$DB/triggers/$DB.bkup_done
file1c=/nz/$DB/triggers/$DB.bkup_aborted
########checking logfile status
if [ -f "$file1" ]; then
echo " Initiate incremental backup - trigger file available" > $LOG
mv $file1 $file1a
echo "Starting Full backup of Database $DBNAME" >>$LOG
#######Backup
NOW=$(date +"%Y-%m-%d")
nzbackup -u admin -db $DBNAME -connector netbackup -connectorArgs "DATASTORE_SERVER=edp-nbu-120-ap:DATASTORE_POLICY=phx_netezza" -streams 8 -v >>$LOG
if [ $? -ne 0 ]; then
cat /tmp/massage.log | mailx -s "FAILED:Backup(Refresh) NZ $DBNAME $BKTP Database Backup on $HOST " -a $LOG $DBA
mv $file1 $file1c
else
BKPSTS=$(nzsql -qc "SELECT status FROM _v_backup_history WHERE optype='${BKTP}' AND dbname = '${DBNAME}' AND starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
BKPLOG=$(nzsql -qc "SELECT logfile FROM _v_backup_history WHERE optype='${BKTP}' AND dbname = '${DBNAME}' AND starttime = (SELECT MAX(starttime) FROM _v_backup_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
STATUS=`echo $BKPSTS|cut -d " " -f3`
LOGFILE=`echo $BKPLOG|cut -d " " -f3`
cd $BKP_LOG_DIR
BKP_DT=`echo $LOGFILE|cut -d "." -f3`
echo $BKP_DT
if [ $BKP_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then
cat $BKP_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$DB_TYPE NZ $DBNAME $BKTP Database Backup on $HOST " $email_list
/bin/touch /dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done
mv $file1a $file1b
else
cat /tmp/massage.log | mailx -s "FAILED:Backup(Refresh)NZ $DBNAME $BKTP Database Backup on $HOST " -a $BKP_LOG_DIR/$LOGFILE $DBA
mv $file1a $file1c
fi
fi
else
echo "Triiger file does not exist `date`" > $LOG
exit;
fi
else
echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE NZ $i Database Backup failed on $HOST " $DBA
fi
else
echo "Please specify database name " >>$LOG
exit 1;
fi
################################################################################
2. After we get the trigger file on dev it will hit Full restore on DEV for PMACDW database.
crontab
00,15,30,45 5-11 * * 6 /export/home/nz/scripts/refresh/full_restore_refresh.sh PMACDW >/dev/null 2>&1
###############################Full Restore script ################################
#!/bin/sh
##Pass Database name with space
cd /export/home/nz
. ~/.bashrc
export PATH=/nz/kit/bin:/nz/kit/sbin:/nz/kit/bin/adm:/bin:/usr/bin:/usr/local/bin:.:/nz/support/bin
email_list="EDW_Support@gmail.com"
DBA="NZRed@gmail.com,EDW_Support@gmail.com"
HOST=`hostname`
if [ $# > 0 ]; then
i=$1
DBNAME=$(nzsql -l|grep -w $i |awk '{ print $i}'|cut -d '|' -f1|cut -d " " -f2)
if [ $i = "$DBNAME" ]; then
DBNAME=$i
DB=$DBNAME
DB=`echo "${DB,,}"`
BKTP=FULL
DB_TYPE=DEV
LOG_DIR=/export/home/nz/scripts/log
LOG=$LOG_DIR/$DBNAME_restore_`hostname`_`date +%y%m%d%H%M%S`.log
RST_LOG_DIR=/nz/kit/log/restoresvr/
file1=/dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done
file1a=/dbbackup/refresh_trigger/bnr/.full_restore_$DBNAME.wip
file1b=/dbbackup/refresh_trigger/bnr/.full_restore_$DBNAME.done
file1c=/export/home/nz/dba/scripts/bnr/.full_restore_$DBNAME.abort
########checking logfile status
if [ -f "$file1" ]; then
##This is to capture user sessions during LQC full restoration
nzsession | grep $DBNAME > /export/home/nz/cap_session
#cat /export/home/nz/cap_session | mail -s "List of EDW-NTZ-LQC on $DBNAME user session on `date` " $email_list
#End of user tracing during full restore set2
# Kill users connected to dbs to be dropped
for killpid in `nzsession | grep $DBNAME | awk '{print $1}'`; do
echo "PID killed"
echo $killpid
nzsession abort -id ${killpid} -force
done
# Drop database $DBNAME before restoring new ones
/nz/kit/bin/nzsql < /export/home/nz/dba/scripts/bnr/dropdbs_$DBNAME.sql
if [ $? -ne 0 ]; then
echo "DATABASE $DBNAME DROP COMMAND FAILED CAUSES EXISTING REFRESH,It will try again after 15 min."|mailx -s "FAILED:$BKTP Refresh NZ $DBNAME $BKTP Database Restore on $HOST" $DBA
exit;
else
echo "DATABASE DROPPED"
fi
echo " Initiate FULL restore - trigger file available" >> $LOG
mv $file1 $file1a
echo "Starting FULL restore of Database $DBNAME" >>$LOG
#######Restore
NOW=$(date +"%Y-%m-%d")
nzrestore -db $DBNAME -sourcedb $DBNAME -connector netbackup -connectorArgs DATASTORE_SERVER=edp-nbu-120-ap -streams 8 -v -npshost 7836279-H1 -lockdb T >>$LOG
if [ $? -ne 0 ]; then
echo " FAILED:$BKTP Refresh NZ DB $DBNAME on $HOST"| mailx -s "FAILED:$BKTP Refresh NZ DB $DBNAME on $HOST " -a $LOG $DBA
mv -f $file1a $file1c
else
RSTSTS=$(nzsql -qc "SELECT status FROM _v_Restore_history WHERE optype='${BKTP}' AND dbname = '${DBNAME}' AND starttime = (SELECT MAX(starttime) FROM _v_Restore_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
RSTLOG=$(nzsql -qc "SELECT logfile FROM _v_Restore_history WHERE optype='${BKTP}' AND dbname = '${DBNAME}' AND starttime = (SELECT MAX(starttime) FROM _v_Restore_history where OPTYPE='${BKTP}' and DBNAME = '${DBNAME}');")
STATUS=`echo $RSTSTS|cut -d " " -f3`
LOGFILE=`echo $RSTLOG|cut -d " " -f3`
cd $RST_LOG_DIR
RST_DT=`echo $LOGFILE|cut -d "." -f3`
echo $RST_DT
if [ $RST_DT = $NOW ] && [ $STATUS = "COMPLETED" ]; then
cat $RST_LOG_DIR/$LOGFILE | mailx -s "SUCCESS:$BKTP Refresh NZ $DBNAME Database on $HOST " $email_list
#/bin/touch /dbbackup/refresh_trigger/bnr/.full_bkups_$DBNAME.done
mv -f $file1a $file1b
else
echo "FAILED:$BKTP NZ Refresh DB $DBNAME on $HOST " | mailx -s "FAILED:$BKTP NZ Refresh DB $DBNAME on $HOST " -a $RST_LOG_DIR/$LOGFILE $DBA
mv -f $file1a $file1c
fi
fi
else
echo "Triiger file does not exist `date`" > $LOG
exit;
fi
else
echo "$i Database does not exist on $HOST"| mailx -s "Notification:$DB_TYPE NZ $i Database Restore failed on $HOST " $DBA
fi
else
echo "Please specify database name " >>$LOG
exit 1;
fi
