DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Full Database Export  
# Version:      2018.10.01
# Dependencies: inc_system.sh

######################################
# Preamble: 2018.10.01               #
######################################
set -a; DIR_SCRIPTS="/u01/app/scripts"; source $DIR_SCRIPTS/inc_system.sh
sFullName=$(basename "$0"); me=${sFullName%.*}
sSLog="$DIR_LOGS/$me.sess.log"; sHLog="$DIR_LOGS/$me.hist.log"; > $sSLog
if [[ $MAINT_WINDOW -eq 1 ]]; then printf "Maintenance Window Detected - Exiting\n"; exit; fi

# User Vars
usrCompress="ALL";        # Oracle COMPRESS value: ALL|METADATA_ONLY|DATA_ONLY|[NONE]
usrCompAlg="HIGH";        # Oracle COMPRESSION_ALGORITHM value: [BASIC]|LOW|MEDIUM|HIGH
usrConsistant=0;          # Sets FLASHBACK_TIME=systimestamp. 0=Disabled
usrDirExp="/u02/exports"; # Dir for export .dmp Files. Ex: "/u02/sccu"
usrDirExpRet=8640;        # Minutes to retain locally: 12 hours=720, 24=1440, Wk=10080. 0=KeepForever
usrDirObj="sccu";         # Oracle DirObj used for export. SELECT * FROM dba_directories;
usrDpUn="system";         # Data Pump username.
usrDpPW="system";
usrOraSID="dnasolo";      # ORACLE_SID of database for export. Case sensitive!
usrThreads=10;            # Valid Oracle PARALLEL value. If not EE use 1.

usrEmailList="michaele@sccu.com"; # Space delimited list of email address to get warnings.

usrScpMtPt="/mnt/mlbisilonsmb01";
usrScpLimit="524288"      # Useful for ACFS MtPts (524288). ""=Disabled (not limited).
usrScpDest="root@localhost:$usrScpMtPt/exports"; # Ex: "oracle@10.4.0.167:/u04/exports". ""=Disabled
usrScpPW="VMqgif2MDDwAzmU!";
usrScpDel=1;              # If value is 1 local .tar file deleted after SCP.

######################################
# Init Script Actions\Functions\Vars #
######################################

# Vars
nExpStatus=1;
sDateStr=$(date "+%Y%m%d")
ORACLE_SID="$usrOraSID";
sParFile="$DIR_TMP/$me.$ORACLE_SID.par"

# Process CmdLine Parameters
if ! [[ -z "$1" ]]; then export ORACLE_SID="$1"; fi
if ! [[ -z "$2" ]]; then export usrThreads="$2"; fi

######################################
# Start                              #
######################################
clear; log "$sSLog" "$sFullName Started"; linesep
SysVars_show
printf "ORACLE_SID:  $ORACLE_SID\n"
linesep
printf "usrScpDest:    $usrScpDest\n"
printf "usrScpDel:     $usrScpDel\n" 
linesep "="
sleep 5

# Sweep
log "$sSLog" "Sweeeping"
if [[ $usrDirExpRet -ne 0 ]]; then
   find $usrDirExp/*.tar -type f -mmin +$usrDirExpRet -delete > /dev/null 2>&1
fi
rm $usrDirExp/$me.$ORACLE_SID.log > /dev/null 2>&1
rm $sParFile > /dev/null 2>&1

# Create .par File
log "$sSLog" "Create .par File"

cat > $sParFile <<EOF
# Created By: $sFullName
CLUSTER=N
COMPRESSION=$usrCompress
COMPRESSION_ALGORITHM=$usrCompAlg
DIRECTORY=$usrDirObj
DUMPFILE=$me.$ORACLE_SID.%U.dmp
EXCLUDE=STATISTICS
FILESIZE=32g
FULL=YES
JOB_NAME=$me
LOGFILE=$me.$ORACLE_SID.log
PARALLEL=$usrThreads
REUSE_DUMPFILES=YES
EOF

if [[ $usrConsistant -eq 1 ]]; then echo "FLASHBACK_TIME=systimestamp" >> $sParFile; fi;


# Export
log "$sSLog" "Executing expdp ($ORACLE_SID)"
$ORACLE_HOME/bin/expdp $usrDpUn/$usrDpPW PARFILE=$sParFile
if [[ $? -eq 1 ]]; then
   log "$sSLog" "Export QC Check FAILED ($?)"
   mail -s "$HOSTNAME.$sFullName $ORACLE_SID Error" "$usrEmailList" <<< "Export QC Check: FAILED"
else
   log "$sSLog" "Export QC Check: PASSED ($?)"
   nExpStatus=0
fi

# If Export OK: tar, SCP
if [[ $nExpStatus -eq 0 ]]; then

   # tar Files
   log "$sSLog" "taring Export Files"
   cd $usrDirExp
   tar -cvf $ORACLE_SID.$sDateStr.tar --exclude '*.tar' --remove-files $me.$ORACLE_SID*.dmp

   # SCP tar File
   if ! [[ -z "$usrScpDest" ]]; then
        log "$sSLog" "SCP"

        # SCP: MountPt Chk
        if [[ $(findmnt -rno SOURCE $usrScpMtPt) ]]; then
             log "$sSLog" "$usrScpMtPt Check: OK"
        else
             sMsg="$usrScpMtPt Check: ERROR - Scp Operation aborted."
             log "$sSLog" "$sMsg"
             mail -s "$HOSTNAME.$sFullName $ORACLE_SID Error" "$usrEmailList" <<< "$sMsg"
        fi

        # SCP: Operation
        if [[ $(findmnt -rno SOURCE $usrScpMtPt) ]]; then
           if [[ -z $usrScpLimit ]]; then
              log "$sSLog" "SCP: Operation"
              sshpass -p "$usrScpPW" scp $usrDirExp/$ORACLE_SID.$sDateStr.tar $usrScpDest
           else
              log "$sSLog" "SCP: Operation (limit: $usrScpLimit)"
              sshpass -p "$usrScpPW" scp -l $usrScpLimit $usrDirExp/$ORACLE_SID.$sDateStr.tar $usrScpDest
           fi
           # SC: QC
           if [[ $? -eq 0 ]]; then
              log "$sSLog" "SCP Operation: OK"
              # Delete .tar File?
              if [[ $usrScpDel -eq 1 ]]; then
                 log "$sSLog" "Deleting Local .tar File"
                 rm $usrDirExp/$ORACLE_SID.$sDateStr.tar
              fi
           else
              log "$sSLog" "SCP Operation: FAILED"
              mail -s "$HOSTNAME.$sFullName $ORACLE_SID Error" "$usrEmailList" <<< "SCP Operation: FAILED"
           fi
        fi
   fi

fi

######################################
# End                                #
######################################
log "$sSLog" "$sFullName Ended [Elapse Time: $(elapse)]";
echo $(linesep "=") >> $sHLog; cat $sSLog >> $sHLog;
tail -32768 $sHLog > $sHLog.tmp; mv $sHLog.tmp $sHLog