Database Server Generated Alerts and Enterprise Manager

You installed Oracle Enterprise Manager Cloud Control, added your production databases to the Enterprise Manager system. You set up the metric thresholds, monitoring templates and notification rules. Everything was looking OK until you got a warning email about “Current Open Cursors Count”. It was surprising because you didn’t set any threshold for it.

You checked the “Metric and Collection Settings” page to be sure that you didn’t set up any threshold, and you were right!

You wanted to edit the metric from the event details page, and all you got is “The metric data can not be found.” error. So what is happening?

This is a general question about that unreasonable “Current Open Cursors Count” warning event which is triggered when the open cursors count is greater than 1200 (default threshold value). The point is it is not generated by the metric thresholds of Enterprise Manager. This event is generated on the target database server!

If you’re in similar situation, connect to the target database, and check DBA_THRESHOLDS view. You’ll find the “Current Open Cursors Count” and other possible metrics and thresholds defined at the database server.

[tooltip text=”Tooltip Text”]Quick tip: The events generated on database server, are grouped as “Server_Adaptive_Threshold_Metric”.[/tooltip]

If you want to remove these thresholds, you need to DBMS_SERVER_ALERT.SET_THRESHOLD procedure. To clean up the threshold for “Current Open Cursors Count”, connect to the target database as SYS, and issue the following PL/SQL command:

You can find the instance name from the DBA_THRESHOLDS. As you can see set_threshold procedure required the metric id as the first parameter. The metric id of “Current Open Cursors Count” is 2104, but what if you want to clean threshold of another database generated events such as “Average Users Waiting Counts”, “Logons Per Sec”? How will you find the IDs of these metrics?

To learn the metric IDs, we need to get the DML of the DBA_THRESHOLDS view. You can use Oracle SQL Developer’s find database object feature to get the DML. Then copy the SELECT part of the DML, and add a.metrics_id column to the query. It’s a UNION query, and there are combined two selects. So you need to add “a.metrics_id” to the both select queries. When you run the query, you can see the defined thresholds with their ID.

Please share this post Share on Facebook3Share on Google+0Share on LinkedIn5Share on Reddit0Tweet about this on Twitter

Gokhan Atil is a database administrator who has hands-on experience with both RDBMS and noSQL databases (Oracle, PostgreSQL, Microsoft SQL Server, Sybase IQ, MySQL, Cassandra, MongoDB and ElasticSearch), and strong background on software development. He is certified as Oracle Certified Professional (OCP) and is awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for his continuous contributions to the Oracle users community.

2 Comments

  1. erpcharan

    thanks for sharing the useful information about the oracle concepts.

     

    Nice Blog!

Leave Comment

Your email address will not be published. Required fields are marked *