Rem drv: Rem Rem $Header: data_propagation_pkgbody.sql 21-apr-2007.00:35:06 nqureshi Exp $ Rem Rem data_propagation_pkgbody.sql Rem Rem Copyright (c) 2003, 2007, Oracle. All rights reserved. Rem Rem NAME Rem data_propagation_pkgdef.sql - Rem Rem DESCRIPTION Rem This is an internal package that provides low-level services Rem to propagate target-related data to one or more targets. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem nqureshi 04/21/07 - pdp backport Rem pmodi 08/12/05 - Bug:4542715 - Use superuser mode Rem gan 08/09/05 - add API for job Rem gan 07/29/05 - add save_repo_mntr_setting Rem gan 07/26/05 - bug 4510491, extend update_metric_thresholds for Rem repo target Rem rzazueta 07/05/05 - Fix 4435559 Rem rzazueta 06/15/05 - Add purge, stop, delete Rem gsbhatia 07/01/05 - New repmgr header impl Rem jgwang 05/09/05 - remove column from metric guid computation Rem rpinnama 04/13/05 - Use mgmt_template.apply_template Rem jgwang 01/26/05 - add UDM cred support Rem rzazueta 12/17/04 - Fix 4067675: Repository-only targets Rem rzazueta 12/01/04 - No job for repository-only targets Rem rzazueta 10/11/04 - Add collection credentials to apply template Rem rpinnama 10/10/04 - Replace template_ with mntr_ Rem rzazueta 09/26/04 - Add calls to create template copy and monitoring Rem settings copy Rem rzazueta 09/08/04 - Add support for templates Rem dcawley 07/07/04 - Increase user name size Rem skini 12/04/03 - Use multi-target list job submission Rem rzazueta 10/08/03 - Fix bug 3181130: Cutover to use job queues Rem skini 09/22/03 - Data prop for credentials Rem skini 09/26/03 - Fix submit job Rem skini 09/09/03 - Change in schedule_record structure Rem njuillar 07/23/03 - Remove copy_from flag in update_metric_thresholds Rem rzazueta 07/18/03 - Replace emd url with agent guid Rem njuillar 07/07/03 - Added check for multi-agent in copy_thresholds Rem rzazueta 06/16/03 - Add delete_operation_data method Rem rzazueta 06/12/03 - rzazueta_propagate_target_data Rem rzazueta 06/09/03 - Creation Rem CREATE OR REPLACE PACKAGE BODY MGMT_TARGET_UPDATE AS G_MODULE constant VARCHAR2(64) := 'DATA_PROPAGATION'; -- internal procedure -- Insert record into operations data table. It doesn't set operation_guid. -- operation_guid will be set in the submit_update_operation function PROCEDURE insert_operations_data(p_data_set_guid RAW, p_source_target_name VARCHAR2, p_source_target_type VARCHAR2, p_data_set_type NUMBER) IS BEGIN INSERT INTO MGMT_UPDATE_OPERATIONS_DATA (data_set_guid, source_target_guid, data_set_type) SELECT p_data_set_guid, target_guid, p_data_set_type FROM MGMT_TARGETS WHERE target_name=p_source_target_name AND target_type=p_source_target_type; IF SQL%ROWCOUNT = 0 THEN -- The target was not found raise_application_error(mgmt_global.INVALID_TARGETS_ERR, 'The following target is invalid: ' || p_source_target_name); END IF; END; -- -- submit_threshold_data -- -- Submit a threshold data set -- -- p_source_target_name The source target name -- p_source_target_type The source target type -- p_data The threshold data -- -- RETURNS: A new data set guid obtained by storing the data -- FUNCTION submit_threshold_data(p_source_target_name VARCHAR2, p_source_target_type VARCHAR2, p_data MGMT_METRIC_THR_OBJ_ARRAY) RETURN RAW IS l_data_set_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function insert_operations_data(l_data_set_guid, p_source_target_name, p_source_target_type, 1); -- now insert into the thresholds table FOR j in 1..p_data.count LOOP INSERT INTO MGMT_UPDATE_THRESHOLDS_DATA (data_set_guid, metric_guid, coll_name, key_value, eval_order, num_occurences, fixit_job, warning_threshold, critical_threshold, warning_operator, critical_operator, key_part1_value, key_part2_value, key_part3_value, key_part4_value, key_part5_value) VALUES (l_data_set_guid, p_data(j).metric_guid, p_data(j).coll_name, p_data(j).key_value, p_data(j).eval_order, p_data(j).num_occurences, p_data(j).fixit_job, p_data(j).warning_threshold, p_data(j).critical_threshold, p_data(j).warning_operator, p_data(j).critical_operator, p_data(j).key_part1_value, p_data(j).key_part2_value, p_data(j).key_part3_value, p_data(j).key_part4_value, p_data(j).key_part5_value); END LOOP; RETURN l_data_set_guid; END; -- -- submit_properties_data -- -- Submit a properties data set -- -- p_source_target_name The source target name -- p_source_target_type The source target type -- p_data The property data -- -- RETURNS: A new data set guid obtained by storing the data -- FUNCTION submit_properties_data(p_source_target_name VARCHAR2, p_source_target_type VARCHAR2, p_data MGMT_TARGET_PROPERTY_LIST) RETURN RAW IS l_data_set_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function insert_operations_data(l_data_set_guid, p_source_target_name, p_source_target_type, TARGET_OP_PROPERTIES); -- now insert into the properties table FOR j in 1..p_data.count LOOP INSERT INTO MGMT_UPDATE_PROPERTIES_DATA (data_set_guid, property_name, property_type, property_value) VALUES (l_data_set_guid, p_data(j).property_name, p_data(j).property_type, p_data(j).property_value); END LOOP; RETURN l_data_set_guid; END; -- -- submit_credentials_data -- -- Submit a credentials data set -- -- p_source_target_name The source target name -- p_source_target_type The source target type -- p_data The credentials data -- -- RETURNS: A new data set guid obtained by storing the data -- FUNCTION submit_credential_data(p_source_target_name VARCHAR2, p_source_target_type VARCHAR2, p_credential_set_name VARCHAR2, p_data MGMT_CRED_ROW_ARRAY) RETURN RAW IS l_data_set_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function insert_operations_data(l_data_set_guid, p_source_target_name, p_source_target_type, TARGET_OP_CREDENTIALS); -- Insert the credentials data FOR i IN 1..p_data.count LOOP INSERT INTO MGMT_UPDATE_CREDENTIALS_DATA(data_set_guid, credential_set_name, credential_column, credential_value) VALUES (l_data_set_guid, p_credential_set_name, p_data(i).credential_set_column, encrypt(p_data(i).credential_value)); END LOOP; RETURN l_data_set_guid; END; -- -- submit_coll_credentials_data -- -- Submit a credentials data set -- -- p_source_target_name The source target name -- p_source_target_type The source target type -- p_data The credentials data -- -- RETURNS: A new data set guid obtained by storing the data -- FUNCTION submit_coll_credential_data(p_source_target_name VARCHAR2, p_source_target_type VARCHAR2, p_metric_name VARCHAR2, p_metric_column VARCHAR2, p_collection_name VARCHAR2, p_credential_set_name VARCHAR2, p_data MGMT_CRED_ROW_ARRAY) RETURN RAW IS l_data_set_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function insert_operations_data(l_data_set_guid, p_source_target_name, p_source_target_type, TARGET_OP_COLL_CREDENTIALS); -- Insert the credentials data FOR i IN 1..p_data.count LOOP INSERT INTO MGMT_UPDATE_COLL_CREDS_DATA VALUES (l_data_set_guid, MGMT_METRIC.get_metric_guid(p_source_target_type, p_metric_name), p_collection_name, p_credential_set_name, p_data(i).credential_set_column, encrypt(p_data(i).credential_value)); END LOOP; RETURN l_data_set_guid; END; -- -- submit_monitoring_settings -- -- Submit monitoring settings data set -- -- p_source_target_name The source target name -- p_source_target_type The source target type -- p_metric_list The metrics data -- p_collection_list The collections data -- -- RETURNS: A new data set guid obtained by storing the data -- FUNCTION submit_monitoring_settings(p_source_target_name IN VARCHAR2, p_source_target_type IN VARCHAR2, p_metric_list IN MGMT_MNTR_METRIC_ARRAY, p_collection_list IN MGMT_MNTR_COLLECTION_ARRAY) RETURN RAW IS l_data_set_guid RAW(16); l_mntr_set_copy_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function insert_operations_data(l_data_set_guid, p_source_target_name, p_source_target_type, TARGET_OP_MNTR_SETTINGS); -- Create the monitoring settings copy l_mntr_set_copy_guid := MGMT_MONITORING.create_mntr_set_copy(p_source_target_type, p_source_target_name, l_data_set_guid, p_metric_list, p_collection_list); -- now insert into the template data map table INSERT INTO MGMT_UPDATE_TEMPLATE_DATA_MAP (data_set_guid, template_copy_guid) VALUES (l_data_set_guid, l_mntr_set_copy_guid); RETURN l_data_set_guid; END; -- -- submit_template_data -- -- Submit template data set -- -- p_template_name The source template name -- p_target_type The target type -- p_copy_common_only_flag The copy_copy_only flag. Default is 0 (copy_all) -- p_target_name The target name when copy_common_only_flag is 1 -- p_ca_creds A list of credentials for corrective actions -- p_coll_creds A list of collection credentials -- FUNCTION submit_template_data(p_template_name IN VARCHAR2, p_target_type IN VARCHAR2, p_copy_common_only_flag IN INTEGER DEFAULT 0, p_target_name IN VARCHAR2 DEFAULT NULL, p_ca_creds IN MGMT_MNTR_CA_ARRAY DEFAULT NULL, p_coll_creds IN MGMT_COLLECTION_CRED_ARRAY DEFAULT NULL) RETURN RAW IS l_data_set_guid RAW(16); l_template_guid RAW(16); l_template_copy_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); l_template_copy_guid := MGMT_TEMPLATE.create_template_clone(p_target_type, p_template_name, p_target_name, l_data_set_guid, p_copy_common_only_flag, p_ca_creds, p_coll_creds); l_template_guid := MGMT_TEMPLATE.get_template_guid(p_target_type, p_template_name); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function INSERT INTO MGMT_UPDATE_OPERATIONS_DATA (data_set_guid, source_target_guid, data_set_type) VALUES (l_data_set_guid, l_template_guid, TARGET_OP_TEMPLATES); -- now insert into the template data map table INSERT INTO MGMT_UPDATE_TEMPLATE_DATA_MAP (data_set_guid, template_copy_guid) VALUES (l_data_set_guid, l_template_copy_guid); RETURN l_data_set_guid; END; -- -- Submit an update operation. -- -- p_data_guids A list of data set guids -- p_destination_list A list of destination targets -- p_update_master_agents This is a flag that indicates how to deal -- with multi-agent targets in the destination list. If set to -- true (1), the master agent as well as all the standby agents -- monitoring the multi-agent target are updated. If set to false -- (0, the default), only the standby agents are updated, but -- not the master. This assumes that the caller of the API -- has synchronously updated the master. -- p_schedule An optional schedule, in case the update operation -- needs to be scheduled at some point in the future, eg., -- during a maintenance window. It is null by default, which -- means that the operation will be scheduled immediately -- Note that the schedule cannot be a repeating schedule -- -- RETURNS: An operation guid after saving the operation -- FUNCTION submit_update_operation(p_data_guids IN MGMT_USER_GUID_ARRAY, p_destination_list IN MGMT_JOB_TARGET_LIST, p_update_master_agents IN NUMBER DEFAULT 0, p_schedule IN MGMT_JOB_SCHEDULE_RECORD DEFAULT NULL) RETURN RAW IS l_operation_guid RAW(16); l_job_targets MGMT_JOB_TARGET_LIST; l_job_params MGMT_JOB_PARAM_LIST; l_job_name MGMT_JOB.job_name%TYPE; l_job_id MGMT_JOB.job_id%TYPE; l_execution_ids MGMT_JOB_GUID_ARRAY; l_queue_names SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); l_count NUMBER; l_schedule MGMT_JOB_SCHEDULE_RECORD := p_schedule; l_agent_guid MGMT_TARGETS.target_guid%TYPE; l_emd_url MGMT_TARGETS.emd_url%TYPE; l_agent_guid_list MGMT_USER_GUID_ARRAY; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_is_multi_agent_target NUMBER; l_invalid_target_names MGMT_JOB_PARAMETER.scalar_value%TYPE; l_invalid_target_count NUMBER := 0; l_agent_guids MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS(); l_target_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); l_job_target_lists MGMT_JOB_TARGET_LIST_ARRAY := MGMT_JOB_TARGET_LIST_ARRAY(); l_list_members MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST(); BEGIN -- Construct arrays of job targets, target guids and agent guids -- For each destination target, -- if update master agents is true -- add data for this target to the arrays -- if multi-agent target. -- Get guids of the other agents monitoring the target -- For each agent guid -- add target data to arrays l_job_targets := MGMT_JOB_TARGET_LIST(); FOR j in 1..p_destination_list.count LOOP BEGIN -- get the target guid and agent guid for the given target SELECT t.target_guid, t.emd_url INTO l_target_guid, l_emd_url FROM mgmt_targets t WHERE t.target_name = p_destination_list(j).target_name AND t.target_type = p_destination_list(j).target_type; IF l_emd_url IS NULL THEN -- This is a repository-only target. Set agent_guid to zeros l_agent_guid := HEXTORAW('0000000000000000'); ELSE -- User having privilege on target does not get privilege on agent -- hence select agent_guid in SUPER USER mode. BEGIN -- Enter super-user mode. SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, MGMT_USER.OP_SET_IDENTIFIER); SELECT a.target_guid INTO l_agent_guid FROM mgmt_targets a WHERE a.emd_url = l_emd_url AND a.target_type = mgmt_global.G_AGENT_TARGET_TYPE; -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); EXCEPTION WHEN OTHERS THEN -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); raise; END; END IF; -- The target was found IF mgmt_user.has_priv(l_current_user, MGMT_USER.OPERATOR_TARGET, l_target_guid) != MGMT_USER.USER_HAS_PRIV THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'The current user (' || l_current_user || ') does not have sufficient privileges to perform this operation'); END IF; l_is_multi_agent_target := mgmt_target.is_multi_agent_target (p_destination_list(j).target_name, p_destination_list(j).target_type); -- Always update the agent when target is not multi agent target IF p_update_master_agents = 1 OR l_is_multi_agent_target != 1 THEN -- Add data for the master agent to the arrays l_job_targets.extend(1); l_target_guids.extend(1); l_agent_guids.extend(1); l_job_targets(l_job_targets.last) := p_destination_list(j); l_target_guids(l_target_guids.last) := l_target_guid; l_agent_guids(l_agent_guids.last) := l_agent_guid; END IF; IF l_is_multi_agent_target = 1 THEN -- Get the guids of the standby agents SELECT agent_guid BULK COLLECT INTO l_agent_guid_list FROM mgmt_target_agent_assoc WHERE target_guid = l_target_guid AND agent_guid <> l_agent_guid; IF l_agent_guid_list IS NOT NULL AND l_agent_guid_list.count > 0 THEN FOR k IN 1..l_agent_guid_list.count LOOP l_job_targets.extend(1); l_target_guids.extend(1); l_agent_guids.extend(1); l_job_targets(l_job_targets.last) := p_destination_list(j); l_target_guids(l_target_guids.last) := l_target_guid; l_agent_guids(l_agent_guids.last) := l_agent_guid_list(k); END LOOP; END IF; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN -- The target was not found l_invalid_target_count := l_invalid_target_count + 1; IF l_invalid_target_count = 1 THEN l_invalid_target_names := l_invalid_target_names || p_destination_list(j).target_name; ELSE l_invalid_target_names := l_invalid_target_names || ',' || p_destination_list(j).target_name; END IF; END; END LOOP; IF l_invalid_target_count > 0 THEN raise_application_error(mgmt_global.INVALID_TARGETS_ERR, 'The following targets are invalid: ' || l_invalid_target_names); END IF; -- Get a new operation guid l_operation_guid := SYS_GUID(); -- Set operation guid for all data_guids in the operations data table FORALL j in 1..p_data_guids.count UPDATE MGMT_UPDATE_OPERATIONS_DATA SET operation_guid = l_operation_guid WHERE data_set_guid = p_data_guids(j); -- submit job l_job_params := MGMT_JOB_PARAM_LIST(); l_job_params.extend(1); l_job_params(1) := MGMT_JOB_PARAM_RECORD('operation_guid', 1, l_operation_guid, null); l_job_name := PROPTGTDATA_JOB_NAME_PREFIX || l_operation_guid; l_queue_names.extend(l_job_targets.COUNT); FOR i IN 1..l_job_targets.COUNT LOOP -- The queue name is created using the target guid and the agent -- guid to account for multi-agent targets l_queue_names(i) := PROPTGTDATA_JOB_NAME_PREFIX || l_target_guids(i) || ' ' || l_agent_guids(i); -- Create the queue if not already created SELECT count(1) INTO l_count FROM MGMT_JOB_QUEUES WHERE queue_name=l_queue_names(i); IF l_count=0 THEN MGMT_JOB_ENGINE.create_job_queue(l_queue_names(i)); END IF; END LOOP; -- Construct multiple target lists, one for each target l_job_target_lists.extend(l_job_targets.COUNT); l_list_members.extend(1); FOR i IN 1..l_job_targets.COUNT LOOP l_list_members(1) := l_job_targets(i); l_job_target_lists(i) := l_list_members; END LOOP; MGMT_JOBS.submit_job(l_job_name, PROPTGTDATA_JOB_DESCRIPTION, 0, PROPTGTDATA_JOB_TYPE, l_job_target_lists, l_job_params, l_schedule, -1, l_job_id, l_execution_ids, null, MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY, null, null, l_queue_names); -- Insert data of destination targets in the operations details table FORALL j in 1..l_agent_guids.count INSERT INTO MGMT_UPDATE_OPERATIONS_DETAILS (operation_guid, destination_target_guid, agent_guid, execution_id, execution_status) VALUES (l_operation_guid, l_target_guids(j), l_agent_guids(j), l_execution_ids(j), MGMT_JOBS.SCHEDULED_STATUS); -- Save to mgmt_update_operations table INSERT INTO MGMT_UPDATE_OPERATIONS (operation_guid, job_id, job_owner, submission_timestamp) VALUES (l_operation_guid, l_job_id, l_current_user, SYSDATE); RETURN l_operation_guid; END; -- -- Internal helper function, used in update_metric_thresholds -- PROCEDURE update_thresholds_repo_target(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_data_set IN MGMT_METRIC_THR_OBJ_ARRAY) AS BEGIN FOR indx IN 1..p_data_set.COUNT LOOP mgmt_monitoring.set_threshold( p_target_type => p_target_type, p_target_name => p_target_name, p_metric_name => p_data_set(indx).metric_name, p_metric_column => p_data_set(indx).metric_column, p_coll_name => p_data_set(indx).coll_name, p_key_value => p_data_set(indx).key_value, p_crit_threshold => p_data_set(indx).critical_threshold, p_warn_threshold => p_data_set(indx).warning_threshold); END LOOP; END update_thresholds_repo_target; -- -- update_metric_thresholds -- -- -- Convenience function that handles the update of metric thresholds for one -- single-agent or multi-agent target. If the specified agent is a multi-agent -- target, it automatically sets up an asynchronous update for the standby -- agents by submitting an update operation -- If the target is repository target (end_url is null or empty), threshold -- is directly updated. Only p_target_name, p_target_type, and p_data_set are -- meaningful. In p_data_set, only metric_name, metric_column, coll_name, -- key_value, critical_threshold and warning_threshold are used. -- -- p_target_name, p_target_type The target name and type -- p_data_set The set of thresholds updated -- p_update_master_agents This is a flag that indicates how to deal -- with multi-agent targets in the destination list. If set to -- true (1), the master agent as well as all the standby agents -- monitoring the multi-agent target are updated. If set to false -- (0, the default), only the standby agents are updated, but -- not the master. This assumes that the caller of the API -- has synchronously updated the master. -- p_schedule An optional schedule, in case the update operation -- needs to be scheduled at some point in the future, eg., -- during a maintenance window. It is null by default, which -- means that the operation will be scheduled immediately -- Note that the schedule cannot be a repeating schedule -- -- RETURNS: An operation guid after saving the operation -- PROCEDURE update_metric_thresholds(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_source_target_name IN VARCHAR2, p_source_target_type IN VARCHAR2, p_data_set IN MGMT_METRIC_THR_OBJ_ARRAY, p_update_master_agents IN NUMBER DEFAULT 0, p_schedule IN MGMT_JOB_SCHEDULE_RECORD DEFAULT NULL) IS l_data_set_guid RAW(16); l_operation_guid RAW(16); l_data_guids MGMT_USER_GUID_ARRAY; l_targets MGMT_JOB_TARGET_LIST; l_emd_url mgmt_targets.emd_url%TYPE; BEGIN SELECT emd_url INTO l_emd_url FROM mgmt_targets WHERE target_name = p_target_name AND target_type = p_target_type; IF l_emd_url IS NULL or LENGTH(TRIM(l_emd_url)) = 0 THEN update_thresholds_repo_target(p_target_name, p_target_type, p_data_set); RETURN; END IF; l_data_set_guid := submit_threshold_data(p_source_target_name, p_source_target_type, p_data_set); l_data_guids := MGMT_USER_GUID_ARRAY(); l_data_guids.extend(1); l_data_guids(1) := l_data_set_guid; l_targets := MGMT_JOB_TARGET_LIST(); l_targets.extend(1); l_targets(1) := MGMT_JOB_TARGET_RECORD(p_target_name, p_target_type); l_operation_guid := submit_update_operation(l_data_guids, l_targets, p_update_master_agents, p_schedule); -- RETURN l_operation_guid; END; -- -- copy_metric_thresholds -- -- Convenience procedure to copy the thresholds of a source target to -- a specified destination list. copy_flags is an array that specifies, -- for each target in the destination list, whether ALL thresholds -- should be copied, or only common ones. Note that the final data set could -- be different for each target; this method therefore ends up submitting -- multiple operations -- -- p_target_name, p_target_type The target name and type -- p_data_set The set of thresholds to copy -- p_destination_list The set of destination targets -- p_copy_common_only_flags An array that is as large as the number -- of destination targets. For each target, it specifies whether to -- copy the common thresholds only (1) or all the thresholds (0) -- from the source. -- p_update_master_agents This is a flag that indicates how to deal -- with multi-agent targets in the destination list. If set to -- true (1), the master agent as well as all the standby agents -- monitoring the multi-agent target are updated. If set to false -- (0, the default), only the standby agents are updated, but -- not the master. This assumes that the caller of the API -- has synchronously updated the master. -- p_schedule An optional schedule -- PROCEDURE copy_metric_thresholds(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_data_set IN MGMT_METRIC_THR_OBJ_ARRAY, p_destination_list IN MGMT_JOB_TARGET_LIST, p_copy_common_only_flags IN SMP_EMD_INTEGER_ARRAY, p_update_master_agents IN NUMBER DEFAULT 0, p_schedule IN MGMT_JOB_SCHEDULE_RECORD DEFAULT NULL) IS l_dest_met_thr CURSOR_TYPE; l_dest_data_set MGMT_METRIC_THR_OBJ_ARRAY; l_non_applicable_metric_list SMP_EMD_STRING_ARRAY; l_non_applicable_kvalue_list SMP_EMD_STRING_ARRAY; l_is_multi_agent_target NUMBER; l_metric_name MGMT_METRICS.metric_name%TYPE; l_metric_column MGMT_METRICS.metric_column%TYPE; l_metric_label MGMT_METRICS.metric_label%TYPE; l_column_label MGMT_METRICS.column_label%TYPE; l_key_column MGMT_METRICS.key_column%TYPE; l_metric_type MGMT_METRICS.metric_type%TYPE; l_coll_name MGMT_METRIC_THRESHOLDS.coll_name%TYPE; l_key_value MGMT_METRIC_THRESHOLDS.key_value%TYPE; l_eval_order MGMT_METRIC_THRESHOLDS.eval_order%TYPE; l_warning_threshold MGMT_METRIC_THRESHOLDS.warning_threshold%TYPE; l_critical_threshold MGMT_METRIC_THRESHOLDS.critical_threshold%TYPE; l_warning_operator MGMT_METRIC_THRESHOLDS.warning_operator%TYPE; l_critical_operator MGMT_METRIC_THRESHOLDS.critical_operator%TYPE; l_num_occurences MGMT_METRIC_THRESHOLDS.num_occurences%TYPE; l_fixit_job MGMT_METRIC_THRESHOLDS.fixit_job%TYPE; l_composite_key MGMT_METRICS_COMPOSITE_KEYS.composite_key%TYPE; l_key_part1_value MGMT_METRICS_COMPOSITE_KEYS.key_part1_value%TYPE; l_key_part2_value MGMT_METRICS_COMPOSITE_KEYS.key_part2_value%TYPE; l_key_part3_value MGMT_METRICS_COMPOSITE_KEYS.key_part3_value%TYPE; l_key_part4_value MGMT_METRICS_COMPOSITE_KEYS.key_part4_value%TYPE; l_key_part5_value MGMT_METRICS_COMPOSITE_KEYS.key_part5_value%TYPE; l_num_keys MGMT_METRICS.num_keys%TYPE; l_metric_guid MGMT_METRICS.metric_guid%TYPE; l_target_guid MGMT_METRIC_THRESHOLDS.target_guid%TYPE; l_has_key_column NUMBER; l_data_set_guid RAW(16); l_operation_guid RAW(16); l_data_guids MGMT_USER_GUID_ARRAY; l_targets MGMT_JOB_TARGET_LIST; BEGIN FOR j in 1..p_destination_list.count LOOP -- Compute final thresholds for destination target and save to -- repository. After the call, l_dest_met_thr will have a cursor -- with the final thresholds of the destination target emd_mntr_baseline.copy_thresholds(p_target_name, p_target_type, p_destination_list(j).target_name, p_destination_list(j).target_type, p_data_set, p_copy_common_only_flags(j), l_dest_met_thr, l_non_applicable_metric_list, l_non_applicable_kvalue_list, l_is_multi_agent_target); -- Construct array of thresholds from the l_dest_met_thr cursor l_dest_data_set := MGMT_METRIC_THR_OBJ_ARRAY(); LOOP FETCH l_dest_met_thr INTO l_metric_name, l_metric_column, l_metric_label, l_column_label, l_key_column, l_metric_type, l_coll_name, l_key_value, l_eval_order, l_warning_threshold, l_critical_threshold, l_warning_operator, l_critical_operator, l_num_occurences, l_fixit_job, l_composite_key, l_key_part1_value, l_key_part2_value, l_key_part3_value, l_key_part4_value, l_key_part5_value, l_num_keys, l_metric_guid, l_target_guid; EXIT WHEN l_dest_met_thr%NOTFOUND; -- exit when last row is fetched l_has_key_column := 0; IF (l_key_column IS NOT NULL AND length(trim(l_key_column)) <>0) THEN l_has_key_column := 1; END IF; l_dest_data_set.extend(1); l_dest_data_set(l_dest_data_set.last) := MGMT_METRIC_THR_OBJ( l_metric_name, l_metric_column, l_metric_type, l_key_column, l_coll_name, l_key_value, l_eval_order, l_num_occurences, l_fixit_job, l_warning_threshold, l_critical_threshold, l_warning_operator, l_critical_operator, l_has_key_column, l_metric_guid, l_target_guid, l_num_keys, l_key_part1_value, l_key_part2_value, l_key_part3_value, l_key_part4_value, l_key_part5_value); END LOOP; CLOSE l_dest_met_thr; -- Now, for the current destination target, get a data set guid -- for the final set of thresholds and submit the update operation l_data_set_guid := submit_threshold_data(p_target_name, p_target_type, l_dest_data_set); l_data_guids := MGMT_USER_GUID_ARRAY(); l_data_guids.extend(1); l_data_guids(1) := l_data_set_guid; l_targets := MGMT_JOB_TARGET_LIST(); l_targets.extend(1); l_targets(1) := MGMT_JOB_TARGET_RECORD(p_destination_list(j).target_name, p_destination_list(j).target_type); l_operation_guid := submit_update_operation(l_data_guids, l_targets, p_update_master_agents, p_schedule); END LOOP; EXCEPTION WHEN OTHERS THEN IF l_dest_met_thr IS NOT NULL AND l_dest_met_thr%ISOPEN THEN CLOSE l_dest_met_thr; END IF; RAISE; END; -- -- update_monitoring_settings -- -- Convenience function that handles the update of monitoring settings for one -- single-agent or multi-agent target. If the specified agent is a multi-agent -- target, it automatically sets up an asynchronous update for the standby -- agents by submitting an update operation. -- -- p_target_name, p_target_type The target name and type -- p_metric_list The metrics data -- p_collection_list The collections data -- p_update_master_agents This is a flag that indicates how to deal -- with multi-agent targets in the destination list. If set to -- true (1), the master agent as well as all the standby agents -- monitoring the multi-agent target are updated. If set to false -- (0, the default), only the standby agents are updated, but -- not the master. This assumes that the caller of the API -- has synchronously updated the master. -- p_schedule An optional schedule, in case the update operation -- needs to be scheduled at some point in the future, eg., -- during a maintenance window. It is null by default, which -- means that the operation will be scheduled immediately -- Note that the schedule cannot be a repeating schedule -- -- RETURNS: An operation guid after saving the operation -- FUNCTION update_monitoring_settings(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_metric_list IN MGMT_MNTR_METRIC_ARRAY, p_collection_list IN MGMT_MNTR_COLLECTION_ARRAY, p_update_master_agents IN NUMBER DEFAULT 0, p_schedule IN MGMT_JOB_SCHEDULE_RECORD DEFAULT NULL) RETURN RAW IS l_data_set_guid RAW(16); l_operation_guid RAW(16); l_data_guids MGMT_USER_GUID_ARRAY; l_targets MGMT_JOB_TARGET_LIST; BEGIN l_data_set_guid := submit_monitoring_settings(p_target_name, p_target_type, p_metric_list, p_collection_list); l_data_guids := MGMT_USER_GUID_ARRAY(); l_data_guids.extend(1); l_data_guids(1) := l_data_set_guid; l_targets := MGMT_JOB_TARGET_LIST(); l_targets.extend(1); l_targets(1) := MGMT_JOB_TARGET_RECORD(p_target_name, p_target_type); l_operation_guid := submit_update_operation(l_data_guids, l_targets, p_update_master_agents, p_schedule); RETURN l_operation_guid; END; PROCEDURE save_repo_mntr_settings(p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_data_set_guid IN RAW) AS l_metric_list mgmt_mntr_metric_array := mgmt_mntr_metric_array(); l_policy_list mgmt_mntr_policy_array := mgmt_mntr_policy_array(); l_collection_list mgmt_mntr_collection_array := mgmt_mntr_collection_array(); BEGIN IF emdw_log.p_is_debug_set THEN emdw_log.debug('save_repo_mntr_settings: Enter, ' || p_target_name || '/' || p_target_type || p_data_set_guid, G_MODULE); END IF; mgmt_monitoring.get_mntr_set_copy_settings(p_target_type, p_target_name, p_data_set_guid, l_metric_list, l_policy_list, l_collection_list); mgmt_monitoring.save_settings(p_target_type => p_target_type, p_target_name => p_target_name, p_metric_list => l_metric_list, p_collection_list => l_collection_list); IF emdw_log.p_is_debug_set THEN emdw_log.debug('save_repo_mntr_settings: Exit, ', G_MODULE); END IF; END save_repo_mntr_settings; -- -- apply_template -- -- Convenience procedure to apply a template to a specified destination list. -- -- p_template_name The source template name -- p_target_type The target type -- p_destination_list The set of destination targets -- p_copy_common_only_flags An array that is as large as the number of -- destination targets. For each target, it specifies whether to copy the common -- thresholds only (1) or all the thresholds (0) from the source. -- p_ca_creds A list of credentials for corrective actions. -- p_coll_creds A list of collection credentials -- p_update_master_agents This is a flag that indicates how to deal with multi-agent -- targets in the destination list. If set to true (1), the master agent as well as all the -- standby agents monitoring the multi-agent target are updated. If set to false (0), -- only the standby agents are updated, but not the master. -- p_schedule An optional schedule -- PROCEDURE apply_template(p_template_name IN VARCHAR2, p_target_type IN VARCHAR2, p_destination_list IN MGMT_JOB_TARGET_LIST, p_copy_common_only_flags IN SMP_EMD_INTEGER_ARRAY, p_ca_creds IN MGMT_MNTR_CA_ARRAY DEFAULT NULL, p_coll_creds IN MGMT_COLLECTION_CRED_ARRAY DEFAULT NULL, p_update_master_agents IN NUMBER DEFAULT 1, p_schedule IN MGMT_JOB_SCHEDULE_RECORD DEFAULT NULL) IS l_data_set_guid RAW(16); l_operation_guid RAW(16); l_data_guids MGMT_USER_GUID_ARRAY; l_targets MGMT_JOB_TARGET_LIST; l_rep_only_targets INTEGER := 0; l_destination_list MGMT_TARGET_ARRAY; l_copy_common_only_flags MGMT_INTEGER_ARRAY; BEGIN l_destination_list := MGMT_TARGET_ARRAY(); FOR i in 1..p_destination_list.count LOOP l_destination_list.extend(1); l_destination_list(l_destination_list.last) := MGMT_TARGET_OBJ.NEW(p_target_name => p_destination_list(i).target_name, p_target_type => p_destination_list(i).target_type); END LOOP; l_copy_common_only_flags := MGMT_INTEGER_ARRAY(); FOR i in 1..p_copy_common_only_flags.count LOOP l_copy_common_only_flags.extend(1); l_copy_common_only_flags(l_copy_common_only_flags.last) := p_copy_common_only_flags(i); END LOOP; MGMT_TEMPLATE.apply_template(p_template_name => p_template_name, p_target_type => p_target_type, p_destination_list => l_destination_list, p_copy_common_only_flags => l_copy_common_only_flags, p_ca_creds => p_ca_creds, p_coll_creds => p_coll_creds, p_update_master_agents => p_update_master_agents, p_schedule => p_schedule); END; -- Throw an exception if the current user does not have privileges to -- perform the current operation. -- If p_allow_superuser is TRUE, then superusers will be able to -- perform the operation. If set to FALSE, then the user will -- be able to perform the operation only if the user is the -- owner of the operation/job. -- PROCEDURE check_modify_operation(p_operation_guid RAW, p_allow_superuser BOOLEAN DEFAULT TRUE) IS l_owner VARCHAR2(256); l_current_user VARCHAR2(256) := mgmt_user.get_current_em_user(); BEGIN SELECT job_owner INTO l_owner FROM MGMT_UPDATE_OPERATIONS WHERE operation_guid=p_operation_guid; IF l_current_user=l_owner THEN RETURN; END IF; IF p_allow_superuser THEN IF MGMT_USER.has_priv(l_current_user, MGMT_USER.SUPER_USER) = MGMT_USER.USER_HAS_PRIV THEN RETURN; END IF; END IF; raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'The current user (' || l_current_user || ') does not have sufficient privileges to perform this operation'); EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, 'The specified operation does not exist'); END; -- -- delete_operation_data -- -- Based on the operation_guid, it deletes the data associated with it -- from these tables: -- MGMT_UPDATE_OPERATIONS -- MGMT_UPDATE_OPERATIONS_DATA -- MGMT_UPDATE_OPERATIONS_DETAILS -- MGMT_UPDATE_THRESHOLDS_DATA -- MGMT_UPDATE_PROPERTIES_DATA -- MGMT_UPDATE_CREDENTIALS_DATA -- -- Before deleting any data, it checks that there are no active -- executions for the given operation_guid. It generates an error -- if the operation_guid has any active executions. -- -- p_operation_guid The operation guid for which data are to be deleted. -- PROCEDURE delete_operation_data(p_operation_guid IN RAW) IS l_num_active_executions NUMBER; l_data_set_guids MGMT_USER_GUID_ARRAY; l_data_set_types SMP_EMD_INTEGER_ARRAY; BEGIN check_modify_operation(p_operation_guid, TRUE); -- Cannot delete an operation that has active executions SELECT count(execution_id) INTO l_num_active_executions FROM mgmt_update_operations_details WHERE operation_guid = p_operation_guid AND execution_status NOT IN (MGMT_JOBS.COMPLETED_STATUS, MGMT_JOBS.FAILED_STATUS, MGMT_JOBS.ABORTED_STATUS, MGMT_JOBS.STOPPED_STATUS); IF l_num_active_executions > 0 THEN raise_application_error(MGMT_GLOBAL.ACTIVE_EXECUTIONS_EXIST_ERR, 'Cannot remove an operation with active executions'); END IF; -- Delete thresholds, properties, and credentials data associated -- with operation_guid SELECT data_set_guid, data_set_type BULK COLLECT INTO l_data_set_guids, l_data_set_types FROM mgmt_update_operations_data WHERE operation_guid = p_operation_guid; IF l_data_set_guids IS NOT NULL AND l_data_set_guids.count > 0 THEN FOR j IN 1..l_data_set_guids.count LOOP IF l_data_set_types(j) = TARGET_OP_THRESHOLDS THEN DELETE FROM MGMT_UPDATE_THRESHOLDS_DATA WHERE data_set_guid = l_data_set_guids(j); ELSIF l_data_set_types(j) = TARGET_OP_PROPERTIES THEN DELETE FROM MGMT_UPDATE_PROPERTIES_DATA WHERE data_set_guid = l_data_set_guids(j); ELSIF l_data_set_types(j) = TARGET_OP_CREDENTIALS THEN DELETE FROM MGMT_UPDATE_CREDENTIALS_DATA WHERE data_set_guid = l_data_set_guids(j); ELSIF l_data_set_types(j) = TARGET_OP_COLL_CREDENTIALS THEN DELETE FROM MGMT_UPDATE_COLL_CREDS_DATA WHERE data_set_guid = l_data_set_guids(j); ELSIF l_data_set_types(j) = TARGET_OP_APPLY_PDP THEN DELETE FROM MGMT_UPDATE_PDP_DATA WHERE data_set_guid = l_data_set_guids(j); END IF; END LOOP; END IF; -- Delete data set guids associated with operation guid DELETE FROM MGMT_UPDATE_OPERATIONS_DATA WHERE operation_guid = p_operation_guid; -- Delete operations details associated with operation guid DELETE FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE operation_guid = p_operation_guid; -- Delete the operation itself DELETE FROM MGMT_UPDATE_OPERATIONS WHERE operation_guid = p_operation_guid; END; -- -- doPurge should be called only after the data has been deleted from -- mgmt_update_operations_details table. doPurge will do the cleanup -- for the remaining tables. -- FUNCTION doPurge(p_commit IN BOOLEAN) RETURN NUMBER IS l_purge_rowids p_rowid_list_type; l_rows_purged NUMBER := 0; -- A data set with an operation_guid set to NULL means that the operation -- is in the process of being submitted. Remove only the ones with a non-null -- operation_guid. CURSOR operations_data_cursor IS SELECT d.ROWID FROM MGMT_UPDATE_OPERATIONS_DATA d WHERE NOT EXISTS (SELECT operation_guid FROM MGMT_UPDATE_OPERATIONS_DETAILS det WHERE det.operation_guid = d.operation_guid) AND d.operation_guid IS NOT NULL; CURSOR operations_cursor IS SELECT o.ROWID FROM MGMT_UPDATE_OPERATIONS o WHERE NOT EXISTS (SELECT operation_guid FROM MGMT_UPDATE_OPERATIONS_DETAILS det WHERE det.operation_guid = o.operation_guid); CURSOR thresholds_cursor IS SELECT td.ROWID FROM MGMT_UPDATE_THRESHOLDS_DATA td WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_OPERATIONS_DATA dat WHERE dat.data_set_guid = td.data_set_guid); CURSOR properties_cursor IS SELECT pd.ROWID FROM MGMT_UPDATE_PROPERTIES_DATA pd WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_OPERATIONS_DATA dat WHERE dat.data_set_guid = pd.data_set_guid); CURSOR credentials_cursor IS SELECT cd.ROWID FROM MGMT_UPDATE_CREDENTIALS_DATA cd WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_OPERATIONS_DATA dat WHERE dat.data_set_guid = cd.data_set_guid); CURSOR coll_creds_cursor IS SELECT ccd.ROWID FROM MGMT_UPDATE_COLL_CREDS_DATA ccd WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_OPERATIONS_DATA dat WHERE dat.data_set_guid = ccd.data_set_guid); CURSOR template_data_map_cursor IS SELECT tdm.ROWID FROM MGMT_UPDATE_TEMPLATE_DATA_MAP tdm WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_OPERATIONS_DATA dat WHERE dat.data_set_guid = tdm.data_set_guid); BEGIN -- -- Remove data from the operations data table. -- OPEN operations_data_cursor; LOOP -- Bulk collect row ids FETCH operations_data_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_OPERATIONS_DATA WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (operations_data_cursor%ISOPEN) THEN CLOSE operations_data_cursor; END IF; -- -- Remove data from the operations table. -- OPEN operations_cursor; LOOP -- Bulk collect row ids FETCH operations_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_OPERATIONS WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (operations_cursor%ISOPEN) THEN CLOSE operations_cursor; END IF; -- -- Remove data from the thresholds data table. -- OPEN thresholds_cursor; LOOP -- Bulk collect row ids FETCH thresholds_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_THRESHOLDS_DATA WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (thresholds_cursor%ISOPEN) THEN CLOSE thresholds_cursor; END IF; -- -- Remove data from the properties data table. -- OPEN properties_cursor; LOOP -- Bulk collect row ids FETCH properties_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_PROPERTIES_DATA WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (properties_cursor%ISOPEN) THEN CLOSE properties_cursor; END IF; -- -- Remove data from the credentials data table. -- OPEN credentials_cursor; LOOP -- Bulk collect row ids FETCH credentials_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_CREDENTIALS_DATA WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (credentials_cursor%ISOPEN) THEN CLOSE credentials_cursor; END IF; -- -- Remove data from the coll_creds data table. -- OPEN coll_creds_cursor; LOOP -- Bulk collect row ids FETCH coll_creds_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_COLL_CREDS_DATA WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (coll_creds_cursor%ISOPEN) THEN CLOSE coll_creds_cursor; END IF; -- -- Remove data from the template data map table. -- OPEN template_data_map_cursor; LOOP -- Bulk collect row ids FETCH template_data_map_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_TEMPLATE_DATA_MAP WHERE ROWID = l_purge_rowids(i); IF p_commit THEN COMMIT; END IF; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (template_data_map_cursor%ISOPEN) THEN CLOSE template_data_map_cursor; END IF; -- Delete monitoring settings copies FOR crec IN (SELECT mntr_set_copy_guid FROM MGMT_MNTR_SET_COPIES c WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_TEMPLATE_DATA_MAP m WHERE m.data_set_guid = c.copy_req_guid) ) LOOP MGMT_MONITORING.delete_mntr_set_copy(crec.mntr_set_copy_guid); -- Commit after every mntr_set_copy because delete call deletes -- many entries from several tables IF p_commit THEN COMMIT; END IF; END LOOP; -- Delete template copies FOR crec IN (SELECT template_copy_guid FROM MGMT_TEMPLATE_COPIES c WHERE NOT EXISTS (SELECT data_set_guid FROM MGMT_UPDATE_TEMPLATE_DATA_MAP m WHERE m.data_set_guid = c.copy_req_guid) ) LOOP MGMT_TEMPLATE.delete_template_copy(crec.template_copy_guid); -- Commit after every template_copy because delete call deletes -- many entries from several tables IF p_commit THEN COMMIT; END IF; END LOOP; RETURN l_rows_purged; END doPurge; PROCEDURE purge(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS l_purge_rowids p_rowid_list_type; l_rows_purged NUMBER := 0; CURSOR operations_details_cursor (c_purge_till_time DATE) IS SELECT ROWID FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_status IN (MGMT_JOBS.COMPLETED_STATUS, MGMT_JOBS.FAILED_STATUS, MGMT_JOBS.ABORTED_STATUS, MGMT_JOBS.STOPPED_STATUS) AND last_updated_timestamp < c_purge_till_time ORDER BY execution_id; BEGIN -- -- Purge old data from the operations details table. -- OPEN operations_details_cursor(pcb_params.purge_upto_time); LOOP -- Bulk collect row ids FETCH operations_details_cursor BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize; -- Exit when no more row ids fetched EXIT WHEN l_purge_rowids.COUNT <= 0; -- BULK delete FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST DELETE FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE ROWID = l_purge_rowids(i); COMMIT; l_rows_purged := l_rows_purged + l_purge_rowids.COUNT; l_purge_rowids.DELETE; END LOOP; -- Close the cursor if open IF (operations_details_cursor%ISOPEN) THEN CLOSE operations_details_cursor; END IF; -- Cleanup remaining tables l_rows_purged := l_rows_purged + doPurge(true); pcb_params.callback_result := 0; pcb_params.rows_processed := l_rows_purged; pcb_params.error_code := 0; pcb_params.error_msg := NULL; END purge; -- PROCEDURE: clean_on_tgt_del -- -- PURPOSE: -- Cleanup data propagation entries as needed whenever a target is deleted. -- The data propagation tables that have target info in them are marked -- as exceptions and will not be cleaned by the admin routines. -- -- IN Parameters: -- target_name_in : target name of the deleted target -- target_type_in : target type of the deleted target -- target_guid_in : target guid of the deleted target -- -- -- OUT Parameters: -- NONE PROCEDURE clean_on_tgt_del (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS l_rows_purged NUMBER := 0; l_execution_ids MGMT_JOB_GUID_ARRAY; BEGIN SELECT execution_id BULK COLLECT INTO l_execution_ids FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE destination_target_guid = target_guid_in ORDER BY execution_id; IF l_execution_ids IS NOT NULL AND l_execution_ids.COUNT > 0 THEN FORALL i IN 1..l_execution_ids.COUNT DELETE FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id = l_execution_ids(i); l_rows_purged := doPurge(true); END IF; END; -- PROCEDURE: stop_executions -- -- PURPOSE: -- Stop executions of scheduled data propagation jobs. -- -- IN Parameters: -- p_execution_ids : array of execution ids to be stopped. -- -- OUT Parameters: -- p_target_name_cursor : target names on which the user does not have operator -- privilege. -- p_execution_id_cursor: execution ids that could not be stopped because they -- were already running -- PROCEDURE stop_executions(p_execution_ids IN MGMT_JOB_GUID_ARRAY, p_target_name_cursor OUT CURSOR_TYPE, p_execution_id_cursor OUT CURSOR_TYPE) IS l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.GET_CURRENT_EM_USER; l_tguids MGMT_USER_GUID_ARRAY; l_priv_tguids MGMT_USER_GUID_ARRAY; l_priv_exec_ids MGMT_JOB_GUID_ARRAY; l_stop_pending_exec_ids MGMT_JOB_GUID_ARRAY; l_cursor CURSOR_TYPE; BEGIN SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, MGMT_USER.OP_SET_IDENTIFIER); -- Do a best-effort stop. Ie. Stop only those executions on targets -- on which the current user has operator privilege. SELECT destination_target_guid BULK COLLECT INTO l_tguids FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id IN (SELECT * FROM TABLE (CAST(p_execution_ids AS MGMT_JOB_GUID_ARRAY))); l_cursor := MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_tguids); FETCH l_cursor BULK COLLECT INTO l_priv_tguids; CLOSE l_cursor; -- if we don't have operator privilege on any targets -- the user cannot stop the job execution IF l_priv_tguids IS NOT NULL AND l_priv_tguids.COUNT > 0 THEN -- sort execution_ids before stopping to avoid deadlocks. SELECT execution_id BULK COLLECT INTO l_priv_exec_ids FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id IN (SELECT * FROM TABLE (CAST(p_execution_ids AS MGMT_JOB_GUID_ARRAY))) AND destination_target_guid IN (SELECT * FROM TABLE (CAST(l_priv_tguids AS MGMT_USER_GUID_ARRAY))) ORDER BY execution_id; IF l_priv_exec_ids IS NOT NULL AND l_priv_exec_ids.COUNT > 0 THEN FOR i IN 1..l_priv_exec_ids.COUNT LOOP BEGIN MGMT_JOBS.stop_execution(l_priv_exec_ids(i)); EXCEPTION -- stop_execution throws exception if execution_id does not exist -- (execution has already completed and gone away) WHEN OTHERS THEN NULL; END; END LOOP; END IF; UPDATE MGMT_UPDATE_OPERATIONS_DETAILS SET execution_status = MGMT_JOBS.STOPPED_STATUS WHERE execution_id IN (SELECT * FROM TABLE (CAST(l_priv_exec_ids AS MGMT_JOB_GUID_ARRAY))) AND execution_status = MGMT_JOBS.SCHEDULED_STATUS; END IF; -- Return target names for which user does not have operator privilege. OPEN p_target_name_cursor FOR SELECT t.target_name FROM MGMT_TARGETS t, (SELECT column_value FROM TABLE (CAST(l_tguids AS MGMT_USER_GUID_ARRAY))) a WHERE t.target_guid = a.column_value AND t.target_guid NOT IN (SELECT * FROM TABLE (CAST(l_priv_tguids AS MGMT_USER_GUID_ARRAY))); -- Return execution ids that could not be stopped because they were already running OPEN p_execution_id_cursor FOR SELECT execution_id FROM MGMT_JOB_EXEC_SUMMARY WHERE execution_id IN (SELECT * FROM TABLE (CAST(l_priv_exec_ids AS MGMT_JOB_GUID_ARRAY))) AND status = MGMT_JOBS.STOP_PENDING_STATUS; SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END; -- PROCEDURE: delete_executions -- -- PURPOSE: -- Delete data propagation entries for executions which are completed, stopped, failed or -- aborted. -- -- IN Parameters: -- p_execution_ids : array of execution ids to be deleted. -- -- OUT Parameters: -- p_target_name_cursor : target names on which the user does not have operator -- privilege. -- p_execution_id_cursor: execution ids that could not be deleted because they -- are scheduled or running. -- -- Notes: There is no need to delete the execution from the job system. Data propagation -- jobs are system jobs. They go away when they complete. We only need to remove -- the entries from the data propagation tables. -- PROCEDURE delete_executions(p_execution_ids IN MGMT_JOB_GUID_ARRAY, p_target_name_cursor OUT CURSOR_TYPE, p_execution_id_cursor OUT CURSOR_TYPE) IS l_current_user MGMT_CREATED_USERS.user_name%TYPE := MGMT_USER.GET_CURRENT_EM_USER; l_tguids MGMT_USER_GUID_ARRAY; l_priv_tguids MGMT_USER_GUID_ARRAY; l_priv_exec_ids MGMT_JOB_GUID_ARRAY; l_stop_pending_exec_ids MGMT_JOB_GUID_ARRAY; l_cursor CURSOR_TYPE; l_rows_purged NUMBER := 0; BEGIN SETEMUSERCONTEXT(MGMT_USER.GET_REPOSITORY_OWNER, MGMT_USER.OP_SET_IDENTIFIER); -- Do a best-effort delete. Ie. Delete only those executions on targets -- on which the current user has operator privilege. SELECT destination_target_guid BULK COLLECT INTO l_tguids FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id IN (SELECT * FROM TABLE (CAST(p_execution_ids AS MGMT_JOB_GUID_ARRAY))); l_cursor := MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.OPERATOR_TARGET, l_tguids); FETCH l_cursor BULK COLLECT INTO l_priv_tguids; CLOSE l_cursor; -- if we don't have operator privilege on any targets -- the user cannot delete the execution IF l_priv_tguids IS NOT NULL AND l_priv_tguids.COUNT > 0 THEN -- select execution_ids to be deleted SELECT execution_id BULK COLLECT INTO l_priv_exec_ids FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id IN (SELECT * FROM TABLE (CAST(p_execution_ids AS MGMT_JOB_GUID_ARRAY))) AND destination_target_guid IN (SELECT * FROM TABLE (CAST(l_priv_tguids AS MGMT_USER_GUID_ARRAY))) AND execution_status IN (MGMT_JOBS.COMPLETED_STATUS, MGMT_JOBS.ABORTED_STATUS, MGMT_JOBS.FAILED_STATUS, MGMT_JOBS.STOPPED_STATUS) ORDER BY execution_id; IF l_priv_exec_ids IS NOT NULL AND l_priv_exec_ids.COUNT > 0 THEN FORALL i IN 1..l_priv_exec_ids.COUNT DELETE FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id = l_priv_exec_ids(i); l_rows_purged := doPurge(false); END IF; END IF; -- Return target names on which user does not have operator privilege. OPEN p_target_name_cursor FOR SELECT t.target_name FROM MGMT_TARGETS t, (SELECT column_value FROM TABLE (CAST(l_tguids AS MGMT_USER_GUID_ARRAY))) a WHERE t.target_guid = a.column_value AND t.target_guid NOT IN (SELECT * FROM TABLE (CAST(l_priv_tguids AS MGMT_USER_GUID_ARRAY))); -- Return execution ids that could not be deleted because they were scheduled or running OPEN p_execution_id_cursor FOR SELECT execution_id FROM MGMT_UPDATE_OPERATIONS_DETAILS WHERE execution_id IN (SELECT * FROM TABLE (CAST(p_execution_ids AS MGMT_JOB_GUID_ARRAY))); SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END; -- -- has_running_update_execution -- Check if the target has any scheduled execution for data propergation. -- Aborted and Failed execution are counted, since system job in those states -- will be retried. -- Parameter: p_dest_target_guid, -- p_op_type, one of the constant defined in the package -- Return: 1 for having execution, 0 for none FUNCTION has_running_update_execution(p_dest_target_guid IN RAW, p_op_type IN NUMBER) RETURN NUMBER AS l_count NUMBER; BEGIN IF emdw_log.p_is_debug_set THEN emdw_log.debug('has_running_update_execution: enter, ' || p_dest_target_guid || ' op type ' || p_op_type, G_MODULE); END IF; SELECT count(execution_id) INTO l_count FROM mgmt_update_operations_data d, mgmt_update_operations_details de WHERE d.operation_guid = de.operation_guid AND d.data_set_type = p_op_type AND de.destination_target_guid = p_dest_target_guid AND de.execution_status NOT IN (MGMT_JOBS.COMPLETED_STATUS, MGMT_JOBS.STOPPED_STATUS); IF emdw_log.p_is_debug_set THEN emdw_log.debug('has_running_update_execution: exit, count ' || l_count, G_MODULE); END IF; RETURN l_count; END has_running_update_execution; -- -- stop_propagation_execution -- Abort all existing execution on the target. -- Parameters: p_dest_target_guid -- p_op_type -- Throw exception when fail to stop the execution PROCEDURE stop_propagation_execution(p_dest_target_guid IN RAW, p_op_type IN NUMBER) AS l_target_name_cur CURSOR_TYPE; l_execution_guid_cur CURSOR_TYPE; l_execution_guids MGMT_JOB_GUID_ARRAY; BEGIN IF emdw_log.p_is_debug_set THEN emdw_log.debug('stop_propagation_execution: enter, ' || p_dest_target_guid || ' op type ' || p_op_type, G_MODULE); END IF; SELECT execution_id BULK COLLECT INTO l_execution_guids FROM mgmt_update_operations_data d, mgmt_update_operations_details de WHERE d.operation_guid = de.operation_guid AND d.data_set_type = p_op_type AND de.destination_target_guid = p_dest_target_guid AND de.execution_status NOT IN (MGMT_JOBS.COMPLETED_STATUS, MGMT_JOBS.STOPPED_STATUS); IF l_execution_guids IS NOT NULL AND l_execution_guids.COUNT > 0 THEN stop_executions(l_execution_guids, l_target_name_cur, l_execution_guid_cur); END IF; IF emdw_log.p_is_debug_set THEN emdw_log.debug('stop_propagation_execution: exit', G_MODULE); END IF; END stop_propagation_execution; -- -- _data -- -- Submit a pdp data set -- -- p_source_target_name The source target name -- p_source_target_type The source target type -- p_setting_status_in enable/disable setting -- p_data The pdp data MGMT_PDP_ACTUAL_SETTING_ARRAY -- -- RETURNS: A new data set guid obtained by storing the data -- -- FUNCTION submit_pdp_data ( p_setting_guid_in IN RAW, p_source_target_name VARCHAR2, p_source_target_type VARCHAR2, p_setting_status_in IN NUMBER, p_pdp_type_in IN VARCHAR2, p_data MGMT_PDP_ACTUAL_SETTING_ARRAY) RETURN RAW IS l_data_set_guid RAW(16); BEGIN -- Get a new data set guid l_data_set_guid := SYS_GUID(); -- insert record into the operations data table. -- operation_guid will be set in the submit_update_operation function INSERT INTO MGMT_UPDATE_OPERATIONS_DATA (data_set_guid,source_target_guid, data_set_type) VALUES (l_data_set_guid,p_setting_guid_in,TARGET_OP_APPLY_PDP); -- now insert into the properties table INSERT INTO MGMT_UPDATE_PDP_DATA (data_set_guid, setting_name, SETTING_VALUE) VALUES (l_data_set_guid, 'SETTING_STATUS', p_setting_status_in ); INSERT INTO MGMT_UPDATE_PDP_DATA (data_set_guid, setting_name, SETTING_VALUE) VALUES (l_data_set_guid, 'SETTING_TYPE', p_pdp_type_in ); FOR j in 1..p_data.count LOOP INSERT INTO MGMT_UPDATE_PDP_DATA (data_set_guid, setting_name, SETTING_VALUE) VALUES (l_data_set_guid, p_data(j).PDP_VALUE_NAME, p_data(j).PDP_VALUE ); END LOOP; RETURN l_data_set_guid; END submit_pdp_data; END MGMT_TARGET_UPDATE; / show errors;