Monitoring ORACLE with Check_MK

Overview

As of version 1.1.9i6 Check_MK ships an agent plugin and checks for monitoring various aspects of ORACLE databases. Currently the following checks are available:

oracle_tablespaces	Check various aspects of tablespaces
oracle_sessions	Current number of active sessions
oracle_logswitches	Number of log switches in the last 60 minutes

All ORACLE related actions are directly done on the database hosts. No ORACLE software is needed on your Nagios host. The agent plugin currently supports Linux and HP-UX.

Installing the plugin

In order to use the ORACLE monitoring you need the agent plugin mk_oracle, which you will find in the agents' plugins directory on your Nagios host (usually /usr/share/check_mk/agents/plugins). As of version 1.1.9i6 this plugin is running on Linux and HP-UX. The plugin is implemented as a shell script and should be easily portable to any other Unixoid system.

Install the plugin on your database hosts into the plugins directory of the Check_MK agent. Look into the agent's code in order to learn which directory this is. The default is /usr/lib/check_mk_agent/plugins. It may vary in your installation. If you are unsure, you can grep it out of the agent:

root@linux# grep MK_LIBDIR /usr/bin/check_mk_agent
export MK_LIBDIR="/usr/lib/check_mk_agent"
PLUGINSDIR=$MK_LIBDIR/plugins
LOCALDIR=$MK_LIBDIR/local

If the directory is not yet existing, simply create it. Then put the plugin mk_oracle there and make it executable:

root@linux# mkdir -p /usr/lib/check_mk_agent/plugins
root@linux# cp mk_oracle /usr/lib/check_mk_agent/plugins
root@linux# chmod 755 /usr/lib/check_mk_agent/plugins/mk_oracle

The plugin will automatically detect all running ORACLE instaces by looking at running processes called ora_pmon_.... For each instance found that way it will query information about sessions, log switches and tablespaces and send them via the usual channel to Nagios. In future versions of the plugin, more data might be sent.

Configuring sqlplus

The agent plugin needs to be able to call sqlplus and make some simple queries in your various database instances. Since each ORACLE installation is different from each other, the plugin has no chance to do this without your help. Your task is to create a shell script called sqlplus.sh and put this into the configuration directory of the agent. This is usually /etc/check_mk. If unsure, you can again grep this from your agent:

root@linux# grep MK_CONFDIR /usr/bin/check_mk_agent
export MK_CONFDIR="/etc/check_mk"

Now create this directory (if not existing) and create a shell script sqlplus.sh there:

root@linux# mkdir -p /etc/check_mk
root@linux# vi /etc/check_mk/sqlplus.sh

This script will be called by the plugin

• as root
• with the SID of the database as only argument

The task of the script is:

• run sqlplus and provide it with all passwords and parameters, so that it will run without any user interaction
• let sqlplus read SQL code from the scripts standard input.
• exit with 0 if the query was successful, and with 1 otherwise.

An example of such a script is here. It assumes that there is a local operating system user nagios which can connect to the databases without a password via /. It also assumes that in the databases there is a user called nagios with sufficient permissions to read the tables dba_data_files and dba_tablespaces:

#!/bin/sh

# EXAMPLE

# This script is called by the Check_MK ORACLE plugin in order to
# execute an SQL query.

# It is your task to adapt this script so that the ORACLE environment
# is setup and the correct user chosen to execute sqlplus.

# The script will get the query on stdin and shall output the
# result on stdout. Error messages goes to stderr.

ORACLE_SID=$1
if [ -z "$ORACLE_SID" ] ; then
    echo "Usage: $0 ORACLE_SID" >&2
    exit 1
fi

su nagios -c "
ORACLE_SID=$ORACLE_SID
ORAENV_ASK=NO
. /usr/local/bin/oraenv
sqlplus -s /"

Note: the script sqlplus.sh must be executable in order to work:

root@linux# chmod 755 /etc/check_mk/sqlplus.sh

The plugin will send that script an SQL query via stdin. In the upper example, sqlplus -s / is the last command and will be provided with that data since it will be passed the data from stdin by su.

Please note, that you may have to adapt this script for your needs. Your user might not be nagios. Your oraenv-script might be named other and so on.

Creating an operating system user in oracle

Here we show how to create an operating system user nagios that can use ORACLE without password:

Create the operating system user nagios:

root@linux# useradd -m nagios

Login in in sqlplus as sysdba. Create a user nagios, which can login via /:

SQL> create user ops$nagios identified externally;
SQL> grant connect to ops$nagios;

Testing

If you have setup everything correctly, you should retrieve information about all your running databases when looking at the agent output from the Nagios server, e.g.:

root@linux# check_mk -d SRV-DB1-ABC

This should contain sections like <<<oracle_sessions>>> and <<<oracle_logswitches>>>. If this is not the case, you can try to call the plugin directly on your database host. Please make sure, that you export the environment variable pointing to your agent confguration directory first and the simply call the plugin executable:

root@linux# export MK_CONFDIR="/etc/check_mk"
root@linux# cd /usr/lib/check_mk_agent/plugins
root@linux# ./mk_oracle

This might output some more information since the Check_MK agent suppresses all messages on stderr.