Table of Contents
[SCRIPT] Nagios check plugin for oracle
Description
Script used as plugin for Nagios to remotely check Oracle. This script is a complete rewrite of this one.
Instructions
Install
Download script code and copy to your nagios plugins home, for example:
/usr/local/nagios/libexec/check_remote_oracle
Then you'll have to add it to nagios and enable checks through it.
Configuration
Required variables (not passed through CLI):
Variable | Default value | Description |
---|---|---|
ORACLEUSER | NAGIOS | User for connecting to OracleDB (no the HOST running OracleDB) |
ORACLEUSERPASSWORD | NAGIOSPASSWORD | Password for the previous user |
You can create the NAGIOS user issuing the following commands:
CREATE USER "NAGIOS" PROFILE "DEFAULT" IDENTIFIED BY "NAGIOSPASSWORD" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK ; GRANT CONNECT, SELECT_CATALOG_ROLE TO "NAGIOS" ;
Control specific grants:
- SGA:
GRANT SELECT ON SYS.V_$SGASTAT TO VOXELADMIN ;
For a better understand of the usage, this script will do something like:
ssh ora01.ciberterminal.net sqlplus 'NAGIOSUSER/NAGIOSPASSWORD@ORACLE_SID' SELECT 1+1 FROM DUAL;
Note: This user is not user in the –dgstats check, look the check instructions here down.
Testing
You can test it by executing it:
bash check_remote_oracle
I will show the instructions:
$ bash check_remote_oracle Usage: ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --tns <ORACLE_SID> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --db <ORACLE_SID> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --login <ORACLE_SID> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --sessions <ORACLE_SID> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --cache <ORACLE_SID> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --parsing <ORACLE_SID> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --tablespace <ORACLE_SID> <TABLESPACE> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --undo <ORACLE_SID> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --diskgroup <ORACLE_SID> <DISKGROUP> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --asmfs <ASMFS_PATH> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --dgstats <ORACLE_SID> <CRITICAL> <WARNING> ./check_remote_oracle -H <Hostname/IP address> [-U Oracle OS User] --redogen <ORACLE_SID> ./check_remote_oracle --help ./check_remote_oracle --version
For example, checking the statistics of the dataguard:
$ bash check_remote_oracle -H scan01.ciberterminal.net --sessions DBSID 150 100 OK - DBSID Concurrent Active Sessions Count: 4 |Sessions=4;100;150;0;20
Check Descriptions
--tns
Check remote TNS server, that is: tnsping
it (not much useful…)
--db
Check remote database (search /bin/ps for PMON process)
--login
Attempt a dummy login and alert if not ORA-01017: invalid username/password
--sessions
Check remote database concurrent active sessions:
SELECT COUNT(SES.SID) FROM V$SESSION SES, V$SQLAREA SQL, V$SESSION_WAIT WA, V$PROCESS P WHERE SES.STATUS='ACTIVE' AND SES.SQL_ID=SQL.SQL_ID AND SES.SID=WA.SID AND SES.paddr=p.addr AND UPPER(SES.USERNAME) NOT IN ( 'SYS','SYSMAN','MDSYS','SYSTEM','NAGIOS' ) /
--cache
Check remote database for library and buffer cache hit ratios:
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' /
--parsing
Check remote database for Soft/Hard parse ratios:
SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'parse count (total)' ; SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'parse count (hard)' ; SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'parse count (failures)' ; SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'parse count (describe)' ;
--tablespace
Check remote database for <TABLESPACENAME> capacity in given ORACLESID :
<code SQL>
SELECT MAXMB,
REALFREEMB FREEMB
FROM (
SELECT MAXUSAGE.TABLESPACENAME,
MAXUSAGE.MAXMB,
CASE WHEN MAXUSAGE.ACTUALDATAFILEMB < MAXUSAGE.MAXMB THEN
MAXMB-(ACTUALDATAFILEMB-FREEMB)
ELSE
FREEMB
END REALFREEMB
FROM
(
select TABLESPACENAME,
SUM(case when MAXBYTES > 0 then MAXBYTES else BYTES END)/1024/1024 MAXMB,
SUM(BYTES)/1024/1024 ACTUALDATAFILEMB
FROM DBADATAFILES
GROUP BY TABLESPACENAME
) MAXUSAGE,
(
select TABLESPACENAME, SUM(BYTES)/1024/1024 FREEMB
FROM dbafreespace
GROUP BY TABLESPACENAME
) FREEUSAGE
WHERE MAXUSAGE.TABLESPACENAME=FREEUSAGE.TABLESPACENAME
AND MAXUSAGE.TABLESPACENAME = '<TABLESPACENAME>'
)
;
</code>
==== –undo ====
Check remote database for UNDO usage :
<code SQL>
select TOTALSPACE.TOTAL TOTALSPACE,
TOTALSPACE.TOTAL-NVL(USEDSPACE.USED,0.0) FREESPACE
FROM (
SELECT TABLESPACENAME, SUM(BYTES)/1024/1024 TOTAL
FROM DBADATAFILES WHERE TABLESPACENAME = (SELECT value FROM V\\\$parameter WHERE name = 'undotablespace')
GROUP BY TABLESPACENAME
) TOTALSPACE
LEFT OUTER JOIN
(
SELECT TABLESPACENAME, SUM(BYTES)/1024/1024 USED
FROM DBAUNDOEXTENTS
WHERE (STATUS='UNEXPIRED' OR STATUS='ACTIVE')
AND TABLESPACENAME = (SELECT value FROM V\\\$parameter WHERE name = 'undotablespace')
GROUP BY TABLESPACENAME
) USEDSPACE
ON TOTALSPACE.TABLESPACENAME=USEDSPACE.TABLESPACENAME
;
</code>
==== –diskgroup ====
Check remote database for <DISKGROUPNAME> capacity in ORACLEASMSID (Tipically +ASM1/2…)
<code SQL>
SELECT TOTALMB, NVL(FREEMB,0.0) FREEMB
FROM V$ASMDISKGROUP
WHERE NAME='<DISKGROUP_NAME>' ;
</code>
==== –dgstats ====
Dataguard statistics (Apply & Transport Lag).
This check is done ON THE STANDBY (the master does not have information on V$DATAGUARDSTATS.
So ORACLE_SID and HOSTNAME must be the STANDBY ones.
<code SQL>
SELECT * FROM V$DATAGUARD_STATS WHERE NAME LIKE '%lag' ;
</code>
==== –redogen ====
Daily REDO generation statistics.
Something like that:
<code SQL>
SELECT A.DAY, Round(A.COUNT#*B.AVG#/1024/1024) DailyAvgMb
FROM
(
SELECT ToChar(FirstTime,'YYYY-MM-DD') DAY, COUNT(1) COUNT#, MIN(RECID) MIN#, MAX(RECID) MAX#
FROM v$loghistory
GROUP BY ToChar(First_Time,'YYYY-MM-DD')
ORDER BY 1 DESC
) A,
(
SELECT Avg(BYTES) AVG#
FROM v$log
) B
ORDER BY DAY
/
</code>
====== Script code ======
<file bash checkremoteoracle>
#! /bin/bash
#
# latigid010@yahoo.com
# 01/06/2000
#
# enricm@gmail.com
# 16/04/2012
#
# dodger@ciberterminal.net
# 03/10/2013: Complete rewrite of this f**shit
#
# This Nagios plugin was created to check Oracle status on a remote site through SSH
#
########################################################################
#
# CONSTANTS
#
########################################################################
# EXIT STATUS
STATEOK=0
STATEWARNING=1
STATECRITICAL=2
STATEUNKNOWN=3
STATEDEPENDENT=4
# /EXIT STATUS
REVISION=“1.1”
# colors
LIGHTGREEN=“\033[1;32m”
LIGHTRED=“\033[1;31m”
WHITE=“\033[0;37m”
RESET=“\033[0;00m”
########################################################################
#
# / CONSTANTS
#
########################################################################
########################################################################
#
# FUNCTIONS
#
########################################################################
# printf “%s${LIGHTRED}USAGE:${RESET}
# $0 ORACLESID BACKUPMETHOD
#
# Where ORACLESID allowed: ${LIGHTGREEN}${KNOWNSIDS[]}${RESET}
# And BACKUPMETHOD allowed: ${LIGHTGREEN}${KNOWNTYPE[]}${RESET}\n”
# # VERY INITIAL CHECKS
support() {
printf “%sSend email to dodger@ciberterminal.net if you have questions\nregarding use of this software. To submit patches or suggest improvements,\nsend email to nagiosplug-dodger@ciberterminal.net.\nPlease include version information with all correspondence (when possible,\nuse output from the –version option of the plugin itself).\n” | sed -e 's/\n/ /g'
}
printrevision() {
echo “$1 v$2 (nagios-plugins 1.4.15)”
printf “%sThe nagios plugins come with ABSOLUTELY NO WARRANTY. You may redistribute\ncopies of the plugins under the terms of the GNU General Public License.\nFor more information about these matters, see the file named COPYING.\n” | sed -e 's/\n/ /g'
}
printusage() {
echo “Usage:
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –tns <ORACLESID>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –db <ORACLESID>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –login <ORACLESID>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –sessions <ORACLESID> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –cache <ORACLESID> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –parsing <ORACLESID> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –tablespace <ORACLESID> <TABLESPACE> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –undo <ORACLESID> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –diskgroup <ORACLESID> <DISKGROUP> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –asmfs <ASMFSPATH> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –dgstats <ORACLESID> <CRITICAL> <WARNING>
${PROGPATH}/${PROGNAME} -H <Hostname/IP address> [-U Oracle OS User] –redogen <ORACLESID>
${PROGPATH}/${PROGNAME} –help
${PROGPATH}/${PROGNAME} –version
”
}
printhelp() {
printrevision $PROGNAME $REVISION
echo “”
printusage
echo “
Check Oracle status
–tns SID/IP Address
Check remote TNS server
–db SID
Check remote database (search /bin/ps for PMON process)
–sessions SID
Check remote database concurrent active sessions
–login SID
Attempt a dummy login and alert if not ORA-01017: invalid username/password
–cache
Check remote database for library and buffer cache hit ratios
–parsing
Check remote database for Soft/Hard parse ratios
–tablespace
Check remote database for tablespace capacity in ORACLESID
–undo
Check remote database for UNDO tablespace capacity in ORACLESID
–diskgroup
Check remote database for diskgroup capacity in ORACLEASMSID (Tipically +ASM1/2…)
–dgstats
Dataguard statistics (Apply & Transport Lag). This check is done ON THE STANDBY (the master does not have information on V\$DATAGUARDSTATS. So ORACLESID and HOSTNAME must be the STANDBY ones.
–redogen
Daily check for yesterday's redo generation
–help
Print this help screen
–version
Print version and license information
If the plugin doesn't work, check that the ORACLEHOME environment
variable is set, that ORACLEHOME/bin is in your PATH, and the
tnsnames.ora file is locatable and is properly configured on your Oracle server.
If you want to use a default Oracle home, add in your oratab file:
:/opt/app/oracle/product/7.3.4:N
”
}
usage()
{
# Information options
case “${1^^}” in
“–HELP”|“-H”)
printhelp
exit $STATEOK
;;
“–VERSION”|“-V”)
printrevision $PROGNAME $REVISION
exit $STATEOK
;;
*)
printusage
exit $STATEOK
;;
esac
}
returnvalues()
{
local let VALUE=$1
if [ ${VALUE} -gt ${CRITICAL} ] ; then
MSG=“CRITICAL”
RETURNCODE=${STATECRITICAL}
elif [ ${VALUE} -gt ${WARNING} ] ; then
MSG=“WARNING”
RETURNCODE=${STATEWARNING}
elif [ ${VALUE} -le ${WARNING} ] ; then
MSG=“OK”
RETURNCODE=${STATEOK}
else
MSG=“UNKNOWN”
RETURNCODE=${STATEUNKNOWN}
fi
echo ${MSG}
return ${RETURNCODE}
}
getremoteoraclehome()
{
# Hunt down a reasonable ORACLEHOME
ORATABLIST=“$(${SSH} “locate oratab” | egrep ”/oratab$“ | tr '\n' ' ')”
for ORATAB in ${ORATABLIST} ; do
ORACLEHOME=$(${SSH} “cat ${ORATAB}” | egrep “^(${ORACLESID}|*):” | awk -F\: '{print $2}')
"${ORACLE_HOME}" && [ $($SSH “ls -d ${ORACLEHOME}” |wc -l) -eq 1 ] && return 0
done
return 1
}
checktns()
{
local AUX=“${ORAENV} tnsping ${ORACLESID}”
local TNSCHECK=“$(${SSH} ${AUX})”
# echo ${TNSCHECK}
if "${TNSCHECK}" =~ .*OK.*\(([0-9]{1,})\ .* ; then
return ${STATEOK}
else
return ${STATECRITICAL}
fi
}
checkdb()
{
local PMONCHECK=“$(${SSH} “ps -ef” | egrep -v grep | grep -c “orapmon${ORACLESID}”)”
if [ ${PMONCHECK} -ge 1 ] ; then
# echo “${ORACLESID} OK - ${PMONCHECK} PMON process(es) running”
return ${STATEOK}
else
# echo “${ORACLESID} Database is DOWN”
return ${STATECRITICAL}
fi
}
checklogin()
{
local AUX=“${ORAENV} echo 'select 1+1 from dual ;' | ${SQLPLUS}”
local LOGINCHECK=“$(${SSH} ${AUX} | egrep -c '^space{0,}2$')”
if [ ${LOGINCHECK} -ge 1 ] ; then
# echo “${ORACLESID} OK - dummy login connected”
return ${STATEOK}
else
# echo “${ORACLESID} - dummy login fail”
return ${STATECRITICAL}
fi
}
# THIS FUNCTION IS PERSONALIZED FOR VOXEL
# TSDG variable must be “SCHEMA1:SCHEMA2:SCHEMA-n”
checkasessions()
{
local let RETURNCODE=${STATEUNKNOWN}
local let MAXINDEX=0
local let i=0
local let x=0
local let TOTALSESSIONS=0
declare -a RESSULTARRAY
case ${ORACLESID^^} in
“DIVAPRO” )
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 99999
select count() from V\\\$SESSION where USERNAME='DIVAPP' and PROGRAM LIKE 'w3wp.exe' ;
select count() from V\\\$SESSION where USERNAME='DIVAPP' and PROGRAM NOT LIKE 'w3wp.exe' ;
”
;;
“BAVELPRO” )
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 99999
select count() from V\\\$SESSION where USERNAME='VOXEL';
select count() from V\\\$SESSION where USERNAME='MANAGERDATASYNCH' ;
select count() from V\\\$SESSION where USERNAME='MANAGERWEB' ;
select count() from V\\\$SESSION where USERNAME='MIRINDAUSER' ;
”
;;
* )
echo “CRITICAL - ${ORACLESID} Error getting asessions check ORACLESID”
return ${STATECRITICAL}
;;
esac
local AUX=“${ORAENV} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local RESSULT=“$(${SSH} “${AUX}” | awk '{print $1}')”
if "${RESSULT}" =~ .*(ORA\-[0-9]{1,}).* ; then
echo “CRITICAL - ${ORACLESID} Error getting sessions: ${BASHREMATCH[1]}”
return ${STATECRITICAL}
fi
for i in ${RESSULT} ; do
let TOTALSESSIONS+=$i
RESSULTARRAY[$x]=$i
[ $i -gt ${RESSULTARRAY[$MAXINDEX]} ] && MAXINDEX=$x
let x++
done
MSG=“$(returnvalues ${RESSULTARRAY[${MAXINDEX}]})”
RETURNCODE=$?
case ${ORACLESID^^} in
“DIVAPRO” )
echo “${MSG} - ${ORACLESID} All Oracle Opened Sessions Count: ${TOTALSESSIONS} |Sessions=${TOTALSESSIONS} DEVOLUIVAWEB=${RESSULTARRAY[0]} DEVOLUIVABACKEND=${RESSULTARRAY[1]}”
return ${RETURNCODE}
;;
“BAVELPRO” )
AUX=“Sessions=${TOTALSESSIONS}”
AUX=“${AUX} VOXEL=${RESSULTARRAY[0]}”
AUX=“${AUX} MANAGERDATASYNCH=${RESSULTARRAY[1]}”
AUX=“${AUX} MANAGERWEB=${RESSULTARRAY[2]}”
AUX=“${AUX} MIRINDAUSER=${RESSULTARRAY[3]}”
echo “${MSG} - ${ORACLESID} All Oracle Opened Sessions Count: ${TOTALSESSIONS} |${AUX}”
return ${RETURNCODE}
;;
* )
echo “CRITICAL - ${ORACLESID} Error getting asessions check ORACLESID”
return ${STATECRITICAL}
;;
esac
}
#This a statistics-only control, so it always return OK unless it can't connect to Oracle
checkredogen()
{
local let RETURNCODE=${STATEUNKNOWN}
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 9999999999
SELECT Round(A.COUNT#B.AVG#/1024/1024) YESTERDAYREDOGENMB
FROM (
SELECT ToChar(FirstTime,'YYYY-MM-DD') DAY, COUNT(1) COUNT#, MIN(RECID) MIN#, MAX(RECID) MAX#
FROM v\\\$loghistory
WHERE ToChar(FirstTime,'YYYY-MM-DD')=ToChar(SYSDATE-1,'YYYY-MM-DD')
GROUP BY ToChar(First_Time,'YYYY-MM-DD')
ORDER BY 1 DESC
) A,
(
SELECT Avg(BYTES) AVG#
FROM v\\\$log
) B
/”
local AUX=“${ORAENV} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local RESSULT=“$(${SSH} “${AUX}” | awk '{print $1}')”
if "${RESSULT}" =~ .*(ORA\-[0-9]{1,}).* ; then
echo “CRITICAL - ${ORACLESID} Error getting redo generation: ${BASHREMATCH[1]}”
return ${STATE_CRITICAL}
fi
echo “OK - ${ORACLE_SID} Redo generation: ${RESSULT} |RedoGen=${RESSULT}”
return 0
}
checkcache()
{
local let RETURNCODE=${STATEUNKNOWN}
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 9999999.99
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'
/”
local AUX=“${ORAENV} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local LIBHITS=“$(${SSH} “${AUX}” | awk '{print $1}')”
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 9999999.99
select sum(lc.pins)/(sum(lc.pins)+sum(lc.reloads))100
from v\\\$librarycache lc
/”
local AUX=“${ORAENV} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local CACHEHITS=“$(${SSH} “${AUX}” | awk '{print $1}')”
if | "${LIBHITS}" =~ .*(ORA\-[0-9]{1,}).* ; then
echo “CRITICAL - ${ORACLESID} Error getting Cache: ${BASHREMATCH[1]}”
return ${STATECRITICAL}
fi
if ! | "${LIBHITS}" =~ [0-9]{1,2}\.[0-9]{0,2} ; then
echo “CRITICAL - ${ORACLESID} Error getting Cache, values returned: ${CACHEHITS} , ${LIBHITS}”
return ${STATECRITICAL}
fi
if | ${LIBHITS/.*} -le ${CRITICAL} ; then
MSG=“CRITICAL”
RETURNCODE=${STATECRITICAL}
elif | ${LIBHITS/.*} -le ${WARNING} ; then
MSG=“WARNING”
RETURNCODE=${STATEWARNING}
elif ${CACHEHITS/.*} -gt ${WARNING} && ${LIBHITS/.*} -gt ${WARNING} ; then
MSG=“OK”
RETURNCODE=${STATEOK}
else
MSG=“UNKNOWN”
RETURNCODE=${STATEUNKNOWN}
fi
echo “${MSG} - ${ORACLESID} - Cache Hit Rates: ${CACHEHITS}% Lib – ${LIBHITS}% Buff|lib=${CACHEHITS}%;${CRITICAL};${WARNING};0;100 buffer=${LIBHITS}%;${CRITICAL};${WARNING};0;100”
return ${RETURNCODE}
}
checkparsing()
{
local let RETURNCODE=${STATEUNKNOWN}
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 99999999999999
SELECT VALUE FROM V\\\$SYSSTAT WHERE NAME = 'parse count (total)' ;
SELECT VALUE FROM V\\\$SYSSTAT WHERE NAME = 'parse count (hard)' ;
SELECT VALUE FROM V\\\$SYSSTAT WHERE NAME = 'parse count (failures)' ;
SELECT VALUE FROM V\\\$SYSSTAT WHERE NAME = 'parse count (describe)' ;”
local AUX=“${ORAENV} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local RESSULT=“$(${SSH} “${AUX}” | tr '\n' ' ')”
if | "${RESSULT}" =~ ^(([[:space:|)[0-9]{1,}){4}$ ]] ; then
echo “CRITICAL - ${ORACLESID} Error getting parsing data: ${RESSULT}”
return ${STATECRITICAL}
fi
# PARSING INDEXES like possitional parameter
# TOTAL=1
# HARD=2
# FAIL=3
# DESCRIBE=4
SOFTPARSE=“$(echo “${RESSULT}” | awk '{printf ”%2.2f“,($1-$2-$3-$4)/$1100}')”
HARDPARSE=“$(echo “${RESSULT}” | awk '{printf ”%2.2f“,$2/$1100}')”
FAILEDPARSE=“$(echo “${RESSULT}” | awk '{printf ”%2.2f“,$3/$1100}')”
if ${SOFTPARSE/.*} -le ${CRITICAL} ; then
MSG=“CRITICAL”
RETURNCODE=${STATECRITICAL}
elif ${SOFTPARSE/.*} -le ${WARNING} ; then
MSG=“WARNING”
RETURNCODE=${STATEWARNING}
elif ${SOFTPARSE/.*} -gt ${WARNING} ; then
MSG=“OK”
RETURNCODE=${STATEOK}
else
MSG=“UNKNOWN”
RETURNCODE=${STATEUNKNOWN}
fi
echo “${MSG} - ${ORACLESID} - Parse Ratio %: ${SOFTPARSE}% Soft – ${HARDPARSE}% Hard – ${FAILEDPARSE}% Failed|Soft=${SOFTPARSE}%;${CRITICAL};${WARNING};0;100 Hard=${HARDPARSE}%;${CRITICAL};${WARNING};0;100 Failed=${FAILEDPARSE}%;${CRITICAL};${WARNING};0;100”
return ${RETURNCODE}
}
genericspacecheck()
{
local CHECK=“$1”
local OBJECTNAME=“$2”
case ${CHECK,,} in
“undo” )
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 9999999.99
select TOTALSPACE.TOTAL TOTALSPACE,
TOTALSPACE.TOTAL-NVL(USEDSPACE.USED,0.0) FREESPACE
FROM (
SELECT TABLESPACENAME, SUM(BYTES)/1024/1024 TOTAL
FROM DBADATAFILES WHERE TABLESPACENAME = (SELECT value FROM V\\\$parameter WHERE name = 'undotablespace')
GROUP BY TABLESPACENAME
) TOTALSPACE
LEFT OUTER JOIN
(
SELECT TABLESPACENAME, SUM(BYTES)/1024/1024 USED
FROM DBAUNDOEXTENTS
WHERE (STATUS='UNEXPIRED' OR STATUS='ACTIVE')
AND TABLESPACENAME = (SELECT value FROM V\\\$parameter WHERE name = 'undotablespace')
GROUP BY TABLESPACENAME
) USEDSPACE
ON TOTALSPACE.TABLESPACENAME=USEDSPACE.TABLESPACENAME
;”
OBJECTNAME=UNDO
;;
“tablespace” )
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
SET NUMF 99999999.99
SELECT MAXMB,
REALFREEMB FREEMB
FROM (
SELECT MAXUSAGE.TABLESPACENAME,
MAXUSAGE.MAXMB,
CASE WHEN MAXUSAGE.ACTUALDATAFILEMB < MAXUSAGE.MAXMB THEN
MAXMB-(ACTUALDATAFILEMB-FREEMB)
ELSE
FREEMB
END REALFREEMB
FROM
(
select TABLESPACENAME,
SUM(case when MAXBYTES > 0 then MAXBYTES else BYTES END)/1024/1024 MAXMB,
SUM(BYTES)/1024/1024 ACTUALDATAFILEMB
FROM DBADATAFILES
GROUP BY TABLESPACENAME
) MAXUSAGE,
(
select TABLESPACENAME, SUM(BYTES)/1024/1024 FREEMB
FROM dbafreespace
GROUP BY TABLESPACENAME
) FREEUSAGE
WHERE MAXUSAGE.TABLESPACENAME=FREEUSAGE.TABLESPACENAME
AND MAXUSAGE.TABLESPACENAME = '${OBJECTNAME}'
)
;”
;;
“diskgroup” )
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
SET NUMF 9999999999
SELECT TOTALMB, NVL(FREEMB,0.0) FREEMB
FROM V\\\$ASMDISKGROUP
WHERE NAME='${OBJECTNAME}' ;”
;;
* )
echo “CRITICAL - Function genericspacecheck received wrong parameter : ${CHECK} ”
return ${STATECRITICAL}
;;
esac
local AUX=“${ORAENV} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local RESSULT=“$(${SSH} “${AUX}”)”
if | "${RESSULT}" =~ ^(([[:space:|)[0-9]{1,}){2}$ || ! “${RESSULT}” ]] ; then
echo “CRITICAL - ${ORACLESID} Error getting ${CHECK^} usage: ${RESSULT}”
return ${STATECRITICAL}
fi
local TOTAL=“$(echo ${RESSULT} | awk '{print $1}')”
local FREE=“$(echo ${RESSULT} | awk '{print $2}')”
local USED=“$(echo “${RESSULT}” | awk '{printf ”%2.2f“,$1-$2}')”
local PERCENT=“$(echo “${RESSULT}” | awk '{printf ”%2.2f“,100-($2/$1100)}')”
MSG=“$(returnvalues ${PERCENT/.})”
RETURNCODE=$?
echo “${MSG} - ${ORACLESID} ${CHECK^} ${OBJECTNAME} : ${PERCENT}%, Used : ${USED} of ${TOTAL} MB |${OBJECTNAME}=${PERCENT};${WARNING};${CRITICAL};0;20”
return ${RETURNCODE}
}
asmfsspacecheck()
{
local OBJECTNAME=“$1”
local AUX=“df -P ${OBJECTNAME} | egrep ${OBJECTNAME}”
local RESSULT=“$(${SSH} “${AUX}” 2> /dev/null)”
if ! "${RESSULT}" =~ ^\/.*%.*${OBJECTNAME}$ ; then
echo “CRITICAL - Error getting ${COMMAND} space for ${OBJECTNAME}”
return ${STATECRITICAL}
fi
local TOTAL=“$(echo ${RESSULT} | awk '{print $21024}')”
local FREE=“$(echo ${RESSULT} | awk '{print $41024}')”
local USED=“$(echo “${RESSULT}” | awk '{printf $31024}')”
local PERCENT=“$(echo “${RESSULT}” | awk '{printf $5}')”
local WARNINGVALUE=“$(echo ${TOTAL} ${WARNING} | awk '{printf ”%.2f“,$1$2/100}')”
local CRITICALVALUE=“$(echo ${TOTAL} ${CRITICAL} | awk '{printf ”%.2f“,$1$2/100}')”
MSG=“$(returnvalues ${PERCENT%/})”
RETURNCODE=$?
echo “${MSG} - ${CHECK^} ${OBJECTNAME} : ${PERCENT}, Used : $1) GB of $2) GB |size=${TOTAL}B used=${USED}B;${WARNINGVALUE};${CRITICALVALUE};0;${TOTAL}”
return ${RETURNCODE}
}
# This check needs SYS/ AS SYSDBA priviledge as connect to the standby (supposed to be in mount state)
dataguardstatsnew()
{
local ORAENVSID=“${ORAENV} export ORACLESID=${ORACLESID} ;”
local let RETURNCODE=${STATEUNKNOWN}
local SQLPLUS=“sqlplus -s '/ as sysdba'”
local TRANSPORTLAG=“”
local APPLYLAG=“”
local QUERY=“SET HEAD OFF
SET PAGES 0
SET FEED OFF
set numf 99999999999999
SELECT VALUE FROM V\\\$DATAGUARDSTATS ;”
local AUX=“${ORAENVSID} printf \”%s\n${QUERY}\“ | ${SQLPLUS}”
local RESSULT=“$(${SSH} “${AUX}”)”
if | "${RESSULT}" =~ ^(([[:space:|)[0-9]{1,}){4}$ ]] ; then
echo “CRITICAL - ${ORACLESID} Error getting dataguard data: ${RESSULT}”
return ${STATECRITICAL}
fi
TRANSPORTLAG=“$(echo “${RESSULT}” | head -1)”
APPLYLAG=“$(echo “${RESSULT}” | head -2| tail -1)”
# echo “Transport: ${TRANSPORTLAG}”
# echo “Apply: ${APPLYLAG}”
TRANSPORTLAGSECONDS=$(echo “(((${TRANSPORTLAG:1:2}24)+${TRANSPORTLAG:4:2})60+${TRANSPORTLAG:7:2})60+${TRANSPORTLAG:10:2}” | bc)
APPLYLAGSECONDS=$(echo “(((${APPLYLAG:1:2}24)+${APPLYLAG:4:2})60+${APPLYLAG:7:2})60+${APPLYLAG:10:2}” | bc)
if [ ${TRANSPORTLAGSECONDS} -gt ${APPLYLAGSECONDS} ] ; then
MSG=“$(returnvalues ${TRANSPORTLAGSECONDS})”
RETURNCODE=$?
else
MSG=“$(returnvalues ${APPLYLAGSECONDS})”
RETURNCODE=$?
fi
echo “${MSG} - Dataguard stats for ${ORACLESID} - TransportLag: ${TRANSPORTLAG} ApplyLag: ${APPLYLAG}|transport=${TRANSPORTLAGSECONDS} apply=${APPLYLAGSECONDS};${WARNING};${CRITICAL};0;86400”
return ${RETURNCODE}
}
########################################################################
#
# / FUNCTIONS
#
########################################################################
########################################################################
#
# VARIABLES
#
########################################################################
PROGNAME=$(basename $0)
PROGPATH=$(dirname $0)
[ $# -le 1 ] && usage $*
REMOTESERVER=$2 && shift 2
if ! ${REMOTE_SERVER}
then
echo “No remote server specified!!!”
exit ${STATEUNKNOWN}
fi
# Checking for non-standard user to connect
if "${1}" = "-U"
then
ORACLEOSUSER=$2 && shift 2
else
ORACLEOSUSER=“oracle”
fi
SSH=“ssh ${ORACLEOSUSER}@${REMOTESERVER} -xq”
ORACLEUSER=“NAGIOS”
ORACLEUSERPASSWORD=“NAGIOSPASSWORD”
########################################################################
#
# / VARIABLES
#
########################################################################
########################################################################
#
# MAIN
#
########################################################################
# CLEANING THE COMMAND
COMMAND=“${1:2}”
ORACLESID=“$2”
| ! "${ORACLE_SID}" && printusage && exit ${STATEUNKNOWN}
if [ $# -eq 4 ] ; then
CRITICAL=$3
WARNING=$4
elif [ $# -eq 5 ] ; then
TSDG=$3
CRITICAL=$4
WARNING=$5
fi
if "${WARNING}" && ${CRITICAL} ; then
if [ ${WARNING} -gt ${CRITICAL} ] ; then
echo “UNKNOWN - Warning level is less than Critical”
exit $STATEUNKNOWN
fi
fi
# if the check is not for asmfs, then a ORACLEHOME is necessary
if ! "${COMMAND}" = "asmfs" ; then
getremoteoraclehome
RES=$?
if [ ${RES} -ne ${STATEOK} ] ; then
echo “CRITICAL - No ORACLEHOME found”
exit $STATEUNKNOWN
fi
fi
# SQLPLUS FOR CONNECTIONS
SQLPLUS=“sqlplus -s '${ORACLEUSER}/${ORACLEUSERPASSWORD}@${ORACLESID}'”
ORAENV=“ORACLEHOME=${ORACLEHOME};PATH=$PATH:$ORACLEHOME/bin;LDLIBRARYPATH=$LDLIBRARYPATH:$ORACLEHOME/lib;export ORACLEHOME PATH LDLIBRARYPATH;”
case “${COMMAND}” in
“tns”)
checktns
RES=$?
if [ ${RES} -eq ${STATEOK} ] ; then
echo “OK - reply time ${BASHREMATCH[1]} from ${ORACLESID}”
else
echo “CRITICAL - No TNS Listener on ${ORACLESID}”
fi
;;
“db”)
checkdb
RES=$?
if [ ${RES} -eq ${STATEOK} ] ; then
echo “OK - ${ORACLESID} Database running”
else
echo “CRITICAL - ${ORACLESID} Database is DOWN”
fi
;;
“login”)
checklogin
RES=$?
if [ ${RES} -eq ${STATEOK} ] ; then
echo “OK - ${ORACLESID} dummy login connected”
else
echo “CRITICAL - ${ORACLESID} dummy login fail”
fi
;;
“sessions”)
checksessions
RES=$?
;;
“cache”)
checkcache
RES=$?
;;
“parsing”)
checkparsing
RES=$?
;;
“undo”)
genericspacecheck undo
RES=$?
;;
“tablespace”)
genericspacecheck tablespace “${TSDG}”
RES=$?
;;
“diskgroup”)
genericspacecheck diskgroup “${TSDG}”
RES=$?
;;
“asmfs”)
ASMFSDISK=${ORACLESID}
asmfsspacecheck ${ASMFSDISK}
RES=$?
;;
“dgstats”)
# dataguardstats
dataguardstatsnew
RES=$?
;;
“redogen”)
checkredogen
RES=$?
;;
*)
exit $STATEUNKNOWN
;;
esac
# EXIT STATUS
# STATEOK=0
# STATEWARNING=1
# STATECRITICAL=2
# STATEUNKNOWN=3
# STATEDEPENDENT=4
# /EXIT STATUS
#WIP
exit ${RES}
</file>