Home Directory Plugins Databases Oracle yaonp - yet another oracle nagios plugin

Search Exchange

Search All Sites

Nagios Live Webinars

Let our experts show you how Nagios can help your organization.

Contact Us

Phone: 1-888-NAGIOS-1
Email: sales@nagios.com


Remember Me

Directory Tree

yaonp - yet another oracle nagios plugin

2 votes
Last Release Date
Compatible With
  • Nagios 1.x
  • Nagios 2.x
  • Nagios 3.x
Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
yaonp - yet another oracle nagios plugin

for a short overview a list of advantages and disadvantages for this plugin


- it works
at least for my test enviroment consisting of Nagios XI (Linux, Ubuntu), Oracle 11g2 on Solaris 10 (Sparc)
as it uses only basic functionality it should work with all Nagios releases
- uses no additional perl modules
- needs no other software components, except a standard oracle installation with available sqlplus
- many tests, each for it self may be activates or deactivated. Following tests are currently implemented:
# Dictionary Cache Miss Ratio
# Library Cache Miss Ratio
# DB Block Buffer Cache Miss Ratio
# redo buffer allocation entries/retries ratio
# Latch Miss Ratio
# Disk Sort Ratio
# Rollback Segment Waits
# Dispatcher Workload
# Count Sessions
# Number of locked objects
# Number of locks blocking other locks
# Number of sessions blocked by other sessions longer than a minute
# Tablespace Usage
- no additional rights needed for nagios user
- simple configuration, just some variables in the scripts have to be filled
- written to be easy extensible, even by Oracle DBAs who don't know much perl


- uses two scripts, not only one like many other plugins
- even worse, one of the scripts has to be run from an privileged account as "SELECT"-right is needed on v$- and dba_-views
- it is version 1.0, there may be some minor bugs and glitches, please report if you find one.
Installation (all steps on the host running oracle, except 5., 6. and 9.):

1. extract the files, yaonp-collector.pl and yaonp-plugin.pl
2. edit the files, place the correct values to the variables
2.a. if you don't like to run yaonp-collector.pl with your oracle or instance user, create a new one with "SELECT"-right on v$- and dba_-views
3. copy yaonp-collector.pl to a location where it can be executet by the user from 2.a.
4. copy yaonp-plugin.pl to your nagios plugin directory
5. edit your checkcommands.cfg on your nagios host and insert the new commands there, e.g.

