path: root/web/attachments/56241-check_oracle.jjm

#!/bin/sh
#
# latigid010@yahoo.com
# 01/06/2000
#
#  This Nagios plugin was created to check Oracle status
#

PROGNAME=`basename $0`
PROGPATH=`echo $0 | sed -e 's,[\\/][^\\/][^\\/]*$,,'`
REVISION=`echo '$Revision: 1.6 $' | sed -e 's/[^0-9.]//g'`

. $PROGPATH/utils.sh


function init_var() {
RETURN_MSG=""
CHECK_CACHE=no
CHECK_DB=no
CHECK_EXTENTS=no
CHECK_LOGIN=no
CHECK_NAMES=no
CHECK_TS=no
CHECK_TNS=no
CRIT_LVL=-1
WARN_LVL=-1
ORACLE_SID=${ORACLE_SID}
ORA_USER=""
ORA_PASS=""
ORA_TS=""
ORA_HOST=""
ORA_NAMES=""


# Hunt down a reasonable ORACLE_HOME
if [ -z "$ORACLE_HOME" ] ; then
        # Adjust to taste
        for oratab in /var/opt/oracle/oratab /etc/oratab
        do
        [ ! -f $oratab ] && continue
        ORACLE_HOME=`IFS=:
                while read SID ORACLE_HOME junk;
                do
                        if [ "$SID" = "$2" -o "$SID" = "*" ] ; then
                                echo $ORACLE_HOME;
                                exit;
                        fi;
                done < $oratab`
        [ -n "$ORACLE_HOME" ] && break
        done
fi
# Last resort
[ -z "$ORACLE_HOME" -a -d $PROGPATH/oracle ] && ORACLE_HOME=$PROGPATH/oracle

if [ -z "$ORACLE_HOME" -o ! -d "$ORACLE_HOME" ] ; then
        echo "Cannot determine ORACLE_HOME for sid ${ORACLE_SID}"
        exit ${STATE_UNKNOWN}
fi
PATH=$PATH:$ORACLE_HOME/bin
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export ORACLE_HOME PATH LD_LIBRARY_PATH
}


print_usage() {
  echo "This check_oracle provides a new argument handling scheme."
  echo "	To use the old scheme - add a "--" before any arguments"
  echo "Old Usage:"
  echo "  $PROGNAME --tns <Oracle Sid or Hostname/IP address>"
  echo "  $PROGNAME --db <ORACLE_SID>"
  echo "  $PROGNAME --login <ORACLE_SID>"
  echo "  $PROGNAME --cache <ORACLE_SID> <USER> <PASS> <CRITICAL> <WARNING>"
  echo "  $PROGNAME --extents <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
  echo "  $PROGNAME --tablespace <ORACLE_SID> <USER> <PASS> <TABLESPACE> <CRITICAL> <WARNING>"
  echo "  $PROGNAME --oranames <Hostname>"
  echo "  $PROGNAME --help"
  echo "  $PROGNAME --version"
  echo "The new argument handling takes shorter args + allows chaining of checks"
  echo "New Usage:"
  echo "  $PROGNAME [args...]"
  echo "	-c	Cache Check		-C <ARG> 	Critical level"
  echo "	-d	DB Check		-W <ARG>	Warning level"
  echo "	-e	Extent Check"	
  echo "	-l      Listener Check		-u <ARG>	User"
  echo "	-n 	Oranames check		-p <ARG> 	Password"
  echo "	-t	tns names check		-s <ARG>	oracle SID"
  echo "	-T	Tablespace check	-H <ARG>	Host check"	
  echo "			-h 	Help"
}

