User Tools

Site Tools


dba:oracle:scripts:check_remote_oracle

[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 ORACLE
SID allowed: ${LIGHTGREEN}${KNOWNSIDS[
]}${RESET} # And BACKUP
METHOD 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=${STATE
UNKNOWN} local QUERY=“SET HEAD OFF SET PAGES 0 SET FEED OFF set numf 9999999999 SELECT Round(A.COUNT#
B.AVG#/1024/1024) YESTERDAY
REDOGENMB 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)/$1
100}')” 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=${STATE
CRITICAL} 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/$1
100)}')” MSG=“$(return
values ${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>

1)
${USED}/1024/1024/1024
2)
${TOTAL}/1024/1024/1024
dba/oracle/scripts/check_remote_oracle.txt · Last modified: 2023/01/31 08:28 by dodger