Monitoring Oracle databases

Abstract

Introduction

Overview

Steps

What’s Already Done For You

To make your life a bit easier, a few configuration tasks have already been done for you:

Installation of Oracle Instant Client

oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64
oracle-lib-compat-11.2.0.12-1.el6.x86_64
oracle-instantclient-sqlplus-selinux-11.2.0.2-1.el6.noarch
perl-DBD-Oracle-1.62-3.el6.x86_64
oracle-nofcontext-selinux-0.1.23.36-1.el6.noarch
nagios-plugins-oracle-1.4.16-10.el6.x86_64
oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64
oracle-instantclient-selinux-11.2.0.2-1.el6.noarch

Installation of check_oracle_health plugin
- /var/lib/shinken/libexec/check_oracle_health

We are supposing here that the Oracle machine you want to monitor is named srv-lin-1 and is a linux. Please change the above lines and commands with the real name of your server of course.

Setup the oracle user account

Create the database user

You will need to configure the user for all your oracle databases.

Connect to your database as sysadmin on the oracle server :

srv-lin-1:oracle# sqlplus "/ as sysdba"

And then create your shinken account on the database:

CREATE USER shinken IDENTIFIED BY shinkenpassword;GRANT CREATE SESSION TO shinken;
GRANT SELECT any dictionary TO shinken;
GRANT SELECT ON V_$SYSSTAT TO shinken;
GRANT SELECT ON V_$INSTANCE TO shinken;
GRANT SELECT ON V_$LOG TO shinken;
GRANT SELECT ON SYS.DBA_DATA_FILES TO shinken;
GRANT SELECT ON SYS.DBA_FREE_SPACE TO shinken;

And for old 8.1.7 database only:

---- if somebody still uses Oracle 8.1.7...
GRANT SELECT ON sys.dba_tablespaces TO shinken;
GRANT SELECT ON dba_temp_files TO shinken;
GRANT SELECT ON sys.v_$Temp_extent_pool TO shinken;
GRANT SELECT ON sys.v_$TEMP_SPACE_HEADER TO shinken;
GRANT SELECT ON sys.v_$session TO shinken;

Test the connection

To see if the connection to the database named PROD is ok, just launch :

/var/lib/shinken/libexec/check_oracle_health --connect "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = srv-lin-1)(PORT = 1521)))(CONNECT_DATA =(SID = PROD)))" --user "shinken" --password "shinken" --mode connection-time

Configuring host template and host for monitoring

The parameters common to every Oracle server monitored shoud be setted in the host template "oracle-connection-method".

The parameters specifics to each server, such as database name for example, have to be done on the host's data itself.

It's a best practice to use the same database monitoring user on every Oracle server monitored. Doing so, you can configure the database user/password only once in the host template "oracle-connection-method".