Rem drv:
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/severity/severity_triggers.sql /st_emcore_10.2.0.4.2db11.2/1 2009/01/23 21:13:33 smalathe Exp $
Rem
Rem severity_triggers.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem severity_triggers.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem smalathe 01/15/09 - Bug 6979013: Do not queue notification if
Rem notification_status=2
Rem neearora 11/20/07 - bug 6640129
Rem neearora 06/20/07 - bug 6142074
Rem pmodi 06/11/07 - Backport pmodi_bug-6038522 from main
Rem neearora 05/22/07 - added l_an_ack_sev in call to
Rem QUEUE_METRIC_NOTIFICATIONS
Rem neearora 05/06/07 - ER 5958873
Rem pmodi 04/05/07 - Bug 5894391 - Fix DST issue mgmt_last_violation
Rem tbl
Rem pmodi 04/17/07 - Backport pmodi_bug-5894391 from main
Rem neearora 03/27/07 - enqueue the severity record in AQ
Rem neearora 07/21/06 - project 21470. Invoke target relocation
Rem callbacks if severity for targetType : cluster
Rem MetricName : crs_event MetricStatus : status is
Rem received
Rem rpinnama 12/09/05 - Backport rpinnama_bug-4635074 from main
Rem pmodi 12/01/05 - Backport pmodi_bug-4740910 from main
Rem pmodi 11/21/05 - Bug:4740910 Use bulk delete in trigger
Rem rpinnama 09/29/05 - Fix 4635074 : Mark host/agent as UP on metric
Rem error end
Rem pmodi 08/30/05 - Bug:4578672 Chg decode stmt for perf
Rem gan 08/31/05 - process_unreachable_start
Rem neearora 08/28/05 - Bug 4545110.Moved availability marker forward
Rem when processing a metric error end severity.
Rem gan 08/25/05 - blackout->unreachable
Rem rpinnama 07/25/05 - Fix 4415154 : Do not accept Unreachable Clear if
Rem target is Unrch avail status is not cleared
Rem jsadras 07/04/05 - add cfg_ columns to current violation
Rem rkpandey 07/12/05 - Bug 4294977:Dont reject severities from non-master agent
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem jaysmith 06/17/05 - update ca violation copy
Rem pmodi 06/20/05 - Bug:4389284- Change for NLS Message
Rem rpinnama 05/16/05 - Fix bug 4345566 : Improve the perf of before insert trigger
Rem rpinnama 04/20/05 - Fix bug 3981814 : Calculate notification status
Rem for severities that come after blackout ends
Rem jsadras 04/13/05 - Bug:4298778:auto error end
Rem jsadras 04/01/05 - rowid to urowid for mgmt_availability rowid
Rem jsadras 03/11/05 - Bug:4215429, Violation type
Rem snakai 02/12/05 - check for duplicate beacon sevs
Rem gsbhatia 02/07/05 - updating repmgr header
Rem streddy 01/14/05 - Synchronously re-evaluate cluster availability
Rem in SA mode
Rem rpinnama 01/13/05 - Fix bug 3901347
Rem rpinnama 01/27/05 - Handle the case of agent blackout
Rem snakai 01/06/05 - use gensvc_avail instead of bcn_avail pkg
Rem ktlaw 01/11/05 - add repmgr header
Rem jsadras 12/30/04 - perf
Rem jsadras 12/05/04 - purge_apis
Rem rpinnama 11/23/04 - Use load_timestamp for sorting
Rem jaysmith 11/11/04 - pass violation from severity trigger to CA code
Rem jaysmith 10/28/04 - log underlying error from
Rem trigger_ca_for_violation
Rem asawant 08/31/04 - Modifying exception thrown for severities out of
Rem order.
Rem rpinnama 10/06/04 - Delete violation context when the violation is
Rem deleted
Rem jriel 09/30/04 - change notification constants
Rem rpinnama 09/20/04 - Support informational violations
Rem dcawley 09/16/04 - Cutover queue metric severities to new type
Rem rpinnama 09/12/04 - Add update trigger on severity
Rem rpinnama 09/07/04 - Populate mgmt_violations on insert into
Rem mmgt_severity
Rem gan 07/19/04 - 3492516
Rem rpinnama 04/12/04 - Lock availability to serialize updates to
Rem streddy 10/15/03 - Check for target existence before processing a
Rem rpinnama 10/15/03 - Dont accept unreachable clear during blackouts
Rem snakai 08/07/03 - use unknown in beacon avail
Rem rpinnama 07/25/03 - Queue severity to notification queue in standalone mode
Rem streddy 07/10/03 - Delete annotations as part of severity delete
Rem streddy 07/10/03 - Rewrote get_last_severity cursor for better performance
Rem streddy 04/29/03 - Handle multi-agent targets
Rem mbhoopat 05/13/03 - support server alerts
Rem streddy 04/03/03 - Composite availability changes
Rem rpinnama 03/03/03 - Use consolidated availability cursor
Rem rpinnama 05/05/03 - Fix bug 2938027 : Remove time sequence check for blackouts
Rem rpinnama 04/07/03 - Exclude out of time checks for blackout triggers
Rem rpinnama 02/03/03 - Exclude Unreachable start severity from out-of-seq check
Rem rpinnama 01/23/03 - Handle severities received during UNKWN after BLKOUT
Rem rpinnama 01/08/03 - Display different message on severities received during blackout
Rem rpinnama 01/06/03 - Fix blackout bugs
Rem rpinnama 12/20/02 - Do proper checking for targets being deleted
Rem rpinnama 12/05/02 - Discard severities received during blackouts
Rem rpinnama 11/01/02 - raise duplicate error on receive duplicate severity clear
Rem rpinnama 11/06/02 - Close the last severity cursor
Rem rpinnama 10/09/02 - Check for null severity code
Rem rpinnama 10/08/02 - Fix bug 2606389: exact fetch results in multiple rows
Rem rpinnama 09/20/02 - Fix collection timestamp during day-light savings transitions
Rem rpinnama 09/19/02 - Recover from error state when UP/DOWN sevs come
Rem rpinnama 08/19/02 - Fix 2520691: Exclude targets with beacon availability
Rem from out-of-time sequence check
Rem rpinnama 08/02/02 - Catch out-of-time-sequence severities
Rem snakai 07/25/02 - beacon funcs/procs moved to pkg
Rem rpinnama 07/22/02 - Use newly created errors and exceptions
Rem snakai 07/23/02 - allow clearing of beacon avail sevs
Rem rpinnama 07/19/02 - Add emd_url while logging errors
Rem rpinnama 07/17/02 - Detect duplicate target downs
Rem snakai 07/12/02 - beacon avail fix
Rem skini 07/02/02 - Account for delete-pending targets
Rem tjaiswal 07/09/02 - Obsolete mgmt annotations table
Rem rpinnama 06/25/02 - Use predefined error messages
Rem rpinnama 06/21/02 - Add more error checks.
Rem snakai 06/20/02 - add beacon availability
Rem rpinnama 06/18/02 - Support severity comments.
Rem rpinnama 06/05/02 - Modify the severity trigger.
Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Created
Rem
--
-- Instead of triggers on mgmt_severity
--
-- Instead of insert trigger
CREATE OR REPLACE TRIGGER em_insert_violations
INSTEAD OF INSERT ON MGMT_SEVERITY FOR EACH ROW
DECLARE
BEGIN
-- Insert into mgmt_violations table
EM_SEVERITY.insert_violation(
p_target_guid => :new.target_guid,
p_policy_guid => :new.metric_guid,
p_key_value => :new.key_value,
p_collection_timestamp => :new.collection_timestamp,
p_violation_guid => :new.severity_guid,
p_violation_level => :new.severity_code,
p_violation_type => :new.severity_type,
p_violation_duration => :new.severity_duration,
p_annotated_flag => :new.annotated_flag,
p_notification_status => :new.notification_status,
p_message => :new.message,
p_message_nlsid => :new.message_nlsid,
p_message_params => :new.message_params,
p_action_message => :new.action_message,
p_action_nlsid => :new.action_nlsid,
p_action_message_params => :new.action_message_params,
p_advisory_id => :new.advisory_id,
p_user_name => :new.user_name);
END;
/
show errors;
-- Instead of insert trigger
CREATE OR REPLACE TRIGGER em_update_violations
INSTEAD OF UPDATE ON MGMT_SEVERITY FOR EACH ROW
DECLARE
BEGIN
-- Update violation
UPDATE mgmt_violations
SET target_guid = NVL(:new.target_guid, target_guid),
policy_guid = NVL(:new.metric_guid, policy_guid),
key_value = NVL(:new.key_value, key_value),
collection_timestamp = NVL(:new.collection_timestamp, collection_timestamp),
violation_guid = NVL(:new.severity_guid, violation_guid),
violation_level = NVL(:new.severity_code, violation_level),
violation_type = NVL(:new.severity_type, violation_type),
violation_duration = :new.severity_duration,
annotated_flag = NVL(:new.annotated_flag, annotated_flag),
notification_status = NVL(:new.notification_status, notification_status),
message = :new.message,
message_nlsid = :new.message_nlsid,
message_params = :new.message_params,
action_message = :new.action_message,
action_message_nlsid = :new.action_nlsid,
action_message_params = :new.action_message_params,
advisory_id = :new.advisory_id,
load_timestamp = NVL(:new.load_timestamp, load_timestamp),
user_name = NVL(:new.user_name, user_name)
WHERE violation_guid = :old.severity_guid;
END;
/
show errors
-- Instead of insert trigger
CREATE OR REPLACE TRIGGER em_delete_violations
INSTEAD OF DELETE ON MGMT_SEVERITY FOR EACH ROW
DECLARE
BEGIN
-- Update violation
EM_SEVERITY.delete_violation(
p_target_guid => :old.target_guid,
p_policy_guid => :old.metric_guid,
p_key_value => :old.key_value,
p_collection_timestamp => :old.collection_timestamp);
END;
/
show errors
CREATE OR REPLACE TRIGGER em_violation_checks
BEFORE INSERT ON mgmt_violations FOR EACH ROW
DECLARE
l_emd_url MGMT_TARGETS.emd_url%TYPE;
l_policy_type mgmt_policies.policy_type%TYPE := NULL;
l_last_violation_level mgmt_violations.violation_level%TYPE := NULL;
l_last_collection_timestamp mgmt_violations.collection_timestamp%TYPE := NULL;
l_last_violation_guid mgmt_violations.violation_guid%TYPE := NULL;
l_cycle_guid mgmt_violations.cycle_guid%TYPE := NULL;
l_prev_violation_level mgmt_violations.violation_level%TYPE := NULL;
l_prev_collection_timestamp mgmt_violations.collection_timestamp%TYPE := NULL;
l_prev_violation_guid mgmt_violations.violation_guid%TYPE := NULL;
l_next_violation_level mgmt_violations.violation_level%TYPE := NULL;
l_next_collection_timestamp mgmt_violations.collection_timestamp%TYPE := NULL;
l_next_violation_guid mgmt_violations.violation_guid%TYPE := NULL;
l_cnt_avail_guids NUMBER := 0;
l_update_avail BOOLEAN := FALSE;
l_update_agent_avail BOOLEAN := FALSE;
l_agent_unreachable BOOLEAN := FALSE;
l_upd_target_bcn_avail BOOLEAN := FALSE;
l_upd_test_bcn_avail BOOLEAN := FALSE;
l_unrch_start_timestamp MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
l_curr_avail_rowid UROWID;
l_curr_avail_status MGMT_AVAILABILITY.current_status%TYPE;
l_curr_avail_start_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
l_curr_avail_end_ts MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
l_prev_avail_rowid UROWID;
l_prev_avail_status MGMT_AVAILABILITY.current_status%TYPE;
l_prev_avail_start_ts MGMT_AVAILABILITY.start_collection_timestamp%TYPE;
l_prev_avail_end_ts MGMT_AVAILABILITY.end_collection_timestamp%TYPE;
l_new_avail_status MGMT_AVAILABILITY.current_status%TYPE;
l_marker_timestamp MGMT_AVAILABILITY_MARKER.marker_timestamp%TYPE;
l_marker_status MGMT_AVAILABILITY_MARKER.marker_avail_status%TYPE;
l_error INTEGER := 0;
l_tgt_tzrgn MGMT_TARGETS.timezone_region%TYPE;
l_cnt NUMBER;
l_new_key NUMBER;
l_sev_cnt NUMBER := 0;
l_is_host NUMBER := 0;
l_target_type MGMT_TARGETS.target_type%type ;
l_mon_mode MGMT_TARGETS.monitoring_mode%type ;
l_curr_violation MGMT_VIOLATIONS%ROWTYPE;
l_states_match NUMBER := 0;
-- max sev ts
l_max_sev_ts DATE;
l_new_max_sev_ts DATE;
--
-- Availability cursor
l_curr_avail_cursor EM_SEVERITY.AVAIL_CURSOR;
-- Flag to control INSERT or UPDATE in mgmt_last_violation
-- I >> INSERT, U >> UPDATE
l_insert_or_update CHAR(1);
l_last_violation_rowid UROWID;
l_save_violation NUMBER := 0;
l_violation_summary_rec MGMT_VIOLATION_SUMMARY_REC;
l_tz_region VARCHAR2(64);
--
-- Get the last avail severity record
--
CURSOR get_last_severity_info
(v_target_guid RAW, v_policy_guid RAW, v_key_value VARCHAR2, v_max_ts DATE) IS
SELECT violation_level, collection_timestamp, violation_guid,cycle_guid
FROM mgmt_violations
WHERE target_guid = v_target_guid
AND policy_guid = v_policy_guid
AND key_value = v_key_value
AND collection_timestamp = v_max_ts
ORDER BY load_timestamp DESC , DECODE(violation_level,
MGMT_GLOBAL.G_SEVERITY_CLEAR, 1,
MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, 2,
MGMT_GLOBAL.G_SEVERITY_WARNING, 3,
MGMT_GLOBAL.G_SEVERITY_CRITICAL, 4,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, 6,
9);
--
-- Get the previous non-comment severity record for a given target/policy/key/collTS
-- This is very similar to the above cursor, but the above cursor is optimized to
-- give the information for the last severity
--
CURSOR get_prev_violation_info(v_target_guid RAW,
v_policy_guid RAW,
v_key_value VARCHAR2,
v_collection_timestamp DATE) IS
SELECT violation_level, collection_timestamp, violation_guid
FROM mgmt_violations
WHERE target_guid = v_target_guid
AND policy_guid = v_policy_guid
AND key_value = v_key_value
AND collection_timestamp <= v_collection_timestamp
AND violation_level != MGMT_GLOBAL.G_SEVERITY_COMMENT
ORDER BY collection_timestamp DESC, load_timestamp DESC ,
DECODE(violation_level,
MGMT_GLOBAL.G_SEVERITY_CLEAR, 1,
MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, 2,
MGMT_GLOBAL.G_SEVERITY_WARNING, 3,
MGMT_GLOBAL.G_SEVERITY_CRITICAL, 4,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, 6,
9);
CURSOR get_next_violation_info(v_target_guid RAW,
v_policy_guid RAW,
v_key_value VARCHAR2,
v_collection_timestamp DATE) IS
SELECT violation_level, collection_timestamp, violation_guid
FROM mgmt_violations
WHERE target_guid = v_target_guid
AND policy_guid = v_policy_guid
AND key_value = v_key_value
AND collection_timestamp >= v_collection_timestamp
AND violation_level != MGMT_GLOBAL.G_SEVERITY_COMMENT
ORDER BY collection_timestamp , load_timestamp ,
DECODE(violation_level,
MGMT_GLOBAL.G_SEVERITY_CLEAR, 1,
MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL, 2,
MGMT_GLOBAL.G_SEVERITY_WARNING, 3,
MGMT_GLOBAL.G_SEVERITY_CRITICAL, 4,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, 5,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, 6,
9) DESC;
BEGIN
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG('em_violation_checks:Enter '||
' target_guid='||:new.target_guid||
' policy_guid='||:new.policy_guid||
' key='||:new.key_value||
' viol level='||:new.violation_level||
' timestamp='||to_char(:new.collection_timestamp,'DD-MON-YY HH24:MI'),
EM_SEVERITY.MODULE) ;
END IF ;
-- Check if the target is being deleted. If so, refuse
-- to allow it to be added. This is achieved by throwing
-- an application error, which the loader will catch and
-- log
SELECT COUNT(*) INTO l_cnt
FROM MGMT_TARGETS_DELETE
WHERE target_guid=:new.target_guid
AND delete_complete_time IS NULL;
IF l_cnt > 0 THEN
raise_application_error(MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR,
MGMT_GLOBAL.TARGET_DELETION_IN_PROG_ERR_M ||
'(target guid = ' || :new.target_guid || ')');
END IF;
--
-- Check if the target exists.
-- Get the timezone region at the same time.
--
BEGIN
SELECT timezone_region , target_type, emd_url,monitoring_mode
INTO l_tgt_tzrgn ,l_target_type,l_emd_url,l_mon_mode
from MGMT_TARGETS
WHERE target_guid = :new.target_guid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error(MGMT_GLOBAL.UNKNOWN_TARGET_ERR,
MGMT_GLOBAL.UNKNOWN_TARGET_ERR_M ||
'(target guid = ' || :new.target_guid || ')');
WHEN OTHERS THEN
raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
'(target guid = ' || :new.target_guid || ')' ||
'(SQLERROR = ' || SQLERRM || ')' );
END;
--
-- For severity comments, dont do anything
--
IF ((NOT (:new.violation_level IS NULL)) AND
(:new.violation_level != MGMT_GLOBAL.G_SEVERITY_COMMENT) ) THEN
-- Check for duplication (if the severity already exists)
-- Check for PK
SELECT COUNT(*) INTO l_sev_cnt
FROM mgmt_violations
WHERE target_guid = :new.target_guid
AND policy_guid = :new.policy_guid
AND key_value = :new.key_value
AND collection_timestamp = :new.collection_timestamp
AND violation_level = :new.violation_level;
IF (l_sev_cnt > 0) THEN
IF EMDW_LOG.p_is_debug_set THEN
EMDW_LOG.debug('Found exact duplicate record in severity table' ||
' code ' || :new.violation_level || ' timestamp '
|| :new.collection_timestamp, EM_SEVERITY.MODULE);
END IF;
-- Raise exception if this severity already exists.
raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M ||
' (target_guid = ' || :new.target_guid || ')' ||
'(policy_guid = ' || :new.policy_guid || ')' ||
'(key_value = ' || :new.key_value || ')' ||
'(collection_timestamp = ' || :new.collection_timestamp || ')' ||
'(violation_level =' ||
EM_SEVERITY.get_severity_string(:new.violation_level) || ')' );
END IF;
--
-- Check if the target/test is set up to use beacon availability.
--
BEGIN
l_upd_target_bcn_avail := MGMT_GENSVC_AVAIL.IS_TARGET_AVAIL_VIOL( :new.target_guid,
:new.policy_guid );
IF ( l_upd_target_bcn_avail ) THEN
l_upd_test_bcn_avail := FALSE;
ELSE
l_upd_test_bcn_avail := MGMT_GENSVC_AVAIL.IS_TEST_AVAIL_VIOL( :new.target_guid,
:new.policy_guid,
:new.key_value );
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
--
-- Filter out invalid violations as they would create invalid notifications
-- and break the beacon avail algo.
--
IF ( l_upd_target_bcn_avail ) AND
( :new.violation_level IN ( MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START,
MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR,
MGMT_GLOBAL.G_SEVERITY_ERROR_START,
MGMT_GLOBAL.G_SEVERITY_ERROR_END ) ) THEN
raise_application_error(MGMT_GLOBAL.INCONSISTANT_SEVERITY_ERR,
MGMT_GLOBAL.INCONSISTANT_SEVERITY_ERR_M ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
') (curr_avail_status=' ||
EM_SEVERITY.get_avail_string(l_curr_avail_status) ||
')' );
END IF;
--Bug6038522 - No false notification if current status is same as pre-blk and/or
-- no_beacon_data severity
-- Applicable only for beacon managed target
--
IF ( ( l_upd_target_bcn_avail ) AND
( EM_SEVERITY.viol_matches_pre_state(:new.target_guid,
:new.policy_guid,
:new.key_value,
:new.violation_level)
) ) THEN
:new.notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE;
END IF;
--
-- Verify that this is a target's Response.Status up|down metric value.
-- This will let us know if we have to update the availability
--
SELECT count(*)
INTO l_cnt_avail_guids
FROM MGMT_METRICS
WHERE metric_guid = :new.policy_guid
AND metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME
AND metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN;
IF (l_cnt_avail_guids > 0) THEN
--
-- Update the severity type to be of type availability..
--
:new.violation_type := MGMT_GLOBAL.G_SEVERITY_TYPE_AVAILABILITY;
l_update_avail := TRUE;
ELSE
l_update_avail := FALSE;
IF :new.violation_type IS NULL
THEN
BEGIN
SELECT policy_type
INTO l_policy_type
FROM mgmt_policies
WHERE policy_guid = :new.policy_guid ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_policy_type := MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC ;
END ;
IF l_policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC
THEN
:new.violation_type := MGMT_GLOBAL.G_SEVERITY_TYPE_THRESHOLD ;
ELSE
:new.violation_type := MGMT_GLOBAL.G_SEVERITY_TYPE_POLICY ;
END IF ; -- l_policy_type
END IF ; -- :new.violation_type is NULL
END IF; -- cnt_avail_guid >0
--
-- Check if it is agent availability
--
IF (l_update_avail = TRUE) THEN
IF l_target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE
THEN
l_update_agent_avail := TRUE;
ELSE
l_update_agent_avail := FALSE; -- continue
END IF ;
-- Lock the availability records before modifying them.
IF (l_update_agent_avail = TRUE) THEN
EM_SEVERITY.lock_avail_for_agt(l_emd_url);
ELSIF (l_update_avail = TRUE) THEN
-- Lock the availability records before modifying them.
EM_SEVERITY.lock_avail_for_tgt(:new.target_guid);
END IF;
END IF;
--
-- Check for duplicate severities..
--
l_max_sev_ts := NULL;
BEGIN
SELECT collection_timestamp, ROWID
INTO l_max_sev_ts, l_last_violation_rowid
FROM mgmt_last_violation
WHERE target_guid = :new.target_guid
AND policy_guid = :new.policy_guid
AND key_value = :new.key_value;
-- Update in mgmt_last_violation
l_insert_or_update := 'U' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- Insert in mgmt_last_violation
l_insert_or_update := 'I';
-- last_violation Table may not be populated,
-- check the violation table and bootstrap it
SELECT NVL(max(collection_timestamp), EM_SEVERITY.DATE_1900_01_01)
INTO l_max_sev_ts
FROM mgmt_violations
WHERE target_guid = :new.target_guid
AND policy_guid = :new.policy_guid
AND key_value = :new.key_value
AND collection_timestamp > em_severity.DATE_1900_01_01
AND violation_level != MGMT_GLOBAL.G_SEVERITY_COMMENT;
END;
--
-- We need to update the notification_status to 'delivered' (2) if this
-- key_value is one that has never been seen before (so that the
-- notification manager knows not to notify).
--
IF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR) THEN
IF l_max_sev_ts = em_severity.DATE_1900_01_01 THEN
-- l_max_sev_ts would be em_severity.DATE_1900_01_01 when we do not find any existance of
-- given target, policy and key-value. So it is new key value.
:new.notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE;
END IF;
END IF;
l_max_sev_ts := NVL(l_max_sev_ts, :new.collection_timestamp);
l_new_max_sev_ts := GREATEST(l_max_sev_ts, :new.collection_timestamp);
IF l_insert_or_update = 'U' THEN
-- Update or insert last_violation
UPDATE mgmt_last_violation
SET collection_timestamp = l_new_max_sev_ts
WHERE ROWID = l_last_violation_rowid ;
ELSE
-- It is I so insert
INSERT INTO mgmt_last_violation
(target_guid, policy_guid, key_value, collection_timestamp)
VALUES
(:new.target_guid, :new.policy_guid, :new.key_value, l_new_max_sev_ts);
END IF;
OPEN get_last_severity_info
(:new.target_guid, :new.policy_guid, :new.key_value, l_max_sev_ts);
FETCH get_last_severity_info INTO l_last_violation_level,
l_last_collection_timestamp,
l_last_violation_guid,
l_cycle_guid;
IF (get_last_severity_info%ISOPEN) THEN
CLOSE get_last_severity_info;
END IF;
IF (NOT (l_last_violation_level IS NULL)) THEN
--
-- Set the duration for the previous severity or clear
--
IF (:new.collection_timestamp >= l_last_collection_timestamp) THEN
UPDATE mgmt_violations
SET violation_duration = (:new.collection_timestamp -
l_last_collection_timestamp) * 24
WHERE violation_guid = l_last_violation_guid;
ELSE
-- Calculate the violation duration for the current record
-- by looking at the violation previous to this
OPEN get_prev_violation_info
(:new.target_guid, :new.policy_guid,
:new.key_value, :new.collection_timestamp);
FETCH get_prev_violation_info INTO l_prev_violation_level,
l_prev_collection_timestamp,
l_prev_violation_guid;
IF (get_prev_violation_info%ISOPEN) THEN
CLOSE get_prev_violation_info;
END IF;
IF (l_prev_violation_guid IS NOT NULL) THEN
UPDATE mgmt_violations
SET violation_duration = (:new.collection_timestamp -
l_prev_collection_timestamp) * 24
WHERE violation_guid = l_prev_violation_guid;
END IF;
OPEN get_next_violation_info
(:new.target_guid, :new.policy_guid,
:new.key_value, :new.collection_timestamp);
FETCH get_next_violation_info INTO l_next_violation_level,
l_next_collection_timestamp,
l_next_violation_guid;
IF (get_next_violation_info%ISOPEN) THEN
CLOSE get_next_violation_info;
END IF;
IF (l_next_violation_guid IS NOT NULL) THEN
:new.violation_duration := (l_next_collection_timestamp -
:new.collection_timestamp) * 24;
ELSE
:new.violation_duration := 0;
END IF;
END IF;
--
-- CHECK FOR DUPLICATE STATE SEVERITY.
-- If the last severity code is the same as the one that we are
-- currently inserting, then the severity record is a duplicate.
-- Raise a duplicate exception. It will be processed by the loader.
--
IF l_last_violation_level = :new.violation_level THEN
IF EMDW_LOG.p_is_debug_set THEN
EMDW_LOG.debug('Found severity equals last serverity ' ||
:new.violation_level, EM_SEVERITY.MODULE);
END IF;
raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M ||
' The new severity code is the same the last severity code for : ' ||
'(target_guid = ' || :new.target_guid || ')' ||
'(policy_guid = ' || :new.policy_guid || ')' ||
'(key_value = ' || :new.key_value || ')' ||
'(violation_level =' ||
EM_SEVERITY.get_severity_string(:new.violation_level) || ')' );
END IF;
IF ( l_upd_target_bcn_avail OR l_upd_test_bcn_avail ) THEN
--
-- For beacon availability, dont fix severity sequencing
--
NULL;
ELSE
IF (l_update_avail = TRUE) THEN
--
-- For availability severities, do the following checks
-- 1. Check if the availability exists
-- 2. Check if the severity received is after the marker ts
--
BEGIN
SELECT marker_timestamp, marker_avail_status
INTO l_marker_timestamp, l_marker_status
FROM MGMT_AVAILABILITY_MARKER
WHERE target_guid = :new.target_guid;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(MGMT_GLOBAL.INTERNAL_SEVERITY_ERR,
MGMT_GLOBAL.INTERNAL_SEVERITY_ERR_M ||
' No marker data available. ' ||
'(target_guid = ' || :new.target_guid || ')' ||
'(policy_guid = ' || :new.policy_guid || ')' ||
'(key_value = ' || :new.key_value || ')' ||
'(violation_level =' ||
EM_SEVERITY.get_severity_string(:new.violation_level) || ')' ||
'(SQLERROR = ' || SQLERRM ||')' );
END;
--
-- Exempt the following severities from marker check as these severities
-- are created on the OMS side unlike others which are generated
-- by the agent.
-- Unrch Start, Blackout Start, Blackout end severities
-- do not change the avail marker. So no need to do the
-- avail marker time check.
--
-- Unrch end severity is inserted when an agent performs clean hb.
-- Though it modifies avail marker, we exclude the time check because
-- the hb recorder batches heartbeats and there could be a delay
-- between the time when the hb was received and when it is recorded.
-- During the delay there might be other severities inserted, which
-- might move the availability marker.
--
IF ( :new.violation_level IN (MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START,
MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR,
MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END) ) THEN
-- Skip the time check..
NULL;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START) THEN
-- For blackout start severity,
-- Adjust collection_timestamp so that it is after the latest severity
IF (l_marker_timestamp > :new.collection_timestamp) THEN
:new.collection_timestamp := l_marker_timestamp;
END IF;
ELSIF (l_marker_timestamp > :new.collection_timestamp) THEN
--
-- Special case for severities receive during daylight transition
-- time
--
IF (MGMT_GLOBAL.CHECK_OVERLAP(:new.collection_timestamp,
l_tgt_tzrgn) = 0 ) THEN
raise_application_error(MGMT_GLOBAL.SEVERITY_OUT_ORDER_ERR,
MGMT_GLOBAL.SEVERITY_OUT_ORDER_ERR_M ||
' Severity received out of time sequence. ' ||
'(target_guid = ' || :new.target_guid || ')' ||
'(marker_ts = ' || l_marker_timestamp || ')' ||
'(new collection_ts = ' || :new.collection_timestamp || ')' ||
'(violation_level =' ||
EM_SEVERITY.get_severity_string(:new.violation_level) || ')' ||
'(marker_avail_status =' ||
EM_SEVERITY.get_avail_string(l_marker_status) || ')' );
ELSE
-- If there is DST overlap, then set new colleciton timestamp
-- to be 1 second after the marker timestamp
:new.collection_timestamp := l_marker_timestamp + (1/(24*60*60));
-- Chg CTS in mgmt_last_violation tbl as it has been changed
-- due to DST correction
UPDATE mgmt_last_violation
SET collection_timestamp = :new.collection_timestamp
WHERE target_guid = :new.target_guid
AND policy_guid = :new.policy_guid
AND key_value = :new.key_value;
END IF; -- IF (new collection ts is in DST transition)
END IF; -- IF (new collection_ts < marker timestamp)
ELSE
-- DETECT OUT-OF-SEQUENCE severities.
-- For non-availability severities, do the following checks
-- 1. Check to see if the new severity ts is after the
-- last severity ts
--
IF (l_last_collection_timestamp > :new.collection_timestamp) THEN
-- Check to see if the new collection timestamp is in
-- daylight saving transition, overlap. If so, adjust the
-- collection timestamp to be last_collection_timestamp + 1 sec
IF (MGMT_GLOBAL.CHECK_OVERLAP(:new.collection_timestamp,
l_tgt_tzrgn) = 0 ) THEN
raise_application_error(MGMT_GLOBAL.SEVERITY_OUT_ORDER_ERR,
MGMT_GLOBAL.SEVERITY_OUT_ORDER_ERR_M ||
' Severity received out of time sequence. ' ||
'(target_guid = ' || :new.target_guid || ')' ||
'(last_collection_ts = ' || l_last_collection_timestamp || ')' ||
'(new collection_ts = ' || :new.collection_timestamp || ')' ||
'(violation_level =' ||
EM_SEVERITY.get_severity_string(:new.violation_level) || ')' );
ELSE
-- If there is DST overlap, then set new colleciton timestamp
-- to be 1 second after the last collection timestamp.
:new.collection_timestamp :=
l_last_collection_timestamp + (1 / (24 *60 *60));
-- Chg CTS in mgmt_last_violation tbl as it has been changed
-- due to DST correction
UPDATE mgmt_last_violation
SET collection_timestamp = :new.collection_timestamp
WHERE target_guid = :new.target_guid
AND policy_guid = :new.policy_guid
AND key_value = :new.key_value;
END IF; -- IF (new collectiontimestamp is in DST transition)
END IF; -- IF (l_last_collection_timestamp > :new.collection_timestamp)
END IF; -- IF (l_update_avail = TRUE)
END IF; -- IF (l_upd_target_bcn_avail OR l_upd_test_bcn_avail)
END IF; -- IF (get_last_severity_info%FOUND)
-- ALL SEVERITY CHECKS DONE..
-- Clear open errors if availability metric and not beacon managed
IF l_update_avail AND
NOT l_upd_target_bcn_avail AND
:new.violation_level IN (MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
MGMT_GLOBAL.G_SEVERITY_WARNING,
MGMT_GLOBAL.G_SEVERITY_CLEAR,
MGMT_GLOBAL.G_SEVERITY_CRITICAL) THEN
EM_COLL_UTIL.clear_open_metric_errors
(p_target_guid=>:new.target_guid,
p_metric_guid=>:new.policy_guid,
p_collection_timestamp=>:new.collection_timestamp) ;
END IF ;
-- Get current and prev availability for the target
-- NOTE : The current availability status and rowids have to be fetched after
-- clearing any open metric errors, as inserting metric error ends
-- would affect the current availability
IF (l_update_avail = TRUE) THEN
--
-- Get the current availability status
--
l_curr_avail_cursor := EM_SEVERITY.get_avail_cursor(:new.target_guid);
FETCH l_curr_avail_cursor INTO l_curr_avail_rowid,
l_curr_avail_status,
l_curr_avail_start_ts,
l_curr_avail_end_ts;
FETCH l_curr_avail_cursor INTO l_prev_avail_rowid,
l_prev_avail_status,
l_prev_avail_start_ts,
l_prev_avail_end_ts;
IF (l_curr_avail_cursor%NOTFOUND) THEN
-- If prev record does not exist, default prev values
l_prev_avail_rowid := NULL;
l_prev_avail_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
l_prev_avail_start_ts := NULL;
l_prev_avail_end_ts := NULL;
END IF;
CLOSE l_curr_avail_cursor;
END IF;
-- Bug fix 3981814 : Calculate notification status
-- for severities received immediately after blackout
IF (l_update_avail = TRUE) AND
(l_last_violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END) THEN
-- Check to see if the pre-blackout status is the same as current_status
l_states_match := EM_SEVERITY.state_matches_preblkout_state(
:new.target_guid,
:new.violation_level);
-- If the states match, do not send the notification
IF (l_states_match = 1) THEN
:new.notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE;
END IF;
END IF;
IF (:new.violation_level IN (MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,
MGMT_GLOBAL.G_SEVERITY_WARNING,
MGMT_GLOBAL.G_SEVERITY_CRITICAL) ) THEN
EM_POLICY.get_config_key_details(
p_object_guid => :new.target_guid,
p_policy_guid => :new.policy_guid,
p_key_value => :new.key_value,
p_cfg_coll_name => :new.cfg_coll_name,
p_cfg_key_value => :new.cfg_key_value,
p_cfg_key_operator => :new.cfg_key_operator);
-- update the current violation record,
--
EM_SEVERITY.update_current_violation(
p_target_guid => :new.target_guid,
p_policy_guid => :new.policy_guid,
p_key_value => :new.key_value,
p_collection_ts => :new.collection_timestamp,
p_violation_guid => :new.violation_guid,
p_violation_level => :new.violation_level,
p_violation_type => :new.violation_type,
p_annotated_flag => :new.annotated_flag,
p_value => :new.value,
p_string_value => :new.string_value,
p_message => :new.message,
p_message_nlsid => :new.message_nlsid,
p_message_params => :new.message_params,
p_action_message => :new.action_message,
p_action_nlsid => :new.action_message_nlsid,
p_action_message_params => :new.action_message_params,
p_advisory_id => :new.advisory_id,
p_exempt_code => NULL,
p_exempt_until => NULL,
p_exempt_by => NULL,
p_cfg_coll_name => :new.cfg_coll_name,
p_cfg_key_value => :new.cfg_key_value,
p_cfg_key_operator=> :new.cfg_key_operator);
-- Trigger corrective action when this severity arrives.
:new.violation_guid := NVL(:new.violation_guid, SYS_GUID());
BEGIN
IF :new.cfg_coll_name IS NOT NULL AND
:new.cfg_key_value IS NOT NULL AND
:new.cfg_key_operator IS NOT NULL THEN
l_curr_violation.target_guid := :new.target_guid;
l_curr_violation.policy_guid := :new.policy_guid;
l_curr_violation.key_value := :new.key_value;
l_curr_violation.collection_timestamp := :new.collection_timestamp;
l_curr_violation.load_timestamp := :new.load_timestamp;
l_curr_violation.violation_level := :new.violation_level;
l_curr_violation.violation_type := :new.violation_type;
l_curr_violation.violation_duration := :new.violation_duration;
l_curr_violation.violation_guid := :new.violation_guid;
l_curr_violation.annotated_flag := :new.annotated_flag;
l_curr_violation.notification_status := :new.notification_status;
l_curr_violation.value := :new.value;
l_curr_violation.string_value := :new.string_value;
l_curr_violation.message := :new.message;
l_curr_violation.message_nlsid := :new.message_nlsid;
l_curr_violation.message_params := :new.message_params;
l_curr_violation.action_message := :new.action_message;
l_curr_violation.action_message_nlsid := :new.action_message_nlsid;
l_curr_violation.action_message_params := :new.action_message_params;
l_curr_violation.advisory_id := :new.advisory_id;
l_curr_violation.cfg_coll_name := :new.cfg_coll_name;
l_curr_violation.cfg_key_value := :new.cfg_key_value;
l_curr_violation.cfg_key_operator := :new.cfg_key_operator ;
MGMT_JOB_ENGINE.trigger_ca_for_violation(:new.target_guid,
:new.policy_guid,
:new.key_value,
:new.violation_level,
:new.violation_guid,
l_curr_violation,
:new.cfg_coll_name,
:new.cfg_key_value,
:new.cfg_key_operator);
END IF ;
EXCEPTION
WHEN OTHERS THEN
EM_SEVERITY.log_error(:new.target_guid, MGMT_GLOBAL.INVALID_PARAMS_ERR,
'Could not create CA for policy ' || :new.policy_guid ||
' Target ' || :new.target_guid ||
' Violation GUID ' || :new.violation_guid ||
' Key Value ' || :new.key_value ||
': ' || SQLERRM);
END;
IF (l_update_avail = TRUE) THEN
IF (l_update_agent_avail = TRUE) THEN
IF (EM_SEVERITY.is_sev_in_blackout(:new.target_guid,
:new.collection_timestamp) = TRUE) THEN
-- Agent down received during blackout.. Ignore.
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Agent down received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSE
-- Generate Agent down for all targets that are not in BLACKOUT.
EM_SEVERITY.process_agent_down_sev(:new.target_guid,
:new.collection_timestamp,
l_emd_url,
l_curr_avail_status,
l_error);
END IF;
ELSIF ( l_upd_target_bcn_avail ) THEN
--
-- Target's beacon availability was computed to be down.
--
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_DOWN,
:new.violation_guid,
:new.collection_timestamp,
l_error);
ELSE
--
-- Availabilty severity for non-agent target
--
IF (EM_SEVERITY.is_sev_in_blackout(:new.target_guid,
:new.collection_timestamp) = TRUE) THEN
-- Target down received during blackout.. Ignore.
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Target down received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSIF ( (l_curr_avail_status IN (MGMT_GLOBAL.G_STATUS_UNREACHABLE,
MGMT_GLOBAL.G_STATUS_BLACKOUT)) OR
( (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
(l_prev_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) ) ) THEN
-- We reach here if
-- the sev is NOT received during blackout
-- The target is in BLACKOUT or Unrch a BLACKOUT has just ended for this target
-- (i.e., the current avail is BLACKOUT or Unreachable
-- the prev avail is BLACKOUT and the curr avail is UNRCH or UNKNWN)
-- THEN extend the marker
EM_SEVERITY.extend_avail_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_DOWN,
l_error);
-- If the target is just out of BLACKOUT and is not in UNRCH state,
-- update the curr availability
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
EM_SEVERITY.update_current_availability(:new.target_guid,
MGMT_GLOBAL.G_STATUS_DOWN,
:new.collection_timestamp,
:new.violation_guid);
ELSE
-- do not deliver the notification
:new.notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE;
END IF;
ELSIF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_DOWN) THEN
-- Got target down while in error state or down state (duplicate)
-- raise error
raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M ||
' Received WARNING/ALERT severity when availability is DOWN for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
') (curr_avail_status=' ||
EM_SEVERITY.get_avail_string(l_curr_avail_status) ||
')' );
ELSE
-- if current availability = up | error | pending
-- ERROR case is included here so that we recover when
-- we receive severity before error clears.
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_DOWN,
:new.violation_guid,
:new.collection_timestamp,
l_error);
EM_SEVERITY.update_availability_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_DOWN);
END IF; -- (l_curr_avail_status = UNREACHABLE)
END IF; -- (l_update_agent_avail = TRUE)
ELSIF ( l_upd_test_bcn_avail ) THEN
--
-- Schedule a recalculation of the beacon availability of the test
--
MGMT_GENSVC_AVAIL.SCHEDULE_TEST_AVAIL(:new.target_guid, :new.key_value);
ELSE
--
-- non-Resp/Status severity .. Do nothing.
--
NULL;
END IF; -- (l_update_avail = TRUE)
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_CLEAR) THEN
IF (l_update_avail = TRUE) THEN
IF (l_update_agent_avail = TRUE) THEN
IF (EM_SEVERITY.is_sev_in_blackout(:new.target_guid,
:new.collection_timestamp) = TRUE) THEN
-- Received agent up during blackout.. Discard the severity..
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Agent up received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts = ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSE
EM_SEVERITY.process_agent_up_sev(:new.target_guid,
:new.collection_timestamp,
l_emd_url,
l_curr_avail_status,
l_error);
END IF;
ELSIF ( l_upd_target_bcn_avail ) THEN
--
-- Target's beacon availability was computed to be up.
--
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_UP,
:new.violation_guid,
:new.collection_timestamp,
l_error);
ELSE
-- Availability severity for non-agent target
IF (EM_SEVERITY.is_sev_in_blackout(:new.target_guid,
:new.collection_timestamp) = TRUE) THEN
-- Received target up during blackout.. Discard the severity..
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Target up received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts = ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSIF ( (l_curr_avail_status IN (MGMT_GLOBAL.G_STATUS_UNREACHABLE,
MGMT_GLOBAL.G_STATUS_BLACKOUT)) OR
( (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
(l_prev_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) ) ) THEN
-- We reach here if
-- the sev is NOT blacked out
--
-- the current avail is BLACKOUT or UNRCHABLE or
-- the prev avail is BLACKOUT and the curr avail is UNKNWN
-- THEN extend the marker
EM_SEVERITY.extend_avail_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_UP,
l_error);
-- If the current status is UNKNOWN after BLACKOUT fix the curr availability
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
EM_SEVERITY.update_current_availability(:new.target_guid,
MGMT_GLOBAL.G_STATUS_UP,
:new.collection_timestamp,
:new.violation_guid);
ELSE
-- do not deliver the notification
:new.notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE;
END IF;
ELSIF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UP) THEN
-- raise duplicate error..
raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M ||
' Received CLEAR severity when availability is UP for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
') (curr_avail_status=' ||
EM_SEVERITY.get_avail_string(l_curr_avail_status) ||
')' );
ELSE
-- if current availability = down | error | pending
-- ERROR case is included here so that we recover when
-- we receive severity before error clears.
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_UP,
:new.violation_guid,
:new.collection_timestamp,
l_error);
EM_SEVERITY.update_availability_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_UP);
END IF; -- END IF l_curr_avail = BLACKOUT
END IF; -- IF (l_update_agent_avail = TRUE)
ELSIF ( l_upd_test_bcn_avail ) THEN
--
-- Schedule a recalculation of the beacon availability of the test
--
MGMT_GENSVC_AVAIL.SCHEDULE_TEST_AVAIL(:new.target_guid, :new.key_value);
ELSE
-- non Availability CLEAR severity .. do nothing.
NULL;
END IF; -- END IF l_update_avail = TRUE
--
-- Remove the violation record from current violation table because it
-- has been cleared.
--
EM_SEVERITY.delete_current_violation(:new.target_guid,
:new.policy_guid,
:new.key_value);
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_START) THEN
IF (EM_SEVERITY.is_sev_in_blackout(:new.target_guid,
:new.collection_timestamp) = TRUE) THEN
-- Received target metric error during blackout.. Discard the severity..
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Target metric error start received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts = ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSIF ( (l_curr_avail_status IN (MGMT_GLOBAL.G_STATUS_UNREACHABLE,
MGMT_GLOBAL.G_STATUS_BLACKOUT)) OR
( (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
(l_prev_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) ) ) THEN
EM_SEVERITY.extend_avail_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_ERROR,
l_error);
-- If the current status is UNKNOWN after BLACKOUT fix the curr availability
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) THEN
EM_SEVERITY.update_current_availability(:new.target_guid,
MGMT_GLOBAL.G_STATUS_ERROR,
:new.collection_timestamp,
:new.violation_guid);
ELSE
-- do not deliver the notification
:new.notification_status := MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE;
END IF;
ELSIF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_ERROR) THEN
-- Receieved duplicate error starts... raise error.
raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
') (curr_avail_status=' ||
EM_SEVERITY.get_avail_string(l_curr_avail_status) ||
')' );
ELSE
-- if current availability = up | down | agent_down | pending
-- Close the existing record
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_ERROR,
:new.violation_guid,
:new.collection_timestamp,
l_error);
EM_SEVERITY.update_availability_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_ERROR);
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_ERROR_END) THEN
IF (l_target_type IN (MGMT_GLOBAL.G_HOST_TARGET_TYPE,
MGMT_GLOBAL.G_AGENT_TARGET_TYPE) ) THEN
l_new_avail_status := MGMT_GLOBAL.G_STATUS_UP;
ELSE
l_new_avail_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
END IF;
IF (EM_SEVERITY.is_sev_in_blackout(:new.target_guid,
:new.collection_timestamp) = TRUE) THEN
-- Received target metric error during blackout.. Discard the severity..
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Target metric error end received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSIF ( (l_curr_avail_status IN (MGMT_GLOBAL.G_STATUS_UNREACHABLE,
MGMT_GLOBAL.G_STATUS_BLACKOUT)) OR
( (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNKNOWN) AND
(l_prev_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) ) ) THEN
-- Extend marker status to :new.collection_timestamp ignoring blackouts..
EM_SEVERITY.extend_avail_marker(:new.target_guid,
:new.collection_timestamp,
l_new_avail_status,
l_error);
-- For UNKNOWN case, there is no need to update the current availability
-- as it is already UNKNOWN
ELSIF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_ERROR) THEN
-- Close the existing ERROR record
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
l_new_avail_status,
:new.violation_guid,
:new.collection_timestamp,
l_error);
EM_SEVERITY.update_availability_marker(:new.target_guid,
:new.collection_timestamp,
l_new_avail_status);
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_START) THEN
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
-- If current availability IS BLACKOUT,
-- do not affect availability .. wait till blackout clears..
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Target unreachable start received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || :new.collection_timestamp ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSIF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
-- Is it duplicate Unreachable start severity??
raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR,
MGMT_GLOBAL.DUPLICATE_RECORD_ERR_M ||
' Duplicate unreachable start record for : ' ||
'(target_guid = ' || :new.target_guid || ')' ||
'(policy_guid = ' || :new.policy_guid || ')' ||
'(key_value = ' || :new.key_value || ')' ||
'(violation_level =' ||
EM_SEVERITY.get_severity_string(:new.violation_level) || ')' );
ELSE
EM_SEVERITY.process_unreachable_start(:new.target_guid,
l_curr_avail_start_ts,
l_curr_avail_rowid,
:new.violation_guid,
l_error);
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_UNREACHABLE_CLEAR) THEN
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
-- Cleanup any unreachable records from marker ts to
-- this unreachable record.
l_new_avail_status := MGMT_GLOBAL.G_STATUS_UNREACHABLE;
-- Update the marker to collection_ts as we got here
-- beacause of a clean HB.
EM_SEVERITY.process_unreachable_clear(:new.target_guid,
:new.collection_timestamp,
:new.message,
l_error,
:new.message_nlsid,
:new.message_params,
l_new_avail_status);
-- Check to see if the target recovered from Unreachable state,
-- If not, raise application error
-- Add an ASSERT to make sure that the agent Unreachability is clearted.
IF (l_new_avail_status = MGMT_GLOBAL.G_STATUS_UNREACHABLE) THEN
-- Donot accept the severity if agent unreachability is not cleared
raise_application_error(MGMT_GLOBAL.INVALID_UNRCH_CLR_VIOL_ERR,
MGMT_GLOBAL.INVALID_UNRCH_CLR_VIOL_ERR_M ||
' Could not recover from Unreachability ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || to_char(:new.collection_timestamp, MGMT_GLOBAL.G_AGENT_DATE_FORMAT) ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
END IF;
ELSIF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
-- Dont accept the severity if received during blackout
raise_application_error(MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR,
MGMT_GLOBAL.SEVERITY_IN_BLACKOUT_ERR_M ||
' Target unreachable clear received during blackout for : ' ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || to_char(:new.collection_timestamp, MGMT_GLOBAL.G_AGENT_DATE_FORMAT) ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
ELSE
-- Not possible to receive unreachable clear here
-- Dont accept the severity if received from other state.
raise_application_error(MGMT_GLOBAL.INVALID_UNRCH_CLR_VIOL_ERR,
MGMT_GLOBAL.INVALID_UNRCH_CLR_VIOL_ERR_M ||
' Unreachability clear severity received with current status = ' || l_curr_avail_status ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (collection_ts= ' || to_char(:new.collection_timestamp, MGMT_GLOBAL.G_AGENT_DATE_FORMAT) ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
')' );
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START) THEN
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
-- Duplicate blackout start received.. Ignore..
raise_application_error(MGMT_GLOBAL.INCONSISTANT_SEVERITY_ERR,
MGMT_GLOBAL.INCONSISTANT_SEVERITY_ERR_M ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid=' || :new.policy_guid ||
') (key_value= ' || :new.key_value ||
') (violation_level =' || EM_SEVERITY.get_severity_string(:new.violation_level) ||
') (curr_avail_status=' ||
EM_SEVERITY.get_avail_string(l_curr_avail_status) ||
')' );
ELSE
-- Close the existing availability record
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_BLACKOUT,
:new.violation_guid,
:new.collection_timestamp,
l_error);
-- Do not affect the availability marker..
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END) THEN
IF (l_curr_avail_status = MGMT_GLOBAL.G_STATUS_BLACKOUT) THEN
l_is_host := 0;
SELECT COUNT(1) INTO l_is_host
FROM MGMT_TARGETS
WHERE target_guid = :new.target_guid
AND target_type IN (MGMT_GLOBAL.G_HOST_TARGET_TYPE,
MGMT_GLOBAL.G_AGENT_TARGET_TYPE);
l_agent_unreachable := EM_SEVERITY.is_agent_unreachable(:new.target_guid);
-- ELSIF (l_is_host > 0) THEN
-- For host mark the status as UP.
-- l_new_avail_status := MGMT_GLOBAL.G_STATUS_UP;
if ((l_agent_unreachable = TRUE) OR (l_is_host > 0)) THEN
l_new_avail_status := MGMT_GLOBAL.G_STATUS_UNREACHABLE;
ELSE
l_new_avail_status := MGMT_GLOBAL.G_STATUS_UNKNOWN;
END IF;
-- Close the existing BLACKOUT record
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
l_new_avail_status,
:new.violation_guid,
:new.collection_timestamp,
l_error);
--
-- Recompute beacon availability
--
IF ( l_upd_target_bcn_avail ) THEN
MGMT_GENSVC_AVAIL.SCHEDULE_TARGET_AVAIL(:new.target_guid);
END IF;
ELSE
-- Blackout END received when target is NOT in blackout..
-- Agent sends blackout END during start up. (ignore them)
NULL;
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_NO_BEACONS) THEN
IF ( l_upd_target_bcn_avail ) THEN
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_UNKNOWN,
:new.violation_guid,
:new.collection_timestamp,
l_error);
ELSE
raise_application_error(MGMT_GLOBAL.INVALID_SEVERITY_CODE_ERR,
MGMT_GLOBAL.INVALID_SEVERITY_CODE_ERR_M ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid = ' || :new.policy_guid ||
') (key_value = ' || :new.key_value ||
') (violation_level = ' || :new.violation_level || ')' );
END IF;
ELSIF (:new.violation_level = MGMT_GLOBAL.G_SEVERITY_UNKNOWN) THEN
EM_SEVERITY.process_simple_avail(l_curr_avail_rowid,
:new.target_guid,
MGMT_GLOBAL.G_STATUS_UNKNOWN,
:new.violation_guid,
:new.collection_timestamp,
l_error);
EM_SEVERITY.update_availability_marker(:new.target_guid,
:new.collection_timestamp,
MGMT_GLOBAL.G_STATUS_UNKNOWN);
ELSE
-- Severity code received is invalid
raise_application_error(MGMT_GLOBAL.INVALID_SEVERITY_CODE_ERR,
MGMT_GLOBAL.INVALID_SEVERITY_CODE_ERR_M ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid = ' || :new.policy_guid ||
') (key_value = ' || :new.key_value ||
') (violation_level = ' || :new.violation_level || ')' );
END IF;
ELSE
IF (:new.violation_level IS NULL) THEN
-- Severity code received is invalid
raise_application_error(MGMT_GLOBAL.INVALID_SEVERITY_CODE_ERR,
MGMT_GLOBAL.INVALID_SEVERITY_CODE_ERR_M ||
' (target_guid = ' || :new.target_guid ||
') (policy_guid = ' || :new.policy_guid ||
') (key_value = ' || :new.key_value ||
') (violation_level = ' || :new.violation_level || ')' );
END IF;
END IF; -- If violation_level != COMMENT
:new.violation_guid := NVL(:new.violation_guid,SYS_GUID()) ;
IF NVL(l_last_violation_level,MGMT_GLOBAL.G_SEVERITY_CLEAR) =
MGMT_GLOBAL.G_SEVERITY_CLEAR
THEN
:new.cycle_guid := :new.violation_guid ;
ELSE
:new.cycle_guid := l_cycle_guid ;
END IF ;
-- Add the severity to the notification queue for stand alone mode
IF (EMD_MAINTENANCE.IS_CENTRAL_MODE = 0) THEN
-- If not central mode, ping down devices and
EMD_NOTIFICATION.PING_DEVICES;
l_curr_violation.target_guid := :new.target_guid;
l_curr_violation.policy_guid := :new.policy_guid;
l_curr_violation.key_value := :new.key_value;
l_curr_violation.collection_timestamp := :new.collection_timestamp;
l_curr_violation.load_timestamp := :new.load_timestamp;
l_curr_violation.violation_level := :new.violation_level;
l_curr_violation.violation_type := :new.violation_type;
l_curr_violation.violation_duration := :new.violation_duration;
l_curr_violation.violation_guid := :new.violation_guid;
l_curr_violation.annotated_flag := :new.annotated_flag;
l_curr_violation.notification_status := :new.notification_status;
l_curr_violation.message := :new.message;
l_curr_violation.message_nlsid := :new.message_nlsid;
l_curr_violation.message_params := :new.message_params;
l_curr_violation.action_message := :new.action_message;
l_curr_violation.action_message_nlsid := :new.action_message_nlsid;
l_curr_violation.action_message_params := :new.action_message_params;
l_curr_violation.advisory_id := :new.advisory_id;
l_curr_violation.user_name := :new.user_name;
-- queue the severity for notification
-- Bug 6979013: If notification_status = 2, do not queue notification
IF l_curr_violation.notification_status !=
MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE THEN
EMD_NOTIFICATION.QUEUE_METRIC_NOTIFICATIONS(l_curr_violation);
END IF;
-- If this severity ends a blackout for a repository side target,
-- then re-evaluate its status based on the member statuses
IF (:new.violation_level = mgmt_global.G_SEVERITY_BLACKOUT_END) THEN
em_severity_repos.sync_eval_target_severities(:new.target_guid);
END IF;
-- Remember the target-guid so that we can process the severities
-- of the parent targets synchronously in SA mode
em_severity.P_LAST_TARGET_GUID := :new.target_guid;
END IF;
IF :new.policy_guid = EM_SEVERITY.CLUSTER_CRS_EVENT_METRIC_GUID
THEN
BEGIN
l_violation_summary_rec := MGMT_VIOLATION_SUMMARY_REC(:new.target_guid,
:new.policy_guid,
:new.key_value,
:new.collection_timestamp,
:new.violation_level,
:new.message,
:new.message_nlsid,
:new.message_params,
:new.value,
:new.string_value
);
EM_SEVERITY.exec_crs_event_callbacks(l_violation_summary_rec,l_save_violation);
IF (l_save_violation = 0) THEN
RAISE_APPLICATION_ERROR(MGMT_GLOBAL.IGNORE_SEVERITY_ERR,
MGMT_GLOBAL.IGNORE_SEVERITY_ERR_M || '( target_guid = ' || l_violation_summary_rec.target_guid ||
' policy_guid = ' || l_violation_summary_rec.policy_guid || ')');
END IF;
END;
END IF;
-- do not add severity in notify_input_q in case of db control as
-- dbcontrol directly call QUEUE_METRIC_NOTIFICATIONS.
IF(:new.notification_status != MGMT_GLOBAL.G_NOTIF_STATUS_IGNORE AND
EMD_MAINTENANCE.IS_CENTRAL_MODE != 0)
THEN
EMD_NOTIFICATION.QUEUE_NOTIF_INPUT(:new.violation_guid, EMD_NOTIFICATION.GUID_TYPE_SEVERITY);
END IF;
IF EMDW_LOG.P_IS_DEBUG_SET THEN
EMDW_LOG.DEBUG('em_violation_checks:Exit',EM_SEVERITY.MODULE) ;
END IF ;
END;
/
show errors;
CREATE OR REPLACE TRIGGER em_violation_update
BEFORE UPDATE OF violation_level ON mgmt_violations FOR EACH ROW
WHEN ( new.violation_level <> old.violation_level)
BEGIN
-- Disallow update of violation levels. The only acceptable means
-- of changing a severity code is by successfully inserting a new row
-- into the mgmt_violations table.
raise_application_error(MGMT_GLOBAL.UPDATE_RECORD_ERR,
MGMT_GLOBAL.UPDATE_RECORD_ERR_M ||
'Violation levels cannot be updated. ');
END;
/
show errors;
rem
rem If a violation is deleted, we also want to delete related information
rem
CREATE OR REPLACE TRIGGER em_violation_delete
AFTER DELETE ON mgmt_violations FOR EACH ROW
BEGIN
-- We delete in bulk in the purge api so we want this
-- trigger not to delete from these tables for every row.
IF NOT EM_SEVERITY.g_from_purge_api
THEN
-- Delete current violation
DELETE FROM mgmt_current_violation
WHERE violation_guid = :old.violation_guid;
-- Delete violation context
DELETE FROM mgmt_violation_context
WHERE target_guid = :old.target_guid
AND policy_guid = :old.policy_guid
AND key_value = :old.key_value
AND collection_timestamp = :old.collection_timestamp;
--
-- Following deletes should move to rollups when
-- we implement rollups for severities
--
-- Delete all annotations for this violation
DELETE FROM MGMT_SEVERITY_ANNOTATION a
WHERE a.severity_guid = :old.violation_guid;
-- Delete notification history for this severity
DELETE FROM MGMT_NOTIFICATION_LOG l
WHERE l.source_obj_type = mgmt_global.G_ANNOTATION_SOURCE_SEVERITY
AND l.source_obj_guid = :old.violation_guid;
ELSE
-- This collection would be use in delete_policy_data for
-- bulk delete in related tables.
EM_SEVERITY.p_violation_guids.extend;
EM_SEVERITY.p_violation_guids(EM_SEVERITY.p_violation_guids.last) :=
:OLD.violation_guid;
END IF ;
END;
/
show errors
--
-- INSTEAD OF INSERT trigger to redirect inserts to mgmt_annotation
-- with proper source_obj_type
--
CREATE OR REPLACE TRIGGER sev_annotation_insert_tr
INSTEAD OF INSERT ON MGMT_SEVERITY_ANNOTATION
BEGIN
INSERT INTO MGMT_ANNOTATION
(source_obj_type, source_obj_guid, timestamp, annotation_type,
user_name, message)
VALUES
(mgmt_global.G_ANNOTATION_SOURCE_SEVERITY, :new.severity_guid,
:new.collection_timestamp, :new.annotation_type, :new.user_name,
:new.message);
END;
/
show errors;
--
-- INSTEAD OF DELETE trigger to redirect deletes to mgmt_annotation
-- with proper source_obj_type
--
CREATE OR REPLACE TRIGGER sev_annotation_delete_tr
INSTEAD OF DELETE ON MGMT_SEVERITY_ANNOTATION
BEGIN
DELETE FROM MGMT_ANNOTATION
WHERE source_obj_type = mgmt_global.G_ANNOTATION_SOURCE_SEVERITY
AND source_obj_guid = :old.severity_guid
AND timestamp = :old.collection_timestamp;
END;
/
show errors;
--
-- Insert severity statement level trigger for synchronous
-- availability processing only in DBCONSOLE mode
--
COLUMN :script_name NEW_VALUE dbconsole_file NOPRINT
VARIABLE script_name VARCHAR2(256)
BEGIN
IF ('&&EM_REPOS_MODE' <> 'CENTRAL') THEN
:script_name := '&&EM_SQL_ROOT/core/latest/severity/severity_triggers_dbconsole.sql';
ELSE
:script_name := '&&EM_SQL_ROOT/core/latest/admin/admin_do_nothing.sql';
END IF;
END;
/
SELECT :script_name FROM DUAL;
@@&&dbconsole_file