clean up - delete Enterprise manager ( EM ) console Alerts

Administration Oracle Ajouter un commentaire

The GRID console , though a very useful tool, has the very bad habit to maintain the display of irrelevant ALERTS on its main / Home page.
To get rid of these informations is not obvious nor , of course, documented by Oracle.

SYSMAN account includes 3 relevant objects related to Alerts :
MGMT_SECURITY , actually a View, based on the TABLE SYSMAN.MGMT_VIOLATIONS
MGMT_CURRENT_SECURITY , actually a View, based on the TABLE SYSMAN.MGMT_CURRENT_VIOLATIONS

Here is its description :

Column Name	ID	Data Type	Null?

TARGET_GUID	1	RAW (16)	N
METRIC_GUID	2	RAW (16)	N
KEY_VALUE	3	VARCHAR2 (256 Byte)	N
COLLECTION_TIMESTAMP	4	DATE	N
SEVERITY_CODE	5	NUMBER	N
SEVERITY_TYPE	6	NUMBER	N
SEVERITY_DURATION	7	NUMBER	Y
SEVERITY_GUID	8	RAW (16)	Y
ANNOTATED_FLAG	9	NUMBER	Y
NOTIFICATION_STATUS	10	NUMBER	Y
MESSAGE	11	VARCHAR2 (4000 Byte)	Y
MESSAGE_NLSID	12	VARCHAR2 (64 Byte)	Y
MESSAGE_PARAMS	13	VARCHAR2 (4000 Byte)	Y
ACTION_MESSAGE	14	VARCHAR2 (4000 Byte)	Y
ACTION_NLSID	15	VARCHAR2 (64 Byte)	Y
ACTION_MESSAGE_PARAMS	16	VARCHAR2 (4000 Byte)	Y
ADVISORY_ID	17	VARCHAR2 (64 Byte)	Y
LOAD_TIMESTAMP	18	DATE	Y
USER_NAME	19	VARCHAR2 (64 Byte)	Yand

DELETE_CURRENT_SEVERITY a procedure that  belongs to SYSMAN.EM_SEVERITY package.
Here is its description :

PROCEDURE delete_current_severity (
p_target_guid IN RAW,
p_metric_guid IN RAW,
p_key_value   IN VARCHAR2);

Note : A trigger deltes Rows from MGMT_CURRENT_SSEVERITY AFTER DELETE from MGMT_SEVERITY !!?

A full description of the parameters  can be obtained via the Dictionnary :

SQL> select column_name, comments
from  dba_col_comments
where table_name=’MGMT_SEVERITY’

COLUMN_NAME	COMMENTS

ADVISORY_ID	Advisory ID of the severity
LOAD_TIMESTAMP	Date and time when the severity was loaded
USER_NAME	Name of the user to load the severity
TARGET_GUID	 The target guid of the severity
METRIC_GUID	 The metric guid of the severity
KEY_VALUE	 The key value of the severity
COLLECTION_TIMESTAMP	 The timestamp at which the severity occurred
SEVERITY_CODE	The severity codes for error, warnin, critical etc.
    These codes are backwards compatible with EM 9i and EM 10gR1.
      15 - CLEAR
      18 - INFO
      20 - WARNING
      25 - CRITICAL
     115 - AGENT UNREACHABLE CLEART
     125 - AGENT UNREACHABLE START
     215 - BLACKOUT END
     225 - BLACKOUT START
     315 - METRIC ERROR END
     325 - METRIC ERROR START
    Codes 115 and above are applicable only for response/status metric
SEVERITY_TYPE	 The severity type allows an application that is selecting
      from this table to filter the rows returned by the type of
      severity.  Values in this column are:
           0 - METRIC THRESHOLD ALERT
           1 - AVAILABILITY
SEVERITY_DURATION	The delta time, in hours, from when the severity was logged
    until it was cleared.
SEVERITY_GUID	 The unique id of the severity. Defaults to SYS_GUID()
ANNOTATED_FLAG	A flag to indicate whether the severity is annotated or not.
NOTIFICATION_STATUS	The column used by the notification sub system to determine
    notification status of the severity.
MESSAGE	The message of the severity.  The messages usually contain
    details about what triggered this severity.
MESSAGE_NLSID	The NLS ID of the severity message.
MESSAGE_PARAMS	URL encoded parameters separated by "&" to be used to
    format the severity message.
ACTION_MESSAGE	Suggested action message in english for this severity
ACTION_NLSID	The NLS ID of the action message.
ACTION_MESSAGE_PARAMS	URL encoded parameters for translating action message

Pour avoir des infos sur les alertes :

SELECT distinct s.target_guid, s.metric_guid,t.target_name,t.target_type,
    m.metric_name,   s.collection_timestamp, s.key_value
       FROM mgmt_targets t ,mgmt_current_severity s, mgmt_metrics m
            where s.target_guid = t.target_guid
            and m.metric_guid = s.metric_guid
            and t.target_type='oracle_database' -- interested only in database resultes (not hosts, application server,...)
            and s.collection_timestamp < to_timestamp('01/07/2010','DD/MM/YYYY') -- my deadline
            and t.target_name like 'my_database%'

To clean up, use something like (be careful could delete more rows than you expoect (i have to check that later on…)):

delete from mgmt_severity
WHERE (target_guid,metric_guid) IN
(SELECT s.target_guid,s.metric_guid
FROM mgmt_targets t ,mgmt_severity s
where s.target_guid = t.target_guid
and t.target_type=’oracle_database’
and s.collection_timestamp < to_timestamp(’25/06/2010′,’DD/MM/YYYY’)
and t.target_name like ‘pprtr%’)

Faire un commentaire