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