Configure Nagios to monitor Oracle database on a remote Linux Host Using CHECK_ORACLE_HEALTH

Monitoring Oracle Database with Nagios involves using plugins and configurations that allow Nagios to check the status and performance of the Oracle Database.

Download and install oracle instant client for Nagios server

1. Install the libnsl package which is a required package for instant clients for Linux 8

[root@emcl etc]# dnf install libnsl -y
Last metadata expiration check: 1:31:06 ago on Fri 15 Dec 2023 05:26:18 PM EST.
Package libnsl-2.28-164.0.1.el8.x86_64 is already installed.
Dependencies resolved.
Nothing to do.
Complete!

2. For downloading instance client go to the oracle website https://www.oracle.com/ and do the following

From the top menu go to Resources then click Developer Downloads

Click Database

Click Instant Client

Then choose Instant client for Linux 64 bit

you should download these rpm files 

• oracle-instantclient-basic-21.12.0.0.0-1.el8.x86_64.rpm
• oracle-instantclient-devel-21.12.0.0.0-1.el8.x86_64.rpm
• oracle-instantclient-sqlplus-21.12.0.0.0-1.el8.x86_64.rpm

Download and copy rpm file to /software and install them

[root@emcl software]# rpm -qa | grep instantclient
[root@emcl software]# rpm -Uvh oracle-instantclient-basic-21.12.0.0.0-1.el8.x86_64.rpm
warning: oracle-instantclient-basic-21.12.0.0.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-instantclient-basic-21.12.################################# [100%]
[root@emcl software]# rpm -Uvh oracle-instantclient-devel-21.12.0.0.0-1.el8.x86_64.rpm
warning: oracle-instantclient-devel-21.12.0.0.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-instantclient-devel-21.12.################################# [100%]
[root@emcl software]# rpm -Uvh oracle-instantclient-sqlplus-21.12.0.0.0-1.el8.x86_64.rpm
warning: oracle-instantclient-sqlplus-21.12.0.0.0-1.el8.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ad986da3: NOKEY
Verifying...                          ################################# [100%]
Preparing...                          ################################# [100%]
Updating / installing...
   1:oracle-instantclient-sqlplus-21.1################################# [100%]

Configure oracle server (nagios client)

1. Creating nagios user in the database and giving it the required access

[oracle@emcl ~]$ sqlplus / as sysdba
create user nagios identified by nagios;
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;

Set up Check_Oracle_Health on Nagios server

1. prerequisite for installing Check_Oracle_Health on OEL8.5 (our nagios server)

1_1. The installation of the Perl modules DBI and DBD::Oracle are required. perl-DBI.x86_64 rpm exists on OEL8 ISO image and we will install it. In addition, we need to install libaio and Perl-CPAN related RPMs, I checked my system it was already installed.

[root@emcl software]# yum search  perl-DBI
Last metadata expiration check: 1:04:11 ago on Wed 03 Jan 2024 03:03:33 PM EST.
======================================================== Name Exactly Matched: perl-DBI =========================================================
perl-DBI.x86_64 : A database access API for perl


[root@emcl software]# yum install perl-DBI.x86_64

[root@emcl software]# rpm -qa | grep perl-DBI
perl-DBI-1.641-3.module+el8.3.0+7665+79fef143.x86_64


[root@emcl software]# rpm -qa | grep libaio
libaio-0.3.112-1.el8.x86_64

[root@emcl DBD-Oracle-1.83-0]# rpm -qa | grep perl-CPAN
perl-CPAN-Meta-Requirements-2.140-396.el8.noarch
perl-CPAN-Meta-2.150010-396.el8.noarch
perl-CPAN-Meta-YAML-0.018-397.el8.noarch
perl-CPAN-2.18-397.el8.noarch
###Prerequisites:
####Oracle Instant Client:
[root@emcl software]# rpm -qa | grep oracle-instantclient
oracle-instantclient-sqlplus-21.12.0.0.0-1.el8.x86_64
oracle-instantclient-devel-21.12.0.0.0-1.el8.x86_64
oracle-instantclient-basic-21.12.0.0.0-1.el8.x86_64

