用于检查自动扩展的表空间何时需要增加数据文件。

#!/usr/bin/bash
#
# Nagios plugin to check Oracle tablespace free blocks.
#
# $Id: check_tablespace.sh,v 1.0 2010/11/12 $
#
# Copyright (C) 2010-2011  Yu jun / BII-ERG Ltd.
#
# The purpose of this program is to check if the tablespace need to add
# new datafiles. We assume all tablespaces are autoextended, that means
# the datafiles (tempfiles) in each tablespace are all autoextendable.
#
# For Oracle datafile, the size is limited to 4194304 Oracle blocks If
# You do not use bigfile. This program can alert you when you should add
# datafiles to the tablespace.
#
# For this program to run correctly, you should create a database account
# named "db_check",and the password also should be "db_check". If you
# need to change the username and password, you should modify this
# program. You also need to grant below priviledges to account "db_check"
#  1. create session
#  2. select on dba_data_files
#  3. select on dba_temp_files
# Set Environment Variable
export ORACLE_HOME=/app/oracle/product/10.2
export PATH=$PATH:$ORACLE_HOME/bin


# External commands
CMD_AWK="/usr/bin/awk"

# Nagios plugin return values
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
STATE_TEXT=""

# Default values
WARN_TRIGGER=0
CRIT_TRIGGER=0
VERBOSE=0

# ------------------------------ FUNCTIONS -------------------------------------

printInfo() {
    echo "Nagios plugin to check Oracle tablespace free blocks."
    echo "Copyright (C) 2010-2011  Yu jun / BII-ERG Ltd."
}

printHelp() {
    echo
    echo "Usage: `basename $0` [-s ] [-t ] [-w ] [-c ]"
    echo
    echo "  -s  Oracle database connect string, should be set in file tnsnames.ora"
    echo "  -t  Oracle tablespace type, only data and temp is valid."
    echo "      data : the tablespace is data tablespace."
    echo "      temp : the tablespace is temporary tablespace."
    echo "  -w  If free blocks less than this value,then send a warning message."
    echo "  -c  If free blocks less than this value,then send a critical message."
    echo "  -h  this help screen"
    echo "  -v  verbose output (for debugging)"
    echo "  -V  version info"
    echo
    echo "Example: `basename $0` -s ORCL -t data -w 2097152 -c 524288"
    echo "This will return CRITICAL if data tablespaces' free blocks less than"
    echo "524288(4G if block_size=8k), WARNING if data tablespaces' free blocks"
    echo "less than 2097152(16G if block_size=8k).Else will return UNKNOWN."
    echo
}

printVersion() {
    echo
    echo "\$Id: check_tablespace.sh,v 1.0 2010/11/12 $"
    echo
}

# Checks command line options (pass $@ as parameter).
checkOptions() {
    if [ $# -lt 6 ]; then
        printInfo
        printHelp
        exit $STATE_UNKNOWN
    fi

    while getopts s:t:w:c:hvV OPT $@; do
            case $OPT in
                s) DBConnStr=$OPTARG
                   ;;
                t) Type=$OPTARG
                   ;;
                w) # warning range
                   opt_warn=$OPTARG
                   WARN_TRIGGER=1
                   ;;
                c) # critical range
                   opt_crit=$OPTARG
                   CRIT_TRIGGER=1
                   ;;
                h) printInfo
                   printHelp
                   exit $STATE_UNKNOWN
                   ;;
                v) VERBOSE=1
                   ;;
                V) printInfo
                   printVersion
                   exit $STATE_UNKNOWN
                   ;;
                ?) printInfo
                   printHelp
                   exit $STATE_UNKNOWN
                   ;;
            esac
    done
    range_error=0
    if [ $WARN_TRIGGER -eq 1 ]; then
        if [ "$opt_warn" != "" ]; then
            if [ "$(echo $opt_warn| grep '^[0-9]*$')" = "" ]; then
                range_error=1
            fi
            OPT_WARN=$opt_warn
        fi
    fi
    if [ $CRIT_TRIGGER -eq 1 ]; then
        if [ "$opt_crit" != "" ]; then
            if [ "$(echo $opt_crit | grep '^[0-9]*$')" = "" ]; then
                range_error=1
            fi
            OPT_CRIT=$opt_crit
        fi
    fi
    if [ $WARN_TRIGGER -eq 1 ] && [ $CRIT_TRIGGER -eq 1 ]; then
      if [ $OPT_CRIT -ge $OPT_WARN ]; then
        range_error=1
      fi
    fi
    if [ $range_error -eq 1 ]; then
        echo "Error: Invalid safe range values."
        printInfo
        printHelp
        exit $STATE_UNKNOWN
    fi
    
    if [ -z "$DBConnStr" ]; then
        echo "Error: Invalid Database Connect String."
        printInfo
        printHelp
        exit $STATE_UNKNOWN      
    fi  
    
    type_error=0
   
    if [ -z "$Type" ]; then
      type_error=1
    else
      TYPE=$(echo $Type | tr "[a-z]" "[A-Z]")
      if [ $TYPE != "DATA" ] && [ $TYPE != "TEMP" ]; then
        type_error=1
      fi   
    fi

    if [ $type_error -eq 1 ]; then
        echo "Error: Invalid tablespace type value."
        printInfo
        printHelp
        exit $STATE_UNKNOWN
    fi
}
    

