hI,
I have this problem and I need your help
Nagios notifies:
ERROR: SERVICE NOTIFICATION: nagiosadmin;ora1;ORACLE:check_login_health;CRITICAL;notify-service-by-email;
(No output on stdout) stderr: execvp(/usr/local/nagios/libexec/check_oracle_instant, ...)
failed. errno is 2: No such file or directory
But it seems that it works:
[nagios@localhost ~]$ perl /usr/local/nagios/libexec/check_oracle_instant -H 172.17.9.15
Bareword found where operator expected at (eval 6) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 10) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 11) line 1, near "3 attempts"
(Missing operator before attempts?)
Bareword found where operator expected at (eval 17) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 21) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 22) line 1, near "3 attempts"
(Missing operator before attempts?)
Bareword found where operator expected at (eval 28) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 32) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 33) line 1, near "3 attempts"
(Missing operator before attempts?)
Bareword found where operator expected at (eval 39) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 43) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 44) line 1, near "3 attempts"
(Missing operator before attempts?)
Bareword found where operator expected at (eval 50) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 54) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 55) line 1, near "3 attempts"
(Missing operator before attempts?)
Bareword found where operator expected at (eval 61) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 65) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 66) line 1, near "3 attempts"
(Missing operator before attempts?)
Bareword found where operator expected at (eval 72) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 76) line 1, near "<proxy> ::"
(Missing operator before ::?)
Bareword found where operator expected at (eval 77) line 1, near "3 attempts"
(Missing operator before attempts?)
status and health of ORACLE is OK
nagios@localhost local]$ ls -la
total 0
drwxr-xr-x. 13 root root 145 ene 16 13:37 .
drwxr-xr-x. 13 root root 155 ene 16 08:54 ..
drwxr-xr-x. 2 root root 6 abr 11 2018 bin
drwxr-xr-x. 2 root root 6 abr 11 2018 etc
drwxr-xr-x. 2 root root 6 abr 11 2018 games
drwxr-xr-x. 2 root root 6 abr 11 2018 include
drwxr-xr-x. 2 root root 6 abr 11 2018 lib
drwxr-xr-x. 2 root root 6 abr 11 2018 lib64
drwxr-xr-x. 2 root root 6 abr 11 2018 libexec
drwxrwxrwx. 9 nagios nagios 94 ene 16 14:56 nagios
drwxr-xr-x. 2 root root 6 abr 11 2018 sbin
drwxr-xr-x. 5 root root 49 ene 16 08:54 share
drwxr-xr-x. 2 root root 6 abr 11 2018 src
nagios@localhost nagios]$ PRTG/prtg@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=172.17.9.15)(Port=1521)))(CONNECT_DATA=(SID=PRUEBA11)))
>
>
Thank you.
pluggin check_oracle_instant does not work
Re: pluggin check_oracle_instant does not work
The first line on the plugin is the path to the perl application that the plugin uses to run.
If it set to the correct path to the perl application?
If not, update and test the plugin again.
How did you define the command in the Nagios configuration file?
If there a proxy server between the nagios server and the Oracle server?
If it set to the correct path to the perl application?
If not, update and test the plugin again.
How did you define the command in the Nagios configuration file?
If there a proxy server between the nagios server and the Oracle server?
Be sure to check out our Knowledgebase for helpful articles and solutions!
Re: pluggin check_oracle_instant does not work
Thank you,
path to the perl application:
#!/usr/bin/perl
[nagios@localhost bin]$ pwd
/usr/bin
[nagios@localhost bin]$ ls -la
-rwxrwxrwx. 2 root root 11488 ene 21 2019 perl
configuration proxy:
[nagios@localhost ~]$ sudo vi /etc/profile.d/proxy.sh
export http_proxy=http://PROXY:8080
export https_proxy=http://PROXY:8080
export no_proxy="127.0.0.1, localhost"
command in the Nagios configuration file:
[nagios@localhost ~]$ cat /usr/local/nagios/etc/objects/commands.cfg
#### CHECK ORACLE ###
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$
}
define service{
use generic-service
host_name ora1
service_description ORACLE:check_login_health
check_command check_oracle_instant!1521!PRUEBA11!PRTG!prtg
}
define host{
use generic-host
host_name ora1
alias ora1
address 172.17.9.15
max_check_attempts 10
}
cat /usr/local/nagios/etc/resource.cfg
$USER1$=/usr/local/nagios/libexec
[nagios@localhost ~]$ sudo cat /usr/local/nagios/libexec/check_oracle_instant
[sudo] password for nagios:
#!/usr/bin/perl
$host = $ARGV[0];
$port = $ARGV[1];
$sid = $ARGV[2];
$user = $ARGV[3];
$pass = $ARGV[4];
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,"<","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)\';'],
[5,"<","Rollback Segment Waits",'SELECT (Sum(waits) / Sum(gets)) * 100 FROM v\$rollstat;'],
[50,"<","Dispatcher Workload",'SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0) FROM v\$dispatcher;']
);
# is possible define own selects [reference value,operator (<;>;eq;ne etc.),Description,select]
my @results;
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 {
#open (SQL,"sqlplus -s system/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\)</dev/null
sub logon {
open (SQL,"sqlplus -s system/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=172.17.9.15\\)\\(Port=1521\\)\\)\\(CONNECT_DATA=\\(SID=PRUEBA11\\)\\)\\)</dev/null
|") or die;
while ( my $res = <SQL> )
{
if ($res =~ /^(ORA-\d{5})/) {return $1;}
}
}
if (logon() 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
set pagesize 0
set numformat 999.999
$param_array[$i][3]
EOF |") or die;
while ( my $res = <SQL> )
{
# print trim($res)."\n";
if ( $res =~/^\s*\S+/ ) { push(@results,trim($res));}
}
}
for ($i=0;$i<@results;$i++) {
# print $i." hodnota je ".$result[$i]." a ma byt ".$param_array[$i][0];
eval "unless (".$results[$i].$param_array[$i][1].$param_array[$i][0].") { print\"".$param_array[$i][2]." ".$sid." KO \\n\"; exit ".$ERRORS{"WARNING"}.";}";
}
print "status and health of $sid ORACLE is OK";
exit $ERRORS{"OK"};
} else {print "Unable to connect to $sid ORACLE !!! "; exit $ERRORS{"CRITICAL"};}
path to the perl application:
#!/usr/bin/perl
[nagios@localhost bin]$ pwd
/usr/bin
[nagios@localhost bin]$ ls -la
-rwxrwxrwx. 2 root root 11488 ene 21 2019 perl
configuration proxy:
[nagios@localhost ~]$ sudo vi /etc/profile.d/proxy.sh
export http_proxy=http://PROXY:8080
export https_proxy=http://PROXY:8080
export no_proxy="127.0.0.1, localhost"
command in the Nagios configuration file:
[nagios@localhost ~]$ cat /usr/local/nagios/etc/objects/commands.cfg
#### CHECK ORACLE ###
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$
}
define service{
use generic-service
host_name ora1
service_description ORACLE:check_login_health
check_command check_oracle_instant!1521!PRUEBA11!PRTG!prtg
}
define host{
use generic-host
host_name ora1
alias ora1
address 172.17.9.15
max_check_attempts 10
}
cat /usr/local/nagios/etc/resource.cfg
$USER1$=/usr/local/nagios/libexec
[nagios@localhost ~]$ sudo cat /usr/local/nagios/libexec/check_oracle_instant
[sudo] password for nagios:
#!/usr/bin/perl
$host = $ARGV[0];
$port = $ARGV[1];
$sid = $ARGV[2];
$user = $ARGV[3];
$pass = $ARGV[4];
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,"<","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)\';'],
[5,"<","Rollback Segment Waits",'SELECT (Sum(waits) / Sum(gets)) * 100 FROM v\$rollstat;'],
[50,"<","Dispatcher Workload",'SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0) FROM v\$dispatcher;']
);
# is possible define own selects [reference value,operator (<;>;eq;ne etc.),Description,select]
my @results;
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 {
#open (SQL,"sqlplus -s system/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\)</dev/null
sub logon {
open (SQL,"sqlplus -s system/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=172.17.9.15\\)\\(Port=1521\\)\\)\\(CONNECT_DATA=\\(SID=PRUEBA11\\)\\)\\)</dev/null
|") or die;
while ( my $res = <SQL> )
{
if ($res =~ /^(ORA-\d{5})/) {return $1;}
}
}
if (logon() 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
set pagesize 0
set numformat 999.999
$param_array[$i][3]
EOF |") or die;
while ( my $res = <SQL> )
{
# print trim($res)."\n";
if ( $res =~/^\s*\S+/ ) { push(@results,trim($res));}
}
}
for ($i=0;$i<@results;$i++) {
# print $i." hodnota je ".$result[$i]." a ma byt ".$param_array[$i][0];
eval "unless (".$results[$i].$param_array[$i][1].$param_array[$i][0].") { print\"".$param_array[$i][2]." ".$sid." KO \\n\"; exit ".$ERRORS{"WARNING"}.";}";
}
print "status and health of $sid ORACLE is OK";
exit $ERRORS{"OK"};
} else {print "Unable to connect to $sid ORACLE !!! "; exit $ERRORS{"CRITICAL"};}
Re: pluggin check_oracle_instant does not work
The query in the plugin has some errors in it that prints out the invalid data.
You can try and modify the query section but I did find an updated copy of the plugin which worked without any modifications.
https://exchange.nagios.org/directory/P ... ed/details
You can try and modify the query section but I did find an updated copy of the plugin which worked without any modifications.
https://exchange.nagios.org/directory/P ... ed/details
Be sure to check out our Knowledgebase for helpful articles and solutions!
Re: pluggin check_oracle_instant does not work
Imposible.
TNSping works:
[root@localhost libexec]# tnsping PRUEBA
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2020 11:33:50
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/ora01/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.16)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclcdb)))
OK (10 msec)
plugin by CLI works:
[root@localhost libexec]# ./check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle
status and health of database orclcdb is OK
But Nagios web says:
ORASERVER
ORACLE: check_login_health
OK 02-18-2020 16:09:40 0d 22h 55m 21s 1/5
Status Information --> Unknown argument used, correct your syntax.!!!
Why?
IN ORACLE.CFG:
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
}
define service{
# use profile name
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant!1521!orclcdb!system!oracle
max_check_attempts 5
check_interval 5
retry_interval 3
check_period 24x7
notification_interval 30
notification_period 24x7
notification_options w,c,r
}
IN COMMANDS.CFG:
## CHECK ORACLE ###
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$
}
Thank you!
TNSping works:
[root@localhost libexec]# tnsping PRUEBA
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2020 11:33:50
Copyright (c) 1997, 2016, Oracle. All rights reserved.
Used parameter files:
/ora01/app/oracle/product/12.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.2.16)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclcdb)))
OK (10 msec)
plugin by CLI works:
[root@localhost libexec]# ./check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle
status and health of database orclcdb is OK
But Nagios web says:
ORASERVER
ORACLE: check_login_health
OK 02-18-2020 16:09:40 0d 22h 55m 21s 1/5
Status Information --> Unknown argument used, correct your syntax.!!!
Why?
IN ORACLE.CFG:
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
}
define service{
# use profile name
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant!1521!orclcdb!system!oracle
max_check_attempts 5
check_interval 5
retry_interval 3
check_period 24x7
notification_interval 30
notification_period 24x7
notification_options w,c,r
}
IN COMMANDS.CFG:
## CHECK ORACLE ###
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$
}
Thank you!
Re: pluggin check_oracle_instant does not work
The command definition in the commands.cfg is missing the options so edit the command and change it from.
to
Save the change and restart nagios and see if the check functions.
Code: Select all
command_line $USER1$/check_oracle_instant $HOSTADDRESS$ $ARG1$ $ARG2$ $ARG3$ $ARG4$Code: Select all
command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$Be sure to check out our Knowledgebase for helpful articles and solutions!
Re: pluggin check_oracle_instant does not work
Hello,
If I set:
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$
}
define service{
use generic-service
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant!1521!orclcdb!system!oracle
}
Nagios is ok but in the web:
ORASERVER ORACLE: check_login_health CRITICAL Unable to connect to ORACLE orclcdb, error !!!
If I set:
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$
}
define service{
use generic-service
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle
}
Nagios doen´t work:
[root@localhost libexec]# /usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
Error: Service check command 'check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle' specified in service 'ORACLE: check_login_health' for host 'ORASERVER' not defined anywhere!
I appreciate it a lot
If I set:
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$
}
define service{
use generic-service
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant!1521!orclcdb!system!oracle
}
Nagios is ok but in the web:
ORASERVER ORACLE: check_login_health CRITICAL Unable to connect to ORACLE orclcdb, error !!!
If I set:
define command{
command_name check_oracle_instant
command_line $USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$
}
define service{
use generic-service
host_name ORASERVER
service_description ORACLE: check_login_health
check_command check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle
}
Nagios doen´t work:
[root@localhost libexec]# /usr/local/nagios/bin/nagios -v /usr/local/nagios/etc/nagios.cfg
Error: Service check command 'check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle' specified in service 'ORACLE: check_login_health' for host 'ORASERVER' not defined anywhere!
I appreciate it a lot
Re: pluggin check_oracle_instant does not work
When you changed the check command for the service to the following
check_command check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle
that created a configuration error so remove that entry and put it back to the following.
The plugin you are using is different than the one you posted earlier so can you upload the plugin to the forum so I can see the whole thing?
check_command check_oracle_instant -H 10.0.2.16 -p 1521 -s orclcdb -u system -w oracle
that created a configuration error so remove that entry and put it back to the following.
Code: Select all
check_command check_oracle_instant!1521!orclcdb!system!oracleBe sure to check out our Knowledgebase for helpful articles and solutions!
Re: pluggin check_oracle_instant does not work
This is the 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
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
|") 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 very much.
#!/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
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
|") 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 very much.
Re: pluggin check_oracle_instant does not work
Edit the check_oracle_instant command to the following
Save the change.
Another thing you probably have to do, the plugin uses the sqlplus command to gather the information from the server.
If the sqlplus command is not in the path, you will have edit the plugin and add the full path to it.
For example, I had to change these lines from
to
There are 3 of them so edit all of them.
Make sure you defined a host entry for the server. The latest verification error says it is not defined.
Code: Select all
$USER1$/check_oracle_instant -H $HOSTADDRESS$ -p $ARG1$ -s $ARG2$ -u $ARG3$ -w $ARG4$ -n $ARG5$ Another thing you probably have to do, the plugin uses the sqlplus command to gather the information from the server.
If the sqlplus command is not in the path, you will have edit the plugin and add the full path to it.
For example, I had to change these lines from
Code: Select all
open (SQL,"sqlplus -s mismatch/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\)</dev/nullCode: Select all
open (SQL,"/usr/lib/oracle/11.2/client64/bin/sqlplus -s mismatch/mismatch@\\(DESCRIPTION=\\(ADDRESS=\\(PROTOCOL=TCP\\)\\(Host=$host\\)\\(Port=$port\\)\\)\\(CONNECT_DATA=\\(SID=$sid\\)\\)\\)</dev/nullMake sure you defined a host entry for the server. The latest verification error says it is not defined.
Code: Select all
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
}Be sure to check out our Knowledgebase for helpful articles and solutions!