Setting up backup job using shell script & expdp to backup database

How to set up backup job using shell script and expdp to backup database fully or a specific schema.

The below is the complete code that does that.

#/bin/ksh

export ORACLE_SID=$1
function failNotify 
{
    export MAIL_LIST=distribution\@example.com
    echo "DB              : $ORACLE_SID" > /tmp/failmsg.txt
    echo "Host            : `hostname`" >> /tmp/failmsg.txt
    echo "Backup Script   : ${thisScriptDir}/${thisScript}" >> /tmp/failmsg.txt
    echo "Backup Date     : `date`" >> /tmp/failmsg.txt

    if [[ -f ${dump_dir_path}/${logfilename} ]]; then
        echo "Last 10 lines from export log file below" >> /tmp/failmsg.txt
        echo >> /tmp/failmsg.txt	
        tail -10 ${dump_dir_path}/${logfilename} >> /tmp/failmsg.txt
    fi
    cat /tmp/failmsg.txt | mailx -s "Backup for $ORACLE_SID Failed" $MAIL_LIST
}

function startExpdp 
{
    export EXPDIR=$1
    echo "Export Directory .......................... $EXPDIR" >> $jobfile    

    $ORACLE_HOME/bin/expdp \'\/ as sysdba\' dumpfile=${dumpfilename}  \ 
        logfile=${logfilename} directory=${EXPDIR} compression=ALL full=y

    if [[ `cat "${dump_dir_path}/${logfilename}" | grep 'successfully completed at' | wc -l` -ne 1 ]]; then
        export exp_failed=y
        failNotify
    fi
}

# Get OS path for Dump directory
function getDumpLocation 
{
    export DB_DUMP_DIR=$1
    dump_dir_path=`${ORACLE_HOME}/bin/sqlplus -S / as sysdba <> $jobfile
       exit
    fi
    echo "Database dump directory OS path ........... $dump_dir_path" >> $jobfile
}

function checkToProceed
{
    if [[ -z $ORACLE_SID ]]; then
        echo "No Oracle SID passed. Cannot proceed" >> $jobfile
        failNotify
        exit
    elif [[ `cat ${ORATAB} | grep -v '#' | grep -v ^$ | grep -iw ${ORACLE_SID} | wc -l` -ne 1 ]]; then
        echo "Invalid Oracle SID \"${ORACLE_SID}\" passed. Cannot proceed." >> $jobfile
        failNotify
        exit
    elif [[ `ps -eaf | grep -v grep | grep -i $ORACLE_SID | grep pmon | wc -l` -ne 1 ]]; then
        echo "Database \"$ORACLE_SID\" is not running. Cannot proceed" >> $jobfile
        failNotify
        exit			 
    fi
}

# The initialialization
function mainBkp 
{
    # Log File Setting
    export jobfile="/tmp/dbBackupJob_${ORACLE_SID}.`date +"%d_%b_%y"`"

    export thisScript=`basename $0`
    export thisScriptDir=`pwd`
    echo "Backup job started ........................ `date`" >> $jobfile
    OS=`uname -s`
    case $OS in
        Linux)
            export ORATAB=/etc/oratab ;;
        Solaris)
            export ORATAB=/var/opt/oracle/oratab ;;
        *)
            echo "Error : Unsupported Operating System. Cannot proceed" >> $jobfile
            exit ;;
    esac

    checkToProceed
    export PATH=$PATH:/usr/local/bin
    export ORAENV_ASK=NO
    . /usr/local/bin/oraenv >> $jobfile
    export dumpfilename=${ORACLE_SID}_`date +"%a"`.dmp
    export logfilename=${ORACLE_SID}_`date +"%a"`.log
    getDumpLocation DATA_PUMP_DIR
    # Cleanup old dump and log file
    if [[ -f "${dump_dir_path}/${dumpfilename}" ]]; then
        rm -f "${dump_dir_path}/${logfilename}"
        rm -f "${dump_dir_path}/${dumpfilename}"
    fi
    echo "Starting export ........................... `date`" >> $jobfile
    echo "File to write dump ........................ ${dump_dir_path}/${dumpfilename}" >> $jobfile
    echo "File to write dump log .................... ${dump_dir_path}/${logfilename}" >> $jobfile
    startExpdp DATA_PUMP_DIR
    if [[ $exp_failed -ne y ]]; then
        echo "Export completed successfully ............. `date`" >> $jobfile
    else
        echo "Export failed ............................. `date`" >> $jobfile
        failNotify
    fi
    echo "Backup job completed ...................... `date`" >> $jobfile
}

mainBkp

The below is the explanation of the functions used in this schell script

failNotify

This function takes care of notifying that this script did not run successfully and sends the email to the distribution identified by shell variable "MAIL_LIST". The mail contains an elaborate list of details like server, the backup script, etc.

startExpdp

This function initiates the backup job by staring expdp. The expdp is called with full path. If the export log file does not contain the message "successfully completed at", then the function failNotify is invoked.

getDumpLocation

This function logs in to database and find the actual location of the dumpfile in the operating system. The query it uses is

select directory_path from dba_directories where directory_name = '<directory name>';

checkToProceed

Does lots of sanity chcecks like if the database is running, if the correct Oracle SID is passed, or Oracle SID is ever passed.

mainBkp

The main functions that does initialization and invokes the other functions to get the job done.

The output will look like this

Backup job started ........................ Mon Nov 30 21:35:54 IST 2015
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/11.2.0/db3 is /u03/app/oracle
Database dump directory OS path ........... /u02/exp/voice
Starting export ........................... Mon Nov 30 21:35:54 IST 2015
File to write dump ........................ /u02/exp/voice/voice_Mon.dmp
File to write dump log .................... /u02/exp/voice/voice_Mon.log
Export Directory .......................... DATA_PUMP_DIR
Export failed ............................. Mon Nov 30 21:36:00 IST 2015
Backup job completed ...................... Mon Nov 30 21:36:00 IST 2015
Comments