Managing Alert Thresholds

Managing Alert Thresholds

Getting the Current Threshold Setting
 List of supported metrics can be found in the documentation "PL/SQL Packages and Types
Reference": 10g, 11g or link or from the query below:
SELECT METRIC_ID, METRIC_NAME, METRIC_UNIT,
GROUP_ID, GROUP_NAME
FROM V$METRICNAME
ORDER BY METRIC_NAME
-- current threshold settings
select * from DBA_THRESHOLDS;
set serveroutput on
DECLARE
V_WOPERATOR BINARY_INTEGER;
V_WVALUE VARCHAR2(50);
Page 95 Oracle DBA Code Examples
V_COPERATOR BINARY_INTEGER;
V_CVALUE VARCHAR2(50);
V_OBS_PERIOD BINARY_INTEGER;
V_CON_PERIOD BINARY_INTEGER;
FUNCTION GET_OPERATOR_NAME( P_OPER IN BINARY_INTEGER) RETURN VARCHAR2
IS
BEGIN
IF P_OPER =0 THEN
RETURN 'GT';
ELSIF P_OPER =1 THEN
RETURN 'EQ';
ELSIF P_OPER =2 THEN
RETURN 'LT';
ELSIF P_OPER =3 THEN
RETURN 'LE';
ELSIF P_OPER =4 THEN
RETURN 'GE';
ELSIF P_OPER =5 THEN
RETURN 'OPERATOR_CONTAINS';
ELSIF P_OPER =6 THEN
RETURN 'NE';
ELSIF P_OPER =7 THEN
RETURN 'OPERATOR_DO_NOT_CHECK';
END IF;
END GET_OPERATOR_NAME;
BEGIN
DBMS_SERVER_ALERT.GET_THRESHOLD(
metrics_id =>DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
warning_operator =>V_WOPERATOR ,
warning_value =>V_WVALUE,
critical_operator =>V_COPERATOR,
critical_value =>V_CVALUE,
observation_period =>V_OBS_PERIOD,
consecutive_occurrences =>V_CON_PERIOD,
instance_name =>NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name =>NULL);
DBMS_OUTPUT.PUT_LINE('Warning Op.: '|| GET_OPERATOR_NAME(V_WOPERATOR));
DBMS_OUTPUT.PUT_LINE('Warning Val: '||V_WVALUE);
DBMS_OUTPUT.PUT_LINE('Critical Op.: '|| GET_OPERATOR_NAME(V_COPERATOR));
DBMS_OUTPUT.PUT_LINE('Critical Val: '||V_CVALUE);
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE='-13799' THEN
DBMS_OUTPUT.PUT_LINE('No threshold was found with the specified threshold
key.(ORA-13799)');
ELSE
RAISE;
END IF;
END;
/
Setting Tablespace Alert Thresholds
 warning_operator takes one of the following (not all applicable for all metrics):
o OPERATOR_EQ GE GT LE LT NE
o OPERATOR_CONTAINS

o OPERATOR_DO_NOT_CHECK (disables the alert for the specified metric)
-- set the free-space-remaining thresholds in the USERS tablespace to 10 MB
(warning)
-- and 2 MB (critical), and disable the percent-full thresholds.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE, -- Tablespace FREE
space in KB
warning_operator => DBMS_SERVER_ALERT.OPERATOR_LT, -- GT is not applicable
here
warning_value => '10240',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_LT,
critical_value => '2048',
observation_period => 1, -- computation period (1-60 min)
consecutive_occurrences => 1, -- violation times before alert
instance_name => NULL, -- NULL= 'database_wide'. Passed value is not checked
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS'); -- if NULL -> All Tablespaces
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- tablespace USAGE by
%
warning_operator => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
warning_value => '0',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_DO_NOT_CHECK,
critical_value => '0',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
END;
/
SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Restoring a Tablespace to Database Default Thresholds
 You can restore the metric threshold values to revert to the database defaults by setting
them to NULL in the DBMS_SERVER_ALERT.SET_THRESHOLD.
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- tablespace USAGE by
%
warning_operator => NULL, -- do not use ''
warning_value => NULL,
critical_operator => NULL,
critical_value => NULL,
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => 'USERS');
END;
/

SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Modifying Database Default Thresholds
 set the object_name to NULL
BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, -- redo with
TABLESPACE_BYT_FREE
warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
warning_value => '80',
critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT,
critical_value => '92',
observation_period => 1,
consecutive_occurrences => 1,
instance_name => NULL,
object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
object_name => NULL);
END;
/
SELECT *
FROM dba_thresholds
where object_name = 'USERS' and object_type='TABLESPACE';
Viewing Alerts
-- outstanding alerts (to be cleared)
SELECT
SEQUENCE_ID,OWNER,
OBJECT_NAME,OBJECT_TYPE,SUBOBJECT_NAME,
REASON_ID, REASON,TIME_SUGGESTED,
SUGGESTED_ACTION,ADVISOR_NAME,METRIC_VALUE,
MESSAGE_TYPE,MESSAGE_GROUP,MESSAGE_LEVEL,
HOSTING_CLIENT_ID,MODULE_ID,PROCESS_ID,
HOST_ID,HOST_NW_ADDR,INSTANCE_NAME,
INSTANCE_NUMBER,USER_ID,EXECUTION_CONTEXT_ID,CREATION_TIME
FROM DBA_OUTSTANDING_ALERTS
-- history of alerts that have been cleared
SELECT
SEQUENCE_ID,OWNER,
OBJECT_NAME,SUBOBJECT_NAME,OBJECT_TYPE,
REASON_ID, REASON,TIME_SUGGESTED,
SUGGESTED_ACTION,ADVISOR_NAME,METRIC_VALUE,
MESSAGE_TYPE,MESSAGE_GROUP,MESSAGE_LEVEL,
HOSTING_CLIENT_ID,MODULE_ID,PROCESS_ID,
HOST_ID,HOST_NW_ADDR,INSTANCE_NAME,INSTANCE_NUMBER,
USER_ID,EXECUTION_CONTEXT_ID,CREATION_TIME
FROM DBA_ALERT_HISTORY
ORDER BY SEQUENCE_ID
-- list of all metrics
SELECT METRIC_ID, METRIC_NAME, METRIC_UNIT,GROUP_ID, GROUP_NAME
FROM V$METRICNAME

ORDER BY METRIC_NAME
-- system-level metric values in memory
SELECT BEGIN_TIME,END_TIME,INTSIZE_CSEC,
GROUP_ID,ENTITY_ID,ENTITY_SEQUENCE,
METRIC_ID,METRIC_NAME,VALUE,METRIC_UNIT
FROM V$METRIC –- also V$METRIC_HISTORY
ORDER BY BEGIN_TIME, VALUE DESC
-- alert types
select
INST_ID,REASON_ID,OBJECT_TYPE,TYPE,GROUP_NAME,SCOPE,INTERNAL_METRIC_CATEGORY,
INTERNAL_METRIC_NAME
from GV$ALERT_TYPES
order by OBJECT_TYPE,TYPE