Oracle DBA – A lifelong learning experience

Removing alerts from Enterprise manager Grid Control

Posted by John Hallas on February 18, 2009

We have an Enterprise manager Grid control morning check report that indicates issues across all our environments.

The list of checks include :

Usable flash recovery area less than 20%

Filesystems over 90% used

Databases not backed up within 1 day and not blacked out and not a physical standby

Dataguard status (targets not blacked out)

Alert log errors

Various specific job checks

The alert log query we use is a view based on this query

select distinct t.target_name,




from mgmt_targets t,

mgmt_metrics m,

mgmt_string_metric_history smh

where t.target_guid = smh.target_guid

and m.metric_guid = smh.metric_guid

and m.statefull = 0

and m.metric_name like ‘alertLog%’

However that still requires us to logon to each database and click on the alert_log link under diagnostic options on the home purge. We then need to purge the alerts.

Looking for a better way to do this I traced an EM session on the OMS repository database whilst purging these alerts. This provided me with the following statement which can be used to delete all outstanding alerts for every system.

delete from mgmt_string_metric_history where metric_guid in (

select metric_guid from mgmt_metrics where statefull=0 and metric_name like ‘alertLog%’);

This is set up as a script on the OMS database server. There is no commit within the script. This is intentional so that the count of deleted records should match the number of alerts outstanding on the morning check report. It is also intentional that we have not created this as a EM job. The reason is that it will then perform an auto-commit which does not allow any regression if the record count is different from the number of alerts.

I hope the idea of a morning check report proves useful and the sql statements listed can be utilised.


3 Responses to “Removing alerts from Enterprise manager Grid Control”

  1. Manish Patil said

    Hi John,

    Thanks for the helpful article i.e Removing alerts from Enterprise manager Grid Control.

    You mentioned about an Enterprise manager Grid control morning check report. Can you please let me know in details? Is this custom report?

    I am looking for the same report.

    Thanks in advance!!

    Manish Patil

  2. […] John Hallas on June 21, 2010 I mentioned that we have a morning check report that we run from OEM in this post . I was asked about it a couple of days ago so I thought I would post the contents as it may give […]

  3. fouedgray said

    Thank you, very useful indeed.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: