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;