Monitoring Oracle databases¶

Abstract

This document describes how you can monitor an Oracle database server such as:

• Connection time
• A recent restart
• The number of connections
• Cache hit
• etc ...

Introduction¶

These instructions assume that you’ve installed Shinken according to the Installation tutorial. The sample configuration entries below reference objects that are defined in the sample config files (“commands.cfg”, “templates.cfg”, etc.) that are installed if you follow the quickstart.

Overview¶

Note

TODO: draw a oracle diag

Monitoring an Oracle server need the plugin check_oracle_health available at labs.consol.de/lang/en/nagios/check_oracle_health/ and an oracle user account for the connection.

Steps¶

There are some steps you’ll need to follow in order to monitor a new database machine. They are:

• Install dependencies
• Install check plugins
• Setup the oracle user account
• Creating an alias definition for Oracle databases
• Update your server host definition for oracle monitoring
• Restart the Shinken daemon

What’s Already Done For You¶

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

• Some check_oracle_ commands definition has been added to the “commands.cfg” file.
• An Oracle host template (called “oracle”) has already been created in the “templates.cfg” file.

The above-mentioned config files can be found in the ///etc/shinken/// directory (or c:shinkenetc under windows). You can modify the definitions in these and other definitions to suit your needs better if you’d like. However, I’d recommend waiting until you’re more familiar with configuring Shinken before doing so. For the time being, just follow the directions outlined below and you’ll be monitoring your Oracle boxes in no time.

Tip

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.

Installing dependencies¶

Installing SQL*Plus on the Shinken server¶

Check_oracle_health plugin needs sqlplus oracle client on the Shinken server, you can download packages on the Oracle Technology Network website. You need to have these 3 packages: oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm oracle-instantclient11.2-devel-11.2.0.3.0-1.x86_64.rpm oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

You can install them like this:

linux:~ # rpm -Uvh oracle-instantclient11.2-*


Then you have to create some symbolic links in order to have commands in the path:

linux:~ # ln -s /usr/lib/oracle/11.2/client64/bin/adrci /usr/bin/adrci
linux:~ # ln -s /usr/lib/oracle/11.2/client64/bin/genezi /usr/bin/genezi
linux:~ # ln -s /usr/lib/oracle/11.2/client64/bin/sqlplus /usr/bin/sqlplus


Also, you need to export Oracle environment variables in order to install CPAN modules:

linux:~ # export ORACLE_HOME=/usr/lib/oracle/11.2/client64
linux:~ # export PATH=$PATH:$ORACLE_HOME/bin
linux:~ # export LD_LIBRARY_PATH=$ORACLE_HOME/lib  Installing CPAN modules¶ linux:~ # perl -MCPAN -e shell cpan[1]> install DBI cpan[2]> force install DBD::Oracle  Installing the check plugins on Shinken¶ First connect as root under you Shinken server (or all poller servers for a multi-box setup) and launch: shinken.sh -p check_oracle_health  Setup the oracle user account¶ Tip 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;  Then you will need to configure your user/password in the macros file so the plugins will have the good values for the connction. So update the /etc/shinken/resource.cfg file or c:\shinken\etc\resource.cfg file to setup the new password: $ORACLEUSER$=shinken$ORACLEPASSWORD$=shinkenpassword  Creating an alias definition for Oracle databases¶ First, you have to create a tnsnames.ora config file on the shinken server that will contain the alias definition for PROD database: linux:~ # mkdir -p /usr/lib/oracle/11.2/client64/network/admin linux:~ # vim /usr/lib/oracle/11.2/client64/network/admin/tnsnames.ora PROD = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.X)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PROD) ) ) :wq  Note that you have to declare all databases that you want to monitor with Shinken in this file. For example, if you want to monitor ERP and FINANCE databases, your config file will look like this: ERP = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.X)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ERP) ) ) FINANCE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.X)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FINANCE) ) )  Then, you need define an environment variable that will contain the path of this file with also all others variables related to sqlplus: linux:~ # vi /etc/profile.d/oracle.sh export PATH=$PATH:/usr/lib/oracle/11.2/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib
export ORACLE_HOME=/usr/lib/oracle/11.2/client64

Restarting Shinken¶

You’re done with modifying the Shiknen configuration, so you’ll need to verify your configuration files and restart Shinken.

If the verification process produces any errors messages, fix your configuration file before continuing. Make sure that you don’t (re)start Shinken until the verification process completes without any errors!

Read the Docs v: latest
Versions
latest
documentation