Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\blackout\blackout_triggers.sql
Rem drv: <create type="triggers" pos="basic/basic_triggers.sql+"/> Rem Rem $Header: blackout_triggers.sql 02-aug-2006.11:52:46 rkpandey Exp $ Rem $Header: blackout_triggers.sql 02-aug-2006.11:52:46 rkpandey Exp $ Rem Rem blackout_triggers.sql Rem Rem Copyright (c) 2002, 2006, Oracle. All rights reserved. Rem Rem NAME Rem blackout_triggers.sql - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem <other useful comments, qualifications, etc.> Rem Rem MODIFIED (MM/DD/YY) Rem rkpandey 08/02/06 - Backport rkpandey_fix_host_notif from main Rem skini 12/12/05 - Backport skini_bug-4696597 from main Rem rkpandey 03/31/06 - blackout agent at last Rem skini 11/28/05 - Fix bu 4696597: when closing node-level Rem agent-side blackouts, process all current Rem targets Rem skini 09/21/05 - After avail change, catch all exceptions Rem pratagar 08/29/05 - bug 4572196 Rem pmodi 07/08/05 - Bug:4399250 - Use NLS message id for blackout Rem skini 07/10/05 - Fix bug 4367848: catch NO_DATA_FOUND Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem skini 04/12/05 - Fix infinite loop Rem skini 04/07/05 - Add update trigger to MMGT_BLACKOUTS Rem skini 03/21/05 - Change blackout message Rem kmanicka 03/04/05 - move to EMDW_LOG from dbms_output Rem skini 02/20/05 - Fix 4150882: select distinct Rem gsbhatia 02/13/05 - updating repmgr header Rem gsbhatia 02/09/05 - updating repmgr header Rem gsbhatia 02/07/05 - updating repmgr header Rem ktlaw 01/11/05 - add repmgr header Rem snakai 11/22/04 - update beacon avail calls Rem dhgoswam 11/02/04 - bug 3851644 Rem snakai 10/31/04 - process blackout end events for beacon monitored Rem tgts Rem rpinnama 09/13/04 - mgmt_severity -> mgmt_violations (esp for returning clause) Rem skini 09/03/04 - Fix equal start times Rem skini 08/30/04 - Fix for bug 3413008: account for out-of-order Rem jobs Rem skini 08/18/04 - Catch no_data_found Rem gan 07/16/04 - handle duplicate record for severity bug 3492516 Rem skini 12/15/03 - Move end time check to trigger Rem skini 12/05/03 - Obtain a lock before querying in insert trigger Rem streddy 11/30/03 - Call master_agent package for blackouts Rem skini 09/25/03 - Convert repos columns to utc columns Rem skini 09/12/03 - Update source for emctl BOs Rem skini 08/28/03 - Only update windows for the current occurrence Rem ancheng 02/14/03 - target version fix Rem skini 01/16/03 - Insert flat target entries for cli blackouts Rem skini 12/05/02 - Generate messages Rem skini 12/03/02 - Change in set_blackout_status Rem skini 11/24/02 - Add repos timestamps to windows table Rem skini 11/15/02 - Use category props Rem skini 11/12/02 - Add triggers to update blackout windows Rem skini 08/26/02 - ENDED state Rem skini 08/25/02 - Use set_blackout_status Rem skini 08/20/02 - New blackout states Rem skini 08/05/02 - Update blackout status Rem skini 07/30/02 - Overlapping blackouts Rem skini 07/26/02 - continue blackout development Rem skini 07/23/02 - Handle overlapping blackouts Rem rpinnama 06/05/02 - Insert severity record on detecting blackout start. Rem skini 06/12/02 - Table name change Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/14/02 - Created Rem rem rem capture some availability data by watching blackout state changes rem (0 = in blackout, 1 = out of blackout) rem CREATE OR REPLACE TRIGGER blackout_change BEFORE INSERT ON MGMT_BLACKOUT_STATE FOR EACH ROW DECLARE latest_availability_rowid ROWID; latest_availability_status NUMBER; latest_availability_sev_guid RAW(16); new_annotation_guid RAW(16); l_emd_url VARCHAR2(2000); l_count NUMBER; l_target_guid RAW(16); is_target_type_host BOOLEAN; host_guid RAW(16); host_availability_status NUMBER; l_current_target_state NUMBER; l_current_blackout_state NUMBER; l_blackout_name MGMT_BLACKOUTS.blackout_name%TYPE; l_created_by MGMT_BLACKOUTS.created_by%TYPE; l_reason_id MGMT_BLACKOUTS.reason_id%TYPE; l_reason MGMT_BLACKOUT_REASON.reason%TYPE; l_created_thru MGMT_BLACKOUTS.created_thru%TYPE; l_actual_start_time DATE; l_actual_end_time DATE; l_occurrence_number NUMBER; response_metric_guid RAW(16); update_availability BOOLEAN := false; l_new_blackout_status NUMBER; l_monitoring_mode NUMBER; l_open_windows MGMT_BLACKOUT_WINDOW_ARRAY; l_current_window MGMT_BLACKOUT_WINDOW_RECORD; -- A list of blackout windows to potentially end as part -- of the processing for the current window. -- Entries here are inserted in ascending order of end time. l_blackouts_to_end MGMT_BLACKOUT_WINDOW_ARRAY := MGMT_BLACKOUT_WINDOW_ARRAY(); k NUMBER; l_start_msg VARCHAR2(1000); l_stop_msg VARCHAR2(1000); l_start_msg_params VARCHAR2(1000); l_stop_msg_params VARCHAR2(1000); BEGIN -- Check for invalid entries BEGIN SELECT blackout_status, created_thru, occurrence_number, created_by, reason_id, blackout_name INTO l_current_blackout_state, l_created_thru, l_occurrence_number, l_created_by, l_reason_id, l_blackout_name FROM MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid; IF l_reason_id IS NOT NULL THEN SELECT reason INTO l_reason FROM MGMT_BLACKOUT_REASON WHERE reason_id=l_reason_id; ELSE l_reason := '<none>'; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- Blackout definition does not exist! Reject it? raise_application_error(MGMT_GLOBAL.INVALID_BLACKOUT_ERR, 'Blackout ' || :new.blackout_guid || ' does not exist'); END; -- Check for duplicates. We reject the last one SELECT COUNT(*) INTO l_count FROM MGMT_BLACKOUT_STATE WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid AND collection_timestamp=:new.collection_timestamp AND blackout_code=:new.blackout_code; IF l_count > 0 THEN raise_application_error(MGMT_GLOBAL.DUPLICATE_RECORD_ERR, 'Duplicate record generated for guid ' || :new.blackout_guid); END IF; -- Lock a row in the blackouts proxy tables to serialize -- severity inserts for a single target SELECT target_guid INTO l_target_guid FROM MGMT_BLACKOUT_PROXY_TARGETS WHERE target_guid=:new.target_guid FOR UPDATE; -- Collect all active blackout windows that start before the current -- one. Some of them will potentially have to be started, others stopped SELECT MGMT_BLACKOUT_WINDOW_RECORD(blackout_guid, occurrence_number, start_time, end_time, status) BULK COLLECT INTO l_open_windows FROM MGMT_BLACKOUT_WINDOWS WHERE target_guid=:new.target_guid AND status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED, MGMT_BLACKOUT.BLK_STATE_ENDED) AND start_time < :new.collection_timestamp AND blackout_guid != :new.blackout_guid ORDER BY start_time; IF l_open_windows IS NOT NULL AND l_open_windows.COUNT > 0 THEN -- We need to always lock the blackouts in the same order to -- prevent deadlocks. Proceed only after all locks are obtained. -- MGMT_BLACKOUT_ENGINE.lock_windows(l_open_windows); FOR i IN 1..l_open_windows.COUNT LOOP l_current_window := l_open_windows(i); -- Close all pending windows whose end time is less -- than the current start time. Note that these -- windows are already in sorted order. FOR j IN 1..l_blackouts_to_end.COUNT LOOP IF l_blackouts_to_end(j).end_time <= l_current_window.start_time THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Closing window with end time ' || to_char(l_blackouts_to_end(j).end_time, 'mm/dd/yyyy hh:mi:ss pm'),MGMT_BLACKOUT_ENGINE.MODULE_NAME); END IF; MGMT_BLACKOUT_ENGINE.end_blackout_window(l_blackouts_to_end(j).blackout_guid, l_blackouts_to_end(j).occurrence_number, :new.target_guid, l_blackouts_to_end(j).end_time); l_blackouts_to_end.delete(j); END IF; END LOOP; -- Insert a START for the current window, if required IF l_current_window.status=MGMT_BLACKOUT.BLK_STATE_START_PENDING THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('STARTING window with start time ' || to_char(l_current_window.start_time, 'mm/dd/yyyy hh:mi:ss pm'),MGMT_BLACKOUT_ENGINE.MODULE_NAME); END IF; MGMT_BLACKOUT_ENGINE.start_blackout_window(l_current_window.blackout_guid, l_current_window.occurrence_number, :new.target_guid); END IF; -- Add the end time in sorted order to the current list -- of blackouts that may need to be ended IF l_current_window.end_time IS NOT NULL THEN k := 1; WHILE k <= l_blackouts_to_end.COUNT AND l_blackouts_to_end(k).end_time < l_current_window.end_time LOOP k := k+1; END LOOP; l_blackouts_to_end.extend(1); IF k < l_blackouts_to_end.COUNT THEN FOR l IN k+1..l_blackouts_to_end.COUNT-1 LOOP l_blackouts_to_end(l+1):= l_blackouts_to_end(l); END LOOP; END IF; l_blackouts_to_end(k) := l_current_window; IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Added to end list window with start time ' || to_char(l_current_window.start_time, 'mm/dd/yyyy hh:mi:ss pm') || ' end time ' || to_char(l_current_window.end_time, 'mm/dd/yyyy hh:mi:ss pm') || ' at index ' || k,MGMT_BLACKOUT_ENGINE.MODULE_NAME); END IF; END IF; END LOOP; -- End all remaining open windows that need to be ended -- before this one -- Note that the previous loop would have -- ended only blackout windows scheduled to end before the -- start of another one FOR j IN 1..l_blackouts_to_end.COUNT LOOP IF l_blackouts_to_end(j).end_time < :new.collection_timestamp THEN IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Closing window with end time ' || to_char(l_blackouts_to_end(j).end_time, 'mm/dd/yyyy hh:mi:ss pm'),MGMT_BLACKOUT_ENGINE.MODULE_NAME); END IF; MGMT_BLACKOUT_ENGINE.end_blackout_window(l_blackouts_to_end(j).blackout_guid, l_blackouts_to_end(j).occurrence_number, :new.target_guid, l_blackouts_to_end(j).end_time); END IF; END LOOP; END IF; -- Get the current target state, accounting for overlapping -- blackouts SELECT SUM(decode(blackout_status, 1, 1, 0)) INTO l_count FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE target_guid=:new.target_guid; IF l_count > 0 THEN l_current_target_state := MGMT_BLACKOUT_ENGINE.IN_BLACKOUT; ELSE l_current_target_state := MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT; END IF; IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('Old target state for ' || :new.target_guid || ' is ' || l_current_target_state,MGMT_BLACKOUT_ENGINE.MODULE_NAME); END IF; -- Update the state of the target in the blackout flat target status UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET blackout_status=:new.blackout_code WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid; IF :new.blackout_code = MGMT_BLACKOUT_ENGINE.IN_BLACKOUT THEN :new.target_status := MGMT_BLACKOUT_ENGINE.IN_BLACKOUT; ELSE -- Obtain the overall state of the target; this is what we -- should actually set the target_state to SELECT SUM(decode(blackout_status, 1, 1, 0)) INTO l_count FROM MGMT_BLACKOUT_FLAT_TARGETS WHERE target_guid=:new.target_guid; IF l_count > 0 THEN -- In blackout :new.target_status := MGMT_BLACKOUT_ENGINE.IN_BLACKOUT; ELSE -- Not in blackout :new.target_status := MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT; END IF; END IF; -- Update the overall blackout state IF this is a cli blackout -- For repository blackouts, the overall state will be updated -- after all targets have been processed IF l_created_thru IS NOT NULL THEN IF :new.blackout_code=MGMT_BLACKOUT_ENGINE.IN_BLACKOUT THEN UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_STARTED WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid; ELSE UPDATE MGMT_BLACKOUT_FLAT_TARGETS SET job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_STOPPED WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid; END IF; MGMT_BLACKOUT_ENGINE.set_blackout_status(:new.blackout_guid, MGMT_BLACKOUT_ENGINE.BLK_MODE_NONE); SELECT blackout_status INTO l_new_blackout_status FROM MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid; IF l_new_blackout_status=MGMT_BLACKOUT_ENGINE.BLK_STATE_ENDED THEN SELECT emd_url INTO l_emd_url FROM MGMT_TARGETS WHERE target_guid=:new.target_guid; MGMT_BLACKOUT_ENGINE.submit_discard_state_job(:new.blackout_guid, l_emd_url, :new.collection_timestamp); END IF; END IF; IF EMDW_LOG.p_is_debug_set THEN EMDW_LOG.debug('New target state for ' || :new.target_guid || ' is ' || :new.target_status,MGMT_BLACKOUT_ENGINE.MODULE_NAME); END IF; -- Generate a severity (which will affect availability) -- only if the state of the target has changed IF :new.target_status != l_current_target_state THEN update_availability := true; END IF; -- TODO: -- Exclude beacons -- Generate appropriate annotations -- Log errors if we receive an invalid blackout code -- or target_guid. -- -- Get Response/Status Metric guid for the target -- Can we avoid getting this guid and get this in severity trigger? -- IF update_availability THEN BEGIN SELECT DISTINCT met.metric_guid INTO response_metric_guid FROM MGMT_TARGETS tgt, MGMT_METRICS met WHERE tgt.target_guid = :new.target_guid AND tgt.target_type = met.target_type AND met.metric_name = MGMT_GLOBAL.G_AVAIL_METRIC_NAME AND met.metric_column = MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN AND met.type_meta_ver = tgt.type_meta_ver AND (met.category_prop_1 = tgt.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = tgt.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = tgt.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = tgt.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = tgt.category_prop_5 OR met.category_prop_5 = ' '); update_availability := TRUE; EXCEPTION WHEN NO_DATA_FOUND THEN update_availability := FALSE; END; END IF; IF (update_availability = TRUE) THEN IF (:new.target_status = MGMT_BLACKOUT_ENGINE.IN_BLACKOUT) THEN l_start_msg := REPLACE(MGMT_BLACKOUT_ENGINE.BLK_START_MSG, '{0}', l_created_by); l_start_msg := REPLACE(l_start_msg, '{1}', l_reason); l_start_msg := REPLACE(l_start_msg, '{2}', l_blackout_name); l_start_msg_params := l_created_by||'&'||l_reason||'&'||l_blackout_name ; -- Setting blackout for this target... BEGIN INSERT INTO mgmt_violations (target_guid, policy_guid, collection_timestamp, violation_level, message, message_nlsid, message_params) VALUES (:new.target_guid, response_metric_guid, :new.collection_timestamp, MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, l_start_msg, MGMT_BLACKOUT_ENGINE.BLK_START_MSG_NLSID, l_start_msg_params ) RETURNING collection_timestamp INTO l_actual_start_time; EXCEPTION WHEN MGMT_GLOBAL.duplicate_record THEN :new.collection_timestamp := MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity( :new.blackout_guid, :new.target_guid, response_metric_guid, MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, MGMT_BLACKOUT_ENGINE.BO_START_MSG, MGMT_BLACKOUT_ENGINE.BO_START_MSG_NLSID, NULL ); WHEN MGMT_GLOBAL.internal_severity THEN :new.collection_timestamp := MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity( :new.blackout_guid, :new.target_guid, response_metric_guid, MGMT_GLOBAL.G_SEVERITY_BLACKOUT_START, MGMT_BLACKOUT_ENGINE.BO_START_MSG, MGMT_BLACKOUT_ENGINE.BO_START_MSG_NLSID, NULL ); END; IF l_actual_start_time > :new.collection_timestamp THEN BEGIN :new.collection_timestamp := l_actual_start_time; SELECT end_time INTO l_actual_end_time FROM MGMT_BLACKOUT_WINDOWS WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid AND occurrence_number=l_occurrence_number AND status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED, MGMT_BLACKOUT.BLK_STATE_ENDED); -- Adjust the end time if required IF l_actual_end_time IS NOT NULL AND l_actual_end_time <= l_actual_start_time THEN l_actual_end_time := l_actual_start_time+(1/(60*60*24)); END IF; UPDATE MGMT_BLACKOUT_WINDOWS SET start_time=l_actual_start_time, end_time=l_actual_end_time WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid AND occurrence_number=l_occurrence_number AND status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED, MGMT_BLACKOUT.BLK_STATE_ENDED); EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; -- Blackout all the tests of Services (i.e. webapps) BEGIN MGMT_GENSVC_AVAIL.PROCESS_BLACKOUT_START(:new.target_guid, :new.collection_timestamp); EXCEPTION WHEN OTHERS THEN MGMT_BLACKOUT_ENGINE.log_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Error calling gensvc.process_blackout_start() for blackout ' || :new.blackout_guid || ' on target ' || :new.target_guid); END; ELSIF (:new.target_status = MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT) THEN -- Clearing blackout.. BEGIN l_stop_msg := REPLACE(MGMT_BLACKOUT_ENGINE.BLK_STOP_MSG, '{0}', l_blackout_name); l_stop_msg_params := l_blackout_name ; INSERT INTO mgmt_violations (target_guid, policy_guid, collection_timestamp, violation_level, message, message_nlsid, message_params) VALUES (:new.target_guid, response_metric_guid, :new.collection_timestamp, MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, l_stop_msg, MGMT_BLACKOUT_ENGINE.BLK_STOP_MSG_NLSID, l_stop_msg_params ); EXCEPTION WHEN MGMT_GLOBAL.duplicate_record THEN :new.collection_timestamp := MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity( :new.blackout_guid, :new.target_guid, response_metric_guid, MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, MGMT_BLACKOUT_ENGINE.BO_END_MSG, MGMT_BLACKOUT_ENGINE.BO_END_MSG_NLSID, NULL ); WHEN MGMT_GLOBAL.internal_severity THEN :new.collection_timestamp := MGMT_BLACKOUT_ENGINE.reinsert_blackout_severity( :new.blackout_guid, :new.target_guid, response_metric_guid, MGMT_GLOBAL.G_SEVERITY_BLACKOUT_END, MGMT_BLACKOUT_ENGINE.BO_END_MSG, MGMT_BLACKOUT_ENGINE.BO_END_MSG_NLSID, NULL ); END; -- Select a new master agent if this is a multi-agent target SELECT monitoring_mode INTO l_monitoring_mode FROM mgmt_targets WHERE target_guid = :new.target_guid; IF (l_monitoring_mode = mgmt_global.G_MON_MODE_OMS_MEDIATED) THEN em_master_agent.select_master_agent(:new.target_guid, :new.collection_timestamp); END IF; -- For services (i.e. webapps), discard the state of its beacons BEGIN MGMT_GENSVC_AVAIL.PROCESS_BLACKOUT_END(:new.target_guid, :new.collection_timestamp); EXCEPTION WHEN OTHERS THEN MGMT_BLACKOUT_ENGINE.log_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Error calling gensvc.process_blackout_end() for blackout ' || :new.blackout_guid || ' on target ' || :new.target_guid); END; ELSE NULL; END IF; ELSE NULL; END IF; END; / show errors; CREATE OR REPLACE TRIGGER blackout_status AFTER UPDATE ON MGMT_BLACKOUT_FLAT_TARGETS FOR EACH ROW DECLARE l_created_thru MGMT_BLACKOUTS.created_thru%TYPE; l_occurrence_number MGMT_BLACKOUTS.occurrence_number%TYPE; l_status MGMT_BLACKOUTS.blackout_status%TYPE; BEGIN SELECT created_thru, occurrence_number INTO l_created_thru, l_occurrence_number FROM MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid; IF l_created_thru IS NULL THEN -- Compute the new status IF :new.job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_START_PROCESSING THEN l_status := MGMT_BLACKOUT.BLK_STATE_START_PENDING; ELSIF :new.job_status=MGMT_BLACKOUT_ENGINE.BLK_JSTATE_STOPPED THEN l_status := MGMT_BLACKOUT.BLK_STATE_ENDED; ELSE l_status := MGMT_BLACKOUT.BLK_STATE_STARTED; END IF; -- Update entries in the blackout window table. We only -- need to update "active" entries, ones that are -- not in state stopped or ended. UPDATE MGMT_BLACKOUT_WINDOWS SET status=l_status WHERE blackout_guid=:new.blackout_guid AND occurrence_number=l_occurrence_number AND target_guid=:new.target_guid AND status NOT IN (MGMT_BLACKOUT.BLK_STATE_STOPPED, MGMT_BLACKOUT.BLK_STATE_ENDED); END IF; END; / show errors; CREATE OR REPLACE TRIGGER insert_flat_targets AFTER INSERT ON MGMT_BLACKOUT_TARGET_DETAILS FOR EACH ROW DECLARE l_created_thru MGMT_BLACKOUTS.created_thru%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; BEGIN -- For CLI blackouts, add entries to the flat targets table SELECT created_thru INTO l_created_thru FROM MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid; IF l_created_thru IS NOT NULL THEN -- This is an agent-side blackout INSERT INTO MGMT_BLACKOUT_FLAT_TARGETS(blackout_guid, target_guid, host_blackout, job_status, blackout_status, last_updated_time) VALUES (:new.blackout_guid, :new.target_guid, 0, MGMT_BLACKOUT_ENGINE.BLK_JSTATE_START_PROCESSING, MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT, SYSDATE); --Find the target type to make sure that its of type host SELECT target_type INTO l_target_type FROM MGMT_TARGETS WHERE target_guid = :new.target_guid; --If include_members is true then insert all the dependent targets to the MGMT_BLACKOUT_FLAT_TARGETS IF (:new.include_members = 1 AND l_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN --Find all the targets guid for this url and insert in MGMT_BLACKOUT_FLAT_TARGETS FOR crec IN ( SELECT a.target_guid FROM MGMT_TARGETS a, MGMT_TARGETS h WHERE h.target_guid = :new.target_guid AND h.emd_url=a.emd_url AND a.target_type != MGMT_GLOBAL.G_HOST_TARGET_TYPE ) LOOP INSERT INTO MGMT_BLACKOUT_FLAT_TARGETS ( blackout_guid, target_guid, host_blackout, job_status, blackout_status, last_updated_time ) VALUES ( :new.blackout_guid, crec.target_guid, 0, MGMT_BLACKOUT_ENGINE.BLK_JSTATE_START_PROCESSING, MGMT_BLACKOUT_ENGINE.NOT_IN_BLACKOUT, SYSDATE ); END LOOP; END IF; END IF; END; / show errors; CREATE OR REPLACE TRIGGER update_source BEFORE INSERT ON MGMT_BLACKOUT_SCHEDULE FOR EACH ROW DECLARE l_created_thru MGMT_BLACKOUTS.created_thru%TYPE; BEGIN BEGIN SELECT created_thru INTO l_created_thru FROM MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; IF l_created_thru IS NOT NULL THEN IF :new.duration > 0 THEN :new.duration_source := MGMT_BLACKOUT_ENGINE.DURATION_SOURCE_DURATION; ELSIF :new.duration < 0 THEN :new.duration_source := MGMT_BLACKOUT_ENGINE.DURATION_SOURCE_INDEFINITE; ELSE :new.duration_source := MGMT_BLACKOUT_ENGINE.DURATION_SOURCE_ENDTIME; END IF; END IF; END; / show errors; CREATE OR REPLACE TRIGGER blackout_window_insert BEFORE INSERT OR UPDATE ON MGMT_BLACKOUT_WINDOWS FOR EACH ROW DECLARE l_timezone_region MGMT_TARGETS.timezone_region%TYPE; BEGIN SELECT timezone_region INTO l_timezone_region FROM MGMT_TARGETS WHERE target_guid=:new.target_guid; :new.utc_start_time := MGMT_GLOBAL.to_utc(:new.start_time, l_timezone_region); IF :new.end_time IS NOT NULL THEN :new.utc_end_time := MGMT_GLOBAL.to_utc(:new.end_time, l_timezone_region); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; / show errors; CREATE OR REPLACE TRIGGER handle_related_targets BEFORE INSERT ON MGMT_BLACKOUT_STATE FOR EACH ROW DECLARE l_created_thru MGMT_BLACKOUTS.created_thru%TYPE; l_include_members MGMT_BLACKOUT_TARGET_DETAILS.include_members%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; l_targets_to_process MGMT_USER_GUID_ARRAY; BEGIN -- For CLI blackouts, add entries to the blackout state table SELECT created_thru INTO l_created_thru FROM MGMT_BLACKOUTS WHERE blackout_guid=:new.blackout_guid; IF l_created_thru IS NOT NULL THEN BEGIN SELECT include_members INTO l_include_members FROM MGMT_BLACKOUT_TARGET_DETAILS WHERE blackout_guid=:new.blackout_guid AND target_guid=:new.target_guid; EXCEPTION -- If we get a NO_DATA_FOUND, we are processing a -- node-level blackout, and a target that is on -- the node but is not the host. WHEN NO_DATA_FOUND THEN l_include_members := 0; END; --Find the target type to make sure that its of type host SELECT target_type INTO l_target_type FROM MGMT_TARGETS WHERE target_guid = :new.target_guid; --If include_members is true then insert all the dependent targets to the MGMT_BLACKOUT_STATE IF (l_include_members = 1 AND l_target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE) THEN --Find all the targets guid for this host url and insert in MGMT_BLACKOUT_FLAT_TARGETS SELECT t.target_guid BULK COLLECT INTO l_targets_to_process FROM MGMT_TARGETS t, MGMT_BLACKOUT_FLAT_TARGETS ft WHERE ft.blackout_guid=:new.blackout_guid AND t.target_guid=ft.target_guid AND t.target_guid != :new.target_guid ORDER BY DECODE(t.target_type, MGMT_GLOBAL.G_AGENT_TARGET_TYPE, 2, 1), 1; IF l_targets_to_process IS NOT NULL AND l_targets_to_process.COUNT > 0 THEN FOR i IN 1..l_targets_to_process.COUNT LOOP INSERT INTO MGMT_BLACKOUT_STATE ( blackout_guid, target_guid, collection_timestamp, load_timestamp, blackout_code, target_status ) VALUES ( :new.blackout_guid, l_targets_to_process(i), :new.collection_timestamp, :new.load_timestamp, :new.blackout_code, :new.target_status ); END LOOP; END IF; END IF; END IF; END; / show errors; -- Compute status for all agent-side blackouts on blackout update CREATE OR REPLACE TRIGGER compute_agent_side_status BEFORE UPDATE ON MGMT_BLACKOUTS FOR EACH ROW BEGIN IF :new.created_thru IS NOT NULL THEN :new.blackout_status := MGMT_BLACKOUT_ENGINE.compute_blackout_status(:new.blackout_guid, MGMT_BLACKOUT_ENGINE.BLK_MODE_NONE, :new.last_start_time, :new.last_end_time); END IF; END; / show errors;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de