#!/bin/sh
#
# Contributed by Wulf Dietrich - w_dietrich@hotmail.com 10/2/2000
#
# 01/03/2000: autoexted feature for Oracle 7 added
#             sort messages (error, warning, ok)
#             fixed bug if more then one datafile of same size
#             send error only if found error and warning
# 09/03/2000: support for Oracle 8.1 added
#             minor changes in msg-handling
#
# check free space per Tablespace
# need to run ora_create_ops_user before using this agent
#
# Usage:
#contrib:ora_ts:8-18:15:8:enabled:tst=SYSTEM=90=95 tst=USERS=90=95:
#
# developed on AIX

#. /usr/local/bcnu/etc/bcnuenv
. $BCNUHOME/agent/agent_head

sort_by_severity()
{
        sed -e "s/^ok /wok /g" $1 | sort $2 | sed "s/^wok /ok /g"
}

DATAFILE=$BCNUTMP/${BCNUAGENT}.dat
OUTFILE=$BCNUTMP/${BCNUAGENT}.out
MSGFILE=$BCNUTMP/${BCNUAGENT}.msg
TMPFILE=$BCNUTMP/${BCNUAGENT}.tmp

OK=1
ERR=""
WARN=""

# we need this for numeric format used by awk (dezimal point) on AIX systems
export LANG=C


SID=""
SID_LIST=""
for param in $BCNUPARAM ; do

  bcnu_param
  SID=$p1

  if [ -z "$SID_LIST" ] ; then
    SID_LIST=" $SID "
  else
    if ! echo "$SID_LIST" | $GREP " $SID " >/dev/null ; then
      SID_LIST="${SID_LIST}${SID} "
    fi
  fi
 
done

if [ -z "$SID_LIST" ] ; then
  # nothing to do
  BCNUMSG="$BCNU -m 'ok - nothing to monitor' -e $BCNU_OK -t $BCNUHOSTTYPE $BCNUHOST"
  bcnu_send
  exit 0
fi

rm -f $TMPFILE $DATAFILE $OUTFILE $MSGFILE
bcnu_check_err "remove temporary files"

for SID in $SID_LIST ; do

  ORACLE_HOME=`grep "^$SID:" $ORATAB | cut -d: -f2`
  if [ -z "$ORACLE_HOME" ] ; then
    WARN=1
    printf "%-7s - Database Instance %s:\t ORACLE_SID $SID not found in $ORATAB\n" warning $ORACLE_SID >> $MSGFILE
    bcnu_check_err "write to file $MSGFILE"
    continue
  fi

  export ORACLE_HOME=$ORACLE_HOME
  export PATH=$ORACLE_HOME/bin:$PATH
  export ORACLE_SID=$SID

  if echo $ORACLE_HOME | $GREP "7.3" >/dev/null ; then
    sqlplus -s / > $TMPFILE <<EOF
set pagesize 0
set linesize 160
set heading off
set feedback off
set verify off
select
  value
from
  v\$parameter
where
  name = 'db_block_size'
  ;
quit
EOF
    bcnu_check_err "select db_block_size on SID $SID" $TMPFILE '^ORA-'
    DB_BLOCK_SIZE=`cat $TMPFILE`
    sqlplus -s / >> $DATAFILE <<EOF
set pagesize 0
set linesize 160
set heading off
set feedback off
set verify off
select
  '$SID '
  || fs.tablespace_name
  || ' ' || df.file_name
  || ' ' || to_char(ae.maxextend*$DB_BLOCK_SIZE/1024/1024, '999999999.999')
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  || ' ' || to_char(sum(fs.bytes)/1024/1024, '999999999.999')
from
  dba_data_files df,
  dba_free_space fs,
  sys.filext\$ ae
where
  df.tablespace_name = fs.tablespace_name
and
  df.file_id = ae.file#
group by
  '$SID '
  || fs.tablespace_name
  || ' ' || df.file_name
  || ' ' || to_char(ae.maxextend*$DB_BLOCK_SIZE/1024/1024, '999999999.999')
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  ;
select
  '$SID '
  || fs.tablespace_name
  || ' ' || df.file_name
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  || ' ' || to_char(sum(fs.bytes)/1024/1024, '999999999.999')
from
  dba_data_files df,
  dba_free_space fs
where
  df.tablespace_name = fs.tablespace_name
