Clearing Alerts in Enterprise Manager

August 22, 2007

SYSMAN can be compared to a mothman in the movie titled “The Mothman Prophecies” because like the mothman, SYSMAN also seems like it is a dark, mysterious figure lurking in the background. SYSMAN, by way of introduction, is used in Oracle as the schema used for Enterprise Manager or Grid Control or Database Control, the exact name depending on the context of the application. Much of the data or content you see in Database Control comes from SYSMAN tables.

Using Oracle Enterprise Manager can be, without doubt, one of the most frustrating experiences in Oracle because of OEM’s poor, non-intuitive, disjointed interface. From a Web development standpoint, as an example, how many different ways can you vary the location of a “Home” link? Somewhere between looking in the top left corner, the top right corner, and near the bottom center of a page (and that’s just when a Home link is presented because in many pages there is not a direct link), you have to roll your eyes and wonder what Oracle Corporation was thinking when OEM was being developed.

Unfortunately, the interface isn’t the worst problem. My number one pet peeve about OEM is its inability to clean up after itself, so to speak. That’s right; I’m talking about the Alerts section of the home page. The figure below is part of what’s contained in the Alerts section of my home computer.

On May 21^st, we see that session 147 was blocking another session. That was over three months ago (as of the date of publication of this article). In the Alerts section of a production database, I’ve seen messages linger for well over a year. Even after invalid objects have long since been compiled/made valid, OEM continues to display irrelevant information about the state affairs of a database. Messages about tablespaces crossing an alert threshold or how much space is remaining in the db_recovery_file_dest location are other examples of stale information OEM insists on surfacing in the Alerts section. The worst part about these messages is that OEM does not provide a means of dismissing them.

There are several ways in which stale messages can be removed from the Alerts section, but it is unlikely you would ever know how to do this via any published documentation.

The Driving Table

The table of interest in the SYSMAN schema is named MGMT_SEVERITY.

USER is "SYSTEM"
SQL> conn sysman/oracle
Connected.
USER is "SYSMAN"
SQL> desc mgmt_severity
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 TARGET_GUID                               NOT NULL RAW(16)
 METRIC_GUID                               NOT NULL RAW(16)
 KEY_VALUE                                 NOT NULL VARCHAR2(256)
 COLLECTION_TIMESTAMP                      NOT NULL DATE
 LOAD_TIMESTAMP                                     DATE
 SEVERITY_CODE                             NOT NULL NUMBER
 SEVERITY_TYPE                                      NUMBER
 SEVERITY_DURATION                                  NUMBER
 SEVERITY_GUID                                      RAW(16)
 ANNOTATED_FLAG                                     NUMBER
 NOTIFICATION_STATUS                                NUMBER
 MESSAGE                                            VARCHAR2(4000)
 MESSAGE_NLSID                                      VARCHAR2(64)
 MESSAGE_PARAMS                                     VARCHAR2(4000)
 ACTION_MESSAGE                                     VARCHAR2(4000)
 ACTION_NLSID                                       VARCHAR2(64)
 ACTION_MESSAGE_PARAMS                              VARCHAR2(4000)
 ADVISORY_ID                                        VARCHAR2(64)
 USER_NAME                                          VARCHAR2(64)

Aside from the RAW datatypes, working with data in this table should be fairly straightforward, that is, once you know what to look for. The table’s primary key is a composite key, and uses TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP, and SEVERITY_CODE.

You won’t find the RAW values anywhere on the page, even if you look at the page’s source. If you try that approach, mainly as an exercise to see what the underlying HTML code looks like, all you’re going to see (at the bottom of the text editor page) are a few lines of code, with one of them stretching out well over 30,000 characters.

The two best telltales in the table are KEY_VALUE and COLLECTION_TIMESTAMP. The KEY_VALUE values have a pattern to them. If the message is about a tablespace having crossed a space threshold, then the KEY_VALUE is simply the name of the tablespace. If the message is about SYS having logged on, the KEY_VALUE will be a mixture of “SYS” plus “_<FROM WHERE>.” The recovery area message is identified by a value of RECOVERY AREA. The blocking message starts with SID, and the invalid objects in a schema begins with the schema name.

For the most part, the date and time shown under Alert Triggered will appear as the value for COLLECTION_TIMESTAMP. Between that time and the “key values” in the KEY_VALUE column, you should be sufficiently armed to invoke a procedure named DELETE_CURRENT_SEVERITY in the EM_SEVERITY package.

PROCEDURE DELETE_CURRENT_SEVERITY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TARGET_GUID                  RAW                     IN
 P_METRIC_GUID                  RAW                     IN
 P_KEY_VALUE                    VARCHAR2                IN

This procedure deletes data from a table named MGMT_CURRENT_SEVERITY.

  BEGIN
    DELETE FROM MGMT_CURRENT_SEVERITY
     WHERE target_guid = p_target_guid
       AND metric_guid = p_metric_guid
       AND key_value = p_key_value;
  END delete_current_severity;

On the face of it, this approach seems like a safe way to go about clearing stale messages, but there is a better way, and that way is to delete directly from the MGMT_SEVERITY table and let the SEVERITY_DELETE trigger do all the work for you.

The trigger deletes from three tables: MGMT_CURRENT_SEVERITY (and uses only the SEVERITY_GUID value), MGMT_SEVERITY_ANNOTATION, and MGMT_NOTIFICATION_LOG. The easiest way to delete from the driving table is to use a GUI tool such as SQL Manager or Toad. Delete the row, commit the transaction, refresh OEM, and the message will be gone.

The tablespace space usage message may not go away if you edit the storage, and the number of invalid objects by schema isn’t accurate in that it may not list all schemas with invalid objects.

In Closing

There doesn’t seem to be any information on MetaLink about clearing stale messages from OEM, and the Enterprise Manager documentation appears to be limited to only clearing alert log type of alerts. Alert log errors/messages are fairly easy to clear or purge, and alerts of that nature may also appear as a message in the Alerts section on the home page. Clear or purge the alert log error and the message should go away.

If in doubt, don’t delete from the table, but as you saw in this article, Oracle does make provisions for deletions and takes steps to delete data from other tables as well. After seeing how easy it is to perform some housekeeping on one table, SYSMAN shouldn’t seem as mysterious as it may have been in the beginning. It would be better if OEM took care of this cleanup itself, so barring that, you have two options when it comes to seeing stale messages: live with them for an indeterminate amount of time, or take matters into your own hands and do the cleanup yourself.

» See All Articles by Columnist Steve Callan