print_help() {
  print_revision $PROGNAME $REVISION
  echo ""
  print_usage
  echo ""
  echo "Check Oracle status"
  echo ""
  echo "--tns SID/IP Address"
  echo "   Check remote TNS server"
  echo "--db SID"
  echo "   Check local database (search /bin/ps for PMON process) and check"
  echo "   filesystem for sgadefORACLE_SID.dbf"
  echo "--login SID"
  echo "   Attempt a dummy login and alert if not ORA-01017: invalid username/password"
  echo "--cache"
  echo "   Check local database for library and buffer cache hit ratios"
  echo "       --->  Requires Oracle user/password and SID specified."
  echo "       		--->  Requires select on v_$sysstat and v_$librarycache"
  echo "--extents"
  echo "   Check to see if there are extents unable to be extended."
  echo "--tablespace"
  echo "   Check local database for tablespace capacity in ORACLE_SID"
  echo "       --->  Requires Oracle user/password specified."
  echo "       		--->  Requires select on dba_data_files and dba_free_space"
  echo "--oranames Hostname"
  echo "   Check remote Oracle Names server"
  echo "--help"
  echo "   Print this help screen"
  echo "--version"
  echo "   Print version and license information"
  echo ""
  echo "If the plugin doesn't work, check that the ORACLE_HOME environment"
  echo "variable is set, that ORACLE_HOME/bin is in your PATH, and the"
  echo "tnsnames.ora file is locatable and is properly configured."
  echo ""
  echo "When checking local database status your ORACLE_SID is case sensitive."
  echo ""
  echo "If you want to use a default Oracle home, add in your oratab file:"
  echo "*:/opt/app/oracle/product/7.3.4:N"
  echo ""
  support
}


check_cache() {
    if [ ${CRIT_LVL} -gt ${WARN_LVL} ] ; then
        RETURN_MSG="UNKNOWN - Warning level is less then Crit"
        return ${STATE_UNKNOWN}
    fi
    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << EOF
set pagesize 0
select (1-(pr.value/(dbg.value+cg.value)))*100
from v\\$sysstat pr, v\\$sysstat dbg, v\\$sysstat cg
where pr.name='physical reads'
and dbg.name='db block gets'
and cg.name='consistent gets';
EOF`

    if [ -n "`echo $result | grep ORA-`" ] ; then
      error=` echo "$result" | grep "ORA-" | head -1`
      echo "CRITICAL - $error"
      exit $STATE_CRITICAL
    fi


    buf_hr=`echo $result | awk '{print int($1)}'`
    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << EOF
set pagesize 0
select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))*100
from v\\$librarycache lc;
EOF`

    if [ -n "`echo $result | grep ORA-`" ] ; then
      error=` echo "$result" | grep "ORA-" | head -1`
      echo "CRITICAL - $error"
      exit $STATE_CRITICAL
    fi

    lib_hr=`echo $result | awk '{print int($1)}'`

    if [ $buf_hr -le ${CRIT_LVL} -o $lib_hr -le ${CRIT_LVL} ] ; then
        export RETURN_MSG="${ORACLE_SID} CRITICAL - Cache Hit Rates: $lib_hr% Lib -- $buf_hr% Buff"
        exit $STATE_CRITICAL
    fi
    if [ $buf_hr -le ${WARN_LVL} -o $lib_hr -le ${WARN_LVL} ] ; then
        export RETURN_MSG="${ORACLE_SID} WARNING  - Cache Hit Rates: $lib_hr% Lib -- $buf_hr% Buff"
        exit $STATE_WARNING
    fi
    export RETURN_MSG="${ORACLE_SID} OK - Cache Hit Rates: $lib_hr% Lib -- $buf_hr% Buff"

    return ${STATE_OK}


}

check_extents() {
    if [ ${CRIT_LVL} -lt ${WARN_LVL} ] ; then
	RETURN_MSG="UNKNOWN - Warning level is higher then Crit"
	return ${STATE_UNKNOWN}
    fi
    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << EOF
set pagesize 200
column Sname form a40    heading 'Object Name'
column Size  form 99,999  heading 'Size'
column Next  form 99,999 heading 'Next'
column Tname form a15    heading 'TsName'

select a.owner||'.'||a.segment_name "Sname",
       a.bytes/1024/1024            "Size",
       a.next_extent/1024/1024 "Next",
       a.tablespace_name "TName"
from  sys.dba_segments a
where a.tablespace_name not like 'T%MP'   -- Exclude TEMP tablespaces
and   a.tablespace_name like '%${ORA_TS}%'
and   next_extent * 1 > (                  -- Cannot extend 1x, can change to 2x...
                           select max(b.bytes)
                             from dba_free_space b
                            where a.tablespace_name = b.tablespace_name)
order by 3 desc;
EOF`
 
    if [ -n "`echo $result | grep ORA-`" ] ; then
       error=` echo "$result" | grep "ORA-" | head -1`
       echo "CRITICAL - $error"
       exit $STATE_CRITICAL
    fi

    if [ `echo $result | grep -c "no rows selected"` -eq 1 ]
    then
    	export RETURN_MSG="${ORACLE_SID} : ${ORA_TS} OK - Extents.  Extensible"
	return ${STATE_OK}
    else
	nn=`expr \`echo "$result" | wc -l\` - 3`
	export RETURN_MSG="CRIT: Result: $nn objects can't extend"
	return $STATE_CRITICAL
    fi
}

