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:
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.
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.
The Oracle::Availability probe determines the availability of the database from the RHN Satellite Server.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
|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
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.
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.
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.