Today I came across interesting issue, where OEM was sending mail notification for one of the metrics though I had disabled it from GUI. This is known issue in many of the OEM versions. I found it interesting as none of MOS notes given clear instructions on how to make use of “DBMS_SERVER_ALERT.SET_THRESHOLD”.

so in this article I will focus mainly on how to make use of “DBMS_SERVER_ALERT.SET_THRESHOLD” in such situations.

I am using OEM version 12.1.0.5 & target database in on 11.2.0.4. My mailbox was continously bombarded with warning & clear notifications for metrics “Average Active Sessions”. Using OMS GUI I have already removed thresholds for this metrics. This needs to be done under Target home “Monitoring” => “Metric and Collection Settings” as given below.

I found matching note “EM 13c, EM 12c: How to Prevent Alerts from the Server Adaptive Threshold Metric (Server_Adaptive_Threshold_Metric) Starting with Enterprise Manager 12.1.0.4 Cloud Control (Doc ID 1929003.1)“

This note gives basic steps to fix such issue. This steps needs to be done on target side:

1. For all thresholds (adaptive and static), query:

select metrics_name, warning_value,critical_value, instance_name, object_type,object_name from dba_thresholds;

2. Make use of “DBMS_SERVER_ALERT.SET_THRESHOLD” to make threshold null for a particular metric.

procedure set_threshold(
metrics_id IN BINARY_INTEGER,
warning_operator IN BINARY_INTEGER,
warning_value IN VARCHAR2,
critical_operator IN BINARY_INTEGER,
critical_value IN VARCHAR2,
observation_period IN BINARY_INTEGER,
consecutive_occurrences IN BINARY_INTEGER,
instance_name IN VARCHAR2,
object_type IN BINARY_INTEGER,
object_name IN VARCHAR2);

Oracle had given following example for the same:

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.PHYSICAL_READS_SEC,
warning_operator => NULL,
warning_value => NULL,
critical_operator => NULL,
critical_value => NULL,
observation_period => NULL,
consecutive_occurrences => NULL,
instance_name => 'orcl',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM ,
object_name => ''
);
END;
/

So now a task was to feed this package with right values as I didn’t find metric_id for “Average Active Sessions”. After I explored a lot about it & gone through package body definition of “DBMS_SERVER_ALERT” to find some values & I found one:

RSN_SYS_ACTVS CONSTANT REASON_ID_T:=156; — average active sessions

Okay so metric id I can user here is DBMS_SERVER_ALERT.RSN_SYS_ACTVS.

I created code as given below based on example given by Oracle & tried running it:

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.RSN_SYS_ACTVS,
warning_operator => NULL,
warning_value => NULL,
critical_operator => NULL,
critical_value => NULL,
observation_period => NULL,
consecutive_occurrences => NULL,
instance_name => 'PRDDB',
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_SYSTEM,
object_name => NULL
);
END;
/

This code failed with below error:

Error at line 1
ORA-13908: Invalid combination of metrics id and object type parameters.
ORA-06512: at "SYS.DBMS_SERVER_ALERT", line 7
ORA-06512: at line 2

Somewhere it didnt take values I specified.

After searching alot I found below view, which had these valus in interger number instead of text.

SELECT object_type OBJ_TYPE, metrics_id, instance_name, flags, warning_operator, warning_value, critical_operator, critical_value CRIT_VAL, observation_period OBS_PER, consecutive_occurrences, object_id,object_type
FROM TABLE(dbms_server_alert.view_thresholds);

So here after crosschecking exact values with dba_thresholds, I found metric_id for “Average Active Sessions”/RSN_SYS_ACTVS is 2147 & value of “OBJECT_TYPE_SYSTEM is 1.

My new code for set_threshold was as given below:

BEGIN
dbms_server_alert.set_threshold(2147, NULL, NULL, NULL, NULL, NULL,NULL,'PRDDB',1,NULL);
END;
/

This worked without any issues. Dont forget to issue commit once procedure is run.

This step resolved my issues. I was able to get rid of unwanted entries from dba_thresholds which were causing this issue.

Hope u will find this post very useful.

Cheers

Regards,

Adityanath