D.9. Oracle 8i, 9i, 10g, and 11g

The probes in this section may be applied to instances of the Oracle database matching the versions supported. Oracle probes require the configuration of the database and associations made by running the following command: 
$ORACLE_HOME/rdbms/admin/catalog.sql
In addition, for these probes to function properly, the Oracle user configured in the probe must have minimum privileges of CONNECT and SELECT_CATALOG_ROLE.
Some Oracle probes are specifically aimed at tuning devices for long-term performance gains, rather than avoiding outages. Therefore, Red Hat recommends scheduling them to occur less frequently, between every hour and every two days. This provides a better statistical representation, de-emphasizing anomalies that can occur at shorter time intervals. This applies to following probes: Buffer Cache, Data Dictionary Cache, Disk Sort Ratio, Library Cache, and Redo Log.
For CRITICAL and WARNING thresholds based upon time to work as intended, their values cannot exceed the amount of time allotted to the timeout period. Otherwise, an UNKNOWN status is returned in all cases of extended latency, thereby nullifying the thresholds. For this reason, Red Hat strongly recommends ensuring that timeout periods exceed all timed thresholds. In this section, this refers specifically to the probe TNS Ping.
Finally, customers using these Oracle probes against a database using Oracle's Multi-Threaded Server (MTS) must contact Red Hat support to have entries added to the RHN Server's /etc/hosts file to ensure that the DNS name is resolved correctly.

D.9.1. Oracle::Active Sessions

The Oracle::Active Sessions probe monitors an Oracle instance and collects the following metrics:
  • Active Sessions — The number of active sessions based on the value of V$PARAMETER.PROCESSES.
  • Available Sessions — The percentage of active sessions that are available based on the value of V$PARAMETER.PROCESSES.

Table D.48. Oracle::Active Sessions settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30
Critical Maximum Active Sessions
Warning Maximum Active Sessions
Critical Maximum Available Sessions Used
Warning Maximum Available Sessions Used

D.9.2. Oracle::Availability

The Oracle::Availability probe determines the availability of the database from the RHN Satellite.

Table D.49. Oracle::Availability settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30

D.9.3. Oracle::Blocking Sessions

The Oracle::Blocking Sessions probe monitors an Oracle instance and collects the following metric:
  • Blocking Sessions — The number of sessions preventing other sessions from committing changes to the Oracle database, as determined by the required Time Blocking value you provide. Only those sessions that have been blocking for this duration, which is measured in seconds, are counted as blocking sessions.

Table D.50. Oracle::Blocking Sessions settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Time Blocking (seconds)* 20
Timeout* 30
Critical Maximum Blocking Sessions
Warning Maximum Blocking Sessions

D.9.4. Oracle::Buffer Cache

The Oracle::Buffer Cache probe computes the Buffer Cache Hit Ratio so as to optimize the system global area (SGA) Database Buffer Cache size. It collects the following metrics:
  • Db Block Gets — The number of blocks accessed via single block gets (not through the consistent get mechanism).
  • Consistent Gets — The number of accesses made to the block buffer to retrieve data in a consistent mode.
  • Physical Reads — The cumulative number of blocks read from disk.
  • Buffer Cache Hit Ratio — The rate at which the database goes to the buffer instead of the hard disk to retrieve data. A low ratio suggests more RAM should be added to the system.

Table D.51. Oracle::Buffer Cache settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port 1521
Timeout* 30
Warning Minimum Buffer Cache Hit Ratio
Critical Minimum Buffer Cache Hit Ratio

D.9.5. Oracle::Client Connectivity

The Oracle::Client Connectivity probe determines if the database is up and capable of receiving connections from the monitored system. This probe opens an rhnmd connection to the system and issues a sqlplus connect command on the monitored system.
The Expected DB name parameter is the expected value of V$DATABASE.NAME. This value is case-insensitive. A CRITICAL status is returned if this value is not found.
Requirements — The Red Hat Network Monitoring Daemon (rhnmd) must be running on the monitored system to execute this probe. For this probe to run, the nocpulse user must be granted read access to your log files.

Table D.52. Oracle::Client Connectivity settings

Field Value
Oracle Hostname or IP address*
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
ORACLE_HOME* /opt/oracle
Expected DB Name*
Timeout* 30

D.9.6. Oracle::Data Dictionary Cache

The Oracle::Data Dictionary Cache probe computes the Data Dictionary Cache Hit Ratio so as to optimize the SHARED_POOL_SIZE in init.ora. It collects the following metrics:
  • Data Dictionary Hit Ratio — The ratio of cache hits to cache lookup attempts in the data dictionary cache. In other words, the rate at which the database goes to the dictionary instead of the hard disk to retrieve data. A low ratio suggests more RAM should be added to the system.
  • Gets — The number of blocks accessed via single block gets (not through the consistent get mechanism).
  • Cache Misses — The number of accesses made to the block buffer to retrieve data in a consistent mode.

Table D.53. Oracle::Data Dictionary Cache settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30
Warning Minimum Data Dictionary Hit Ratio
Critical Minimum Data Dictionary Hit Ratio

D.9.7. Oracle::Disk Sort Ratio

The Oracle::Disk Sort Ratio probe monitors an Oracle database instance and collects the following metric:
  • Disk Sort Ratio — The rate of Oracle sorts that were too large to be completed in memory and were instead sorted using a temporary segment.

Table D.54. Oracle::Disk Sort Ratio settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30
Critical Maximum Disk Sort Ratio
Warning Maximum Disk Sort Ratio

