Netezza Database refresh from prod to Dev --- Full Refresh nzbackup nzrestore

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 PMACDW  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