Simon Krenger

For Nagios, many, many Oracle plugins are available for checking database availability and performance. But if you just want to check if the instance is up and running (and not add more complexity), you can use the simple script provided here.

In an earlier post, I showed how to install SQL*Plus on Debian and based on that tutorial, I wrote a little shell script to query a database (I called it check_oracle_dual.sh):

#!/bin/bash

ORACLE_INSTANTCLIENT_FOLDER=/opt/oracle/instantclient_11_2/
ORACLE_SQLPLUS_BINARY=sqlplus

ORACLE_USERNAME=
ORACLE_PASSWORD=
HOST=
INSTANCE=test01
ASSYSDBA=0

VERBOSE=0

usage() {
cat << EOF
usage: $0 -h  -u  -p  [-i ] [-s] [-v]

This script connects to the specified Oracle instance and executes a simple
statement. If that statement succeeds, the script returns 0.

OPTIONS:
   -h      Specify the host (required)
   -u      Oracle username (required)
   -p      Oracle password for the user (required)
   -i      SID of the instance (default: test01)
   -s      Force login AS SYSDBA
   -v      Verbose
EOF
}


while getopts "u:p:i:h:vs" OPTION; do
        case $OPTION in
                u)
                        ORACLE_USERNAME=$OPTARG
                        ;;
                p)
                        ORACLE_PASSWORD=$OPTARG
                        ;;
                i)
                        INSTANCE=$OPTARG
                        ;;
                v)
                        VERBOSE=1
                        ;;
                h)
                        HOST=$OPTARG
                        ;;
                s)
                        ASSYSDBA=1
                        ;;
                ?)
                        usage
                        exit 1
                        ;;
        esac
done

if [ -z "$ORACLE_USERNAME" ]; then
        echo "You must specify a username (-u)!"
        usage
        exit 1
fi


if [ -z "$ORACLE_PASSWORD" ]; then
        echo "You must specify a password (-p)!"
        usage
        exit 1
fi


if [ -z "$HOST" ]; then
        echo "You must specify a host (-h)!"
        usage
        exit 1
fi

if [[ "$ORACLE_USERNAME" == "sys" || "$ORACLE_USERNAME" == "SYS" ]]; then
        ASSYSDBA=1
fi

export LD_LIBRARY_PATH=$ORACLE_INSTANTCLIENT_FOLDER
export ORACLE_SID=$INSTANCE

CONNECT_STRING=$(echo $ORACLE_USERNAME/$ORACLE_PASSWORD@$HOST/$INSTANCE)

if [ $ASSYSDBA -eq 1 ]; then
        CONNECT_STRING="$CONNECT_STRING AS SYSDBA"
fi

SPOUT=$($ORACLE_INSTANTCLIENT_FOLDER/$ORACLE_SQLPLUS_BINARY -S "$CONNECT_STRING" << EOF
SET ECHO OFF
SET HEADING OFF
SELECT to_char(sysdate,'yyyy-mm-dd') FROM dual;
EOF
)

if [ $? -eq 0 ]; then
        TRIMMED=$(echo $SPOUT)
        echo "OK: $ORACLE_USERNAME@$INSTANCE, sysdate='$TRIMMED'"
        if [ $VERBOSE -eq 1 ]; then
                echo "| host=$HOST, username=$ORACLE_USERNAME, instance=$INSTANCE, as_sysdba=$ASSYSDBA"
        fi
        exit 0
else
        # Remove the \n from the output of SQL*Plus
        SPOUT=$(echo $SPOUT | tr '\n' ' ')
        echo "ERROR: sqlplus returned $? : $SPOUT"
        echo " | CONNECT_STRING=$CONNECT_STRING"
        exit 2
fi

Save this script (usually, you put it in the Nagios plugin folder, /usr/lib/nagios/plugins/ in my case) and use chmod to make it executable (chmod +x check_oracle_dual.sh).

You might have to edit the ORACLE_INSTANTCLIENT_FOLDER to fit your environment (it should point to a directory containing the sqlplus executable.

Then, define a new command in your Nagios configuration file (more information on Nagios configuration here):

define command{
        command_name    check_oracle_dual
        command_line    $USER1$/check_oracle_dual.sh -v -h $HOSTADDRESS$ -u $ARG1$ -p $ARG2$ $ARG3$
}

After adding the command definition, add the service to an existing host like this (this queries the instance “dev01” on host “mydbhost” using the credentials “scott/tiger”):

define service {
    use                     generic-service
    host_name               mydbhost
    service_description     Oracle Instance Query
    check_command           check_oracle_dual!scott!tiger!-i dev01
}

This will try to connect to the database using the connection string

$ORACLE_USERNAME/$ORACLE_PASSWORD@$HOST/$INSTANCE

so for example

scott/tiger@mydbhost/dev01

Some people in the comments have noted that if they had their TNSNAMES set up correctly, they were able to just use the connection string

$ORACLE_USERNAME/$ORACLE_PASSWORD@$INSTANCE