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:
DBMS_SERVER_ALERT.SET_THRESHOLD( metrics_id => 2104,
warning_operator => NULL,warning_value => NULL,
critical_operator => NULL,critical_value => NULL,
observation_period => 1,consecutive_occurrences => 1,
instance_name => 'YOUR_INSTANCE_NAME',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM,
object_name => NULL );
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.