The DBD::Oracle Perl module is not in an RPM repository, the good thing is that you can download it via CPAN. Run the following command with the root user.

 [root@emcl software]# perl -MCPAN -e shell 

During the installation, you may be prompted to provide the paths to the Oracle Instant Client libraries and include files. Make sure to provide the correct paths.

[root@emcl DBD-Oracle-1.83-0]# export ORACLE_HOME=/usr/lib/oracle/21/client64/
[root@emcl DBD-Oracle-1.83-0]# export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib
[root@emcl DBD-Oracle-1.83-0]# export PATH=$PATH:$ORACLE_HOME/bin:$LD_LIBRARY_PATH:${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin

[root@emcl DBD-Oracle-1.83-0]# cd /root/.cpan/build/DBD-Oracle-1.83-0
[root@emcl DBD-Oracle-1.83-0]# perl Makefile.PL

Then run the following

[root@emcl DBD-Oracle-1.83-0]# make
[root@emcl DBD-Oracle-1.83-0]# make install

Verify installation using the following command.

[root@emcl DBD-Oracle-1.83-0]# perl -e 'use DBD::Oracle; print "DBD::Oracle is installed\n";'
DBD::Oracle is installed

2. Download and Install check_oracle_health:

[root@emcl software]# cd /software/
[root@emcl software]# wget https://labs.consol.de/assets/downloads/nagios/check_oracle_health-3.3.2.1.tar.gz
--2024-01-03 16:14:26--  https://labs.consol.de/assets/downloads/nagios/check_oracle_health-3.3.2.1.tar.gz
Resolving labs.consol.de (labs.consol.de)... 94.185.89.33, 2a03:3680:0:2::21
Connecting to labs.consol.de (labs.consol.de)|94.185.89.33|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 173953 (170K) [application/octet-stream]
Saving to: ‘check_oracle_health-3.3.2.1.tar.gz’

check_oracle_health-3.3.2.1.tar.gz   100%[===================================================================>] 169.88K   721KB/s    in 0.2s

2024-01-03 16:14:27 (721 KB/s) - ‘check_oracle_health-3.3.2.1.tar.gz’ saved [173953/173953]



[root@emcl DBD-Oracle-1.83-0]# cd /software/
[root@emcl software]# tar xzf check_oracle_health-3.3.2.1.tar.gz
[root@emcl software]# cd check_oracle_health-3.3.2.1  
[root@emcl libexec]# ./configure -prefix=/usr/local/nagios -with-nagios-user=nagios
-bash: ./configure: No such file or directory
[root@emcl libexec]# cd /software/check_oracle_health-3.3.2.1
[root@emcl check_oracle_health-3.3.2.1]# ./configure -prefix=/usr/local/nagios -with-nagios-user=nagios

[root@emcl check_oracle_health-3.3.2.1]# make
[root@emcl check_oracle_health-3.3.2.1]# make install

Here are some examples of testing the Check_Oracle_health

###checking the existance of check_oracle_health are created under /usr/local/nagios/libexec/ path
[root@emcl check_oracle_health-3.3.2.1]# cd /usr/local/nagios/libexec/
[root@emcl libexec]# ll check_oracle
check_oracle         check_oracle_health


[root@emcl libexec]# /usr/local/nagios/libexec/check_oracle_health -V
check_oracle_health (3.3.2.1)
This nagios plugin comes with ABSOLUTELY NO WARRANTY. You may redistribute
copies of this plugin under the terms of the GNU General Public License.

##checking tnsping 
[root@emcl libexec]# /usr/local/nagios/libexec/check_oracle_health --connect=192.168.56.10:1521/pdb1.amir.net --username=nagios --password=nagios --mode tnsping     
OK - connection established to 192.168.56.10:1521/pdb1.amir.net.

