Deleting alerts in Enterprise Manager

SYSMAN can be compared to
a mothman in the movie called “The Mothman Prophecies” because like the mothman,
SYSMAN also appears to be a dark and mysterious figure lurking in the
Context. SYSMAN, as an introduction, is used in Oracle as a 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 a doubt, one of Oracle’s most frustrating experiences
because of the poor, unintuitive and disjointed OEM interface. From a website
development perspective, for example, in how many different ways can you vary the
location of a “Home” link? Somewhere between looking in the upper 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 no direct link
link), you must be rolling your eyes and wondering what Oracle Corporation was
think about when the OEM was in development.

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

May 21stwe
see that session 147 was blocking another session. It was over three months
there is (as of the publication date of this article). In the Alerts section of a
production database, I’ve seen messages persist for over a year. Even after
invalid objects have long since been compiled/validated, the OEM continues to
display irrelevant information about the state of a database. posts
on tablespaces exceeding an alert threshold or on the space remaining in
the db_recovery_file_dest location are other examples of obsolete OEM information
insists on appearing in the Alerts section. The worst in these messages
is that the OEM does not provide a way to reject them.

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

The driving table

The table of interest in the
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)

Apart from RAW
data types, working with data from this table should be quite simple,
that is, once you know what to look for. The primary key of the table is a compound
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 are looking at the page source. If you try
this approach, mainly as an exercise to see what the underlying HTML code looks like
like, all you’re going to see (at the bottom of the text editor page) are some
lines of code, one of which spans over 30,000 characters.

The two best witnesses of
the table are KEY_VALUE and COLLECTION_TIMESTAMP. KEY_VALUE values ​​have a
model for them. If the message concerns a tablespace that has crossed a space
threshold, then the KEY_VALUE is simply the name of the tablespace. If the
the message says SYS has connected, the KEY_VALUE will be a mix of “SYS”
more “_.” The recovery area message is identified by a value
of RECOVERY AREA. Blocking message starts with SID and invalid objects
in a schema starts with the name of the schema.

For the most part, the date
and the time displayed under Alert Triggered will appear as the value for
COLLECTION_TIMESTAMP. Between this time and the “key values” in KEY_VALUE
column, you must be armed enough to invoke a named procedure
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 the 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;

At first glance, this
approach seems like a safe way to clear outdated messages, but it
is a better way, and this way is to remove directly from the MGMT_SEVERITY
table and let the SEVERITY_DELETE trigger do all the work for you.

The trigger removes from
three tables: MGMT_CURRENT_SEVERITY (and only uses the SEVERITY_GUID value),
MGMT_SEVERITY_ANNOTATION and MGMT_NOTIFICATION_LOG. The easiest way to remove
from the control table consists of using a graphical tool such as SQL Manager or Toad. Remove
line, commit the transaction, refresh OEM and the message will disappear.

The use of table space
the message may not disappear if you change the storage, and the number of invalid
objects per schema is not accurate as it may not list all schemas with
invalid objects.

In conclusion

There doesn’t seem to be any
information on MetaLink about removing obsolete messages from the OEM, and
Enterprise Manager documentation seems to be limited to removing alerts
alert log type. Alert log errors/messages are fairly easy to clear or
purge, and alerts of this nature may also appear as a message in Alerts
section on the home page. Clear or Purge Alert Log Error and Message
should go away.

If in doubt, do not delete
of the array, but as you’ve seen in this article, Oracle makes provisions for
deletions and takes action to also delete data from other tables. After
given how easy it is to do a bit of housekeeping on a table, SYSMAN shouldn’t
sound as mysterious as it could at first. It would be better if
The OEM took care of this cleaning themselves, so unless you have two options
when it comes to seeing outdated messages: live with them indefinitely
little time, or take matters into your own hands and clean up
yourself.

»


See all articles by columnist
Steve Callan