define command {
command_name ora_dictcachmisses
command_line $USER1$/check_nrpe -H $HOS
TADDRESS$ -c ora_dictcachmisses

6. edit your services.cfg on your nagios host to map the tests to hosts, e.g.

define service {
service_description ora_dictcachmisses
check_command ora_dictcachmisses!
host_name ozone
check_period 24x7
event_handler_enabled 0
max_check_attempts 3
check_interval 5
retry_interval 1
active_checks_enabled 1
passive_checks_enabled 0
notifications_enabled 1
check_freshness 0
freshness_threshold 86400
contact_groups admins

6. edit your nrpe.cfg and insert the checks, e.g.

command[ora_dictcachmisses]=/usr/local/nagios/libexec/yaonp-plugin.pl dictcachmisses -w 10% -c 25%

7. insert the call of the yaonp-collector.pl in the calling users crontab, e.g.

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /usr/xpg4/bin/sh -c '. /export/home/oracle/.profile ; /usr/bin/perl /export/home/oracle/yaonp-collector.pl'

8. reload/restart your nrpe, if not started from inetd
9. reload/restart nagios on your nagios host
10. be happy

Reviews (2)
by, August 21, 2012
I installed yaonp, if I send nagios server to oracle server in command line :./check_nrpe -H PUO32044-PC -c ora_numsessions
OK: 30 Count Sessions|'Count Sessions'=30
I get output, but I get "not output returned" on nagios3 web site.
Could you help me.

i have installed your yaonp-collector.pl in my oracle db machine. to test i have executed your script.

#perl yaonp-collector.pl

dictcachmisses|9.7|||Dict Cache Miss Ratio=%|
libcachmisses|5.7|||Lib Cache Miss Ratio=%|
dbblckbufmisses|2.6|||Block Buffer Cache Miss=%|
redoentryretr|0|||redo buff alloc entries/retries=%|
latchmisses|0|||Latch Miss=%|
disksorts|0|||Disk Sort=%|
rollbckwa|0|||Rollback Segment Waits=%|
dispworkl|0|||Dispatcher Workload=%|
numsessions|23|||Count Sessions=|
numlckedobj|0|||Number of locked objects=|
numlckblcklck|0|||Number of locks blocking other locks=|
numblcksess|0|||Number of sessions blocked by other sessions=|
tbspcuse|5|SYSAUX||Tablespace Usage=%|
tbspcuse|59|SYSTEM||Tablespace Usage=%|
tbspcuse|0|TEMP||Tablespace Usage=%|
tbspcuse|0|UNDOTBS1||Tablespace Usage=%|
tbspcuse|0|USERS||Tablespace Usage=%|

those are the results but i want to modify your one to do the following;

select current_utilization from v$resource_limit where resource_name ='sessions';

this would give a numeric number and i want to alert based on the number retrieved.

i have tried to modify your plugin SQL's but yet no luck ,,,,, can you give me an idea.

anyideas owner of the plugin?
Owner's reply

Sorry for answering your question so late. But i had problems with my internet connection and was most time offline. Even now it is very slow and often disconnecting.

But now back to topic. Here is a small, step by step, example how to extend yaonp:

1. on oracle host

1. open yaonp-collector.pl, go to the section where the checks are defined and copy/paste the last check, three lines. E.g.

$SQL[12]='SELECT \'tbspcuse\'||\'|\'||ROUND(USED_PERCENT)||\'|\'||TABLESPACE_NAME||\'|\'||\'|Tablespace Usage=%|\' FROM dba_tablespace_usage_metrics;';

2. modify this lines to your needs
a. give the test a name. Eg. sessuti
b. count the array index up by one
c. edit the SQL-Statement to your needs and adjust the output fitting for yaonp-plugin.pl. E.g.

select current_utilization from v$resource_limit where resource_name ='sessions';

will be

'SELECT \'sessuti\'||\'|\'||current_utilization||\'|\'||\'|\'||\'|Session Utilization=%|\'FROM v$resource_limit WHERE resource_name =\'sessions\';';

the resulting lines should look like:

$SQL[13]='SELECT \'sessuti\'||\'|\'||current_utilization||\'|\'||\'|\'||\'|Session Utilization=%|\'FROM v$resource_limit WHERE resource_name =\'sessions\';';

3. test it

ozone:~$ ./yaonp-collector.pl |grep sess
numsessions|24|||Count Sessions=|
numblcksess|0|||Number of sessions blocked by other sessions=|
sessuti|31|||Session Utilization=%|

looks good.

4. edit nrpe.cfg and insert the new check, e.g.

command[ora_sessuti]=/usr/local/nagios/libexec/yaonp-plugin.pl sessuti -w 50% -c 75%

5. reload/restart nrpe.

All done on oracle host.

2. on nagios host

1. edit checkcommands.cfg and insert the new command, e.g.

define command {
command_name ora_sessuti
command_line $USER1$/check_nrpe -H $HOSTADDRESS$ -c ora_sessuti

2. edit services.cfg, e.g.

define service {
service_description ora_sessuti
check_command ora_sessuti!
host_name ozone
check_period 24x7
event_handler_enabled 0
max_check_attempts 3
check_interval 5
retry_interval 1
active_checks_enabled 1
passive_checks_enabled 0
notifications_enabled 1
check_freshness 0
freshness_threshold 86400
contact_groups admins

3. reload/restart nagios/icinga on your nagios host
4. done