and
  df.file_id not in (select file# from sys.filext\$)
group by
  '$SID '
  || fs.tablespace_name
  || ' ' || df.file_name
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  ;
quit
EOF
  bcnu_check_err "select on SID $SID" $DATAFILE '^ORA-'
  
  elif echo $ORACLE_HOME | $GREP -e "8.0" -e "8.1" >/dev/null ; then
 
    sqlplus -s / >> $DATAFILE <<EOF
set pagesize 0
set linesize 160
set heading off
set feedback off
set verify off
select
  '$SID '
  || fs.tablespace_name
  || ' ' || df.file_name
  || ' ' || decode(df.autoextensible,
              'YES', to_char(df.maxbytes/1024/1024, '999999999.999'),
              'NO', to_char(df.bytes/1024/1024, '999999999.999'))
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  || ' ' || to_char(sum(fs.bytes)/1024/1024, '999999999.999')
from
  dba_data_files df,
  dba_free_space fs
where
  df.tablespace_name = fs.tablespace_name
group by
  '$SID '
  || fs.tablespace_name
  || ' ' || df.file_name
  || ' ' || decode(df.autoextensible,
              'YES', to_char(df.maxbytes/1024/1024, '999999999.999'),
              'NO', to_char(df.bytes/1024/1024, '999999999.999'))
  || ' ' || to_char(df.bytes/1024/1024, '999999999.999')
  ;
quit
EOF
  bcnu_check_err "select on SID $SID" $DATAFILE '^ORA-'

  else

    WARN=1
    printf "%-7s - Database Instance %s:\t DB version unexpected or not found in ORACLE_HOME=$ORACLE_HOME\n" warning $ORACLE_SID >> $MSGFILE
    bcnu_check_err "write to file $MSGFILE"
    bcnu_warn_send

  fi

done

if ! [ -s $DATAFILE ] ; then
  # nothing to do
  if [ $WARN ] ; then

    printf "Monitored Tablespaces:\n\n" > $OUTFILE
    bcnu_check_err "create output file"

    sort_by_severity $MSGFILE '-k 1' >> $OUTFILE
    bcnu_check_err "sort messages"

    BCNUMSG="$BCNU -m 'warning - tablespaces instable' -e $BCNU_WARN -t $BCNUHOSTTYPE -f $OUTFILE $BCNUHOST"
    bcnu_warn_send

  else

    BCNUMSG="$BCNU -m 'ok - no tablespace found to monitor' -e $BCNU_OK -t $BCNUHOSTTYPE $BCNUHOST"
    bcnu_send

  fi
  exit 0
fi

# if we have more then one datafile per tablespace
sort $DATAFILE | $AWK 'BEGIN {OLD_TS=""; MAXSIZE=0; SIZE=0;} {
SID=$1
TS=$2
FREE=$6
if ( TS == OLD_TS && SID == OLD_SID ) {
  MAXSIZE=MAXSIZE+$4; SIZE=SIZE+$5;
  }
else {
  if ( OLD_TS != "" ) {
    printf "%s %s %0.3f %0.3f %0.3f\n", OLD_SID, OLD_TS, MAXSIZE, SIZE, OLD_FREE;
    }
  MAXSIZE=$4; SIZE=$5;
  }
OLD_TS=TS;
OLD_SID=SID;
OLD_FREE=FREE;
}
END {printf "%s %s %0.3f %0.3f %0.3f\n", SID, TS, MAXSIZE, SIZE, FREE;}' >$TMPFILE
bcnu_check_err "awk pre processing"


for param in $BCNUPARAM ; do

  bcnu_param
  SID=$p1
  TS_NAME=$p2
  VAL_WARN=$p3
  VAL_ERR=$p4

  $AWK -v SID=$SID -v TS_NAME=$TS_NAME -v VAL_WARN=$VAL_WARN -v VAL_ERR=$VAL_ERR '{
# $6:    free space in actual size
# $4-$5: additional free space if autoextend enabled
PCT_USED=100*($3-($5+$3-$4))/$3;
#  printf "%s %s %s %u\n", $1, $2, $3, PCT_USED;
if ( $1 == SID && $2 == TS_NAME ) {
  if ( PCT_USED > VAL_ERR ) {
    VAL=VAL_ERR;
    SEVERITY="error";
    OP=">";
    }
  else {
    if ( PCT_USED > VAL_WARN ) {
      VAL=VAL_WARN;
      SEVERITY="warning";
      OP=">";
      }
    else {
      VAL=VAL_WARN;
      SEVERITY="ok";
      OP="<";
      }
    }
  printf "%-7s - Tablespace %-20s on %-8s %s %u%s full (%u%s)\n", SEVERITY, TS_NAME, SID, OP, VAL, "%", PCT_USED, "%";
  }
}' $TMPFILE >> $MSGFILE
  bcnu_check_err "awk processing"

done

printf "Monitored Tablespaces:\n\n" > $OUTFILE
bcnu_check_err "create output file"

sort_by_severity $MSGFILE '-k 1 -k 6' >> $OUTFILE
bcnu_check_err "sort messages"

if grep "^error" $OUTFILE >/dev/null ; then
  OK=""
  ERR=1
fi
if grep "^warning" $OUTFILE >/dev/null ; then
  OK=""
  WARN=1
fi

# add usage for all tablespaces
$AWK 'BEGIN {printf "\nTablespace Usage:\n"; OLD_SID="";} {
  SID=$1;
  if ( SID != OLD_SID ) { printf "\n%s%s\n", SID, ":"; }
  PCT_USED=100*($3-($5+$3-$4))/$3;
  printf "%-20s\t%14.3f%s%3u%s\n", $2, $3, "MB, ", PCT_USED, "% full";
  OLD_SID=SID;
}' $TMPFILE >> $OUTFILE
bcnu_check_err "awk post processing"

if [ $OK ] ; then
    BCNUMSG="$BCNU -m 'ok - tablespaces stable' -e $BCNU_OK -t $BCNUHOSTTYPE -f $OUTFILE $BCNUHOST"
    bcnu_send
else
  if ! [ $ERR ] ; then
    if [ $WARN ] ; then
      BCNUMSG="$BCNU -m 'warning - tablespaces instable' -e $BCNU_WARN -t $BCNUHOSTTYPE -f $OUTFILE $BCNUHOST"
      bcnu_warn_send
    fi
  else
    BCNUMSG="$BCNU -m 'error - tablespaces instable' -e $BCNU_ERR -t $BCNUHOSTTYPE -f $OUTFILE $BCNUHOST"
    bcnu_err_send
  fi
fi