check_tablespace() {
    if [ ${CRIT_LVL} -lt ${WARN_LVL} ] ; then
	RETURN_MSG="UNKNOWN - Warning level is more then Crit"
	return ${STATE_UNKNOWN}
    fi
    result=`sqlplus -s ${ORA_USER}/${ORA_PASS}@${ORACLE_SID} << EOF
set pagesize 0
select b.free,a.total,100 - trunc(b.free/a.total * 1000) / 10 prc
from (
select tablespace_name,sum(bytes)/1024/1024 total
from dba_data_files group by tablespace_name) A,
( select tablespace_name,sum(bytes)/1024/1024 free
from dba_free_space group by tablespace_name) B
where a.tablespace_name=b.tablespace_name and a.tablespace_name='${ORA_TS}';
EOF`
 
    if [ -n "`echo $result | grep ORA-`" ] ; then
       error=` echo "$result" | grep "ORA-" | head -1`
       echo "CRITICAL - $error"
       exit $STATE_CRITICAL
    fi


    ts_free=`echo $result | awk '{print int($1)}'` 
    ts_total=`echo $result | awk '{print int($2)}'` 
    ts_pct=`echo $result | awk '{print int($3)}'` 
	
    ce_ret=${STATE_OK}
    ce_ret_msg=""
    if [ ${CHECK_EXTENTS} == "yes" ]
    then
	check_extents
	ce_ret=${?}
	ce_ret_msg=${RETURN_MSG}
    fi
    if [ ${ts_pct} -ge ${CRIT_LVL} ] || [ ${ce_ret} -eq ${STATE_CRITICAL} ] 
    then
  	RETURN_MSG="${ORACLE_SID} : ${ORA_TS} CRITICAL - $ts_pct% used [ $ts_free / $ts_total MB available ] || ${ce_ret_msg}"
	return ${STATE_CRITICAL}
    fi
    if [ ${ts_pct} -ge ${WARN_LVL} ] || [ ${ce_ret} -eq ${STATE_WARNING} ] 
    then
  	RETURN_MSG="${ORACLE_SID} : ${ORA_TS} WARNING  - $ts_pct% used [ $ts_free / $ts_total MB available ] || ${ce_ret_msg}"
	return ${STATE_WARNING}
    fi
    if [ $ts_free -eq 0 -a $ts_total -eq 0 -a $ts_pct -eq 0 ] || \
   		[ ${ce_ret} -eq ${STATE_UNKNOWN} ]
    then
        RETURN_MSG="No data returned by Oracle - tablespace ${ORA_TS} not found? || ${ce_ret_msg}"
        return ${STATE_UNKNOWN}
    fi
    RETURN_MSG="${ORACLE_SID} : ${ORA_TS} OK - $ts_pct% used [ $ts_free / $ts_total MB available ] || ${ce_ret_msg}"
    return ${STATE_OK}
}

check_tns() {
    tnstarg=""
    if [ -n "${ORA_HOST}" -a "${ORA_HOST}" != "" ]
    then
	tnstarg=${ORA_HOST}
    elif [ -n "${ORACLE_SID}" ] #-a "${ORACLE_SID}" != "" ]
    then
	tnstarg=${ORACLE_SID}
	RETURN_MSG="set tnstarg = $tnstarg (ORACLE_SID = ${ORACLE_SID})"
    else
	RETURN_MSG="Could not determine destination"
	return $STATE_WARNING
    fi
	
    tnschk=` tnsping ${tnstarg}`
    tnschk2=` echo  $tnschk | grep -c OK`
    if [ ${tnschk2} -eq 1 ] ; then 
	tnschk3=` echo $tnschk | sed -e 's/.*(//' -e 's/).*//'`
	export RETURN_MSG="OK - reply time ${tnschk3} from ${tnstarg}"
	return ${STATE_OK}
    else
	export RETURN_MSG="No TNS Listener on ${tnstarg}"
	return $STATE_CRITICAL
    fi
}


