{"id":123,"date":"2012-04-19T11:13:13","date_gmt":"2012-04-19T11:13:13","guid":{"rendered":"http:\/\/selectsysdatefromdual.gnusystems.net\/?page_id=123"},"modified":"2012-05-10T09:08:27","modified_gmt":"2012-05-10T09:08:27","slug":"nagios-plugin-check_remote_oracle","status":"publish","type":"page","link":"https:\/\/selectsysdatefromdual.gnusystems.net\/?page_id=123","title":{"rendered":"Nagios plugin check_remote_oracle"},"content":{"rendered":"

New version 0.3 to fetch real undo usage (“UNEXPIRED SEGMENTS”) !!!<\/p>\n

We picked up the nagios plugin check_oracle and made some upgrades so it can monitor remote databases with no need of installing a nagios agent on the Oracle Database Server or the Oracle client on the nagios server.<\/p>\n

The only requisite is having an ssh daemon running on the oracle server and copy the nagios user ssh key to that oracle server so nagios can connect through ssh with no password.<\/p>\n

The first thing we change is the way to get the ORACLE_HOME environment variable from the oratab file, just because… IT DIDN’T WORKED! <\/p>\n

When an oratab contained commented lines (“#”) the logic of the script produced a messed out output, so, firstly, we make the code to ignore the lines starting by a comment “^#” from the oratab file, and then it works (locally). <\/p>\n

Then, we implemented the solution to ssh the server and get the ORACLE_HOME remotelly.<\/p>\n

Here’s the original code from the original check_oracle plugin:<\/p>\n

# Hunt down a reasonable ORACLE_HOME\r\nif [ -z \"$ORACLE_HOME\" ] ; then\r\n        # Adjust to taste\r\n        for oratab in \/var\/opt\/oracle\/oratab \/etc\/oratab\r\n        do\r\n        [ ! -f $oratab ] && continue\r\n        ORACLE_HOME=`IFS=:\r\n                while read SID ORACLE_HOME junk;\r\n                do\r\n                        if [ \"$SID\" = \"$2\" -o \"$SID\" = \"*\" ] ; then\r\n                                echo $ORACLE_HOME;\r\n                                exit;\r\n                        fi;\r\n                done < $oratab`\r\n        [ -n \"$ORACLE_HOME\" ] && break\r\n        done\r\nfi\r\n# Last resort\r\n[ -z \"$ORACLE_HOME\" -a -d $PROGPATH\/oracle ] && ORACLE_HOME=$PROGPATH\/oracle\r\n\r\nif [ \"$cmd\" != \"--db\" ]; then\r\n        if [ -z \"$ORACLE_HOME\" -o ! -d \"$ORACLE_HOME\" ] ; then\r\n                echo \"Cannot determine ORACLE_HOME for sid $2\"\r\n                exit $STATE_UNKNOWN\r\n        fi\r\nfi<\/pre>\n
And here the code modified to ignore commented lines and parse a remote oratab with ssh:<\/p>\n
# Hunt down a reasonable ORACLE_HOME\r\nGET_ORACLE_HOME='if [ -z \"$ORACLE_HOME\" ] ; then\r\n        # Adjust to taste\r\n        for oratab in \/var\/opt\/oracle\/oratab \/etc\/oratab\r\n        do\r\n        [ ! -f $oratab ] && continue\r\n        ORACLE_HOME=`IFS=:\r\n                while read SID ORACLE_HOME junk;\r\n                do\r\n                        if [ \"$SID\" = \"$ORACLE_SID\" -o \"$SID\" = \"*\" ] ; then\r\n                                echo $ORACLE_HOME;\r\n                                exit;\r\n                        fi;\r\n                done << EOF\r\n$(grep -v ^# $oratab)\r\nEOF`\r\n        [ -n \"$ORACLE_HOME\" ] && echo $ORACLE_HOME ; break\r\n        echo $ORACLE_HOME\r\n        done\r\nfi'\r\nORACLE_HOME=`ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \"ORACLE_SID=${2} ; ${GET_ORACLE_HOME}\"`\r\n\r\nif [ \"$cmd\" != \"--db\" ]; then\r\n        if [ -z \"$ORACLE_HOME\" -o ! `ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \"ls -d ${ORACLE_HOME}\"` ] ; then\r\n                echo \"Cannot determine ORACLE_HOME for sid $2\"\r\n                exit $STATE_UNKNOWN\r\n        fi\r\nfi<\/pre>\n
Now, we have to get these parameters we've introduced on the code, for instance, $ORACLE_OSUSER and $REMOTE_SERVER:<\/p>\n
REMOTE_SERVER=$2 && shift 2\r\nif [ -z REMOTE_SERVER ]\r\nthen\r\n        echo \"No remote server specified!!!\"\r\n        exit $STATE_UNKNOWN\r\nfi\r\n\r\n# Parametres: Si $3=\"-U\" llavors $4 es el OracleOSUser, si no, no.\r\n\r\nif [ \"${1}\" = \"-U\" ]\r\nthen\r\n        ORACLE_OSUSER=$2 && shift 2\r\nelse\r\n        ORACLE_OSUSER=\"oracle\"\r\nfi<\/pre>\n
Now we've got all the parameters we need to connect through ssh to a remote Oracle database without having to install the Oracle client locally. <\/p>\n
So we just keep introducing the ssh command to the checks passed to the database.<\/p>\n
This is the listener check command:<\/p>\n
case \"$cmd\" in\r\n--tns)\r\n    tnschk=`ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \"ORACLE_HOME=${ORACLE_HOME};PATH=$PATH:$ORACLE_HOME\/bin;LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME\/lib;export ORACLE_HOME PATH LD_LIBRARY_PATH; tnsping $2\"`\r\n    tnschk2=` echo  $tnschk | grep -c OK`\r\n    if [ ${tnschk2} -eq 1 ] ; then\r\n        tnschk3=` echo $tnschk | sed -e 's\/.*(\/\/' -e 's\/).*\/\/'`\r\n        echo \"OK - reply time ${tnschk3} from $2\"\r\n        exit $STATE_OK\r\n    else\r\n        echo \"No TNS Listener on $2\"\r\n        exit $STATE_CRITICAL\r\n    fi\r\n    ;;<\/pre>\n
This one, is the pmon database check command:<\/p>\n
--db)\r\n    pmonchk=`ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \"ps -ef | grep -v grep | grep -c ora_pmon_${2}\"`\r\n    if [ ${pmonchk} -ge 1 ] ; then\r\n        echo \"${2} OK - ${pmonchk} PMON process(es) running\"\r\n        exit $STATE_OK\r\n    else\r\n        echo \"${2} Database is DOWN\"\r\n        exit $STATE_CRITICAL\r\n    fi\r\n    ;;<\/pre>\n
Next, for the rest of the options, that involved querying the database, we had to develop a function \"connect_to_server\" in order to be able to escape the \"$\" sign from the  V$_ views on the ssh command:<\/p>\n
    connect_to_server(){\r\n        ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \"ORACLE_HOME=${ORACLE_HOME};PATH=$PATH:$ORACLE_HOME\/bin;LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME\/lib;ORACLE_SID=${ORACLE_SID} ;export ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH;$GET_RESU\r\nLT\"\r\n        exit\r\n}<\/pre>\n
So, the DB caches check ends up like this:<\/p>\n
\r\n--cache)\r\n    if [ \"$#\" -ne 4 ] ; then \r\n        echo \"Wrong Parameter Count!!!\"\r\n        exit\r\n    fi\r\nORACLE_SID=${2}\r\n\r\n    if [ ${3} -gt ${4} ] ; then\r\n        echo \"UNKNOWN - Warning level is less than Critical\"\r\n        exit $STATE_UNKNOWN\r\n    fi\r\n\r\n    connect_to_server(){\r\n        ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \"ORACLE_HOME=${ORACLE_HOME};PATH=$PATH:$ORACLE_HOME\/bin;LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME\/lib;ORACLE_SID=${ORACLE_SID} ;export ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH;$GET_RESU\r\nLT\"\r\n        exit\r\n}\r\n\r\n    GET_RESULT=\"sqlplus -s \/ as sysdba << EOF\r\nset pagesize 0\r\nset numf '9999999.99'\r\nselect (1-(pr.value\/(dbg.value+cg.value)))*100\r\nfrom v\\\\\\$sysstat pr, v\\\\\\$sysstat dbg, v\\\\\\$sysstat cg\r\nwhere pr.name='physical reads'\r\nand dbg.name='db block gets'\r\nand cg.name='consistent gets'\r\n\/\r\nEOF\"\r\n    result=`connect_to_server`;\r\n\r\n    if [ -n \"`echo $result | grep ORA-`\" ] ; then\r\n      error=` echo \"$result\" | grep \"ORA-\" | head -1`\r\n      echo \"CRITICAL - $error\"\r\n      exit $STATE_CRITICAL\r\n    fi\r\n\r\n    buf_hr=`echo \"$result\" | awk '\/^[0-9\\. \\t]+$\/ {print int($1)}'` \r\n    buf_hrx=`echo \"$result\" | awk '\/^[0-9\\. \\t]+$\/ {print $1}'` \r\n    GET_RESULT=\"sqlplus -s \/ as sysdba << EOF\r\nset pagesize 0\r\nset numf '9999999.99'\r\nselect sum(lc.pins)\/(sum(lc.pins)+sum(lc.reloads))*100\r\nfrom v\\\\\\$librarycache lc;\r\nEOF\"\r\n        \r\n    result=`connect_to_server`;\r\n\r\n    if [ -n \"`echo $result | grep ORA-`\" ] ; then\r\n      error=` echo \"$result\" | grep \"ORA-\" | head -1`\r\n      echo \"CRITICAL - $error\"\r\n      exit $STATE_CRITICAL\r\n    fi\r\n\r\n    lib_hr=`echo \"$result\" | awk '\/^[0-9\\. \\t]+$\/ {print int($1)}'`\r\n    lib_hrx=`echo \"$result\" | awk '\/^[0-9\\. \\t]+$\/ {print $1}'`\r\n\r\n    if [ $buf_hr -le ${3} -o $lib_hr -le ${3} ] ; then\r\n        echo \"${2} CRITICAL - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${4};${3};0;100 buffer=$buf_hrx%;${4};${3};0;100\"\r\n        exit $STATE_CRITICAL\r\n    fi\r\n    if [ $buf_hr -le ${4} -o $lib_hr -le ${4} ] ; then\r\n        echo \"${2} WARNING  - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${4};${3};0;100 buffer=$buf_hrx%;${4};${3};0;100\"\r\n        exit $STATE_WARNING\r\n    fi\r\n    echo \"${2} OK - Cache Hit Rates: $lib_hrx% Lib -- $buf_hrx% Buff|lib=$lib_hrx%;${4};${3};0;100 buffer=$buf_hrx%;${4};${3};0;100\"\r\n\r\n    exit $STATE_OK\r\n    ;;<\/pre>\n
Finally, here the tablespaces check:<\/p>\n
--tablespace)\r\n    if [ \"$#\" -ne 5 ] ; then \r\n        echo \"Wrong Parameter Count!!!\"\r\n        exit\r\n    fi\r\n    if [ ${4} -lt ${5} ] ; then\r\n        echo \"UNKNOWN - Warning level is more than Critical\"\r\n        exit $STATE_UNKNOWN\r\n    fi\r\n\r\n    ORACLE_SID=$2\r\n\r\n    connect_to_server(){\r\n        ssh $ORACLE_OSUSER@$REMOTE_SERVER -x \" ORACLE_HOME=${ORACLE_HOME};PATH=$PATH:$ORACLE_HOME\/bin;LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME\/lib;ORACLE_SID=${ORACLE_SID} ;export ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH;$GET_RESULT\"\r\n        exit\r\n}\r\n\r\n    GET_RESULT=\"sqlplus -s \/ as sysdba << EOF\r\nset pagesize 0\r\nset numf '9999999.99'\r\nselect NVL(b.free,0.0),a.total,100 - trunc(NVL(b.free,0.0)\/a.total * 1000) \/ 10 prc\r\nfrom (\r\nselect tablespace_name,sum(bytes)\/1024\/1024 total\r\nfrom dba_data_files group by tablespace_name) A\r\nLEFT OUTER JOIN\r\n( select tablespace_name,sum(bytes)\/1024\/1024 free\r\nfrom dba_free_space group by tablespace_name) B\r\nON a.tablespace_name=b.tablespace_name WHERE a.tablespace_name='${3}';\r\nEOF\"\r\n\r\n    result=`connect_to_server`;\r\n\r\n    if [ -n \"`echo $result | grep ORA-`\" ] ; then\r\n      error=` echo \"$result\" | grep \"ORA-\" | head -1`\r\n      echo \"CRITICAL - $error\"\r\n      exit $STATE_CRITICAL\r\n    fi\r\n\r\n    ts_free=`echo \"$result\" | awk '\/^[ 0-9\\.\\t ]+$\/ {print int($1)}'` \r\n    ts_total=`echo \"$result\" | awk '\/^[ 0-9\\.\\t ]+$\/ {print int($2)}'` \r\n    ts_pct=`echo \"$result\" | awk '\/^[ 0-9\\.\\t ]+$\/ {print int($3)}'` \r\n    ts_pctx=`echo \"$result\" | awk '\/^[ 0-9\\.\\t ]+$\/ {print $3}'` \r\n    if [ \"$ts_free\" -eq 0 -a \"$ts_total\" -eq 0 -a \"$ts_pct\" -eq 0 ] ; then\r\n        echo \"No data returned by Oracle - tablespace $5 not found?\"\r\n        exit $STATE_UNKNOWN\r\n    fi\r\n    if [ \"$ts_pct\" -ge ${4} ] ; then\r\n        echo \"${2} : ${3} CRITICAL - $ts_pctx% used [ $ts_free \/ $ts_total MB available ]|${3}=$ts_pctx%;${5};${4};0;100\"\r\n        exit $STATE_CRITICAL\r\n    fi\r\n    if [ \"$ts_pct\" -ge ${5} ] ; then\r\n        echo \"${2} : ${3} WARNING  - $ts_pctx% used [ $ts_free \/ $ts_total MB available ]|${3}=$ts_pctx%;${5};${4};0;100\"\r\n        exit $STATE_WARNING\r\n    fi\r\n    echo \"${2} : ${3} OK - $ts_pctx% used [ $ts_free \/ $ts_total MB available ]|${3}=$ts_pctx%;${5};${4};0;100\"\r\n    exit $STATE_OK\r\n    ;;<\/pre>\n
And here the help of the plugin:<\/p>\n
su - nagios -c \"\/usr\/local\/nagios\/libexec\/check_remote_oracle --help\"\r\ncheck_remote_oracle v0.1 (nagios-plugins 1.4.15)\r\nThe nagios plugins come with ABSOLUTELY NO WARRANTY. You may redistribute\r\ncopies of the plugins under the terms of the GNU General Public License.\r\nFor more information about these matters, see the file named COPYING.\r\n\r\nUsage:\r\n  check_remote_oracle -H  [-U Oracle OS User] --tns \r\n  check_remote_oracle -H  [-U Oracle OS User] --db \r\n  check_remote_oracle -H  [-U Oracle OS User] --login \r\n  check_remote_oracle -H  [-U Oracle OS User] --cache   \r\n  check_remote_oracle -H  [-U Oracle OS User] --tablespace    \r\n  check_remote_oracle --help\r\n  check_remote_oracle --version\r\n\r\nCheck Oracle status\r\n\r\n--tns SID\/IP Address\r\n   Check remote TNS server\r\n--db SID\r\n   Check local database (search \/bin\/ps for PMON process)\r\n--login SID\r\n   Attempt a dummy login and alert if not ORA-01017: invalid username\/password\r\n--cache\r\n   Check local database for library and buffer cache hit ratios\r\n--tablespace\r\n   Check local database for tablespace capacity in ORACLE_SID\r\n--help\r\n   Print this help screen\r\n--version\r\n   Print version and license information\r\n\r\nIf the plugin doesn't work, check that the ORACLE_HOME environment\r\nvariable is set, that ORACLE_HOME\/bin is in your PATH, and the\r\ntnsnames.ora file is locatable and is properly configured on your Oracle server.\r\n\r\nIf you want to use a default Oracle home, add in your oratab file:\r\n*:\/opt\/app\/oracle\/product\/7.3.4:N\r\n\r\nSend email to nagios-users@lists.sourceforge.net if you have questions\r\nregarding use of this software. To submit patches or suggest improvements,\r\nsend email to nagiosplug-devel@lists.sourceforge.net.\r\nPlease include version information with all correspondence (when possible,\r\nuse output from the --version option of the plugin itself).<\/pre>\n
Enjoy it!<\/p>\n
You can download the plugin, here<\/a>.
\n(New version 0.2 with active sessions count, ASM diskgroups and hard\/soft parsing percentage ratios check)<\/p>\n","protected":false},"excerpt":{"rendered":"
New version 0.3 to fetch real undo usage (“UNEXPIRED SEGMENTS”) !!! We picked up the nagios plugin check_oracle and made some upgrades so it can monitor remote databases with no need of installing a nagios agent on the Oracle Database Server or the Oracle client on the nagios server. The only requisite is having an […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":[],"_links":{"self":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages\/123"}],"collection":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=123"}],"version-history":[{"count":9,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages\/123\/revisions"}],"predecessor-version":[{"id":133,"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=\/wp\/v2\/pages\/123\/revisions\/133"}],"wp:attachment":[{"href":"https:\/\/selectsysdatefromdual.gnusystems.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=123"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}