pluggin check_oracle_instant does not work

Support forum for Nagios Core, Nagios Plugins, NCPA, NRPE, NSCA, NDOUtils and more. Engage with the community of users including those using the open source solutions.
Nacho
Posts: 10
Joined: Wed Jan 29, 2020 12:52 am

Re: pluggin check_oracle_instant does not work

Post by Nacho »

Hello,
By CLI is ok:
[root@localhost libexec]# ./check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle -n d
status and health of database orclcdb is OK

But by web:
Unable to connect to ORACLE orclcdb, error !!!

HOST:
define host{
use linux-server
host_name ORASERVER
alias ORASERVER
address 10.0.2.16
max_check_attempts 5
check_period 24x7
notification_interval 30
notification_period 24x7
}

SERVICE:
define service{
use generic-service
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant!1521!orclcdb!system!oracle!d
}

COMMAND:
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$ -n $ARG5
}

PLUGIN:
#!/usr/bin/perl
# This perl nagios plugin allows you to check oracle service (ability to connect to database )
# and the health of oracle database (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)
# It is possible to define your own parameters.
# Big advantage is that it does not need to install ORACLE client or compile other perl modules.
#
# modified by Gerrit Doornenbal, g(dot)doornenbal(at)hccnet(dot)nl
# dec 2012 (v1.1)
#
# addressed issues:
# v1.0 - Better help and command line options
# v1.0 - Risk of locked users due to wrong parameters is much lower.
# v.10 - Better error handling
# v1.0 - Database health error give more info.
# v1.1 - Options added to skip specific tests, when specific results are not marked as problematic.
# v1.1 - Added Tablespace usage check. Find's the tablespace with the highest percentage used.
# (counting max tablespace filesize against the real filesize minus the free space inside the db file.)
#

use strict;
no strict 'refs';

# Check for proper args....
my %status="";
if ($#ARGV <= 0){
&print_help();
}

my ($host, $port, $sid, $user, $pass, $nocheck) = pars_args();
#print "submitted arguments are: $host $port $sid $user $pass $nocheck\n";

sub trim($);
my @result;
my %ERRORS=('OK'=>0,'WARNING'=>1,'CRITICAL'=>2);
# Create param_array without deselected checks.
my @param_array;
if (index($nocheck, "t") == -1) {push @param_array, [90,"<","TableSpace usage",'select * from (select round((d.sizeMb-round(sum(f.bytes))/1048576)/d.maxMb*100) percentused, f.tablespace_name from dba_free_space f, (select tablespace_name, sum(MAXBYTES)/1048576 maxMb, sum(bytes)/1048576 sizeMb from dba_data_files group by tablespace_name) d where f.tablespace_name (+)=d.tablespace_name group by f.tablespace_name, d.sizeMb, d.maxMb order by percentused desc) where rownum <2;'];}
if (index($nocheck, "d") == -1) {push @param_array, [90,">","Dictionary Cache Hit Ratio",'SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 FROM v\$rowcache;'];}
if (index($nocheck, "l") == -1) {push @param_array, [99,">","Library Cache Hit Ratio",'SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100 FROM v\$librarycache;'];}
if (index($nocheck, "b") == -1) {push @param_array, [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\';'];}
if (index($nocheck, "a") == -1) {push @param_array, [98,">","Latch Hit Ratio",'SELECT (1 - (Sum(misses) / Sum(gets))) * 100 FROM v\$latch;'];}
if (index($nocheck, "s") == -1) {push @param_array, [5,"<","Disk Sort Ratio",'SELECT (disk.value/mem.value) * 100 FROM v\$sysstat disk,v\$sysstat mem WHERE disk.name = \'sorts (disk)\' AND mem.name = \'sorts (memory)\';'];}
if (index($nocheck, "r") == -1) {push @param_array, [5,"<","Rollback Segment Waits",'SELECT (Sum(waits) / Sum(gets)) * 100 FROM v\$rollstat;'];}
if (index($nocheck, "w") == -1) {push @param_array, [50,"<","Dispatcher Workload",'SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0) FROM v\$dispatcher;'];}
# it is possible define own selects [reference value,operator (<;>;eq;ne etc.),Description,select]