check_oranames() {
    namesctl status ${ORA_NAMES} | awk '
    /Server has been running for:/ {
        msg = "OK: Up"
        for (i = 6; i <= NF; i++) {
            msg = msg " " $i
        }
        status = '${STATE_OK}'
    }
    /error/ {
        msg = "CRITICAL: " $0
        status = '$STATE_CRITICAL'
    }
    END {
        print msg
        RETURN status
    }'
}

check_db() {
    pmonchk=`ps -ef | grep -v grep | grep ${ORACLE_SID} | grep -c pmon`
    if [ ${pmonchk} -ge 1 ] ; then
        export RETURN_MSG="${ORACLE_SID} OK - ${pmonchk} PMON process(es) running"
        return ${STATE_OK}
    else
        export RETURN_MSG="${2} Database is DOWN"
        return $STATE_CRITICAL
    fi
}

check_login() {
    loginchk=`sqlplus dummy/user@${ORACLE_SID} < /dev/null`
    loginchk2=` echo  ${loginchk} | grep -c ORA-01017`
    if [ ${loginchk2} -eq 1 ] ; then
        export RETURN_MSG="OK - dummy login connected"
        return ${STATE_OK}
    else
        loginchk3=` echo "${loginchk}" | grep "ORA-" | head -1`
        export RETURN_MSG="CRITICAL - ${loginchk3}"
        return $STATE_CRITICAL
    fi
}
#begin our "main"
#

init_var


while getopts ":cC:dehH:ln:p:s:tT:u:W:" arg ; do
   case ${arg} in

   c )  			#echo "Cache Received" 
	CHECK_CACHE=yes
	;;
   C )  			#echo "Critical lvl = ${OPTARG}" 
	CRIT_LVL=${OPTARG}
	;;
   d )  			#echo "DB Received" 
	CHECK_DB=yes
	;;
   e )  			#echo "Extents Received" 
	CHECK_EXTENTS=yes
	;;
   H )  			#echo "Host Received = ${OPTARG}" 
	ORA_HOST=${OPTARG}
	;;
   h )  			#echo "Help Received" 
	print_help 
	exit ${STATE_UNKNOWN}
	;;
   l )  			#echo "Login Received" 
	CHECK_LOGIN=yes
	;;
   n )  			#echo "oranames Received = $OPTARG}" 
	CHECK_NAMES=yes
	ORA_NAMES=${OPTARG}
	;;
   p )  			#echo "Passwd Received = ${OPTARG}" 
	ORA_PASS=${OPTARG}
	;;
   s )  			#echo "SID Received = ${OPTARG}" 
	ORACLE_SID=${OPTARG}
	;;
   t ) 				#echo "tnsnames Received"
	CHECK_TNS=yes
	;;
   T )  			#echo "Tablespace Received = ${OPTARG}" 
	ORA_TS=${OPTARG}
	CHECK_TS=yes
	;;
   u )  			#echo "User Received = ${OPTARG}"
	ORA_USER=${OPTARG}
	;;
   W )  			#echo "Warning lvl = ${OPTARG}"
	WARN_LVL=${OPTARG} 
	;;
   \?)  print_usage 
	exit ${STATE_UNKNOWN}
	;;
   esac
done


shift $(($OPTIND - 1))

if [ "$1" == "--help" ]
then
	print_help
fi

# "This is where new options can be used - working on compat first
#  "step through following:"
if [ -z $1 ] 
then
  
