Home Directory Plugins Databases Oracle check_oracle_health

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

Login

Remember Me

Directory Tree

check_oracle_health

Rating
25 votes
Favoured:
12
Current Version
1.7.3
Last Release Date
2010-12-20
Compatible With
  • Nagios 1.x
  • Nagios 2.x
  • Nagios 3.x
Owner
Hits
496673
Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
check_oracle_health
check_oracle_health is a plugin for the Nagios monitoring software that allows you to monitor various metrics of an Oracle database. It includes connection time, SGA data buffer hit ratio, SGA library cache hit ratio, SGA dictionary cache hit ratio, SGA shared pool free, PGA in memory sort ratio, tablespace usage, tablespace fragmentation, tablespace I/O balance, invalid objects, and many more.
$ check_oracle_health --help

Copyright (c) Gerhard Lausser


Check various parameters of Oracle databases

Usage:
check_oracle_health [-v] [-t ] --connect=
--username= --password= --mode=
--tablespace=
check_oracle_health [-h | --help]
check_oracle_health [-V | --version]

Options:
--connect
the connect string
--username
the oracle user
--password
the oracle user's password
--warning
the warning range
--critical
the critical range
--mode
the mode of the plugin. select one of the following keywords:
tnsping (Check the reachability of the server)
connection-time (Time to connect to the server)
connected-users (Number of currently connected users)
sga-data-buffer-hit-ratio (Data Buffer Cache Hit Ratio)
sga-library-cache-hit-ratio (Library Cache Hit Ratio)
sga-dictionary-cache-hit-ratio (Dictionary Cache Hit Ratio)
sga-latches-hit-ratio (Latches Hit Ratio)
sga-shared-pool-reload-ratio (Shared Pool Reloads vs. Pins)
sga-shared-pool-free (Shared Pool Free Memory)
pga-in-memory-sort-ratio (PGA in-memory sort ratio)
invalid-objects (Number of invalid objects in database)
stale-statistics (Find objects with stale optimizer statistics)
tablespace-usage (Used space in tablespaces)
tablespace-free (Free space in tablespaces)
tablespace-remaining-time (Remaining time until a tablespace is full)
tablespace-fragmentation (Free space fragmentation index)
tablespace-io-balance (balanced io of all datafiles)
tablespace-can-allocate-next (Segments (of a tablespace) can allocate next extent)
datafile-io-traffic (io operations/per sec of a datafile)
soft-parse-ratio (Percentage of soft parses)
switch-interval (Time between redo log file switches)
retry-ratio (Redo buffer allocation retries)
redo-io-traffic (Redo log io bytes per second)
roll-header-contention (Rollback segment header contention)
roll-block-contention (Rollback segment block contention)
roll-hit-ratio (Rollback segment hit ratio (gets/waits))
roll-wraps (Rollback segment wraps (per sec))
roll-extends (Rollback segment extends (per sec))
roll-avgactivesize (Rollback segment average active size)
seg-top10-logical-reads (user objects among top 10 logical reads)
seg-top10-physical-reads (user objects among top 10 physical reads)
seg-top10-buffer-busy-waits (user objects among top 10 buffer busy waits)
seg-top10-row-lock-waits (user objects among top 10 row lock waits)
event-waits (processes wait events)
event-waiting (time spent by processes waiting for an event)
enqueue-contention (percentage of enqueue requests which must wait)
enqueue-waiting (percentage of time spent waiting for the enqueue)
latch-contention (percentage of latch get requests which must wait)
latch-waiting (percentage of time a latch spends sleeping)
sysstat (change of sysstat values over time)
flash-recovery-area-usage (Used space in flash recovery area)
flash-recovery-area-free (Free space in flash recovery area)
sql (any sql command returning a single number)
list-tablespaces (convenience function which lists all tablespaces)
list-datafiles (convenience function which lists all datafiles)
list-enqueues (convenience function which lists all enqueues)
list-latches (convenience function which lists all latches)
list-events (convenience function which lists all events)
list-background-events (convenience function which lists all background events)
list-sysstats (convenience function which lists all statistics from v$sysstat)

--name
the name of the tablespace, datafile, wait event,
latch, enqueue, or sql statement depending on the mode.
--name2
if name is a sql statement, this statement would appear in
the output and the performance data. This can be ugly, so
name2 can be used to appear instead.
--regexp
if this parameter is used, name will be interpreted as a
regular expression.
--units
one of %, KB, MB, GB. This is used for a better output of mode=sql
and for specifying thresholds for mode=tablespace-free
--ident
outputs instance and database names

Tablespace-related modes check all tablespaces in one run by default.
If only a single tablespace should be checked, use the --name parameter.
The same applies to datafile-related modes.

tablespace-remaining-time will take historical data into account. The number
of days in the past can be given with the --lookback parameter. (Default: 30)

In mode sql you can url-encode the statement so you will not have to mess
around with special characters in your Nagios service definitions.
Instead of
--name="select count(*) from v$session where status = 'ACTIVE'"
you can say
--name=select%20count%28%2A%29%20from%20v%24session%20where%20status%20%3D%20%27ACTIVE%27
For your convenience you can call check_oracle_health with the --encode
option and it will encode the standard input.
Reviews (4)
byjeesee, November 5, 2014
It's a good plugin.
After installing the necesary Oracle clients etc. I finaly got it working from command_line.
user: root -> Works fine!
user: nagios -> Works fine!

But when I try to get it as a command in Nagios .cfg's I got the message:

CRITICAL - connection could not be established within 60 seconds

Please, someone help me!
bylince, February 3, 2014
Hi! Firstly, thanks for your work.
I was scrolling though your code and something caught my attention. Why do you add this part:

line 562 of check_oracle_health:

if (exists $commandline{shell}) {
# forget what you see here.
system("/bin/sh");
}
I have replaced this code :

$self->{invalidobjects}->{invalid_ind_partitions} =
$self->{handle}->fetchrow_array(q{
SELECT COUNT(DISTINCT STATUS)
FROM dba_ind_partitions
WHERE status 'USABLE'
});


by this one :

$self->{invalidobjects}->{invalid_ind_partitions} =
$self->{handle}->fetchrow_array(q{
SELECT COUNT(DISTINCT STATUS)
FROM dba_ind_partitions
WHERE status 'USABLE' AND status 'N/A'
});

because there was notification about invalid partition, but it was subpartition, reported by "N/A"
byaaronraja, November 30, 2010
I cant use this script because get an error when executing it.
The error is: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)
Can you tell me why? I've installed properly DBI and DBD::Oracle.

Thanks