14.2. Event Notification

Whether your shop is using Oracle9i or Oracle 10g, it's easy to set up automatic notifications for various types of error or warning conditions. Oracle9i uses the OEM Event Manager, and Oracle 10g uses the web-based EM Database Control to set up and proactively monitor database health. In the following sections we'll take a look at both of these.

14.2.1. Oracle9i OEM Event Manager

OEM can automatically alert the DBA, through an e-mail message or page, to error conditions or conditions that may signal an impending error. Using OEM's Event Manager, accessible as one of the nodes in the OEM Navigator pane, the DBA can monitor a variety of error conditions, such as an abnormal termination of the Oracle instance or a tablespace running low on space. Even events that would not technically be considered an error condition can be monitored. For example, you could tell Event Manager to notify you when users are performing too many table scans within a certain period of time, as shown here.

Creating a new event is straightforward. From OEM's toolbar at the top, select Event Create Event. On the Tests tab in the Create Event window, you can select from a long list of available tests. In this example, the DBA will be notified when any tablespace's used space exceeds a specified threshold percentage or the number of full table scans performed each second exceeds a specified threshold amount. The Parameters tab in the Create Event window is used to specify these thresholds. For the Tablespace Full test, an alert will be sent to the DBA via pager or e-mail whenever any tablespace is 80 percent full or higher, as shown below.

For the Table Scans Per Second test, the DBA will be notified with a warning message if the number of full table scans exceeds 10 per second at least three times, or with a critical error if the number of full table scans exceeds 25 per second at least three times, as shown below.

These tests can be performed on the database automatically on a regular schedule, specified on the Schedule tab of the Create Event window:

In this example, when the event is saved, the tests in the event will run immediately and then every 15 minutes thereafter.

The DBA can also specify a script to run automatically when event conditions are detected. This is helpful when the DBA is on vacation or not able to receive e-mail or pager messages for some other reason. You can select a script through the Fixit Jobs tab of the Create Event window.

In many cases, a fixit job can repair the problem without any intervention by the DBA at all. The fixit job can, for example, temporarily allocate more disk space on a spare disk volume for the tablespace that is about to run out of space. A fixit job can be a series of predefined actions to be performed when the event occurs, such as shutting down and restarting the database, or a fixit job may call a customized SQL script written by the DBA or any combination of predefined actions and customized scripts.

14.2.2. Oracle 10g Advisory Framework

Using Oracle 10g's EM Database Control, you can configure diagnostics for a number of different potential trouble spots using Advisor Central.

For example, SQL Tuning Advisor can automatically identify and tune SQL statements that have a high CPU and I/O impact on the database; Segment Advisor can identify object within a schema or tablespace that have a high percentage of unused space.

For each tablespace in a database or across the entire database, you can specify two thresholds at which a notification message is sent if the space threshold is exceeded.

For the USERS tablespace, a warning message is generated if the tablespace exceeds 60 percent full, and a critical message is generated if the tablespace exceeds 85 percent full. For any other tablespace that does not have specific thresholds set, the warning threshold is 85 percent and the critical threshold is 97 percent.