Set up Check Oracle Health
This tutorial explains how to set up Check Oracle Health: check_oracle_health script (credits to Gerhard Lausser) to work on your Nagios environment on CentOS (or any RedHat-based Linux). This Nagios plugin allows monitoring many Oracle DB parameters – like tablespaces size, session, process count, SGA pool, etc. Check it out on the author’s web set.
The hardest part of the setup is installing the dependent Perl libraries and making modifications in the Perl code for them to work.
On the Oracle server, we need to create the monitoring user and grant rights, only the minimum necessary for the script to work.
su – oracle
sqlplus / as sysdba
Type the commands:
CREATE USER nagios IDENTIFIED BY account unlock;
GRANT CREATE SESSION TO nagios;
GRANT SELECT any dictionary TO nagios;
GRANT SELECT ON V_$SYSSTAT TO nagios;
GRANT SELECT ON V_$INSTANCE TO nagios;
GRANT SELECT ON V_$LOG TO nagios;
GRANT SELECT ON SYS.DBA_DATA_FILES TO nagios;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO nagios;
On the Nagios server: Upload the check script: check_oracle_health (can be downloaded from the author’s site in the link above)
- Configure using command
./configure -prefix=/usr/local/nagios/libexec -with-nagios-user=nagios
- Install oracle instant client on the Nagios server
- dependencies
yum search libaio
yum install libaio.x86_64
Install the Oracle client base libs, the devel package and the sqlplus client for sql
rpm -ivh oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm
rpm -ivh OracleInstantClient/oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64.rpm
rpm -ivh OracleInstantClient/oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm
- Install all dependent perl modules – DBI , DBD::Oracle
Now here comes the hard part. The DBD::Oracle perl module is not in an rpm repository, the good thing is that you can download it via CPAN. Unfortunately it doesn’t work out of the box. I had to make quite a few modifications to get it working.Follow below.
perl-DBI is already installed on nagios server, it’s an RPM package
## oracle is not available in rpm so install manually
#with CPAN
perl -MCPAN -e shell
get DBD::Oracle
## make with makefile.PL
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
cd .cpan/build/DBD-Oracle-1.74/
perl Makefile.PL
## needed to use
perl Makefile.PL -l
make
make test
make install
Now you need to copy over the included perl lib files in the “Nagios” dir included in the check_oracle_health script package.
cp -R Nagios/ /usr/lib/perl5/site_perl/
Up to here the basic functionality of the script should be available, however, when you try the tablespace checks I got a weird error. Like: Tablespace.pm did not return a true value or: Undefined subroutine &DBD::Oracle::Server::Database::Tablespace::init_tablespaces
Make the following amendments in the code.
(add “1;” at the last line)
## tablespace to work
vi /usr/lib/perl5/site_perl/Nagios/DBD/Oracle/Server.pm
use Nagios::DBD::Oracle::Server::Database;
## then
cp -r Nagios/DBD ./
## add “1;” at the last line
/usr/lib/perl5/site_perl/DBD/Oracle/Server/Database/Tablespace.pm
…
1;
Configure Check Command in Nagios
Now you have to configure the check command in Nagios. For the command to work, it needs to have the Oracle client paths added to the environment variables – ORACLE_HOME, LD_LIBRARY_PATH, PATH. To avoid all sorts of issues in passing environment variables in Nagios and altering the startup script of Nagios to set them (as described on the author’s site), just pass the variables directly in the command. The latest version of check_oracle_health allows that.
## configure the command
vi objects/commands.cfg
# Oracle Database check health command
define command{
command_name check_oracle_health
command_line /usr/bin/perl $USER1$/check_oracle_health.pl
–connect=//$HOSTADDRESS$:$ARG1$/$ARG2$ –username=$ARG3$ –password=$ARG4$
–warning=$ARG5$ –critical=$ARG5$ –mode $ARG7$ –environment
ORACLE_HOME=’/usr/lib/oracle/12.1/client64′ –environment
PATH=/usr/lib/oracle/12.1/client64/bin –environment
LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
}
# Oracle Database check health command with -name attribute
define command{
command_name check_oracle_health_name
command_line /usr/bin/perl $USER1$/check_oracle_health.pl
–connect=//$HOSTADDRESS$:$ARG1$/$ARG2$ –username=$ARG3$ –password=$ARG4$
–warning=$ARG5$ –critical=$ARG5$ –mode $ARG7$ –name $ARG8$ –environment ORACLE_HOME=’/usr/lib/oracle/12.1/client64′ –environment PATH=/usr/lib/oracle/12.1/client64/bin –environment LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
It’s good to have a command without thresholds too and use depending on the services you’re using that the check_oracle_health script provides. For example, if you’re checking for free space the cmd will give you “are you sure you want to be notified if free space gets above threshold”
# Oracle Database check health command without thresholds
define command{
command_name check_oracle_health_nothres
command_line /usr/bin/perl $USER1$/check_oracle_health.pl
–connect=//$HOSTADDRESS$:$ARG1$/$ARG2$ –username=$ARG3$ –password=$ARG4$
–mode $ARG5$
–environment ORACLE_HOME=’/usr/lib/oracle/12.1/client64′ –environment PATH=/usr/lib/oracle/12.1/client64/bin –environment LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
}
## add the service checks for the staging oracle server
define service {
use generic-service
host_name oracledbtest
service_description Oracle sessions
### check_oracle_health.pl — PORT ! SID ! USER ! Pass ! Warn ! crit !
mode
check_command
check_oracle_health!1521!YOOURSID!nagios!!60!80!session-usage
}
### Change last argument to the different modes
tns-ping , process-usage , sga-shared-pool-free
You can add all the modes you want. You can manually test the command like so. It’s very important that the ORACLE_HOME and other variables are set. They can be passed to the command directly, this way you don’t need to modify your Nagios startup script to set the vars in the Nagios user environment.
./check_oracle_health.pl –connect=//X.X.X.X:1521/YOOURSID –username=nagios -password=XXXX –mode=tablespace-free –name NAMEOFTBLSPC –environment ORACLE_HOME=’/usr/lib/oracle/12.1/client64′ –environment PATH=$PATH:$ORACLE_HOME/bin –environment LD_LIBRARY_PATH=$ORACLE_HOME/lib