You are here
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.
|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 188.8.131.52.0 and DBD::Oracle 1.58
Download the Oracle Instant Client Files
Download the Oracle Instant Client fles 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:
The rest of this section assumes that Oracle Instant Client is at version 184.108.40.206.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_220.127.116.11.0-2_i386.deb oracle-instantclient11.2-sqlplus_18.104.22.168.0-2_i386.deb oracle-instantclient11.2-devel_22.214.171.124.0-2_i386.deb
CentOS, RHEL, SLES
Install the packages with:
sudo rpm -i oracle-instantclient11.2-basic-126.96.36.199.0-1.i386.rpm sudo rpm -i oracle-instantclient11.2-sqlplus-188.8.131.52.0-1.i386.rpm sudo rpm -i oracle-instantclient11.2-devel-184.108.40.206.0-1.i386.rpm
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:
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:
su - opsview tar –zxvf DBD-Oracle-1.58.tar.gz cd DBD-Oracle-1.58perl Makefile.PL make 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:
Setup and Configuration
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.
For more information, refer to Opsview Knowledge Center - Adding Host Templates to Hosts.
Step 2: Add and configure variables required for this host
|ORACREDENTIALS||Used in authenticating with Oracle RDBMS. Override the Username and Password with your credentials.|
For more information, refer to Opsview Knowledge Center - Adding Variables to Hosts.
Step 3: Apply changes and the system will now be monitored
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.