#!/bin/ksh 
#                        CARIDIAN BCT
#   Object Type: Shell Script
#   Object Name: ibm_rman_arch_cleanup.ksh
#   Subsystem..: Oracle maintenance
#   Written by.: Sylvan Creach
#   Parameters.:
#   Output File:
#   External...: /usr/local/scripts/oracle/.dbowner
#                /usr/local/scripts/oracle/oracle.login [SID]
#
#   Description: Created for IBM production databases that have archiving turned on.
#                The tape backup system is not install so this script will maintain  
#                the archive logs on the system and also copy them to a Netapp drive   
#                that can be set up to perform the tape backup.
#  
#   USAGE: ibm_rman_arch_cleanup.ksh [SID] [DAYS] 
#               SID        - DB id
#               DAYS       - All file older that this will be removed
#
# ------------------------------------------------------------------------------
#           M A I N T E N A N C E   L O G
# ------------------------------------------------------------------------------
#   Date     |  Programmer     |  Description
# ------------------------------------------------------------------------------
# 12/15/04   |  Sylvan Creach  |  Initial Release
# 02/25/10   |  Sylvan Creach  |  Modified the logic to save the .arc files to a flat
#                                 file to process. Prior it was just looping through
#                                 a find command to process. I suspected when a database
#                                 was generating excessing *.arc files, this made the script
#                                 go into an endless loop.
# 05/17/11   |  Sylvan Creach  |  Change Backup_dir="/db_dumps/db_archive" to
#                              |  "/db_archive"
# 06/09/11   |  Sylvan Creach  |  Also Inserted a COMPRESS option and set it to NO.
#            |                 |  Add the logic to remove archive older that 10 days (Arc_expire)
# ------------------------------------------------------------------------------
#
# ------------------------------------------------------------------------------
# Verify Oracle Sid and Days to cleanup have been specified on the Unix command
# ------------------------------------------------------------------------------
if [[ $# -lt 2 ]]; then
   print "USAGE: $0 [SID] [DAYS] {Email Threshold} {Paging Threshold}"
   print "SID            - Database SID name"
   print "DAYS           - The number of days Old before removing (will add 1 for overhead)"
   print "               - (Will add 1 day for overhead)"
   print "Email Thres..  - Override the default 79 percent Threshold"
   print "Paging Thres.. - Override the default 90 percent Threshold"
   exit 1
fi
DB=$1
DAYS=$2
EmailT=$3 
PageT=$4 
((DAYS+=1))
# ------------------------------------------------------------------------------
# Set the COMPRESS=Y if you want files compress going to the Backup_dir
# ------------------------------------------------------------------------------
COMPRESS=N
jobrunning=$(ps -ef | grep ibm_arch_cleanup | grep -c $DB)
if [[ $jobrunning -gt 1 ]]; then
   print "$0 already running for $DB -exiting"
   exit 0
fi
typeset -i dest_cnt dest_cnt2
typeset -u usid 
usid=$DB
# ------------------------------------------------------------------------------
# Get database owner/password and set Oracle environment variables  
# ------------------------------------------------------------------------------
. /usr/local/scripts/oracle/.dbowner
. /usr/local/scripts/oracle/oracle.login $DB
# ------------------------------------------------------------------------------
# Main routine
# ------------------------------------------------------------------------------
main() {
   init
   get_arch_dir
   save_files
   remove_files
   chk_disk
   rm_old
   notify
}
# ------------------------------------------------------------------------------
# initialization function
# ------------------------------------------------------------------------------
init() {
   # Set deletion of the archive logs in days
   Arc_expire=10
   archive_list="/tmp/rman_cleanup_files$$.out"
   archive_del="/tmp/rman_cleanup_rmfiles$$.out"
   OS=$(uname -a | awk '{print $1}')
   STAMP=$(date "+%m/%d/%Y %H:%M")
   LOG=$HOME/logs/arch_cleanup_${DB}.log
   >$LOG
   LOGCMD="tee -a $LOG"
   if [[ $DAYS -le 2 ]]; then
      print "DAYS must be at least a value of 2 or greater \nSetting to 2" | $LOGCMD
      DAYS=2
   fi
   if [[ $DAYS -ge $Arc_expire ]]; then
      print "DAYS must be at less than the Archive Expire value - $Arc_expire" | $LOGCMD
      tempnum=$Arc_expire
      ((tempnum-=1))
      DAYS=$tempnum
      print "Setting to - $DAYS"
   fi
   if [[ $EmailT -gt 0 ]]; then 
       E_THOLD=$EmailT
       print "Setting Email Threshold to - $E_THOLD"
   else
       E_THOLD=79
   fi
   if [[ $PageT -gt 0 ]]; then 
       P_THOLD=$PageT
       print "Setting Paging Threshold to - $P_THOLD"
   else
       P_THOLD=90  #Paging Threshold
   fi
   mailusers="$(/usr/local/scripts/oracle/get_dba_email.ksh)"
   pageuser="oncall"
   print "$0 Script parameters:" | $LOGCMD
   print "SID  :$DB" | $LOGCMD
   print "DAYS :$DAYS\n" | $LOGCMD
}
# ------------------------------------------------------------------------------
# Parse the INIT file and get the archive directory
# ------------------------------------------------------------------------------
get_arch_dir() {
    INIT="$ORACLE_HOME/dbs/init${ORACLE_SID}.ora"
    SPFILE="$ORACLE_HOME/dbs/spfile${ORACLE_SID}.ora"
   if [[ -a $SPFILE ]]; then
       SPTMP=/tmp/spfile${ORACLE_SID}.txt
       strings $SPFILE > $SPTMP
       INIT=$SPTMP
       parse_init
   elif [[ -a $INIT ]]; then
       parse_init
   else
     echo "SCRIPT: $SN" | $LOGCMD
     echo "${STAMP} : ERROR: ORACLE Database \"${ORACLE_SID}\" init or spfile file not found" | $LOGCMD
     /usr/local/scripts/oracle/pager.ksh oncall "$ORACLE_SID - init for spfile not found"
     echo "${STAMP} : Aborting..." | $LOGCMD
     echo "ERROR SPFile not found - $SPFILE"
     abort_pgm
   fi
   Backup_dir="/db_archive/$DB"
    if [[ ! -d $Backup_dir ]]; then
       mkdir $Backup_dir
       ERR=$?
       if [[ $ERR -ne 0 ]]; then
          echo "$(date) : ERROR Could not create directory in $Backup_dir" | $LOGCMD
       fi
    fi

}
parse_init() {
typeset -l Linit_dest
 dest_cnt2=0
 dest_cnt=0
  dest_cnt=$(egrep -i LOG_ARCHIVE_DEST $INIT |wc -l|awk '{print $1}')
  arch_grep=$(egrep -i LOG_ARCHIVE_DEST $INIT)
  print "INIT=$INIT arch grep command: $arch_grep dest_cnt=$dest_cnt"
     case $dest_cnt in
      1)
        init_dest=$(egrep -i LOG_ARCHIVE_DEST $INIT | sed 's/"//g' | sed "s/'//g" |grep -v '^#' | cut -d= -f2)
        Linit_dest=$init_dest
        if [ $Linit_dest = "location" ]; then
                init_dest=$(egrep -i LOG_ARCHIVE_DEST $INIT | sed 's/"//g' | sed "s/'//g" |grep -v '^#' | cut -d= -f3)
        fi
        print "dest = $init_dest"
        ;;
      0)
        echo "${STAMP} : ERROR: ORACLE Database \"${ORACLE_SID}\" Archive Destination not found" | $LOGCMD
        echo "${STAMP} : Aborting..." | $LOGCMD
        print "Option 0"
        exit 1
        ;;
      2)
        print "Option *"
        init_dest=$(egrep -i LOG_ARCHIVE_DEST_1 $INIT | sed 's/"//g'|sed "s/'//g" | grep -v '^#' | cut -d= -f2)
        Linit_dest=$init_dest
        if [ $Linit_dest = "location" ]; then
           init_dest=$(egrep -i LOG_ARCHIVE_DEST_1 $INIT | sed 's/"//g' |sed "s/'//g"| grep -v '^#' | cut -d= -f3)
        fi
        dest_cnt2=$(egrep -i LOG_ARCHIVE_DEST_2 $INIT |wc -l)
        if [[ $dest_cnt2 -eq 1 ]]; then
           init_dest2=$(egrep -i LOG_ARCHIVE_DEST_2 $INIT | sed 's/"//g' |sed "s/'//g"| grep -v '^#' | cut -d= -f2)
           Linit_dest=$init_dest2
           if [ $Linit_dest = "location" ]; then
              init_dest2=$(egrep -i LOG_ARCHIVE_DEST_2 $INIT | sed 's/"//g' |sed "s/'//g"| grep -v '^#' | cut -d= -f3)
           fi
        fi
        print "dest 1 = $init_dest"
        print "dest 2 = $init_dest2"
        ;;
      *)
        print "invalid number of detinations - $dest_cnt"
        exit 1
        ;;
     esac
     #init_dest=$(egrep -i LOG_ARCHIVE_DEST $INIT | sed 's/"//g' | grep -v '^#' | cut -d= -f2)

  print "Init Dest = [$init_dest]"
  if [[ ! -d $init_dest ]]; then
     dir_dest=$(dirname $init_dest)
  else
     dir_dest=$init_dest
  fi
  print "Archive Directory = [$dir_dest]" | $LOGCMD
  if [[ -d $init_dest2 ]]; then
     print "Second Archive destination = [$init_dest2]" | $LOGCMD
  fi

}