D.9.8. Oracle::Idle Sessions

The Oracle::Idle Sessions probe monitors an Oracle instance and collects the following metric:
  • Idle Sessions — The number of Oracle sessions that are idle, as determined by the required Time Idle value you provide. Only those sessions that have been idle for this duration, which is measured in seconds, are counted as idle sessions.

Table D.55. Oracle::Idle Sessions settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Time Idle (seconds)* 20
Timeout* 30
Critical Maximum Idle Sessions
Warning Maximum Idle Sessions

D.9.9. Oracle::Index Extents

The Oracle::Index Extents probe monitors an Oracle instance and collects the following metric:
  • Allocated Extents — The number of allocated extents for any index.
  • Available Extents — The percentage of available extents for any index.
The required Index Name field contains a default value of % that matches any index name.

Table D.56. Oracle::Index Extents settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Index Owner* %
Index Name* %
Timeout* 30
Critical Maximum of Allocated Extents
Warning Maximum of Allocated Extents
Critical Maximum of Available Extents
Warning Maximum of Available Extents

D.9.10. Oracle::Library Cache

The Oracle::Library Cache probe computes the Library Cache Miss Ratio so as to optimize the SHARED_POOL_SIZE in init.ora. It collects the following metrics:
  • Library Cache Miss Ratio — The rate at which a library cache pin miss occurs. This happens when a session executes a statement that it has already parsed but finds that the statement is no longer in the shared pool.
  • Executions — The number of times a pin was requested for objects of this namespace.
  • Cache Misses — The number of pins of objects with previous pins since the object handle was created that must now retrieve the object from disk.

Table D.57. Oracle::Library Cache settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30
Critical Maximum Library Cache Miss Ratio
Warning Maximum Library Cache Miss Ratio

D.9.11. Oracle::Locks

The Oracle::Locks probe monitors an Oracle database instance and collects the following metric:
  • Active Locks — The current number of active locks as determined by the value in the v$locks table. Database administrators should be aware of high numbers of locks present in a database instance.
Locks are used so that multiple users or processes updating the same data in the database do not conflict. This probe is useful for alerting database administrators when a high number of locks are present in a given instance.

Table D.58. Oracle::Locks settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30
Critical Maximum Active Locks
Warning Maximum Active Locks

D.9.12. Oracle::Redo Log

The Oracle::Redo Log probe monitors an Oracle database instance and collects the following metrics:
  • Redo Log Space Request Rate — The average number of redo log space requests per minute since the server has been started.
  • Redo Buffer Allocation Retry Rate — The average number of buffer allocation retries per minute since the server was started.
The metrics returned and the thresholds they are measured against are numbers representing the rate of change in events per minute. The rate of change for these metrics should be monitored because fast growth can indicate problems requiring investigation.

Table D.59. Oracle::Redo Log settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Timeout* 30
Critical Maximum Redo Log Space Request Rate
Warning Maximum Redo Log Space Request Rate
Critical Maximum Redo Buffer Allocation Retry Rate
Warning Maximum Redo Buffer Allocation Retry Rate

D.9.13. Oracle::Table Extents

The Oracle::Table Extents probe monitors an Oracle database instance and collects the following metrics:
  • Allocated Extents-Any Table — The total number of extents for any table.
  • Available Extents-Any Table — The percentage of available extents for any table.
In Oracle, table extents allow a table to grow. When a table is full, it is extended by an amount of space configured when the table is created. Extents are configured on a per-table basis, with an extent size and a maximum number of extents.
For example, a table that starts with 10 MB of space and that is configured with an extent size of 1 MB and max extents of 10 can grow to a maximum of 20 MB (by being extended by 1 MB ten times). This probe can be configured to alert by (1) the number of allocated extents (e.g. "go critical when the table has been extended 5 or more times"), or (2) the table is extended past a certain percentage of its max extents (e.g. "go critical when the table has exhausted 80% or more of its max extents").
The required Table Owner and Table Name fields contain a default value of % that matches any table owner or name.

Table D.60. Oracle::Table Extents settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Table Owner* %
Table Name* %
Timeout* 30
Critical Maximum Allocated Extents
Warning Maximum Allocated Extents
Critical Maximum Available Extents
Warning Maximum Available Extents

D.9.14. Oracle::Tablespace Usage

The Oracle::Tablespace Usage probe monitors an Oracle database instance and collects the following metric:
  • Available Space Used — The percentage of available space in each tablespace that has been used.
Tablespace is the shared pool of space in which a set of tables live. This probe alerts the user when the total amount of available space falls below the threshold. Tablespace is measured in bytes, so extents do not factor into it directly (though each extension removes available space from the shared pool).
The required Tablespace Name field is case insensitive and contains a default value of % that matches any table name.

Table D.61. Oracle::Tablespace Usage settings

Field Value
Oracle SID*
Oracle Username*
Oracle Password*
Oracle Port* 1521
Tablespace Name* %
Timeout* 30
Critical Maximum Available Space Used
Warning Maximum Available Space Used

D.9.15. Oracle::TNS Ping

The Oracle::TNS Ping probe determines if an Oracle listener is alive and collects the following metric:
  • Remote Service Latency — The time it takes in seconds for the Oracle server to answer a connection request.

Table D.62. Oracle::TNS Ping settings

Field Value
TNS Listener Port* 1521
Timeout* 15
Critical Maximum Remote Service Latency
Warning Maximum Remote Service Latency