You are here

Oracle Database Monitoring

Requires Opsview Cloud or Opsview Monitor 6
check_circle
Opsview Supported

Oracle Database Monitoring Tools

Oracle Corporation is a global computer technology corporation that primarily specializes in developing and marketing database software and technology, cloud engineered systems and enterprise software products, particularly its own brands of database management systems. The company also develops and builds tools for database development and systems of middle-tier software, enterprise resource planning (ERP) software, customer relationship management (CRM) software and supply chain management (SCM) software.

What You Can Monitor

Opsview's Oracle database monitoring tools include over 53 performance metrics including system stats, tablespace usage, SGA information and more. This allows you to make sure your database is running at full speed, enabling you to monitor any and every change over time with our Opsview Reporting Module.

Service Checks

Service Check Description
Connected users How many users connected to Oracle
Corrupted blocks in database Number of corrupted blocks in database
Datafile IO Traffic io operations/per sec of a datafile
Datafiles possible maximum number Percentage of the maximum possible number of datafiles
Enqueue contention percentage of enqueue requests which must wait
Enqueue waiting percentage of time spent waiting for the enqueue
Flash recovery area free Free space in flash recovery area
Free space fragmentation index Free space fragmentation index
Invalid Objects How many invalid objects in DB
Latch contention Percentage of latch get requests which must wait
Latch waiting Percentage of time a latch spends sleeping
List all - background events Convenience function which lists all background events
List all - datafiles Convenience function which lists all datafiles
List all - enqueues Convenience function which lists all enqueues
List all - events Convenience function which lists all events
List all - latches Convenience function which lists all latches
List all - sysstats Convenience function which lists all statistics from sysstat
List all - tablespaces Convenience function which lists all tablespaces
Maximum sessions How many sessions of Oracle are being used
PGA in-memory sort ratio PGA in-memory sort ratio
Processes wait events Processes wait events
Processes wait time spent Time spent by processes waiting for an event
Redo buffer allocation retries Redo buffer allocation retries
Redo IO Traffic Redo log io bytes per second
RMAN Backup Errors Number of rman backup errors during the last 3 days
Rollback segment average active size Rollback segment average active size
Rollback segment block contention Rollback segment block contention
Rollback segment extends per sec Rollback segment extends (per sec)
Rollback segment header contention Rollback segment header contention
Rollback segment hit ratio Rollback segment hit ratio (gets/waits)
Rollback segment wraps Rollback segment wraps (per sec)
SGA Data Buffer Hit Ratio Data Buffer Cache Hit Ratio
SGA Dictionary Cache Pin Hit Ratio Dictionary Cache Hit Ratio
SGA Latches Hit Ratio Latches Hit Ratio
SGA Library Cache Get Hit Ratio Library Cache (Get) Hit Ratio
SGA Library Cache Pin Hit Ratio Library Cache (Pin) Hit Ratio
SGA Library cache reload rate Library Cache Reload (and Invalidation) Rate
SGA Shared pool free Shared Pool Free Memory
SGA shared pool reload ratio Shared Pool Reloads vs. Pins
Soft parses percentage Percentage of soft parses
Stale optimizer statistics Find objects with stale optimizer statistics
Sys stat Change of sysstat values over time
Tablespace can allocate next Segments (of a tablespace) can allocate next extent
Tablespace Free Percentage of tablespace free on SYSTEM tablespace
Tablespace remaining time Remaining time until a tablespace is full
Tablespace usage Used space in tablespaces
Time between redo log file switches Time between redo log file switches
Time to connect Time to connect to server
tnsping check tnsping check to Oracle server
Used space in Flash Recovery Area How much space in FRA is used
User objects - top 10 buffer busy waits User objects among top 10 buffer busy waits
User objects - top 10 logical reads User objects among top 10 logical reads
User objects - top 10 row lock waits User objects among top 10 row lock waits

Oracle Database Monitoring Prerequisites

For legal reasons, we cannot distribute Oracle Instant Client with Opsview Monitor, so you will need to install this manually on your Opsview Monitor master and slaves. See the Platform Specific Steps for the steps required to install.

This has been tested against Oracle Database 11g Express Edition, however the monitoring should be uniform across all Oracle database platforms. This has been tested with Oracle Instant Client version 11.2.0.3.0 and DBD::Oracle 1.58

Download the Oracle Instant Client Files

