Search Exchange
Search All Sites
Nagios Live Webinars
Let our experts show you how Nagios can help your organization.Login
Directory Tree
Check Oracle status & health without install Oracle client Popular
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 :-)
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)
bysdbhabal, January 24, 2017
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?
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?
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"};}
$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"};}
byluduing, March 14, 2014
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
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
byDoornenbal, December 12, 2012
Therefore i rewrote this script, so that the usability will be higher, i will post this one soon.
byBob.Davis@wibble.net.nz, November 7, 2011
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 ...
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
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"};
}
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"};
}
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!
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!
byaaronraja, November 30, 2010
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??
What hapends??
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.
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
1050468@isep.ipp.pt
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.