Monitoring Oracle databases with Xymon

This is a quick run-down of what to install when you want to monitor an Oracle database with Xymon. It is based on a Xymon server running on Debian Linux - if you have a different Linux version, the software packages will probably have slightly different names. The guide also assumes that you already have the Xymon installed on the server that is being used for the Oracle monitoring.

Getting the software

You will need to download the following software packages:

The Oracle Instant-Client software from Oracle - get the RPM files and the "full" client package - not the "lite" one
The DBD::Oracle Perl module from CPAN
The Hobbit Client for BEA, Netapp and DB by Francesco Duranti

There are some additional software packages that will be installed from the Debian archives.

Installing the software

First, install some standard software packages:

apt-get install alien fakeroot # For converting rpm to deb apt-get install libaio1 # For Oracle apt-get install libdbi-perl # DBI core module apt-get install libsys-sigaction-perl # For Xymon DB module apt-get install libconfig-inifiles-perl # For Xymon DB module apt-get install libdbd-mysql-perl # Mysql driver for DBI apt-get install libdbd-odbc-perl # mssql driver for DBI

The next step is to convert the Oracle installation files to Debian format. Oracle distributes the Oracle client in RPM-format, but it can easily be converted into Debian package format if you prefer to have all your software managed by dpkg/APT - and I do.

fakeroot alien oracle-instantclient11.2-basic-*.rpm fakeroot alien oracle-instantclient11.2-devel-*.rpm fakeroot alien oracle-instantclient11.2-sqlplus-*.rpm dpkg -i oracle*deb

Configure your system environment for Oracle

The Oracle client needs some environment variables to work correctly. There are various ways of doing this, but this method puts the environment variables into your global setup so it is available to all tasks:

cat >/etc/profile.d/oracleenv.sh <<EOF if test "$LD_LIBRARY_PATH" = "" then LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client/lib else LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client/lib:$LD_LIBRARY_PATH fi ORACLE_HOME=/usr/lib/oracle/11.2/client NLS_LANG=American_America.WE8ISO8859P1 export LD_LIBRARY_PATH ORACLE_HOME NLS_LANG EOF . /etc/profile.d/oracleenv.sh

The reason for putting an NLS_LANG setting here is that the Xymon Oracle-Perl script complains if decimal numbers use a comma as the decimal separator instead of a dot - which happens to be the case where I live. The NLS_LANG setting overrules the default locale setting for the Oracle client, and makes the script work as expected.

Test that your Oracle client works

At this stage, it is a good idea to test that your Oracle client installation works, and you can connect to a database. Use the $ORACLE_HOME/bin/sqlclient utility to connect to a database - you will need the database TNS listener IP-address and portnumber, the SID (database name) and a username+password. Your DBA can provide all of those.

$ORACLE_HOME/bin/sqlplus username/password@IPaddress:portnumber/SID

This should result in you being logged into the database and connected to an "SQL>" prompt. If it does, then your Oracle client works.

Build the Perl DBI::Oracle module

The Perl database-interface (DBI) uses various database-drivers (DBD) to talk to the different kinds of databases. The Oracle driver requires the Oracle client/developer package to build, so it cannot be distributed with Debian - therefore you'll have to build it yourself.

tar zxf DBD-Oracle-1.30.tar.gz cd DBD-Oracle-1.30/ perl Makefile.PL make make install

Install the Xymon/Hobbit database-client

There are several database add-ons for Xymon, but I use the one by Francesco Duranti. Unpack the archive, then copy the *.pl and *.pm files to your ~xymon/server/ext/ directory, and the ini-files to your ~xymon/server/etc/ directory:

tar zxf hobbit_perl_client-1.21.tar.gz cd hobbit-perl-client/ ./dbcheck.pl # Should work, but complain about BBHOME undefined cp -p *.pl *.pm ~xymon/server/ext/ cp *.ini ~xymon/server/etc/

The attempt to run "./dbcheck.pl" is simply to verify that all of the Perl modules are in place. If it complains about missing modules, you will need to fix that.

There is one setting in the dbcheck.ini file that you must change: The oraclehome setting must point to the directory where the Oracle client is installed. This will usually be /usr/lib/oracle/11.2/client (depending on the version of the Oracle client that you installed).

Configure Xymon to run the database checks

You must add a new section to the Xymon tasks.cfg file to run the database checks. Instead of modifying the original tasks.cfg file, I prefer to drop a new file into the ~xymon/server/etc/tasks.d/ directory. So either add a new file here, or add a new section to tasks.cfg. In either case, this is what you should put into that file:

[dbfast] CMD $XYMONHOME/ext/dbcheck.pl fast LOGFILE $XYMONSERVERLOGS/dbfast.log INTERVAL 5m MAXTIME 10m [dbslow] CMD $XYMONHOME/ext/dbcheck.pl slow LOGFILE $XYMONSERVERLOGS/dbslow.log INTERVAL 120m MAXTIME 30m

After adding this, Xymon will automatically pick up the new task, so within 30 seconds you should see the dbfast.log and dbslow.log files in your Xymon log-directory. Check that there are no errors in them.

Configure the individual database tests

You now have all of the tools in place and can configure the actual databases you want to monitor. To monitor an Oracle database, you need a database username that the monitoring script will use to connect to the database - this user must have some specific access rights. My DBA has provided this script to setup the user and grant the necessary permissions:

create user xymon identified by PASSWORD default tablespace users temporary tablespace temp; grant create session to xymon; grant select on dba_audit_session to xymon; grant select on dba_data_files to xymon; grant select on dba_free_space to xymon; grant select on dba_segments to xymon; grant select on dba_rollback_segs to xymon; grant select on dba_extents to xymon; grant select on dba_objects to xymon; grant select on v_$session to xymon; grant select on v_$lock to xymon; grant select on v_$librarycache to xymon; grant select on v_$sysstat to xymon; grant select on v_$shared_pool_reserved to xymon; grant select on v_$parameter to xymon; grant select on v_$process to xymon; grant select on v_$session to xymon; grant select on v_$active_instances to xymon; grant select on v_$instance to xymon; grant select on v_$rowcache to xymon;

Once that is in place, you must add the database in two places for the monitoring to work:

In the hosts.cfg file, with the "dbcheck" tag
In the dbcheck.ini file, where you specify the connection parameters and tests

The entry in hosts.cfg could look like this:

192.168.1.10 ACCOUNTS_DB # noconn dbcheck

I usually add "noconn" because it doesn't make much sense to do a ping-test of the database - the database monitoring script has a "ChkConn" test that does the same - and more.

The corresponding entry in dbcheck.ini could look like this:

[ACCOUNTS_DB] dbtype = Oracle hostname = dbserver1 port = 1521 sid = ACCOUNTS username = xymon password = password

There are several other definitions you can add to dbcheck.ini, e.g. to customize the thresholds for how full a tablespace can be before triggering a warning or an alert. See the comments in dbcheck.ini.

And that's it!

Happy monitoring.