Monitor w/ NAGIOS Using CHECK ORACLE HEALTH

This tutorial explains how to set up the 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 – oracl
sqlplus / as sysdbae        

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 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 libai
yum install libaio.x86_64o        

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.rp
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.rpmm        

• 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 manuall        

#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/liby
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 to the code.

(add “1;” at the last line)

## tablespace to wor
vi /usr/lib/perl5/site_perl/Nagios/DBD/Oracle/Server.pm
use Nagios::DBD::Oracle::Server::Database;
## then
cp -r Nagios/DBD ./k        

## add “1;” at the last line

/usr/lib/perl5/site_perl/DBD/Oracle/Server/Database/Tablespace.p
…
1;m        

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 comman
vi objects/commands.cfgd        

# 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 the 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        

This post by our CTO Mihail Vukadinoff ?originally appeared on our?blog. Check out?this URL?to see more of our content and subscribe to our newsletter if you want to get regular updates.

#DevOps?#OracleCheckHealth #devopsguide?#CTO #digitaltransformation?#nagios