Download the Oracle Instant Client files here. We recommend you select the latest versions of all packages. You will need an account with Oracle to access the packages.

For Debian, Ubuntu, CentOS, RHEL or SLES, download the Linux x86 or x86-64 based on the appropriate architecture of your operating system. Download the .rpm format file (not the .zip). You will need the following packages:

  • oracle-instantclient11.2-basic
  • oracle-instantclient11.2-sqlplus
  • oracle-instantclient11.2-devel

The rest of this section assumes that Oracle Instant Client is at version 11.2.0.3.0.

Debian and Ubuntu

Debian and Ubuntu need to install the alien package, in order to convert the Oracle Instant Client files from .rpm to .deb format. There is also a requirement for having libaio1. Run this as root:

apt-get install alien libaio1 fakeroot alien

Convert the packages into a .deb equivalent:

fakeroot alien --to-deb oracle-instantclient*.rpm

Now install these newly created packages:

sudo dpkg -i oracle-instantclient11.2-basic_11.2.0.3.0-2_i386.deb oracle-instantclient11.2-sqlplus_11.2.0.3.0-2_i386.deb oracle-instantclient11.2-devel_11.2.0.3.0-2_i386.deb

CentOS, RHEL, SLES

Install the packages with:

sudo rpm -i oracle-instantclient11.2-basic-11.2.0.3.0-1.i386.rpm
sudo rpm -i oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.i386.rpm
sudo rpm -i oracle-instantclient11.2-devel-11.2.0.3.0-1.i386.rpm

Environment Variables

We will need to set the bash variables of the Opsview user to run the Oracle client. To do this, we must edit the “~opsview/.bashrc” file, and add the following lines to the bottom:

export ORACLE_HOME=/usr/lib/oracle/11.2/client # For 32 bit systems
export ORACLE_HOME=/usr/lib/oracle/11.2/client64 # For 64 bit systems
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

Log out and back in as the Opsview user to confirm these variables are set correctly.

DBD::Oracle Perl Module

These steps document the installation of DBD::Oracle. This must be done manually as this will be compiled to the specific version of Oracle Instant Client that is installed. Be aware that if you upgrade Oracle Instant Client, you may have to rebuild the DBD::Oracle module.

Check if it is already installed (as opsview user):

perl -MDBD::Oracle -e 'print "Installed\n"'

If not, download the latest perl module here.

The rest of this section assumes the version is 1.58. The compilation will require the gcc package.

Once we have downloaded the file to your Opsview Monitor system, you will need to extract, and then compile, as shown below:

# as the root user

export ORACLE_HOME=/usr/lib/oracle/11.2/client # For 32 bit systems
export ORACLE_HOME=/usr/lib/oracle/11.2/client64 # For 64 bit systems
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib

tar –zxvf DBD-Oracle-1.58.tar.gz
cd DBD-Oracle-1.58
perl Makefile.PL -l
make && make test
sudo make install

The Perl module will be installed into a system area so all Perl programs can access it. If there are problems compiling or installing the module this way, it might be easier to install it via the CPAN. As root, run:

cpan DBD::Oracle

Setup and Configuration

Network Dependencies

TCP port 1521 needs to be open from the Opsview Monitor server to connect to the monitored host.

Step 1: Add the host template

Add the Database - Oracle RDBMS Host Template to your Opsview Monitor host.

Oracle Add Host Template

For more information, refer to Opsview Knowledge Center - Adding Host Templates to Hosts.

Step 2: Add and configure variables required for this host

Variable Description
ORACREDENTIALS Used in authenticating with Oracle RDBMS. Override the Username and Password with your credentials.

Oracle Add Variables

For more information, refer to Opsview Knowledge Center - Adding Variables to Hosts.

Step 3: Apply changes and the system will now be monitored

Oracle Service Checks

Troubleshooting

Missing shared libraries

If you get errors like:

$ sqlplus sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

Then you have not set the environment variables for the user correctly, specifically the LD_LIBRARY_PATH variable.

Missing aio shared library

If you get errors like:

$ sqlplus sqlplus: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

Then you need to install a package that provides the aio (asynchronous I/O) library. See the instructions above based on your OS for the appropriate package.

Plugin cannot locate DBD/Oracle

If you get errors like:

CRITICAL - cannot connect to 192.168.12.126. install_driver(Oracle) failed: Can't locate DBD/Oracle.pm in @INC

Then DBD::Oracle has not been installed correctly.