oracledba.help
Scripts

DBA Script

<- Scripts

#!/bin/bash
# Purpose:      Perform standard database maintenance tasks.
# Version:      2019.02.06
# Dependencies: inc_system.sh, inc_sqlplus.sh
# Usage:        dbMaint.sh <ORACLE_SID>
# Comments:     Methods used are per Oracle's best practices:
#               - Segment methods used from Doc ID 242090.1.
#               - Stats methods used from Doc ID 1337116.1.
# To Disable Scheduler Stats Job:
#   exec DBMS_AUTO_TASK_ADMIN.DISABLE
#   (client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

######################################
# 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
usrAudRetention=30;  # SYS.AUD$ retention in days. Ex value: 30
usrEmailList="michaele@sccu.com";
usrMaxRunTime=300;   # No new action if Elapse greater than this value (in minutes).
usrIdxParallel=24;

# Comma delimited list of Schema names. Ex: "SCHEMA1,SCHEMA2"
#usrSegSchemaLst="ACTIVITYMANAGER";
#usrSegSchemaLst="ACTIVITYMANAGER,AWBATCH,BI,CCM"
usrSegSchemaLst="\
ACTIVITYMANAGER,AWBATCH,BI,CCM,COREUSER,DDC,DMPDATA,DMPDATA2,MAPPER,MARKETVISION,\
OSIBANK,OSIEXTN,OSIOAS,PAYRSWAPSAVE,PORTAL,SAF,SAFUSER,SCCU,SYSMETA,WFINST";

usrScpMtPt="/mnt/mlbisilonsmb01";
usrScpDest="root@localhost:$usrScpMtPt/metadata";
usrScpPW="VMqgif2MDDwAzmU!";

optSegments=1; # Optimize Segments
optIndexes=1;  # Indexes Check
optStats=1;    # Update Stats
optAud=1;      # Sweep Audit Table
optBakMeta=1;  # Backup Oracle metadata (config files, scripts, parameters).


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

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

IFS=","
sDateStr=$(date "+%Y%m%d")
sHostname=$(hostname -s)
sMetaTmpDir=$DIR_TMP/$me;
sScpFile=$DIR_TMP/$sHostname.$me.$sDateStr.tar
rm $DIR_TMP/$me.???.spool > /dev/null 2>&1; # Sweep tmp files from incompleted script.

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

log "$sSLog" "ORACLE_SID: $ORACLE_SID"

# Optimize Segments
if [[ $optSegments -eq 1 ]]; then
   for s in $usrSegSchemaLst; do
      log "$sSLog" "[Schema: $s]"

      # Create File With List of Tables for Schema
      sSQL="SELECT owner || '.' || table_name FROM dba_tables"
      sSQL="$sSQL WHERE owner='$s' AND iot_type IS NULL"
      sSQL="$sSQL ORDER BY table_name;"
      fTables=$(xsql "$sSQL" 1)
      log "$sSLog" "Tables File Created: $fTables"

      # Run SHRINK Cmds
      while read t; do
          # Enable Row Movement
          sSQL="ALTER TABLE $t ENABLE ROW MOVEMENT;"
          log "$sSLog" "$sSQL"; CMD=$(xsql "$sSQL")

          # Shrink Table but dont Change High Water Mark (HWM)
          sSQL="ALTER TABLE $t SHRINK SPACE COMPACT;"
          log "$sSLog" "$sSQL"; CMD=$(xsql "$sSQL")

          # Reset HWM
          sSQL="ALTER TABLE $t SHRINK SPACE;"
          log "$sSLog" "$sSQL"; CMD=$(xsql "$sSQL")
      done <$fTables

      # Housekeeping
      log "$sSLog" "Optimize Segments: Housekeeping"
      rm $fTables > /dev/null 2>&1;

      # usrMaxRunTime Check
      if [[ $(elapse) -ge $usrMaxRunTime ]]; then
         log "$sSLog" "Breaking: usrMaxRunTime exceeded ($usrMaxRunTime)"
         break
      fi
   done
fi

