Going beyond the normal application availability check
One interesting use of the OpenNMS JDBC poller is for extracting data from the Oracle administrative database tables, for example tracking tablespace quota usage to detect quota exhaustion, sudden usage peaks and graph usage over time.
Graph of quota usage for user [redacted] on tablespace DAT. Notice the cleaning operation running at 3.30 AM
Tablespace quotas is a feature present in the Oracle database that allows the DBA to set a limit on the amount of storage that any given user can consume on a specific tablespace. This allows the DBA to share tablespaces across users yet still be able to policy users into predefined usage boundaries. When a user consumes all its quota it can no longer store data, but it can delete it, thus allowing self-recovery.
Configuring OpenNMS to monitor quota usage is rather simple. First of all make sure that you have the Oracle JDBC driver in $OPENNMS_HOME/lib. If not download and copy the jar file in that directory. Do not restart OpenNMS as we will need to restart it later.
Now cd into $OPENNMS_HOME/etc, make a backup copy of the configuration files (not necessary if you already use version control) and then make the changes described below. The code is available at this location. Note that in these files I report only the relevant fragments. It should be straightforward to merge these fragments in the right context of your files.
Things that you will have to change and adapt to your environment:
user and password of the Oracle user used to connect to the Oracle database and query the dba_ts_quotas table. Your DBA should also take care of granting the appropriate rights to this user
hostname and service name for the JDBC URL. This is repeated in two different places, so make sure to change them all. If you don't know the right server and service names consult with your DBA. It might get tricky especially with Oracle RAC configurations. Usually I first try with OPENNMS_JDBC_HOSTNAME, then, if it fails, I fall back to specifying an hostname. Caution: if you specify an hostname, say srvora1, AND assign this service to another host, say srvora2, you will NOT be monitoring quotas on srvora2, but rather on srvora1!
since 1.10 the datacollection-config.xml fragment can be modularized in the datacollection directory as described here
After the required changes are in place restart OpenNMS and then assign the OracleMonitoring service to the right node. Please note that due the way that the JDBC URL is constructed it is not possible to assign this service to more than one database instance, unless they all have the same service name.
To monitor database instances with different service names one must duplicate the whole configuration, with perhaps the exception of graphs.
The graphs will be shown only for Oracle users that have quotas set (ie MaxBytes > 0) on at least one tablespace. Users without quotas will not be shown in the graphs list for the host.
Thresholds and notifications
To enable notifications we must first establish thresholds. For that see the last two code fragments in the gist above. I didn't want to create custom UEIs, so I didn't specify any in the UEI fields.
Note that I have specified the UserName in the datasource label field. This allows us to show useful information in the notification message such as the tablespace and user that have triggered or rearmed the threshold by inserting the %parm[label]% tag in the subject and/or message body.
