Red Hat Network 4.1.0: Reference Guide
Prev | Appendix C. Probes | Next

# 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:

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

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 |

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.

Field | Value
--- | ---
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:

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

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 |

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:

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

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 |

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.

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

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:

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

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 |

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:

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

Field | Value
--- | ---
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:

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

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 |

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:

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

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 |

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:

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

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 |

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:

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

Field | Value
--- | ---
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:

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

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 |

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:

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

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 |

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:

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

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 |

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:

- Remote Service Latency — The time it takes in seconds for the Oracle server to answer a connection request.

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

Table C-62. Oracle::TNS Ping settings

Prev | Home | Next
Network Services | Up | RHN Satellite Server