Skip to main content

Oracle Database

Pack Assets​

Templates​

The Centreon Monitoring Connector Oracle Database brings a host template:

  • App-DB-Oracle-custom

It brings the following service templates:

Service AliasService TemplateService DescriptionDefaultDiscovery
ASM-Diskgroup-Usage-Generic-NameApp-DB-Oracle-ASM-Diskgroup-Usage-Generic-NameCheck ASM groupdisk usage and status to the Oracle server
ASM-Diskgroup-Usage-GlobalApp-DB-Oracle-ASM-Diskgroup-Usage-GlobalCheck ASM groupdisk usage and status to the Oracle serverX
Connection-NumberApp-DB-Oracle-Connection-NumberCheck connection number to the Oracle serverX
Connection-TimeApp-DB-Oracle-Connection-TimeCheck the connection time to the server. This time is given in secondsX
Corrupted-BlocksApp-DB-Oracle-Corrupted-BlocksCheck the number of corrupted blocks on the serverX
Data-Files-StatusApp-DB-Oracle-Data-Files-StatusCheck Oracle data files status
Datacache-HitratioApp-DB-Oracle-Datacache-HitratioCheck the 'Data Buffer Cache Hit Ratio' of the server. No alerts by defaultX
Dictionary-Cache-UsageApp-DB-Oracle-Dictionary-Cache-UsageCheck dictionary cache usage
Event-Waits-UsageApp-DB-Oracle-Event-Waits-UsageCheck event wait usage
Fra-UsageApp-DB-Oracle-Fra-UsageCheck fast recovery area space usage
Invalid-ObjectApp-DB-Oracle-Invalid-ObjectCheck invalid objects
Library-Cache-UsageApp-DB-Oracle-Library-Cache-UsageCheck library cache usage
Long-QueriesApp-DB-Oracle-Long-QueriesCheck long queries
Process-UsageApp-DB-Oracle-Process-UsageCheck Oracle process usedX
Redolog-UsageApp-DB-Oracle-Redolog-UsageCheck redo log usage
Rman-Backup-AgeApp-DB-Oracle-Rman-Backup-AgeCheck RMAN backup age
Rman-Backup-Online-AgeApp-DB-Oracle-Rman-Backup-Online-AgeCheck RMAN backup age in online mode
Rman-Backup-ProblemsApp-DB-Oracle-Rman-Backup-ProblemsCheck RMAN backup errors of the server during the last three daysX
Rollback-Segment-UsageApp-DB-Oracle-Rollback-Segment-UsageCheck rollback segment usage
Session-UsageApp-DB-Oracle-Session-UsageCheck session usedX
SqlApp-DB-Oracle-Sql-Statement-GenericCheck allowing to execute a custom SQL request with a digital answer
Sql-StringApp-DB-Oracle-Sql-Statement-String-GenericCheck allowing to execute a custom SQL request with a string answer
Tablespace-Usage-GlobalApp-DB-Oracle-Tablespace-Usage-GlobalCheck the tablespace usage of the serverXX
TnspingApp-DB-Oracle-TnspingCheck the connection to a remote listenerX

Discovery rules​

Rule NameDescription
App-DB-Oracle-ASM-Diskgroup-Usage-NameDiscover the disk partitions and monitor space occupation
App-DB-Oracle-Tablespaces-Usage-Name

More information about discovering services automatically is available on the dedicated page and in the following chapter.

Collected metrics & status​

Metric nameDescriptionUnit
connection_timeConnection time to the databasems

Prerequisites​

Dependencies​

dnf install 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 (RPM):

  • 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 https://www.cpan.org/modules/by-module/DBD/DBD-Oracle-1.83.tar.gz 
tar xzf DBD-Oracle-1.83.tar.gz 
cd DBD-Oracle-1.83 
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.83.
Using DBD::Oracle 1.83.
Using DBI 1.641 (for perl 5.026003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
Generating a Unix-style Makefile
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​

Monitoring Pack​

If the platform uses an online license, you can skip the package installation instruction below as it is not required to have the pack displayed within the Configuration > Monitoring Connectors Manager menu. If the platform uses an offline license, install the package on the central server with the command corresponding to the operating system's package manager:

dnf install centreon-pack-applications-databases-oracle

Whatever the license type (online or offline), install the Oracle Database Pack through the Configuration > Monitoring Connectors Manager menu.

Plugin​

Since Centreon 22.04, you can benefit from the 'Automatic plugin installation' feature. When this feature is enabled, you can skip the installation part below.

You still have to manually install the plugin on the poller(s) when:

  • Automatic plugin installation is turned off
  • You want to run a discovery job from a poller that doesn't monitor any resource of this kind yet

More information in the Installing the plugin section.

Use the commands below according to your operating system's package manager:

dnf install centreon-plugin-Applications-Databases-Oracle

Configuration​

Host​

  • Log into Centreon and add a new host through Configuration > Hosts.
  • Fill the Name, Alias & IP Address/DNS fields according to your Oracle Database server settings.
  • Apply the App-DB-Oracle-custom template to the host.
  • Once the template is applied, fill in the corresponding macros. Some macros are mandatory.
MandatoryNameDescription
XORACLEPASSWORDThe oracle user's password
XORACLEPORTOracle port (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 is installed, log into your Centreon poller's CLI using the centreon-engine user account (su - centreon-engine) 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

Troubleshooting​

Please find the troubleshooting documentation for Centreon Plugins typical issues.

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.