Skip to main content

Oracle Database

Overview​

Oracle Database is a database management system produced and marketed by Oracle that allows users to define, create, maintain and control access to databases. The Centreon Plugin-Pack Oracle Database aims to retrieve informations and status from the Oracle database using Oracle Instant Client.

Plugin-Pack assets​

Monitored objects​

  • Oracle server

Collected Metrics​

Metric nameDescriptionUnit
connection_timeConnection time to the databasems

Prerequisites​

RPM​

In order to use this template, the wget command-line tool and the GNU Compiler Collection (gcc) are necessary.

yum install -y gcc wget

Oracle instant client​

Go to Instant Client Downloads, choose the right OS your Poller is running on (Linux x86-64) and download the following packages:

  • oracle-instantclient-basic
  • oracle-instantclient-sqlplus
  • oracle-instantclientdevel

Install the RPM Package manually:

rpm -ivh oracle-*.rpm

Perl library for Oracle​

As root, run:

cd /usr/local/src 
wget http://www.cpan.org/modules/by-module/DBD/DBD-Oracle-1.80.tar.gz
tar xzf DBD-Oracle-1.80.tar.gz
cd DBD-Oracle-1.80
export ORACLE_HOME=/usr/lib/oracle/21/client64
export LD_LIBRARY_PATH=/usr/lib/oracle/21/client64/lib
export PATH=$ORACLE_HOME:$PATH
perl Makefile.PL -m /usr/share/oracle/21/client64/demo/demo.mk

The following message should appear:

LD_RUN_PATH=/usr/lib/oracle/21/client64/lib*
Using DBD::Oracle 1.80.
Using DBI 1.52 (for perl 5.008008 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/5.8.8/x86\_64-linux-thread-multi/auto/DBI/
Writing Makefile for DBD::Oracle

If you get an error during the Makefile.PL step, explicitely force the Oracle version to a named version (it will not impact the plugin operations): perl Makefile.PL -V 12.1.0 -m /usr/share/oracle/21/client64/demo/demo.mk

Compile the library:

make

Install it:

make install

Create the file /etc/ld.so.conf.d/oracle.conf and add one line representing the path to the library:

cat > /etc/ld.so.conf.d/oracle.conf <<EOF
/usr/lib/oracle/21/client64/lib/
EOF

Update library cache with the following command:

/sbin/ldconfig

User account​

The safest way to retrieve information from the Oracle server is to create a dedicated user for Centreon.

This user account must have the READ (Oracle 12+) or SELECT (Oracle < 12) permission on following tables:

  • dba_free_space
  • dba_data_files
  • dba_temp_files
  • dba_segments
  • dba_jobs
  • dba_objects
  • DBA_MVIEW_refresh_times
  • dba_indexes
  • dba_ind_partitions
  • dba_ind_subpartitions
  • dba_registry
  • dba_tablespaces
  • DBA_MVIEW_refresh_times
  • DBA_TABLESPACE_USAGE_METRICS
  • v_$sysstat
  • v_$sgastat
  • v_$parameter
  • v_$process
  • v_$session
  • v_$filestat
  • v_$log
  • v_$instance
  • V_$ASM_DISKGROUP
  • v_$database_block_corruption
  • v_$tempstat
  • v_$rowcache
  • v_$system_event
  • v_$recovery_area_usage
  • v_$librarycache
  • v_$sql_monitor
  • v_$resource_limit
  • v_$rman_status
  • v_$backup
  • v_$rman_status
  • v_$rollstat
  • v_$resource_limit
  • v_$tablespace
  • v_$event_name
  • v_$waitstat

Setup​

  1. Install the Centreon Plugin package on every Centreon poller expected to monitor a Oracle Database:
yum install centreon-plugin-Applications-Databases-Oracle
  1. On the Centreon Web interface, install the Oracle Database Centreon Plugin-Pack on the "Configuration > Plugin Packs > Manager" page

Configuration​

  • Log into Centreon and add a new Host through "Configuration > Hosts".
  • Apply the relevant Host Template "App-DB-Oracle-custom", and configure the mandatory Macros:
MandatoryNameDescription
XORACLEPASSWORDThe oracle user's password
XORACLEPORTBy default: 1521
XORACLESIDThe name of the oracle instance
XORACLEUSERNAMEThe oracle user name
ORACLESERVICENAMEThe oracle service name

FAQ​

How can I test the Plugin in the CLI and what do the main parameters stand for ?​

Once the plugin installed, log into your Centreon Poller CLI using the centreon-engine user account and test the Plugin by running the following command:

/usr/lib/centreon/plugins//centreon_oracle.pl \
--plugin=database::oracle::plugin \
--hostname='10.30.2.38' \
--port='1521' \
--sid='XE' \
--username='SYSTEM' \
--password='Centreon75' \
--mode='tablespace-usage' \
--warning-tablespace='90' \
--critical-tablespace='98' \
--verbose

Expected command output is shown below:

OK: All tablespaces are OK | 'tbs_sysaux_usage_sysaux'=552075272B;0:27596154624;0:29069940992;0;30595726360 'tbs_system_usage_system'=945684080B;0:27636154624;0:29065940982;0;30595527360 'tbs_temp_usage_temp'=0B;0:27536080897;0:29065863169;0;30595645450 'tbs_users_usage_users'=2818049B;0:27536154625;0:29065940993;0;30595727460
Tablespace 'sysaux' Total: 29.48 GB Used: 527.60 MB (1.90%) Free: 27.88 GB (98.20%)
Tablespace 'system' Total: 29.48 GB Used: 902.76 MB (3.09%) Free: 27.71 GB (96.91%)
Tablespace 'temp' Total: 29.48 GB Used: 0.00 B (0.00%) Free: 28.59 GB (100.00%)
Tablespace 'users' Total: 29.48 GB Used: 2.78 MB (0.01%) Free: 28.48 GB (99.99%)

The above command checks the used space in tablespaces (--mode='tablespace-usage') on a oracle database installed in the host 10.30.2.38 (--hostname='10.30.2.38') It uses Oracle informations (--username='SYSTEM' --password='Centreon75' --port='1521' --sid='XE') to connect to the database.

The check provides a warning if the percentage of used space exceeds 90% (--warning-tablespace='90') and a critical if this percentage exceeds 98% (--critical-tablespace='98').

The available thresholds as well as all of the options that can be used with this Plugin can be displayed by adding the --help parameter to the command:

/usr/lib/centreon/plugins//centreon_oracle.pl \
--plugin=database::oracle::plugin \
--mode='tablespace-usage' \
--help

You can display all of the modes that come with the Plugin with the command below:

/usr/lib/centreon/plugins//centreon_oracle.pl \
--plugin=database::oracle::plugin \
--list-mode

Why do I get the following message:​

UNKNOWN: Cannot connect: (no error string) |​

This error message means that the Centreon Plugin couldn't successfully connect to the Oracle database. Check that an Oracle database is installed on this host. Check also that no third party device (such as a firewall) is blocking the connection.

DBD::Oracle is not root directory |​

This error message means that the module DBD::Oracle is installed under the /root directory. Remove shell environment variable with PERL and compile DBD::Oracle Perl Module.