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 selectedIf 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
Leave a Reply
You must be logged in to post a comment.