Server generated alerts like “Metrics “Database Time Spent Waiting (%)”,” RESPONSE_TXN” etc” can not be disabled in EM12c.

Hi Everyone.

I this post of mine I am sharing one of the BUGS which I again came across when I faced this issue in my Environment.

I was in process of prioritizing my Monitoring Template by disabling some non-required Alerts for databases in my Organization. I wanted to omit or disable some of the “SYSTEM GENERATED ALERTS”  which was triggered by EM and I was sent alert for same very frequently.

My OMS version was :12.1.0.3.0

Oracle Agent version was: 12.1.0.3.0

I followed simple method, I created a new template and put it under Template Collection for my Administration Group and associate it with Respective group. After this I did the manual Synchronization.

However after doing all this I was still getting email alerts for the metrics which were no longer in my Monitoring template.

The Alert looked like this:
————————–
Host=xxxbidb5.xxx.com
Target type=Database Instance
Target name=BIPRD
Categories=Load
Message=Metrics “Database Time Spent Waiting (%)” is at 76.625 for event class “Concurrency”
Severity=Warning
Event reported time=Mar 8, 2014 4:34:25 PM CST
Target Lifecycle Status=Production
Line of Business=BI
Department=DBA
Operating System=Linux
Platform=x86_64
Associated Incident Id=29735
Associated Incident Status=New
Associated Incident Owner=
Associated Incident Acknowledged By Owner=No
Associated Incident Priority=None
Associated Incident Escalation Level=0
Event Type=Metric Alert
Event name=wait_sess_cls:dbtime_waitclass_pct
Metric Group=Waits by Wait Class
Metric=Database Time Spent Waiting (%)
Metric value=76.6250232644705
Key Value=Concurrency
Key Column 1=Wait Class
Rule Name=RuleSet for all Production Targets,Incident creation Rule for metric alerts.
Rule Owner=DKSHARMA
Update Details:
Metrics “Database Time Spent Waiting (%)” is at 76.625 for event class “Concurrency”
Incident created by rule (Name = RuleSet for all Production Targets, Create incident for critical metric alerts; Owner = DKSHARMA).

So when GUI did not work, I tried to do from the back end by executing below mentioned package:-

exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘System I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Concurrency’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Other’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Configuration’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Commit’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Cluster’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Application’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, NULL, NULL, NULL, NULL, NULL, NULL, ‘BIPRD’,DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Administrative’);

Support asked me to crosscheck the database plugin and registered targets so I did as well.

(/app/oracle/product/agent12c/agent_inst/bin)
oracle@xxxbidb5 $ ./emctl config agent listtargets
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
[xxxbidb5.xxx.com, host]
[xxxbidb5.xxx.com:1830, oracle_emd]
[OraDb11g_home1_1_xxxbidb5, oracle_home]
[agent12c2_2_xxxbidb5, oracle_home]
[BIPRD, oracle_database]

() (/app/oracle/product/agent12c/agent_inst/bin)
oracle@xxxbidb5 $ ./emctl listplugins agent
Oracle Enterprise Manager Cloud Control 12c Release 3
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
—————————————————————
oracle.sysman.db 12.1.0.4.0 /app/oracle/product/agent12c/plugins/oracle.sysman.db.agent.plugin_12.1.0.4.0
oracle.sysman.oh 12.1.0.3.0 /app/oracle/product/agent12c/plugins/oracle.sysman.oh.agent.plugin_12.1.0.3.0

Finally they agreed that it is a  Bug 17414360 – “METRIC THRESHOLDS UPDATED ON THE OMS/REPOSITORY SIDE BUT NOT ON DB SIDE” and was fixed in 12.1.0.5.1.

So I did update my database plugin to 12.1.0.5.5 Bundle Patch but all of no use, still my inbox was getting filled with more and more emails. Did some deep trouble shooting enabled the DEBUG mode on agent side but nothing happened.

Finally I was provided with a very strange solution, which MOS could have provided long before I applied three patches and update by plugins to next higher version. The solution was :

I should set the threshold to a very high value of 99% so that it can be rarely reached and the number of email could be reduced. So I did a very quick thing.

The following may need to be run to fully terminate the alerting for dba_thersholds, especially in the case where the value is exceeding the 99% value for the limit: 
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘System I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Other’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Configuration’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Administrative’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Application’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Cluster’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Concurrency’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Scheduler’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘User I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Commit’);

This would force the database to alert only if the alerts are being triggered for more than 10000 times.

Well not stretching this topic any longer, it was concluded that OEM was not able to disable all “SERVER GENERATED ALERTS”, you have to finally configure them from backend and set the interval to such a high value which is never met.  😦

You can sue this document to get list of SERVER GENERATED ALERTS.

http://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_server_alert.htm#i1007642

CONCLUSION

The metrics for the Database Time Spent Waiting % are Server-generated alerts which means that Enterprise Manager is not responsible for firing the alert. The database server side alert engine fires the alert and send it to Enterprise Manager Agent. 
Disabling collection from Enterprise Manager does not affect Server-Generated alerts. The database server will test thresholds on its side and generate alerts as long as the thresholds are set. Enterprise Manager will faithfully forward along any alerts generated by the server to Enterprise Manager.

Resolution:
PSU patches were applied that include several BUG fixes, including Bug 17414360

[https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=17414360] [https://bug.oraclecorp.com/pls/bug/webbug_edit.edit_info_top?rptno=17414360] . This job resolves issues with jobs that are submitted by the OMS to the target databases that updated server generated metrics.
To keep the alerts from happening, we removed the metrics from the incident rule
As some server generated metrics cannot have thresholds changed in EM, we changed them in the database itself. We set the alert threshold very high for the metrics to keep the metric from triggering regularly.

exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘System I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Other’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Configuration’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Network’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Administrative’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Application’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Cluster’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Concurrency’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Scheduler’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘User I/O’);
exec DBMS_SERVER_ALERT.SET_THRESHOLD(DBMS_SERVER_ALERT.DB_TIME_WAITING, DBMS_SERVER_ALERT.OPERATOR_GE,’10000′, NULL, NULL, 1, 100000, ‘<DB_INSTANCE_NAME>’, DBMS_SERVER_ALERT.OBJECT_TYPE_EVENT_CLASS, ‘Commit’);

Hope this help you to save your time, which you might waste in updating your plugin or applying random patches.

Thanks

Deepak

Advertisements

One thought on “Server generated alerts like “Metrics “Database Time Spent Waiting (%)”,” RESPONSE_TXN” etc” can not be disabled in EM12c.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s