11g Grid | How to manually clear EM Grid Control alerts

List Alerts and number of times they have been raised?

select substr(message_nlsid,1,50),count(*)
 from mgmt_current_severity
 where message_nlsid is not null
 group by message_nlsid
 having count(*) > 10
 order by 2 desc,1 ;



 Message_nlsid                            Count                
-------------------------------------------------------------       
invalid_objects_count_alertmessage               88
service_perf_stat_alertmessage                   37
adrAlertLog_accessViolationErrStack_alertmessage 31
Response_Status_alertmessage                     29
resource_instance_value_alert_message_push       16
adrAlertLog_genericInternalErrStack_alertmessage 15
alert_streams_process_status                     15
TNSERRORS_tnserr_alertmessage                    14

SQL to list all alerts against a given “message_nlsid”

select A.target_name, A.target_type, B.target_guid, B.message
 from mgmt_targets  A, mgmt_current_severity B
 where message_nlsid='invalid_objects_count_alertmessage'
 AND A.target_guid=B.target_guid
 order by TARGET_NAME, TARGET_TYPE;

SQL if you want to list/delete alerts for a known “TARGET”

SELECT A.target_name
, B.target_guid
 , B.metric_guid
 , B.key_value
 FROM mgmt_targets A
 JOIN mgmt_current_severity B
 ON A.target_guid = B.target_guid
 WHERE upper ( A.target_name ) LIKE '%TESTDB%';
DECLARE
CURSOR c1 IS
SELECT A.target_name
, B.target_guid
, B.metric_guid
, B.key_value
FROM mgmt_targets A
JOIN mgmt_current_severity B
ON A.target_guid = B.target_guid
WHERE upper ( A.target_name ) LIKE '%TESTDB%';

BEGIN
FOR r IN c1
LOOP
dbms_output.put_line ( 'R.TARGET_GUID = ' || r.target_guid || '  , r.metric_guid = ' || r.metric_guid || '  , r.key_value = ' || r.key_value );
sysman.em_severity.delete_current_severity ( r.target_guid
, r.metric_guid
, r.key_value );
DELETE from sysman.mgmt_severity
WHERE target_guid = r.target_guid
AND metric_guid = r.metric_guid
AND key_value = r.key_value;
END
LOOP;
COMMIT;
END;

List all alerts with a message “invalid_objects_count_alertmessage”

SELECT A.target_name,B.target_guid,  B.metric_guid,  B.key_value  FROM mgmt_targets A JOIN mgmt_current_severity B  ON A.target_guid = B.target_guid  WHERE message_nlsid='invalid_objects_count_alertmessage';

Delete all alerts for message “invalid_objects_count_alertmessage”

SQL> select severity_code,message
 from mgmt_current_severity
 where message_nlsid='invalid_objects_count_alertmessage'; 
---------------------
88 rows selected

DECLARE
 CURSOR c1 IS
 SELECT B.target_guid,
 B.metric_guid,
 B.key_value
 FROM mgmt_targets A JOIN mgmt_current_severity B
 ON A.target_guid = B.target_guid
 WHERE message_nlsid='invalid_objects_count_alertmessage';
BEGIN
 FOR r IN c1 LOOP
 dbms_output.put_line( 'R.TARGET_GUID = ' ||r.target_guid || '  , r.metric_guid = ' || r.metric_guid || '  , r.key_value = ' || r.key_value);
 sysman.em_severity.delete_current_severity(r.target_guid,r.metric_guid,r.key_value);
 DELETE from sysman.mgmt_severity
 WHERE
 target_guid = r.target_guid AND
 metric_guid = r.metric_guid AND
 key_value = r.key_value;
 END LOOP;
 COMMIT;
END;

SQL > select severity_code,message
 from mgmt_current_severity
 where message_nlsid='invalid_objects_count_alertmessage'; 
no rows selected

If you find it difficult to delete using "message_nlsid" use message flashed at EM console instead.

List Alerts :
SELECT A.target_name,B.target_guid,message,
 B.metric_guid,
 B.key_value
 FROM mgmt_targets A JOIN mgmt_current_severity B
 ON A.target_guid = B.target_guid
 WHERE message like '%An%access%violation%detected%'; 

Delete Alerts :    
DECLARE
 CURSOR c1 IS
 SELECT B.target_guid,
 B.metric_guid,
 B.key_value
 FROM mgmt_targets A JOIN mgmt_current_severity B
 ON A.target_guid = B.target_guid
 WHERE message like '%An%access%violation%detected%';

BEGIN
 FOR r IN c1 LOOP
 dbms_output.put_line( 'R.TARGET_GUID = ' ||r.target_guid || '  , r.metric_guid = ' || r.metric_guid || '  , r.key_value = ' || r.key_value);
 sysman.em_severity.delete_current_severity(r.target_guid,r.metric_guid,r.key_value);
 DELETE from sysman.mgmt_severity
 WHERE
 target_guid = r.target_guid AND
 metric_guid = r.metric_guid AND
 key_value = r.key_value;
 END LOOP;
 COMMIT;
END;

Delete old alerts date/monthwise

select count ( * )
 , to_char ( trunc ( collection_timestamp
 , 'MONTH' )
 , 'MONTH' )
 from mgmt_current_severity
 group by trunc ( collection_timestamp
 , 'MONTH' )
 order by trunc ( collection_timestamp
 , 'MONTH' );
 COUNT(*) TO_CHAR(TRUNC(COLLECTION_TIMESTAMP,'
---------- ------------------------------------
 153 MARCH
 147 APRIL
 120 MAY

DECLARE
 CURSOR c1 IS
 SELECT B.target_guid
 , B.metric_guid
 , B.key_value
 FROM mgmt_targets A
 JOIN mgmt_current_severity B
 ON A.target_guid = B.target_guid
 WHERE to_char ( trunc ( b.collection_timestamp
 , 'MONTH' )
 , 'MONTH' ) like 'MAR%';
 BEGIN
 FOR r IN c1
 LOOP
 dbms_output.put_line ( 'R.TARGET_GUID = ' || r.target_guid || '  , r.metric_guid = ' || r.metric_guid || '  , r.key_value = ' || r.key_value );
 sysman.em_severity.delete_current_severity ( r.target_guid
 , r.metric_guid
 , r.key_value );
 DELETE from sysman.mgmt_severity
 WHERE target_guid = r.target_guid
 AND metric_guid = r.metric_guid
 AND key_value = r.key_value;
 END
 LOOP;
 COMMIT;
 END;
 PL/SQL procedure successfully completed.

COUNT(*) TO_CHAR(TRUNC(COLLECTION_TIMESTAMP,’
———- ————————————
147 APRIL
120 MAY