Home Directory Plugins Databases Oracle Check Oracle status & health without install Oracle client

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

Check Oracle status & health without install Oracle client Popular

Network Monitoring Software - Download Nagios XI
Log Management Software - Nagios Log Server - Download
Netflow Analysis Software - Nagios Network Analyzer - Download
Rating
14 votes
Favoured:
3
Hits
704969
Files:
FileDescription
check_oracle_instantcheck_oracle_instant
This perl nagios plugin allow you to check oracle service (ability to connect to database ) and health of oracle databse (Dictionary Cache Hit Ratio,Library Cache Hit Ratio,DB Block Buffer Cache Hit Ratio,Latch Hit Ratio,Disk Sort Ratio,Rollback Segment Waits,Dispatcher Workload) is possible define own parameters.Big advantage is that it does not need to install ORACLE client or compile other perl modules.
Instalation steps:

1/ download oracle instant client from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html Instant Client Package - Basic and Instant Client Package - SQL*Plus unzip it and make sqlplus runnable from anywhere ( set new PATH or copy to defined PATH) and test SQLPLUS sqlplus user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=IP or hostname)(Port=port))(CONNECT_DATA=(SID=sid)))

2/ copy check_oracle_instant script to libexec directory (on linux /usr/local/nagios/libexec)
and set correct rights and owner

3/ set /usr/local/nagios/objects/etc/commands.cfg add define own values

# ### CHECK ORACLE ###
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$
}

4/ define service add and set own values

define service{
use profile name
host_name hostname
service_description ORACLE: check_login_health
check_command check_oracle_instant!port!sid!login!passwd
}

5/ restart nagios and that is all :-)
Reviews (12)
I am able to get proper output of the command when I am using it from the shell of Nagios server.
But when I am trying get it executed through service in Nagios, it is saying 'Unable to connect to ORACLE orcl, error !!!'
Is there I am missing something to configure in Nagios?
bybangers, October 30, 2014
This plugin is very good in the way that you don't need the Oracle client. I did change the script to suit my needs: split the checks, add perfdata and variable thresholds.


$host = $ARGV[0];
$port = $ARGV[1];
$sid = $ARGV[2];
$user = $ARGV[3];
$pass = $ARGV[4];
$check = $ARGV[5];
$thresholdw = $ARGV[6];
$thresholdc = $ARGV[7];

$ENV{LD_LIBRARY_PATH} = "/etc/oracle";
$sqlplus = '/etc/oracle/sqlplus';

sub trim($);
my %ERRORS=('OK'=>0,'WARNING'=>1,'CRITICAL'=>2,'UNKNOWN'=>3);

my %checks = (
'dictionary' => 0,
'library' => 1,
'blockbuffer' => 2,
'latch' => 3,
'disk' => 4,
'rollback' => 5,
'dispatcher' => 6,
);

