Using Database Alerts with Metrics

Oracle Database 10g helps to reduce the time spent by the DBA on routine database-monitoring tasks by automatically sending messages with information about performance or resources-allocation issues and suggesting remedial actions.

A server-generated alert from the Oracle Database 10g server is a perfect example of a notification message about an impending problem. This message contains details about the error/alert condition and may contain recommendations for fixing the problem. Server-based alerts can also be based on threshold levels of different metrics, as explained earlier in this chapter.

MMON schedules the database-monitoring actions as explained earlier in this chapter. If the database detects any unusual conditions, it invokes the MMON to take immediate action and sends an alert. Along with the database alerts, many database components perform self-tuning on their own by using the history of metric values in the AWR. OEM used to collect many of these alerts in earlier editions. OEM alerts differ from server-generated alerts in that metrics computation and threshold validations are done by the Oracle Database 10g and not by the OEM agent.

There are two kinds of server generated alerts:

• Threshold alerts. These are database alerts based on threshold levels and can be triggered at warning and critical levels. These threshold levels can be set internally or by the customer, or can be customer-altered from preset values. These threshold alerts are also known as stateful alerts, which are automatically cleared when the alert condition is fixed. Stateful alerts are stored in DBA_OUTSTANDING_ALERTS and moved to DBA_ALERT_HISTORY when cleared. The DBA_ALERT_HISTORY view has the same structure as DBA_OUTSTANDING_ALERTS, except for the last column (RESOLUTION).

  There are 161 metrics with definable threshold values, such as physical reads per second, user commits per second, and so on in Oracle Database 10g Release 1. For Oracle Database 10g Release 2, the number of metrics with definable threshold values has increased to around 170.

  
  

• Non-threshold alerts. Also called stateless alerts, these are written directly to the DBA_ALERT_HISTORY. An example of a common non-threshold alert is "resumable session has been suspended." OEM stores these stateless alerts in its own repository; hence, clearing of these alerts makes sense only in the OEM environment.

Whenever an alert is generated, it is sent to a predefined persistence queue called alert_que (owned by SYS). OEM reads this queue and provides notifications on outstanding alerts along with suggestions for any corrective actions. These alerts are displayed on the OEM console. As discussed in Chapter 3, "Customizing Installation Options," in the section titled "Setup, Response, and Clearing of Alerts," OEM can send these messages to pager numbers or to email addresses. If the alert cannot be written to the alert_que, Oracle will record a message about the alert to the database alert log.

Sending Alerts to Third-Party Tools

You can get alerts pushed to a third-party tool or your own paging software by subscribing to alert_que with the DBMS_AQADM.ADD_SUBSCRIBER procedure. For this, associate a database user with the subscribing agent using the ENABLE_DB_ACCESS procedure. An asynchronous notification can also be received by enqueing alerts using alert_que. To get enqueue notifications through email or HTTP posts, you should configure the database server through dbms_aqelm with mail server or proxy server information. Subscribers have to denqueue the message metadata to get the alert content.