Check Oracle status & health without install Oracle client

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?

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,dispatchern "; 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 ...

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

#!/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"}; }

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??

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

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.