my @param_array = (
[">","Dictionary Cache Hit Ratio",'SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 FROM v\$rowcache;'],
[">","Library Cache Hit Ratio",'SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 FROM v\$librarycache;'],
[">","DB Block Buffer Cache Hit Ratio",'SELECT (1 - (phys.value / (db.value + cons.value))) * 100 FROM v\$sysstat phys,v\$sysstat db,v\$sysstat cons WHERE phys.name = \'physical reads\' AND db.name = \'db block gets\' AND cons.name = \'consistent gets\';'],
[">","Latch Hit Ratio",'SELECT (1 - (Sum(misses) / Sum(gets))) * 100 FROM v\$latch;'],
[" "unless (".$results.$param_array[$checks{$check}][0].$thresholdw.") {print\"".$param_array[$checks{$check}][1]." on ".$sid." is WARNING ($results !$param_array[$checks{$check}][0] $thresholdw)\\n| $perfcounter\"; exit ".$ERRORS{"WARNING"}.";}";

print "$param_array[$checks{$check}][1] on $sid is OK ($results $param_array[$checks{$check}][0] $thresholdw)|$perfcounter";
exit $ERRORS{"OK"};

} else {print "Bad check - values are: dictionary,library,blockbuffer,latch,disk,rollback,dispatcher\n "; exit $ERRORS{"UNKNOWN"};}
This is an excellent script. I had to made few changes for making it to work with Oracle 12c PDBs.

First, my sqlplus binary is 64 bits, so I edited the script changing it for 'sqlplus64'.

In Oracle 12c you can use a "Container" and put many datbases in there, as PDB. But in this case, they don't use the variable SID, instead, you must use SERVICE_NAME.

So, I changed this:
==> sub logon {
==> open (SQL,"sqlplus -s aloha/teste@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\) sub logon {
==> open (SQL,"sqlplus64 -s aloha/teste@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SERVICE_NAME=$sid\\)\\)\\)racle 12c
Therefore i rewrote this script, so that the usability will be higher, i will post this one soon.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 and SQL*Plus: Release 11.2.0.2.0 Production and it wont work, SQL plus will connect using something like

sqlplus username/passowrd@ip_address:port/SSID

rather than

sqlplus user/passwd@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=IP or hostname)(Port=port))(CONNECT_DATA=(SID=sid)))

ANy ideas on how to make this work? I cant figure it out ...
byobiouane, August 10, 2011
To make it work with a RAC cluster I had to do the following little changes in the code of the script.

replace $SID by $SERVICE_NAME (it happens 2 times)

It is compatible from my experience (using 10.1 and 10.2)

Thank you
bydbunduki, July 22, 2011
1 of 1 people found this review helpful
#!/usr/bin/perl -w

my $host = $ARGV[0];
my $port = $ARGV[1];
my $sid = $ARGV[2];
my $user = $ARGV[3];
my $pass = $ARGV[4];

### point this to your sqlplus binary
my $sqlplus = "/usr/local/src/instantclient_11_2/sqlplus";

### point this to your sqlplus directory
$ENV{"LD_LIBRARY_PATH"} = "/usr/local/src/instantclient_11_2/";

sub trim($);
my @result;
my %ERRORS=('OK'=>0,'WARNING'=>1,'CRITICAL'=>2);
my @param_array = (
[90,">","Dictionary Cache Hit Ratio",'SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 FROM v\$rowcache;'],
[99,">","Library Cache Hit Ratio",'SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 FROM v\$librarycache;'],
[89,">","DB Block Buffer Cache Hit Ratio",'SELECT (1 - (phys.value / (db.value + cons.value))) * 100 FROM v\$sysstat phys,v\$sysstat db,v\$sysstat cons WHERE phys.name = \'physical reads\' AND db.name = \'db block gets\' AND cons.name = \'consistent gets\';'],
[98,">","Latch Hit Ratio",'SELECT (1 - (Sum(misses) / Sum(gets))) * 100 FROM v\$latch;'],
[5,"\)\)\)\" |");

while ( my $res = ) {
if ($res =~ /^(ORA-\d{5})/) {
return $1;
}
}
}

if (logon() eq "ORA-01017" ){
for (my $i=0; $iumformat 999.999
$param_array[$i][3]
exit
EOF |") or die;
while ( my $res = ) {
#print trim($res)."\n";
if ( $res =~/^\s*\S+/ ) {
push(@results,trim($res));
}
}
}

for (my $i=0; $is OK";
exit $ERRORS{"OK"};
} else {
print "Unable to connect to $sid database!";
exit $ERRORS{"CRITICAL"};
}
bydarkto, December 1, 2010
1 of 1 people found this review helpful
Had to change the script to get the right environment variables


BEGIN {
unless (($ENV{BEGIN_BLOCK}) or $^C) {
$ENV{"LD_LIBRARY_PATH"} = '/usr/lib/oracle/11.2/client/lib/';
$ENV{BEGIN_BLOCK} = 1;
exec 'env',$0,@ARGV;
}
}


My system is Red Hat Linux. I installed

oracle-instantclient11.2-basic-11.2.0.2.0.i386.rpm
oracle-instantclient11.2-sqlplus-11.2.0.2.0.i386.rpm


I think the lack of environment is due some changes in last versions of oracle instant client for linux.

Thanks for the plugin!
Good morning, i can't run this script in my ubuntu distro with nagios or icinga. I get an error: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin). I va installed DBD::Oracle and DBI OK, but it not run.
What hapends??
bychrister.holmer, June 29, 2010
0 of 1 people found this review helpful
This script has a serious flaw, since it tries to login as "system" with an incorrect password. The result is that the "system" account is locked after a couple of attempts. Needs to be modified.
byFIL, June 16, 2010
0 of 2 people found this review helpful
I think I am messing this up because i can´t seen to get this plugin working.Before I put plugins in Nagios3 I test then via command line like this ./check_oracle_instant replacing the words between with the appropriate data to fit my needs.Can anyone tell me if this is the correct syntax?
1050468@isep.ipp.pt
byGewels, December 4, 2009
0 of 1 people found this review helpful
Very good because you dont need to add anythig to the oracle server. Works fine , tested. With DB up it was green , with oracle stopped it became critical , and while DB was starting up it sent a warning message.