DBA Script
#!/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
- 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
- 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
- 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
- End #
log "$sSLog" "$sFullName Ended [Elapse Time: $(elapse)]"; echo $(linesep "=") >> $sHLog; cat $sSLog >> $sHLog; tail -32768 $sHLog > $sHLog.tmp; mv $sHLog.tmp $sHLog
@]