Rem drv: Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_util_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/2 2009/04/27 22:10:50 bram Exp $ Rem Rem collections_util_pkgbody.sql Rem Rem Copyright (c) 2004, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem collections_util_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 jsadras 11/19/08 - remove obsolete code Rem jsadras 08/29/07 - Backport jsadras_bug-6363234 from main Rem jsadras 08/29/07 - Bug:6363234, do not allocate tasks for disabled Rem collections Rem jsadras 08/27/07 - Bug:6363234, clear alerts on collection disable Rem jsadras 07/22/07 - Bug:6156475, snapshot collections processing Rem change Rem jsadras 07/30/07 - Backport jsadras_bug-6156475 from main Rem groyal 07/18/07 - Bug 4863138: Update remove_object_collections to Rem remove repository metric collections associated with UDPS Rem nqureshi 04/18/07 - XbranchMerge kmanicka_pdp5 from main Rem sthiruna 07/28/06 - Copying 10.2.0.3 Config Standard changes from Rem EMCORE_MAIN_LINUX Rem pratagar 01/31/06 - 2 Column SQL UDM Support Rem pratagar 07/25/06 - Backport pratagar_bug-4653111 from main Rem jsadras 02/27/06 - add direct load of metric data Rem jsadras 07/17/06 - Backport jsadras_bug-5002887 from main Rem kmanicka 05/10/06 - implement pdp Rem neearora 03/28/06 - Bug 5108394. Adding capability in loader to Rem delete rows through uploads Rem niramach 01/09/06 - 10.3 Work start:Add p_config_std_guid optional Rem parameter to run_collection to identify Rem configuration standard being evaluated. Rem neearora 07/16/06 - Backport neearora_bug-5108394 from main Rem gan 12/08/05 - Backport gan_bug-4769607 from main Rem jsadras 12/09/05 - Backport jsadras_bug-4702199 from main Rem jsadras 10/27/05 - Bug:4702199,handle_metaver_change_def_coll Rem rpinnama 10/24/05 - Fix 4692595 : Donot clear alerts for common Rem metrics in handle_metaver_change callback Rem rpinnama 09/28/05 - Fix 4637956 : Do not process collections for Rem multi-column UDMs. Rem rpinnama 09/17/05 - Fix 4617189 : Modify rowset handlers to insert Rem first followed by updates Rem rkpandey 09/13/05 - Add pl/sql handlers for rowsets Rem jsadras 08/24/05 - Bug:4543455, Error on resume_collection for Rem agent Rem jsadras 08/18/05 - add catprop change support to default collections Rem jsadras 08/03/05 - Bug:4530907 collection name add to async Rem collections Rem pmodi 08/02/05 - Changing default value Rem jsadras 07/26/05 - add default collections upgrade support Rem pmodi 07/25/05 - Include new param in log_metric_errors Rem jsadras 07/05/05 - clear_open_metric errors, accept error type Rem gsbhatia 07/01/05 - New repmgr header impl Rem jsadras 06/29/05 - Deadlock bug:4428951, get collection lock Rem jsadras 06/20/05 - Bug:4358590, snapshot collections Rem pmodi 06/14/05 - Bug:4408959 -Warning is not an error start Rem rpinnama 05/27/05 - Add copy_object_coll API Rem rpinnama 05/27/05 - Fix 4399189 : Start repo collections while Rem copying to target Rem jsadras 05/02/05 - add is_long_running support Rem jsadras 04/26/05 - make snapshot collection asyncronous, Rem Bug:4328750 Rem rpinnama 05/09/05 - Perform deletes so that it will not result in Rem deadlocks Rem jsadras 04/13/05 - add clear open errors Rem jsadras 03/24/05 - add is_transient Rem jsadras 03/15/05 - Suspend/Resume only at collection level Rem jsadras 03/07/05 - update mgmt_collection.is_enabled on suspend Rem jsadras 03/02/05 - bug:4205233 Rem rpinnama 02/17/05 - Copy schedule_ex also while copying collections Rem jsadras 02/13/05 - Skip policy evaluation on no data for service ( Rem bug:4158855) Rem ramalhot 02/02/05 - changed signature for handle_target_delete Rem jsadras 01/31/05 - metric version support Rem jsadras 12/22/04 - result_key length Rem jsadras 12/15/04 - run_coll_on_all Rem jsadras 12/10/04 - snapshot_store Rem jsadras 11/23/04 - validate_schedule Rem rpinnama 12/02/04 - Use object type constants Rem jsadras 11/10/04 - clear_all_violations Rem jsadras 11/02/04 - task_type Rem jsadras 10/20/04 - add_mod_del_coll_items Rem rpinnama 10/21/04 - Add target deletion callback Rem jsadras 10/06/04 - short_string_table Rem rpinnama 10/15/04 - Insert NULL schedule_ex for repository Rem collections. Rem rpatti 10/22/04 - remove coll properties for all coll for a given Rem metric Rem jsadras 09/29/04 - mgmt_metric_collections_rep Rem jsadras 09/20/04 - type_meta_ver Rem rpinnama 10/01/04 - Add copy collections API Rem rpinnama 10/01/04 - Add add_collection and add_coll_metric_task API Rem jsadras 09/22/04 - format Rem jsadras 09/15/04 - metric_error Rem jsadras 09/09/04 - evaluate_metric.coll_name Rem jsadras 09/07/04 - metric_column Rem shuberma 09/10/04 - Get "store metric" info. Rem shuberma 09/03/04 - Add run snapshot collection utility. Rem rpinnama 09/08/04 - Add metric guid to coll_item properties table Rem jsadras 09/02/04 - timezone resume Rem jsadras 08/18/04 - more features Rem jsadras 08/17/04 - jsadras_repocollect2 Rem jsadras 08/10/04 - Created Rem CREATE OR REPLACE PACKAGE BODY EM_COLL_UTIL IS G_CUSTOM_ERR_START CONSTANT NUMBER := -20999 ; G_CUSTOM_ERR_END CONSTANT NUMBER := -20001 ; -- -- Internal package variable to stop the code in collections trigger -- from executing -- G_RUN_DISABLE_STEPS NUMBER(1) := MGMT_GLOBAL.G_TRUE ; -- Forward declarations PROCEDURE delete_metric_collection_int (p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_clear_alerts IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) ; -- Forward declarations end -- -- validate if the target metric combination is right -- PROCEDURE validate_target_metric(p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2, p_metric_column IN VARCHAR2 := ' ', p_target_guid OUT RAW, p_type_meta_ver OUT VARCHAR2, p_timezone_region OUT VARCHAR2, p_metric_guid OUT RAW, p_source_type OUT NUMBER, p_is_repository OUT NUMBER, p_cat_prop_array OUT mgmt_short_string_array ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('validate_target_metric:Enter',G_MODULE_NAME) ; END IF ; p_cat_prop_array := mgmt_short_string_array() ; p_cat_prop_array.extend(5) ; IF p_metric_name IS NULL OR p_target_name IS NULL OR p_target_type IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Target Type/Target Name/Metric name cannot be null') ; END IF ; SELECT targets.target_guid, targets.type_meta_ver, targets.timezone_region, metrics.metric_guid, metrics.source_type , metrics.is_repository, metrics.category_prop_1, metrics.category_prop_2, metrics.category_prop_3, metrics.category_prop_4, metrics.category_prop_5 INTO p_target_guid, p_type_meta_ver, p_timezone_region, p_metric_guid, p_source_type, p_is_repository, p_cat_prop_array(1), p_cat_prop_array(2), p_cat_prop_array(3), p_cat_prop_array(4), p_cat_prop_array(5) FROM mgmt_targets targets, mgmt_metrics metrics WHERE targets.target_name = p_target_name AND targets.target_type = p_target_type AND metrics.target_type = p_target_type AND metrics.metric_name = p_metric_name AND metrics.metric_column = p_metric_column AND metrics.type_meta_ver = targets.type_meta_ver AND (metrics.category_prop_1 = ' ' OR metrics.category_prop_1 = targets.category_prop_1) AND (metrics.category_prop_2 = ' ' OR metrics.category_prop_2 = targets.category_prop_2) AND (metrics.category_prop_3 = ' ' OR metrics.category_prop_3 = targets.category_prop_3) AND (metrics.category_prop_4 = ' ' OR metrics.category_prop_4 = targets.category_prop_4) AND (metrics.category_prop_5 = ' ' OR metrics.category_prop_5 = targets.category_prop_5) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('validate_target_metric:Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid Target='||p_target_name||' or Metric='|| p_metric_name||' (validate_target_metric)') ; WHEN TOO_MANY_ROWS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'More than 1 matching metric Metric='||p_metric_name|| ' (validate_target_metric)') ; WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'ORA'||sqlcode|| ' when fetching target/metric information (validate_target_metric)') ; END validate_target_metric; -- -- validate the collection schedule -- PROCEDURE validate_schedule(p_coll_schedule IN mgmt_coll_schedule_obj) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('validate_schedule:Enter ',G_MODULE_NAME) ; END IF ; IF p_coll_schedule.frequency_code IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Frequency code cannot be null') ; END IF ; IF p_coll_schedule.frequency_code = MGMT_GLOBAL.G_SCHED_FREQUENCY_ONDEMAND THEN RETURN ; END IF ; IF p_coll_schedule.frequency_code=MGMT_GLOBAL.G_SCHED_FREQUENCY_INTERVAL AND NVL(p_coll_schedule.interval,-1) < 0 THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Invalid value for interval: ' || p_coll_schedule.interval); END IF; IF p_coll_schedule.end_time IS NOT NULL THEN IF p_coll_schedule.end_time < p_coll_schedule.start_time THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'End date is less than start date') ; END IF ; END IF ; IF p_coll_schedule.frequency_code IN (MGMT_GLOBAL.G_SCHED_FREQUENCY_DAILY, MGMT_GLOBAL.G_SCHED_FREQUENCY_WEEKLY, MGMT_GLOBAL.G_SCHED_FREQUENCY_MONTHLY, MGMT_GLOBAL.G_SCHED_FREQUENCY_YEARLY) THEN IF p_coll_schedule.execution_hours < 0 OR p_coll_schedule.execution_hours > 23 OR p_coll_schedule.execution_hours IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'execution_hours must be between 0 and 23'); END IF ; IF p_coll_schedule.execution_minutes < 0 OR p_coll_schedule.execution_minutes > 59 OR p_coll_schedule.execution_minutes IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'execution_minutes must be between 0 and 59'); END IF; IF p_coll_schedule.frequency_code = MGMT_GLOBAL.G_SCHED_FREQUENCY_WEEKLY THEN IF p_coll_schedule.days IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Days array cannot be empty for weekly schedule') ; END IF ; FOR i IN 1..p_coll_schedule.days.count LOOP IF p_coll_schedule.days(i) < 1 OR p_coll_schedule.days(i) > 7 THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Invalid value (' || p_coll_schedule.days(i) || ') for day of week, must be between 1 and 7'); END IF; END LOOP; ELSIF p_coll_schedule.frequency_code IN (MGMT_GLOBAL.G_SCHED_FREQUENCY_MONTHLY, MGMT_GLOBAL.G_SCHED_FREQUENCY_YEARLY) THEN IF p_coll_schedule.days IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Days array cannot be empty for monthly/yearly schedules') ; END IF ; FOR i IN 1..p_coll_schedule.days.count LOOP IF p_coll_schedule.days(i) != -1 AND (p_coll_schedule.days(i) < 1 OR p_coll_schedule.days(i) > 31) THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Invalid value (' || p_coll_schedule.days(i) || ') for day of month, must be between 1 and 31'); END IF; END LOOP; IF p_coll_schedule.frequency_code = MGMT_GLOBAL.G_SCHED_FREQUENCY_YEARLY THEN IF p_coll_schedule.months IS NULL OR p_coll_schedule.months.count=0 THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'The months array cannot be empty for day-of-year schedules'); ELSIF p_coll_schedule.months.count != p_coll_schedule.days.count THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'The days and months arrays must be of equal size for day-of-year schedules'); END IF; FOR i IN 1..p_coll_schedule.months.count LOOP IF p_coll_schedule.months(i) < 1 OR p_coll_schedule.months(i) > 12 THEN raise_application_error(MGMT_GLOBAL.INVALID_SCHEDULE_ERR, 'Invalid value (' || p_coll_schedule.months(i) || ') for month in day-of-year schedule'); END IF; END LOOP; END IF ; -- year_freqeuncy_code END IF ; -- month/year freqeuncy code END IF ; -- day/month/week/year IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('validate_schedule:Exit normal',G_MODULE_NAME) ; END IF ; END validate_schedule; -- -- Get the current schedule of the collection item -- in collection schedule obj and lock the collection -- FUNCTION lock_and_get_schedule (p_target_guid IN RAW, p_coll_name IN VARCHAR2, p_timezone_region IN VARCHAR2 := NULL ) RETURN mgmt_coll_schedule_obj IS l_coll_schedule mgmt_coll_schedule_obj ; BEGIN SELECT mgmt_coll_schedule_obj.new (frequency_code, execution_hours, execution_minutes , interval , months , days , mgmt_global.sysdate_tzrgn(NVL(p_timezone_region, EM_TASK.G_UTC_TIMEZONE)), null) INTO l_coll_schedule FROM mgmt_collections coll WHERE object_guid = p_target_guid AND coll_name = p_coll_name AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET FOR UPDATE ; RETURN(l_coll_schedule) ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Collection ('||p_coll_name||' does not exist (lock_and_get_schedule)'); END lock_and_get_schedule ; -- -- Get only the records for which the key value matches -- delete all other records -- PROCEDURE trim_result_records(p_metric_values IN OUT NOCOPY mgmt_metric_value_array, p_result_key_array IN mgmt_medium_string_table, p_key_value IN VARCHAR2) IS l_current_rec NUMBER ; BEGIN IF p_metric_values IS NOT NULL THEN l_current_rec := p_metric_values.FIRST ; END IF ; WHILE l_current_rec IS NOT NULL LOOP IF p_result_key_array(l_current_rec) != p_key_value THEN p_metric_values.delete(l_current_rec) ; END IF ; l_current_rec := p_metric_values.next(l_current_rec) ; END LOOP ; END trim_result_records ; -- -- Validate the schedule -- FUNCTION collection_name(p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2) RETURN VARCHAR2 IS l_coll_name mgmt_collections.coll_name%type ; BEGIN SELECT coll_name INTO l_coll_name FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = nvl(p_coll_name,coll_name) ; RETURN(l_coll_name) ; EXCEPTION WHEN NO_DATA_FOUND THEN IF p_coll_name IS NULL THEN RETURN(' ') ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid collection name') ; END IF ; WHEN TOO_MANY_ROWS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'More than 1 collection for the metric, specify collection name'); END collection_name; -- -- modify collections schedule or store_metric or enable -- PROCEDURE modify_collection_entry (p_object_guid IN RAW, p_object_type IN NUMBER, p_coll_name IN VARCHAR2, p_schedule_ex IN VARCHAR2 DEFAULT NULL, p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_store_metric IN NUMBER DEFAULT NULL, p_is_enabled IN NUMBER DEFAULT NULL, p_from_trigger IN BOOLEAN := FALSE, p_is_transient IN NUMBER DEFAULT NULL) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_collection_entry:Enter',G_MODULE_NAME) ; END IF ; IF p_coll_schedule IS NULL THEN UPDATE mgmt_collections SET is_enabled = nvl(p_is_enabled,is_enabled), store_metric = nvl(p_store_metric,store_metric), schedule_ex = nvl(p_schedule_ex,schedule_ex), is_transient = NVL(p_is_transient,is_transient) WHERE object_guid = p_object_guid AND object_type = p_object_type AND coll_name = p_coll_name ; ELSE UPDATE mgmt_collections SET is_enabled = nvl(p_is_enabled,is_enabled), store_metric = nvl(p_store_metric,store_metric), is_transient = NVL(p_is_transient,is_transient), frequency_code=p_coll_schedule.frequency_code, start_time=p_coll_schedule.start_time , end_time=p_coll_schedule.end_time, execution_hours=p_coll_schedule.execution_hours, execution_minutes=p_coll_schedule.execution_minutes , interval=p_coll_schedule.interval , days=p_coll_schedule.days , months=p_coll_schedule.months , schedule_ex = nvl(p_schedule_ex,schedule_ex) WHERE object_guid = p_object_guid AND object_type = p_object_type AND coll_name = p_coll_name ; END IF ; IF SQL%NOTFOUND AND NOT p_from_trigger THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Collection to be modified ('||p_coll_name||') does not exist') ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('modify_collection_entry:Exit normal',G_MODULE_NAME) ; END IF ; END modify_collection_entry; -- -- add a entry to mgmt_collections -- PROCEDURE add_collection_entry (p_object_guid IN RAW, p_object_type IN NUMBER, p_coll_name IN VARCHAR2, p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_is_repository IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_transient IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_collection_entry:Enter',G_MODULE_NAME) ; END IF ; IF p_is_repository =MGMT_GLOBAL.G_TRUE OR p_coll_schedule IS NOT NULL THEN INSERT INTO MGMT_COLLECTIONS (object_guid,coll_name,object_type,is_enabled, store_metric, frequency_code,start_time,end_time,execution_hours,execution_minutes, interval,months,days,schedule_ex,is_transient) VALUES (p_object_guid,p_coll_name,p_object_type,p_is_enabled, p_store_metric, p_coll_schedule.frequency_code, p_coll_schedule.start_time, p_coll_schedule.end_time, p_coll_schedule.execution_hours, p_coll_schedule.execution_minutes, p_coll_schedule.interval, p_coll_schedule.months, p_coll_schedule.days, p_schedule_ex, p_is_transient) ; ELSE INSERT INTO MGMT_COLLECTIONS (object_guid,coll_name,object_type,is_enabled, store_metric, frequency_code,schedule_ex,is_transient) VALUES (p_object_guid,p_coll_name,p_object_type,p_is_enabled, p_store_metric,MGMT_GLOBAL.G_SCHED_FREQUENCY_INTERVAL,p_schedule_ex, p_is_transient) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_collection_entry:Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- A collection can contain more than one metric -- If a metric belonging to a collection has its schedule changed -- It affects the whole collection. That is why we modify on dup-val-on-index modify_collection_entry(p_object_guid=>p_object_guid, p_object_type=>p_object_type, p_coll_name=>p_coll_name, p_is_enabled=>p_is_enabled, p_store_metric=>p_store_metric, p_schedule_ex=>p_schedule_ex, p_coll_schedule=>p_coll_schedule ) ; WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('add_collection_entry:Exit exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE RAISE_APPLICATION_ERROR(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||'(add_collection_entry)') ; END IF ; END add_collection_entry; PROCEDURE add_collection_rec ( p_object_guid IN RAW, p_object_type IN NUMBER, p_coll_name IN VARCHAR2, p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_upload_frequency IN NUMBER DEFAULT 0, p_is_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_is_transient IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS l_coll_sched MGMT_COLL_SCHEDULE_OBJ; l_coll_sched_ex MGMT_COLLECTIONS.schedule_ex%TYPE; BEGIN l_coll_sched_ex := NVL(p_schedule_ex, ' '); IF (l_coll_sched_ex = ' ') THEN IF (p_schedule IS NULL) THEN -- 15 was the default schedule previously l_coll_sched := mgmt_coll_schedule_obj.interval_schedule(15, NULL, NULL); ELSE l_coll_sched := p_schedule; END IF; INSERT INTO MGMT_COLLECTIONS (object_guid, coll_name, object_type, is_enabled, store_metric, upload_frequency, schedule_ex, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days, is_transient) VALUES (p_object_guid, p_coll_name, p_object_type, p_is_enabled, p_store_metric, p_upload_frequency, l_coll_sched_ex, l_coll_sched.frequency_code, l_coll_sched.start_time, l_coll_sched.end_time, l_coll_sched.execution_hours, l_coll_sched.execution_minutes, l_coll_sched.interval, l_coll_sched.months, l_coll_sched.days, p_is_transient) ; ELSE INSERT INTO MGMT_COLLECTIONS (object_guid, coll_name, object_type, is_enabled, store_metric, upload_frequency, frequency_code, schedule_ex, is_transient) VALUES (p_object_guid, p_coll_name, p_object_type, p_is_enabled, p_store_metric, p_upload_frequency, MGMT_GLOBAL.G_SCHED_FREQUENCY_INTERVAL, p_schedule_ex, p_is_transient) ; END IF; END add_collection_rec; -- -- delete from mgmt_collections -- PROCEDURE delete_collection_entry(p_object_guid IN RAW, p_object_type IN NUMBER, p_coll_name IN VARCHAR2, p_from_trigger IN BOOLEAN := FALSE) IS l_count NUMBER ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_collection_entry:Enter',G_MODULE_NAME) ; END IF ; IF p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET THEN SELECT count(*) INTO l_count FROM mgmt_collection_metric_tasks WHERE target_guid = p_object_guid AND coll_name = p_coll_name ; IF l_count > 0 THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Metrics exist in the collection, cannot delete') ; END IF ; END IF ; DELETE mgmt_collections WHERE object_guid = p_object_guid AND object_type = p_object_type AND coll_name = p_coll_name ; IF SQL%NOTFOUND AND NOT p_from_trigger THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Collection to be deleted ('||p_coll_name||') does not exist') ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_collection_entry:Exit Normal',G_MODULE_NAME) ; END IF ; END delete_collection_entry; -- Allocate task for the target/metric/schedule -- If a bulk metric then it searches for matching task -- if not found it creates a task -- If not a bulk metric, it creates a new task -- FUNCTION allocate_task(p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_cat_prop_array IN mgmt_short_string_array, p_coll_schedule IN mgmt_coll_schedule_obj, p_source_type IN NUMBER, p_timezone_region IN VARCHAR2 := NULL ) RETURN NUMBER IS l_task_id mgmt_collection_tasks.task_id%type := 0 ; l_type_meta_ver mgmt_targets.type_meta_ver%type := 0 ; l_target_type mgmt_targets.target_type%type := 0 ; l_task_class mgmt_metrics.is_long_running%type ; CURSOR matching_tasks_cur(p_cat_prop_array IN mgmt_short_string_array, p_type_meta_ver IN VARCHAR2) IS SELECT task.days,task.months,task.task_id FROM mgmt_collection_tasks task, mgmt_targets targets, mgmt_collection_metric_tasks task_target WHERE task_target.metric_guid = p_metric_guid AND task_target.target_guid != p_target_guid AND task_target.coll_name = p_coll_name AND targets.target_guid = task_target.target_guid AND (task.timezone_region = nvl(targets.timezone_region, task.timezone_region) OR -- need not compare timezone for on-demand collection task.frequency_code = MGMT_GLOBAL.G_SCHED_FREQUENCY_ONDEMAND ) AND (p_cat_prop_array(1) = ' ' OR targets.category_prop_1 = p_cat_prop_array(1) )AND (p_cat_prop_array(2) = ' ' OR targets.category_prop_2 = p_cat_prop_array(2) )AND (p_cat_prop_array(3) = ' ' OR targets.category_prop_3 = p_cat_prop_array(3) )AND (p_cat_prop_array(4) = ' ' OR targets.category_prop_4 = p_cat_prop_array(4) )AND (p_cat_prop_array(5) = ' ' OR targets.category_prop_5 = p_cat_prop_array(5) ) AND task.task_id = task_target.task_id AND task.frequency_code = p_coll_schedule.frequency_code AND targets.type_meta_ver = p_type_meta_ver AND nvl(task.interval,0) = nvl(p_coll_schedule.interval,0) AND nvl(task.execution_hours,0) = nvl(p_coll_schedule.execution_hours,0) AND nvl(task.execution_minutes,0) = nvl(p_coll_schedule.execution_minutes,0) ORDER BY task_id ; FUNCTION array_match(array1 IN mgmt_integer_array, array2 IN mgmt_integer_array) RETURN BOOLEAN IS BEGIN IF array1 IS NULL OR array2 IS NULL OR array1.count != array2.count THEN RETURN FALSE ; ELSE FOR i IN array1.FIRST..array1.LAST LOOP IF array1(i) != array2(i) THEN RETURN FALSE ; END IF ; END LOOP ; RETURN TRUE ; END IF ; EXCEPTION WHEN OTHERS THEN RETURN FALSE ; END ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('allocate_task:Enter',G_MODULE_NAME) ; END IF ; SELECT type_meta_ver,target_type INTO l_type_meta_ver,l_target_type FROM mgmt_targets WHERE target_guid = p_target_guid ; IF p_source_type IN ( MGMT_GLOBAL.G_METRIC_SOURCE_QUERY, MGMT_GLOBAL.G_METRIC_SOURCE_BULK_PLSQL) THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('allocate_task: Finding matching tasks',G_MODULE_NAME) ; END IF ; FOR rec in matching_tasks_cur(p_cat_prop_array,l_type_meta_ver) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('allocate_task:Evaluating task:'||rec.task_id, G_MODULE_NAME); END IF ; IF p_coll_schedule.frequency_code IN (MGMT_GLOBAL.G_SCHED_FREQUENCY_INTERVAL, MGMT_GLOBAL.G_SCHED_FREQUENCY_DAILY, MGMT_GLOBAL.G_SCHED_FREQUENCY_ONDEMAND) THEN l_task_id := rec.task_id ; EXIT ; ELSIF p_coll_schedule.frequency_code = MGMT_GLOBAL.G_SCHED_FREQUENCY_WEEKLY AND array_match(rec.days,p_coll_schedule.days) THEN l_task_id := rec.task_id ; EXIT ; ELSIF p_coll_schedule.frequency_code != MGMT_GLOBAL.G_SCHED_FREQUENCY_YEARLY AND array_match(rec.days,p_coll_schedule.days) AND array_match(rec.months,p_coll_schedule.months) THEN l_task_id := rec.task_id ; EXIT ; END IF ; END LOOP ; END IF ; IF l_task_id = 0 THEN SELECT NVL(is_long_running,EM_TASK.G_TASK_CLASS_SHORT_RUNNING) INTO l_task_class FROM mgmt_metrics m, mgmt_targets t WHERE t.target_type = m.target_type AND t.type_meta_ver = m.type_meta_ver AND ( (t.category_prop_1 = m.category_prop_1) OR (m.category_prop_1 = ' ') ) AND ( (t.category_prop_2 = m.category_prop_2) OR (m.category_prop_2 = ' ') ) AND ( (t.category_prop_3 = m.category_prop_3) OR (m.category_prop_3 = ' ') ) AND ( (t.category_prop_4 = m.category_prop_4) OR (m.category_prop_4 = ' ') ) AND ( (t.category_prop_5 = m.category_prop_5) OR (m.category_prop_5 = ' ') ) AND t.target_guid = p_target_guid AND m.metric_guid = p_metric_guid; l_task_id := EM_TASK.create_task (p_coll_schedule=>p_coll_schedule, p_task_type=>EM_TASK.G_TASK_TYPE_REPO, p_task_class=>l_task_class, p_timezone_region=>p_timezone_region) ; END IF ; EM_TASK.lock_task(p_task_id=>l_task_id) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('allocate_task:Exit normal allocated task='|| to_char(l_task_id),G_MODULE_NAME) ; END IF ; RETURN(nvl(l_task_id,0)) ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('allocate_task:Exit exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' (allocate_task)') ; END IF ; END allocate_task; -- -- Add/Modify a collection metric task -- -- Parameters -- p_dml_operation : I Insert into table -- U Update table -- D Delete from table -- PROCEDURE add_modify_metric_task(p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_task_id IN NUMBER, p_dml_operation IN VARCHAR2 := 'I', p_from_trigger IN BOOLEAN := FALSE) IS l_count NUMBER ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_modify_metric_task:Enter Task='|| to_char(p_task_id)||' Op code='||p_dml_operation, G_MODULE_NAME) ; END IF ; -- If repository metric then we allow only one collection -- per target/metric else one target/metric/collection IF p_dml_operation ='I' THEN BEGIN INSERT INTO MGMT_COLLECTION_METRIC_TASKS (target_guid,metric_guid,coll_name,task_id) VALUES (p_target_guid,p_metric_guid,p_coll_name, p_task_id) ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- agent collections will not have task_id so they will be ignored IF p_task_id IS NOT NULL THEN RAISE ; END IF ; END ; ELSIF p_dml_operation IN ('U','D') THEN IF p_dml_operation ='U' THEN UPDATE mgmt_collection_metric_tasks SET task_id = p_task_id WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name ; ELSE DELETE mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name ; END IF ; IF SQL%NOTFOUND AND NOT p_from_trigger THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'No record found when Updating/Deleting metric collection task'); END IF ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_modify_metric_task:Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('add_metric_task:Exit exception '||sqlerrm,G_MODULE_NAME) ; END IF ; IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN RAISE ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' (add_modify_metric_task)') ; END IF ; END add_modify_metric_task; -- Add collection metric task PROCEDURE add_collection_metric_task (p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_task_id IN NUMBER DEFAULT NULL) IS BEGIN INSERT INTO MGMT_COLLECTION_METRIC_TASKS (target_guid, metric_guid, coll_name, task_id) VALUES (p_target_guid, p_metric_guid, p_coll_name, p_task_id) ; END add_collection_metric_task; -- -- Suspend the task identified by p_task_id -- Removes all collections associated with the task -- Called by em_task when next time is null -- PROCEDURE set_task_null(p_task_id IN NUMBER) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_task_null:Enter task_id='||p_task_id,G_MODULE_NAME) ; END IF ; UPDATE mgmt_collection_metric_tasks SET task_id = NULL WHERE task_id = p_task_id ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('set_task_null:Exit count='||SQL%ROWCOUNT,G_MODULE_NAME) ; END IF ; END set_task_null ; -- -- Run the steps to be done on collections disable -- Currently includes the following -- Clear alerts on metrics in the collection -- Clear errors on metrics in the collection -- PROCEDURE run_disable_steps (p_target_guid IN RAW, p_coll_name IN VARCHAR2 ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_disable_steps:Enter run_disable_steps='|| g_run_disable_steps||'target='||p_target_guid|| ' coll='||p_coll_name,G_MODULE_NAME) ; END IF ; IF G_RUN_DISABLE_STEPS = MGMT_GLOBAL.G_FALSE THEN RETURN ; END IF ; FOR rec IN ( SELECT policy_guid metric_guid, decode(violation_type,0,1,1,1,0) is_metric FROM mgmt_current_violation WHERE target_guid = p_target_guid AND cfg_coll_name = p_coll_name UNION SELECT metric_guid, 1 FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND coll_name = p_coll_name ) LOOP EM_SEVERITY.clear_alerts (p_target_guid => p_target_guid, p_policy_guid => rec.metric_guid, p_cfg_coll_name => p_coll_name, p_clear_message => EM_SEVERITY.G_CLR_COLL_DISABLED_MSG, p_clear_nlsid => EM_SEVERITY.G_CLR_COLL_DISABLED_NLSID, p_is_metric => (rec.is_metric=1) ) ; END LOOP ; -- clear all errors for metrics in the collection clear_open_metric_errors (p_target_guid => p_target_guid, p_metric_guid =>NULL, p_coll_name =>p_coll_name, p_error_type =>NULL) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_disable_steps:Exit',G_MODULE_NAME) ; END IF ; END run_disable_steps ; -- -- -- Schedules a repository collection -- Takes care of all changes -- Pseudo Code: -- Handles following conditions -- -- Condition 1: -- IF task exists for the target/metric -- Lock task -- If Suspend operation -- If last target in task then remove task -- If not last target then set task_id=null -- IF schedule operation -- If last target -- if plsql source then update task schedule -- if sql source then remove task and allocate new task -- If not last target then -- remove target from task -- allocate new or matching task -- -- Condition 2: -- IF task does not exist and schedule operation then -- allocate new or matching task -- -- Condition 3: -- IF task does not exist and stop operation then -- removes metric collection record. -- PROCEDURE schedule_repo_collection (p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_op_code IN NUMBER, p_source_type IN NUMBER DEFAULT MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL, p_coll_schedule IN mgmt_coll_schedule_obj DEFAULT NULL, p_cat_prop_array IN mgmt_short_string_array DEFAULT NULL, p_timezone_region IN VARCHAR2 := NULL ) IS l_record_found BOOLEAN := FALSE ; l_current_task_id mgmt_collection_tasks.task_id%type ; l_new_task_id mgmt_collection_tasks.task_id%type ; l_targets_in_task_count NUMBER := 0 ; l_metrics_in_coll NUMBER := 0 ; l_last_collection_time DATE ; l_timezone_region mgmt_targets.timezone_region%type ; -- indicates what should be done to mgmt_collection_metric_tasks table -- I=Insert U=Update D=Delete X=no action l_dml_operation VARCHAR2(1) := 'X' ; l_enabled mgmt_collections.is_enabled%type ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('schedule_repo_collection:Enter Operation='|| to_char(p_op_code)||' coll='|| p_coll_name,G_MODULE_NAME) ; EMDW_LOG.DEBUG('schedule_repo_collection:Target Guid='|| to_char(p_target_guid)||' Metric Guid='|| to_char(p_metric_guid),G_MODULE_NAME) ; END IF ; BEGIN -- lock both tasks and metric tasks at same time to avoid race conditions SELECT mtask.task_id INTO l_current_task_id FROM mgmt_collection_tasks tasks, mgmt_collection_metric_tasks mtask WHERE mtask.target_guid = p_target_guid AND mtask.metric_guid = p_metric_guid AND mtask.coll_name = p_coll_name AND tasks.task_id(+) = mtask.task_id FOR UPDATE ; IF l_current_task_id IS NOT NULL THEN SELECT count(*) INTO l_targets_in_task_count FROM mgmt_collection_metric_tasks WHERE task_id = l_current_task_id ; END IF ; l_dml_operation := 'U' ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('schedule_repo_collection:Task='||l_current_task_id|| ' Count='||to_char(l_targets_in_task_count),G_MODULE_NAME) ; END IF ; EXCEPTION WHEN NO_DATA_FOUND THEN l_current_task_id := NULL ; l_dml_operation := 'I' ; WHEN TOO_MANY_ROWS THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Duplicate collection for target/metric') ; END ; l_timezone_region := nvl(p_timezone_region,EM_TASK.G_UTC_TIMEZONE) ; IF p_op_code = G_SCHEDULE_COLLECTION_OP THEN validate_schedule(p_coll_schedule) ; l_enabled := MGMT_GLOBAL.G_TRUE ; ELSE l_enabled := MGMT_GLOBAL.G_FALSE ; END IF ; IF l_current_task_id IS NOT NULL THEN -- task exists IF p_op_code IN ( G_SUSPEND_COLLECTION_OP, G_STOP_COLLECTION_OP) THEN -- suspend operation and task exists l_new_task_id := NULL ; IF p_op_code = G_SUSPEND_COLLECTION_OP THEN l_dml_operation := 'U' ; ELSE l_dml_operation := 'D' ; END IF ; IF l_targets_in_task_count = 1 THEN EM_TASK.remove_task(p_task_id=>l_current_task_id) ; END IF ; ELSE -- not suspend operation and task exists IF l_targets_in_task_count = 1 THEN -- last target IF p_source_type IN ( MGMT_GLOBAL.G_METRIC_SOURCE_PLSQL, MGMT_GLOBAL.G_METRIC_SOURCE_OLD_PLSQL) THEN l_dml_operation := 'X' ; EM_TASK.update_task(p_task_id=>l_current_task_id, p_coll_schedule=>p_coll_schedule, p_timezone_region=>l_timezone_region) ; ELSE l_dml_operation := 'U' ; EM_TASK.remove_task(p_task_id=>l_current_task_id) ; l_new_task_id := allocate_task(p_target_guid=>p_target_guid, p_metric_guid=>p_metric_guid, p_coll_name=>p_coll_name, p_cat_prop_array=>p_cat_prop_array, p_coll_schedule=>p_coll_schedule, p_source_type=>p_source_type, p_timezone_region=>l_timezone_region); END IF ;-- source type ELSE -- not last target l_dml_operation := 'U' ; l_new_task_id := allocate_task(p_target_guid=>p_target_guid, p_metric_guid=>p_metric_guid, p_coll_name=>p_coll_name, p_cat_prop_array=>p_cat_prop_array, p_coll_schedule=>p_coll_schedule, p_source_type=>p_source_type, p_timezone_region=>l_timezone_region); END IF; -- last target check END IF ; -- suspend operation check ELSIF p_op_code = G_SCHEDULE_COLLECTION_OP THEN l_new_task_id := allocate_task(p_target_guid=>p_target_guid, p_metric_guid=>p_metric_guid, p_coll_name=>p_coll_name, p_cat_prop_array=>p_cat_prop_array, p_coll_schedule=>p_coll_schedule, p_source_type=>p_source_type, p_timezone_region=>l_timezone_region) ; ELSIF p_op_code = G_STOP_COLLECTION_OP THEN l_dml_operation := 'D' ; END IF; -- Clear alerts on stop -- TBD:now we do not clear alerts on SUSPEND -- we have to accept suspend codes and only clear -- for user suspends and not for system suspends -- Example: we should not clear on suspend due to blackouts -- This should be before removing the metric record IF p_op_code = G_STOP_COLLECTION_OP AND l_dml_operation = 'D' THEN run_disable_steps(p_target_guid=>p_target_guid, p_coll_name =>p_coll_name) ; END IF ; IF l_dml_operation IN ('I','U','D') AND nvl(l_new_task_id,-1) != nvl(l_current_task_id,-2) THEN add_modify_metric_task(p_target_guid=>p_target_guid, p_metric_guid=>p_metric_guid, p_coll_name=>p_coll_name, p_task_id=>l_new_task_id, p_dml_operation=>l_dml_operation ) ; END IF ; -- delete the collection if this was the last metric for the collection IF p_op_code = G_STOP_COLLECTION_OP THEN SELECT nvl(count(*),0) INTO l_metrics_in_coll FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND coll_name = p_coll_name ; IF l_metrics_in_coll = 0 THEN delete_collection_entry(p_object_guid=>p_target_guid, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name=>p_coll_name) ; -- Delete collection properties DELETE mgmt_coll_item_properties WHERE object_guid = p_target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND coll_name = p_coll_name ; -- Delete credentials DELETE mgmt_credentials2 WHERE credential_guid IN (SELECT credential_guid FROM mgmt_collection_credentials WHERE target_guid = p_target_guid AND coll_name = p_coll_name); DELETE mgmt_collection_credentials WHERE target_guid = p_target_guid AND coll_name = p_coll_name ; END IF ; ELSIF p_op_code IN (G_SCHEDULE_COLLECTION_OP,G_SUSPEND_COLLECTION_OP) THEN -- trigger code will not run disable steps when this flag is set G_RUN_DISABLE_STEPS := MGMT_GLOBAL.G_FALSE ; modify_collection_entry(p_object_guid=>p_target_guid, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name=>p_coll_name, p_is_enabled=>l_enabled) ; G_RUN_DISABLE_STEPS := MGMT_GLOBAL.G_TRUE ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('schedule_repo_collection:Exit normal',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('schedule_repo_collection:Exit exception '||sqlerrm, G_MODULE_NAME) ; END IF ; RAISE ; END schedule_repo_collection; -- -- Procedure to stop/resume/suspend a single collection -- PROCEDURE suspend_resume_stop_single (p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_source_type IN NUMBER, p_timezone_region IN VARCHAR2, p_cat_prop_array IN mgmt_short_string_array, p_op_code IN NUMBER DEFAULT G_SUSPEND_COLLECTION_OP ) IS l_coll_schedule mgmt_coll_schedule_obj ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('suspend_resume_stop_single:Enter',G_MODULE_NAME) ; EMDW_LOG.DEBUG('suspend_resume_stop_single:Mguid='||p_metric_guid, G_MODULE_NAME) ; EMDW_LOG.DEBUG('suspend_resume_stop_single:coll='||p_coll_name, G_MODULE_NAME) ; END IF ; -- get current schedule from collections table and lock collection -- so 2 sessions cannot concurrently change the same/target collection l_coll_schedule := lock_and_get_schedule (p_target_guid=>p_target_guid, p_coll_name=>p_coll_name, p_timezone_region=>p_timezone_region); schedule_repo_collection (p_target_guid=>p_target_guid, p_coll_name=>p_coll_name, p_timezone_region=>nvl(p_timezone_region, EM_TASK.G_UTC_TIMEZONE), p_metric_guid=>p_metric_guid, p_source_type=>p_source_type, p_coll_schedule=>l_coll_schedule, p_cat_prop_array=>p_cat_prop_array, p_op_code=>p_op_code) ; IF p_op_code = G_STOP_COLLECTION_OP THEN DELETE mgmt_metric_collections_rep WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('suspend_resume_stop_single:Exit Normal',G_MODULE_NAME) ; END IF ; END suspend_resume_stop_single ; -- -- Applies to repository collections only -- Suspends Schedules or stops Collections -- Suspend updates thec collection task to null -- Stop removes the collection record from the system -- schedule will schedule repository collection -- Parameter: -- p_op_code : Can take the values -- G_SUSPEND_COLLECTION_OP -- G_SCHEDULE_COLLECTION_OP -- G_STOP_COLLECTION_OP -- PROCEDURE suspend_resume_stop_collection (p_target_type IN VARCHAR2, p_target_name IN VARCHAR2, p_metric_name IN VARCHAR2 DEFAULT NULL, p_metric_column IN VARCHAR2 DEFAULT ' ', p_coll_name IN VARCHAR2 DEFAULT NULL, p_op_code IN NUMBER DEFAULT G_SUSPEND_COLLECTION_OP, p_target_guid IN RAW := NULL) IS l_target_guid mgmt_targets.target_guid%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_metric_column mgmt_metrics.metric_column%type ; l_source_type mgmt_metrics.source_type%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_is_rep_metric mgmt_metrics.is_repository%type ; l_timezone_region mgmt_targets.timezone_region%type ; l_cat_prop_array mgmt_short_string_array := mgmt_short_string_array() ; l_coll_schedule mgmt_coll_schedule_obj ; l_coll_rec mgmt_collections%rowtype ; l_collections_found BOOLEAN := FALSE ; l_error_message VARCHAR2(30) ; l_metric_name mgmt_metrics.metric_name%type ; l_prev_coll_name mgmt_collections.coll_name%type ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('suspend_resume_stop:Enter Op='||p_op_code,G_MODULE_NAME); EMDW_LOG.DEBUG('suspend_resume_stop:Name='||p_target_name|| ' Type='||p_target_type,G_MODULE_NAME) ; END IF ; IF p_target_guid IS NULL THEN l_target_guid := mgmt_target.get_target_guid (target_name_in=>p_target_name , target_type_in=>p_target_type) ; ELSE l_target_guid := p_target_guid ; END IF ; -- Suspend Collections -- If target/collection name is given, -- process all collections for the target/collection -- If target/metric name is given -- Assume collection name=metric_name and process -- all collections for the target/collection -- if both collection/metric name are not given -- process all collections for the target -- Note: We do not allow suspend/resume/stop operations at -- collection/metric level. We only allow up to collection level FOR rec IN ( SELECT metric_guid,coll_name,task_id FROM mgmt_collection_metric_tasks WHERE target_guid = l_target_guid AND coll_name=NVL(NVL(p_coll_name,p_metric_name),coll_name) AND ( (p_op_code = G_SUSPEND_COLLECTION_OP AND task_id IS NOT NULL ) OR (p_op_code = G_SCHEDULE_COLLECTION_OP AND task_id IS NULL ) OR (p_op_code = G_STOP_COLLECTION_OP) ) ORDER BY coll_name,metric_guid ) LOOP SELECT metric_name, metric_column INTO l_metric_name,l_metric_column FROM mgmt_metrics WHERE metric_guid = rec.metric_guid AND ROWNUM = 1 ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('suspend_resume_stop:processing Coll='||rec.coll_name|| ' metric='||l_metric_name||' '||l_metric_column,G_MODULE_NAME) ; END IF ; validate_target_metric (p_target_type=>p_target_type, p_target_name=>p_target_name, p_metric_name=>l_metric_name, p_metric_column=>l_metric_column, p_target_guid=>l_target_guid, p_type_meta_ver=>l_type_meta_ver, p_metric_guid=>l_metric_guid, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_is_repository=>l_is_rep_metric, p_cat_prop_array=>l_cat_prop_array) ; -- Do not resume non-repository collections using schedule repo IF l_is_rep_metric = MGMT_GLOBAL.G_FALSE AND p_op_code = G_SCHEDULE_COLLECTION_OP THEN IF rec.coll_name != NVL(l_prev_coll_name,rec.coll_name||' ') THEN G_RUN_DISABLE_STEPS := MGMT_GLOBAL.G_FALSE ; modify_collection_entry(p_object_guid=>l_target_guid, p_object_type=>MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_coll_name=>rec.coll_name, p_is_enabled=>MGMT_GLOBAL.G_TRUE) ; G_RUN_DISABLE_STEPS := MGMT_GLOBAL.G_TRUE ; END IF ; ELSE suspend_resume_stop_single (p_target_guid=>l_target_guid, p_metric_guid=>rec.metric_guid, p_coll_name=>rec.coll_name, p_source_type=>l_source_type, p_timezone_region=>l_timezone_region, p_cat_prop_array=>l_cat_prop_array, p_op_code=>p_op_code) ; END IF ; l_prev_coll_name := rec.coll_name ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('suspend_resume_stop:Exit Normal',G_MODULE_NAME) ; END IF ; END suspend_resume_stop_collection; -- Handle meta version change of target for default collections -- Logic: -- Remove collections for metrics which are no longer valid -- Start Default collection for new version -- PROCEDURE handle_metaver_change_def_coll (p_callback_obj IN MGMT_TARGET_META_VER_CBK_OBJ) IS -- Get collections with invalid metrics CURSOR invalid_metric_collections(p_target_guid IN RAW, p_target_type IN VARCHAR2, p_to_metaver IN VARCHAR2, p_to_catprops IN mgmt_short_string_array ) IS WITH current_collections AS (SELECT metric_guid, coll_name FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid ) SELECT metric_guid ,coll_name FROM current_collections MINUS SELECT tasks.metric_guid ,tasks.coll_name FROM current_collections tasks, mgmt_metrics m WHERE m.metric_guid = tasks.metric_guid AND m.target_type = p_target_type AND m.type_meta_ver = p_to_metaver AND (m.category_prop_1 = p_to_catprops(1) OR m.category_prop_1=' ') AND (m.category_prop_2 = p_to_catprops(2) OR m.category_prop_2=' ') AND (m.category_prop_3 = p_to_catprops(3) OR m.category_prop_3=' ') AND (m.category_prop_4 = p_to_catprops(4) OR m.category_prop_4=' ') AND (m.category_prop_5 = p_to_catprops(5) OR m.category_prop_5=' ') ; l_target_name mgmt_targets.target_name%type ; l_category_prop_1 mgmt_targets.category_prop_1%type ; l_category_prop_2 mgmt_targets.category_prop_2%type ; l_category_prop_3 mgmt_targets.category_prop_3%type ; l_category_prop_4 mgmt_targets.category_prop_4%type ; l_category_prop_5 mgmt_targets.category_prop_5%type ; l_coll_names mgmt_medium_string_array := mgmt_medium_string_array() ; l_del_coll_names mgmt_medium_string_array := mgmt_medium_string_array() ; l_metrics mgmt_target_guid_array := mgmt_target_guid_array() ; l_clear_message mgmt_violations.message%type ; l_clear_nlsid mgmt_violations.message_nlsid%type ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('handle_metaver_change_def_coll:Enter',G_MODULE_NAME) ; END IF ; -- Lock all collections for target SELECT coll_name BULK COLLECT INTO l_coll_names FROM mgmt_collections WHERE object_guid = p_callback_obj.target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET FOR UPDATE ; SELECT target_name INTO l_target_name FROM mgmt_targets WHERE target_guid = p_callback_obj.target_guid ; IF l_coll_names IS NOT NULL AND l_coll_names.COUNT > 0 THEN -- remove metric collections for metrics which are no longer valid OPEN invalid_metric_collections(p_callback_obj.target_guid, p_callback_obj.target_type, p_callback_obj.to_meta_ver, p_callback_obj.to_catprop_array) ; FETCH invalid_metric_collections BULK COLLECT INTO l_metrics,l_del_coll_names; CLOSE invalid_metric_collections ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('handle_metaver_change:Invalid Metrics count='|| l_metrics.COUNT,G_MODULE_NAME) ; END IF ; -- If there are invalid metrics then -- Delete collections for which all metrics are invalid -- Delete all the metric data from collection for invalid metrics IF l_metrics IS NOT NULL AND l_metrics.COUNT > 0 THEN FOR i IN l_metrics.first..l_metrics.last LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('handle_metaver_change:Removing Coll details for =>' ||l_del_coll_names(i)||':'||l_metrics(i),G_MODULE_NAME) ; END IF ; delete_metric_collection_int (p_target_guid => p_callback_obj.target_guid, p_metric_guid => l_metrics(i), p_coll_name => l_del_coll_names(i), p_clear_alerts => MGMT_GLOBAL.G_TRUE ); END LOOP ; END IF ; END IF ; -- Start repository collections for new version/category props mgmt_collection.start_default_collection (p_target_name=>l_target_name, p_target_type=>p_callback_obj.target_type, p_target_guid=>p_callback_obj.target_guid); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('handle_metaver_change_def_coll:Exit',G_MODULE_NAME) ; END IF ; END handle_metaver_change_def_coll ; -- -- if any of the targets are in blackout, we just ignore them -- PROCEDURE remove_targets_in_blkout(p_targets_in IN mgmt_target_guid_array, p_targets_out OUT NOCOPY mgmt_target_guid_array, p_timezone_region IN VARCHAR2) IS l_target_idx NUMBER ; BEGIN IF p_targets_in IS NULL OR p_targets_in.COUNT <= 0 THEN RETURN ; END IF ; p_targets_out := mgmt_target_guid_array() ; l_target_idx := p_targets_in.FIRST ; WHILE l_target_idx IS NOT NULL LOOP IF NOT MGMT_BLACKOUT_ENGINE.IS_BLACKED_OUT(p_targets_in(l_target_idx), CAST(systimestamp AT time zone p_timezone_region as DATE)) THEN p_targets_out.EXTEND ; p_targets_out(p_targets_out.COUNT):=p_targets_in(l_target_idx); END IF ; l_target_idx := p_targets_in.NEXT(l_target_idx) ; END LOOP ; END remove_targets_in_blkout ; -- -- PURPOSE -- To bulk insert metric values into mgmt_metric_raw table -- Parameters -- p_metric_values: Rows to be inserted -- p_check_threshold : 1 - Check values against thresholds and raise alerts -- 0 - Do not check thresholds -- PROCEDURE insert_metric_raw (p_metric_values IN mgmt_metric_raw_table, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_coll_name IN VARCHAR2 DEFAULT ' ', p_check_threshold IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS l_key_values mgmt_medium_string_table := mgmt_medium_string_table() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('insert_raw:Enter store='||p_store_metric|| ' coll='||p_coll_name|| ' threshold_check='||p_check_threshold,G_MODULE_NAME) ; END IF ; IF p_metric_values IS NOT NULL AND p_metric_values.COUNT > 0 THEN -- generate composite keys if there are any multi key metrics -- EM_POLICY_EVAL.get_set_composite_key(p_metric_raw_records=>p_metric_values, p_key_values=>l_key_values) ; -- Store metric data IF p_store_metric = MGMT_GLOBAL.G_TRUE THEN EM_METRIC_EVAL.store_metric_data(p_metric_raw_records=>p_metric_values, p_key_values=>l_key_values) ; END IF ; END IF ; -- evaluate thresholds IF p_check_threshold = MGMT_GLOBAL.G_TRUE THEN EM_POLICY_EVAL.evaluate_thresholds(p_metric_raw_records => p_metric_values, p_coll_name => p_coll_name ) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('insert_raw:Exit'||p_check_threshold,G_MODULE_NAME) ; END IF ; END insert_metric_raw ; -- -- store metric data and evaluate thresholds if specified -- PROCEDURE store_metric_data (p_target_guid IN RAW, p_metric_guid IN RAW, p_metric_results IN mgmt_metric_results_table, p_store_metric IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE, p_coll_name IN VARCHAR2 DEFAULT ' ', p_check_thresholds IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE, p_collection_timestamp IN DATE DEFAULT NULL ) IS l_collection_timestamp DATE ; l_metric_info EM_METRIC_EVAL.metric_info_rec ; l_metric_values mgmt_metric_value_array := mgmt_metric_value_array() ; l_key_index_array EM_POLICY_EVAL.g_integer_array_table := EM_POLICY_EVAL.g_integer_array_table() ; l_result_key_array mgmt_medium_string_table := mgmt_medium_string_table(); BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data(r):Enter',G_MODULE_NAME) ; END IF ; l_collection_timestamp := NVL(p_collection_timestamp, mgmt_target.sysdate_target(p_target_guid)); EM_METRIC_EVAL.get_metric_info (p_target_guid => p_target_guid, p_metric_guid => p_metric_guid, p_eval_mode => EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_metric_info => l_metric_info, p_repo_metric => MGMT_GLOBAL.G_FALSE ) ; IF p_metric_results IS NOT NULL AND p_metric_results.COUNT > 0 THEN EM_METRIC_EVAL.convert_results (p_target_guid => p_target_guid, p_metric_results => p_metric_results, p_collection_timestamp => l_collection_timestamp, p_metric_info => l_metric_info, p_metric_values => l_metric_values) ; IF EMDW_LOG.P_IS_INFO_SET THEN EM_METRIC_EVAL.dump_metric_results(p_metric_values=>l_metric_values) ; END IF ; IF l_metric_info.num_keys > 0 THEN EM_POLICY_EVAL.initialize_counters (p_metric_values=>l_metric_values, p_key_columns=>l_metric_info.key_cols, p_key_index_array=>l_key_index_array, p_result_key_array=>l_result_key_array) ; END IF ; IF p_store_metric = MGMT_GLOBAL.G_TRUE THEN EM_METRIC_EVAL.store_metric_data(p_metric_values=>l_metric_values, p_metric_name=>l_metric_info.metric_name, p_coll_name=>p_coll_name, p_metric_guid=>l_metric_info.metric_guid, p_metric_type=>l_metric_info.metric_type, p_num_keys=>l_metric_info.num_keys, p_metric_columns=>l_metric_info.metric_cols, p_result_key_array=>l_result_key_array, p_store_metric=>MGMT_GLOBAL.G_TRUE) ; END IF ; -- p_store_metric=TRUE END IF ; -- p_metric_results is not null -- -- Evaluate policy will be true for background tasks -- only realtime UI collections will have option to skip evaluate policy -- IF p_check_thresholds = MGMT_GLOBAL.G_TRUE THEN EM_POLICY_EVAL.evaluate_policy (p_task_id=>NULL, p_target_guids=>mgmt_target_guid_array(p_target_guid), p_metric_info=>l_metric_info, p_metric_values=>l_metric_values, p_result_key_array=>l_result_key_array, p_key_index_array=>l_key_index_array, p_eval_mode=>EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_coll_name=>p_coll_name ) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_metric_data(r):Exit',G_MODULE_NAME) ; END IF ; END store_metric_data ; -- -- Internal procedure to run the collection -- Runs on two modes determined by p_eval_mode -- task_id mode in which it runs the collection for a collection task -- called by collection worker -- target/metric mode in which it runs the collection for a target/metric -- combination -- PROCEDURE run_collection (p_task_id IN NUMBER := NULL, p_target_guids IN mgmt_target_guid_array:= NULL, p_metric_guid IN RAW := NULL, p_coll_name IN VARCHAR2 := ' ' , p_key_value IN VARCHAR2 := NULL, p_store_metric IN NUMBER := NULL, p_store_metric_errors IN NUMBER := MGMT_GLOBAL.G_TRUE, p_eval_mode IN NUMBER := EM_METRIC_EVAL.G_TARGET_EVAL_MODE, p_evaluate_policy IN NUMBER := MGMT_GLOBAL.G_TRUE, p_timezone_region IN VARCHAR2 := 'GMT', p_metric_values OUT NOCOPY mgmt_metric_value_array, p_error_message IN OUT NOCOPY VARCHAR2, p_config_std_guid IN RAW DEFAULT NULL ) IS l_metric_info EM_METRIC_EVAL.METRIC_INFO_REC ; l_key_index_array EM_POLICY_EVAL.g_integer_array_table := EM_POLICY_EVAL.g_integer_array_table() ; l_result_key_array mgmt_medium_string_table := mgmt_medium_string_table(); l_current_rec NUMBER := NULL ; l_coll_name mgmt_collection_metric_tasks.coll_name%type ; l_store_metric mgmt_collections.store_metric%type ; l_prev_error_message mgmt_collection_tasks.error_message%type := p_error_message ; l_target_guids mgmt_target_guid_array := mgmt_target_guid_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('em.run_collection:Enter',G_MODULE_NAME) ; END IF ; p_error_message := NULL ; -- Evaluate metric BEGIN IF p_eval_mode = EM_METRIC_EVAL.G_TASK_EVAL_MODE THEN l_store_metric := NULL ; EM_METRIC_EVAL.evaluate_metric(p_task_id=>p_task_id, p_metric_info=>l_metric_info, p_metric_values=>p_metric_values, p_coll_name=>l_coll_name) ; ELSE l_store_metric := p_store_metric ; remove_targets_in_blkout(p_target_guids, l_target_guids, p_timezone_region) ; IF l_target_guids IS NULL OR l_target_guids.COUNT = 0 THEN RETURN ; END IF ; l_coll_name := NVL(p_coll_name,' ') ; -- IF p_coll_name = ' ' -- THEN -- l_coll_name := p_coll_name ; -- ELSE -- l_coll_name := collection_name(l_target_guids(1), -- p_metric_guid, -- p_coll_name); -- END IF ; EM_METRIC_EVAL.evaluate_metric(p_target_guids=>l_target_guids, p_metric_guid=>p_metric_guid, p_metric_info=>l_metric_info, p_coll_name=>l_coll_name, p_metric_values=>p_metric_values) ; END IF ; EXCEPTION WHEN OTHERS THEN p_error_message := sqlerrm ; END ; -- -- Store errors if any -- -- If store_metric_errors is TRUE then --store metric errors for the following condtions -- metric is correct and -- current error message is different from previous --clear metric errors for the following conditions -- metric is correct and -- previous error message is there and current error IS NULL -- Store/Clear metric errors if in target mode. IF p_store_metric_errors = MGMT_GLOBAL.G_TRUE AND ( --Store error message ( p_error_message IS NOT NULL AND l_metric_info.metric_guid IS NOT NULL AND ( l_prev_error_message IS NULL OR p_error_message != l_prev_error_message ) ) --clear error message OR ( p_error_message IS NULL AND l_prev_error_message IS NOT NULL AND l_metric_info.metric_guid IS NOT NULL ) -- Store or Clear error message for target OR p_eval_mode = EM_METRIC_EVAL.G_TARGET_EVAL_MODE ) THEN EM_METRIC_EVAL.store_metric_errors (p_task_id=>p_task_id, p_target_guids=>l_target_guids, p_timezone_region=>p_timezone_region, p_metric_guid=>l_metric_info.metric_guid, p_coll_name=>l_coll_name, p_error_message=>p_error_message) ; END IF ; -- Return on ERROR IF p_error_message IS NOT NULL THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('em.run_collection:Exit Exception '||p_error_message, G_MODULE_NAME) ; END IF ; RETURN ; END IF ; IF p_metric_values IS NOT NULL AND p_metric_values.COUNT>0 THEN -- if status = Skip then skip policy evaluation all together IF p_metric_values(1).status = EM_METRIC_EVAL.G_METRIC_EVAL_SKIP THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('em.run_collection:Exit Skip',G_MODULE_NAME) ; END IF ; RETURN ; END IF ; -- Initialize the various counters for faster access to information IF l_metric_info.num_keys > 0 THEN EM_POLICY_EVAL.initialize_counters (p_metric_values=>p_metric_values, p_key_columns=>l_metric_info.key_cols, p_key_index_array=>l_key_index_array, p_result_key_array=>l_result_key_array) ; -- p_key_value will only be populated for realtime UI collections IF p_key_value IS NOT NULL THEN trim_result_records(p_metric_values,l_result_key_array,p_key_value) ; END IF ; END IF ; -- num_keys >0 -- -- store metric would be NULL for background collection tasks -- IF l_store_metric = MGMT_GLOBAL.G_TRUE OR l_store_metric IS NULL THEN EM_METRIC_EVAL.store_metric_data(p_metric_values=>p_metric_values, p_metric_name=>l_metric_info.metric_name, p_coll_name=>l_coll_name, p_metric_guid=>l_metric_info.metric_guid, p_metric_type=>l_metric_info.metric_type, p_num_keys=>l_metric_info.num_keys, p_metric_columns=>l_metric_info.metric_cols, p_result_key_array=>l_result_key_array, p_store_metric=>l_store_metric) ; END IF ; -- l_store_metric=TRUE END IF ; -- p_metric_values IS NULL -- -- Evaluate policy will be true for background tasks -- only realtime UI collections will have option to skip evaluate policy -- IF p_evaluate_policy = MGMT_GLOBAL.G_TRUE THEN EM_POLICY_EVAL.evaluate_policy(p_task_id=>p_task_id, p_target_guids=>l_target_guids, p_metric_info=>l_metric_info, p_metric_values=>p_metric_values, p_result_key_array=>l_result_key_array, p_key_index_array=>l_key_index_array, p_eval_mode=>p_eval_mode, p_coll_name=>l_coll_name, p_config_std_guid =>p_config_std_guid ) ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('em.run_collection:Exit Normal ',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('em.run_collection:Exit Exception '||sqlerrm,G_MODULE_NAME) ; END IF ; p_error_message := sqlerrm ; END run_collection; -- -- Run the repository collection identified by coll_name -- Picks up all the distinct tasks associated with the -- collection and runs them -- PROCEDURE run_collection(p_coll_name IN VARCHAR2) IS BEGIN RAISE_APPLICATION_ERROR(MGMT_GLOBAL.COLLECTION_ERR,'Deprecated code') ; END run_collection ; -- -- API to run on-demand collections asyncronously -- PROCEDURE run_collection(p_context mgmt_namevalue_array) IS l_target_type mgmt_targets.target_type%type ; l_target_name mgmt_targets.target_name%type ; l_metric_name mgmt_metrics.metric_name%type ; l_store_metric mgmt_collections.store_metric%type ; FUNCTION get_value(p_name IN VARCHAR2) RETURN VARCHAR2 IS BEGIN FOR i IN p_context.FIRST..p_context.LAST LOOP IF p_context(i).name = p_name THEN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('run_collection(async):'||p_name||'=>' ||p_context(i).value,G_MODULE_NAME) ; END IF ; RETURN(p_context(i).value) ; END IF ; END LOOP ; RAISE_APPLICATION_ERROR(MGMT_GLOBAL.COLLECTION_ERR, 'Could not find value for '||p_name) ; END get_value ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection(async):Enter',G_MODULE_NAME) ; END IF ; l_target_type := get_value('target_type') ; l_target_name := get_value('target_name') ; l_metric_name := get_value('metric_name') ; l_store_metric := get_value('store_metric') ; BEGIN MGMT_COLLECTION.RUN_COLLECTION( p_target_type=>l_target_type, p_target_name=>l_target_name, p_metric_name=>l_metric_name, p_coll_name=>l_metric_name, p_store_metric=>l_store_metric, p_store_metric_errors=> MGMT_GLOBAL.G_TRUE) ; EXCEPTION WHEN OTHERS THEN -- Just in case, even though RUN_COLLECTION isn't supposed to release an exception. IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('run_collection(async):Exception '||sqlerrm, G_MODULE_NAME); END IF ; END; COMMIT; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_collection(async):Exit' ,G_MODULE_NAME) ; END IF ; END run_collection ; -- run_snapshot_collections. -- To be called when snapshots are refreshed. This procedure looks at the snapshot-metric dependency MAP and determines -- which metrics (repository based) select from data populated by the snapshot. Since this indicates that the underlying -- data may have changed, those metrics' collections are run. -- NOTE: this procedure commits before exiting and does not though any exceptions. -- Parameters: -- p_snapshotType: The snapshot type (or name) that has just been refreshed. -- p_targetType: Needed along with the snapshot type to uniquely identify the snapshot that was refreshed. -- p_targetName: The snapshot was refreshed for this target. -- PROCEDURE run_snapshot_collections( p_context IN mgmt_namevalue_array) IS l_task_id mgmt_collection_tasks.task_id%type ; l_context mgmt_namevalue_array := mgmt_namevalue_array() ; l_metrics mgmt_short_string_array := mgmt_short_string_array(); l_target_names mgmt_medium_string_array := mgmt_medium_string_array(); l_target_types mgmt_medium_string_array := mgmt_medium_string_array(); l_store_metrics mgmt_integer_table := mgmt_integer_table(); l_task_class_list mgmt_integer_table := mgmt_integer_table(); l_target_guid mgmt_targets.target_guid%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_cat_prop_1 mgmt_targets.category_prop_1%type ; l_cat_prop_2 mgmt_targets.category_prop_2%type ; l_cat_prop_3 mgmt_targets.category_prop_3%type ; l_cat_prop_4 mgmt_targets.category_prop_4%type ; l_cat_prop_5 mgmt_targets.category_prop_5%type ; l_target_type mgmt_targets.target_type%TYPE ; l_target_name mgmt_targets.target_name%TYPE ; l_snapshot_name mgmt_snapshot_metric_map.snapshot_name%TYPE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_snapshot_collections:Enter',G_MODULE_NAME) ; END IF ; BEGIN FOR i IN 1..3 LOOP CASE(p_context(i).name) WHEN 'snapshot_type' THEN l_snapshot_name := p_context(i).value ; WHEN 'target_type' THEN l_target_type := p_context(i).value ; WHEN 'target_name' THEN l_target_name := p_context(i).value ; END CASE ; END LOOP ; EXCEPTION -- any error will result in one of snapshot, target name,target type null -- so it will be returned in next step WHEN OTHERS THEN NULL ; END ; IF l_snapshot_name IS NULL OR l_target_name IS NULL OR l_target_name IS NULL THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_snapshot_collections:Exit invalid data',G_MODULE_NAME) ; END IF ; RETURN ; ELSIF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('run_snapshot_collections:'|| ' Snapshot Name='||l_snapshot_name|| ' Target Name='||l_target_name|| ' Target Type='||l_target_type,G_MODULE_NAME) ; END IF ; -- Get the list of metrics based on snapshot-metric-map -- Get list of targets which share the same agent as the current -- target. -- Combine the two lists to get list of metrics and targets. --For example: --if we have agent A1 monitoring host H1 --and Databases D1 and D2. --Snapshot map Contains metric MD1 of target type oracle_database --Snapshot map Contains metric MH1 of target type host -- --This procedure is called with H1,Host as parameter. -- --Metric MD1 will be collected for databases D1 and D2. --Metric HD1 will be collected for H1 -- BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_cat_prop_1, l_cat_prop_2, l_cat_prop_3, l_cat_prop_4, l_cat_prop_5 FROM mgmt_targets WHERE target_name = l_target_name AND target_type = l_target_type ; WITH target_list AS ( -- list of targets hosted by this target SELECT tgt.target_name, tgt.target_type, tgt.type_meta_ver, tgt.category_prop_1, tgt.category_prop_2, tgt.category_prop_3, tgt.category_prop_4, tgt.category_prop_5 FROM mgmt_target_assocs assoc, mgmt_targets tgt WHERE assoc.assoc_target_guid = l_target_guid AND assoc.assoc_guid = MGMT_ASSOC.g_hosted_by_guid AND tgt.target_guid = assoc.source_target_guid UNION ALL SELECT l_target_name, l_target_type, l_type_meta_ver, l_cat_prop_1 , l_cat_prop_2 , l_cat_prop_3, l_cat_prop_4 , l_cat_prop_5 FROM DUAL ) SELECT UNIQUE m.metric_name , s.store_metric, m.is_long_running, tgt.target_name, tgt.target_type BULK COLLECT INTO l_metrics, l_store_metrics , l_task_class_list, l_target_names, l_target_types FROM mgmt_snapshot_metric_map s, mgmt_metrics m, target_list tgt WHERE l_snapshot_name = s.snapshot_name AND l_target_type = s.snapshot_target_type AND m.metric_guid = s.metric_guid AND m.target_type = tgt.target_type AND m.type_meta_ver = tgt.type_meta_ver AND (m.category_prop_1 = tgt.category_prop_1 OR m.category_prop_1=' ') AND (m.category_prop_2 = tgt.category_prop_2 OR m.category_prop_2=' ') AND (m.category_prop_3 = tgt.category_prop_3 OR m.category_prop_3=' ') AND (m.category_prop_4 = tgt.category_prop_4 OR m.category_prop_4=' ') AND (m.category_prop_5 = tgt.category_prop_5 OR m.category_prop_5=' '); EXCEPTION WHEN NO_DATA_FOUND THEN NULL ; END ; -- If there were no dependent metrics, then simply return. IF l_metrics IS NULL OR l_metrics.COUNT = 0 THEN RETURN; END IF; -- Loop through the metrics and submit a asyncronous task for each of the metric FOR i IN l_metrics.FIRST..l_metrics.LAST LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('run_snapshot_collections:'|| ' target_type='|| l_target_types(i)|| ' target_name='|| l_target_names(i)|| ' metric_name='|| l_metrics(i)|| ' store_metric='|| l_store_metrics(i),G_MODULE_NAME) ; END IF ; BEGIN MGMT_COLLECTION.RUN_COLLECTION( p_target_type=>l_target_types(i), p_target_name=>l_target_names(i), p_metric_name=>l_metrics(i), p_coll_name=>l_metrics(i), p_store_metric=>l_store_metrics(i), p_store_metric_errors=> MGMT_GLOBAL.G_TRUE) ; EXCEPTION WHEN OTHERS THEN -- Just in case, even though RUN_COLLECTION isn't supposed to release an exception. IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('run_snapshot_collections:Exception '||sqlerrm, G_MODULE_NAME); END IF ; END; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_snapshot_collections:Exit',G_MODULE_NAME) ; END IF ; END run_snapshot_collections ; PROCEDURE lock_metric(p_metric_guid IN RAW, p_willing_to_wait IN BOOLEAN) IS resource_busy EXCEPTION ; PRAGMA EXCEPTION_INIT(resource_busy,-54) ; l_task_list mgmt_integer_array := mgmt_integer_array() ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('lock_metric:Enter',G_MODULE_NAME) ; END IF ; -- no one is calling this now, if someone wants to lock -- metric then can use a seperate table or use dbms_lock IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('lock_metric:Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN NO_DATA_FOUND THEN --TODO:use mgmt_lock utility to lock the metric NULL ; WHEN resource_busy THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Another collection for metric running,Please retry') ; WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, MGMT_GLOBAL.COLLECTION_ERR_M||':'||sqlerrm) ; END lock_metric; -- -- Store metrics for a collection metrics -- PROCEDURE store_coll_item_metrics(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_coll_name IN VARCHAR2, p_metrics_list IN mgmt_metric_name_array, p_category_prop_1 IN VARCHAR2, p_category_prop_2 IN VARCHAR2, p_category_prop_3 IN VARCHAR2, p_category_prop_4 IN VARCHAR2, p_category_prop_5 IN VARCHAR2) IS l_metric_guid mgmt_metrics.metric_guid%type ; l_metric_name mgmt_metrics.metric_name%type ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_coll_item_metrics:Enter ',G_MODULE_NAME) ; END IF ; IF p_metrics_list IS NULL OR p_metrics_list.COUNT = 0 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Metric list List is Null') ; END IF ; FOR metric in p_metrics_list.FIRST..p_metrics_list.LAST LOOP BEGIN l_metric_name := p_metrics_list(metric).metric_name ; SELECT metric_guid INTO l_metric_guid FROM mgmt_metrics WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND metric_name = p_metrics_list(metric).metric_name AND metric_column = p_metrics_list(metric).metric_column AND (category_prop_1 = ' ' OR category_prop_1 = nvl(p_category_prop_1,' ') )AND (category_prop_2 = ' ' OR category_prop_2 = nvl(p_category_prop_2,' ') )AND (category_prop_3 = ' ' OR category_prop_3 = nvl(p_category_prop_3,' ') )AND (category_prop_4 = ' ' OR category_prop_4 = nvl(p_category_prop_4,' ') )AND (category_prop_5 = ' ' OR category_prop_5 = nvl(p_category_prop_5,' ') ); INSERT INTO mgmt_coll_item_metrics (target_type,type_meta_ver,coll_name,metric_guid) VALUES (p_target_type,p_type_meta_ver,p_coll_name,l_metric_guid) ; EXCEPTION -- Ingore duplicates WHEN DUP_VAL_ON_INDEX THEN NULL ; END ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('store_coll_item_metrics:Exit normal ',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN NO_DATA_FOUND THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('store_coll_item_metrics:Exit exception Invalid Metric', G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Invalid Metric/Target type/Valid if Combination ='||l_metric_name|| ' Valid Ifs='||p_category_prop_1||','|| p_category_prop_2||','|| p_category_prop_3||','|| p_category_prop_4||','|| p_category_prop_5||' (store_coll_item_metrics)') ; WHEN DUP_VAL_ON_INDEX THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('store_coll_item_metrics:Exit exception Duplicate Metric',G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Duplicate Metric/valid-if Combination Metric='||l_metric_name|| 'valid -ifs ='||p_category_prop_1||','|| p_category_prop_2||','|| p_category_prop_3||','|| p_category_prop_4||','|| p_category_prop_5||' (store_coll_item_metrics)') ; WHEN OTHERS THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('store_coll_item_metrics:Exit exception '||sqlerrm,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, sqlerrm||' (store_coll_item_metric)'); END store_coll_item_metrics; -- -- delete collection items -- PROCEDURE delete_coll_items(p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_coll_name IN VARCHAR2) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_coll_items:Enter',G_MODULE_NAME) ; END IF ; DELETE mgmt_coll_item_metrics WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = p_coll_name ; DELETE mgmt_coll_items WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = p_coll_name ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_coll_items:Exit normal',G_MODULE_NAME) ; END IF ; END delete_coll_items; -- -- Add Modify Delete Default collection items -- PROCEDURE add_mod_del_coll_items (p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2, p_metrics_list IN mgmt_metric_name_array DEFAULT NULL, p_coll_name IN VARCHAR2, p_op_code IN NUMBER := G_CREATE_COLL_ITEMS, p_valid_if_list IN mgmt_validif_array DEFAULT NULL, p_is_enabled IN NUMBER DEFAULT MGMT_GLOBAL.G_TRUE) IS l_valid_if_list mgmt_validif_array := mgmt_validif_array() ; l_catprop_1_choices MGMT_CATEGORY_PROP_ARRAY := MGMT_CATEGORY_PROP_ARRAY() ; l_catprop_2_choices MGMT_CATEGORY_PROP_ARRAY := MGMT_CATEGORY_PROP_ARRAY() ; l_catprop_3_choices MGMT_CATEGORY_PROP_ARRAY := MGMT_CATEGORY_PROP_ARRAY() ; l_catprop_4_choices MGMT_CATEGORY_PROP_ARRAY := MGMT_CATEGORY_PROP_ARRAY() ; l_catprop_5_choices MGMT_CATEGORY_PROP_ARRAY := MGMT_CATEGORY_PROP_ARRAY() ; l_dflt_catprop_choices MGMT_CATEGORY_PROP_ARRAY := MGMT_CATEGORY_PROP_ARRAY(' ') ; l_coll_items_deleted BOOLEAN := FALSE ; l_coll_items_count NUMBER := 0 ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_mod_del_coll_items:Enter',G_MODULE_NAME) ; END IF ; IF p_valid_if_list IS NULL OR p_valid_if_list.COUNT = 0 THEN IF p_op_code = G_CREATE_COLL_ITEMS THEN l_valid_if_list := MGMT_VALIDIF_ARRAY(MGMT_VALIDIF_OBJ.NEW()); ELSE -- for delete operations if valid-if list is not given -- we default to all valid-ifs for the collection item SELECT category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 BULK COLLECT INTO l_catprop_1_choices, l_catprop_2_choices, l_catprop_3_choices, l_catprop_4_choices, l_catprop_5_choices FROM mgmt_coll_items WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = p_coll_name ; IF SQL%NOTFOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'No collection items exist for the target type') ; END IF ; l_valid_if_list := MGMT_VALIDIF_ARRAY( MGMT_VALIDIF_OBJ.NEW( l_catprop_1_choices, l_catprop_2_choices, l_catprop_3_choices, l_catprop_4_choices, l_catprop_5_choices )) ; END IF ; ELSE l_valid_if_list := p_valid_if_list ; END IF ; -- Iterate through valid if list FOR vi_ctr IN l_valid_if_list.FIRST..l_valid_if_list.LAST LOOP l_catprop_1_choices := NVL(l_valid_if_list(vi_ctr).catprop_1_choices, l_dflt_catprop_choices); l_catprop_2_choices := NVL(l_valid_if_list(vi_ctr).catprop_2_choices, l_dflt_catprop_choices); l_catprop_3_choices := NVL(l_valid_if_list(vi_ctr).catprop_3_choices, l_dflt_catprop_choices); l_catprop_4_choices := NVL(l_valid_if_list(vi_ctr).catprop_4_choices, l_dflt_catprop_choices); l_catprop_5_choices := NVL(l_valid_if_list(vi_ctr).catprop_5_choices, l_dflt_catprop_choices); FOR cp1_ctr IN l_catprop_1_choices.FIRST..l_catprop_1_choices.LAST LOOP FOR cp2_ctr IN l_catprop_2_choices.FIRST..l_catprop_2_choices.LAST LOOP FOR cp3_ctr IN l_catprop_3_choices.FIRST..l_catprop_3_choices.LAST LOOP FOR cp4_ctr IN l_catprop_4_choices.FIRST..l_catprop_4_choices.LAST LOOP FOR cp5_ctr IN l_catprop_5_choices.FIRST..l_catprop_5_choices.LAST LOOP BEGIN IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('add_mod_del_coll_items:target type='|| p_target_type||'('||p_type_meta_ver|| ') Coll='||p_coll_name|| ' Catprop='|| l_catprop_1_choices(cp1_ctr)||','|| l_catprop_2_choices(cp2_ctr)|| l_catprop_3_choices(cp3_ctr)||','|| l_catprop_4_choices(cp4_ctr)|| l_catprop_5_choices(cp5_ctr),G_MODULE_NAME) ; END IF ; IF p_op_code = G_CREATE_COLL_ITEMS THEN INSERT INTO mgmt_coll_items (target_type,type_meta_ver,coll_name, category_prop_1,category_prop_2,category_prop_3, category_prop_4,category_prop_5,is_enabled) VALUES (p_target_type,p_type_meta_ver,p_coll_name, nvl(l_catprop_1_choices(cp1_ctr),' '), nvl(l_catprop_2_choices(cp2_ctr),' '), nvl(l_catprop_3_choices(cp3_ctr), ' '), nvl(l_catprop_4_choices(cp4_ctr),' '), nvl(l_catprop_5_choices(cp5_ctr),' '), nvl(p_is_enabled,1) ) ; store_coll_item_metrics( p_target_type=>p_target_type, p_type_meta_ver=>p_type_meta_ver, p_coll_name=>p_coll_name, p_metrics_list=>p_metrics_list, p_category_prop_1=>l_catprop_1_choices(cp1_ctr), p_category_prop_2=>l_catprop_2_choices(cp2_ctr), p_category_prop_3=>l_catprop_3_choices(cp3_ctr), p_category_prop_4=>l_catprop_4_choices(cp4_ctr), p_category_prop_5=>l_catprop_5_choices(cp5_ctr)) ; ELSIF p_op_code = G_DELETE_COLL_ITEMS THEN -- delete category specific collection items DELETE mgmt_coll_items WHERE target_type= p_target_type AND type_meta_ver=p_type_meta_ver AND coll_name = p_coll_name AND category_prop_1 = nvl(l_catprop_1_choices(cp1_ctr),' ') AND category_prop_2 = nvl(l_catprop_2_choices(cp2_ctr),' ') AND category_prop_3 = nvl(l_catprop_3_choices(cp3_ctr),' ') AND category_prop_4 = nvl(l_catprop_4_choices(cp4_ctr),' ') AND category_prop_5 = nvl(l_catprop_5_choices(cp5_ctr),' ') ; IF SQL%NOTFOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Collection Item to Deleted does not exist') ; END IF ; l_coll_items_deleted := TRUE ; -- enable/disable operations ELSE UPDATE mgmt_coll_items SET is_enabled = p_op_code WHERE target_type= p_target_type AND type_meta_ver=p_type_meta_ver AND coll_name = nvl(p_coll_name,coll_name) AND category_prop_1 = nvl(l_catprop_1_choices(cp1_ctr),' ') AND category_prop_2 = nvl(l_catprop_2_choices(cp2_ctr),' ') AND category_prop_3 = nvl(l_catprop_3_choices(cp3_ctr),' ') AND category_prop_4 = nvl(l_catprop_4_choices(cp4_ctr),' ') AND category_prop_5 = nvl(l_catprop_5_choices(cp5_ctr),' ') ; IF SQL%NOTFOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Collection Item to enabled/disabled does not exist') ; END IF ; END IF ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Duplicate Collection Item') ; WHEN OTHERS THEN IF SQLCODE BETWEEN G_CUSTOM_ERR_START AND G_CUSTOM_ERR_END THEN raise ; ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'ORA'||to_char(sqlcode)|| ' when creating default collection items') ; END IF ; END ; END LOOP ; END LOOP ; END LOOP ; END LOOP ; END LOOP ; END LOOP ; -- if all collection items valid-ifs have been deleted -- delete the metrics associated with the collection item IF l_coll_items_deleted THEN SELECT COUNT(*) INTO l_coll_items_count FROM mgmt_coll_items WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = p_coll_name ; IF l_coll_items_count = 0 THEN DELETE mgmt_coll_item_metrics WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = p_coll_name ; END IF ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('add_mod_del_coll_items:Exit normal',G_MODULE_NAME) ; END IF ; END add_mod_del_coll_items; PROCEDURE copy_default_collections(p_target_type IN VARCHAR2, p_from_type_meta_ver IN VARCHAR2, p_to_type_meta_ver IN VARCHAR2) IS l_from_guid mgmt_collections.object_guid%type ; l_to_guid mgmt_collections.object_guid%type ; l_table VARCHAr2(30) ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('copy_default_collections:Enter',G_MODULE_NAME) ; END IF ; l_table := 'mgmt_coll_items' ; INSERT INTO mgmt_coll_items ( target_type,type_meta_ver,coll_name, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5,is_enabled,is_required ) SELECT target_type,p_to_type_meta_ver,coll_name, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5,is_enabled,is_required FROM mgmt_coll_items WHERE target_type = p_target_type AND type_meta_ver = p_from_type_meta_ver ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('copy_default_collections:copied '||sql%rowcount|| ' row(s) to table='||l_table,G_MODULE_NAME) ; END IF ; l_table := 'mgmt_collections' ; INSERT INTO mgmt_collections ( object_guid, coll_name, object_type, is_enabled, schedule_ex, store_metric, upload_frequency, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days ) SELECT to_type.target_type_ver_guid, coll_name, object_type, is_enabled, schedule_ex, store_metric, upload_frequency, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days FROM mgmt_collections coll, mgmt_target_type_versions from_type, mgmt_target_type_versions to_type WHERE coll.object_guid = from_type.target_type_ver_guid AND coll.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_DEFAULT AND from_type.target_type = p_target_type AND from_type.type_meta_ver = p_from_type_meta_ver AND to_type.target_type = p_target_type AND to_type.type_meta_ver = p_to_type_meta_ver ; IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('copy_default_collections:copied '||sql%rowcount|| ' row(s) to table='||l_table,G_MODULE_NAME) ; END IF ; l_table := 'mgmt_coll_item_metrics' ; INSERT INTO mgmt_coll_item_metrics (target_type,type_meta_ver,coll_name,metric_guid) SELECT target_type,p_to_type_meta_ver,coll_name,metric_guid FROM mgmt_coll_item_metrics WHERE target_type = p_target_type AND type_meta_ver = p_from_type_meta_ver ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.DEBUG('copy_default_collections:copied '||sql%rowcount|| ' row(s) to table='||l_table,G_MODULE_NAME) ; EMDW_LOG.INFO('copy_default_collections:Exit',G_MODULE_NAME) ; END IF ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error(MGMT_GLOBAL.COLLECTION_ERR, 'Default collections already exist for '||p_target_type|| ' ('||p_to_type_meta_ver||') in '||l_table) ; END copy_default_collections ; -- -- Clear open metric errors -- By Default only errors are cleared. -- error_type=NULL will clear both warning and error types -- PROCEDURE clear_open_metric_errors (p_target_guid IN RAW, p_metric_guid IN RAW, p_collection_timestamp IN DATE DEFAULT NULL, p_coll_name IN VARCHAR2 DEFAULT NULL, p_agent_guid IN RAW DEFAULT NULL, p_error_type IN NUMBER DEFAULT MGMT_GLOBAL.G_METRIC_ERROR_TYPE_ERROR) IS l_metric_name mgmt_metrics.metric_name%type ; l_metric_guid mgmt_metrics.metric_guid%type ; l_metric_column mgmt_metrics.metric_column%type ; l_target_type mgmt_metrics.target_type%type ; l_collection_timestamp DATE := p_collection_timestamp; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('clear_open_metric_errors:Enter'|| ' target_guid='||p_target_guid|| ' metric_guid='||p_metric_guid,G_MODULE_NAME) ; END IF ; IF p_metric_guid IS NOT NULL THEN SELECT target_type,metric_name,metric_column INTO l_target_type,l_metric_name,l_metric_column FROM mgmt_metrics WHERE metric_guid = p_metric_guid AND ROWNUM = 1 ; -- If the input metric guid is table metric use it, else -- get the metric guid of the table metric IF l_metric_column = ' ' THEN l_metric_guid := p_metric_guid ; ELSE SELECT metric_guid INTO l_metric_guid FROM mgmt_metrics WHERE target_type = l_target_type AND metric_name = l_metric_name AND metric_column = ' ' AND ROWNUM = 1 ; END IF ; END IF ; -- Insert CLEAR only if error exists FOR rec IN ( SELECT * FROM mgmt_current_metric_errors WHERE target_guid = p_target_guid AND metric_guid = NVL(l_metric_guid,metric_guid) AND coll_name = NVL(p_coll_name,coll_name) AND agent_guid = NVL(p_agent_guid,agent_guid) AND ( p_error_type IS NULL OR metric_error_type = p_error_type) ) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('clear_open_metric_errors:Clearing Metric='|| rec.metric_guid||' Coll='||rec.coll_name|| ' Agent='||rec.agent_guid, G_MODULE_NAME) ; END IF ; l_collection_timestamp := NVL(l_collection_timestamp, mgmt_target.sysdate_target(p_target_guid)) ; INSERT INTO MGMT_METRIC_ERRORS (target_guid,metric_guid,coll_name, agent_guid,collection_timestamp,metric_error_message, metric_error_type) VALUES (rec.target_guid, rec.metric_guid, rec.coll_name, rec.agent_guid, l_collection_timestamp,NULL, rec.metric_error_type) ; END LOOP ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('clear_open_metric_errors:Exit', G_MODULE_NAME) ; END IF ; END clear_open_metric_errors ; -- -- Utility function to log metric errors -- PROCEDURE log_metric_errors (p_target_guid IN RAW, p_metric_guid IN RAW, p_error_message IN VARCHAR2, p_collection_timestamp IN DATE DEFAULT NULL, p_coll_name IN VARCHAR2 DEFAULT ' ', p_agent_guid IN RAW DEFAULT MGMT_GLOBAL.G_ALL_ZERO_GUID, p_metric_error_type IN NUMBER DEFAULT MGMT_GLOBAL.G_METRIC_ERROR_TYPE_ERROR ) IS l_collection_timestamp DATE := NVL(p_collection_timestamp, mgmt_target.sysdate_target(p_target_guid)) ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('log_metric_errors:Enter'|| ' target_guid='||p_target_guid|| ' metric_guid='||p_metric_guid|| ' error='||p_error_message,G_MODULE_NAME) ; END IF ; INSERT INTO MGMT_METRIC_ERRORS (target_guid,metric_guid,coll_name, agent_guid,collection_timestamp,metric_error_message, metric_error_type) VALUES (p_target_guid, p_metric_guid, p_coll_name, p_agent_guid, l_collection_timestamp,p_error_message, p_metric_error_type) ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('log_metric_errors:Exit',G_MODULE_NAME) ; END IF ; END log_metric_errors ; -- -- Create collection properties -- PROCEDURE create_coll_properties(p_object_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_object_type IN NUMBER, p_coll_properties IN mgmt_namevalue_array ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('create_coll_properties:Enter',G_MODULE_NAME) ; END IF ; IF p_coll_properties IS NOT NULL AND p_coll_properties.COUNT >0 THEN FOR i in p_coll_properties.FIRST..p_coll_properties.LAST LOOP BEGIN INSERT INTO MGMT_COLL_ITEM_PROPERTIES (object_guid, metric_guid, object_type, coll_name, property_name,property_value) VALUES (p_object_guid, p_metric_guid, p_object_type, p_coll_name, p_coll_properties(i).name, p_coll_properties(i).value) ; NULL ; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Duplicate collection property - '||p_coll_properties(i).name) ; END ; END LOOP ; END IF ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('create_coll_properties:Exit normal',G_MODULE_NAME) ; END IF ; END create_coll_properties; -- -- add one collection property -- PROCEDURE add_coll_item_property( p_object_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_object_type IN NUMBER, p_property_name IN VARCHAR2, p_property_value IN VARCHAR2) IS BEGIN INSERT INTO mgmt_coll_item_properties (object_guid, metric_guid, coll_name, object_type, property_name, property_value) VALUES (p_object_guid, p_metric_guid, p_coll_name, p_object_type, p_property_name, p_property_value); END add_coll_item_property; -- -- Update single collection property -- PROCEDURE update_coll_item_property( p_object_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_property_name IN VARCHAR2, p_property_value IN VARCHAR2) IS BEGIN UPDATE mgmt_coll_item_properties SET property_value = p_property_value WHERE object_guid = p_object_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND property_name = p_property_name; END update_coll_item_property; -- -- get collection property -- FUNCTION get_coll_item_property( p_object_guid IN RAW, p_object_type IN NUMBER, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_property_name IN VARCHAR2) RETURN MGMT_COLL_ITEM_PROPERTIES.property_value%TYPE IS l_value MGMT_COLL_ITEM_PROPERTIES.property_value%TYPE; BEGIN SELECT property_value INTO l_value FROM mgmt_coll_item_properties WHERE object_guid = p_object_guid AND object_type = p_object_type AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND property_name = p_property_name; RETURN l_value; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END get_coll_item_property; -- -- Delete single collection property -- PROCEDURE delete_coll_item_property( p_object_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_property_name IN VARCHAR2) IS BEGIN DELETE FROM mgmt_coll_item_properties WHERE object_guid = p_object_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND property_name = p_property_name; END delete_coll_item_property; -- -- delete collection properties -- PROCEDURE delete_coll_properties(p_object_guid IN RAW, p_object_type IN NUMBER, p_metric_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL ) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_coll_properties:Enter',G_MODULE_NAME) ; END IF ; IF (p_coll_name IS NOT NULL) THEN DELETE mgmt_coll_item_properties WHERE object_guid = p_object_guid AND metric_guid = p_metric_guid AND object_type = p_object_type AND coll_name = p_coll_name ; ELSE DELETE mgmt_coll_item_properties WHERE object_guid = p_object_guid AND metric_guid = p_metric_guid AND object_type = p_object_type; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_coll_properties:Exit normal',G_MODULE_NAME) ; END IF ; END delete_coll_properties; -- -- Remove the repository collection task -- Only removes if this task is not there in -- in mgmt_metric_collections -- PROCEDURE remove_repo_task(p_task_id IN NUMBER) IS l_count NUMBER ; BEGIN BEGIN EM_TASK.lock_task(p_task_id) ; EXCEPTION WHEN MGMT_GLOBAL.invalid_params THEN RETURN ; END ; -- -- remove the repo collection task only IFF there are -- no other collections associated with the task -- SELECT COUNT(*) INTO l_count FROM mgmt_collection_metric_tasks WHERE task_id = p_task_id ; IF l_count = 0 THEN EM_TASK.remove_task(p_task_id) ; END IF ; END remove_repo_task; -- -- delete_collection -- Removes all collection related information for a given collection and metric -- PROCEDURE delete_metric_collection_int (p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT NULL, p_clear_alerts IN NUMBER DEFAULT MGMT_GLOBAL.G_FALSE) IS l_metric_guids MGMT_TARGET_GUID_ARRAY; l_proc_name CONSTANT VARCHAR2(40) := 'delete_metric_collection_int : ' ; l_coll_name mgmt_collections.coll_name%TYPE ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Enter' || ' p_target_guid = [' || p_target_guid || ']' || ' p_metric_guid = [' || p_metric_guid || ']' || ' p_coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; -- the metric guid passed could be the table descriptor metric -- we need to get all metric column guids. SELECT DISTINCT metric_guid BULK COLLECT INTO l_metric_guids FROM mgmt_metrics met WHERE (met.target_type,met.metric_name) IN (SELECT met_in.target_type, met_in.metric_name FROM mgmt_metrics met_in WHERE met_in.metric_guid = p_metric_guid AND ROWNUM = 1 ) AND key_order = 0 ; IF p_clear_alerts = MGMT_GLOBAL.G_TRUE THEN FOR i IN l_metric_guids.FIRST..l_metric_guids.LAST LOOP EM_SEVERITY.clear_alerts (p_target_guid => p_target_guid, p_policy_guid => l_metric_guids(i), p_cfg_coll_name => p_coll_name, p_is_metric => TRUE, p_clear_message => EM_SEVERITY.G_CLR_METRIC_DISABLED_MSG, p_clear_nlsid => EM_SEVERITY.G_CLR_METRIC_DISABLED_NLSID ) ; END LOOP; END IF ; -- The following delete sequence has to be maintained to -- avoid deadlocks between agent uploads and console updates BEGIN SELECT coll_name INTO l_coll_name FROM mgmt_collections WHERE object_guid = p_target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND coll_name = p_coll_name FOR UPDATE ; EXCEPTION WHEN NO_DATA_FOUND THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name ||': Exit no collection',G_MODULE_NAME); END IF ; RETURN ; END ; -- Delete from mgmt_collection_metric_tasks FORALL i IN l_metric_guids.FIRST..l_metric_guids.LAST DELETE mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND metric_guid = l_metric_guids(i) AND coll_name = p_coll_name; -- Delete from mgmt_collections table only iff all the entries -- are gone from metric collections table DELETE mgmt_collections WHERE object_guid = p_target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND coll_name = p_coll_name AND NOT EXISTS ( SELECT NULL FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND coll_name = p_coll_name ); -- Delete from collection properties FORALL i IN l_metric_guids.FIRST..l_metric_guids.LAST DELETE FROM mgmt_coll_item_properties WHERE object_guid = p_target_guid AND metric_guid = l_metric_guids(i) AND coll_name = p_coll_name; -- Get the list of policies for which configurations have to be removed FOR rec IN (SELECT policy_guid FROM mgmt_policies WHERE metric_guid IN (SELECT * FROM TABLE(CAST(l_metric_guids AS MGMT_TARGET_GUID_ARRAY))) ORDER BY policy_guid ) LOOP IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.INFO(l_proc_name || 'Removing policy assocs for ' || ' target_guid = [' || p_target_guid || ']' || ' policy_guid = [' || rec.policy_guid || ']' || ' coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; -- Remove target-metric/policy associations, and decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => p_target_guid, p_policy_guid => rec.policy_guid, p_coll_name => p_coll_name, p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); END LOOP; -- Delete collection credentials also. -- TODO: Currently, there is no proper API to delete these -- So, directly deleting from table FORALL i IN l_metric_guids.FIRST..l_metric_guids.LAST DELETE FROM mgmt_credentials2 WHERE credential_guid IN (SELECT credential_guid FROM mgmt_collection_credentials WHERE target_guid = p_target_guid AND metric_guid = l_metric_guids(i) AND coll_name = p_coll_name); FORALL i IN l_metric_guids.FIRST..l_metric_guids.LAST DELETE FROM mgmt_collection_credentials WHERE target_guid = p_target_guid AND metric_guid = l_metric_guids(i) AND coll_name = p_coll_name; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Exit' , G_MODULE_NAME) ; END IF ; END delete_metric_collection_int; -- delete_collection -- Removes all collection related information for a given collection PROCEDURE delete_collection_int(p_target_guid RAW, p_coll_name VARCHAR2 DEFAULT NULL, p_clear_alerts NUMBER DEFAULT 1) IS l_metric_guids MGMT_TARGET_GUID_ARRAY; l_policy_guids MGMT_TARGET_GUID_ARRAY; l_proc_name CONSTANT VARCHAR2(30) := 'delete_collection_int : ' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Enter' || ' p_target_guid = [' || p_target_guid || ']' || ' p_coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; -- The following delete sequence has to be maintained to -- avoid deadlocks between agent uploads and console updates SELECT metric_guid BULK COLLECT INTO l_metric_guids FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND coll_name = p_coll_name; -- clear all alerts associated with the collection IF (p_clear_alerts = MGMT_GLOBAL.G_TRUE) THEN EM_COLL_UTIL.run_disable_steps(p_target_guid=>p_target_guid, p_coll_name =>p_coll_name) ; END IF; -- Delete from mgmt_collections table DELETE FROM mgmt_collections WHERE object_guid = p_target_guid AND object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET AND coll_name = p_coll_name; -- Delete from mgmt_collection_metric_tasks DELETE FROM mgmt_collection_metric_tasks WHERE target_guid = p_target_guid AND coll_name = p_coll_name; -- Delete from collection properties DELETE FROM mgmt_coll_item_properties WHERE object_guid = p_target_guid AND metric_guid IN (SELECT * FROM TABLE(CAST(l_metric_guids AS MGMT_TARGET_GUID_ARRAY))) AND coll_name = p_coll_name; -- Get the list of policies for which configurations have to be removed -- As an optimization, we can query from mgmt_policy_assoc and select only -- active associations SELECT policy_guid BULK COLLECT INTO l_policy_guids FROM mgmt_policies WHERE metric_guid IN (SELECT * FROM TABLE(CAST(l_metric_guids AS MGMT_TARGET_GUID_ARRAY))) ORDER BY policy_guid; -- For each metric column/policy, delete the policy setup IF (l_policy_guids IS NOT NULL) AND (l_policy_guids.COUNT > 0) THEN FOR pol_ctr IN l_policy_guids.FIRST..l_policy_guids.LAST LOOP IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || 'Removing policy assocs for ' || ' target_guid = [' || p_target_guid || ']' || ' policy_guid = [' || l_policy_guids(pol_ctr) || ']' || ' coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; -- Remove target-metric/policy associations, and decrement CA counters EM_POLICY.remove_object_policy_assoc( p_object_guid => p_target_guid, p_policy_guid => l_policy_guids(pol_ctr), p_coll_name => p_coll_name, p_remove_ca_assoc => MGMT_GLOBAL.G_TRUE); END LOOP; END IF; -- Delete collection credentials also. -- TODO: Currently, there is no proper API to delete these -- So, directly deleting from table DELETE FROM mgmt_credentials2 WHERE credential_guid IN (SELECT credential_guid FROM mgmt_collection_credentials WHERE target_guid = p_target_guid AND metric_guid IN (SELECT * FROM TABLE(CAST(l_metric_guids AS MGMT_TARGET_GUID_ARRAY))) AND coll_name = p_coll_name); DELETE FROM mgmt_collection_credentials WHERE target_guid = p_target_guid AND metric_guid IN (SELECT * FROM TABLE(CAST(l_metric_guids AS MGMT_TARGET_GUID_ARRAY))) AND coll_name = p_coll_name; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Exit' || ' p_target_guid = [' || p_target_guid || ']' || ' p_coll_name = [' || p_coll_name || ']', G_MODULE_NAME) ; END IF ; END delete_collection_int; -- handle_target_delete -- This target deleteion callback -- removes collections related information for the given target -- -- Parameters: -- p_target_name: The Name of the target -- p_target_type: The type of the target -- p_target_guid: The GUID of the target -- -- NOTES: -- The registered callback to delete collection information PROCEDURE handle_target_delete(p_target_name VARCHAR2, p_target_type VARCHAR2, p_target_guid RAW) IS l_target_guid mgmt_targets.target_guid%type ; BEGIN --l_target_guid := mgmt_target.generate_target_guid(p_target_name=>p_target_name, -- p_target_type=>p_target_type) ; remove_all_object_collections(p_target_guid, MGMT_GLOBAL.G_OBJECT_TYPE_TARGET); END handle_target_delete; -- add object collections -- Adds collections related information for the given object -- -- Parameters: -- p_object_guid: The GUID identifying the object -- p_object_type: The type of object -- p_coll_list : The list of collections to be added PROCEDURE add_object_collections( p_target_type IN VARCHAR2, p_object_guid IN RAW, p_object_type IN NUMBER, p_coll_list IN MGMT_MNTR_COLLECTION_ARRAY DEFAULT NULL) IS l_metric_guid MGMT_METRICS.metric_guid%TYPE; l_coll_obj MGMT_MNTR_COLLECTION; l_coll_metric MGMT_COLL_METRIC; l_coll_prop MGMT_COLL_PROP; l_target_name MGMT_TARGETS.target_name%TYPE; l_cred_rec MGMT_CRED_RECORD; l_coll_cred_array MGMT_COLLECTION_CRED_ARRAY; l_task_id mgmt_collection_metric_tasks.task_id%TYPE; l_cat_props mgmt_short_string_array; l_tz_rgn mgmt_targets.timezone_region%TYPE; l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; l_metric_guids mgmt_target_guid_array ; l_metric_names mgmt_short_string_array ; l_rep_flags mgmt_integer_array ; l_source_types mgmt_integer_array ; l_proc_name CONSTANT VARCHAR2(30) := 'add_object_collections: ' ; TYPE met_array IS TABLE OF PLS_INTEGER INDEX BY mgmt_metrics.metric_name%TYPE ; l_metric_list met_array ; l_metric_list_index NUMBER ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Enter' || ' target_type = [' || p_target_type || ']' || ' object_guid = [' || p_object_guid || ']' || ' object_type = [' || p_object_type || ']', G_MODULE_NAME) ; END IF ; IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN -- Get targets tzrgn and catprops required for allocating task SELECT target_name,type_meta_ver,timezone_region, mgmt_short_string_array( category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5) INTO l_target_name,l_type_meta_ver,l_tz_rgn, l_cat_props FROM mgmt_targets WHERE target_guid = p_object_guid; END IF; IF ( (p_coll_list IS NOT NULL) AND (p_coll_list.COUNT > 0) )THEN IF p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET THEN SELECT metric_guid,metric_name,is_repository,source_type BULK COLLECT INTO l_metric_guids,l_metric_names, l_rep_flags,l_source_types FROM MGMT_METRICS m WHERE m.target_type = p_target_type AND m.type_meta_ver = l_type_meta_ver AND (m.category_prop_1 = l_cat_props(1) OR m.category_prop_1 = ' ') AND (m.category_prop_2 = l_cat_props(2) OR m.category_prop_2 = ' ') AND (m.category_prop_3 = l_cat_props(3) OR m.category_prop_3 = ' ') AND (m.category_prop_4 = l_cat_props(4) OR m.category_prop_4 = ' ') AND (m.category_prop_5 = l_cat_props(5) OR m.category_prop_5 = ' ') AND metric_column = ' ' ; ELSE SELECT DISTINCT metric_guid,metric_name BULK COLLECT INTO l_metric_guids,l_metric_names FROM mgmt_metrics WHERE target_type = p_target_type AND metric_column = ' ' ; END IF ; -- Build a associative array indexed by metric name FOR i IN 1..l_metric_names.COUNT LOOP l_metric_list(l_metric_names(i)) := i ; END LOOP ; FOR coll_ctr IN p_coll_list.FIRST..p_coll_list.LAST LOOP l_coll_obj := p_coll_list(coll_ctr); IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Adding collection rec ' || ' object_guid = [' || p_object_guid || ']' || ' object_type = [' || p_object_type || ']' || ' coll_name = [' || l_coll_obj.coll_name || ']', G_MODULE_NAME) ; END IF ; EM_COLL_UTIL.add_collection_rec ( p_object_guid => p_object_guid, p_object_type => p_object_type, p_coll_name => l_coll_obj.coll_name, p_schedule_ex => l_coll_obj.schedule_ex, p_schedule => l_coll_obj.schedule, p_store_metric => l_coll_obj.store_metric, p_upload_frequency => l_coll_obj.upload_frequency, p_is_enabled => l_coll_obj.is_enabled); IF ( (l_coll_obj.metric_list IS NOT NULL) AND (l_coll_obj.metric_list.COUNT > 0) )THEN FOR met_ctr IN l_coll_obj.metric_list.FIRST..l_coll_obj.metric_list.LAST LOOP l_coll_metric := l_coll_obj.metric_list(met_ctr); BEGIN l_metric_list_index := l_metric_list(l_coll_metric.metric_name) ; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.NO_SUCH_METRIC_ERR, 'Metric not found. target_type ' || p_target_type || ' metric_name = ' || l_coll_metric.metric_name || ' metric_column = ') ; END ; l_metric_guid := l_metric_guids(l_metric_list_index) ; l_task_id := NULL; -- For target objects create a repo task, if the metric is repo metric IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Get metric for guid ' || ' metric_guid = [' || l_metric_guid || ']' || ' metric_name = [' || l_coll_metric.metric_name || ']' || ' is_repository = [' || l_rep_flags(l_metric_list_index) || ']', G_MODULE_NAME) ; END IF ; IF (l_rep_flags(l_metric_list_index) = MGMT_GLOBAL.G_TRUE AND l_coll_obj.is_enabled = MGMT_GLOBAL.G_TRUE) THEN l_task_id := allocate_task( p_target_guid => p_object_guid, p_metric_guid => l_metric_guid, p_coll_name => l_coll_obj.coll_name, p_cat_prop_array => l_cat_props, p_coll_schedule => l_coll_obj.schedule, p_source_type => l_source_types(l_metric_list_index), p_timezone_region => l_tz_rgn); END IF; END IF; EM_COLL_UTIL.add_collection_metric_task( p_target_guid => p_object_guid, p_metric_guid => l_metric_guid, p_coll_name => l_coll_obj.coll_name, p_task_id => l_task_id); -- Add collection properties.. IF ( (l_coll_metric.property_list IS NOT NULL) AND (l_coll_metric.property_list.COUNT > 0) )THEN FOR prop_ctr IN l_coll_metric.property_list.FIRST..l_coll_metric.property_list.LAST LOOP l_coll_prop := l_coll_metric.property_list(prop_ctr); EM_COLL_UTIL.add_coll_item_property( p_object_guid => p_object_guid, p_metric_guid => l_metric_guid, p_coll_name => l_coll_obj.coll_name, p_object_type => p_object_type, p_property_name => l_coll_prop.name, p_property_value => l_coll_prop.value); END LOOP; END IF; -- property_list is NOT NULL -- NOTE: credentials set only for target objects IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN IF (l_coll_metric.credentials IS NOT NULL) AND (l_coll_metric.credentials.COUNT > 0) THEN -- TODO: Cutover to use object API for credentials FOR cred_ctr IN l_coll_metric.credentials.FIRST..l_coll_metric.credentials.LAST LOOP l_cred_rec := l_coll_metric.credentials(cred_ctr); l_coll_cred_array := MGMT_COLLECTION_CRED_ARRAY ( MGMT_COLLECTION_CRED_RECORD.NEW(l_target_name, p_target_type, l_coll_metric.metric_name, ' ', l_coll_obj.coll_name, l_cred_rec) ); MGMT_CREDENTIAL.set_collection_credentials(l_coll_cred_array); END LOOP; END IF; END IF; -- object = TARGET END LOOP; END IF; -- metric_list is NOT NULL END LOOP; END IF; END add_object_collections; -- remove object collections -- Removes collections related information for the given object -- NOTE: -- This remove excludes collections for remote and multi-column UDM metrics -- -- Parameters: -- p_object_guid: The GUID identifying the object -- p_object_type: The type of object PROCEDURE remove_object_collections( p_object_guid IN RAW, p_object_type IN NUMBER) IS l_coll_count NUMBER := 0; l_coll_names MGMT_SHORT_STRING_ARRAY; l_tasks_count NUMBER := 0; l_props_count NUMBER := 0; l_creds2_count NUMBER := 0; l_coll_cred_count NUMBER := 0; l_proc_name CONSTANT VARCHAR2(30) := 'remove_object_collections ' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Enter' || ' object_guid = [' || p_object_guid || ']' || ' object_type = [' || p_object_type || ']', G_MODULE_NAME) ; END IF ; -- Get the list of collections that are managed by the -- Metric and Policy settings or templates -- These are collections on non-remote and non-multi-column UDM metrics -- as well as repository metrics that are created with a UDP policy SELECT coll_name BULK COLLECT INTO l_coll_names FROM ( SELECT cmt.coll_name FROM mgmt_collection_metric_tasks cmt, mgmt_metrics m WHERE NVL(m.remote, 0) = 0 AND cmt.target_guid = p_object_guid AND cmt.metric_guid = m.metric_guid UNION ( SELECT c.coll_name FROM mgmt_collections c WHERE c.object_guid = p_object_guid AND c.object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET MINUS SELECT coll_name FROM mgmt_collection_metric_tasks cmt WHERE cmt.target_guid = p_object_guid) ) ORDER BY coll_name; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Valid col list ' || l_coll_names.COUNT, G_MODULE_NAME) ; END IF ; -- Delete collection entries DELETE FROM mgmt_collections WHERE object_guid = p_object_guid AND object_type = p_object_type AND coll_name IN (SELECT column_value FROM TABLE(CAST(l_coll_names AS MGMT_SHORT_STRING_ARRAY))); l_coll_count := SQL%ROWCOUNT ; DELETE mgmt_collection_metric_tasks WHERE target_guid = p_object_guid AND coll_name IN (SELECT column_value FROM TABLE(CAST(l_coll_names AS MGMT_SHORT_STRING_ARRAY))); l_tasks_count := SQL%ROWCOUNT ; -- Delete collection properties DELETE FROM mgmt_coll_item_properties WHERE object_guid = p_object_guid AND object_type = p_object_type AND coll_name IN (SELECT column_value FROM TABLE(CAST(l_coll_names AS MGMT_SHORT_STRING_ARRAY))); l_props_count := SQL%ROWCOUNT ; IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN DELETE FROM mgmt_credentials2 WHERE credential_guid IN (SELECT credential_guid FROM mgmt_collection_credentials WHERE target_guid = p_object_guid AND coll_name IN (SELECT column_value FROM TABLE(CAST(l_coll_names AS MGMT_SHORT_STRING_ARRAY)))); l_creds2_count := SQL%ROWCOUNT; DELETE FROM mgmt_collection_credentials WHERE target_guid = p_object_guid AND coll_name IN (SELECT column_value FROM TABLE(CAST(l_coll_names AS MGMT_SHORT_STRING_ARRAY))); l_coll_cred_count := SQL%ROWCOUNT; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ' Exit Delete count.' || ' Props='|| l_props_count || ' Tasks='|| l_tasks_count || ' Collections='|| l_coll_count || ' Cred2='|| l_creds2_count || ' coll cred='|| l_coll_cred_count , G_MODULE_NAME) ; END IF ; END remove_object_collections; -- remove all object collections -- Removes all collections related information for the given object -- -- Parameters: -- p_object_guid: The GUID identifying the object -- p_object_type: The type of object PROCEDURE remove_all_object_collections( p_object_guid IN RAW, p_object_type IN NUMBER) IS l_coll_count NUMBER := 0; l_tasks_count NUMBER := 0; l_props_count NUMBER := 0; l_creds2_count NUMBER := 0; l_coll_cred_count NUMBER := 0; l_proc_name CONSTANT VARCHAR2(30) := 'remove_all_object_collections ' ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ': Enter' || ' object_guid = [' || p_object_guid || ']' || ' object_type = [' || p_object_type || ']', G_MODULE_NAME) ; END IF ; -- The deletes have to be done in the following sequence to avoid deadlocks -- MGMT_COLLECTIONS -- MGMT_COLECTION_METRIC_TASKS -- MGMT_COLL_ITEM_PROPERTIES -- Delete collection entries DELETE FROM mgmt_collections WHERE object_guid = p_object_guid AND object_type = p_object_type; l_coll_count := SQL%ROWCOUNT ; DELETE mgmt_collection_metric_tasks WHERE target_guid = p_object_guid ; l_tasks_count := SQL%ROWCOUNT ; -- Delete collection properties DELETE FROM mgmt_coll_item_properties WHERE object_guid = p_object_guid AND object_type = p_object_type; l_props_count := SQL%ROWCOUNT ; IF (p_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN DELETE FROM mgmt_credentials2 WHERE credential_guid IN (SELECT credential_guid FROM mgmt_collection_credentials WHERE target_guid = p_object_guid); l_creds2_count := SQL%ROWCOUNT; DELETE FROM mgmt_collection_credentials WHERE target_guid = p_object_guid; l_coll_cred_count := SQL%ROWCOUNT; END IF; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO(l_proc_name || ' Exit Delete count.' || ' Props='|| l_props_count || ' Tasks='|| l_tasks_count || ' Collections='|| l_coll_count || ' Cred2='|| l_creds2_count || ' coll cred='|| l_coll_cred_count , G_MODULE_NAME) ; END IF ; END remove_all_object_collections; PROCEDURE copy_object_coll( p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dest_object_guid IN RAW, p_dest_object_type IN NUMBER, p_src_coll_name IN VARCHAR2, p_dest_coll_name IN VARCHAR2) IS BEGIN -- Copy collection FOR crec IN (SELECT p_dest_object_guid, coll_name, p_dest_object_type, is_enabled, schedule_ex, store_metric, upload_frequency, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days FROM mgmt_collections WHERE object_guid = p_src_object_guid AND object_type = p_src_object_type AND coll_name = p_src_coll_name) LOOP BEGIN INSERT INTO mgmt_collections (object_guid, coll_name, object_type, is_enabled, schedule_ex, store_metric, upload_frequency, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days) VALUES (p_dest_object_guid, p_dest_coll_name, p_dest_object_type, crec.is_enabled, crec.schedule_ex, crec.store_metric, crec.upload_frequency, crec.frequency_code, crec.start_time, crec.end_time, crec.execution_hours, crec.execution_minutes, crec.interval, crec.months, crec.days); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Ignore duplicate exceptions NULL; END; END LOOP; -- Copy metric collection tasks FOR cmt_rec IN (SELECT cmt.metric_guid, cmt.coll_name FROM mgmt_collection_metric_tasks cmt WHERE cmt.target_guid = p_src_object_guid AND cmt.coll_name = p_src_coll_name ORDER BY cmt.metric_guid) LOOP BEGIN INSERT INTO MGMT_COLLECTION_METRIC_TASKS (target_guid, metric_guid, coll_name, task_id) VALUES (p_dest_object_guid, cmt_rec.metric_guid, p_dest_coll_name, NULL); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Ignore duplicate exceptions NULL; END; END LOOP; -- Copy collection properties FOR cprop_rec IN (SELECT metric_guid, property_name, property_value FROM mgmt_coll_item_properties WHERE object_guid = p_src_object_guid AND object_type = p_src_object_type AND coll_name = p_src_coll_name ORDER BY metric_guid, property_name) LOOP BEGIN INSERT INTO mgmt_coll_item_properties (object_guid, metric_guid, coll_name, object_type, property_name, property_value) VALUES (p_dest_object_guid, cprop_rec.metric_guid, p_dest_coll_name, p_dest_object_type, cprop_rec.property_name, cprop_rec.property_value); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN -- Ignore duplicate exceptions NULL; END; END LOOP; END copy_object_coll; -- copy_object_collections -- To copy collections from one object type to another. -- This procedure copies the collections, collection_metric tasks, -- collection properties and collection credentials -- Parameters: -- p_src_object_guid: The GUID identifying the source -- p_src_object_type: The type of source -- p_dest_object_guid: The GUID identifying the destination -- p_dest_object_type: The type of destination PROCEDURE copy_object_collections( p_src_object_guid IN RAW, p_src_object_type IN NUMBER, p_dest_object_guid IN RAW, p_dest_object_type IN NUMBER) IS l_met_info EM_METRIC.METRIC_INFO_REC; l_task_id mgmt_collection_metric_tasks.task_id%TYPE; l_cat_props mgmt_short_string_array; l_tz_rgn mgmt_targets.timezone_region%TYPE; l_coll_sched mgmt_coll_schedule_obj; BEGIN -- Copy mgmt_collections INSERT INTO mgmt_collections (object_guid, coll_name, object_type, is_enabled, schedule_ex, store_metric, upload_frequency, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days) SELECT p_dest_object_guid, coll_name, p_dest_object_type, is_enabled, schedule_ex, store_metric, upload_frequency, frequency_code, start_time, end_time, execution_hours, execution_minutes, interval, months, days FROM mgmt_collections WHERE object_guid = p_src_object_guid AND object_type = p_src_object_type; -- Start the repo collections, if required. IF (p_dest_object_type = MGMT_GLOBAL.G_OBJECT_TYPE_TARGET) THEN -- Get targets tzrgn and catprops required for allocating task SELECT timezone_region, mgmt_short_string_array( category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5) INTO l_tz_rgn, l_cat_props FROM mgmt_targets WHERE target_guid = p_dest_object_guid; -- Iterate through the metric tasks and add them FOR met_tsk_rec IN (SELECT cmt.metric_guid, cmt.coll_name, c.frequency_code, c.execution_hours, c.execution_minutes, c.interval, c.months, c.days, c.start_time, c.end_time FROM mgmt_collections c, mgmt_collection_metric_tasks cmt WHERE c.object_guid = p_src_object_guid AND c.object_type = p_src_object_type AND c.object_guid = cmt.target_guid AND c.coll_name = cmt.coll_name) LOOP -- Get metric details EM_METRIC.get_metric_info_for_target( p_metric_guid => met_tsk_rec.metric_guid, p_target_guid => p_dest_object_guid, p_metric_info => l_met_info); IF (l_met_info.is_repository = MGMT_GLOBAL.G_TRUE) THEN l_coll_sched := mgmt_coll_schedule_obj.new( p_frequency_code => met_tsk_rec.frequency_code, p_execution_hours => met_tsk_rec.execution_hours, p_execution_minutes => met_tsk_rec.execution_minutes, p_interval => met_tsk_rec.interval, p_months => met_tsk_rec.months, p_days => met_tsk_rec.days, p_start_time => met_tsk_rec.start_time, p_end_time => met_tsk_rec.end_time); l_task_id := allocate_task( p_target_guid => p_dest_object_guid, p_metric_guid => l_met_info.metric_guid, p_coll_name => met_tsk_rec.coll_name, p_cat_prop_array => l_cat_props, p_coll_schedule => l_coll_sched, p_source_type => l_met_info.source_type, p_timezone_region => l_tz_rgn); END IF; EM_COLL_UTIL.add_collection_metric_task( p_target_guid => p_dest_object_guid, p_metric_guid => l_met_info.metric_guid, p_coll_name => met_tsk_rec.coll_name, p_task_id => l_task_id); END LOOP; ELSE INSERT INTO MGMT_COLLECTION_METRIC_TASKS (target_guid, metric_guid, coll_name, task_id) SELECT p_dest_object_guid, cmt.metric_guid, cmt.coll_name, NULL FROM mgmt_collections c, mgmt_collection_metric_tasks cmt WHERE c.object_guid = p_src_object_guid AND c.object_type = p_src_object_type AND c.object_guid = cmt.target_guid AND c.coll_name = cmt.coll_name; END IF; -- Copy collection properties INSERT INTO mgmt_coll_item_properties (object_guid, metric_guid, coll_name, object_type, property_name, property_value) SELECT p_dest_object_guid, cim.metric_guid, cim.coll_name, p_dest_object_type, cim.property_name, cim.property_value FROM mgmt_collections c, mgmt_coll_item_properties cim WHERE c.object_guid = p_src_object_guid AND c.object_type = p_src_object_type AND c.object_guid = cim.object_guid AND c.object_type = cim.object_type AND c.coll_name = cim.coll_name; END copy_object_collections; -- run_snapshot_dependent_collections. -- To be called when snapshots are refreshed. This procedure looks at the snapshot-metric dependency MAP and determines -- which metrics (repository based) select from data populated by the snapshot. Since this indicates that the underlying -- data may have changed, those metrics' collections are run. -- NOTE: this procedure commits before exiting and does not though any exceptions. -- Parameters: -- p_snapshotType: The snapshot type (or name) that has just been refreshed. -- p_targetType: Needed along with the snapshot type to uniquely identify the snapshot that was refreshed. -- p_targetName: The snapshot was refreshed for this target. -- PROCEDURE run_snapshot_dependent_colls( p_snapshotType VARCHAR2, p_targetType VARCHAR2, p_targetName VARCHAR2 ) IS l_task_id mgmt_collection_tasks.task_id%TYPE ; l_target_guid mgmt_targets.target_guid%type ; l_type_meta_ver mgmt_targets.type_meta_ver%type ; l_cat_prop_1 mgmt_targets.category_prop_1%type ; l_cat_prop_2 mgmt_targets.category_prop_2%type ; l_cat_prop_3 mgmt_targets.category_prop_3%type ; l_cat_prop_4 mgmt_targets.category_prop_4%type ; l_cat_prop_5 mgmt_targets.category_prop_5%type ; l_metric_count NUMBER := 0 ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_snapshot_dependent_colls:Enter'|| 'snapshot=>'||p_snapshotType|| 'target name=>'||p_targetName,G_MODULE_NAME) ; END IF ; -- Get the list of metrics based on snapshot-metric-map -- Get list of targets which share the same agent as the current -- target. -- Combine the two lists to get list of metrics and targets. --For example: --if we have agent A1 monitoring host H1 --and Databases D1 and D2. --Snapshot map Contains metric MD1 of target type oracle_database --Snapshot map Contains metric MH1 of target type host -- --This procedure is called with H1,Host as parameter. -- --Metric MD1 will be collected for databases D1 and D2. --Metric HD1 will be collected for H1 -- BEGIN SELECT target_guid, type_meta_ver, category_prop_1, category_prop_2, category_prop_3, category_prop_4, category_prop_5 INTO l_target_guid, l_type_meta_ver, l_cat_prop_1, l_cat_prop_2, l_cat_prop_3, l_cat_prop_4, l_cat_prop_5 FROM mgmt_targets WHERE target_name = p_targetName AND target_type = p_targetType ; WITH target_list AS ( -- list of targets hosted by this target SELECT tgt.target_name, tgt.target_type, tgt.type_meta_ver, tgt.category_prop_1, tgt.category_prop_2, tgt.category_prop_3, tgt.category_prop_4, tgt.category_prop_5 FROM mgmt_target_assocs assoc, mgmt_targets tgt WHERE assoc.assoc_target_guid = l_target_guid AND assoc.assoc_guid = MGMT_ASSOC.g_hosted_by_guid AND tgt.target_guid = assoc.source_target_guid UNION ALL SELECT p_targetName, p_targetType, l_type_meta_ver, l_cat_prop_1 , l_cat_prop_2 , l_cat_prop_3, l_cat_prop_4 , l_cat_prop_5 FROM DUAL ) SELECT 1 INTO l_metric_count FROM mgmt_snapshot_metric_map s, mgmt_metrics m, target_list tgt WHERE p_snapshotType = s.snapshot_name AND p_targetType = s.snapshot_target_type AND m.metric_guid = s.metric_guid AND m.target_type = tgt.target_type AND m.type_meta_ver = tgt.type_meta_ver AND (m.category_prop_1 = tgt.category_prop_1 OR m.category_prop_1=' ') AND (m.category_prop_2 = tgt.category_prop_2 OR m.category_prop_2=' ') AND (m.category_prop_3 = tgt.category_prop_3 OR m.category_prop_3=' ') AND (m.category_prop_4 = tgt.category_prop_4 OR m.category_prop_4=' ') AND (m.category_prop_5 = tgt.category_prop_5 OR m.category_prop_5=' ') AND ROWNUM = 1 ; EXCEPTION WHEN NO_DATA_FOUND THEN NULL ; END ; -- If there were no dependent metrics, then simply return. IF l_metric_count != 1 THEN RETURN; END IF ; l_task_id := EM_TASK.create_task (p_coll_schedule=> mgmt_coll_schedule_obj.one_time( mgmt_global.sysdate_tzrgn(em_task.g_utc_timezone)), p_timezone_region=>EM_TASK.G_UTC_TIMEZONE, p_task_type=>EM_TASK.G_TASK_TYPE_SNAPSHOT, p_context=> mgmt_namevalue_array( mgmt_namevalue_obj.new('snapshot_type',p_snapshotType), mgmt_namevalue_obj.new('target_type',p_targetType), mgmt_namevalue_obj.new('target_name',p_targetName) ), p_task_class=>EM_TASK.G_TASK_CLASS_LONG_RUNNING) ; COMMIT ; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('run_snapshot_dependent_colls:Exit Task='||l_task_id, G_MODULE_NAME) ; END IF ; END run_snapshot_dependent_colls ; PROCEDURE process_collection_row( p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT ' ', p_schedule_ex IN VARCHAR2 DEFAULT ' ', p_is_repository IN NUMBER DEFAULT 1, p_store_metric IN VARCHAR2 DEFAULT NULL, p_schedule IN NUMBER DEFAULT NULL, p_last_collected_timestamp DATE DEFAULT NULL, p_status_message IN VARCHAR2 DEFAULT NULL, p_suspended IN NUMBER DEFAULT NULL) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('process_collection_row:Enter'|| 'target guid=>'||p_target_guid|| 'metric guid=>'||p_metric_guid|| 'collection name=>'||p_coll_name, G_MODULE_NAME) ; END IF ; BEGIN INSERT INTO mgmt_metric_collections (target_guid, metric_guid, coll_name, schedule_ex, is_repository, store_metric, schedule, last_collected_timestamp, status_message, suspended) VALUES (p_target_guid, p_metric_guid, p_coll_name, p_schedule_ex, p_is_repository, NVL(p_store_metric, 'Y'), NVL(p_schedule, 15), p_last_collected_timestamp, NVL(p_status_message, ''), NVL(p_suspended, 0)); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE mgmt_metric_collections SET schedule_ex = NVL(p_schedule_ex, schedule_ex), is_repository = NVL(p_is_repository, is_repository), schedule = NVL(p_schedule, schedule), store_metric = NVL(p_store_metric, store_metric), last_collected_timestamp = NVL(p_last_collected_timestamp, last_collected_timestamp), status_message = NVL(p_status_message , status_message), suspended = NVL(p_suspended, suspended) WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name; END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('process_collection_row:Exit ', G_MODULE_NAME) ; END IF ; END process_collection_row; PROCEDURE process_coll_prop_row( p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2 DEFAULT ' ', p_property_name IN VARCHAR2, p_property_value IN VARCHAR2 DEFAULT ' ') IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('process_coll_prop_row:Enter'|| 'target guid=>'||p_target_guid|| 'metric guid=>'||p_metric_guid|| 'collection name=>'||p_coll_name|| 'property name=>'||p_property_name|| 'property value'||p_property_value, G_MODULE_NAME) ; END IF ; BEGIN INSERT INTO mgmt_collection_properties (target_guid, metric_guid, coll_name, property_name, property_value) VALUES (p_target_guid, p_metric_guid, p_coll_name, p_property_name, p_property_value); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE mgmt_collection_properties SET property_value = p_property_value WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND property_name = p_property_name; END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('process_coll_prop_row:Exit ', G_MODULE_NAME) ; END IF ; END process_coll_prop_row; ------------------------------------------------ PROCEDURE process_threshold_row( p_target_guid RAW, p_metric_guid RAW, p_coll_name VARCHAR2 DEFAULT ' ', p_key_value VARCHAR2 DEFAULT ' ', p_warning_operator NUMBER DEFAULT 0, p_warning_threshold VARCHAR2 DEFAULT ' ', p_critical_operator NUMBER DEFAULT 0, p_critical_threshold VARCHAR2 DEFAULT ' ', p_num_occurences NUMBER DEFAULT 1, p_eval_order NUMBER DEFAULT 0, p_fixit_job VARCHAR2 DEFAULT '', p_num_warnings NUMBER DEFAULT NULL, p_num_criticals NUMBER DEFAULT NULL, p_message VARCHAR2 DEFAULT NULL, p_message_nlsid VARCHAR2 DEFAULT NULL) IS BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('process_threshold_row:Enter'|| 'target guid=>'||p_target_guid|| 'metric guid=>'||p_metric_guid|| 'collection name=>'||p_coll_name|| 'key value=>'||p_key_value|| 'warning operator'||p_warning_operator|| 'critical operator'||p_critical_operator|| 'critical threshold'||p_critical_threshold|| 'num occurences'||p_num_occurences , G_MODULE_NAME) ; END IF ; BEGIN INSERT INTO mgmt_metric_thresholds (target_guid, metric_guid, coll_name, key_value, warning_operator, warning_threshold, critical_operator, critical_threshold, num_occurences, eval_order, fixit_job, num_warnings, num_criticals, message, message_nlsid) VALUES (p_target_guid, p_metric_guid, p_coll_name, p_key_value, p_warning_operator, p_warning_threshold, p_critical_operator, p_critical_threshold, p_num_occurences, p_eval_order, p_fixit_job, NVL(p_num_warnings, 0), NVL(p_num_criticals, 0), p_message, p_message_nlsid); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE mgmt_metric_thresholds SET warning_operator = NVL(p_warning_operator, warning_operator), warning_threshold = NVL(p_warning_threshold, warning_threshold), critical_operator = NVL(p_critical_operator, critical_operator), critical_threshold = NVL(p_critical_threshold, critical_threshold), num_occurences = NVL(p_num_occurences, num_occurences), num_warnings = NVL(p_num_warnings, num_warnings), num_criticals = NVL(p_num_criticals, num_criticals), eval_order = NVL(p_eval_order, eval_order), fixit_job = NVL(p_fixit_job, fixit_job), message = NVL(p_message, message), message_nlsid = NVL(p_message_nlsid, message_nlsid) WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND key_value = p_key_value; END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('process_threshold_row:Exit ', G_MODULE_NAME) ; END IF ; END process_threshold_row; -- Rowset handler for deletion of MGMT_METRIC_THRESHOLDS -- Only those parameter that defines the PK are used. -- Still all the possible input parameters are specified -- because agent can send all these parameters with delete request PROCEDURE delete_metric_threshold( p_target_guid RAW, p_metric_guid RAW, p_coll_name VARCHAR2 DEFAULT ' ', p_key_value VARCHAR2 DEFAULT ' ', p_warning_operator NUMBER DEFAULT 0, p_warning_threshold VARCHAR2 DEFAULT ' ', p_critical_operator NUMBER DEFAULT 0, p_critical_threshold VARCHAR2 DEFAULT ' ', p_num_occurences NUMBER DEFAULT 1, p_eval_order NUMBER DEFAULT 0, p_fixit_job VARCHAR2 DEFAULT '', p_num_warnings NUMBER DEFAULT NULL, p_num_criticals NUMBER DEFAULT NULL, p_message VARCHAR2 DEFAULT NULL, p_message_nlsid VARCHAR2 DEFAULT NULL) IS l_cnt NUMBER := 0; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('delete_metric_threshold:Enter'|| 'target guid=>'||p_target_guid|| 'metric guid=>'||p_metric_guid|| 'collection name=>'||p_coll_name|| 'key value=>'||p_key_value, G_MODULE_NAME); END IF ; EMD_LOADER.check_deletion_allowed(p_target_guid,p_metric_guid, EMD_LOADER.GET_CURRENT_EMD_URL(), 'mgmt_metric_thresholds'); SELECT COUNT(*) INTO l_cnt FROM mgmt_metric_thresholds WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND key_value = p_key_value; IF(l_cnt = 0) THEN EMDW_LOG.WARN( 'Unable to delete from mgmt_metric_thresholds as no record exists for ' || ' target_guid : '|| p_target_guid || ' metric_guid : ' || p_metric_guid || ' coll_name : ' || p_coll_name || ' key_value : ' || p_key_value, G_MODULE_NAME); return; ELSIF(l_cnt > 1) THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Unable to delete from mgmt_metric_thresholds as more than 1 record exists for ' || ' target_guid : '|| p_target_guid || ' metric_guid : ' || p_metric_guid || ' coll_name : ' || p_coll_name || ' key_value : ' || p_key_value); END IF; DELETE mgmt_metric_thresholds WHERE target_guid = p_target_guid AND metric_guid = p_metric_guid AND coll_name = p_coll_name AND key_value = p_key_value; END delete_metric_threshold; -- Initialize the argument array on first execution of the package in session -- Not able to do in package definition BEGIN g_args := g_args_array() ; -- Do not change the order of first 5 signatures, they match the -- source type of the metric g_args.extend(5) ; -- pl/sql old signature for metric eval proc g_args(1) := mgmt_short_string_array('RAW','RAW','MGMT_METRIC_RESULTS') ; -- pl/sql new signature for metric eval proc g_args(2) := mgmt_short_string_array('RAW','RAW','VARCHAR2', 'MGMT_METRIC_VALUE_ARRAY') ; g_args(3) := NULL ; -- Bulk pl/sql new signature for metric eval proc g_args(4) := mgmt_short_string_array('MGMT_TARGET_GUID_ARRAY', 'RAW','VARCHAR2', 'MGMT_METRIC_VALUE_ARRAY') ; -- Severity eval proc g_args(5) := mgmt_short_string_array('RAW', 'RAW','MGMT_METRIC_DETAILS_ARRAY', 'NUMBER','VARCHAR2','VARCHAR2', 'VARCHAR2') ; END em_coll_util; / show errors