# ------------------------------------------------------------------------------
# Compress archive files older than one day. Skip the most current archive file.
# ------------------------------------------------------------------------------
save_files() {
ERR=N
   sav_files1="/tmp/save_files1_${DB}.tmp"
   sav_log1="/tmp/save_log1_${DB}.tmp"
   >$sav_files1
   >$sav_log1
  find ${dir_dest} -type f -name \*.arc > $archive_list
  curr_nbr=$(cat $archive_list | wc -l) 
  if [[ $curr_nbr -gt 1 ]]; then
     curr_arch=$(ls -1t ${dir_dest}/*.arc | head -1) 
     #get_curr_archives
   cat $archive_list | while read aname
   do 
      if [[ $curr_arch != $aname ]]; then
        base_file=$(basename $aname)
        if [[ $OS = "Linux" ]]; then
           compress_name="${Backup_dir}/${base_file}.zip"
        else
           compress_name="${Backup_dir}/${base_file}.Z"
        fi
        cp_name="${Backup_dir}/${base_file}"
        if [[ ! -a $cp_name && ! -a $compress_name ]]; then
           # Save the file
           if [[ $OS = "Linux" ]]; then
              if [[ $COMPRESS = "Y" ]]; then
                 print "Compressing $compress_name" | $LOGCMD
                 echo "zip $compress_name $aname" >> $sav_files1
              else
                  print "Coping $aname to $cp_name" | $LOGCMD
                  echo "cp -p $aname $cp_name" >> $sav_files1
              fi
            else
              if [[ $COMPRESS = "Y" ]]; then
                     print "Compressing $compress_name" | $LOGCMD
                     echo "compress -c $aname > $compress_name" >> $sav_files1
              else
                     print "Coping $aname to $cp_name" | $LOGCMD
                     echo "cp -p $aname $cp_name" >> $sav_files1
              fi
            fi
        fi
      else
          print "Skipping $aname" | $LOGCMD
      fi
   done
   # Chmod sav_files1 and run it.
   chmod +x $sav_files1
   $sav_files1 > $sav_log1 2>&1
   if [[ $? -ne 0 ]]; then
      print "Saving files had error bailing application" |$LOGCMD
      cat $sav_log1 |$LOGCMD
      abort_pgm
   fi
 fi
}
# ------------------------------------------------------------------------------
# Remove *.arc files from the arch_destination that are older than DAYS 
# ------------------------------------------------------------------------------
remove_files() {
   print "Removing archive log Files from the archive destination locations found older than $DAYS old..."
   rm_archive1="/tmp/rm_archive1_${DB}.tmp"
   rm_archive2="/tmp/rm_archive2_${DB}.tmp"
   rm_log1="/tmp/rm_log1_${DB}.tmp"
   rm_log2="/tmp/rm_log2_${DB}.tmp"
   find $dir_dest -name \*.arc -mtime +${DAYS} 2>/dev/null | awk '{print "rm "$1}' > $rm_archive1
   fcnt=$(cat $rm_archive1 | wc -l)
   if [[ $fcnt -gt 0 ]]; then
      print "Removing $fcnt archives from $dir_dest" | $LOGCMD
      chmod +x $rm_archive1
      $rm_archive1 > $rm_log1 2>&1
      rc=$?
      if [[ $rc -ne 0 ]]; then
         print "Archive delete from $dir_dest had errors..." | $LOGCMD
         cat $rm_log1 | $LOGCMD
      fi
   fi
   if [[ -d $init_dest2 ]]; then
      print "NOTE: Found a second Archive destination: $init_dest2"
      find $dir_dest2 -name \*.arc -mtime +${DAYS} 2>/dev/null | awk '{print "rm "$1}' > $rm_archive2
      fcnt=$(cat $rm_archive2 | wc -l)
      if [[ $fcnt -gt 0 ]]; then
         print "Removing $fcnt archives from $dir_dest2" | $LOGCMD
         chmod +x $rm_archive2
         $rm_archive2 > $rm_log2 2>&1
         rc=$?
         if [[ $rc -ne 0 ]]; then
            print "Archive delete from $dir_dest2 had errors..." | $LOGCMD
            cat $rm_log2 | $LOGCMD
         fi
      else
         print "Warning: Second archive had no archive logs to delete"
      fi
   fi
}
# ------------------------------------------------------------------------------
# Check disk capacity.  Email if disk capacity is greater than E_THOLD.
# Page if disk capacity if greater than P_THOLD 
# ------------------------------------------------------------------------------
chk_disk() {
     cap=$(df -k $dir_dest | grep -v Filesystem | awk '{print $4}' | sed "s/%//g")
     if [[ $cap -gt $E_THOLD ]]; then
        do_trim_cleanup 4
     fi
     cap=$(df -k $dir_dest | grep -v Filesystem | awk '{print $4}' | sed "s/%//g")
     if [[ $cap -gt $P_THOLD ]]; then
        do_trim_cleanup 3
        /usr/local/scripts/oracle/pager.ksh $pageuser "$(uname -n) DIR - $dir_dest is ${cap}% full"
     fi
}
do_trim_cleanup() {
   DAYS=$1
   if [[ $DAYS -eq 4 ]]; then
      Tmsg="Email Threshold Exceeded"
   else
      Tmsg="Paging Threshold Exceeded"
   fi
   messbody="   $Tmsg \n The $dir_dest directory is more than $E_THOLD - $cap for $DB
   Performing an emergency cleanup by reducing the DAYS kept to $DAYS"
   remove_files
   newcap=$(df -k $dir_dest | grep -v Filesystem | awk '{print $4}' | sed "s/%//g")
   messbody="$messbody \nFree space after cleanup - $newcap"
   echo "$messbody" | mailx -s "ibm_arch_rman_cleanup.ksh has a space issue $DB - !! Emergency Cleanup performed !!" $mailusers
}
# ------------------------------------------------------------------------------
# This standardized the the email when the Script aborts abnormally
# ------------------------------------------------------------------------------
abort_pgm() {
       cat $LOG | mailx -s "$0 script had ERRORS" $mailusers
  exit 1
}
# ------------------------------------------------------------------------------
# Notify users of script success  
# ------------------------------------------------------------------------------
notify() {
   if [[ $curr_nbr -gt 1 ]]; then
       cat $LOG | $LOGCMD 
   else
       print "No archives needed to process" | $LOGCMD
   fi
   # Doing file cleanup...
   if [[ $DEBUG = "" ]]; then
       print "Removing temp files"
       rm -f $archive_list $archive_del
   fi
}
get_curr_archives() {
curr_arch=$(sqlplus -s <<!
$DBAUSER/$DBAPASS
set pages 0 feed off
whenever sqlerror exit 1
select name from v\$archived_log where sequence#=(select max( sequence#) from v\$archived_log where name is not null)
/
exit
!)
xcnt=$(echo $curr_arch | wc | awk '{print $2}')
if [[ $xcnt -eq 1 ]]; then
  if [[ -a $curr_arch ]]; then
      print "Current Archive File= $curr_arch"
  else
     print "ERROR occured - $curr_arch"|$LOGCMD
     exit 1
  fi
else
  save_curr_arch=$curr_arch
  curr_arch=$(echo $save_curr_arch|awk '{print $1}')
  if [[ -a $curr_arch ]]; then
      print "Current Archive File= $curr_arch"
  else
     print "ERROR occured - $curr_arch"|$LOGCMD
     exit 1
  fi
fi
}
rm_old() {
find $Backup_dir -name \*.arc -mtime +${Arc_expire} -type f -exec rm -f {} \;
}
main