if [ -n ${ORACLE_SID} ] && [ "${ORACLE_SID}" != "" ]
then
   if [ -n ${ORA_PASS} -a "${ORA_PASS}" != "" ] || \
		[ -n ${ORA_USER} -a "${ORA_USER}" != "" ] || \
		[ -n ${CRIT_LVL} >= 0 ] || [ ${WARN_LVL} >= 0 ]
   then
   	if [ ${CHECK_CACHE} == "yes" ] && [ ${CHECK_TS} == "no" ]
   	then
		check_cache
		ret=${?}
		echo ${RETURN_MSG}
		exit ${ret}
   	fi
   	if [ ${CHECK_EXTENTS} == "yes" ] && [ ${CHECK_TS} == "no" ]
   	then
		echo "call check_Extents"
		check_extents
		ret=${?}
		echo ${RETURN_MSG}
		exit ${RET}
   	fi
   else
        echo "error - not proper args"
   fi
   if [ -n ${ORA_TS} ] && [ "${ORA_TS}" != "" ]
   then
   	if [ ${CHECK_TS} == "yes" ] && [ ${CHECK_CACHE} == "no" ]
   	then
		check_tablespace
		ret=${?}
		echo ${RETURN_MSG}
		exit ${ret}
   	fi
   	if [ ${CHECK_TS} == "yes" ] && [ ${CHECK_CACHE} == "yes" ]
   	then
		RETURN_MSG="INVALID: Currently Cache and tablespace checks don't mesh"
		echo ${RETURN_MSG}
		exit ${STATE_UNKNOWN}
   	fi
   fi
   if [ ${CHECK_LOGIN} == "yes" ]
   then
	check_login
	ret=${?}
	echo ${RETURN_MSG}
	exit ${ret}
   fi
   if [ ${CHECK_DB} == "yes" ]
   then
	check_db
	ret=${?}
	echo ${RETURN_MSG}
	exit ${ret}
   fi	
fi 
else
   echo "Using old Invocation Methods: \"$*\""
fi

case "$1" in
1)
    cmd='--tns'
    ;;
2)
    cmd='--db'
    ;;
*)
    cmd="$1"
    ;;
esac

# Information options
case "$cmd" in
--help)
		print_help
    exit ${STATE_OK}
    ;;
-h)
		print_help
    exit ${STATE_OK}
    ;;
--db)
    		CHECK_DB=yes
		ORACLE_SID=$2
		check_db 
                ret=${?}
    echo ${RETURN_MSG}
    exit $ret
    ;;
--cache)
		CHECK_CACHE=yes
		ORACLE_SID=$2
		ORA_USER=$3
		ORA_PASS=$4
		CRIT_LVL=$5
		WARN_LVL=$6
		check_cache
                ret=${?}
    echo ${RETURN_MSG}
    exit $ret
    ;;
--extents)
		CHECK_EXTENTS=yes
		ORACLE_SID=$2
		ORA_USER=$3
                ORA_PASS=$4
                CRIT_LVL=$5
                WARN_LVL=$6
		check_extents
                ret=${?}
    echo ${RETURN_MSG}
    exit $ret
    ;;
--login)
		CHECK_LOGIN=yes
		ORACLE_SID=$2
		check_login
                ret=${?}
    echo ${RETURN_MSG}
    exit $ret
    ;;
--oranames)
		CHECK_NAMES=yes
		ORA_NAMES=$2
		check_oranames
    exit ${?}
    ;;
--tablespace)
		CHECK_TS=yes
		ORACLE_SID=$2
                ORA_USER=$3
                ORA_PASS=$4
		ORA_TS=$5
                CRIT_LVL=$6
                WARN_LVL=$7
		check_tablespace
  		ret=${?} 
    echo ${RETURN_MSG}
    exit $ret
    ;;
--tns)
		CHECK_TNS=yes
		export ORACLE_SID=$2
		echo "--tns ${ORACLE_SID} "
		check_tns
                ret=${?}
    echo ${RETURN_MSG}
    exit $ret
    ;;
--version)
		print_revision $PLUGIN $REVISION
    exit ${STATE_OK}
    ;;
-V)
		print_revision $PLUGIN $REVISION
    exit ${STATE_OK}
    ;;
*)
    print_usage
    exit ${STATE_UNKNOWN}
    ;;
esac


exit ${STATE_UNKNOWN}