##checking tablespaces usage 
[root@emcl libexec]# /usr/local/nagios/libexec/check_oracle_health --connect=192.168.56.10:1521/pdb1.amir.net --username=nagios --password=nagios --mode tablespace-usage
OK - tbs USERS usage is 0.00%, tbs UNDOTBS1 usage is 0.03%, tbs TEMP usage is 0.00%, tbs SYSTEM usage is 0.84%, tbs SYSAUX usage is 0.81% | 
'tbs_users_usage_pct'=0.00%;90;98 'tbs_users_usage'=1MB;29491;32112;0;32767 'tbs_users_alloc'=5MB;;;0;32767 'tbs_undotbs1_usage_pct'=0.03%;90;98 'tbs_undotbs1_usage'=9MB;29491;32112;0;32767 'tbs_undotbs1_alloc'=100MB;;;0;32767 'tbs_temp_usage_pct'=0.00%;90;98 'tbs_temp_usage'=0MB;265420;289013;0;294911 'tbs_temp_alloc'=1152MB;;;0;294911 'tbs_system_usage_pct'=0.84%;90;98 'tbs_system_usage'=276MB;29491;32112;0;32767 'tbs_system_alloc'=280MB;;;0;32767 'tbs_sysaux_usage_pct'=0.81%;90;98 'tbs_sysaux_usage'=264MB;29491;32112;0;32767 'tbs_sysaux_alloc'=280MB;;;0;32767

##checking ASM disks
[root@emcl ~]#  /usr/local/nagios/libexec/check_oracle_health --connect=192.168.56.10:1521/pdb1.amir.net --username=nagios --password=nagios --mode asm-diskgroup-usage
OK - dg DATA usage is 25.27%, dg FRA usage is 9.30% | 'dg_data_usage_pct'=25.27%;90;98 'dg_data_usage'=4140MB;14742;16052;0;16380 'dg_data_size'=16380MB 'dg_fra_usage_pct'=9.30%;90;98 'dg_fra_usage'=952MB;9212;10031;0;10236 'dg_fra_size'=10236MB

Configuring Nagios-related files on the Nagios server to check the Oracle server using CHECK_ORACLE_HEALTH

1. Configuration of Nagios server files including hosts.cfg, services.cfg and commands.cfg for checking tablespace usage

[root@emcl ~]#  vi /usr/local/nagios/etc/hosts.cfg

## Default
define host{
use                             linux-box               ; Inherit default values from a template
host_name                       src                     ; The name we're giving to this server
alias                           Oracle Linux Server release 7.9               ; A longer name for the server
address                         192.168.56.10           ; IP address of Remote Linux host
}

##add this to the end of following file
[root@emcl admin]# vi /usr/local/nagios/etc/services.cfg
define service {
    use                         generic-service
    host_name                   src
    service_description         Oracle Tablespace Usage
    check_command               check_oracle_health_tablespace!1521!pdb1.amir.net!nagios!nagios!tablespace-usage
        }

[root@emcl ~]# vi /usr/local/nagios/etc/objects/commands.cfg
define command {
     command_name check_oracle_health_tablespace
  command_line $USER1$/check_oracle_health --connect=$HOSTADDRESS$:$ARG1$/$ARG2$ --username=$ARG3$ --password=$ARG4$ --mode $ARG5$

}

2. Nagios configuration for tnsping

[root@emcl ~]# vi /usr/local/nagios/etc/services.cfg
define service {
    use                         generic-service
    host_name                   src
    service_description         TNS Ping
    check_command               check_oracle_health_tnsping!1521!pdb1.amir.net!tnsping
        }

[root@emcl ~]# vi /usr/local/nagios/etc/objects/commands.cfg

define command {
    command_name check_oracle_health_tnsping
    command_line $USER1$/check_oracle_health --connect=$HOSTADDRESS$:$ARG1$/$ARG2$ --mode $ARG3$
}

3. Nagios configuration for ASM Diskgroup Free Space

[root@emcl ~]# vi /usr/local/nagios/etc/services.cfg

define service {
    use                         generic-service
    host_name                   src
    service_description         ASM Diskgroup Free Space
    check_command               check_oracle_health_asm_disk_used!1521!pdb1.amir.net!nagios!nagios!asm-diskgroup-usage
        }

[root@emcl ~]# vi /usr/local/nagios/etc/objects/commands.cfg

define command {
  command_name check_oracle_health_asm_disk_used
  command_line $USER1$/check_oracle_health --connect=$HOSTADDRESS$:$ARG1$/$ARG2$ --username=$ARG3$ --password=$ARG4$ --mode $ARG5$
}