Oracle Database Monitoring Infrastructure

PDF 

ORAMON

Author: Bill Ennis

ORAMON is a set of scripts that we have written to monitor the health of an Oracle Database instance. The scripts include a notification mechanism that allows you to stay informed whenever there is a problem. They are also completely configurable in that they allow thresholds for various alarms to be defined at several levels when installed in an enterprise.

The scripts monitor the following aspects of Oracle:

  • Is the database up?
  • Are the vital Oracle operating system processes up? This includes the listener, pmon, smon, etc.
  • What are the cache hit ratios for the data buffer, data dictionary, and library caches?
  • What is the status of disk usage within my tablespaces?
  • Will all of my next extent allocations succeed?
  • Am I about to exceed the maximum number of sessions allowed in my instance?
  • Are there any blocking locks on the system?
  • Are any users exceeding their allotted quota of disk space?

Prerequisites

ORAMON currently runs in a Unix environment (successfully run on HP/UX and Solaris with no porting needed). The scripts are written in Perl (any 5.00x variant of Perl will do). In addition the Perl DBI and DBD/Oracle modules are used for database access.

A file is required in the ORAMON installation directory that stores the names of the instances that ORAMON is to monitor. This file should be named serverlist.<machine name> where machine name is the name of the machine where ORAMON will be running on.

ORAMON needs an account with the dba role to connect to the database. The login credentials for this user are stored in the oracle users home directory in a hidden file named .opass (be sure to set permissions on this file so that only the oracle user account can read it).

Some environment settings are system dependent such as the directory that ORAMON is installed in, the email accounts that notifications needs to be sent to, etc. These settings can be assigned in the CONSTANTS.pl and THRESHOLDS.DEFAULT files.

ORAMON thresholds can be configured for each script to the adminstrator’s preference. For example, cache hit rates can be set to 70% if so desired. If the hit rate is lower than 70% an alarm will fire.

Notification

The big advantage of using ORAMON is that it can monitor things and notify you only when there are problems. This frees you from constantly having to check for problem situations with your database. ORAMON uses email for notification and can even send messages to your pager or cell phone if they are Internet enabled (i.e. have a mailbox that can forward messages to them). Two levels of alarming exist, the first of which will generate an email if an alarm condition is raised and the second will issue a page (generally used for serious situations where the problem needs to be dealt with immediately).

Reports

ORAMON generates reports for each day and each script that is run. Check under the installation directory for ORAMON and you will find a directory named ORADATA. Within ORADATA there is a directory for each component of ORAMON. Each component generates a daily log composed of all information collected for that day.

Configuration

ORAMON can be configured in several ways. You have the ability to define thresholds for when alarms will fire. These thresholds are set in configuration files in the ORAMON directory. Thresholds can be set on three levels – globally, at a machine wide basis, and at an instance basis. Globally means that if you install ORAMON in a shared directory (i.e. NFS mount) that all machines and their respective instances would be able to share the threshold settings. Global settings are set in the THRESHOLDS.DEFALT file. At the machine level means that you can assign thresholds to all Oracle instances running on a machine in one file (named THRESHOLDS.<machine name>). And at the instance level means that thresholds that have been set apply only to one Oracle instance (in a file named THRESHOLDS.<oracle instance name>).

A combination of the above can be used in that you can assign a subset of the thresholds at the instance level if needed, only those set in the threshold file for the Oracle istance will override the global or machine level settings.

If you are interested in having ORAMON installed at your site please contact us!