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.
Click for larger image
On May 21st, 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
