Oracle Server-Generated Alerts

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Introduction to Metrics

MMON collects database metrics continuously and automatically saves them in the SGA for one hour.

The OEM Database Control�s All Metrics page offers an excellent way to view the various metrics.

Oracle Database 10g Metric Groups are (can be obtained from V$METRICGROUP):

o Event Class Metrics
o Event Metrics
o File Metrics
o Service Metrics
V$SERVICEMETRIC, V$SERVICEMETRIC_HISTORY

o Session Metrics
o System Metrics
V$SYSMETRIC, V$SYSMETRIC_HISTORY

o Tablespace Metrics

Viewing Saved Metrics

MMON will automatically flush the metric data from the SGA to the DBA_HISTORY_* views on disk. Examples of the history views are DBA_HIST_SUMMARY_HISTORY,

DBA_HIST SYSMETRIC_HISTORY, and DBA_HIST_METRICNAME. Each of these views contains snapshots of the corresponding V$ view.

Database Alerts

There are three situations when a database can send an alert:

� A monitored metric crosses a critical threshold value

� A monitored metric crosses a warning threshold value

� A service or target suddenly becomes unavailable

Default Server-Generated Alerts

Your database comes with a set of the following default alerts already configured. In addition, you can choose to have other alerts.

� Any snapshot too old errors

� Tablespace space usage (warning alert at 85 percent usage; critical alert at 97 percent usage)

� Resumable session suspended

� Recovery session running out of free space

Server-Generated Alert Mechanism

MMON process checks all the configured metrics and if any metric crosses a preset threshold, an alert will be generated.

Using the Database Control to Manage Server Alerts

You can use Database Control to:

� set a warning and critical threshold

� A response action: a SQL script or a OS command line to execute

� set Notification Rules: when notify a DBA

Using the DBMS_SERVER_ALERT Package to Manage Alerts

SET_THRESHOLD - This procedure will set warning and critical thresholds for given metrics.

DBMS_SERVER_ALERT.SET_THRESHOLD(
DBMS_SERVER_ALERT.CPU_TIME_PER_CALL,
DBMS_SERVER_ALERT.OPERATOR_GE, '8000',
DBMS_SERVER_ALERT.OPERATOR_GE, '10000', 1, 2,
'inst1',
DBMS_SERVER_ALERT.OBJECT_TYPE_SERVICE,
'dev.oracle.com')

In this example, a warning alert is issued when CPU time exceeds 8000 microseconds for each user call and a critical alert is issued when CPU time exceeds 10,000 microseconds for each user call. The arguments include:

o CPU_TIME_PER_CALL specifies the metric identifier. For a list of    support metrics, see PL/SQL Packages and Types Reference.

o The observation period is set to 1 minute. This period specifies the number    of minutes that the condition must deviate from the threshold value before    the alert is issued.

o The number of consecutive occurrences is set to 2. This number specifies    how many times the metric value must violate the threshold values before    the alert is generated.

o The name of the instance is set to inst1.

o The constant DBMS_ALERT.OBJECT_TYPE_SERVICE specifies the    object type on which the threshold is set. In this example, the service name    is dev.oracle.com.

Note: If you don�t want Oracle to send any metric based alerts, simply set the warning value and the critical value to NULL.

GET_THRESHOLD - Use this procedure to retrieve threshold values.

DBMS_SERVER_ALERT.GET_THRESHOLD(
metrics_id IN NUMBER,
warning_operator OUT NUMBER,
warning_value OUT VARCHAR2,
critical_operator OUT NUMBER,
critical_value OUT VARCHAR2,
observation_period OUT NUMBER,
consecutive_occurrences OUT NUMBER,
instance_name IN VARCHAR2,
object_type IN NUMBER,
object_name IN VARCHAR2)

See the section "Proactive Tablespace Management" for more examples of using DBMS_SERVER_ALERT package.

Using the Alert Queue

You can use the DBMS_AQ and DBMS_AQADM packages for directly accessing and reading alert messages in the alert queue.

Steps you should follow are:

1. Create an agent and subscribe the agent to the ALERT_QUE using the CREATE_AQ_AGENT and ADD_SUBSCRIBER procedures of the DBMS_AQADM package.

2. Associate a database user with the subscribing agent and assign the enqueue privilege to the user using the ENABLE_DB_ACCESS and GRANT_QUEUE_PRIVILEGE procedures of the DBMS_AQADM package.

3. Optionally, you can register with the DBMS_AQ.REGISTER procedure to receive an asynchronous notification when an alert is enqueued to ALERT_QUE.

4. To read an alert message, you can use the DBMS_AQ.DEQUEUE procedure or OCIAQDeq call. After the message has been dequeued, use the DBMS_SERVER_ALERT.EXPAND_MESSAGE procedure to expand the text of the message.

Data Dictionary Views of Metrics and Alerts

DBA_THRESHOLDS - lists the threshold settings defined for the instance.

DBA_OUTSTANDING_ALERTS - describes the outstanding alerts in the database.

DBA_ALERT_HISTORY - lists a history of alerts that have been cleared.

V$ALERT_TYPES - provides information such as group and type for each alert.

V$METRICNAME - contains the names, identifiers, and other information about the system metrics.

V$METRIC and V$METRIC_HISTORY - views contain system-level metric values in memory.

V$ALERT_TYPES

STATE - Holds two possible values: stateful or stateless. The database considers all the non-threshold alerts as stateless alerts. A stateful alert first appears in theDBA_OUTSTANDING_ALERTS view and goes to the DBA_ALERT_HISTORY view when it is cleared. A stateless alert goes straight to DBA_ALERT_HISTORY.

SCOPE - Classifies alerts into database wide and instance wide. The only database-level alert is the one based on the Tablespace Space Usage metric. All the other alerts are at the instance level.

GROUP_NAME - Oracle aggregates the various database alerts into some common groups: Space, Performance, Configuration-related database alerts.