# ----------------------------- MAIN PROGRAM -----------------------------------

checkOptions $@

if [ $VERBOSE -eq 1 ]; then
    set -vx
fi

if [ $TYPE = "DATA" ]; then
  SYSTAB="DBA_DATA_FILES"
elif [ $TYPE = "TEMP" ]; then
  SYSTAB="DBA_TEMP_FILES"
fi

if [ $CRIT_TRIGGER -eq 1 ]; then
CRITNUM=$(sqlplus -silent "db_check/db_check@$DBConnStr" << EOF
set pages 0 feedback off verify off heading off echo off
select count(1) from (select sum(USER_BLOCKS) sum_ublock,sum(MAXBLOCKS) sum_mblock,sum(MAXBLOCKS)-sum(USER_BLOCKS) diff,tablespace_name from $SYSTAB group by tablespace_name having (sum(MAXBLOCKS)-sum(USER_BLOCKS)) < $OPT_CRIT);
exit;
EOF)
  if [ $? -ne 0 ] ; then
    echo "$CRITNUM"
    printInfo
    printHelp
    exit $STATE_UNKNOWN
  fi
fi

if [ $WARN_TRIGGER -eq 1 ]; then
WARNNUM=$(sqlplus -silent "db_check/db_check@$DBConnStr" << EOF
set pages 0 feedback off verify off heading off echo off
select count(1) from (select sum(USER_BLOCKS) sum_ublock,sum(MAXBLOCKS) sum_mblock,sum(MAXBLOCKS)-sum(USER_BLOCKS) diff,tablespace_name from $SYSTAB group by tablespace_name having (sum(MAXBLOCKS)-sum(USER_BLOCKS)) < $OPT_WARN);
exit;
EOF)
  if [ $? -ne 0 ] ; then
    echo "$WARNNUM"
    printInfo
    printHelp
    exit $STATE_UNKNOWN
  fi
fi

if [ $CRIT_TRIGGER -eq 1 ] && [ $WARN_TRIGGER -eq 1 ]; then
  if [ $CRITNUM -gt 0 ]; then
    STATE_TEXT="- There are $CRITNUM tablespaces need to add files"
    echo "$TYPE TABLESPACE CRITICAL $STATE_TEXT"
    exit $STATE_CRITICAL
  elif [ $WARNNUM -gt 0 ]; then
    STATE_TEXT="- There are $WARNNUM tablespaces need to add files"
    echo "$TYPE TABLESPACE WARNING $STATE_TEXT"
    exit $STATE_WARNING
  else
    STATE_TEXT="- There are no tablespaces need to add files"
    echo "$TYPE TABLESPACE OK $STATE_TEXT"
    exit $STATE_OK   
  fi
fi

if [ $CRIT_TRIGGER -eq 1 ] && [ $WARN_TRIGGER -ne 1 ]; then
  if [ $CRITNUM -gt 0 ]; then
    STATE_TEXT="- There are $CRITNUM tablespaces need to add files"
    echo "$TYPE TABLESPACE CRITICAL $STATE_TEXT"
    exit $STATE_CRITICAL
  else
    STATE_TEXT="- There are no tablespaces need to add files"
    echo "$TYPE TABLESPACE OK $STATE_TEXT"
    exit $STATE_OK   
  fi
fi

if [ $CRIT_TRIGGER -ne 1 ] && [ $WARN_TRIGGER -eq 1 ]; then
  if [ $WARNNUM -gt 0 ]; then
    STATE_TEXT="- There are $WARNNUM tablespaces need to add files"
    echo "$TYPE TABLESPACE WARNING $STATE_TEXT"
    exit $STATE_WARNING
  else
    STATE_TEXT="- There are no tablespaces need to add files"
    echo "$TYPE TABLESPACE OK $STATE_TEXT"
    exit $STATE_OK   
  fi
fi