# Indexes
if [[ $optIndexes -eq 1 ]]; then
   log "$sSLog" "Processing Indexes"
   nContinue=1;
   for s in $usrSegSchemaLst; do
       # 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='$s' "
       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");   

       #echo $aINDEXES > $DIR_TMP/idx.tmp
       #cat $DIR_TMP/idx.tmp
       #pause

       # Rebuild aINDEXES
       #IFS=","
       for ts_idx in ${aINDEXES[
}; do
             IFS=, read -a aTS_IDX <<< "$ts_idx"
             sTablespace=${aTS_IDX[0]}
             sIndex=${aTS_IDX[1]}
             # Rebuild
             #sSQLIdx="ALTER INDEX $s.$sIndex REBUILD ONLINE NOLOGGING TABLESPACE $sTablespace;"
             sSQLIdx="ALTER INDEX $s.$sIndex REBUILD PARALLEL $usrIdxParallel ONLINE NOLOGGING TABLESPACE $sTablespace;"
             log "$sSLog" "$sSQLIdx"
             CMD=$(xsql "$sSQLIdx"); 
             # Reset PARALLEL
             sSQLResetPar="ALTER INDEX $s.$sIndex PARALLEL 1;"
             log "$sSLog" "$sSQLResetPar"
             CMD=$(xsql "$sSQLResetPar");

             # usrMaxRunTime Check
             if $ -ge $usrMaxRunTime ?; then
                log "$sSLog" "Breaking: usrMaxRunTime exceeded ($usrMaxRunTime)"
                nContinue=0
                break
             fi
      done
      IFS=","
      if $nContinue -eq 0 ?; then
         break
      fi      
   done 

fi

  1. Update Stats

if $optStats -eq 1 ?; then

   # Update Dictionary Stats?
   if "$" = ?; then
      log "$sSLog" "Updating Dictionary Stats"
      sSQL="EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;"
      xsql "$sSQL" 
   fi 

   # Update User Stats
   log "$sSLog" "Update Stats"
   xsql "exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;"

fi

  1. Sweep Audit Table

if $optAud -eq 1 ?; then

   log "$sSLog" "Sweep Audit Table"
   sAudBefore=$(xsql "SELECT count(*) FROM SYS.AUD$;")
   CMD=$(xsql "DELETE FROM SYS.AUD$ WHERE ntimestamp# < sysdate-$usrAudRetention;")
   sAudAfter=$(xsql "SELECT count(*) FROM SYS.AUD$;")
   log "$sSLog" "SYS.AUD$ before: $sAudBefore"; log "$sSLog" "SYS.AUD$ after:  $sAudAfter"

fi

  1. Backup Metadata

if $optBakMeta -eq 1 ?; then

   # Sweep
   rm -rf $sMetaTmpDir; mkdir $sMetaTmpDir;

   # Linux Info
   free -m  > $sMetaTmpDir/ram.txt
   lsblk -S > $sMetaTmpDir/DiskHCtrlID.txt
   lsblk    > $sMetaTmpDir/lsblk.txt
   uname -a > $sMetaTmpDir/os.txt

   # Linux Configs
   tar -cvf $sMetaTmpDir/configs.tar $HOME/.bashrc >/dev/null 2>&1
   tar -rvf $sMetaTmpDir/configs.tar /etc/fstab >/dev/null 2>&1
   tar -rvf $sMetaTmpDir/configs.tar /etc/group >/dev/null 2>&1
   tar -rvf $sMetaTmpDir/configs.tar /etc/hosts >/dev/null 2>&1
   tar -rvf $sMetaTmpDir/configs.tar /etc/nsswitch.conf >/dev/null 2>&1
   tar -rvf $sMetaTmpDir/configs.tar /etc/passwd >/dev/null 2>&1
   tar -rvf $sMetaTmpDir/configs.tar /etc/resolv.conf >/dev/null 2>&1 

   # Scripts
   crontab -l > $sMetaTmpDir/cron.$USER.txt
   tar -cvof $sMetaTmpDir/scripts.tar $DIR_SCRIPTS/* >/dev/null 2>&1

   # Oracle TNS Files
   cp $ORACLE_HOME/network/admin/tnsnames.ora $sMetaTmpDir/
   cp $ORACLE_HOME/network/admin/sqlnet.ora $sMetaTmpDir/
   if -f ?; then
      cp $ORACLE_HOME/network/admin/listener.ora $sMetaTmpDir/
   fi

   # Oracle Instances Parameters
   sSQL="CREATE pfile='$sMetaTmpDir/init.$ORACLE_SID.ora' FROM spfile;"
   CMD=$(xsql "$sSQL")

   # SCP: MountPt Chk
   if $ ?; then
      log "$sSLog" "$usrScpMtPt Check: OK"
      # SCP: Operation
      log "$sSLog" "SCP: Operation"
      tar -cvof $sScpFile $sMetaTmpDir >/dev/null 2>&1
      sshpass -p "$usrScpPW" scp $sScpFile $usrScpDest
      if ; then
         log "$sSLog" "SCP Operation: OK"
      else
         log "$sSLog" "SCP Operation: FAILED"
         mail -s "$HOSTNAME.$sFullName $ORACLE_SID Error" "$usrEmailList" <<< "SCP Operation: FAILED"
      fi
   else
      sMsg="$usrScpMtPt Check: ERROR (not mounted) - Scp Operation aborted."
      log "$sSLog" "$sMsg"
      mail -s "$sHostname.$sFullName $ORACLE_SID Error" "$usrEmailList" <<< "$sMsg"
   fi

  # Housekeeping
  if ; then
     rm -f $sScpFile
  fi 

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

@]