DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Rebuild indexes in a manner that optimizes maintenance time and does not overly gen REDO.
# Version:      2019.02.26
# Dependencies: inc_system.sh,inc_sqlplus.sh
# To get last index for schema:
#    SELECT index_name, index_type, status FROM dba_indexes WHERE owner='&v_schema' ORDER BY index_type,index_name;
# To get total indexes for schema:
#    SELECT count(*) FROM dba_indexes WHERE owner='&v_schema';

######################################
# 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
usrOraSid="dnasolo";     # ORACLE_SID
usrSchema="OSIBANK";     # Schema name. Ex: "ACTIVITYMANAGER";
usrLastIndex="ZIP5_DX1"; # Last index for this schema in alpha order. Ex: "UQ__LF_PERSON_CUSTOM__7760A435";
usrTotalToProc=50;       # Total indexes to rebuild during this script run.
usrIdxParallel=24;       # PARALLEL value to use on index rebuilds.


######################################
# Init Script Actions\Functions\Vars #
######################################
source $DIR_LIB/inc_sqlplus.sh

# Vars
sLastProcFile="$DIR_TMP/$me.LastProc.tmp";
sLastProcIndex="";

# Get Last Item Processed
if ! [[ -f $sLastProcFile ]]; then
     sLastProcIndex="TOP";
     nOK2Proc=1;
else
     sLastProcIndex=$(cat $sLastProcFile)
     nOK2Proc=0;
fi


######################################
# Start                              #
######################################
clear; log "$sSLog" "$sFullName Started"; linesep
SysVars_show
printf "ORACLE_SID:     $ORACLE_SID\n"
printf "usrSchema:      $usrSchema\n"
printf "usrLastIndex:   $usrLastIndex\n"
printf "usrTotalToProc: $usrTotalToProc\n"
printf "usrIdxParallel: $usrIdxParallel\n"
printf "sLastProcIndex: $sLastProcIndex\n"
linesep "="
sleep 5

# Create Array with Tablespace Names and Index Names for Schema
log "$sSLog" "$s"
#unset IFS
sSQL="SELECT trim(tablespace_name) || ',' || trim(index_name) FROM dba_indexes "
sSQL="$sSQL WHERE owner='$usrSchema' "
sSQL="$sSQL AND   index_type='NORMAL' "
sSQL="$sSQL AND   index_name NOT LIKE 'SYS%' "
sSQL="$sSQL ORDER BY index_name;"
###echo $sSQL; pause
aINDEXES=$(xsql "$sSQL");

# Index Rebuild Process Loop
nProcCounter=1;
for ts_idx in ${aINDEXES[

   IFS=, read -a aTS_IDX <<< "$ts_idx"
   sTablespace=${aTS_IDX[0]}
   sIndex=${aTS_IDX[1]}

   # Skip Indexes until next to process found.
   if "$sIndex" = ?; then
      nOK2Proc=1;
      printf "Found last processed: $sLastProcIndex\n\n"
      continue
   fi

   # Upon Next OK Index to Rebuild
   if nOK2Proc -eq 1 ?; then
      if $nProcCounter -le $usrTotalToProc ?; then
         # Rebuild
         sSQLIdx="ALTER INDEX $usrSchema.$sIndex REBUILD PARALLEL $usrIdxParallel ONLINE NOLOGGING TABLESPACE $sTablespace;"
         log "$sSLog" "$sSQLIdx"
         CMD=$(xsql "$sSQLIdx");

         # Reset PARALLEL
         sSQLResetPar="ALTER INDEX $usrSchema.$sIndex PARALLEL 1;"
         log "$sSLog" "$sSQLResetPar"
         CMD=$(xsql "$sSQLResetPar");

         echo "$sIndex" > $sLastProcFile
         let nProcCounter=$nProcCounter+1
      else
         printf "Max to process reached: $nProcCounter\n"
         exit
      fi
   else
      printf "Not Processing: $sIndex \n"
   fi

done

1. Housekeeping

sLastProcIndex=$(cat $sLastProcFile) if "$sLastProcIndex" = ?; then

   rm $sLastProcFile

fi

1. End #

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

@]