代码改变世界

oracle表空间自动扩容[shell]

  明朝散发  阅读(60)  评论(0)  编辑  收藏  举报 
#############################################################################################
# Version 1 20191021
# Manual
# The purpose of this script is to automaticall manage tablespaces without human intervention
# Version 1 capabilites
# Autonomously manage non TEMP non UNDO tablespaces
# 1 parameter Tablespace Threshold    : Define at utilzation % action is taken
# 2 parameter Target Utilization      : Define target utilizaion % as safe
# 3 parameter Mount Point utilization where warning to increase storage is mailed out
# 4 parameter Mount Point utlization where autonomous script will not add space till OS utilization is reduced by adding storage
# 5 parameter BTS support Email
# Parameters to be configured
#       CFG_NOTIFICATION_EMAIL  Emails to notify. ( Emails are case sensitive )
#       CFG_MAX_DB_FILE_SIZE_KB=16000000
#       CFG_LOG_DIR=$HOME/dba/temp
#       CFG_LOG_FILE=autonomous.log
#       CFG_LOG_FILE_DAILY=autonomous_daily.log
#
#
###############################################################################################


#!/usr/bin/ksh
#####################################
# Autonomous Tablespace Management
#####################################
# Function Main
# Read /etc/oratab
# Get DB SID
# Get HOME
# Get DB Version
# Get status Y databases
############################
HOSTNAME=`hostname`
DATE_HH=`date +"%H"`
DATE_MM=`date +"%M"`
####################################################################
# THRESHOLD     Define Usage % before auto tablespace management kicks in   ( 84 b4 aleart kicks in )
# UTIL_TARGET   Defines what is the Prefered Health utilization             ( 80)
######################################################################
clear
if [ $# -lt 5 ] ; then
        echo " Configure CFG_* PARAMETER B4 1st RUN "
        echo " This script accepts 5 parameters and optionally additional 2 emails"
        echo " Calulations are ALL in KB where applicable "
        echo " 1) Treshold in % Tablespace Utilization trigger point"
        echo " 2) Target_utilization in % Tablespace mgt to bring utiliation to target"
        echo " 3) OS used threshold which will trigger alert to increase storage "
        echo " 4) OS used limit which prevents auto extend "
        echo " 5) EMAIL GROUP to notify of activities by script "
        echo " Usage Example  :   AutoTSMgt.sh 92 85 85 95 "
        exit 1
fi
THRESHOLD=`echo $1`
UTIL_TARGET=`echo $2`
#16G =16000000 KB
CFG_MAX_DB_FILE_SIZE_KB=16000000
CFG_LOG_DIR=$HOME/dba/temp
CFG_LOG_FILE=autonomous.log
CFG_LOG_FILE_DAILY=autonomous_daily.log
CFG_NOTIFICATION_EMAIL_1=wow@163.com
CFG_NOTIFICATION_EMAIL_3=`echo $6`
CFG_NOTIFICATION_EMAIL_4=`echo $7`

#### SET as 16777216  KB  16GB for production  UAT
VAR_OS_USED_SPACE_THRESHOLD=`echo $3`
VAR_OS_USED_SPACE_LIMIT=`echo $4`
CFG_NOTIFICATION_EMAIL_2=`echo $5`
VAR_OS_TYPE=`uname | tr "[:upper:]" "[:lower:]"`
echo $VAR_OS_TYPE


#########################
#Checking for SQL errors
#########################
CheckSQL()
{
         cat /tmp/error.log | grep ORA
        if [ $? -eq 0 ]; then
                VAR_ERROR_MESSAGE=`cat /tmp/error.log | grep ORA `
                if [ "$VAR_ERROR_MESSAGE" == "ORA-01034: ORACLE not available" ]; then
                        echo $VAR_ERROR_MESSAGE
                        LogMessage "$VAR_ERROR_MESSAGE "
                        echo "Skipping database $ORACLE_SID, database should be up based on oratab entry. Please investigate"
                        LogMessage "Skipping database $ORACLE_SID, database should be up based on oratab entry. Please investigate"
                        FLAG_SKIPDB=true
                else
                        LogMessage "$VAR_ERROR_MESSAGE "
                        LogMessage "Exiting Autonomous Management please resolve above error"

                        echo "$VAR_ERROR_MESSAGE "
                        echo "Exiting Autonomous Management please resolve above error"
                        cat /dev/null > /tmp/error.log
                        MailOut
                        echo $VAR_ERROR_MESSAGE
                        exit 1
                fi
        fi
}

DBModeCheck()
{
db_mode=`sqlplus -s "/ as sysdba" <<EOF
set heading off feedback off verify off
select open_mode from v\\$database;
exit
EOF`
echo "$ORACLE_SID is $db_mode"
if db_mode='READ WRITE'; then
return 0
else
return 1
fi
}

##########################
#Get block size of db
##########################
GetDbBlockSize()
{
        DB_BLOCK_SIZE=`$ORACLE_HOME/bin/sqlplus -S /nolog << !  | tee /tmp/error.log
        connect / as sysdba
        whenever sqlerror exit sql.sqlcode
        set linesize 200
        set pagesize 0
        set feedback off
        set trimspool on
        select value from v\\$parameter where NAME='db_block_size';
        exit;
!`
        CheckSQL
        #LogMessage " Database block size is $DB_BLOCK_SIZE bytes"
}
##################
# Mail Function
#################
MailOut()
{
        #VAR_MESSAGE=$1
        #echo this is the Message to be mailed  "$VAR_MESSAGE"
        VAR_MAILX=`which mailx`
        echo $VAR_MAILX
        LogMessage "Email notification sent"
        echo "Email notification sent"

        #echo `$VAR_MAILX -s "AUTO TABLESPACE MANAGEMENT" $CFG_NOTIFICATION_EMAIL < $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY`
        echo `$VAR_MAILX -s "Autonomous Tablespace Management"  $CFG_NOTIFICATION_EMAIL_1 $CFG_NOTIFICATION_EMAIL_2 $CFG_NOTIFICATION_EMAIL_3 $CFG_NOTIFICATION_EMAIL_4 < $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY`
        #$SCRIPT_DIR/mailx.sh "Alert: $HOSTNAME ${ORACLE_SID} Error in autonomous.log" $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY
        #cat $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY >> $CFG_LOG_DIR/$CFG_LOG_FILE
        cat /dev/null > $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY

}
####################
#LOG WRITER FUNCTION
####################
LogMessage()
{
        VAR_MESSAGE=$1
        echo `date "+%D +%T"` $VAR_MESSAGE >> $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY
        echo `date "+%D +%T"` $VAR_MESSAGE >> $CFG_LOG_DIR/$CFG_LOG_FILE
}

###################
#ADD RESIZE FUNTION
##################
AddResize()
{
        VAR_LIST_OF_TS=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
        connect / as sysdba
        set linesize 200
        set pagesize 0
        set feedback off
        set trimspool on
        select tablespace_name from dba_tablespace_usage_metrics where used_percent >= $THRESHOLD and tablespace_name not like ('TEMP%')  and tablespace_name not like('UNDO%');
        exit;
!`
        CheckSQL


        for TABLESPACE in $VAR_LIST_OF_TS ; do
                #echo "CRURRENTLY WORKING ON TABLESPACE $TABLESPACE "

                VAR_KBYTES_TO_ADD=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                connect / as sysdba
                set linesize 200
                set pagesize 0
                set feedback off
                set trimspool on
                select round(round(round(a.used_percent,0)/$UTIL_TARGET*a.tablespace_size*b.block_size/1024,0)-floor(a.tablespace_size*b.block_size/1024),0) from dba_tablespace_usage_metrics a,dba_tablespaces b  where a.tablespace_name=b.tablespace_name and a.tablespace_name='$TABLESPACE';
                exit;
!`
                CheckSQL
                echo ""
                LogMessage " $VAR_KBYTES_TO_ADD KB needs to be added into tablespace $TABLESPACE"
                echo       " $VAR_KBYTES_TO_ADD KB needs to be added into tablespace $TABLESPACE"
                VAR_DATA_FILE_LIST=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                connect / as sysdba
                set linesize 200
                set pagesize 0
                set feedback off
                set trimspool on
                select file_name from dba_data_files where tablespace_name='$TABLESPACE' and bytes/1024 < $CFG_MAX_DB_FILE_SIZE_KB order by file_name;
                exit;
!`
                CheckSQL
                #### LOOP THROUGH ALL FILES IN TABLESAPCE
                #### GET SIZE OF FILE
                #####echo $VAR_DATA_FILE_LIST
                for FILENAME in $VAR_DATA_FILE_LIST ; do
                        #echo $VAR_KBYTES_TO_ADD
                        #echo $FILENAME
                        #echo $VAR_DATA_FILE_LIST
                        VAR_FILE_SIZE_KB=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                        connect / as sysdba
                        set linesize 200
                        set pagesize 0
                        set feedback off
                        set trimspool on
                        select bytes/1024 from dba_data_files where file_name='$FILENAME';
                        exit;
!`
                        #echo CURRENT_FILE SIZE is $VAR_FILE_SIZE_KB
                        #echo $VAR_KBYTES_TO_ADD KB TO BE ADDED
                        let VAR_FINAL_SINGLE_FILE_SIZE="$VAR_FILE_SIZE_KB + $VAR_KBYTES_TO_ADD"
                        #echo "$VAR_FINAL_SINGLE_FILE_SIZE equal $VAR_FILE_SIZE_KB plus $VAR_KBYTES_TO_ADD"
                        #echo ""
                        ### IF amout ot add is eq 0 or less that 0 no conenction to db and break out of loop
                        if [  $VAR_KBYTES_TO_ADD -gt 0 ]; then
                                if [ $VAR_FINAL_SINGLE_FILE_SIZE -lt $CFG_MAX_DB_FILE_SIZE_KB ]; then
                                        echo SIZE OF VAR_FINAL_SINGLE_FILE_SIZE $VAR_FINAL_SINGLE_FILE_SIZE IS LESS THAN CFG_MAX_DB_FILE_SIZE_KB $CFG_MAX_DB_FILE_SIZE_KB
                                        #echo "RESIZING DATAFIE TO $VAR_FINAL_SINGLE_FILE_SIZE"
                                        #LogMessage "RESIZING DATAFIE TO $VAR_FINAL_SINGLE_FILE_SIZE"
                                        #LogMessage "RESIZING DATAFIE TO $VAR_FINAL_SINGLE_FILE_SIZE"
                                        #echo ""
                                        `$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                                        connect / as sysdba
                                        set linesize 200 pagesize 0 feedback off trimspool on
                                        define OS_IN_NUM=$VAR_FINAL_SINGLE_FILE_SIZE
                                        column OS_OUT_NUM new_value ROUNDUP_IN_NUM
                                        SELECT CEIL(&OS_IN_NUM / 1024) as OS_OUT_NUM FROM DUAL;
                                        alter database datafile '$FILENAME' resize &ROUNDUP_IN_NUM M;
                                        exit;
!`
                                        CheckSQL
                                        LogMessage " $ORACLE_SID $TABLESPACE $FILENAME resized to $VAR_FINAL_SINGLE_FILE_SIZE K"
                                        echo       " $ORACLE_SID $TABLESPACE $FILENAME resized to $VAR_FINAL_SINGLE_FILE_SIZE K"

                                        VAR_KBYTES_TO_ADD=0

                                elif [  $VAR_FINAL_SINGLE_FILE_SIZE -gt $CFG_MAX_DB_FILE_SIZE_KB ]; then
                                        echo SIZE TO ADD EXCEEDS VAR MAX DB FILE SIZE KB
                                        echo RESIZIZING $FILE TO $CFG_MAX_DB_FILE_SIZE_KB KB
                                        `$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                                        connect / as sysdba
                                        set linesize 200 pagesize 0 feedback off trimspool on
                                        define OS_IN_NUM=$CFG_MAX_DB_FILE_SIZE_KB
                                        column OS_OUT_NUM new_value ROUNDUP_IN_NUM
                                        SELECT CEIL(&OS_IN_NUM / 1024) as OS_OUT_NUM FROM DUAL;
                                        alter database datafile '$FILENAME' resize &ROUNDUP_IN_NUM M;
                                        exit;
!`
                                        CheckSQL
                                        LogMessage " $ORACLE_SID $TABLESPACE $FILENAME resized to $CFG_MAX_DB_FILE_SIZE_KB K"
                                        echo       " $ORACLE_SID $TABLESPACE $FILENAME resized to $CFG_MAX_DB_FILE_SIZE_KB K"

                                        let VAR_KBYTES_TO_ADD="$VAR_KBYTES_TO_ADD - (CFG_MAX_DB_FILE_SIZE_KB - $VAR_FILE_SIZE_KB)"
                                        echo remaining KBYTES TO ADD $VAR_KBYTES_TO_ADD
                                fi
                        elif [ $VAR_FINAL_SINGLE_FILE_SIZE -le 0 ]; then
                                break
                        fi
                        #let CTR_FILES=CTR_FILES+1
                        #TABLESPACE=$TABLESPACE
                done
                ###BBBB
                CTR_FILES=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                        connect / as sysdba
                        set linesize 200 pagesize 0 feedback off trim on
                        select count(*) from dba_data_files where tablespace_name='$TABLESPACE';
                        exit;
!`
                        CheckSQL

                #echo TOTAL NUMBER OF FILES $CTR_FILES

                #### ADD DATAFILE TO TABLESPACE  if conditions met.
                TABLESPACE=$(echo $TABLESPACE|tr "[:upper:]" "[:lower:]")
                echo $TABLESPACE
                if [[ $VAR_KBYTES_TO_ADD -gt 0 && $VAR_KBYTES_TO_ADD -lt $CFG_MAX_DB_FILE_SIZE_KB ]]; then
                        echo ADDING DATAFILE OF SIZE $VAR_KBYTES_TO_ADD
                        let CTR_FILES=CTR_FILES+1
                        VAR_NEW_DATA_FILENAME=$DATABASE_MOUNT/$ORACLE_SID/dbf/$TABLESPACE"_f"$CTR_FILES.dbf
                        echo $VAR_NEW_DATA_FILENAME
                        `$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                        connect / as sysdba
                        set linesize 200 pagesize 0 feedback off trimspool on
                        alter tablespace $TABLESPACE add datafile '$VAR_NEW_DATA_FILENAME' SIZE $VAR_KBYTES_TO_ADD K;
                        exit;
!`
                        CheckSQL
                        LogMessage " $ORACLE_SID $TABLESPACE $FILENAME of size $VAR_KBYTES_TO_ADD K added"
                        echo       " $ORACLE_SID $TABLESPACE $FILENAME of size $VAR_KBYTES_TO_ADD K added"
                elif [[  $VAR_KBYTES_TO_ADD -gt 0 && $VAR_KBYTES_TO_ADD -gt $CFG_MAX_DB_FILE_SIZE_KB ]]; then
                        echo ADDING 1 FILE OF MAX SIZE
                        let CTR_FILES=CTR_FILES+1
                        VAR_NEW_DATA_FILENAME=$DATABASE_MOUNT/$ORACLE_SID/dbf/$TABLESPACE"_f"$CTR_FILES.dbf
                        echo $VAR_NEW_DATA_FILENAME
                        `$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                        connect / as sysdba
                        set linesize 200 pagesize 0 feedback off trimspool on
                        alter tablespace $TABLESPACE add datafile '$VAR_NEW_DATA_FILENAME' SIZE $CFG_MAX_DB_FILE_SIZE_KB K;
                        exit;
!`
                        CheckSQL
                        LogMessage " $ORACLE_SID $TABLESPACE $FILENAME of size $CFG_MAX_DB_FILE_SIZE_KB K added"
                        echo       " $ORACLE_SID $TABLESPACE $FILENAME of size $CFG_MAX_DB_FILE_SIZE_KB K added"
                fi

        done

}
##########################
#PRE CHECK FUUNCTION for Normal Tablesapces
#High level calculation space required
#Check OS Free Space
#Check 1 mount point for data files
#Check OS Threshild limits
#Call addresize function if prereq met
#########################
PreCheckNTS()
{

        CTR_NO_OF_TS_UTIL_GT_THRESHOLD=`$ORACLE_HOME/bin/sqlplus -S /nolog << !  | tee /tmp/error.log
        connect / as sysdba
        whenever sqlerror exit sql.sqlcode
        set linesize 200
        set pagesize 0
        set feedback off
        set trimspool on
        select count(*)  from dba_tablespace_usage_metrics where used_percent > $THRESHOLD;
        exit;
!`
        CheckSQL

        LogMessage " $CTR_NO_OF_TS_UTIL_GT_THRESHOLD tablespace(s) above $THRESHOLD %"
        echo       " $CTR_NO_OF_TS_UTIL_GT_THRESHOLD tablespace(s) above $THRESHOLD %"


        if [ $CTR_NO_OF_TS_UTIL_GT_THRESHOLD -ge 1 ]; then

                #echo "Perform Tablespace resize activity"
                #echo "Calculate total storage to add"
                SPACE_REQUIRED_AT_OS=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                connect / as sysdba
                set pagesize 0 feedback off trim on
                col AAA format 999999999999
                select sum((used_percent/$UTIL_TARGET)*tablespace_size*$DB_BLOCK_SIZE/1024)-sum(tablespace_size*$DB_BLOCK_SIZE/1024) as AAA from dba_tablespace_usage_metrics where used_percent >= $THRESHOLD;
                exit;
!`
                #echo "Space requried at OS is $SPACE_REQUIRED_AT_OS K"
                #echo "ENSURE THAT THERE IS ONLY 1 MOUNTPOINT FOR DATAFILES ELSE EXIT"
                CTR_NO_DATAFILE_MOUNT_POINT=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                connect / as sysdba
                set linesize 200
                set pagesize 0
                set feedback off
                set trimspool on
                select count (distinct substr(file_name, 0, instr(file_name,'/',2)-1)) from dba_data_files;
                exit;
!`

                if [ $CTR_NO_DATAFILE_MOUNT_POINT -gt 1 ]; then
                        LogMessage "Error :  THERE ARE MORE THAN 1 DATAFILE MOUNT POINTS"
                        echo       "Error :  THERE ARE MORE THAN 1 DATAFILE MOUNT POINTS"
                        LogMessage "Error :  THERE SHOULD ONLY BE 1 EXITING"
                        echo       "Error :  THERE SHOULD ONLY BE 1 EXITING"
                        MailOut
                        echo Error : THERE ARE MORE THAN 1 DATAFILE MOUNT POINTS. PLEASE CONTACT DBA TEAM
                        exit 1
                else
                        #LogMessage "There is one mount point for datafiles"
                        DATABASE_MOUNT=`$ORACLE_HOME/bin/sqlplus -S /nolog << ! | tee /tmp/error.log
                        connect / as sysdba
                        set linesize 200
                        set pagesize 0
                        set feedback off
                        set trimspool on
                        select distinct substr(file_name, 0, instr(file_name,'/',2)-1) from dba_data_files;
                        exit;
!`
                        case $VAR_OS_TYPE in
                        solaris|sunos)
                                #LogMessage "The operating system is linux or solaris"
                                OS_FREE_SPACE=`df -k $DATABASE_MOUNT | grep -v Mounted | awk '{print $4}'`
                                OS_USED_SPACE=`df -k $DATABASE_MOUNT | grep -v Mounted | awk '{print $3}'`
                                let OS_TOTAL_SPACE="$OS_USED_SPACE + $OS_FREE_SPACE"
                                echo $OS_TOTAL_SPACE
                                ;;
                        linux)
                                #LogMessage "The operating system is linux or solaris"
                                OS_FREE_SPACE=`df -kP $DATABASE_MOUNT | grep -v Mounted | awk '{print $4}'`
                                OS_USED_SPACE=`df -kP $DATABASE_MOUNT | grep -v Mounted | awk '{print $3}'`
                                let OS_TOTAL_SPACE="$OS_USED_SPACE + $OS_FREE_SPACE"
                                echo $OS_TOTAL_SPACE
                                ;;
                        hpux)
                                #LogMessage "The operating system is HPUX"
                                LogMessage "The operating system $VAR_OS_TYPE is not supported exiting"
                                echo       "The operating system $VAR_OS_TYPE is not supported exiting"
                                MailOut
                                echo "Error The operating system $VAR_OS_TYPE is not supported exiting"
                                exit 1
                                ;;
                        aix)
                                #LogMessage "The operating system is AIX "
                                OS_FREE_SPACE=`df -k $DATABASE_MOUNT | grep -v Mounted | awk '{print $3}'`
                                OS_TOTAL_SPACE=`df -k $DATABASE_MOUNT | grep -v Mounted | awk '{print $2}'`
                                ;;

                        *)
                                LogMessage "The operating system $VAR_OS_TYPE is not supported exiting"
                                echo       "The operating system $VAR_OS_TYPE is not supported exiting"
                                MailOut
                                echo "Error The operating system $VAR_OS_TYPE is not supported exiting"
                                exit 1
                                ;;
                        esac
                        #OS_FREE_SPACE=`df -k $DATABASE_MOUNT | grep -v Mounted | awk '{print $3}'`
                        #OS_TOTAL_SPACE=`df -k $DATABASE_MOUNT | grep -v Mounted | awk '{print $2}'`

                        LogMessage "There is one mount point for datafiles $DATABASE_MOUNT"
                        LogMessage "Mount point free  KB : $OS_FREE_SPACE "
                        LogMessage "Mount point total KB : $OS_TOTAL_SPACE "
                        LogMessage "Space required    KB : $SPACE_REQUIRED_AT_OS "

                        echo       "There is one mount point for datafiles $DATABASE_MOUNT"
                        echo       "Mount point free  KB : $OS_FREE_SPACE "
                        echo       "Mount point total KB : $OS_TOTAL_SPACE "
                        echo       "Space required    KB : $SPACE_REQUIRED_AT_OS "

                        EST_PERCENT_OS_USED_AFTER_ADD=$((100- ((($OS_FREE_SPACE*100) - ($SPACE_REQUIRED_AT_OS*100)) / $OS_TOTAL_SPACE*100/100) ))
                        LogMessage " Estimated OS used space after adding approx $EST_PERCENT_OS_USED_AFTER_ADD%"
                        echo       " Estimated OS used space after adding approx $EST_PERCENT_OS_USED_AFTER_ADD%"

                        if [ $EST_PERCENT_OS_USED_AFTER_ADD -gt $VAR_OS_USED_SPACE_LIMIT ]; then
                                LogMessage "Error :  Estimated used space $EST_PERCENT_OS_USED_AFTER_ADD% is higher than OS used space limit $VAR_OS_USED_SPACE_LIMIT %"
                                LogMessage "Error :  $ORACLE_SID INSUFFICIENT SPACE in $DATABASE_MOUNT TO SAFELY ADD OR RESIZE TABLESPACE."
                                LogMessage "Error :  Please add more storage [ BTS ACTION ] "
                                LogMessage "Error :  Autonomous Tablespace Managment exited, please resolve above limitation"

                                echo       "Error :  $ORACLE_SID INSUFFICIENT SPACE in $DATABASE_MOUNT TO SAFELY ADD OR RESIZE TABLESPACE."
                                echo       "Error :  Please add more storage [ BTS ACTION ]"
                                echo       "Error :  Autonomous Tablespace Managment exited, please resolve above limitation"


                                MailOut
                                echo "Error :  $ORACLE_SID INSUFFICIENT SPACE in $DATABASE_MOUNT TO SAFELY ADD OR RESIZE TABLESPACE."
                                exit 1
                        elif [ $EST_PERCENT_OS_USED_AFTER_ADD -gt $VAR_OS_USED_SPACE_THRESHOLD ]; then
                                LogMessage "Utilization above defined os used space threshold , please plan to add storage. [ BTS ACTION ]"
                                LogMessage "Proceeding to Add Or Resize Tablespace"
                                echo       "Utilization above defined os used space threshold , please plan to add storage. [ BTS ACTION ]"
                                echo       "Proceeding to Add Or Resize Tablespace"
                                AddResize
								
                        elif [ $EST_PERCENT_OS_USED_AFTER_ADD -le $VAR_OS_USED_SPACE_THRESHOLD ]; then
                                LogMessage "Proceeding to add or resize tablespace. "
                                LogMessage "Estimated used $EST_PERCENT_OS_FREE_AFTER % less than threshold of $VAR_OS_USED_SPACE_THRESHOLD %"
                                echo       "Proceeding to add or resize tablespace. "
                                echo       "Estimated used $EST_PERCENT_OS_FREE_AFTER % less than threshold of $VAR_OS_USED_SPACE_THRESHOLD %"
                                AddResize
                        fi
                fi
        elif [ $CTR_NO_OF_TS_UTIL_GT_THRESHOLD -eq 0 ]; then
                        LogMessage "Tablespace Utilization Healthy"
                        echo       "Tablespace Utilization Healthy"
        fi
}
#########
#Temp Tablespace Precheck
#######
#PreCheckTTS()

#########
#UNDO Tablespace Precheck
#######
#PreCheckUTS()


##########################################
##MAIN
#LOOP for all database in oratabl : Y
#Call PreCheckNTS function for each database
##########################################
LogMessage "Autonomous Tablespace Management started "
LogMessage  " Operating system  $VAR_OS_TYPE"
LogMessage  " TS THRESHOLD      $THRESHOLD %"
LogMessage  " TS Util Target    $UTIL_TARGET %"
LogMessage  " OS USED THRESHOLD $VAR_OS_USED_SPACE_THRESHOLD %"
LogMessage  " OS USED LIMIT     $VAR_OS_USED_SPACE_LIMIT %"
LogMessage  " MAX ind file sz   $CFG_MAX_DB_FILE_SIZE_KB KB"
LogMessage  " Contact           $CFG_NOTIFICATION_EMAIL_1 $CFG_NOTIFICATION_EMAIL_2 $CFG_NOTIFICATION_EMAIL_3 $CFG_NOTIFICATION_EMAIL_4"

echo       "Autonomous Tablespace Management started "
echo        " Operating system  $VAR_OS_TYPE"
echo        " TS THRESHOLD      $THRESHOLD %"
echo        " TS Util Target    $UTIL_TARGET %"
echo        " OS USED THRESHOLD $VAR_OS_USED_SPACE_THRESHOLD %"
echo        " OS USED LIMIT     $VAR_OS_USED_SPACE_LIMIT %"
echo        " MAX ind file sz   $CFG_MAX_DB_FILE_SIZE_KB KB"
echo        " Contact           $CFG_NOTIFICATION_EMAIL_1 $CFG_NOTIFICATION_EMAIL_2 $CFG_NOTIFICATION_EMAIL_3 $CFG_NOTIFICATION_EMAIL_4"



if [ $VAR_OS_TYPE = 'solaris' ]; then
        VAR_ORATAB=/var/opt/oracle/oratab
        LogMessage "$VAR_ORATAB used"
elif [ $VAR_OS_TYPE = 'sunos' ]; then
        VAR_ORATAB=/var/opt/oracle/oratab
        LogMessage "$VAR_ORATAB used"
else
        VAR_ORATAB=/etc/oratab
        LogMessage "$VAR_ORATAB used"
fi

cat $VAR_ORATAB | sort | while read ORATAB_LINE
do
  case $ORATAB_LINE in
  \#*) ;;  # comment-line in oratab
   "") ;;
    *) if [ `echo $ORATAB_LINE | cut -f 3 -d :` = 'Y' ]
       then
         #echo FUNCTION MAIN, ORATAB_LINE value is $ORATAB_LINE
         ORACLE_SID=`echo $ORATAB_LINE | cut -f 1 -d :`
         export ORACLE_SID
         ORACLE_HOME=`echo $ORATAB_LINE | cut -f 2 -d :`
         export ORACLE_HOME
         LogMessage " Managing Database $ORACLE_SID"
         echo       " Managing Database $ORACLE_SID"
         #GetSQLError   # test function
         FLAG_SKIPDB=false
         GetDbBlockSize
         if [ $FLAG_SKIPDB == "true" ]; then
                FLAG_SKIPDB=false
         else
           if DBModeCheck; then
             PreCheckNTS
           else
             echo "$ORACLE_SID is a Standby Database thus skipping it"
           fi
         fi
         #PreCheckTTS
         #PreCheckUTS
       fi
       ;;
  esac
done
LogMessage "Autonomous Tablespace Management Ended"
#cat $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY | grep added resized
if grep added $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY; then
        MailOut
else
        cat /dev/null > $CFG_LOG_DIR/$CFG_LOG_FILE_DAILY
fi
相关博文：
·  清理SYSAUX（其二）
·  清理SYSAUX（其一）
·  利用脚本自动生成增加数据文件的sql语句，让你轻松高效应对 Oracle 表空间扩容！ --转载：https://www.modb.pro/db/1862145748071104512?utm_source=index_ori
·  [Oracle19C 数据库管理] 管理存储与表空间
·  Oracle 表空间和数据文件遇到的坑 (转载于 微信公众号 JieKeXu DBA之路)
阅读排行：
· 使用C#创建一个MCP客户端
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· ollama系列1：轻松3步本地部署deepseek，普通电脑可用
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
SQL AI 助手