my @results;
my $logontest = logon();
my $i=0;
if ($logontest eq "ORA-01017"){
for (my $i=0; $i<array_rows(\@param_array); $i++){
# print "$param_array[$i][0] -- $param_array[$i][1] -- $param_array[$i][2] -- $param_array[$i][3]\n";

#open (SQL,"sqlplus -s $user/$pass@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\) << EOF
open (SQL,"/ora01/app/oracle/product/12.2.0/db_1/bin/sqlplus -s $user/$pass@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\) << EOF
set pagesize 0
set numformat 999.999
$param_array[$i][3]
EOF |") or die;
while ( my $res = <SQL> )
{
#$res=trim($res);
#print "result=".$res."\n";
if ( $res =~/^\s*\S+/ ) { push(@results,trim($res));}
}
}

# checking the results from all tests.
for ($i=0;$i<@results;$i++) {
my $value =$results[$i];
$value =~ s/^\S+\s*//; #For tablespace check get tablespace name. (2nd string)
$results[$i] =~ s/ .*//; #and remove the name (2nd string) from the results....
#print " checking ".$param_array[$i][2]." RESULT:".$results[$i]." value:".$value." reference value:".$param_array[$i][0]."\n";
if ($results[$i] eq "ERROR:") {print "Unable to connect to ORACLE $sid, invalid credentials or locked !!! \n"; exit $ERRORS{"CRITICAL"};}
eval "unless (".$results[$i].$param_array[$i][1].$param_array[$i][0].") { print\"".$param_array[$i][2]." ".$sid.":$value result ".int($results[$i])." against $param_array[$i][0] \\n\"; exit ".$ERRORS{"WARNING"}.";}";

}
print "status and health of database $sid is OK\n";
exit $ERRORS{"OK"};

} else {print "Unable to connect to ORACLE $sid, error $logontest !!! \n"; exit $ERRORS{"CRITICAL"};}



sub array_rows {
my ($array_rows) = @_;

my $rows = @$array_rows;
return $rows;
}

sub trim($) {
my $string = shift;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string;
}

sub logon {
# changed to not existing (mismatch) user to stop system user being locked.
#open (SQL,"sqlplus -s mismatch/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\)</dev/null
open (SQL,"/ora01/app/oracle/product/12.2.0/db_1/bin/sqlplus -s mismatch/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\)</dev/null
|") or die;
while ( my $res = <SQL> )
{
if ($res =~ /^(ORA-\d{5})/) {return $1;}
}
}

sub pars_args {

my $host = "localhost";
my $port = "";
my $sid = "";
my $user = "";
my $pass = "";
my $file = "";

# $oldarg = "";

while(@ARGV)
{
if($ARGV[0] =~/^-H|^--host/)
{
$host = $ARGV[1];
shift @ARGV;
shift @ARGV;
next;
}
if($ARGV[0] =~/^-p|^--port/)
{
$port = $ARGV[1];
shift @ARGV;
shift @ARGV;
next;
}
if($ARGV[0] =~/^-s|^--sid/)
{
$sid = $ARGV[1];
shift @ARGV;
shift @ARGV;
next;
}
if($ARGV[0] =~/^-n|^--nocheck/)
{
$nocheck = $ARGV[1];
shift @ARGV;
shift @ARGV;
next;
}
if($ARGV[0] =~/^-f|^--file/)
{
$file = $ARGV[1];
shift @ARGV;
shift @ARGV;
open my $info, $file or die "Could not open $file: $!";
while( my $line = <$info>) {
my $newargv = $line." ".join(" ", @ARGV);
#print "newargv is: $newargv !\n";
@ARGV = split(" ", $newargv);
}
close $info;
next;
}
if($ARGV[0] =~/^-u|^--user/)
{
$user = $ARGV[1];
shift @ARGV;
shift @ARGV;
next;
}
if($ARGV[0] =~/^-w|^--pass/)
{
$pass = $ARGV[1];
shift @ARGV;
shift @ARGV;
next;
}
# Code to jump out of loop when not existing argument is used (The while loop only reaches this point if all options above have failed.)
print "Unknown argument used, correct your syntax.!!! \n"; exit($status{"UNKNOWN"});
}
return ($host, $port, $sid, $user, $pass, $nocheck); }

sub print_help() {
print "This plugin logs into the database and does some health checking inside the database.\n\n";
print "Usage: check_oracle_instant -H host -l listener-port -s SID -u username -p password -n tdlbasrw -f filename\n\n";
print "Options:\n";
print " -H --host STRING or IPADDRESS\n";
print " Address of the indicated host.\n";
print " -l --lsn portnumber\n";
print " Oracle listener port number.\n";
print " -s --sid Connect String\n";
print " Oracle connect string\n";
print " -u --user username\n";
print " Oracle login name\n";
print " -p --pass password\n";
print " Oracle password\n";
print " -n --nocheck dlbasrw\n";
print " Here you can disable specific tests in case you don't want them:\n";
print " t: Tablespace usage\n";
print " d: Dictionary Cache Hit Ratio\n";
print " l: Library Cache Hit Ratio\n";
print " b: DB Block Buffer Cache Hit Ratio\n";
print " a: Latch Hit Ratio\n";
print " s: Disk Sort Ratio\n";
print " r: Rollback Segment Waits\n";
print " w: Dispatcher Workload\n";
print " -f --file filename\n";
print " Filename with any (default) option as stated above.\n";
print " This file contains one line, with all options you wish to use. example:\n";
print " -l 1521 -u system -p testpass\n";
print " Settings set after the -f in the commandline wil override the settings in this file.\n\n";
exit($status{"UNKNOWN"});
}

Thank you.
User avatar
tgriep
Madmin
Posts: 9177
Joined: Thu Oct 30, 2014 9:02 am

Re: pluggin check_oracle_instant does not work

Post by tgriep »

Your command is missing the $ on the ARG5 argument.
Update it to this.

Code: Select all

command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$ -n $ARG5$
If it still fails, one more thing to check is if it is a permission issue.
When Nagios runs a check, it runs it as the nagios user so if that account cannot run the applications or have access to them, it will fail.

Login to the server as root and change to the nagios user by running this.

Code: Select all

su - nagios
Then run the check.

Code: Select all

./check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle -n d
If you see any permission errors, fix them until the plugin runs.
Be sure to check out our Knowledgebase for helpful articles and solutions!
Nacho
Posts: 10
Joined: Wed Jan 29, 2020 12:52 am

Re: pluggin check_oracle_instant does not work

Post by Nacho »

Thank you very much.
I'm going to start from scratch.
scottwilkerson
DevOps Engineer
Posts: 19396
Joined: Tue Nov 15, 2011 3:11 pm
Location: Nagios Enterprises
Contact:

Re: pluggin check_oracle_instant does not work

Post by scottwilkerson »

Nacho wrote:Thank you very much.
I'm going to start from scratch.
Ok, sounds good.

Locking thread
Former Nagios employee
Creator:
ahumandesign.com
enneagrams.com
Locked