Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/sdk/sdk_monitoring_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/04/27 22:10:50 bram Exp $ Rem Rem sdk_monitoring_pkgbody.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sdk_monitoring_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem bram 04/24/09 - Backport jsadras_rfi_backport_6686041_10.2.0.5 Rem from st_emcore_10.2.0.1.0 Rem pratagar 07/25/06 - Backport pratagar_bug-4653111 from main Rem pratagar 11/28/05 - Delete UDM and 2 Col SQL UDM Support. Rem rpinnama 09/27/05 - Fix 4637956 : Do not clear severities for Rem multi-column UDMS Rem rpinnama 09/21/05 - Fix 4595139 : Obtain settings lock before Rem updating the settings Rem rpinnama 09/09/05 - Bug 4597429 : Ignore remote metrics while Rem clearing open alerts Rem rpinnama 08/18/05 - Fix 4417130 : Avoid get_metric_guid call in a Rem loop Rem rpinnama 07/27/05 - Fix 4495847 : Decrement CA counter on removing Rem target policy assoc Rem jsadras 07/05/05 - Calling em_severity.clear_alerts Rem rzazueta 06/16/05 - Add new delete_mntr_set_copy Rem rpinnama 06/21/05 - Fix 4361635: Provide get_target_settings API Rem rpinnama 05/09/05 - Re-order the remove* API to avoid deadlocks Rem rpinnama 04/15/05 - Remove policy eval details also. Rem rpinnama 04/07/05 - Delete composite keys on deleting monitoring Rem setting copy Rem rpinnama 04/05/05 - Clear violations for disabled/removed Rem metrics/policies Rem rpinnama 12/02/04 - Use object type constants Rem rpinnama 11/22/04 - Support transposed metrics Rem rpinnama 11/18/04 - Add delete_config API Rem groyal 10/27/04 - Add corrective action support to Rem save_target_policy_config Rem groyal 10/25/04 - Add save_target_policy_config Rem rpinnama 10/21/04 - Rem rpinnama 10/10/04 - Change template to mntr Rem rzazueta 09/27/04 - Propagate settings to standby agents of Rem multi-agent target Rem rpinnama 10/01/04 - Lock CAs before calling reset and delete Rem rpinnama 09/30/04 - Rem rpinnama 09/27/04 - Use collection name Rem rpinnama 08/26/04 - Add save_settings API Rem rpinnama 07/26/04 - rpinnama_add_policy_api Rem rpinnama 07/26/04 - Created Rem CREATE OR REPLACE PACKAGE BODY mgmt_monitoring AS TYPE metric_info_array IS TABLE OF EM_METRIC.METRIC_INFO_REC ; TYPE metric_index_array IS TABLE OF NUMBER INDEX BY VARCHAR2(32) ; PROCEDURE get_metric_info_array (p_target_guid IN RAW, p_metric_info_array OUT metric_info_array, p_metric_index_array OUT metric_index_array ) IS l_prev_metric_name mgmt_metrics.metric_name%TYPE ; l_curr_index NUMBER := 0 ; l_met_index NUMBER := 0 ; l_metric_guid mgmt_metrics.metric_guid%TYPE ; BEGIN p_metric_info_array := metric_info_array() ; FOR rec IN ( SELECT m.metric_guid, m.metric_type, m.metric_name, m.num_keys, m.is_repository, m.is_transposed, m.source_type, m.key_order, m.metric_column,m.keys_from_mult_colls FROM MGMT_METRICS m, mgmt_targets t WHERE t.target_guid = p_target_guid AND m.target_type = t.target_type AND m.type_meta_ver = t.type_meta_ver AND (m.category_prop_1 = t.category_prop_1 OR m.category_prop_1 = ' ') AND (m.category_prop_2 = t.category_prop_2 OR m.category_prop_2 = ' ') AND (m.category_prop_3 = t.category_prop_3 OR m.category_prop_3 = ' ') AND (m.category_prop_4 = t.category_prop_4 OR m.category_prop_4 = ' ') AND (m.category_prop_5 = t.category_prop_5 OR m.category_prop_5 = ' ') ORDER BY metric_name,decode(metric_column,' ',1,2), decode(key_order,0,1,0) , key_order, metric_column) LOOP IF rec.metric_name != l_prev_metric_name OR l_prev_metric_name IS NULL THEN p_metric_info_array.extend(1) ; l_curr_index := l_curr_index + 1 ; p_metric_info_array(l_curr_index).key_cols := mgmt_short_string_array() ; p_metric_info_array(l_curr_index).metric_cols := mgmt_namevalue_array() ; END IF ; p_metric_index_array(rawtohex(rec.metric_guid)) := l_curr_index ; IF rec.metric_column = ' ' THEN p_metric_info_array(l_curr_index).metric_name := rec.metric_name ; p_metric_info_array(l_curr_index).metric_guid := rec.metric_guid ; p_metric_info_array(l_curr_index).num_keys := rec.num_keys ; p_metric_info_array(l_curr_index).metric_type := rec.metric_type ; p_metric_info_array(l_curr_index).is_repository := rec.is_repository ; p_metric_info_array(l_curr_index).is_transposed := rec.is_transposed ; p_metric_info_array(l_curr_index).source_type := rec.source_type; p_metric_info_array(l_curr_index).keys_from_mult_colls := rec.keys_from_mult_colls ; ELSE p_metric_info_array(l_curr_index).metric_cols.extend(1) ; l_met_index := p_metric_info_array(l_curr_index).metric_cols.COUNT ; p_metric_info_array(l_curr_index).metric_cols(l_met_index) := mgmt_namevalue_obj.new( rec.metric_column,rec.metric_type) ; IF rec.key_order > 0 THEN p_metric_info_array(l_curr_index).key_cols.extend(1) ; l_met_index := p_metric_info_array(l_curr_index).key_cols.COUNT ; p_metric_info_array(l_curr_index).key_cols(l_met_index) := rec.metric_column ; END IF; END IF ; l_prev_metric_name := rec.metric_name ; END LOOP ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('get_metric_info_array:Count='||l_curr_index,G_MODULE_NAME) ; FOR i IN 1..p_metric_info_array.COUNT LOOP EMDW_LOG.DEBUG('get_metric_info_array:'||i||'=>'|| p_metric_info_array(i).metric_name,G_MODULE_NAME) ; END LOOP ; l_metric_guid := p_metric_index_array.FIRST ; WHILE l_metric_guid IS NOT NULL LOOP EMDW_LOG.DEBUG('get_metric_info_array:'||l_metric_guid||'='|| p_metric_index_array(l_metric_guid),G_MODULE_NAME) ; l_metric_guid := p_metric_index_array.next(l_metric_guid) ; END LOOP ; END IF ; END get_metric_info_array ; PROCEDURE get_target_settings( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_list OUT MGMT_MNTR_METRIC_ARRAY, p_policy_list OUT MGMT_MNTR_POLICY_ARRAY, p_collection_list OUT MGMT_MNTR_COLLECTION_ARRAY) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); EM_TEMPLATE.get_object_settings( p_object_guid => l_target_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_metric_list => p_metric_list, p_policy_list => p_policy_list, p_coll_list => p_collection_list); END get_target_settings; -- Saves the settings for a given target -- Algo: This procedures deletes the existing settings and adds details for the -- new settings. PROCEDURE save_settings ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_list IN MGMT_MNTR_METRIC_ARRAY DEFAULT NULL, p_policy_list IN MGMT_MNTR_POLICY_ARRAY DEFAULT NULL, p_collection_list IN MGMT_MNTR_COLLECTION_ARRAY DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_mntr_metric MGMT_MNTR_METRIC; l_mntr_policy MGMT_MNTR_POLICY; l_mntr_coll MGMT_MNTR_COLLECTION; l_coll_metric MGMT_COLL_METRIC; l_coll_sched MGMT_COLL_SCHEDULE_OBJ; l_coll_prop MGMT_COLL_PROP; l_coll_cred_array MGMT_COLLECTION_CRED_ARRAY; l_cred_rec MGMT_CRED_RECORD; l_operation_guid MGMT_UPDATE_OPERATIONS.operation_guid%TYPE; l_tgt_policy_guids MGMT_TARGET_GUID_ARRAY; l_tgt_policy_names MGMT_MEDIUM_STRING_ARRAY; l_tgt_metric_names MGMT_MEDIUM_STRING_ARRAY; l_tgt_metric_cols MGMT_MEDIUM_STRING_ARRAY; l_tgt_coll_names MGMT_SHORT_STRING_ARRAY; l_tgt_enabled MGMT_INTEGER_ARRAY; l_metric_found INTEGER := 0; l_metric_enabled INTEGER := 0; l_policy_found INTEGER := 0; l_policy_enabled INTEGER := 0; l_proc_name VARCHAR2(32) := 'save_settings'; l_emd_url mgmt_targets.emd_url%TYPE := NULL; l_lock_name mgmt_targets.emd_url%TYPE; l_lock_handle VARCHAR2(256); l_clear_message mgmt_violations.message%type ; l_clear_nlsid mgmt_violations.message_nlsid%type ; l_metric_info_array metric_info_array ; l_metric_index_array metric_index_array ; l_metric_info_rec EM_METRIC.METRIC_INFO_REC ; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); BEGIN -- Get target owner for auditing, emd_url for lock SELECT target_guid,emd_url INTO l_target_guid,l_emd_url FROM MGMT_TARGETS WHERE TARGET_NAME = p_target_name AND TARGET_TYPE = p_target_type; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.target_does_not_exist_err, MGMT_GLOBAL.target_does_not_exist_err_m || ' ' || p_target_name || ':' || p_target_type); END ; -- For group/service targets where EMD_URL is NULL -- or if the target cannot be looked up, use target_guid as lock name l_lock_name := NVL(l_emd_url, l_target_guid); EMD_LOADER.obtain_metric_settings_lock( p_lock_name => l_lock_name, p_timeout_secs => 0, p_release_on_commit => 1, p_lock_handle => l_lock_handle); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Target type = [' || p_target_type || ']' || ' Target Name = [' || p_target_name || ']', G_MODULE_NAME); EMDW_LOG.DEBUG(l_proc_name || ' BEGIN DUMP ------------', G_MODULE_NAME); EM_POLICY.dump_metric_list(p_metric_list, l_proc_name, G_MODULE_NAME); EM_POLICY.dump_policy_list(p_policy_list, l_proc_name, G_MODULE_NAME); EM_POLICY.dump_collection_list(p_collection_list, l_proc_name, G_MODULE_NAME); EMDW_LOG.DEBUG(l_proc_name || ' END DUMP ------------', G_MODULE_NAME); END IF ; -- Process collection list first, as that is the table to which agent uploads first IF (p_collection_list IS NOT NULL) THEN -- TODO: Delete collection_credentials -- MGMT_CREDENTIAL.delete_(obj)_collection_credentials ?? EM_COLL_UTIL.remove_object_collections(l_target_guid, MGMT_GLOBAL.G_OBJECT_TYPE_TARGET); IF (p_collection_list.COUNT > 0) THEN EM_COLL_UTIL.add_object_collections( p_target_type => p_target_type, p_object_guid => l_target_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_list => p_collection_list); END IF; -- (p_collection_list.COUNT > 0) END IF; -- collection_list is NOT NULL -- Lock all CAs associated with this target MGMT_JOB_ENGINE.lock_cas_for_object(l_target_guid, MGMT_CA.CA_SCOPE_TARGET); -- Reset all CA counters.. MGMT_JOB_ENGINE.reset_ca_refctr(l_target_guid, MGMT_CA.CA_SCOPE_TARGET); -- Process metric_list IF (p_metric_list IS NOT NULL) THEN -- Get the list of existing non-remote metrics and their enable status -- Collection name is required to handle UDMs -- Ignore remote metrics. SELECT pa.policy_guid, m.metric_name, m.metric_column, pa.coll_name, pa.is_enabled BULK COLLECT INTO l_tgt_policy_guids, l_tgt_metric_names, l_tgt_metric_cols, l_tgt_coll_names, l_tgt_enabled FROM mgmt_policy_assoc pa, (SELECT DISTINCT metric_guid, target_type, metric_name, metric_column FROM mgmt_metrics WHERE target_type = p_target_type AND NVL(remote, 0) = 0) m WHERE pa.object_guid = l_target_guid AND pa.policy_guid = m.metric_guid AND pa.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND pa.policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC ORDER BY pa.policy_guid, pa.coll_name; -- Remove the existing metric list IF (l_tgt_policy_guids.COUNT > 0) THEN -- Remove target-metric associations, and do not decrement CA counters EM_POLICY.remove_object_policy_assocs( p_object_guid => l_target_guid, p_policy_guids => l_tgt_policy_guids, p_coll_name => NULL, p_remove_ca_assoc => MGMT_GLOBAL.G_FALSE); FOR met_ctr IN l_tgt_policy_guids.FIRST..l_tgt_policy_guids.LAST LOOP -- Verify if the metric is present in the new list also, -- If not, clear all violations associated with the metric with message, -- "Metric association removed" message -- If exists and is disabled, clear all violations associated with the metric with message -- "Metric disabled for this target" message. l_metric_found := 0; l_metric_enabled := 0; IF (p_metric_list.COUNT > 0) THEN FOR new_ctr IN p_metric_list.FIRST..p_metric_list.LAST LOOP l_mntr_metric := p_metric_list(new_ctr); IF ( (l_tgt_metric_names(met_ctr) = l_mntr_metric.metric_name) AND (l_tgt_metric_cols(met_ctr) = l_mntr_metric.metric_column) AND (l_tgt_coll_names(met_ctr) = l_mntr_metric.coll_name) ) THEN l_metric_found := 1; l_metric_enabled := l_mntr_metric.is_enabled; END IF; END LOOP; END IF; -- (p_metric_list.COUNT > 0) l_clear_message := NULL ; IF (l_metric_found = 0) THEN -- Clear all existing violations for the given metric, with -- "Metric removed from the target" message l_clear_message := EM_SEVERITY.G_CLR_METRIC_REM_MSG ; l_clear_nlsid := EM_SEVERITY.G_CLR_METRIC_REM_NLSID ; ELSIF ( (l_metric_enabled = 0) AND (l_tgt_enabled(met_ctr) = 1) ) THEN -- Clear all existing violations for the given metric -- "Metric disabled from the target" message l_clear_message := EM_SEVERITY.G_CLR_METRIC_DISABLED_MSG ; l_clear_nlsid := EM_SEVERITY.G_CLR_METRIC_DISABLED_NLSID ; END IF; IF l_clear_message IS NOT NULL THEN EM_SEVERITY.clear_alerts (p_target_guid=>l_target_guid, p_policy_guid=>l_tgt_policy_guids(met_ctr), p_cfg_coll_name=>l_tgt_coll_names(met_ctr), p_is_metric => TRUE, p_clear_message => l_clear_message, p_clear_nlsid => l_clear_nlsid ) ; END IF ; END LOOP; END IF; -- Associate the new list IF (p_metric_list.COUNT > 0) THEN -- get all the metric information in one shot get_metric_info_array(l_target_guid,l_metric_info_array,l_metric_index_array) ; FOR met_ctr IN p_metric_list.FIRST..p_metric_list.LAST LOOP l_mntr_metric := p_metric_list(met_ctr); l_metric_guid := MGMT_METRIC.get_metric_guid(p_target_type, l_mntr_metric.metric_name, l_mntr_metric.metric_column); BEGIN -- sometimes we get invalid metric guid, in the sense, the metric was -- applicable to the target, now it is not no longer applicable. -- The add object policy assoc code ignores it l_metric_info_rec := l_metric_info_array(l_metric_index_array(l_metric_guid)) ; EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_info_rec := NULL ; END ; EM_POLICY.add_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_metric_guid, p_coll_name => l_mntr_metric.coll_name, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_policy_type => MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, p_is_enabled => l_mntr_metric.is_enabled, p_policy_val_list => l_mntr_metric.key_val_list, p_add_or_delete => l_mntr_metric.add_or_delete, p_metric_info => l_metric_info_rec) ; END LOOP; END IF; -- (p_metric_list.COUNT > 0) END IF; -- Process policy_list IF (p_policy_list IS NOT NULL) THEN -- Get the list of existing metrics and their enable status -- Collection name is required to handle UDMs SELECT pa.policy_guid, p.policy_name, pa.coll_name, pa.is_enabled BULK COLLECT INTO l_tgt_policy_guids, l_tgt_policy_names, l_tgt_coll_names, l_tgt_enabled FROM mgmt_policy_assoc pa, mgmt_policies p WHERE pa.object_guid = l_target_guid AND pa.policy_guid = p.policy_guid AND pa.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND pa.policy_type = MGMT_GLOBAL.G_TYPE_POLICY ORDER BY pa.policy_guid, pa.coll_name; -- Remove the existing metric list IF (l_tgt_policy_guids.COUNT > 0) THEN FOR met_ctr IN l_tgt_policy_guids.FIRST..l_tgt_policy_guids.LAST LOOP -- Remove target-policy associations, and do not decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_tgt_policy_guids(met_ctr), p_coll_name => NULL, p_remove_ca_assoc => MGMT_GLOBAL.G_FALSE); -- Verify if the metric is present in the new list also, -- If not, clear all violations associated with the metric with message, -- "Metric association removed" message -- If exists and is disabled, clear all violations associated with the metric with message -- "Metric disabled for this target" message. l_policy_found := 0; l_policy_enabled := 0; IF (p_policy_list.COUNT > 0) THEN FOR new_ctr IN p_policy_list.FIRST..p_policy_list.LAST LOOP l_mntr_policy := p_policy_list(new_ctr); -- l_policy_guid := MGMT_POLICY.get_policy_guid(p_target_type, -- l_mntr_policy.policy_name); IF ( (l_tgt_policy_names(met_ctr) = l_mntr_policy.policy_name) AND (l_tgt_coll_names(met_ctr) = l_mntr_policy.coll_name) ) THEN l_policy_found := 1; l_policy_enabled := l_mntr_policy.is_enabled; END IF; END LOOP; END IF; -- (p_policy_list.COUNT > 0) l_clear_message := NULL ; IF (l_policy_found = 0) THEN -- Clear all existing violations for the given metric, with -- "Metric removed from the target" message l_clear_message := EM_SEVERITY.G_CLR_POLICY_REM_MSG ; l_clear_nlsid := EM_SEVERITY.G_CLR_POLICY_REM_NLSID ; ELSIF ( (l_policy_enabled = 0) AND (l_tgt_enabled(met_ctr) = 1) ) THEN -- Clear all existing violations for the given metric -- "Metric disabled from the target" message l_clear_message := EM_SEVERITY.G_CLR_POLICY_DISABLED_MSG ; l_clear_nlsid := EM_SEVERITY.G_CLR_POLICY_DISABLED_NLSID ; END IF; IF l_clear_message IS NOT NULL THEN EM_SEVERITY.clear_alerts (p_target_guid=>l_target_guid, p_policy_guid=>l_tgt_policy_guids(met_ctr), p_cfg_coll_name=>l_tgt_coll_names(met_ctr), p_is_metric => FALSE, p_clear_message => l_clear_message, p_clear_nlsid => l_clear_nlsid ) ; END IF ; END LOOP; END IF; IF (p_policy_list.COUNT > 0) THEN FOR pol_ctr IN p_policy_list.FIRST..p_policy_list.LAST LOOP l_mntr_policy := p_policy_list(pol_ctr); l_policy_guid := MGMT_POLICY.get_policy_guid(p_target_type, l_mntr_policy.policy_name); EM_POLICY.add_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_policy_guid, p_coll_name => l_mntr_policy.coll_name, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_policy_type => MGMT_GLOBAL.G_TYPE_POLICY, p_is_enabled => l_mntr_policy.is_enabled, p_policy_val_list => l_mntr_policy.key_val_list); END LOOP; END IF; -- (p_policy_list.COUNT > 0) END IF; -- Delete all zero-reference corrective actions MGMT_JOB_ENGINE.delete_noref_cas( p_object_guid => l_target_guid, p_ca_scope => MGMT_CA.CA_SCOPE_TARGET); -- If target is a multi-agent target, propagate settings to standby agents IF EMD_LOADER.is_multi_agent_target(p_target_name, p_target_type) = 1 THEN l_operation_guid := MGMT_TARGET_UPDATE.update_monitoring_settings(p_target_name, p_target_type, p_metric_list, p_collection_list); END IF; END save_settings; PROCEDURE SAVE_TARGET_METRIC_CONFIG ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_coll_name IN VARCHAR2, p_is_enabled IN NUMBER DEFAULT 1, p_policy_val_list IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_ca_id_list MGMT_TARGET_GUID_ARRAY; l_proc_name VARCHAR2(32) := 'save_target_metric_config'; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_metric_name, 'p_metric_name'); l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_metric_guid := MGMT_METRIC.get_metric_guid_for_target( p_target_type => p_target_type, p_target_name => p_target_name, p_metric_name => p_metric_name, p_metric_column => p_metric_column); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Target type = [' || p_target_type || ']' || ' Target Name = [' || p_target_name || ']' || ' Metric Name = [' || p_metric_name || ']' || ' Metric Column = [' || p_metric_column || ']' || ' Coll Name = [' || p_coll_name || ']' || ' Enabled = [' || p_is_enabled || ']', G_MODULE_NAME); EMDW_LOG.DEBUG(l_proc_name || ' BEGIN DUMP ------------', G_MODULE_NAME); EM_POLICY.dump_key_val_list(p_policy_val_list, l_proc_name, G_MODULE_NAME); EMDW_LOG.DEBUG(l_proc_name || ' END DUMP ------------', G_MODULE_NAME); END IF ; -- Lock all CAs associated with this target MGMT_JOB_ENGINE.lock_cas_for_object(l_target_guid, MGMT_CA.CA_SCOPE_TARGET); l_ca_id_list := EM_POLICY.get_ca_id_list( p_object_guid => l_target_guid, p_policy_guid => l_metric_guid, p_coll_name => p_coll_name ); -- Remove target-metric associations, and do not decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_metric_guid, p_coll_name => p_coll_name, p_remove_ca_assoc => MGMT_GLOBAL.G_FALSE); EM_POLICY.add_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_metric_guid, p_coll_name => p_coll_name, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_policy_type => MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, p_is_enabled => p_is_enabled, p_policy_val_list => p_policy_val_list); -- Decrement the CA ids ref counter. Zero counters are automatically deleted EM_POLICY.decrement_ca_ids(l_ca_id_list); END save_target_metric_config; PROCEDURE delete_target_metric_config ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_coll_name IN VARCHAR2) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_proc_name VARCHAR2(32) := 'delete_target_metric_config'; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_metric_name, 'p_metric_name'); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Target type = [' || p_target_type || ']' || ' Target Name = [' || p_target_name || ']' || ' Metric Name = [' || p_metric_name || ']' || ' Metric Column = [' || p_metric_column || ']' || ' Coll Name = [' || p_coll_name || ']', G_MODULE_NAME); END IF; l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_metric_guid := MGMT_METRIC.get_metric_guid_for_target( p_target_type => p_target_type, p_target_name => p_target_name, p_metric_name => p_metric_name, p_metric_column => p_metric_column); -- Remove target-metric associations, and decrement ctr for any CA assoc. EM_POLICY.remove_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_metric_guid, p_coll_name => p_coll_name, p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); EM_SEVERITY.clear_alerts (p_target_guid=>l_target_guid, p_policy_guid=>l_metric_guid, p_cfg_coll_name=>p_coll_name, p_is_metric => TRUE, p_clear_message =>EM_SEVERITY.G_CLR_METRIC_REM_MSG, p_clear_nlsid =>EM_SEVERITY.G_CLR_METRIC_REM_NLSID ) ; END delete_target_metric_config; PROCEDURE save_target_policy_config ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_policy_name IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT ' ', p_is_enabled IN NUMBER DEFAULT 1, p_policy_val_list IN MGMT_POLICY_KEY_VAL_ARRAY DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_ca_id_list MGMT_TARGET_GUID_ARRAY; l_proc_name VARCHAR2(32) := 'save_target_policy_config'; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Target type = [' || p_target_type || ']' || ' Target Name = [' || p_target_name || ']' || ' Policy Name = [' || p_policy_name || ']' || ' Coll Name = [' || p_coll_name || ']' || ' Enabled = [' || p_is_enabled || ']', G_MODULE_NAME); EMDW_LOG.DEBUG(l_proc_name || ' BEGIN DUMP ------------', G_MODULE_NAME); EM_POLICY.dump_key_val_list(p_policy_val_list, l_proc_name, G_MODULE_NAME); EMDW_LOG.DEBUG(l_proc_name || ' END DUMP ------------', G_MODULE_NAME); END IF ; l_target_guid := MGMT_TARGET.get_target_guid(p_target_name, p_target_type); l_policy_guid := MGMT_POLICY.get_policy_guid(p_target_type, p_policy_name); -- Lock all CAs associated with this target MGMT_JOB_ENGINE.lock_cas_for_object(l_target_guid, MGMT_CA.CA_SCOPE_TARGET); l_ca_id_list := EM_POLICY.get_ca_id_list( p_object_guid => l_target_guid, p_policy_guid => l_policy_guid ); -- Remove target-metric associations, and do not decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_policy_guid, p_coll_name => NULL, p_remove_ca_assoc => MGMT_GLOBAL.G_FALSE); EM_POLICY.add_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_policy_guid, p_coll_name => p_coll_name, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_policy_type => MGMT_GLOBAL.G_TYPE_POLICY, p_is_enabled => p_is_enabled, p_policy_val_list => p_policy_val_list); -- Decrement the CA ids ref counter. Zero counters are automatically deleted EM_POLICY.decrement_ca_ids(l_ca_id_list); END save_target_policy_config; PROCEDURE delete_target_policy_config ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_policy_name IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT ' ') IS l_target_guid mgmt_targets.target_guid%TYPE; l_policy_guid mgmt_policies.policy_guid%TYPE; l_proc_name VARCHAR2(32) := 'delete_target_policy_config'; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_policy_name, 'p_policy_name'); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG(l_proc_name || ' Target type = [' || p_target_type || ']' || ' Target Name = [' || p_target_name || ']' || ' Policy Name = [' || p_policy_name || ']' || ' Coll Name = [' || p_coll_name || ']', G_MODULE_NAME); END IF ; l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_policy_guid := MGMT_POLICY.get_policy_guid(p_target_type, p_policy_name); -- Remove target-metric associations, and decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => l_target_guid, p_policy_guid => l_policy_guid, p_coll_name => p_coll_name, p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); EM_SEVERITY.clear_alerts (p_target_guid=>l_target_guid, p_policy_guid=>l_policy_guid, p_cfg_coll_name=>p_coll_name, p_is_metric => FALSE, p_clear_message => EM_SEVERITY.G_CLR_POLICY_REM_MSG, p_clear_nlsid => EM_SEVERITY.G_CLR_POLICY_REM_NLSID ) ; END delete_target_policy_config; -- Update the thresholds for a given target, metric, key configuration PROCEDURE SET_THRESHOLD( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2, p_coll_name IN VARCHAR2 DEFAULT ' ', p_key_value IN VARCHAR2 DEFAULT ' ', p_key_operator IN NUMBER DEFAULT 0, p_crit_threshold IN VARCHAR2 DEFAULT NULL, p_warn_threshold IN VARCHAR2 DEFAULT NULL, p_info_threshold IN VARCHAR2 DEFAULT NULL) IS l_target_guid mgmt_targets.target_guid%TYPE; l_metric_guid mgmt_metrics.metric_guid%TYPE; l_assoc_cnt NUMBER; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_metric_name, 'p_metric_name'); l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_metric_guid := MGMT_METRIC.get_metric_guid_for_target( p_target_type => p_target_type, p_target_name => p_target_name, p_metric_name => p_metric_name, p_metric_column => p_metric_column); -- TODO : Check for the existance of the assoc before updating it SELECT COUNT(1) INTO l_assoc_cnt FROM mgmt_policy_assoc_cfg WHERE object_guid = l_target_guid AND policy_guid = l_metric_guid AND coll_name = p_coll_name AND key_value = p_key_value AND key_operator = p_key_operator; IF (l_assoc_cnt <= 0) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Cannot set threshold for a non-existant target, metric, key combination.'); END IF; EM_POLICY.update_policy_assoc_cfg_param( p_object_guid => l_target_guid, p_policy_guid => l_metric_guid, p_coll_name => p_coll_name, p_key_value => p_key_value, p_key_operator => p_key_operator, p_param_name => ' ', p_crit_threshold => p_crit_threshold, p_warn_threshold => p_warn_threshold, p_info_threshold => p_info_threshold); END set_threshold; -- Generate monitoring set guid FUNCTION generate_mntr_set_copy_guid ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_copy_req_guid IN RAW) RETURN RAW IS l_mntr_set_copy_guid mgmt_mntr_set_copies.mntr_set_copy_guid%TYPE; BEGIN l_mntr_set_copy_guid := DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw('ora$monitoring_copy' || ';' || p_target_type || ';'|| p_target_name || ';'|| p_copy_req_guid)); RETURN l_mntr_set_copy_guid; END generate_mntr_set_copy_guid; FUNCTION get_mntr_set_copy_guid ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_copy_req_guid IN RAW) RETURN RAW IS l_target_guid mgmt_targets.target_guid%TYPE; l_mntr_set_copy_guid mgmt_mntr_set_copies.mntr_set_copy_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); BEGIN SELECT mntr_set_copy_guid INTO l_mntr_set_copy_guid FROM mgmt_mntr_set_copies WHERE target_guid = l_target_guid AND copy_req_guid = p_copy_req_guid; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.TEMPLATE_COPY_NOT_FOUND_ERR, 'Monitoring Setting Copy not found. target_type = ' || p_target_type || ' target_name = ' || p_target_name || ' copy req guid = ' || p_copy_req_guid ); END; RETURN l_mntr_set_copy_guid; END get_mntr_set_copy_guid; -- Get monitoring set copy monitoring settings PROCEDURE get_mntr_set_copy_settings( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_copy_req_guid IN RAW, p_metric_list OUT MGMT_MNTR_METRIC_ARRAY, p_policy_list OUT MGMT_MNTR_POLICY_ARRAY, p_collection_list OUT MGMT_MNTR_COLLECTION_ARRAY) IS l_mntr_set_copy_guid mgmt_mntr_set_copies.mntr_set_copy_guid%TYPE; BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_target_type, 'p_target_type'); EM_CHECK.check_not_null(p_target_name, 'p_target_name'); EM_CHECK.check_not_null(p_copy_req_guid, 'p_copy_req_guid'); l_mntr_set_copy_guid := MGMT_MONITORING.get_mntr_set_copy_guid( p_target_type => p_target_type, p_target_name => p_target_name, p_copy_req_guid => p_copy_req_guid); EM_TEMPLATE.get_object_settings( p_object_guid => l_mntr_set_copy_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_MNTR_SET_COPY, p_metric_list => p_metric_list, p_policy_list => p_policy_list, p_coll_list => p_collection_list); END get_mntr_set_copy_settings; -- Only metric and collection arrays are required. -- Policy array is not required as policies are not sent to agent FUNCTION create_mntr_set_copy ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_copy_req_guid IN RAW, p_metric_list IN MGMT_MNTR_METRIC_ARRAY DEFAULT NULL, p_collection_list IN MGMT_MNTR_COLLECTION_ARRAY DEFAULT NULL) RETURN RAW IS l_mntr_set_copy_guid mgmt_mntr_set_copies.mntr_set_copy_guid%TYPE; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_current_user mgmt_created_users.user_name%TYPE; l_mntr_set_metric MGMT_MNTR_METRIC; l_metric_guid MGMT_METRICS.metric_guid%TYPE; BEGIN l_current_user := MGMT_USER.GET_CURRENT_EM_USER; l_target_guid := MGMT_TARGET.get_target_guid( target_type_in => p_target_type, target_name_in => p_target_name); l_mntr_set_copy_guid := generate_mntr_set_copy_guid( p_target_type, p_target_name, p_copy_req_guid); INSERT INTO mgmt_mntr_set_copies (mntr_set_copy_guid, target_guid, copy_req_guid, created_date, created_by) VALUES (l_mntr_set_copy_guid, l_target_guid, p_copy_req_guid, SYSDATE, l_current_user); -- Add collections EM_COLL_UTIL.add_object_collections( p_target_type => p_target_type, p_object_guid => l_mntr_set_copy_guid, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_MNTR_SET_COPY, p_coll_list => p_collection_list); -- Process metric_list IF ( (p_metric_list IS NOT NULL) AND (p_metric_list.COUNT > 0) )THEN FOR met_ctr IN p_metric_list.FIRST..p_metric_list.LAST LOOP l_mntr_set_metric := p_metric_list(met_ctr); l_metric_guid := MGMT_METRIC.get_metric_guid(p_target_type, l_mntr_set_metric.metric_name, l_mntr_set_metric.metric_column); EM_POLICY.add_object_policy_assoc( p_object_guid => l_mntr_set_copy_guid, p_policy_guid => l_metric_guid, p_coll_name => l_mntr_set_metric.coll_name, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_MNTR_SET_COPY, p_policy_type => MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC, p_is_enabled => l_mntr_set_metric.is_enabled, p_policy_val_list => l_mntr_set_metric.key_val_list); END LOOP; END IF; RETURN l_mntr_set_copy_guid; END create_mntr_set_copy; -- Delete a given monitoring set copy PROCEDURE delete_mntr_set_copy ( p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_copy_req_guid IN RAW) IS l_mntr_set_copy_guid mgmt_mntr_set_copies.mntr_set_copy_guid%TYPE; BEGIN l_mntr_set_copy_guid := get_mntr_set_copy_guid( p_target_type, p_target_name, p_copy_req_guid); delete_mntr_set_copy(l_mntr_set_copy_guid); END delete_mntr_set_copy; -- Delete a given monitoring set copy PROCEDURE delete_mntr_set_copy ( p_mntr_set_copy_guid IN RAW ) IS BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('delete_mntr_set_copy : Deleting mntr set copy = ' || p_mntr_set_copy_guid, 'MONITORING') ; END IF ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('delete_mntr_set_copy : Deleting collections for mntr set copy = ' || p_mntr_set_copy_guid, 'MONITORING') ; END IF ; EM_COLL_UTIL.remove_all_object_collections(p_mntr_set_copy_guid, MGMT_GLOBAL.G_OBJECT_TYPE_MNTR_SET_COPY); -- Remove template metrics and policies FOR metric_rec IN (SELECT policy_guid FROM mgmt_policy_assoc WHERE object_guid = p_mntr_set_copy_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_MNTR_SET_COPY AND policy_type = MGMT_GLOBAL.G_TYPE_THRESHOLD_METRIC) LOOP -- Remove mntr_set_copy-metric associations, and decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => p_mntr_set_copy_guid, p_policy_guid => metric_rec.policy_guid, p_coll_name => NULL, p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); END LOOP; -- Delete composite keys associated with monitoring set copy DELETE FROM mgmt_metrics_composite_keys WHERE target_guid = p_mntr_set_copy_guid; DELETE FROM mgmt_mntr_set_copies WHERE mntr_set_copy_guid = p_mntr_set_copy_guid; END delete_mntr_set_copy; END mgmt_monitoring; / show errors;