Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/severity/severity_repos_pkgbody.sql /st_emcore_10.2.0.4.3db11.2.0.2/1 2010/04/12 09:08:48 gan Exp $ Rem Rem severity_repos_pkgbody.sql Rem Rem Copyright (c) 2003, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem severity_repos_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem gan 03/31/10 - XbranchMerge gan_bug-5088444 from Rem st_emcore_10.2.0.1.0 Rem gan 09/18/07 - Backport gan_bug-6406357 from main Rem pmodi 08/16/07 - Bug-6339029 : Rmv proc call to avoid context Rem switch Rem pmodi 08/16/07 - Bug-6339029 : Rmv proc call to avoid context Rem switch Rem pmodi 08/28/07 - Backport pmodi_bug-6339029 from main Rem gan 06/14/07 - Backport gan_bug-5218353 from main Rem neearora 04/23/07 - bug 5116622 Rem neearora 05/07/07 - Backport neearora_bug-5116622 from main Rem gan 08/12/05 - update last_load_time Rem gan 08/10/05 - optimize for AND/DOWN, OR/UP Rem scgrover 07/07/05 - add extended sql trace Rem gan 06/09/05 - Performance Improvements - phase I Rem gan 05/20/05 - reduce calculation iteration Rem streddy 04/17/05 - Synchronous evaluation for services Rem streddy 04/14/05 - Handle all member blackout case Rem jsadras 04/01/05 - rowid to urowid Rem gan 04/01/05 - Fix get_metric_guid cal Rem gan 03/30/05 - use table metric guid Rem jsadras 03/28/05 - security fix Rem neearora 03/03/05 - Added entry for emdw_log Rem streddy 01/11/05 - Add sync_eval_target_severities procedure Rem gan 11/19/04 - Add event_metric check for non_rs severities Rem streddy 10/24/04 - Eval-order support Rem streddy 10/11/04 - Timezone support Rem streddy 10/04/04 - Use eval-func from metric_dependency table Rem jsadras 10/05/04 - eval_func Rem streddy 09/08/04 - Fix availability out-of-sync problem Rem rpinnama 09/06/04 - Severity table cutover Rem streddy 08/17/04 - Never disable collections Rem streddy 08/12/04 - Add event_metric conditon Rem streddy 12/01/03 - Add debug message Rem rzazueta 10/29/03 - Use mgmt_global.elapsed_time_msec function Rem streddy 10/14/03 - Ignore duplicate severity records Rem streddy 10/09/03 - Use agent availability for host calculations Rem streddy 09/25/03 - Added type_meta_ver support Rem streddy 04/25/03 - Add better exception handling Rem streddy 04/16/03 - Handle unreachable states correctly Rem streddy 04/09/03 - streddy_composite_target_availability Rem streddy 03/31/03 - Created Rem REM REM TODO list before the release: REM [1] Define an intermediate table where dependency severities are REM stored so that we dont go against mgmt_severity table. We need REM to at least store the timestamps of the dependency severities so REM that we dont need to consult mgmt_severity for most normal case REM [2] Handle case where response/status not defined at repository REM correctly REM [3] Analyze query plans REM [4] Consult membership history to do additional forced evaluations REM CREATE OR REPLACE PACKAGE BODY EM_SEVERITY_REPOS AS -- internal types TYPE t_date_list IS TABLE OF DATE INDEX BY BINARY_INTEGER; TYPE t_guids_list IS TABLE OF MGMT_TARGETS.target_guid%TYPE INDEX BY BINARY_INTEGER; -- -- package level variables -- -- target timezone p_target_tz MGMT_TARGETS.timezone_region%TYPE; -- dependencies timezones p_dep_tz_list SMP_EMD_STRING_ARRAY; -- Need timezone conversion? p_convert_tz BOOLEAN := FALSE; G_ENABLE_OPTIMIZATION_PARAM constant VARCHAR2(64) := 'REPO_SEV_ENABLE_OPT'; p_opt_enabled BOOLEAN := FALSE; APP_ERROR_START NUMBER := 20000; METRIC_DEP_SQL_T_GUID CONSTANT VARCHAR2(4000) := 'SELECT target_guid ' || ' FROM mgmt_metric_dependency ' || ' WHERE can_calculate = 1 ' || ' AND event_metric = 1 ' || ' AND disabled = 0 ' || ' AND rs_metric = 1 ' || ' AND target_guid = :t_guid ' || ' ORDER BY eval_order '; METRIC_DEP_SQL CONSTANT VARCHAR2(4000) := 'SELECT target_guid ' || ' FROM mgmt_metric_dependency ' || ' WHERE can_calculate = 1 ' || ' AND event_metric = 1 ' || ' AND disabled = 0 ' || ' AND rs_metric = 1 ' || ' ORDER BY eval_order '; -- FORWARD DECLARATIONS FOR INTERNAL PROCEDURES -- inserts an error message if necessary PROCEDURE INSERT_EVALPROC_ERROR_MSG(v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2, v_err_msg IN VARCHAR2, v_timestamp IN DATE, v_same_error OUT BOOLEAN); -- Internal procedure to insert UNKNOWN availability records PROCEDURE fill_gaps_as_unknown(v_target_guid IN RAW, v_rs_metric_guid IN RAW, v_rs_key_value IN VARCHAR2, v_start_timestamp IN DATE, v_marker_timestamp IN DATE); PROCEDURE update_last_load_time(v_target_guid IN RAW, v_rs_metric_guid IN RAW, v_last_load_time IN DATE) AS l_repo_metric_only NUMBER; BEGIN IF emdw_log.p_is_debug_set THEN emdw_log.debug('update_last_load_time: Enter ', MODULE); END IF; SELECT repo_metric_only INTO l_repo_metric_only FROM mgmt_metric_dependency WHERE target_guid = v_target_guid AND metric_guid = v_rs_metric_guid AND key_value = ' '; IF l_repo_metric_only = 1 THEN UPDATE mgmt_targets SET last_load_time = v_last_load_time WHERE target_guid = v_target_guid; IF emdw_log.p_is_debug_set THEN emdw_log.debug('Update_last_load_time: set time ' || to_char(v_last_load_time, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('update_last_load_time: Exit ', MODULE); END IF; END update_last_load_time; -- Internal procedure used for negative testing PROCEDURE raise_fake_exception IS fake_exception EXCEPTION; PRAGMA EXCEPTION_INIT(fake_exception, -4031); BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('raise_fake_exception:Entry', MODULE); END IF; raise fake_exception; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('raise_fake_exception:Exit', MODULE); END IF; END; -- Internal procedure to insert severities PROCEDURE insert_severity(v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2, v_collection_timestamp IN DATE, v_sev_code IN NUMBER, v_message IN VARCHAR2 DEFAULT NULL, v_message_nlsid IN VARCHAR2 DEFAULT NULL, v_message_params IN VARCHAR2 DEFAULT NULL) IS BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('insert_severity:Entry v_target_guid = ' || v_target_guid || ' v_metric_guid = ' || v_metric_guid || ' v_key_value = ' || v_key_value || ' v_collection_timestamp = ' || to_char(v_collection_timestamp, 'MM/DD/YYYY HH24:MI:SS') || ' v_sev_code = ' || v_sev_code || ' v_message = ' || v_message || ' v_message_nlsid = ' || v_message_nlsid || ' v_message_params = ' || v_message_params, MODULE); END IF; INSERT INTO mgmt_violations (target_guid, policy_guid, collection_timestamp, violation_level, key_value, message, message_nlsid, message_params ) VALUES (v_target_guid, v_metric_guid, v_collection_timestamp, v_sev_code, v_key_value, v_message, v_message_nlsid, v_message_params); EXCEPTION WHEN MGMT_GLOBAL.DUPLICATE_RECORD OR MGMT_GLOBAL.TARGET_DELETION_IN_PROG OR MGMT_GLOBAL.SEVERITY_IN_BLACKOUT OR DUP_VAL_ON_INDEX THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Ignoring exception = ' || substr(SQLERRM, 1, 200), MODULE); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('insert_severity:Exit', MODULE); END IF; END; -- Internal helper function to check the compatibility of status and severity codes FUNCTION status_sev_out_of_sync(v_status_code IN NUMBER, v_sev_code IN NUMBER) RETURN BOOLEAN IS BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('status_sev_out_of_sync:Entry v_status_code = ' || v_status_code || ' v_sev_code = ' || v_sev_code, MODULE); END IF; -- We don't check for the following severity codes here because they -- should never be returned by the eval funcs -- o mgmt_global.G_SEVERITY_UNREACHABLE_CLEAR -- o mgmt_global.G_SEVERITY_BLACKOUT_END -- o mgmt__global.G_SEVERITY_ERROR_END IF ((v_sev_code = mgmt_global.G_SEVERITY_CLEAR AND v_status_code != mgmt_global.G_STATUS_UP) OR (v_sev_code = mgmt_global.G_SEVERITY_CRITICAL AND v_status_code != mgmt_global.G_STATUS_DOWN) OR (v_sev_code = mgmt_global.G_SEVERITY_UNKNOWN AND v_status_code != mgmt_global.G_STATUS_UNKNOWN) OR (v_sev_code = mgmt_global.G_SEVERITY_UNREACHABLE_START AND v_status_code != mgmt_global.G_STATUS_UNREACHABLE) OR (v_sev_code = mgmt_global.G_SEVERITY_ERROR_START AND v_status_code != mgmt_global.G_STATUS_ERROR)) THEN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('status_sev_out_of_sync:Exit return value = TRUE', MODULE); END IF; RETURN TRUE; END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('status_sev_out_of_sync:Exit return value = FALSE', MODULE); END IF; RETURN FALSE; END status_sev_out_of_sync; FUNCTION get_table_metric_guid(v_metric_guid IN RAW) RETURN RAW AS l_table_metric_guid mgmt_metrics.metric_guid%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('get_table_metric_guid: Enter', MODULE); END IF; select unique m2.metric_guid into l_table_metric_guid from mgmt_metrics m1, mgmt_metrics m2 where m1.metric_guid = v_metric_guid and m1.metric_name = m2.metric_name and m1.target_type = m2.target_type and m1.type_meta_ver = m2.type_meta_ver and m2.metric_column = ' '; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('get_table_metric_guid: Exit', MODULE); END IF; return l_table_metric_guid; END get_table_metric_guid; -- Internal procedure to the set status of a target corresponding to the -- severity code PROCEDURE set_target_status(v_target_guid IN RAW, v_metric_guid IN RAW, v_sev_code IN NUMBER, v_timestamp IN DATE, v_message IN VARCHAR2, v_message_nlsid IN VARCHAR2, v_message_params IN VARCHAR2) IS l_curr_sev_code NUMBER; l_curr_status NUMBER; l_new_status NUMBER; l_error INTEGER; l_avail_rowid UROWID; l_table_metric_guid mgmt_metrics.metric_guid%TYPE; l_same_error BOOLEAN; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('set_target_status:Entry v_target_guid = ' || v_target_guid || ' v_metric_guid = ' || v_metric_guid || ' v_sev_code = ' || v_sev_code || ' v_timestamp = ' || to_char(v_timestamp, 'MM/DD/YYYY HH24:MI:SS') || ' v_message = ' || v_message || ' v_message_nlsid = ' || v_message_nlsid || ' v_message_params = ' || v_message_params, MODULE); END IF; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('In set_target_status ' || v_sev_code, MODULE); END IF; -- Log a message for tracking purposes mgmt_log.log_error(v_module_name_in => MODULE, v_error_code_in => E_SEV_EVAL_PROC_ERR, v_log_level_in => mgmt_global.G_ERROR, v_error_msg_in => '(Ignorable) Manually setting status for ' || v_target_guid); BEGIN -- insert an unknown severity first insert_severity(v_target_guid,v_metric_guid, ' ', v_timestamp, mgmt_global.G_SEVERITY_UNKNOWN); -- insert the actual severity now IF (v_sev_code = mgmt_global.G_SEVERITY_ERROR_START) THEN INSERT_EVALPROC_ERROR_MSG(v_target_guid, v_metric_guid, ' ', v_message, v_timestamp, l_same_error); ELSE insert_severity(v_target_guid,v_metric_guid, ' ', v_timestamp, v_sev_code, v_message, v_message_nlsid, v_message_params); END IF; EXCEPTION WHEN OTHERS THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('set_target_status : got an exception = ' || SUBSTR(SQLERRM, 1, 200), MODULE); END IF; mgmt_log.log_error(v_module_name_in => MODULE, v_error_code_in => E_SEV_EVAL_PROC_ERR, v_log_level_in => mgmt_global.G_ERROR, v_error_msg_in => '(Ignorable) Failed to set status for ' || v_target_guid || ' because of error = ' || SUBSTR(SQLERRM, 1, 1900)); END; -- get current availability status for the target SELECT current_status INTO l_curr_status FROM mgmt_current_availability WHERE target_guid = v_target_guid; -- If we are still out of sync, manually set the availability as a -- manual workaround IF (status_sev_out_of_sync(l_curr_status, v_sev_code)) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Manually setting the status', MODULE); END IF; SELECT rowid INTO l_avail_rowid FROM mgmt_availability WHERE target_guid = v_target_guid AND end_collection_timestamp IS NULL; SELECT DECODE(v_sev_code, mgmt_global.G_SEVERITY_CLEAR, mgmt_global.G_STATUS_UP, mgmt_global.G_SEVERITY_CRITICAL, mgmt_global.G_STATUS_DOWN, mgmt_global.G_SEVERITY_BLACKOUT_START, mgmt_global.G_STATUS_BLACKOUT, mgmt_global.G_SEVERITY_UNREACHABLE_START, mgmt_global.G_STATUS_UNREACHABLE, mgmt_global.G_SEVERITY_ERROR_START, mgmt_global.G_STATUS_ERROR, mgmt_global.G_SEVERITY_UNKNOWN, mgmt_global.G_STATUS_UNKNOWN, mgmt_global.G_STATUS_UNKNOWN) INTO l_new_status FROM DUAL; em_severity.process_simple_avail(l_avail_rowid, v_target_guid, l_new_status,null, v_timestamp, l_error); IF (l_error != em_severity.E_NO_ERROR) THEN -- Log a message for tracking purposes mgmt_log.log_error(v_module_name_in => MODULE, v_error_code_in => E_SEV_EVAL_PROC_ERR, v_log_level_in => mgmt_global.G_ERROR, v_error_msg_in => 'Failed to set status for ' || v_target_guid); END IF; END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('set_target_status:Exit', MODULE); END IF; END set_target_status; -- get an array of time points, for which in the 1 min window -- there are availability change for members. PROCEDURE get_eval_time_points(v_target_guid IN RAW, v_rs_metric_guid IN RAW, v_rs_only IN BOOLEAN, v_start_ts IN DATE, v_max_ts IN DATE, v_timepoints OUT MGMT_DATE_TABLE) AS l_timepoints mgmt_date_table := mgmt_date_table(); l_timepoints_count NUMBER := 0; l_minute_mul NUMBER := 0; l_dep_avail_times mgmt_date_table := mgmt_date_table(); l_start_ts DATE; l_stop_ts DATE; l_start_indx NUMBER := 1; BEGIN IF emdw_log.P_IS_DEBUG_SET THEN emdw_log.debug('get_eval_time_points:Enter. target_guid ' || v_target_guid || ' v_start_ts ' || TO_CHAR(v_start_ts, 'YYYY-MM-DD HH24:MI:SS') || ' v_max_ts ' || TO_CHAR(v_max_ts, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; IF p_convert_tz THEN IF v_rs_only THEN SELECT mgmt_global.adjust_tz(a.start_collection_timestamp, t.timezone_region, p_target_tz) coll_time BULK COLLECT INTO l_dep_avail_times FROM MGMT_METRIC_DEPENDENCY_DETAILS dd, MGMT_AVAILABILITY a, MGMT_TARGETS t WHERE dd.target_guid = v_target_guid AND dd.metric_guid = v_rs_metric_guid AND dd.key_value = ' ' AND dd.dep_target_guid = t.target_guid AND a.target_guid = t.target_guid AND a.start_collection_timestamp > mgmt_global.adjust_tz(v_start_ts, p_target_tz, t.timezone_region) AND a.start_collection_timestamp <= mgmt_global.adjust_tz(v_max_ts, p_target_tz, t.timezone_region) ORDER BY coll_time; ELSE SELECT mgmt_global.adjust_tz(v.collection_timestamp, t.timezone_region, p_target_tz) coll_time BULK COLLECT INTO l_dep_avail_times FROM MGMT_METRIC_DEPENDENCY_DETAILS dd, MGMT_METRIC_DEPENDENCY d, MGMT_VIOLATIONS v, MGMT_TARGETS t WHERE d.target_guid = v_target_guid AND d.event_metric = 1 AND d.target_guid = dd.target_guid AND d.metric_guid = dd.metric_guid AND d.key_value = dd.key_value AND dd.dep_target_guid = v.target_guid AND dd.dep_metric_guid = v.policy_guid AND dd.key_value = v.key_value AND v.target_guid = t.target_guid AND v.collection_timestamp > mgmt_global.adjust_tz(v_start_ts, p_target_tz, t.timezone_region) AND v.collection_timestamp <= mgmt_global.adjust_tz(v_max_ts, p_target_tz, t.timezone_region) ORDER BY coll_time; END IF; ELSE IF v_rs_only THEN SELECT a.start_collection_timestamp coll_time BULK COLLECT INTO l_dep_avail_times FROM MGMT_METRIC_DEPENDENCY_DETAILS dd, MGMT_AVAILABILITY a WHERE dd.target_guid = v_target_guid AND dd.metric_guid = v_rs_metric_guid AND dd.key_value = ' ' AND dd.dep_target_guid = a.target_guid AND a.start_collection_timestamp > v_start_ts AND a.start_collection_timestamp <= v_max_ts ORDER BY coll_time; ELSE SELECT v.collection_timestamp coll_time BULK COLLECT INTO l_dep_avail_times FROM MGMT_METRIC_DEPENDENCY_DETAILS dd, MGMT_METRIC_DEPENDENCY d, MGMT_VIOLATIONS v WHERE d.target_guid = v_target_guid AND d.event_metric = 1 AND d.target_guid = dd.target_guid AND d.metric_guid = dd.metric_guid AND d.key_value = dd.key_value AND dd.dep_target_guid = v.target_guid AND dd.dep_metric_guid = v.policy_guid AND dd.key_value = v.key_value AND v.collection_timestamp > v_start_ts AND v.collection_timestamp <= v_max_ts ORDER BY coll_time; END IF; END IF; -- the time points have been sorted ascending order l_start_ts := v_start_ts; l_stop_ts := v_start_ts; WHILE (l_stop_ts < v_max_ts) LOOP l_stop_ts := l_start_ts + 1/(24 * 60); IF (l_stop_ts > v_max_ts) THEN l_stop_ts := v_max_ts; END IF; FOR indx IN l_start_indx..l_dep_avail_times.COUNT LOOP IF (l_dep_avail_times(indx) > l_start_ts AND l_dep_avail_times(indx) <= l_stop_ts) THEN l_timepoints_count := l_timepoints_count + 1; l_start_indx := indx + 1; l_timepoints.extend; l_timepoints(l_timepoints_count) := l_stop_ts; EXIT; -- exit for loop END IF; END LOOP; l_start_ts := l_stop_ts; END LOOP; IF emdw_log.P_IS_DEBUG_SET THEN emdw_log.debug('get_eval_time_points: the eval time point array ', MODULE); FOR indx in 1..l_timepoints.COUNT LOOP emdw_log.debug(TO_CHAR(l_timepoints(indx), 'YYYY-MM-DD HH24:MI:SS'), MODULE); END LOOP; END IF; v_timepoints := l_timepoints; IF emdw_log.P_IS_DEBUG_SET THEN emdw_log.debug('gen_eval_time_points:exit', MODULE); END IF; END get_eval_time_points; PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_SEVERITY_NAME, p_value); END DBMSJOB_EXTENDED_SQL_TRACE_ON; -- Job that drives the severity evaluation process PROCEDURE EXECUTE_REPOS_SEVERITY_EVAL(p_target_guid RAW DEFAULT NULL) IS l_target_guid RAW(16); l_targets t_guids_list; l_start_timestmp TIMESTAMP := SYSTIMESTAMP; l_opt_enabled mgmt_parameters.parameter_value%TYPE; TYPE C1Type IS REF CURSOR; c_targets C1Type; BEGIN MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_SEVERITY_NAME); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('execute_repos_severity_eval:Entry', MODULE); END IF; DBMS_APPLICATION_INFO.SET_MODULE( module_name => MODULE, action_name => SEVERITY_EVALUATION_ACTION); -- Initialize the counters p_target_cnt := 0; p_severity_cnt := 0; p_error_cnt := 0; -- check if optimization is enabled BEGIN SELECT parameter_value INTO l_opt_enabled FROM mgmt_parameters WHERE parameter_name = G_ENABLE_OPTIMIZATION_PARAM; EXCEPTION WHEN OTHERS THEN l_opt_enabled := '0'; END; IF l_opt_enabled = '1' THEN p_opt_enabled := TRUE; ELSE p_opt_enabled := FALSE; END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('execute_repos_severity_eval: optimization is ' || l_opt_enabled, MODULE); END IF; -- prefetch the targets into an array IF p_target_guid IS NOT NULL THEN OPEN c_targets FOR METRIC_DEP_SQL_T_GUID USING p_target_guid; ELSE OPEN c_targets FOR METRIC_DEP_SQL; END IF; FETCH c_targets BULK COLLECT INTO l_targets; IF (c_targets%ISOPEN) THEN CLOSE c_targets; END IF; -- For each unique target in the dependency table FOR idx in 1..l_targets.COUNT LOOP BEGIN SAVEPOINT LAST_CALC_STATE; -- assign current guid to a local variable for code readability l_target_guid := l_targets(idx); -- lock the dependencies for the target; skip the evaluation -- for this round if we can't get a lock IF (em_rep_metric.lock_target_deps(l_target_guid, TRUE) = FALSE) THEN GOTO next_target; END IF; p_target_cnt := p_target_cnt + 1; evaluate_target_severities(l_target_guid); EXCEPTION WHEN OTHERS THEN ROLLBACK TO LAST_CALC_STATE; p_error_cnt := p_error_cnt + 1; mgmt_log.log_error(v_module_name_in => MODULE, v_error_code_in => E_SEV_EVAL_PROC_ERR, v_log_level_in => mgmt_global.G_ERROR, v_error_msg_in => 'Failed to evaluate severities for ' || l_target_guid || ' because of error = ' || SUBSTR(SQLERRM, 1, 1900)); END; <> -- always commit after each target evaluation COMMIT; END LOOP; -- Log the time taken IF (p_target_cnt > 0) THEN MGMT_LOG.LOG_PERFORMANCE(SEVERITY_EVALUATION_ACTION, MGMT_GLOBAL.ELAPSED_TIME_MSEC(l_start_timestmp, SYSTIMESTAMP), l_start_timestmp, 'Y', TARGETS_PROCESSED_CNT_NAME, p_target_cnt); MGMT_LOG.LOG_PERFORMANCE(SEVERITY_EVALUATION_ACTION, 0, l_start_timestmp, 'N', SEVERITIES_GENERATED_CNT_NAME, p_severity_cnt); MGMT_LOG.LOG_PERFORMANCE(SEVERITY_EVALUATION_ACTION, 0, l_start_timestmp, 'N', ERRORS_ENCOUNTERED_CNT_NAME, p_error_cnt); END IF; DBMS_APPLICATION_INFO.SET_MODULE( module_name => ' ', action_name => ' '); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('execute_repos_severity_eval:Exit', MODULE); END IF; END EXECUTE_REPOS_SEVERITY_EVAL; -- Procedure to synchronously evaluate severities for -- parent targets based on a member target change. This -- is currently used only in SA mode and called from -- em_severity.update_current_availability procedure. -- NOTE: ** Do not alter the transactional semantics of -- the main transaction here. PROCEDURE SYNC_EVAL_PARENT_SEVERITIES(v_memb_target_guid IN RAW) IS l_targets t_guids_list; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('sync_eval_parent_severities:Entry v_memb_target_guid = ' || v_memb_target_guid, MODULE); END IF; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Enter sync_eval_parent_severities ' || v_memb_target_guid, MODULE); END IF; SELECT m.target_guid BULK COLLECT INTO l_targets FROM mgmt_metric_dependency m, mgmt_metric_dependency_details d WHERE can_calculate = 1 AND event_metric = 1 AND disabled = 0 AND rs_metric = 1 AND m.target_guid = d.target_guid AND d.dep_target_guid = v_memb_target_guid ORDER BY m.target_guid; -- For each parent target that is potentially affected FOR idx in 1..l_targets.COUNT LOOP IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Parent-guid = ' || l_targets(idx), MODULE); END IF; sync_eval_target_severities(l_targets(idx)); END LOOP; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Exit sync_eval_parent_severities ' || v_memb_target_guid, MODULE); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('sync_eval_parent_severities:Exit', MODULE); END IF; END SYNC_EVAL_PARENT_SEVERITIES; -- Procedure to synchronously evaluate severities PROCEDURE SYNC_EVAL_TARGET_SEVERITIES(v_target_guid IN RAW) IS l_locked BOOLEAN; l_exists NUMBER(1); BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('sync_eval_target_severities:Entry v_target_guid = ' || v_target_guid, MODULE); END IF; -- BEGIN SELECT 1 INTO l_exists FROM mgmt_metric_dependency WHERE target_guid = v_target_guid AND rs_metric = 1; EXCEPTION WHEN NO_DATA_FOUND THEN -- This is not a composite-target availability -- candidate, don't do anything IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('sync_eval_target_severities:Exit Not a composite-target availability', MODULE); END IF; return; END; -- lock the dependencies for the target l_locked := em_rep_metric.lock_target_deps(v_target_guid, TRUE); evaluate_target_severities(v_target_guid, TRUE); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('sync_eval_target_severities:Exit', MODULE); END IF; EXCEPTION WHEN OTHERS THEN mgmt_log.log_error(v_module_name_in => MODULE, v_error_code_in => E_SEV_EVAL_PROC_ERR, v_log_level_in => mgmt_global.G_ERROR, v_error_msg_in => 'sync_eval_target_severities : ' || ' Failed to evaluate severities for target ' || v_target_guid || ' because of error = ' || SUBSTR(SQLERRM, 1, 1900)); END SYNC_EVAL_TARGET_SEVERITIES; -- get the max marker timestamp for UP dependent if formula is OR, or -- the max timestamp for DOWN dependent for AND formula, -- returned time is in service timezone FUNCTION get_max_optimization_time(v_current_marker IN DATE, v_dep_array IN mgmt_metric_details_array, l_formula IN NUMBER) RETURN DATE AS l_check_status NUMBER; l_max_timestamp DATE; BEGIN IF emdw_log.p_is_debug_set THEN emdw_log.debug('get_max_optimization_time: Enter', MODULE); END IF; IF l_formula = mgmt_global.G_REPO_SEV_OPT_AND THEN l_check_status := mgmt_global.G_STATUS_DOWN; ELSE l_check_status := mgmt_global.G_STATUS_UP; END IF; BEGIN -- Hint is used based on assumption that dep array will not have more then 20 record -- And target/marker table has aprox 20/40 records OR less record compare to -- target/marker table,if not, we may have FTS -- Since oracle has no stats for collections this is the best way to tune this qry SELECT /*+ cardinality(d 20) */ max(mgmt_global.adjust_tz(marker_timestamp, t.timezone_region, p_target_tz)) INTO l_max_timestamp FROM mgmt_availability_marker m, mgmt_targets t, TABLE(CAST(v_dep_array as mgmt_metric_details_array)) d WHERE t.target_guid = m.target_guid AND t.target_guid = d.target_guid AND m.marker_avail_status = l_check_status; EXCEPTION WHEN NO_DATA_FOUND THEN l_max_timestamp := TO_DATE('1990-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'); END; IF l_max_timestamp IS NULL THEN l_max_timestamp := TO_DATE('1990-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS'); END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('get_max_optimization_time: max timestamp ' || TO_CHAR(l_max_timestamp, 'YYYY-MM-DD HH24:MI:SS') || ' current timestamp ' || TO_CHAR(v_current_marker, 'YYYY-MM-DD HH24:MI:SS') , MODULE); END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('get_max_optimization_time: Exit', MODULE); END IF; RETURN l_max_timestamp; END get_max_optimization_time; -- Optimize availability for AND logic and at least one member is down -- or for OR logic and at lease one member is up. Service availability is -- jumped to the event time of the member. Non-rs severity is not calculated -- during the jump period. PROCEDURE optimize_and_or(v_current_marker IN DATE, v_service_guid IN RAW, v_avail_metric_guid IN RAW, v_dep_array mgmt_metric_details_array) AS l_formula NUMBER := mgmt_global.G_REPO_SEV_OPT_NONE; l_sub_indx NUMBER; l_eval_func mgmt_metric_dependency.eval_func%TYPE; l_jump_to_time DATE; l_new_sev_code NUMBER; l_new_avail_status NUMBER; BEGIN IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimize_and_or: Enter', MODULE); END IF; IF NOT P_OPT_ENABLED THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimize_and_or: Optimization is disabled. Exit', MODULE); END IF; RETURN; END IF; IF v_dep_array IS NULL OR v_dep_array.COUNT = 0 THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimie_and_or: Only one dependent, no optimization', MODULE); END IF; RETURN; END IF; BEGIN SELECT opt_code INTO l_formula FROM mgmt_metric_dependency WHERE target_guid = v_service_guid AND metric_guid = v_avail_metric_guid AND key_value = ' '; EXCEPTION WHEN OTHERS THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimie_and_or: No optimization code infor. Exit', MODULE); END IF; RETURN; END; IF l_formula = mgmt_global.G_REPO_SEV_OPT_NONE THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimize_and_or: Formula is NONE ' || ' No optimization ', MODULE); END IF; RETURN; END IF; -- get the max timestamp for UP(OR)/DOWN(AND) dependent l_jump_to_time := get_max_optimization_time(v_current_marker, v_dep_array, l_formula); IF l_jump_to_time <= v_current_marker THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimize_and_or: No place to jump. Exit', MODULE); END IF; RETURN; END IF; evaluate_metric_severity(v_service_guid, v_avail_metric_guid, ' ', l_jump_to_time-1/(24*60), l_jump_to_time, l_new_sev_code, TRUE, 1); -- Fetch the new availability status now. SELECT current_status INTO l_new_avail_status FROM mgmt_current_availability WHERE target_guid = v_service_guid; -- update the target's availability marker unless the new -- state is unreachable state IF (l_new_avail_status <> mgmt_global.G_STATUS_UNREACHABLE) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Moving the marker to = ' || to_char(l_jump_to_time, 'HH24:MI:SS') || ' Status is ' || l_new_avail_status, MODULE); END IF; em_severity.update_availability_marker(v_service_guid, l_jump_to_time, l_new_avail_status); END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('optimize_and_or: Exit', MODULE); END IF; END optimize_and_or; -- -- Calculates severities for a single target -- PROCEDURE EVALUATE_TARGET_SEVERITIES(v_target_guid IN RAW, v_sync_eval IN BOOLEAN DEFAULT FALSE) IS l_rs_metric_guid RAW(16); l_target_type mgmt_targets.target_type%TYPE; l_target_name mgmt_targets.target_name%TYPE; l_broken_reason mgmt_targets.broken_reason%TYPE; l_current_marker DATE; l_curr_avail_status NUMBER; l_curr_avail_ts DATE; l_new_avail_status NUMBER; l_new_sev_code NUMBER; l_dep_array MGMT_METRIC_DETAILS_ARRAY; l_dep_count NUMBER; l_calc_start_ts DATE; l_start_ts DATE; l_end_ts DATE; l_max_ts DATE; l_all_unrch BOOLEAN; l_all_blkout BOOLEAN; l_force_eval NUMBER; l_start_timestmp TIMESTAMP := SYSTIMESTAMP; l_target_tz MGMT_TARGETS.timezone_region%TYPE; l_timepoints MGMT_DATE_TABLE; l_event_metric_count NUMBER; l_rs_only BOOLEAN; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Entry v_target_guid = ' || v_target_guid, MODULE); END IF; IF (NOT v_sync_eval) THEN SAVEPOINT LAST_CALC_STATE; END IF; SELECT target_type, target_name, timezone_region, broken_reason INTO l_target_type, l_target_name, l_target_tz, l_broken_reason FROM mgmt_targets WHERE target_guid = v_target_guid; p_target_tz := EM_CHECK.NOOP(l_target_tz) ; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('******Evaluating severities for ' || l_target_name, MODULE); END IF; -- Figure out if response/status metric is defined as repository metric BEGIN SELECT metric_guid, force_calculate, start_timestamp INTO l_rs_metric_guid, l_force_eval, l_calc_start_ts FROM mgmt_metric_dependency WHERE target_guid = v_target_guid AND rs_metric = 1; EXCEPTION WHEN NO_DATA_FOUND THEN -- We currently do not support evaluating severities for targets whose -- response/status is defined on the agent side IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Response/Status metric not defined for ' || l_target_name || '; disabling severity calculations', MODULE); END IF; -- disable all severity calculations for this target UPDATE mgmt_metric_dependency SET disabled = 1, error_msg = 'Response/Status metric not defined for ' || l_target_name || '; disabling severity calculations' WHERE target_guid = v_target_guid AND disabled = 0; IF (SQL%ROWCOUNT > 0) THEN p_error_cnt := p_error_cnt + 1; mgmt_log.log_error(v_module_name_in => MODULE, v_log_level_in => mgmt_global.G_ERROR, v_error_code_in => E_UNSUPPORTED_ERR, v_error_msg_in => E_SEV_EVAL_PROC_ERR_M || l_target_name); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit NO_DATA_FOUND', MODULE); END IF; RETURN; END; -- we directly insert data into availability tables at few places, -- so we need lock the availability record for the target em_severity.lock_avail_for_tgt(v_target_guid); IF (p_neg_test1_enabled = TRUE) THEN raise_fake_exception; END IF; -- get current availability marker for the target SELECT marker_timestamp INTO l_current_marker FROM mgmt_availability_marker WHERE target_guid = v_target_guid; -- get current availability status for the target SELECT current_status, start_collection_timestamp INTO l_curr_avail_status, l_curr_avail_ts FROM mgmt_current_availability WHERE target_guid = v_target_guid; -- if target is in broken state and loader has set the availability to -- metric error, just extend the timer IF (l_broken_reason <> mgmt_global.G_NOT_BROKEN AND l_curr_avail_status = mgmt_global.G_STATUS_ERROR) THEN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:= target is broken. The code is ' || l_broken_reason || ' . Extend the marker for error state' , MODULE); END IF; em_severity.update_availability_marker(v_target_guid, mgmt_global.sysdate_tzrgn(p_target_tz), mgmt_global.G_STATUS_ERROR); RETURN; END IF; -- if the current status is blackout, then skip the evaluations IF (l_curr_avail_status = mgmt_global.G_STATUS_BLACKOUT) THEN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit l_curr_avail_status = ' || mgmt_global.G_STATUS_BLACKOUT, MODULE); END IF; return; END IF; -- If the current availability timestamp is ahead of marker, then -- do calculations from avail timestamp. This can happen when -- blackout ends IF (l_curr_avail_ts > l_current_marker) THEN l_current_marker := l_curr_avail_ts; END IF; -- Handle the bootstrap case: -- If the start timestamp is greater than the current marker, then -- we need to fill the gap with an UNKNOWN record - this happens when -- members get added and deleted for a composite target. IF (l_calc_start_ts > l_current_marker) THEN l_force_eval := 1; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Adding an UNKNOWN severity as the start timestamp ' || 'is ahead of current availability marker for ' || l_target_name || ' startts = ' || to_char(l_calc_start_ts, 'MM/DD/YYYY HH24:MI') || '; current marker = ' || to_char(l_current_marker, 'MM/DD/YYYY HH24:MI'), MODULE); END IF; -- Add an UNKNOWN record in the availability tables fill_gaps_as_unknown(v_target_guid, l_rs_metric_guid, ' ', l_current_marker, l_calc_start_ts); -- assume we are calculating from the start timestamp l_current_marker := l_calc_start_ts; END IF; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Current status = ' || l_curr_avail_status || '; current marker = ' || to_char(l_current_marker, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; -- get all dependencies - use a future date so that we consider all severities -- that occur in future l_dep_array := get_dependencies(v_target_guid, l_rs_metric_guid, ' ', to_date('01/01/9999', 'MM/DD/YYYY'), false); -- set the state to UNKNOWN if there are no dependencies IF (l_dep_array.COUNT = 0) THEN -- we may have members in future in some corner cases SELECT count(*) INTO l_dep_count FROM mgmt_metric_dependency_details WHERE target_guid = v_target_guid AND metric_guid = l_rs_metric_guid; IF (l_dep_count = 0) THEN IF (l_curr_avail_status <> mgmt_global.G_STATUS_UNKNOWN) THEN insert_severity(v_target_guid, l_rs_metric_guid, ' ', l_current_marker + 1/(24 *60 *60), mgmt_global.G_SEVERITY_UNKNOWN); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit l_dep_count = 0', MODULE); END IF; RETURN; END IF; END IF; -- get the min of all dependency markers calc_dep_avail_marker(l_dep_array, l_current_marker, l_all_unrch, l_all_blkout, l_max_ts); IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Max dependency avail marker = ' || to_char(l_max_ts, 'MM/DD/YYYY HH24:MI:SS'), MODULE); IF (l_all_unrch) THEN EMDW_LOG.debug( 'All members unreachable', MODULE); END IF; IF (l_all_blkout) THEN EMDW_LOG.debug( 'All members in blackout', MODULE); END IF; END IF; -- if all targets are unreachable, then insert a UNREACHABLE_START severity IF (l_all_unrch) THEN IF (l_curr_avail_status <> mgmt_global.G_STATUS_UNREACHABLE) THEN insert_severity(v_target_guid, l_rs_metric_guid,' ', l_max_ts, mgmt_global.G_SEVERITY_UNREACHABLE_START); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit all targets are unreachable', MODULE); END IF; RETURN; END IF; -- if all members in blackout, then make parent's status UNKNOWN IF (l_all_blkout) THEN IF (l_curr_avail_status <> mgmt_global.G_STATUS_UNKNOWN) THEN insert_severity(v_target_guid, l_rs_metric_guid,' ', l_max_ts, mgmt_global.G_SEVERITY_UNKNOWN); ELSE em_severity.update_availability_marker(v_target_guid, l_max_ts, mgmt_global.G_SEVERITY_UNKNOWN); END IF; update_last_load_time(v_target_guid, l_rs_metric_guid, l_max_ts); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit all targets are in blackout', MODULE); END IF; RETURN; END IF; IF l_current_marker >= l_max_ts THEN IF emdw_log.p_is_debug_set THEN emdw_log.debug('Current maker no less than min dep avail marker.' || ' Try optimization. Current marker ' || TO_CHAR(l_current_marker, 'YYYY-MM-DD HH24:MI:SS') || ' l_max_ts ' || TO_CHAR(l_max_ts, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; optimize_and_or(l_current_marker, v_target_guid, l_rs_metric_guid, l_dep_array); RETURN; END IF; -- If we need to compute more than 60 cycles, then only compute -- the last 60 cycles to avoid spinning IF (l_current_marker < l_max_ts - 1/24 ) THEN l_current_marker := l_max_ts - 1/24 ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('Marker timestamp too old - adjusting it' || to_char(l_current_marker, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; END IF; SELECT count(*) INTO l_event_metric_count FROM mgmt_metric_dependency WHERE target_guid = v_target_guid AND event_metric = 1 AND ROWNUM < 3; -- if the target has other event metric, force eval IF l_event_metric_count > 1 THEN l_rs_only := FALSE; ELSE l_rs_only := TRUE; END IF; -- either local or global force eval is 1, or has other event metric -- get an array of time points, for which in the 1 min window before it -- there are availability change for members. get_eval_time_points(v_target_guid, l_rs_metric_guid, l_rs_only, l_current_marker, l_max_ts, l_timepoints); IF l_timepoints.COUNT = 0 THEN l_timepoints.extend; l_timepoints(1) := l_current_marker + 1/(24*60); IF l_timepoints(1) > l_max_ts THEN l_timepoints(1) := l_max_ts; END IF; END IF; FOR indx IN 1..l_timepoints.COUNT LOOP l_end_ts := l_timepoints(indx); l_start_ts := l_end_ts - 1/(24*60); IF (l_start_ts < l_current_marker) THEN l_start_ts := l_current_marker; END IF; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('*** Evaluating metrics between ' || ' l_start_ts = ' || to_char(l_start_ts, 'MM/DD/YYYY HH24:MI:SS') || ' l_end_ts = ' || to_char(l_end_ts, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; BEGIN IF (p_neg_test2_enabled = TRUE) THEN raise_fake_exception; END IF; evaluate_metric_severity(v_target_guid, l_rs_metric_guid, ' ', l_start_ts, l_end_ts, l_new_sev_code, TRUE, 1); EXCEPTION WHEN OTHERS THEN DECLARE l_err_msg VARCHAR2(4000); l_same_error BOOLEAN; BEGIN p_error_cnt := p_error_cnt + 1; l_err_msg := SUBSTR(SQLERRM, 1, 4000); insert_evalproc_error_msg(v_target_guid, l_rs_metric_guid, ' ', l_err_msg, l_end_ts, l_same_error); -- For callback function errors and non-application level errors, -- move the marker to end_ts and return. We return here because -- the chances of getting the same error is very high. IF (SQLCODE = E_CALLBACK_ERR OR -SQLCODE < APP_ERROR_START) THEN em_severity.update_availability_marker(v_target_guid, l_end_ts, mgmt_global.G_STATUS_ERROR); update_last_load_time(v_target_guid, l_rs_metric_guid, l_end_ts); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit ' || ' callback function errors or non-application level error', MODULE); END IF; RETURN; END IF; END; END; -- Fetch the new availability status now. SELECT current_status INTO l_new_avail_status FROM mgmt_current_availability WHERE target_guid = v_target_guid; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('New availability status is ' || l_new_avail_status, MODULE); END IF; -- If the new status is clear, then evaluate non r/s-metric -- severities IF (l_new_avail_status = mgmt_global.G_STATUS_UP) THEN IF (l_curr_avail_status <> mgmt_global.G_STATUS_UP) THEN evaluate_non_rs_severities(v_target_guid, l_rs_metric_guid, l_start_ts, l_end_ts, 1); ELSE evaluate_non_rs_severities(v_target_guid, l_rs_metric_guid, l_start_ts, l_end_ts, 0); END IF; END IF; -- update the target's availability marker unless the new -- state is unreachable state IF (l_new_avail_status <> mgmt_global.G_STATUS_UNREACHABLE) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Moving the marker to = ' || to_char(l_end_ts, 'HH24:MI:SS'), MODULE); END IF; em_severity.update_availability_marker(v_target_guid, l_end_ts, l_new_avail_status); END IF; l_curr_avail_status := l_new_avail_status; -- move the savepoint so that we don't end up redoing the same calculations again IF (NOT v_sync_eval) THEN SAVEPOINT LAST_CALC_STATE; END IF; END LOOP; -- move marker to l_max_ts if the last calculated status is not unreachable -- and marker has not been extended to l_max_ts IF (l_curr_avail_status <> mgmt_global.G_STATUS_UNREACHABLE AND l_end_ts <> l_max_ts) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Moving the marker to = ' || to_char(l_max_ts, 'HH24:MI:SS'), MODULE); END IF; em_severity.update_availability_marker(v_target_guid, l_max_ts, l_curr_avail_status); END IF; -- optimization for AND/DWON and OR/UP optimize_and_or(l_max_ts, v_target_guid, l_rs_metric_guid, l_dep_array); update_last_load_time(v_target_guid, l_rs_metric_guid, l_max_ts); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_target_severities:Exit', MODULE); END IF; END EVALUATE_TARGET_SEVERITIES; -- -- Get dependency metric array for a given metric. This function targets that were -- dependents of the metric at the specified time. -- FUNCTION get_dependencies(v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2, v_timestamp IN DATE, v_fetch_sevs IN BOOLEAN DEFAULT TRUE) RETURN MGMT_METRIC_DETAILS_ARRAY IS CURSOR dep_cursor IS SELECT edep_target_guid, edep_metric_guid, dep_key_value, start_timestamp FROM MGMT_METRIC_DEPENDENCY_DETAILS WHERE target_guid = v_target_guid AND metric_guid = v_metric_guid AND key_value = NVL(v_key_value, ' ') AND start_timestamp <= v_timestamp ORDER BY edep_target_guid; l_dep_array MGMT_METRIC_DETAILS_ARRAY := MGMT_METRIC_DETAILS_ARRAY(); l_index NUMBER; l_sev_code NUMBER; l_avail_status NUMBER; l_dep_tz_ts DATE; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('get_dependencies:Entry v_target_guid = ' || v_target_guid || ' v_metric_guid = ' || v_metric_guid || ' v_key_value = ' || v_key_value || ' v_timestamp = ' || to_char(v_timestamp, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; l_index := 0; p_dep_tz_list := SMP_EMD_STRING_ARRAY(); p_convert_tz := FALSE; FOR dep IN dep_cursor LOOP l_index := l_index + 1; l_dep_array.extend(1); -- get the timezone p_dep_tz_list.extend(1); SELECT timezone_region INTO p_dep_tz_list(l_index) FROM mgmt_targets WHERE target_guid = dep.edep_target_guid; IF (p_target_tz <> p_dep_tz_list(l_index)) THEN l_dep_tz_ts := MGMT_GLOBAL.ADJUST_TZ(v_timestamp, p_target_tz, p_dep_tz_list(l_index)); p_convert_tz := TRUE; ELSE l_dep_tz_ts := v_timestamp; END IF; -- get the availability status SELECT current_status INTO l_avail_status FROM mgmt_availability WHERE target_guid = dep.edep_target_guid AND start_collection_timestamp <= l_dep_tz_ts AND NVL(end_collection_timestamp, l_dep_tz_ts + 1) > l_dep_tz_ts; -- Get the latest severity record for this target IF (v_fetch_sevs) THEN BEGIN SELECT violation_level INTO l_sev_code FROM (SELECT max_cts.violation_level FROM (SELECT violation_level, collection_timestamp, MAX(collection_timestamp) OVER () max_coll FROM MGMT_VIOLATIONS WHERE collection_timestamp > em_severity.DATE_1900_01_01 AND collection_timestamp <= l_dep_tz_ts AND target_guid = dep.edep_target_guid AND policy_guid = dep.edep_metric_guid AND key_value = dep.dep_key_value ) max_cts WHERE max_cts.max_coll = max_cts.collection_timestamp ORDER BY 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) ) WHERE ROWNUM = 1 ; EXCEPTION -- Take care of bootstrap case: there are no severities -- for this metric, treat this as CLEAR WHEN NO_DATA_FOUND THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('get_dependencies: defaulting status ' || ' to CLEAR for ' || dep.edep_target_guid, MODULE); END IF; l_sev_code := mgmt_global.G_SEVERITY_CLEAR; END; ELSE l_sev_code := mgmt_global.G_SEVERITY_UNKNOWN; END IF; l_dep_array(l_index) := MGMT_METRIC_DETAILS(dep.edep_target_guid, dep.edep_metric_guid, dep.dep_key_value, l_sev_code, l_avail_status); IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('get_dependencies: for dependency ' || l_index || ' - avail status/ sev code = ' || l_avail_status || '/' || l_sev_code, MODULE); END IF; END LOOP; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('get_dependencies:Exit', MODULE); END IF; -- return the results return l_dep_array; EXCEPTION -- This will happen if all members are added as dependents at a -- future timestamp WHEN NO_DATA_FOUND THEN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('get_dependencies:Exit all members are added as dependents ' || 'at a future timestamp', MODULE); END IF; return l_dep_array; END get_dependencies; -- -- Get the minimum availability marker of the dependent targets. This is the -- marker till which composite metric severities will be calculated. -- PROCEDURE calc_dep_avail_marker(v_dep_array IN MGMT_METRIC_DETAILS_ARRAY, v_curr_marker IN DATE, v_all_membs_unrch OUT BOOLEAN, v_all_membs_blkout OUT BOOLEAN, v_max_marker OUT DATE) IS l_dep_marker DATE; l_min_marker DATE; l_status NUMBER; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('calc_dep_avail_marker:Entry v_curr_marker = ' || TO_CHAR(v_curr_marker, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; v_all_membs_unrch := TRUE; v_all_membs_blkout := TRUE; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('In calc_dep_avail_marker - count = ' || v_dep_array.count || '; curr marker = ' || to_char(v_curr_marker, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; -- base case : if there is only target, then simply returns its marker IF (v_dep_array.COUNT = 1) THEN SELECT marker_timestamp INTO l_dep_marker FROM mgmt_availability_marker WHERE target_guid = v_dep_array(1).target_guid; IF (v_dep_array(1).avail_status <> mgmt_global.G_STATUS_UNREACHABLE) THEN v_all_membs_unrch := FALSE; END IF; IF (v_dep_array(1).avail_status <> mgmt_global.G_STATUS_BLACKOUT) THEN v_all_membs_blkout := FALSE; END IF; IF (p_target_tz <> p_dep_tz_list(1)) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('timezones different - before conversion = ' || to_char(l_dep_marker, 'MM/DD/YYYY HH24:MI:SS'),MODULE); END IF; l_dep_marker := MGMT_GLOBAL.ADJUST_TZ(l_dep_marker, p_dep_tz_list(1), p_target_tz); IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('timezones different - after conversion = ' || to_char(l_dep_marker, 'MM/DD/YYYY HH24:MI:SS'),MODULE); END IF; END IF; v_max_marker := l_dep_marker; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('calc_dep_avail_marker: single dependency case ' || ' v_max_marker = ' || to_char(v_max_marker, 'MM/DD/YYYY HH24:MI:SS') || ' l_dep_marker = ' || to_char(l_dep_marker, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('calc_dep_avail_marker:Exit only one target' || ' v_max_marker = '|| TO_CHAR(v_max_marker, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; RETURN ; END IF; -- The goal is to get the timestamp till which we can calculate severities for the -- composite target. We return the minimum of the availability markers of the up -- targets. If all targets are unreachable, then we set v_all_membs_unrch to TRUE. FOR i IN 1..v_dep_array.COUNT LOOP IF (v_dep_array(i).avail_status <> mgmt_global.G_STATUS_UNREACHABLE) THEN v_all_membs_unrch := FALSE; -- remove this check if we start moving marker for blacked out targets IF (v_dep_array(i).avail_status <> mgmt_global.G_STATUS_BLACKOUT) THEN v_all_membs_blkout := FALSE; SELECT marker_timestamp INTO l_dep_marker FROM mgmt_availability_marker WHERE target_guid = v_dep_array(i).target_guid; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('calc_dep_avail_marker - ' || i || ': ' || to_char(l_dep_marker,'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; IF (p_target_tz <> p_dep_tz_list(i)) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('timezones different - before conversion = ' || TO_CHAR(l_dep_marker, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; l_dep_marker := MGMT_GLOBAL.ADJUST_TZ(l_dep_marker, p_dep_tz_list(i), p_target_tz); IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('timezones different - after conversion = ' || TO_CHAR(l_dep_marker, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; END IF; -- If we have targets for which the marker is currently moving, -- we cannot leave them behind. IF (v_curr_marker > l_dep_marker) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Marker for ' || i || ' is behind parent current marker - ' || ' postponing parent target computation ', MODULE); END IF; v_max_marker := v_curr_marker; RETURN; END IF; -- update the minimum marker IF (l_min_marker IS NULL) THEN l_min_marker := l_dep_marker; ELSIF (l_min_marker > l_dep_marker) THEN l_min_marker := l_dep_marker; END IF; END IF; -- IF NOT BLACKOUT END IF; -- IF NOT UNREACHABLE END LOOP; -- If we are in all blackout case, then return the MIN marker of the -- dependencies IF (v_all_membs_blkout) THEN SELECT MIN( MGMT_GLOBAL.ADJUST_TZ(marker_timestamp, timezone_region, p_target_tz)) INTO l_min_marker FROM mgmt_availability_marker m, mgmt_targets t, (SELECT target_guid FROM TABLE(CAST(v_dep_array AS MGMT_METRIC_DETAILS_ARRAY)))d WHERE m.target_guid = d.target_guid AND d.target_guid = t.target_guid; END IF; -- If we did not find any participating targets, then return the old marker -- which will result in skipping the entire calculations IF (l_min_marker IS NULL OR l_min_marker < v_curr_marker) THEN v_max_marker := v_curr_marker; ELSE v_max_marker := l_min_marker; END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('calc_dep_avail_marker:Exit v_max_marker = '|| TO_CHAR(v_max_marker, 'YYYY-MM-DD HH24:MI:SS'), MODULE); END IF; END calc_dep_avail_marker; -- -- evaluates the severity of a single metric -- PROCEDURE evaluate_metric_severity(v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2 DEFAULT ' ', v_start_ts IN DATE, v_end_ts IN DATE, v_sev_code OUT NUMBER, v_rs_metric IN BOOLEAN DEFAULT TRUE, v_force_eval IN NUMBER DEFAULT 0) IS l_dep_array MGMT_METRIC_DETAILS_ARRAY; l_sev_count NUMBER; l_curr_sev_code NUMBER; l_curr_timestamp DATE; l_sev_eval_proc VARCHAR2(256); l_message VARCHAR2(4000); l_message_nlsid VARCHAR2(64); l_message_params VARCHAR2(4000); l_curr_status NUMBER; l_table_metric_guid mgmt_metrics.metric_guid%TYPE; l_same_error BOOLEAN; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_metric_severity:Entry ' || ' v_target_guid = ' || v_target_guid || ' v_metric_guid = ' || v_metric_guid || ' v_key_value = ' || v_key_value || ' v_start_ts = ' || TO_CHAR(v_start_ts, 'YYYY-MM-DD HH24:MI:SS') || ' v_end_ts = ' || TO_CHAR(v_end_ts, 'YYYY-MM-DD HH24:MI:SS') || ' v_force_eval = ' || v_force_eval, MODULE); END IF; v_sev_code := NO_NEW_SEVERITY; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('*** Entering evaluate_metric_severity for ' || v_metric_guid, MODULE); END IF; -- If this is not a forced evaluation, then see if there are any -- severities between (v_start_ts, v_end_ts) IF (v_force_eval = 0) THEN IF (p_convert_tz = FALSE) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('No timezone convert case', MODULE); END IF; SELECT count(*) INTO l_sev_count FROM MGMT_METRIC_DEPENDENCY_DETAILS dep, MGMT_VIOLATIONS sev WHERE dep.target_guid = v_target_guid AND dep.metric_guid = v_metric_guid AND dep.key_value = v_key_value AND dep.edep_target_guid = sev.target_guid AND dep.edep_metric_guid = sev.policy_guid AND dep.dep_key_value = sev.key_value AND sev.collection_timestamp BETWEEN v_start_ts AND v_end_ts; ELSE IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Timezone convert case', MODULE); END IF; SELECT count(*) INTO l_sev_count FROM MGMT_METRIC_DEPENDENCY_DETAILS dep, MGMT_VIOLATIONS sev, MGMT_TARGETS t WHERE dep.target_guid = v_target_guid AND dep.metric_guid = v_metric_guid AND dep.key_value = v_key_value AND dep.edep_target_guid = sev.target_guid AND dep.edep_metric_guid = sev.policy_guid AND dep.dep_key_value = sev.key_value AND t.target_guid = dep.edep_target_guid AND sev.collection_timestamp BETWEEN MGMT_GLOBAL.ADJUST_TZ(v_start_ts, p_target_tz, t.timezone_region) AND MGMT_GLOBAL.ADJUST_TZ(v_end_ts, p_target_tz, t.timezone_region); END IF; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Number of severities found = ' ||l_sev_count, MODULE); END IF; -- Nothing to do if there are no severities IF (l_sev_count = 0) THEN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_metric_severity:Exit No severities ' || ' v_sev_code = ' || v_sev_code, MODULE); END IF; return; END IF; END IF; -- get dependency metric array for the metric l_dep_array := get_dependencies(v_target_guid, v_metric_guid, v_key_value, v_end_ts); -- call the sev_eval_proc to get the new severity code SELECT eval_func INTO l_sev_eval_proc FROM mgmt_metric_dependency WHERE target_guid = v_target_guid AND metric_guid = v_metric_guid AND key_value = NVL(v_key_value, ' '); BEGIN EXECUTE IMMEDIATE 'BEGIN ' ||EM_CHECK.qualified_sql_name(l_sev_eval_proc)|| '(:1, :2, :3, :4, :5, :6, :7); END; ' USING IN v_target_guid, IN v_metric_guid, IN l_dep_array, OUT v_sev_code, OUT l_message, OUT l_message_nlsid, OUT l_message_params; EXCEPTION WHEN OTHERS THEN raise_application_error(E_CALLBACK_ERR, E_CALLBACK_ERR_M || SUBSTR(SQLERRM, 1, 1900)); END; -- get the current severity code BEGIN SELECT violation_level, collection_timestamp INTO l_curr_sev_code, l_curr_timestamp FROM (SELECT violation_level, collection_timestamp FROM MGMT_VIOLATIONS, (SELECT max(collection_timestamp) AS max_ts FROM MGMT_VIOLATIONS WHERE target_guid = v_target_guid AND policy_guid = v_metric_guid AND key_value = v_key_value AND collection_timestamp > em_severity.DATE_1900_01_01 AND collection_timestamp <= v_end_ts) TS WHERE target_guid = v_target_guid AND policy_guid = v_metric_guid AND key_value = v_key_value AND collection_timestamp = ts.max_ts ORDER BY 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)) WHERE rownum = 1; EXCEPTION -- boot strap case WHEN NO_DATA_FOUND THEN l_curr_sev_code := NO_OLD_SEVERITY; END; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('curr sev code = ' || l_curr_sev_code || '; new sev code = ' || v_sev_code, MODULE); EMDW_LOG.debug('message = ' || l_message, MODULE); END IF; -- get current availability status for the target SELECT current_status INTO l_curr_status FROM mgmt_current_availability WHERE target_guid = v_target_guid; -- If the new severity code is different from the old severity -- code, or if the new status code is not in sync with the current -- status, then generate a new severity IF ((l_curr_sev_code <> v_sev_code) OR (v_rs_metric AND status_sev_out_of_sync(l_curr_status, v_sev_code))) THEN -- If the previous state was UNREACHABLE, then clear the -- unreachable state. Also, insert an UNKNOWN severity if the -- timestamp of the new severity is newer than the unreachable -- state IF (l_curr_sev_code = mgmt_global.G_SEVERITY_UNREACHABLE_START) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Filling unreachability gaps: ' || to_char(l_curr_timestamp, 'MM/DD/YYYY HH24:MI:SS') || ' - ' || to_char(v_end_ts, 'MM/DD/YYYY HH24:MI:SS'), MODULE); END IF; p_severity_cnt := p_severity_cnt + 1; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Inserting an unreachable_clear severity', MODULE); END IF; insert_severity(v_target_guid, v_metric_guid, v_key_value, l_curr_timestamp, mgmt_global.G_SEVERITY_UNREACHABLE_CLEAR); l_curr_sev_code := mgmt_global.G_SEVERITY_UNREACHABLE_CLEAR; -- mark the whole unreachable window as UNKNOWN because we do not -- go back and reconcile old severities IF (v_end_ts > l_curr_timestamp) THEN fill_gaps_as_unknown(v_target_guid, v_metric_guid, v_key_value, l_curr_timestamp, v_end_ts); l_curr_sev_code := mgmt_global.G_SEVERITY_UNKNOWN; END IF; -- Close the ERROR records if needed ELSIF (l_curr_sev_code = mgmt_global.G_SEVERITY_ERROR_START) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Inserting an error_end severity', MODULE); END IF; p_severity_cnt := p_severity_cnt + 1; -- enter a record in mgmt_metric_errors that clears the severity -- state l_table_metric_guid := get_table_metric_guid(v_metric_guid); INSERT INTO mgmt_metric_errors (target_guid, metric_guid, coll_name, collection_timestamp) VALUES (v_target_guid, l_table_metric_guid, REPOSITORY_COLLECTION, v_end_ts); -- clear any old errors UPDATE mgmt_metric_dependency SET error_msg = NULL WHERE target_guid = v_target_guid AND metric_guid = v_metric_guid AND key_value = ' ' AND error_msg IS NOT NULL; l_curr_sev_code := mgmt_global.G_SEVERITY_ERROR_END; END IF; -- Finally add the new severity IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Inserting a new severity', MODULE); END IF; p_severity_cnt := p_severity_cnt + 1; -- If the new severity is a metric error, then don't insert a severity -- directly IF (l_curr_sev_code <> v_sev_code) THEN IF (v_sev_code = mgmt_global.G_SEVERITY_ERROR_START) THEN INSERT_EVALPROC_ERROR_MSG(v_target_guid, v_metric_guid, v_key_value, l_message, v_end_ts, l_same_error); ELSE insert_severity(v_target_guid, v_metric_guid, v_key_value, v_end_ts, v_sev_code, l_message, l_message_nlsid, l_message_params); END IF; END IF; -- We have seen cases where severity and status are not in sync although proper -- severities are inserted. Do a sanity check to make sure we have proper state -- and correct the availability record if needed IF (v_rs_metric) THEN -- get current availability status for the target SELECT current_status INTO l_curr_status FROM mgmt_current_availability WHERE target_guid = v_target_guid; IF (status_sev_out_of_sync(l_curr_status, v_sev_code)) THEN set_target_status(v_target_guid, v_metric_guid, v_sev_code, v_end_ts, l_message, l_message_nlsid, l_message_params); END IF; END IF; END IF; -- l_curr_sev_code <> v_sev_code IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('*** Leaving evaluate_metric_severity ', MODULE); END IF; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_metric_severity:Exit v_sev_code = ' || v_sev_code, MODULE); END IF; END evaluate_metric_severity; -- -- evaluates all non response/status severities of a target in the -- specified time range PROCEDURE evaluate_non_rs_severities(v_target_guid IN RAW, v_rs_metric_guid IN RAW, v_start_ts IN DATE, v_end_ts IN DATE, v_force_eval IN NUMBER) IS CURSOR metrics IS SELECT metric_guid, key_value FROM mgmt_metric_dependency WHERE target_guid = v_target_guid AND metric_guid <> v_rs_metric_guid AND event_metric = 1 AND disabled = 0 AND can_calculate = 1; l_new_sev_code NUMBER; l_err_msg VARCHAR2(1000); BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_non_rs_severities:Entry ' || ' v_target_guid = ' || v_target_guid || ' v_rs_metric_guid = ' || v_rs_metric_guid || ' v_start_ts = ' || v_start_ts || ' v_end_ts = ' || v_end_ts || ' v_force_eval = ' || v_force_eval, MODULE); END IF; FOR metric IN metrics LOOP BEGIN evaluate_metric_severity(v_target_guid, metric.metric_guid, metric.key_value, v_start_ts, v_end_ts, l_new_sev_code, FALSE, v_force_eval); EXCEPTION WHEN OTHERS THEN DECLARE l_err_msg VARCHAR2(4000); l_same_error BOOLEAN; BEGIN p_error_cnt := p_error_cnt + 1; l_err_msg := SUBSTR(SQLERRM, 1, 4000); insert_evalproc_error_msg(v_target_guid, metric.metric_guid,metric.key_value, l_err_msg, v_end_ts, l_same_error); END; END; END LOOP; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('evaluate_non_rs_severities:Exit', MODULE); END IF; END evaluate_non_rs_severities; -- Inserts an error message PROCEDURE INSERT_EVALPROC_ERROR_MSG(v_target_guid IN RAW, v_metric_guid IN RAW, v_key_value IN VARCHAR2, v_err_msg IN VARCHAR2, v_timestamp IN DATE, v_same_error OUT BOOLEAN) IS l_new_err_msg MGMT_METRIC_ERRORS.metric_error_message%TYPE; l_old_err_msg MGMT_METRIC_DEPENDENCY.error_msg%TYPE; l_metric_name MGMT_METRICS.metric_name%TYPE; l_metric_column MGMT_METRICS.metric_column%TYPE; l_target_name MGMT_TARGETS.target_name%TYPE; l_table_metric_guid mgmt_metrics.metric_guid%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('insert_evalproc_error_msg:Entry ' || ' v_target_guid = ' || v_target_guid || ' v_key_value = ' || v_key_value || ' v_err_msg = ' || v_err_msg || ' v_timestamp = ' || v_timestamp, MODULE); END IF; v_same_error := FALSE; IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('In insert_evalproc_error_msg ' || SUBSTR(v_err_msg, 1, 200), MODULE); END IF; -- Construct a more readable message with additional information to be logged -- in mgmt_metric_errors table SELECT target_name INTO l_target_name FROM mgmt_targets WHERE target_guid = v_target_guid; SELECT metric_name, metric_column INTO l_metric_name, l_metric_column FROM mgmt_metrics WHERE metric_guid = v_metric_guid AND ROWNUM = 1; l_new_err_msg := SUBSTR(E_SEV_EVAL_PROC_ERR_M || 'Target name = ' || l_target_name || ', metric_name = ' || l_metric_name || ', metric_column = ' || l_metric_column || '; Error msg = ' || v_err_msg, 1, 4000); -- instead checking against error_msg in mgmt_metric_dependency table, check the -- metric_error_message in mgmt_current_metric_errors directly l_table_metric_guid := get_table_metric_guid(v_metric_guid); BEGIN SELECT metric_error_message INTO l_old_err_msg FROM mgmt_current_metric_errors WHERE target_guid = v_target_guid AND metric_guid = l_table_metric_guid AND coll_name = REPOSITORY_COLLECTION; EXCEPTION WHEN no_data_found THEN l_old_err_msg := NULL; END; -- if the old message is same as new message, then don't a insert -- new message IF ( l_new_err_msg = l_old_err_msg) THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Old message is same as new message', MODULE); END IF; v_same_error := TRUE; IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('insert_evalproc_error_msg:Exit ' || ' Old message is same as new message', MODULE); END IF; RETURN; END IF; -- Insert the new error in mgmt_metric_dependency for tracking purposes UPDATE mgmt_metric_dependency SET error_msg = l_new_err_msg WHERE target_guid = v_target_guid AND metric_guid = v_metric_guid AND key_value = v_key_value; INSERT INTO mgmt_metric_errors (target_guid, metric_guid, coll_name, collection_timestamp, metric_error_message) VALUES (v_target_guid, l_table_metric_guid, REPOSITORY_COLLECTION, v_timestamp, l_new_err_msg); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('insert_evalproc_error_msg:Exit', MODULE); END IF; EXCEPTION -- we don't expect errors here unless somebody blows away metric definitions -- in the middle of execution; just log an error in case of exceptions. WHEN OTHERS THEN IF (EMDW_LOG.p_is_debug_set) THEN EMDW_LOG.debug('Failed to insert an error : ' || SUBSTR(SQLERRM, 1, 200), MODULE); END IF; mgmt_log.log_error(v_module_name_in => MODULE, v_log_level_in => mgmt_global.G_ERROR, v_error_code_in => E_SEV_EVAL_PROC_ERR, v_error_msg_in => 'Failed to insert an error ' || SUBSTR(SQLERRM, 1, 2000)); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('insert_evalproc_error_msg:Exit Exception when others', MODULE); END IF; END INSERT_EVALPROC_ERROR_MSG; -- Internal procedure to insert UNKNOWN availability records PROCEDURE fill_gaps_as_unknown(v_target_guid IN RAW, v_rs_metric_guid IN RAW, v_rs_key_value IN VARCHAR2, v_start_timestamp IN DATE, v_marker_timestamp IN DATE) IS l_curr_status MGMT_CURRENT_AVAILABILITY.current_status%TYPE; BEGIN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('fill_gaps_as_unknown:Entry ' || ' v_target_guid = ' || v_target_guid || ' v_rs_metric_guid = ' || v_rs_metric_guid || ' v_rs_key_values = ' || v_rs_key_value || ' v_start_timestamp = ' || v_start_timestamp || ' v_marker_timestamp = ' || v_marker_timestamp, MODULE); END IF; -- No need to insert an UNKNOWN record if state got resolved without -- any gaps IF (v_start_timestamp = v_marker_timestamp) THEN IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('fill_gaps_as_unknown:Exit no gaps', MODULE); END IF; RETURN; END IF; -- Get the current status SELECT current_status INTO l_curr_status FROM MGMT_CURRENT_AVAILABILITY WHERE target_guid = v_target_guid; -- insert an UNKNOWN severity to fill the gaps IF (l_curr_status <> mgmt_global.G_STATUS_UNKNOWN) THEN insert_severity(v_target_guid, v_rs_metric_guid, v_rs_key_value, v_start_timestamp, mgmt_global.G_SEVERITY_UNKNOWN); END IF; -- l_curr_status <> mgmt_global.G_STATUS_UNKNOWN -- Extend the marker now em_severity.update_availability_marker(v_target_guid, v_marker_timestamp, mgmt_global.G_STATUS_UNKNOWN); IF (EMDW_LOG.p_is_info_set)THEN EMDW_LOG.info('fill_gaps_as_unknown:Exit', MODULE); END IF; END fill_gaps_as_unknown; END EM_SEVERITY_REPOS; / show errors;