C.9. Oracle 8i and 9i

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.

C.9.1. Oracle::Active Sessions

The Oracle::Active Sessions probe monitors an Oracle instance and collects the following metrics:

FieldValue
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 

Table C-48. Oracle::Active Sessions settings

C.9.2. Oracle::Availability

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

FieldValue
Oracle SID* 
Oracle Username* 
Oracle Password* 
Oracle Port*1521
Timeout*30

Table C-49. Oracle::Availability settings

C.9.3. Oracle::Blocking Sessions

The Oracle::Blocking Sessions probe monitors an Oracle instance and collects the following metric:

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

Table C-50. Oracle::Blocking Sessions settings

C.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:

FieldValue
Oracle SID* 
Oracle Username* 
Oracle Password* 
Oracle Port1521
Timeout*30
Warning Minimum Buffer Cache Hit Ratio 
Critical Minimum Buffer Cache Hit Ratio 

Table C-51. Oracle::Buffer Cache settings

C.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.

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

Table C-52. Oracle::Client Connectivity settings

C.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:

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

Table C-53. Oracle::Data Dictionary Cache settings

C.9.7. Oracle::Disk Sort Ratio

The Oracle::Disk Sort Ratio probe monitors an Oracle database instance and collects the following metric:

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

Table C-54. Oracle::Disk Sort Ratio settings

C.9.8. Oracle::Idle Sessions

The Oracle::Idle Sessions probe monitors an Oracle instance and collects the following metric:

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

Table C-55. Oracle::Idle Sessions settings

C.9.9. Oracle::Index Extents

The Oracle::Index Extents probe monitors an Oracle instance and collects the following metric:

The required Index Name field contains a default value of % that matches any index name.

FieldValue
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 

Table C-56. Oracle::Index Extents settings

C.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:

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

Table C-57. Oracle::Library Cache settings

C.9.11. Oracle::Locks

The Oracle::Locks probe monitors an Oracle database instance and collects the following metric:

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.

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

Table C-58. Oracle::Locks settings

C.9.12. Oracle::Redo Log

The Oracle::Redo Log probe monitors an Oracle database instance and collects the following metrics:

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.

FieldValue
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 

Table C-59. Oracle::Redo Log settings

C.9.13. Oracle::Table Extents

The Oracle::Table Extents probe monitors an Oracle database instance and collects the following metrics:

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.

FieldValue
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 

Table C-60. Oracle::Table Extents settings

C.9.14. Oracle::Tablespace Usage

The Oracle::Tablespace Usage probe monitors an Oracle database instance and collects the following metric:

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.

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

Table C-61. Oracle::Tablespace Usage settings

C.9.15. Oracle::TNS Ping

The Oracle::TNS Ping probe determines if an Oracle listener is alive and collects the following metric:

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

Table C-62. Oracle::TNS Ping settings