Tech Tip: Monitoring Oracle Database Servers

This month Anders provides insight on monitoring Oracle databases using Nagios.

Since Oracle’s database server is designed to meet a very broad range of usage scenarios, it can be quite time-consuming to maintain Oracle installations. The database administrator has to keep track of the current usage of several kinds of resources and extents, make sure that the SID-resolution works as expected, tune cache-settings, make sure that log archiving works properly, keep track of backup-modes, manually run test-queries to verify that the database works as expected for specific applications, and verify that transactions work as they should.

The individual tasks are not very time-consuming, but the hours add up when executed over and over again on several installations.

Luckily, there’s a Nagios plugin which can do most of the job for you – check_oracle.pl.

There are several Nagios plugins for Oracle available on the web. The one described in this tech tip was designed in co-operation with an Oracle DBA.

There are several nagios-plugins for Oracle available on the web. The one described in this tech tip was designed in co-operation with an Oracle DBA.

check_oracle.pl depends on Oracle Instant Client and a user with sufficient access to the databases you want to monitor. The plugin can monitor:

  • database availability (tnsping)
  • login or dummy login
  • cache hit ratio
  • tablespace usage
  • datafile usage
  • total number of datafiles
  • available extents per table or tablespace
  • log archiving enabled or not
  • backupmode
  • user defined query, match for string or numeric value
  • locks
  • broken transactions
  • failed transactions
  • transactions in the deferred error queue
  • invalid transactions

The plugin supports placing authentication credentials in an auth-file and can handle hostname as input instead of using a .ora-file for SID-lookups.

The upstream version of the plugin is available from the original authors here:
http://git.op5.org/git/?p=nagios/op5plugins.git;a=blob_plain;f=check_oracle.pl;hb=HEAD