Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/ecm/ecm_ct_pkgbody.sql /st_emcore_10.2.0.4.2db11.2/1 2009/06/19 18:17:46 vkhizder Exp $
Rem
Rem ecm_ct_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem ecm_ct_pkgbody.sql - <one-line expansion of the name>
Rem
Rem DESCRIPTION
Rem <short description of component this file declares/defines>
Rem
Rem NOTES
Rem <other useful comments, qualifications, etc.>
Rem
Rem MODIFIED (MM/DD/YY)
Rem vkhizder 06/18/09 - XbranchMerge vkhizder_bug6503115_10g from
Rem st_emcore_10.2.0.1.0
Rem rrawat 06/26/07 - Bug-5997153
Rem rrawat 07/23/07 - Backport rrawat_bug-5977153 from main
Rem agor 09/07/06 - Backport agor_bug-5478815 from
Rem st_emcore_10.2.0.1.0
Rem rrawat 07/11/06 - Backport rrawat_bug-5211702 from main
Rem rrawat 06/20/06 - Bug-5211702
Rem vkhizder 08/09/05 - fixing error handling due to agent fix for bug
Rem 4313253
Rem mgoodric 07/20/05 - add additional parameter to after_load proc
Rem agor 05/18/05 - catch exceptions
Rem shuberma 04/21/05 - Modifying handle_orphaned_snapshots to avoid
Rem infinite loop.
Rem shuberma 04/15/05 - Remove deletes from post load procedures.
Rem akskumar 04/07/05 - copy_snapshot to return saved config guid
Rem asaraswa 04/07/05 - modify post-load procedure for host configs to
Rem update the patches column in mgmt_hc_os_summary
Rem mgoodric 04/06/05 - fix 10.2GC Beta upgrade bug
Rem asaraswa 02/25/05 - adding code to populate mgmt_inv_summary
Rem agor 02/16/05 - fix 4189156
Rem vkhizder 12/03/04 - delete performance improvements
Rem mningomb 12/10/04 - Cutover Critical patch advisories policy to 10.2
Rem policy framework
Rem vkhizder 11/24/04 - adding comments
Rem akskumar 11/10/04 - saving configurations for composite targets
Rem shuberma 09/27/04 - Remove call to old policy fw.
Rem shuberma 09/03/04 - Adding procedure for running on-demand metrics
Rem that are dependent upon snapshots.
Rem vkhizder 07/27/04 - host config loading changes
Rem rpinnama 06/12/04 - Remove set ECHO
Rem vkhizder 03/03/04 -
Rem vkhizder 02/17/04 - making csa clients non-targets
Rem vkhizder 12/29/03 - fixing null comparinson
Rem mgoodric 10/21/03 - check mgmt_current_metric_errors for errors
Rem vkhizder 10/17/03 - commenting on effects of after-load procedure
Rem shuberma 08/12/03 - Moving policy check until after commit and
Rem delete of old snapshot. THis is because the
Rem policy checker sees the old data
Rem vkhizder 07/23/03 - only loader metadata tables/views are relevant
Rem for deletion/copy
Rem mgoodric 06/13/03 - added COPY_SNAPSHOT
Rem vkhizder 06/03/03 - handing target deletion
Rem shuberma 06/02/03 - Adding call to run policy checks on refresh
Rem vkhizder 05/19/03 - adding oui platform
Rem vkhizder 03/25/03 - more framework improvements
Rem mgoodric 03/15/03 - use exceptions to raise errors
Rem mgoodric 03/14/03 - add PRELOAD_CALLBACK and POSTLOAD_CALLBACK
Rem agor 01/02/03 - add clonable
Rem vkhizder 12/18/02 - adding some generic snapshot related procedures
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem
CREATE OR REPLACE PACKAGE BODY ECM_CT
IS
-- a list of host config snapshot ids
TYPE T_SNAP_GUID_TABLE IS TABLE OF MGMT_ECM_SNAPSHOT.SNAPSHOT_GUID%TYPE;
-- Private constant for the number of retries of snapshot copy operation
-- when snapshot changes while the copy operation is in progress
NUM_OF_SNAPSHOT_COPY_RETRIES CONSTANT NUMBER := 3;
SAVE_COMPOSITE_FAIL constant NUMBER:=0;
SAVE_COMPOSITE_SUC constant NUMBER:=1;
SAVE_COMPOSITE_HOST constant NUMBER:=2;
-- *********************************************************************
PROCEDURE INSERT_SNAPSHOT_INFO (v_type VARCHAR2, v_type_version VARCHAR2,
v_target_name VARCHAR2, v_target_type VARCHAR2,
v_display_target_name VARCHAR2, v_display_target_type VARCHAR2,
v_description VARCHAR2, v_start_timestamp DATE,
v_elapsed_time NUMBER, v_current VARCHAR2,
v_collection_error_stream VARCHAR2, v_guid OUT RAW) IS
BEGIN
INSERT INTO MGMT_ECM_SNAPSHOT (snapshot_type, snapshot_type_version,
target_name, target_type, display_target_name, display_target_type,
description, start_timestamp, elapsed_time, is_current,
collection_error_stream)
VALUES (v_type, v_type_version,
v_target_name, v_target_type, v_display_target_name,
v_display_target_type, v_description, v_start_timestamp,
v_elapsed_time, v_current, v_collection_error_stream)
RETURNING snapshot_guid INTO v_guid;
END INSERT_SNAPSHOT_INFO;
-- *********************************************************************
PROCEDURE INSERT_CONTAINER (v_snapshot_guid RAW, v_type VARCHAR2,
v_name VARCHAR2, v_location VARCHAR2, v_oui_platform NUMBER,
v_clonable VARCHAR2, v_guid OUT RAW) IS
BEGIN
INSERT INTO MGMT_INV_CONTAINER (container_type, container_name,
snapshot_guid, container_location, oui_platform, is_clonable)
VALUES (v_type, v_name, v_snapshot_guid, v_location,
v_oui_platform, v_clonable)
RETURNING container_guid INTO v_guid;
END INSERT_CONTAINER;
-- *********************************************************************
PROCEDURE INSERT_COMPONENT (v_name VARCHAR2, v_version VARCHAR2,
v_container_guid RAW, v_desription VARCHAR2,
v_external_name VARCHAR2, v_languages VARCHAR2,
v_installed_location VARCHAR2, v_installer_version VARCHAR2,
v_min_deinstaller_version VARCHAR2, v_isTopLevel VARCHAR2,
v_timestamp STRING, v_guid OUT RAW) IS
BEGIN
INSERT INTO MGMT_INV_COMPONENT (name, version, container_guid,
description, external_name, languages, installed_location,
installer_version, min_deinstaller_version,is_top_level, timestamp)
VALUES(v_name, v_version, v_container_guid, v_desription,
v_external_name, v_languages, v_installed_location,
v_installer_version, v_min_deinstaller_version, v_isTopLevel,
to_date(v_timestamp,'yyyy-mm-dd hh24:mi:ss'))
RETURNING component_guid INTO v_guid;
END INSERT_COMPONENT;
-- *********************************************************************
PROCEDURE INSERT_PATCH (v_id VARCHAR2, v_container_guid RAW,
v_timestamp STRING,
v_description VARCHAR2, v_isRollbackable VARCHAR2,
v_guid OUT RAW) IS
BEGIN
INSERT INTO MGMT_INV_PATCH (id, container_guid, timestamp,
description, is_rollbackable)
VALUES (v_id, v_container_guid,
to_date(v_timestamp,'yyyy-mm-dd hh24:mi:ss'),
v_description, v_isRollbackable)
RETURNING patch_guid INTO v_guid;
END INSERT_PATCH;
-- *********************************************************************
PROCEDURE INSERT_PATCHSET (v_name VARCHAR2, v_version VARCHAR2,
v_container_guid RAW, v_description VARCHAR2,
v_external_name VARCHAR2, v_installer_version VARCHAR2,
v_min_deinstaller_version VARCHAR2, v_timestamp STRING,
v_guid OUT RAW) IS
BEGIN
INSERT INTO MGMT_INV_PATCHSET (name, version, container_guid, description,
external_name, installer_version, min_deinstaller_version, timestamp)
VALUES (v_name, v_version, v_container_guid, v_description,
v_external_name, v_installer_version, v_min_deinstaller_version,
to_date(v_timestamp,'yyyy-mm-dd hh24:mi:ss'))
RETURNING patchset_guid INTO v_guid;
END INSERT_PATCHSET;
-- *********************************************************************
PROCEDURE INSERT_GEN_SNAPSHOT_INFO (v_snapshot_type VARCHAR2,
v_target_id VARCHAR2, v_target_name VARCHAR2, v_target_type VARCHAR2,
v_display_target_name VARCHAR2, v_display_target_type VARCHAR2,
v_description VARCHAR2, v_start_timestamp DATE,
v_elapsed_time NUMBER, v_current VARCHAR2,
v_status VARCHAR2, v_message VARCHAR2, v_creator VARCHAR2,
v_guid OUT VARCHAR2) IS
BEGIN
INSERT INTO MGMT_ECM_GEN_SNAPSHOT (snapshot_type, target_guid,
target_name, target_type, display_target_name, display_target_type,
description, start_timestamp, elapsed_time, is_current,
status, message, creator)
VALUES (v_snapshot_type, HEXTORAW(v_target_id), v_target_name,
v_target_type, v_display_target_name, v_display_target_type,
v_description, v_start_timestamp, v_elapsed_time, v_current,
v_status, v_message, v_creator)
RETURNING RAWTOHEX(snapshot_guid) INTO v_guid;
END INSERT_GEN_SNAPSHOT_INFO;
-- *********************************************************************
-- use current EM user as snapshot creator
PROCEDURE INSERT_GEN_SNAPSHOT_INFO (v_snapshot_type VARCHAR2,
v_target_id VARCHAR2, v_target_name VARCHAR2, v_target_type VARCHAR2,
v_display_target_name VARCHAR2, v_display_target_type VARCHAR2,
v_description VARCHAR2, v_start_timestamp DATE,
v_elapsed_time NUMBER, v_current VARCHAR2,
v_status VARCHAR2, v_message VARCHAR2, v_guid OUT VARCHAR2) IS
BEGIN
INSERT INTO MGMT_ECM_GEN_SNAPSHOT (snapshot_type, target_guid,
target_name, target_type, display_target_name, display_target_type,
description, start_timestamp, elapsed_time, is_current,
status, message, creator)
VALUES (v_snapshot_type, HEXTORAW(v_target_id), v_target_name,
v_target_type, v_display_target_name, v_display_target_type,
v_description, v_start_timestamp, v_elapsed_time, v_current,
v_status, v_message, mgmt_user.get_current_em_user())
RETURNING RAWTOHEX(snapshot_guid) INTO v_guid;
END INSERT_GEN_SNAPSHOT_INFO;
-- *********************************************************************
-- Private
PROCEDURE CT_LOG (v_message IN VARCHAR2) IS
l_index NUMBER := 1;
l_length NUMBER := 0;
BEGIN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
BEGIN
l_length := LENGTH(v_message);
WHILE (l_length > 0)
LOOP
DBMS_OUTPUT.PUT_LINE (SUBSTR(v_message,l_index,255));
l_length := l_length - 255;
l_index := l_index + 255;
END LOOP;
-- ignore any errors
EXCEPTION WHEN OTHERS THEN NULL;
END;
END IF;
END CT_LOG;
-- *********************************************************************
/* VK: This method is not used any longer.. for now just commenting it out in
case we want to revert back to using (a derivative of) this code
-- Private
PROCEDURE FAIL_WITH_STATUS (
v_code IN NUMBER,
v_message IN VARCHAR2) IS
l_error_message VARCHAR2(32767) := NULL;
BEGIN
BEGIN
IF (v_message IS NOT NULL) THEN
l_error_message := v_message;
ELSE
l_error_message := SQLERRM;
END IF;
-- truncate message if >= 4000
IF (LENGTH(l_error_message) >= ECM_CT.G_MAX_MESSAGE_LEN)
THEN
l_error_message := SUBSTR(l_error_message,1,(ECM_CT.G_MAX_MESSAGE_LEN-3)) || '...';
END IF;
-- VK: "SNAPSHOT" would have to be registered with
-- mgmt_log.register_logging_module before it can be used. Also,
-- this "Error in snapshot: Error:" message seems a bit strange.. And
-- should the truncation above account for this prefix?
-- After-load procedure should just throw the exception..
-- I believe the generic loader will log it
-- MGMT_LOG.LOG_ERROR ('SNAPSHOT', null,
-- 'Error in snapshot: ' || ' Error: ' || l_error_message);
-- ignore any errors
EXCEPTION WHEN OTHERS THEN NULL;
END;
raise_application_error (v_code, l_error_message);
END FAIL_WITH_STATUS;
*/
-- *********************************************************************
-- Called by Generic Loader before entering metric collection data
-- to create a snapshot in MGMT_ECM_GEN_SNAPSHOT
--
-- Inputs: target_guid - guid of collection target
-- collection_name - snapshot type
-- collection_timestamp - collection time at target
-- emd_url - Agent's URL
-- version - snapshot version
-- config_metadata_version - configuration metadata version
--
-- Output: ecm_snapshot_guid - guid of the new snapshot
PROCEDURE PRELOAD_CALLBACK (
v_input IN SMP_EMD_NVPAIR_ARRAY,
v_preLoadOutput OUT SMP_EMD_NVPAIR_ARRAY) IS
-- obtained from v_input
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_snapshot_type MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE := NULL;
l_start_timestamp MGMT_ECM_GEN_SNAPSHOT.START_TIMESTAMP%TYPE := SYSDATE;
-- returned in v_preLoadOutput
l_snapshot_guid VARCHAR2(64) := NULL;
-- obtained from mgmt_targets
l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := NULL;
l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE := NULL;
l_display_target_name MGMT_TARGETS.DISPLAY_NAME%TYPE := NULL;
l_display_target_type MGMT_TARGETS.TYPE_DISPLAY_NAME%TYPE := NULL;
-- local variables
l_input_name VARCHAR(256) := NULL;
l_null_description CONSTANT MGMT_ECM_GEN_SNAPSHOT.DESCRIPTION%TYPE := NULL;
l_null_message CONSTANT MGMT_ECM_GEN_SNAPSHOT.MESSAGE%TYPE := NULL;
l_null_elapsed_time CONSTANT MGMT_ECM_GEN_SNAPSHOT.ELAPSED_TIME%TYPE := -1;
l_null_creator CONSTANT MGMT_ECM_GEN_SNAPSHOT.CREATOR%TYPE := NULL;
BEGIN
FOR i IN 1..v_input.COUNT
LOOP
l_input_name := UPPER(v_input(i).name);
IF (l_input_name = ECM_CT.G_TARGET_GUID) THEN
l_target_guid := HEXTORAW(v_input(i).value);
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_target_guid || '''');
END IF;
ELSIF (l_input_name = ECM_CT.G_COLLECTION_NAME) THEN
l_snapshot_type := v_input(i).value;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_snapshot_type || '''');
END IF;
ELSIF (l_input_name = ECM_CT.G_COLLECTION_TIMESTAMP) THEN
l_start_timestamp := TO_DATE(v_input(i).value,MGMT_GLOBAL.G_AGENT_DATE_FORMAT);
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_start_timestamp || '''');
END IF;
END IF;
END LOOP;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Retrieving target data with ''' || l_target_guid || '''');
END IF;
BEGIN
SELECT target_name, target_type, display_name, type_display_name
INTO l_target_name, l_target_type, l_display_target_name, l_display_target_type
FROM mgmt_targets
WHERE (target_guid = l_target_guid);
-- bail out when target doesn't exist
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE MGMT_GLOBAL.target_does_not_exist;
END;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Creating new ''' || l_snapshot_type || ''' snapshot for target ''' || l_target_name || '''');
END IF;
INSERT_GEN_SNAPSHOT_INFO (
l_snapshot_type, RAWTOHEX(l_target_guid),
l_target_name, l_target_type,
l_display_target_name, l_display_target_type,
l_null_description, l_start_timestamp, l_null_elapsed_time,
ECM_CT.G_IS_TRANSIENT_TYPE, ECM_CT.G_NOT_FOUND_STATUS,
l_null_message, l_null_creator, l_snapshot_guid);
COMMIT;
-- Store the snapshot guid for later use
v_preLoadOutput := SMP_EMD_NVPAIR_ARRAY();
v_preLoadOutput.extend(1);
v_preLoadOutput(1) := SMP_EMD_NVPAIR(ECM_CT.G_ECM_SNAPSHOT_ID,
l_snapshot_guid);
END PRELOAD_CALLBACK;
-- *********************************************************************
-- Called by Generic Loader after entering metric collection data
-- to calculate change history for the snapshot in MGMT_ECM_GEN_SNAPSHOT
--
-- Inputs: target_guid - guid of collection target
-- collection_name - snapshot type
-- collection_timestamp - collection time at target
-- emd_url - Agent's URL
-- version - snapshot version
-- config_metadata_version - configuration metadata version
--
-- Inputs: ecm_snapshot_guid - guid of the new snapshot
PROCEDURE POSTLOAD_CALLBACK (
v_input IN SMP_EMD_NVPAIR_ARRAY,
v_preLoadOutput IN SMP_EMD_NVPAIR_ARRAY) IS
-- obtained from v_input
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_snapshot_type MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE := NULL;
l_start_timestamp MGMT_ECM_GEN_SNAPSHOT.START_TIMESTAMP%TYPE := SYSDATE;
-- obtained from v_preLoadOutput
l_snapshot_guid MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE;
-- obtained from mgmt_targets
l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := NULL;
l_target_type MGMT_TARGETS.TARGET_TYPE%TYPE := NULL;
l_display_target_name MGMT_TARGETS.DISPLAY_NAME%TYPE := NULL;
l_display_target_type MGMT_TARGETS.TYPE_DISPLAY_NAME%TYPE := NULL;
-- obtained from mgmt_ecm_snapshot_metadata
l_after_load_proc_name MGMT_ECM_SNAPSHOT_METADATA.AFTER_LOAD_PROC_NAME%TYPE := NULL;
l_history_on MGMT_ECM_SNAPSHOT_METADATA.HISTORY_ON%TYPE := 'N';
-- useful types
TYPE T_NUMBER_TABLE IS TABLE OF NUMBER;
TYPE T_VARCHAR2_TABLE IS TABLE OF VARCHAR2(4000);
TYPE T_GUID_TABLE IS TABLE OF MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE;
-- used for collecting metric warnings
l_error_message VARCHAR2(32767) := NULL;
l_metric_error_type T_NUMBER_TABLE;
l_metric_error_message T_VARCHAR2_TABLE;
l_lock_snapshot_list T_GUID_TABLE;
-- used to determine lock conditions
l_lock_guid VARCHAR2(64) := NULL;
l_current_snapshot_guid MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE;
-- used to form call to after_load_proc
l_sql VARCHAR2(200) := NULL;
l_metadata_version VARCHAR2(64) := NULL;
l_afterLoadInput SMP_EMD_NVPAIR_ARRAY := NULL;
-- used to detect old-style AFTER_LOAD interface
wrong_number_of_arguments EXCEPTION;
PRAGMA EXCEPTION_INIT(wrong_number_of_arguments, -6553);
-- local variables
l_input_name VARCHAR(256) := NULL;
l_preLoadOutput_name VARCHAR(256) := NULL;
l_null_description CONSTANT MGMT_ECM_GEN_SNAPSHOT.DESCRIPTION%TYPE := NULL;
l_null_message CONSTANT MGMT_ECM_GEN_SNAPSHOT.MESSAGE%TYPE := NULL;
l_null_elapsed_time CONSTANT MGMT_ECM_GEN_SNAPSHOT.ELAPSED_TIME%TYPE := -1;
l_null_creator CONSTANT MGMT_ECM_GEN_SNAPSHOT.CREATOR%TYPE := NULL;
BEGIN
BEGIN
FOR i IN 1..v_input.COUNT
LOOP
l_input_name := UPPER(v_input(i).name);
IF (l_input_name = ECM_CT.G_TARGET_GUID) THEN
l_target_guid := HEXTORAW(v_input(i).value);
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_target_guid || '''');
END IF;
ELSIF (l_input_name = ECM_CT.G_COLLECTION_NAME) THEN
l_snapshot_type := v_input(i).value;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_snapshot_type || '''');
END IF;
ELSIF (l_input_name = ECM_CT.G_COLLECTION_TIMESTAMP) THEN
l_start_timestamp := TO_DATE(v_input(i).value,MGMT_GLOBAL.G_AGENT_DATE_FORMAT);
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_start_timestamp || '''');
END IF;
ELSIF (l_input_name = ECM_CT.G_CONFIG_METADATA_VERSION) THEN
l_metadata_version := v_input(i).value;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_input(i).name || ' = ''' || l_metadata_version || '''');
END IF;
END IF;
END LOOP;
FOR i IN 1..v_preLoadOutput.COUNT
LOOP
l_preLoadOutput_name := UPPER(v_preLoadOutput(i).name);
IF (l_preLoadOutput_name = ECM_CT.G_ECM_SNAPSHOT_ID) THEN
l_snapshot_guid := HEXTORAW(v_preLoadOutput(i).value);
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('--> ' || v_preLoadOutput(i).name || ' = ''' || l_snapshot_guid || '''');
END IF;
END IF;
END LOOP;
-- In addition to retrieving target data, the following makes sure
-- target has not been deleted yet (by checking for l_target_guid).
-- If ecm handle_target_deletion callback is started before this check, the
-- following check will see that the target is no longer in mgmt_targets table.
-- If this check succeeds and target deletion as well as the callback start
-- after this check, the target guid will be removed from the snapshot row
-- committed in the preload_callback procedure with the ecm handle_target_deletion
-- callback procedure (which will see the row due to the prior commit.)
-- The snapshot itself will be removed as part of delete_orphan_snapshots procedure.
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Retrieving target data with ''' || l_target_guid || '''');
END IF;
BEGIN
SELECT target_name, target_type, display_name, type_display_name
INTO l_target_name, l_target_type, l_display_target_name, l_display_target_type
FROM mgmt_targets
WHERE (target_guid = l_target_guid);
-- bail out if target doesn't exist
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE MGMT_GLOBAL.target_does_not_exist;
END;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Collecting metric warnings for target_guid ''' || l_target_guid || '''');
END IF;
BEGIN
SELECT metric_error_type, metric_error_message BULK COLLECT
INTO l_metric_error_type, l_metric_error_message
FROM mgmt_metric_errors
WHERE target_guid = l_target_guid
AND coll_name = l_snapshot_type
AND collection_timestamp = l_start_timestamp
AND metric_error_message IS NOT NULL;
FOR i IN 1..l_metric_error_message.COUNT
LOOP
IF (l_metric_error_type(i) = ECM_CT.G_IS_METRIC_ERROR)
THEN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Found metric errors for target_guid ''' || l_target_guid || '''');
END IF;
-- delete snapshot
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = l_snapshot_guid;
RETURN;
END IF;
IF (l_metric_error_message(i) IS NOT NULL)
THEN
IF (l_error_message IS NULL) THEN
l_error_message := l_metric_error_message(i);
ELSE
l_error_message := l_error_message || ' ' || CHR(10) || l_metric_error_message(i);
END IF;
END IF;
END LOOP;
-- truncate message if >= 4000
IF (LENGTH(l_error_message) >= ECM_CT.G_MAX_MESSAGE_LEN)
THEN
l_error_message := SUBSTR(l_error_message,1,(ECM_CT.G_MAX_MESSAGE_LEN-3)) || '...';
END IF;
UPDATE mgmt_ecm_gen_snapshot
SET message = l_error_message
WHERE (snapshot_guid = l_snapshot_guid);
-- ignore no metric errors
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Finding after_load_proc_name procedure and executing it');
END IF;
BEGIN
SELECT after_load_proc_name
INTO l_after_load_proc_name
FROM mgmt_ecm_snapshot_metadata
WHERE (snapshot_type = l_snapshot_type)
AND (target_type = l_target_type)
AND (kind = ECM_CT.G_IS_LOADER);
-- ignore when no after_load_proc
EXCEPTION
-- should we raise exception if there is no metadata? For now, proceed.
WHEN NO_DATA_FOUND THEN NULL;
END;
IF (l_after_load_proc_name IS NOT NULL) THEN
-- Store the metadata version for integrators to use
l_afterLoadInput := SMP_EMD_NVPAIR_ARRAY();
l_afterLoadInput.extend(1);
l_afterLoadInput(1) := SMP_EMD_NVPAIR(ECM_CT.G_CONFIG_METADATA_VERSION,
l_metadata_version);
l_sql := 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(l_after_load_proc_name) || '(:snapshot_guid,:afterLoadInput)';
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Executing ' || l_sql || ' with ''' || l_snapshot_guid || '''');
END IF;
BEGIN
EXECUTE IMMEDIATE l_sql USING l_snapshot_guid, l_afterLoadInput;
EXCEPTION
-- Retry callback if new parameter is not currently supported by callback
WHEN wrong_number_of_arguments THEN
-- ORA-06553: PLS-306: wrong number or types of arguments in call to 'AFTER_LOAD'
l_sql := 'CALL ' || EM_CHECK.QUALIFIED_SQL_NAME(l_after_load_proc_name) || '(:snapshot_guid)';
EXECUTE IMMEDIATE l_sql USING l_snapshot_guid;
END;
-- Re-obtaining various target/snapshot information in case it was
-- changed by the after-load procedure. CSA performs such changes.
-- Generally, integrators should NOT perform such changes.
SELECT target_guid, target_name, target_type,
display_target_name, display_target_type, snapshot_type
INTO l_target_guid, l_target_name, l_target_type,
l_display_target_name, l_display_target_type, l_snapshot_type
FROM mgmt_ecm_gen_snapshot
WHERE snapshot_guid = l_snapshot_guid;
-- Note: start_timestamp and elapsed_time fields could also be changed by
-- after-load procedure but we do not reacquire them above as they are
-- not used below.
END IF;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Trying to lock current snapshot');
END IF;
BEGIN
SELECT snapshot_guid INTO l_current_snapshot_guid
FROM mgmt_ecm_gen_snapshot
WHERE (snapshot_type = l_snapshot_type)
AND (target_name = l_target_name)
AND (target_type = l_target_type)
AND (is_current = ECM_CT.G_IS_CURRENT_TYPE)
FOR UPDATE OF is_current;
-- ignore when no current snapshot
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
-- Lock on new row if no lock obtained on current snapshot
IF (l_current_snapshot_guid IS NULL) THEN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Inserting locking snapshot with new guid');
END IF;
INSERT_GEN_SNAPSHOT_INFO (
l_snapshot_type, NULL,
l_target_name, l_target_type,
l_display_target_name, l_display_target_type,
l_null_description, SYSDATE, l_null_elapsed_time,
ECM_CT.G_IS_LOCKED_TYPE, ECM_CT.G_NOT_FOUND_STATUS,
l_null_message, l_null_creator, l_lock_guid);
COMMIT;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Locking snapshot with ''' || l_lock_guid || '''');
END IF;
BEGIN
SELECT snapshot_guid BULK COLLECT INTO l_lock_snapshot_list
FROM mgmt_ecm_gen_snapshot
WHERE (snapshot_type = l_snapshot_type)
AND (target_name = l_target_name)
AND (target_type = l_target_type)
AND (is_current = ECM_CT.G_IS_LOCKED_TYPE)
FOR UPDATE OF is_current;
-- bail out with error when lock is missing
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (MGMT_GLOBAL.SNAPSHOT_LOCK_ERR,
'Could not find just created lock row');
END;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Retrying lock of current snapshot');
END IF;
-- We are now locked on L snapshot. If a new current
-- snapshot appears meanwhile, let's try to lock on
-- it as well and get its guid.
BEGIN
SELECT snapshot_guid INTO l_current_snapshot_guid
FROM mgmt_ecm_gen_snapshot
WHERE (snapshot_type = l_snapshot_type)
AND (target_name = l_target_name)
AND (target_type = l_target_type)
AND (is_current = ECM_CT.G_IS_CURRENT_TYPE)
FOR UPDATE OF is_current;
-- ignore if we still can't get the lock
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END IF;
BEGIN
SELECT history_on
INTO l_history_on
FROM mgmt_ecm_snapshot_metadata
WHERE snapshot_type = l_snapshot_type
AND target_type = l_target_type
AND kind = ECM_CT.G_IS_PRESENTATION;
EXCEPTION
-- for now don't worry if metadata is missing
WHEN NO_DATA_FOUND THEN NULL;
END;
IF (l_history_on = 'Y') THEN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Calculating history for snapshot_guid ''' || l_snapshot_guid || '''');
END IF;
MGMT_ECM_SNAPSHOT_PKG.DO_AND_SAVE_HISTORY (
l_snapshot_type,
l_target_type,
l_snapshot_guid,
l_current_snapshot_guid);
END IF;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Making snapshot current for snapshot_guid ''' || l_snapshot_guid || '''');
END IF;
-- delete old current snapshot
IF (l_current_snapshot_guid IS NOT NULL) THEN
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = l_current_snapshot_guid;
END IF;
-- make new snapshot into current snapshot
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_CURRENT_TYPE,
status = ECM_CT.G_COLLECTED_STATUS
WHERE snapshot_guid = l_snapshot_guid;
COMMIT;
-- bail out after deleting temporary snapshot
EXCEPTION
WHEN OTHERS THEN
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = l_snapshot_guid;
RAISE;
END;
IF (l_lock_guid IS NOT NULL) THEN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Deleting snapshot with ''' || l_lock_guid || '''');
END IF;
BEGIN
-- Due to bug 4254731, this delete is changed to an update. The update will
-- ensure that the entry gets cleaned up when other deleted snapshots are cleaned
-- up
-- DELETE FROM mgmt_ecm_gen_snapshot
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = HEXTORAW(l_lock_guid);
COMMIT;
-- bail out when nothing to do
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END IF;
-- Run policy checks for this snapshot. The snapshot type, target type and
-- target name are used to determine if there are any policies that need
-- to be run because they specified ON_REFRESH.
-- This is here because the is_current flag is expected to be 'Y' for the
-- snapshot being tested.
-- Also, it is done in the same transaction as the appearance of the new
-- snapshot so the policy violations reported are always consistent with
-- the current snapshot displayed.
BEGIN
-- New way is to collection dependent metrics which will evaluate policies.
EM_COLL_UTIL.run_snapshot_dependent_colls( p_snapshotType=>l_snapshot_type,
p_targetType=>l_target_type,
p_targetName=>l_target_name );
-- ignore all errors
EXCEPTION WHEN OTHERS THEN NULL;
END;
END POSTLOAD_CALLBACK;
-- *********************************************************************
-- Called to delete a generic snapshot
--
-- Inputs: snapshot_guid - guid of the snapshot to be deleted
PROCEDURE DELETE_SNAPSHOT (v_snapshot_guid IN RAW) IS
BEGIN
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = v_snapshot_guid;
COMMIT;
END DELETE_SNAPSHOT;
/************************ old code for DELETE_SNAPSHOT *******************
-- obtained from mgmt_ecm_gen_snapshot
l_snapshot_type MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE;
l_target_type MGMT_ECM_GEN_SNAPSHOT.TARGET_TYPE%TYPE;
-- obtained from mgmt_ecm_snapshot_md_tables
l_table_names VARCHAR2_TABLE;
-- sql to be executed
l_sql VARCHAR2(200) := NULL;
BEGIN
IF ECM_CT.G_IS_DEBUG = 1 THEN
CT_LOG('-- Deleting snapshot_guid ''' || v_snapshot_guid || '''');
END IF;
BEGIN
SELECT snapshot_type, target_type INTO l_snapshot_type, l_target_type
FROM mgmt_ecm_gen_snapshot
WHERE snapshot_guid = v_snapshot_guid
FOR UPDATE OF is_current;
-- bail out when nothing to do
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN;
END;
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = v_snapshot_guid;
COMMIT;
BEGIN
SELECT t.name BULK COLLECT INTO l_table_names
FROM mgmt_ecm_snapshot_metadata m, mgmt_ecm_snapshot_md_tables t
WHERE m.snapshot_type = l_snapshot_type
AND m.target_type = l_target_type
AND m.kind = ECM_CT.G_IS_LOADER
AND m.metadata_id = t.metadata_id
ORDER BY tbl_order DESC;
IF l_table_names IS NOT NULL AND l_table_names.COUNT > 0 THEN
FOR i IN l_table_names.FIRST..l_table_names.LAST
LOOP
IF ECM_CT.G_IS_DEBUG = 1 THEN
CT_LOG('-- Deleting data in table "' || l_table_names(i) || '"');
END IF;
BEGIN
l_sql := 'DECLARE guid RAW(16); ' ||
'BEGIN guid := :1;' ||
' DELETE FROM ' || em_check.sql_object_name(
em_check.enquote_name(l_table_names(i), false)) ||
' WHERE ' || ECM_CT.G_ECM_SNAPSHOT_ID || ' = guid; ' ||
'END;';
IF ECM_CT.G_IS_DEBUG = 1 THEN
CT_LOG('-- Executing ' || l_sql || ' with ''' || v_snapshot_guid || '''');
END IF;
EXECUTE IMMEDIATE l_sql USING v_snapshot_guid;
COMMIT;
-- ignore when table is empty
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END LOOP;
END IF;
-- don't worry when no loader tables / views are found
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
IF ECM_CT.G_IS_DEBUG = 1 THEN
CT_LOG('-- Destroying snapshot_guid ''' || v_snapshot_guid || '''');
END IF;
DELETE FROM mgmt_ecm_gen_snapshot
WHERE snapshot_guid = v_snapshot_guid;
COMMIT;
END DELETE_SNAPSHOT;
************************ old code for DELETE_SNAPSHOT *******************/
-- *********************************************************************
PROCEDURE HANDLE_ORPHAN_SNAPSHOTS (v_number_of_days IN NUMBER DEFAULT 3) IS
-- number of processed snapshots
l_num_snapshots NUMBER := 0;
BEGIN
LOOP
BEGIN
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE is_current <> ECM_CT.G_IS_CURRENT_TYPE
AND is_current <> ECM_CT.G_IS_NOTCURRENT_TYPE
AND is_current <> ECM_CT.G_IS_DELETED_TYPE
AND (SYSDATE - v_number_of_days) > saved_timestamp
AND ROWNUM <= MGMT_GLOBAL.MAX_COMMIT;
l_num_snapshots := SQL%ROWCOUNT;
IF l_num_snapshots > 0 THEN
COMMIT;
END IF;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for UPDATE of 0 rows.
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
ORPHAN_SNAP_CREATION_ERR_1,
'Encountered a problem while setting up orphan ECM snapshots for ' ||
'deletion. Will try to process orphan snapshots one by one. ' ||
'Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_WARN);
-- If there are no errors during one-by-one processing of orphans,
-- try to continue with the outer loop
l_num_snapshots := MGMT_GLOBAL.MAX_COMMIT + 1;
-- Update snapshots one-by-one
FOR snap IN
(SELECT snapshot_guid FROM mgmt_ecm_gen_snapshot
WHERE is_current <> ECM_CT.G_IS_CURRENT_TYPE
AND is_current <> ECM_CT.G_IS_NOTCURRENT_TYPE
AND (SYSDATE - v_number_of_days) > saved_timestamp
AND ROWNUM <= MGMT_GLOBAL.MAX_COMMIT)
LOOP
BEGIN
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = snap.snapshot_guid;
EXCEPTION
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
ORPHAN_SNAP_CREATION_ERR_2,
'Failed to set up orphan snapshot for deletion. ' ||
'Snapshot ID: ' || snap.snapshot_guid ||
' Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
-- Make sure that after we go through the rest of
-- orphan snapshots in this chunk, we exit the
-- outer loop since there is
-- at least one snapshot that we cannot update,
-- and so we do not want to end up in infinite loop
-- which keeps trying to update it.
l_num_snapshots := 0;
END;
END LOOP;
-- commit all successful updates
COMMIT;
END;
EXIT WHEN l_num_snapshots < MGMT_GLOBAL.MAX_COMMIT;
END LOOP;
RETURN;
END HANDLE_ORPHAN_SNAPSHOTS;
-- *********************************************************************
-- Called to delete all generic snapshots marked for deletion
-- Parameters:
-- p_target_types - target types array. Must match in length to snapshot
-- types array.
-- p_snapshot_types - snapshot types array. Must match in length to
-- target types array. Snapshot type at index i is for target type
-- at index i in the target types array.
-- p_is_primary - indicates if this is the primary thread or one of the
-- worker threads. In the former case, snapshots are removed from
-- mgmt_ecm_gen_snapshot table. In the latter case, they are just
-- marked for later removal by primary thread. There should be only one
-- primary thread.
-- p_result_out - returns number of snapshots that we attempt to delete by
-- converting them to G_IS_BEING_DELETED_TYPE state
PROCEDURE DELETE_SNAPSHOTS(p_target_types VARCHAR2_TABLE,
p_snapshot_types VARCHAR2_TABLE,
p_is_primary BOOLEAN := FALSE,
p_result_out OUT NUMBER)
IS
-- obtained from mgmt_ecm_snapshot_md_tables
l_table_names VARCHAR2_TABLE;
-- sql to be executed
l_sql VARCHAR2(512) := NULL;
-- current date
l_cur_date MGMT_ECM_GEN_SNAPSHOT.SAVED_TIMESTAMP%TYPE;
-- number of processed snapshots
l_num_snapshots NUMBER := 0;
-- number of processed rows (current and total)
l_num_rows NUMBER := 0;
l_total_num_rows NUMBER := 0;
-- temp variable for error case
l_any_work_left NUMBER := 0;
BEGIN
p_result_out := 0;
-- Delete each snapshot type
FOR snap_type_idx IN p_snapshot_types.FIRST..p_snapshot_types.LAST
LOOP
BEGIN
l_table_names := NULL;
BEGIN
SELECT t.name BULK COLLECT INTO l_table_names
FROM mgmt_ecm_snapshot_metadata m, mgmt_ecm_snapshot_md_tables t
WHERE m.target_type = p_target_types(snap_type_idx)
AND m.snapshot_type = p_snapshot_types(snap_type_idx)
AND m.kind = ECM_CT.G_IS_LOADER
AND m.metadata_id = t.metadata_id
ORDER BY tbl_order DESC;
EXCEPTION -- ignore if no tables in snapshot
WHEN NO_DATA_FOUND THEN NULL;
END;
-- Convert all snapshots of this type with is_current =
-- ECM_CT.G_IS_BEING_DELETED_TYPE into ECM_CT.G_IS_DELETED_TYPE. This is
-- assuming that the only reason there would be snapshots of
-- ECM_CT.G_IS_BEING_DELETED_TYPE type is if DB crashed and/or the
-- previous deletion process got interrupted. Noone but this thread
-- should be deleting snapshots of this type during the execution of
-- this method.
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_BEING_DELETED_TYPE;
IF SQL%ROWCOUNT > 0 THEN
COMMIT;
END IF;
-- loop over all snapshots; MAX_COMMIT snapshots at a time
LOOP
-- Mark snapshots to be deleted
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_BEING_DELETED_TYPE
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_DELETED_TYPE
AND rownum <= MGMT_GLOBAL.MAX_COMMIT;
l_num_snapshots := SQL%ROWCOUNT;
p_result_out := p_result_out + l_num_snapshots;
IF l_num_snapshots > 0 THEN
COMMIT;
ELSE
EXIT;
END IF;
-- if there are tables in the current snapshot type
IF l_table_names IS NOT NULL AND l_table_names.COUNT > 0 THEN
-- delete data from each table
FOR tbl_idx IN l_table_names.FIRST..l_table_names.LAST
LOOP
-- Delete rows from the table tbl_idx in chunks of size MAX_COMMIT
LOOP
BEGIN
l_sql :=
' DELETE FROM ' || em_check.sql_object_name(
em_check.enquote_name(l_table_names(tbl_idx), false)) ||
' WHERE ' || ECM_CT.G_ECM_SNAPSHOT_ID ||
' IN (SELECT snapshot_guid FROM mgmt_ecm_gen_snapshot ' ||
' WHERE target_type = ' ||
em_check.enquote_literal(
replace(p_target_types(snap_type_idx), '''', '''''')) ||
' AND snapshot_type = ' ||
em_check.enquote_literal(
replace(p_snapshot_types(snap_type_idx), '''', '''''')) ||
' AND is_current = ''' ||
ECM_CT.G_IS_BEING_DELETED_TYPE || ''') ' ||
' AND ROWNUM <= ' || MGMT_GLOBAL.MAX_COMMIT;
EXECUTE IMMEDIATE l_sql;
l_num_rows := SQL%ROWCOUNT;
l_total_num_rows := l_total_num_rows + l_num_rows;
-- Total number of rows could be less than MAX_COMMIT
-- if some tables have almost nothing to delete
IF l_total_num_rows >= MGMT_GLOBAL.MAX_COMMIT THEN
COMMIT;
l_total_num_rows := 0;
END IF;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for DELETE of 0 rows.
-- includes ref. constraints violations, deadlocks, etc.
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_3,
'Encountered a problem while purging data from table ' ||
l_table_names(tbl_idx) || ' for target type ' ||
p_target_types(snap_type_idx) || ' / snapshot type ' ||
p_snapshot_types(snap_type_idx) ||
'. Will try to remove data one snapshot at a time. ' ||
'Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_WARN);
-- Retry slowly and find out where the exception happened
COMMIT; -- commit anything that DID succeed since last commit
l_total_num_rows := 0;
l_cur_date := SYSDATE;
-- Delete rows for snapshots one-by-one and handle the errors
FOR snap IN
(SELECT snapshot_guid FROM mgmt_ecm_gen_snapshot
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_BEING_DELETED_TYPE)
LOOP
BEGIN
-- Delete data from the table l_table_names(tbl_idx)
-- for the snapshot
LOOP
l_sql :=
' DELETE FROM ' || em_check.sql_object_name(
em_check.enquote_name(l_table_names(tbl_idx), false)) ||
' WHERE ' || ECM_CT.G_ECM_SNAPSHOT_ID || ' = :1 ' ||
' AND ROWNUM <= ' || MGMT_GLOBAL.MAX_COMMIT;
EXECUTE IMMEDIATE l_sql USING snap.snapshot_guid;
l_num_rows := SQL%ROWCOUNT;
l_total_num_rows := l_total_num_rows + l_num_rows;
-- commit when enough rows are deleted
IF l_total_num_rows >= MGMT_GLOBAL.MAX_COMMIT THEN
COMMIT;
l_total_num_rows := 0;
END IF;
EXIT WHEN l_num_rows < MGMT_GLOBAL.MAX_COMMIT;
END LOOP;
-- If code ends up here for the last snapshot,
-- then all snapshots got
-- successfully removed without errors when
-- we tried one-by-one removal.
-- Next, since l_num_rows < MGMT_GLOBAL.MAX_COMMIT
-- at this point, the outter loop for the given table
-- will correctly exit: we are done with this table.
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for DELETE of 0 rows.
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_4,
'Failed to purge data from table ' ||
l_table_names(tbl_idx) || ' for target type ' ||
p_target_types(snap_type_idx) ||
' / snapshot type ' ||
p_snapshot_types(snap_type_idx) ||
'. Snapshot ID with the problem: ' ||
snap.snapshot_guid ||
'. Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
-- commit successful deletions
IF l_total_num_rows > 0 THEN
COMMIT;
l_total_num_rows := 0;
END IF;
-- l_total_num_rows is 0 in this case no matter what.
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_ERROR_TYPE,
saved_timestamp = l_cur_date
WHERE snapshot_guid = snap.snapshot_guid;
COMMIT;
-- see if any work is left from this current chunk
-- of snapshots
l_any_work_left := NULL;
BEGIN
SELECT 1 INTO l_any_work_left
FROM DUAL
WHERE EXISTS (SELECT *
FROM mgmt_ecm_gen_snapshot
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_BEING_DELETED_TYPE);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
IF l_any_work_left IS NOT NULL THEN
-- Now that we found error and changed the
-- snapshot's is_current, we'll try to proceed with
-- fast removal approach in the hope that this was
-- the only problematic snapshot.
-- Exit inner loop (for one-by-one processing of
-- snapshots), but not the outer loop for
-- table tbl_idx:
l_num_rows := MGMT_GLOBAL.MAX_COMMIT + 1;
EXIT;
ELSE
-- If no work is left, go on to the next set of
-- snapshots. Do not go back to the outer loop,
-- since there is a chance of getting exception
-- again, even with 0 snapshots to delete,
-- and then we'd end up here again with infinite
-- loop.
-- So, exit both inner and outer loops. If any
-- tables are left, they we'll go quickly through
-- them - through this or other paths.
l_num_rows := 0;
EXIT;
END IF;
END;
END LOOP; -- deleting rows for snapshots one-by-one
END; -- all code and exception handling for table tbl_idx
-- Exit the loop when the number of removed rows does not
-- reach the maximum (as specified by rownum constraint)
EXIT WHEN l_num_rows < MGMT_GLOBAL.MAX_COMMIT;
END LOOP; -- delete rows from table tbl_idx in chunks
END LOOP; -- delete data from each table
-- commit deletion of any remaining rows
IF l_total_num_rows > 0 THEN COMMIT; END IF;
END IF; -- if there are tables in the metadata for the snapshot type
-- Delete / update rows in mgmt_ecm_gen_snapshot table
BEGIN
IF p_is_primary THEN
-- delete rows from the mgmt_ecm_gen_snapshot table
DELETE FROM mgmt_ecm_gen_snapshot
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_BEING_DELETED_TYPE;
ELSE
-- update rows in the mgmt_ecm_gen_snapshot table to
-- be removed later by primary thread / job
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_ALMOST_DELETED_TYPE
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_BEING_DELETED_TYPE;
END IF;
COMMIT;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for delete / update
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_5,
'Encountered a problem while removing last snapshot rows' ||
' for target type ' || p_target_types(snap_type_idx) ||
' / snapshot type ' || p_snapshot_types(snap_type_idx) ||
'. Will try to remove the rows one snapshot at a time. ' ||
'Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_WARN);
-- find problematic snapshots, and remove / update others
l_cur_date := SYSDATE;
FOR snap IN
(SELECT snapshot_guid FROM mgmt_ecm_gen_snapshot
WHERE target_type = p_target_types(snap_type_idx)
AND snapshot_type = p_snapshot_types(snap_type_idx)
AND is_current = ECM_CT.G_IS_BEING_DELETED_TYPE)
LOOP
BEGIN
IF p_is_primary THEN
DELETE FROM mgmt_ecm_gen_snapshot
WHERE snapshot_guid = snap.snapshot_guid;
ELSE
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_ALMOST_DELETED_TYPE
WHERE snapshot_guid = snap.snapshot_guid;
END IF;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for delete / update
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_6,
'Failed to remove last snapshot row' ||
' for target type ' || p_target_types(snap_type_idx) ||
' / snapshot type ' || p_snapshot_types(snap_type_idx) ||
'. Snapshot ID with the problem: ' ||
snap.snapshot_guid ||
'. Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
-- Last try: make the snapshot an error snapshot and go on
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_ERROR_TYPE,
saved_timestamp = l_cur_date
WHERE snapshot_guid = snap.snapshot_guid;
END;
END LOOP; -- finding problematic snapshots
COMMIT;
END; -- delete / update rows in mgmt_ecm_gen_snapshot table
-- Exit loop when number of snapshots selected for removal
-- does not reach the maximum as determined by rownum constraint
-- in the query at the beginning of the loop
EXIT WHEN l_num_snapshots < MGMT_GLOBAL.MAX_COMMIT;
END LOOP; -- loop over all snapshots to delete, a chunk at a time
-- catch any exceptions for the snapshot type deletion
-- log error, and try to delete other snapshot types
EXCEPTION
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_7,
'Failed to purge deleted snapshots' ||
' for target type ' || p_target_types(snap_type_idx) ||
' / snapshot type ' || p_snapshot_types(snap_type_idx) ||
'. Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
END;
END LOOP; -- delete each snapshot type
END DELETE_SNAPSHOTS;
-- *********************************************************************
-- Called to delete all generic snapshots marked for deletion
-- This is the primary deletion thread if deletions are split into threads
-- To split out CSA snapshot deletion into separate thread:
-- (1) Uncomment 2 sections below that talk about CSA: one for
-- not including CSA in primary threads, and the other
-- that handles "almost deleted" snapshots created by other threads.
-- (2) Uncomment DELETE_CSA_SNAPSHOTS procedure in both this and pkgdef files.
PROCEDURE DELETE_SNAPSHOTS(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
l_target_types VARCHAR2_TABLE;
l_snapshot_types VARCHAR2_TABLE;
-- number of processed snapshots
l_num_snapshots NUMBER := 0;
l_result NUMBER := 0;
-- current date
l_cur_date MGMT_ECM_GEN_SNAPSHOT.SAVED_TIMESTAMP%TYPE;
-- continue deletion of target types?
l_continue_deletion BOOLEAN := TRUE;
BEGIN
-- select snapshot types to delete: all non-host-config, non CSA types.
BEGIN
SELECT target_type, snapshot_type
BULK COLLECT INTO l_target_types, l_snapshot_types
FROM mgmt_ecm_snapshot_metadata
WHERE kind = ECM_CT.G_IS_LOADER;
/*** Exclude CSA snapshot deletion from this procedure
AND NOT ((target_type = 'oracle_csa_collector' -- all but CSA
AND snapshot_type = 'oracle_csa')
OR (target_type = 'oracle_csa_client'
AND snapshot_type = 'oracle_csa_host'));
***/
-- handle the case when there are no types to work on
EXCEPTION
WHEN NO_DATA_FOUND THEN l_continue_deletion := FALSE;
END;
IF l_continue_deletion THEN
DELETE_SNAPSHOTS(l_target_types, l_snapshot_types, TRUE, l_result);
END IF;
pcb_params.rows_processed := l_result;
pcb_params.callback_result := 0;
pcb_params.error_code := 0;
pcb_params.error_msg := NULL;
/*** Following includes CSA and snapshots deleted in other threads
-- Delete "almost deleted" snapshots created by other threads.
LOOP
BEGIN
DELETE FROM mgmt_ecm_gen_snapshot
WHERE is_current = ECM_CT.G_IS_ALMOST_DELETED_TYPE
AND rownum <= MGMT_GLOBAL.MAX_COMMIT;
l_num_snapshots := SQL%ROWCOUNT;
COMMIT;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for delete
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_8,
'Encountered a problem while removing last snapshot rows. ' ||
'Will try to remove the rows one snapshot at a time. ' ||
'Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_WARN);
-- find problematic snapshots, and remove others
l_cur_date := SYSDATE;
FOR snap IN
(SELECT snapshot_guid FROM mgmt_ecm_gen_snapshot
WHERE is_current = ECM_CT.G_IS_ALMOST_DELETED_TYPE
AND rownum <= MGMT_GLOBAL.MAX_COMMIT)
LOOP
BEGIN
DELETE FROM mgmt_ecm_gen_snapshot
WHERE snapshot_guid = snap.snapshot_guid;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for delete
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_SNAP_ERR_9,
'Failed to remove last snapshot row. ' ||
' Snapshot ID with the problem: ' ||
snap.snapshot_guid ||
'. Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
-- Last try: make the snapshot an error snapshot and go on
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_ERROR_TYPE,
saved_timestamp = l_cur_date
WHERE snapshot_guid = snap.snapshot_guid;
END;
END LOOP; -- finding problematic snapshots, and removing others
-- do not exit the loop in case of exception
l_num_snapshots := MGMT_GLOBAL.MAX_COMMIT + 1;
COMMIT;
END;
EXIT WHEN l_num_snapshots < MGMT_GLOBAL.MAX_COMMIT;
END LOOP; -- end of deleting "almost deleted" snapshots
***/
-- This has to follow deletion of "almost deleted" snapshots,
-- since otherwise this procedure would orphan old "almost
-- deleted" snapshots by changing them back to "deleted"
-- snapshots before having a chance to actually delete them
-- first.
HANDLE_ORPHAN_SNAPSHOTS;
END DELETE_SNAPSHOTS;
-- *********************************************************************
/*** Uncomment this procedure if CSA is separated into a separate "thread".
**** See comments above in this file for what else needs to be done.
-- Called to delete all CSA snapshots marked for deletion
PROCEDURE DELETE_CSA_SNAPSHOTS IS
l_target_types VARCHAR2_TABLE;
l_snapshot_types VARCHAR2_TABLE;
BEGIN
l_target_types := VARCHAR2_TABLE();
l_snapshot_types := VARCHAR2_TABLE();
l_target_types.EXTEND;
l_snapshot_types.EXTEND;
l_target_types(l_target_types.LAST) := 'oracle_csa_collector';
l_snapshot_types(l_snapshot_types.LAST) := 'oracle_csa';
l_target_types.EXTEND;
l_snapshot_types.EXTEND;
l_target_types(l_target_types.LAST) := 'oracle_csa_client';
l_snapshot_types(l_snapshot_types.LAST) := 'oracle_csa_host';
DELETE_SNAPSHOTS(l_target_types, l_snapshot_types, FALSE);
END DELETE_CSA_SNAPSHOTS;
***/
-- *********************************************************************
PROCEDURE HANDLE_ORPHAN_HOST_CONFIGS (v_number_of_days IN NUMBER DEFAULT 3) IS
-- number of purged snapshots
l_num_snapshots NUMBER := 0;
BEGIN
-- delete rows from the "suspect orphans" table that did not turn out to
-- be orphans or that were removed on a previous run of purge; i.e. delete
-- any suspect snapshot ids for which there is no longer any snapshots
-- in the snapshot table with temporary status (e.g. T, D, L)
BEGIN
DELETE FROM mgmt_ecm_host_configs_to_del suspect
WHERE NOT EXISTS
(SELECT * FROM mgmt_ecm_snapshot s
WHERE suspect.snapshot_guid = s.snapshot_guid
AND s.is_current <> ECM_CT.G_IS_CURRENT_TYPE
AND s.is_current <> ECM_CT.G_IS_NOTCURRENT_TYPE);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
-- Setup orphan snapshots for removal: old suspects and
-- current snapshots without corresponding targets.
LOOP
BEGIN
UPDATE mgmt_ecm_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE is_current <> ECM_CT.G_IS_DELETED_TYPE AND
snapshot_guid in
(SELECT snapshot_guid
FROM
((SELECT snapshot_guid
FROM mgmt_ecm_host_configs_to_del suspect
WHERE (SYSDATE - suspect.ts) > v_number_of_days)
UNION ALL
(SELECT s.snapshot_guid
FROM mgmt_ecm_snapshot s
WHERE s.is_current = ECM_CT.G_IS_CURRENT_TYPE
AND NOT EXISTS (SELECT * FROM mgmt_targets t
WHERE t.target_name = s.target_name
AND t.target_type = s.target_type))
)
WHERE ROWNUM <= MGMT_GLOBAL.MAX_COMMIT
);
l_num_snapshots := SQL%ROWCOUNT;
IF l_num_snapshots > 0 THEN
COMMIT;
END IF;
EXCEPTION
-- NO_DATA_FOUND cannot be thrown for UPDATE of 0 rows
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
ORPHAN_HOST_CONFIG_ERR_10,
'Encountered a problem while setting up orphan host ' ||
'config snapshots for deletion. Will try to process ' ||
'orphan snapshots one by one. ' ||
'Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_WARN);
-- If there are no errors during one-by-one processing of orphans,
-- try to continue with the outer loop
l_num_snapshots := MGMT_GLOBAL.MAX_COMMIT + 1;
FOR snap IN
(SELECT snapshot_guid
FROM
((SELECT snapshot_guid
FROM mgmt_ecm_host_configs_to_del suspect
WHERE (SYSDATE - suspect.ts) > v_number_of_days)
UNION ALL
(SELECT s.snapshot_guid
FROM mgmt_ecm_snapshot s
WHERE s.is_current = ECM_CT.G_IS_CURRENT_TYPE
AND NOT EXISTS (SELECT * FROM mgmt_targets t
WHERE t.target_name = s.target_name
AND t.target_type = s.target_type))
)
WHERE ROWNUM <= MGMT_GLOBAL.MAX_COMMIT
)
LOOP
BEGIN
UPDATE mgmt_ecm_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = snap.snapshot_guid;
EXCEPTION
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
ORPHAN_HOST_CONFIG_ERR_11,
'Failed to set up orphan host config snapshot for ' ||
'deletion. Snapshot ID: ' || snap.snapshot_guid ||
' Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
-- Make sure that after we go through the rest of
-- orphan snapshots in this chunk, we exit the
-- outer loop since there is
-- at least one snapshot that we cannot update,
-- and so we do not want to end up in infinite loop
-- which keeps trying to update it.
l_num_snapshots := 0;
END;
END LOOP;
-- commit all successful updates
COMMIT;
END; -- removal of a chunk of orphans
EXIT WHEN l_num_snapshots < MGMT_GLOBAL.MAX_COMMIT;
END LOOP;
-- insert new potential suspects: e.g. lock snapshots for which there
-- already exists a current snapshot, and other snapshot that are neither
-- current nor saved.
BEGIN
INSERT INTO mgmt_ecm_host_configs_to_del
(SELECT s.SNAPSHOT_GUID, SYSDATE FROM mgmt_ecm_snapshot s
WHERE (s.is_current <> ECM_CT.G_IS_CURRENT_TYPE
AND s.is_current <> ECM_CT.G_IS_NOTCURRENT_TYPE
AND s.is_current <> ECM_CT.G_IS_LOCKED_TYPE)
OR
(s.is_current = ECM_CT.G_IS_LOCKED_TYPE AND
EXISTS (SELECT * FROM mgmt_ecm_snapshot s2
WHERE s.target_name = s2.target_name AND
s.target_type = s2.target_type AND
s.snapshot_type = s2.snapshot_type AND
s.snapshot_type_version = s2.snapshot_type_version AND
s2.is_current = ECM_CT.G_IS_CURRENT_TYPE)
)
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN NULL; -- ignore exceptions
END;
END HANDLE_ORPHAN_HOST_CONFIGS;
-- *********************************************************************
-- Called to delete all host configuration snapshots marked for deletion
PROCEDURE DELETE_HOST_CONFIG_SNAPSHOTS (pcb_params IN OUT
MGMT_PURGE_CALLBACK_PARAMS) IS
-- sql to be executed
l_sql VARCHAR2(512) := NULL;
-- current date
l_cur_date MGMT_ECM_HOST_CONFIGS_TO_DEL.TS%TYPE;
-- number of processed snapshots
l_num_snapshots NUMBER := 0;
-- number of processed rows (current and total)
l_num_rows NUMBER := 0;
l_total_num_rows NUMBER := 0;
-- number of snapshots that we will attempt to delete
-- by converting them to G_IS_BEING_DELETED_TYPE state
l_prev_result NUMBER := 0;
l_result NUMBER := 0;
l_snapshot_guids T_SNAP_GUID_TABLE;
BEGIN
-- loop over all snapshots; MAX_COMMIT snapshots at a time
LOOP
l_prev_result := l_result;
SELECT snapshot_guid BULK COLLECT INTO l_snapshot_guids
FROM mgmt_ecm_snapshot WHERE is_current = ECM_CT.G_IS_DELETED_TYPE
AND ROWNUM <= MGMT_GLOBAL.MAX_COMMIT;
IF (l_snapshot_guids IS NOT NULL AND l_snapshot_guids.COUNT > 0) THEN
FOR i IN l_snapshot_guids.FIRST..l_snapshot_guids.LAST LOOP
BEGIN
DELETE FROM mgmt_ecm_snapshot
WHERE snapshot_guid = l_snapshot_guids(i);
EXCEPTION
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_HOST_CONFIG_ERR_12,
'Failed to purge host config snapshot with ID: ' ||
l_snapshot_guids(i) ||
'. Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
-- This snapshot will get moved to orphan suspect list
UPDATE mgmt_ecm_snapshot
SET is_current = ECM_CT.G_IS_ERROR_TYPE
WHERE snapshot_guid = l_snapshot_guids(i);
END;
COMMIT;
l_result := l_result + 1;
END LOOP; -- delete snapshots in a chunk
END IF;
EXIT WHEN l_prev_result = l_result;
END LOOP; -- loop over all snapshots, a chunk at a time
-- compute orphan snapshots and mark them for deletion
HANDLE_ORPHAN_HOST_CONFIGS;
pcb_params.callback_result := 0;
pcb_params.error_code := 0;
pcb_params.error_msg := NULL;
pcb_params.rows_processed := l_result;
-- catch any exceptions for the snapshot type deletion
-- log error, and try to delete other snapshot types
EXCEPTION
WHEN OTHERS THEN
MGMT_LOG.LOG_ERROR(ECM_PURGE_LOG_MODULE_NAME,
DELETE_HOST_CONFIG_ERR_13,
'Failed to delete host config snapshots' ||
'. Problem: ' || SQLCODE || ': ' || SQLERRM,
v_log_level_in => MGMT_GLOBAL.G_ERROR);
pcb_params.callback_result := 0;
pcb_params.error_code := 0;
pcb_params.error_msg := 'Failed to delete host config snapshots. See logged errors.';
pcb_params.rows_processed := l_result;
-- Should we re-RAISE the exception here?
-- For now we do not, because we do not do this for
-- generic framework, where every exception is only
-- logged so we can proceed with other snapshot type
-- deletions.
-- RAISE;
END DELETE_HOST_CONFIG_SNAPSHOTS;
-- Dummy for 10.1.0.3.0 -> 10.2.0.1.0 upgrade
PROCEDURE PURGE_HOST_CONFIGS (pcb_params IN OUT
MGMT_PURGE_CALLBACK_PARAMS) IS
BEGIN
NULL;
END PURGE_HOST_CONFIGS;
-- *********************************************************************
-- Get the key list. Need to look at the metadata of this table and the
-- parent tables, even though the actual table has all the columns in it.
-- Private
FUNCTION GET_KEY_COLUMN_LIST (
v_table_name IN VARCHAR2,
v_metadata_id IN RAW)
RETURN VARCHAR2_TABLE IS
l_key_list VARCHAR2_TABLE := NULL;
BEGIN
BEGIN
SELECT c.name BULK COLLECT INTO l_key_list
FROM
(
SELECT name
FROM
mgmt_ecm_snapshot_md_tables t
START WITH name = v_table_name
AND metadata_id = v_metadata_id
CONNECT BY name = PRIOR t.parent_table_name
AND metadata_id = v_metadata_id
) t,
mgmt_ecm_snapshot_md_columns c
WHERE (c.table_name = t.name)
AND (c.metadata_id = v_metadata_id)
AND (c.IS_KEY = 'Y');
-- ignore if no key columns found
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN l_key_list;
END;
-- *********************************************************************
-- Get a list of all non-key columns in a table
-- Private
FUNCTION GET_NON_KEY_COLUMN_LIST (
v_table_name IN VARCHAR2,
v_metadata_id IN RAW)
RETURN VARCHAR2_TABLE IS
l_nonkey_list VARCHAR2_TABLE := NULL;
BEGIN
BEGIN
SELECT name BULK COLLECT INTO l_nonkey_list
FROM mgmt_ecm_snapshot_md_columns
WHERE (table_name = v_table_name)
AND (metadata_id = v_metadata_id)
AND (is_key = 'N');
-- ignore when no non-key columns found
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
RETURN l_nonkey_list;
END;
-- *********************************************************************
-- Private
FUNCTION TO_STRING (v_list IN VARCHAR2_TABLE)
RETURN VARCHAR2 IS
l_string VARCHAR2(32767) := '';
l_comma VARCHAR2(1) := '';
BEGIN
IF (v_list IS NOT NULL AND v_list.COUNT > 0) THEN
FOR i IN v_list.FIRST..v_list.LAST
LOOP
IF (LENGTH(v_list(i)) > 0) THEN
l_string := l_string || l_comma ||
em_check.simple_sql_name(em_check.enquote_name(v_list(i), false));
l_comma := ',';
END IF;
END LOOP;
END IF;
RETURN l_string;
END;
-- *********************************************************************
-- Private
PROCEDURE DUPLICATE_TABLE_DATA (
v_snapshot_guid IN RAW,
v_new_snapshot_guid IN RAW,
v_table_names IN VARCHAR2_TABLE,
v_metadata_id RAW) IS
l_sql VARCHAR2(4000) := NULL;
l_key_columns VARCHAR2(4000) := NULL;
l_columns VARCHAR2(4000) := NULL;
l_column_names VARCHAR2_TABLE;
BEGIN
IF (v_table_names IS NOT NULL AND v_table_names.COUNT > 0) THEN
FOR i IN v_table_names.FIRST..v_table_names.LAST
LOOP
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Duplicating data in table "' || v_table_names(i) || '"');
END IF;
BEGIN
-- Compute the table's column list, l_columns, from the metadata
-- find key column names for v_table_names(i) including those inherited
-- from ancestor tables
l_column_names := GET_KEY_COLUMN_LIST (v_table_names(i), v_metadata_id);
l_key_columns := TO_STRING(l_column_names);
-- CT_LOG('-- Key Columns: ' || l_key_columns);
-- find non-key column names for v_table_names(i)
l_column_names := GET_NON_KEY_COLUMN_LIST
(v_table_names(i), v_metadata_id);
l_columns := TO_STRING(l_column_names);
-- CT_LOG('-- Non-key Columns: ' || l_columns);
IF (LENGTH(l_key_columns) > 0 AND LENGTH(l_columns) > 0) THEN
l_columns := l_key_columns || ' , ' || l_columns;
ELSIF (LENGTH(l_key_columns) > 0) THEN
l_columns := l_key_columns;
END IF;
-- l_columns is not wrapped in em_check (or dbms_assert) since per Ravi P. it's
-- sufficient to create l_columns with wrapped columns there. Thus,
-- we do the wrapping of each column in TO_STRING function.
l_sql := 'DECLARE guid RAW(16); new_guid VARCHAR2(64); ' ||
'BEGIN guid := :1; new_guid := RAWTOHEX(:2);' ||
' INSERT INTO ' || em_check.sql_object_name(
em_check.enquote_name(v_table_names(i), false)) ||
' (' || ECM_CT.G_ECM_SNAPSHOT_ID || ', ' || l_columns || ')' ||
' (SELECT HEXTORAW(new_guid), ' || l_columns ||
' FROM ' || em_check.sql_object_name(
em_check.enquote_name(v_table_names(i), false)) ||
' WHERE (' || ECM_CT.G_ECM_SNAPSHOT_ID || ' = guid)); ' ||
'END;';
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Executing ' || l_sql || ' with ''' || v_snapshot_guid || '''');
END IF;
EXECUTE IMMEDIATE l_sql USING v_snapshot_guid, v_new_snapshot_guid;
COMMIT;
-- ignore if table data is empty
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END LOOP;
END IF;
END DUPLICATE_TABLE_DATA;
-- *********************************************************************
-- Called to copy a generic snapshot
--
-- Inputs: snapshot_guid - guid of the existing snapshot
-- description - description of saved snapshot
-- Output: savedConfigGuid - guid of the newly saved configuration
-- Private
PROCEDURE COPY_SNAPSHOT (
v_snapshot_guid IN RAW,
v_description IN VARCHAR2,
v_savedConfigGuid OUT VARCHAR2) IS
-- obtained from mgmt_ecm_gen_snapshot
l_snapshot_type MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE := NULL;
l_start_timestamp MGMT_ECM_GEN_SNAPSHOT.START_TIMESTAMP%TYPE := SYSDATE;
l_elapsed_time MGMT_ECM_GEN_SNAPSHOT.ELAPSED_TIME%TYPE := -1;
l_target_guid MGMT_ECM_GEN_SNAPSHOT.TARGET_GUID%TYPE;
l_target_name MGMT_ECM_GEN_SNAPSHOT.TARGET_NAME%TYPE := NULL;
l_target_type MGMT_ECM_GEN_SNAPSHOT.TARGET_TYPE%TYPE := NULL;
l_display_target_name MGMT_ECM_GEN_SNAPSHOT.DISPLAY_TARGET_NAME%TYPE := NULL;
l_display_target_type MGMT_ECM_GEN_SNAPSHOT.DISPLAY_TARGET_TYPE%TYPE := NULL;
l_status MGMT_ECM_GEN_SNAPSHOT.STATUS%TYPE := NULL;
l_message MGMT_ECM_GEN_SNAPSHOT.MESSAGE%TYPE := NULL;
l_creator MGMT_ECM_GEN_SNAPSHOT.CREATOR%TYPE := MGMT_USER.GET_CURRENT_EM_USER();
l_is_current MGMT_ECM_GEN_SNAPSHOT.IS_CURRENT%TYPE := NULL;
-- obtained from mgmt_ecm_snapshot_metadata
l_metadata_id_l MGMT_ECM_SNAPSHOT_METADATA.METADATA_ID%TYPE;
-- obtained from mgmt_ecm_snapshot_md_tables
l_table_names VARCHAR2_TABLE;
-- obtained from INSERT_GEN_SNAPSHOT_INFO
l_snapshot_guid VARCHAR2(64) := NULL;
l_new_snapshot_guid MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE;
-- local variables
l_is_current_new MGMT_ECM_GEN_SNAPSHOT.IS_CURRENT%TYPE := NULL;
BEGIN
BEGIN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Copying snapshot_guid ''' || v_snapshot_guid || '''');
END IF;
BEGIN
SELECT snapshot_type, start_timestamp, elapsed_time,
target_guid, target_name, target_type,
display_target_name, display_target_type,
is_current, message, status
INTO l_snapshot_type, l_start_timestamp, l_elapsed_time,
l_target_guid, l_target_name, l_target_type,
l_display_target_name, l_display_target_type,
l_is_current, l_message, l_status
FROM mgmt_ecm_gen_snapshot
WHERE (snapshot_guid = v_snapshot_guid);
-- bail out if no snapshot found
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (MGMT_GLOBAL.SNAPSHOT_MISSING_ERR,
'Could not find snapshot to copy');
END;
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Creating new ''' || l_snapshot_type || ''' snapshot for target ''' || l_target_name || '''');
END IF;
INSERT_GEN_SNAPSHOT_INFO (
l_snapshot_type, RAWTOHEX(l_target_guid),
l_target_name, l_target_type,
l_display_target_name, l_display_target_type,
v_description, l_start_timestamp, l_elapsed_time,
ECM_CT.G_IS_SAVING_TYPE, l_status,
l_message, l_creator, l_snapshot_guid);
COMMIT;
l_new_snapshot_guid := HEXTORAW(l_snapshot_guid);
-- check if target still exists and null out target_guid if not
BEGIN
SELECT target_name INTO l_target_name
FROM mgmt_targets
WHERE (target_guid = l_target_guid);
-- null out target_guid if target no longer exists
EXCEPTION
WHEN NO_DATA_FOUND THEN
UPDATE mgmt_ecm_gen_snapshot
SET target_guid = NULL
WHERE (snapshot_guid = l_new_snapshot_guid);
END;
-- Duplicate loader tables
BEGIN
SELECT metadata_id INTO l_metadata_id_l
FROM mgmt_ecm_snapshot_metadata
WHERE snapshot_type = l_snapshot_type
AND target_type = l_target_type
AND kind = ECM_CT.G_IS_LOADER;
SELECT name BULK COLLECT INTO l_table_names
FROM mgmt_ecm_snapshot_md_tables
WHERE metadata_id = l_metadata_id_l
ORDER BY tbl_order DESC;
DUPLICATE_TABLE_DATA (v_snapshot_guid,
l_new_snapshot_guid,
l_table_names,
l_metadata_id_l);
-- don't worry if no loader tables / views are found
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
-- check if the old snapshot is still valid
BEGIN
SELECT is_current INTO l_is_current_new
FROM mgmt_ecm_gen_snapshot
WHERE (snapshot_guid = v_snapshot_guid)
AND (is_current = l_is_current);
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (MGMT_GLOBAL.SNAPSHOT_MISSING_ERR,
'Snapshot dissapeared during the copy operation');
END;
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_NOTCURRENT_TYPE
WHERE (snapshot_guid = l_new_snapshot_guid);
COMMIT;
v_savedConfigGuid := RAWTOHEX(l_new_snapshot_guid);
EXCEPTION
WHEN OTHERS THEN
-- delete snapshot
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_guid = l_new_snapshot_guid;
COMMIT;
RAISE;
END;
END COPY_SNAPSHOT;
-- *********************************************************************
-- Called to copy a generic snapshot
--
-- Inputs: target name, target type, and snapshot type of the current snapshot
-- to be coppied. v_description is the description of the new saved snapshot.
-- v_snapGuidIn is the snapshot GUID of the configuration to be saved,
-- not-null in case of saving of saved configurations.
-- returns guid of the newly saved configuration
FUNCTION COPY_SNAPSHOT_GEN (
v_target_name IN VARCHAR2,
v_target_type IN VARCHAR2,
v_snapshot_type IN VARCHAR2,
v_description IN VARCHAR2,
v_snapGuidIn IN VARCHAR2 default null)
RETURN VARCHAR2
IS
l_current_snapshot_guid MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE;
l_success BOOLEAN := FALSE;
l_saved_snapshot_guid VARCHAR2(32) :=null;
BEGIN
FOR i IN 1..NUM_OF_SNAPSHOT_COPY_RETRIES
LOOP
BEGIN
IF (v_snapGuidIn IS NULL) THEN
SELECT snapshot_guid INTO l_current_snapshot_guid
FROM mgmt_ecm_gen_snapshot
WHERE (snapshot_type = v_snapshot_type)
AND (target_name = v_target_name)
AND (target_type = v_target_type)
AND (is_current = ECM_CT.G_IS_CURRENT_TYPE);
END IF;
-- bail out when snapshot doesn't exist
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (MGMT_GLOBAL.SNAPSHOT_MISSING_ERR,
'Could not find snapshot to copy');
END;
BEGIN
IF(v_snapGuidIn IS NOT NULL)
THEN
l_current_snapshot_guid := HEXTORAW(v_snapGuidIn);
END IF;
COPY_SNAPSHOT (l_current_snapshot_guid, v_description,l_saved_snapshot_guid);
l_success := TRUE;
EXIT;
-- try again if snapshot changed during the copy
EXCEPTION
WHEN MGMT_GLOBAL.SNAPSHOT_MISSING THEN NULL;
END;
END LOOP;
IF (NOT l_success) THEN
-- snapshot keeps changing for all retries..
raise_application_error (MGMT_GLOBAL.SNAPSHOT_MISSING_ERR,
'Snapshot keeps getting updated during the snapshot save operation.');
END IF;
-- l_saved_snapshot_guid := 'F4276509CA25E495E030578C9203448E';
return l_saved_snapshot_guid;
END;
-------------------------------------------------------------------------------
-- SAVE_COMPOSITE_CONFIG
-- This is to save all the member snapshots for a given composite target type
-- Inputs: target name, target type of the current target, whose configs are to
-- be copied recursively for all members. v_description is the description of the new saved snapshots,
-- which will be used same for all the members
PROCEDURE SAVE_COMPOSITE_CONFIG(v_target_name IN VARCHAR2,
v_target_type IN VARCHAR2,
v_description IN VARCHAR2,
p_save_details out cursorType)
IS
l_save_result save_composite_tab:=save_composite_tab();
l_temp_rec save_composite_rec;
l_collection_time DATE;
l_comp_target_guid RAW(16) := mgmt_target.get_target_guid(v_target_name, v_target_type);
-- saveRec l_member_details%ROWTYPE;
l_is_composite NUMBER:=0;
l_value MGMT_TYPE_PROPERTIES.property_value%TYPE;
l_saved_config_guid varchar2(32);
BEGIN
FOR saveRec in (
SELECT t.target_name target_name,t.target_type target_type,
t.display_name display_target_name,
t.type_display_name display_target_type,m.snapshot_type
FROM mgmt_flat_target_assoc members, mgmt_targets t,
mgmt_ecm_snapshot_metadata m
WHERE members.is_membership = 1
AND members.source_target_guid = l_comp_target_guid
AND members.assoc_target_guid = t.target_guid
AND m.target_type = t.target_type
AND m.kind='P' AND m.compare_on='Y' and m.compare_ui_on='Y'
UNION
SELECT t.target_name target_name,t.target_type target_type,
t.display_name display_target_name,
t.type_display_name display_target_type,m.snapshot_type
FROM mgmt_targets t, mgmt_ecm_snapshot_metadata m
where t.target_name=v_target_name
AND t.target_type=v_target_type
AND m.target_type=v_target_type
AND m.kind='P' AND m.compare_on='Y' AND m.compare_ui_on='Y'
)
LOOP
l_save_result.extend;
IF (saveRec.target_type='host')
THEN
l_save_result(l_save_result.last) := save_composite_rec(
saveRec.target_name,
saveRec.target_type,
saveRec.display_target_name,
saveRec.display_target_type,
saveRec.snapshot_type,
l_collection_time,NULL,SAVE_COMPOSITE_HOST
);
ELSE
BEGIN
-- currently this is going to work only for generic configs
-- not going to work for host configurations
l_saved_config_guid:=COPY_SNAPSHOT_GEN(saveRec.target_name,saveRec.target_type,
saveRec.snapshot_type,v_description);
--get the collection time to display in the save multiple config confirmation page.
SELECT START_TIMESTAMP INTO l_collection_time
FROM MGMT_ECM_GEN_SNAPSHOT
WHERE target_name=saveRec.target_name AND
target_type=saveRec.target_type AND
snapshot_type=saveRec.snapshot_type AND
is_current='Y';
l_save_result(l_save_result.last) := save_composite_rec(
saveRec.target_name,
saveRec.target_type,
saveRec.display_target_name,
saveRec.display_target_type,
saveRec.snapshot_type,
l_collection_time,NULL,SAVE_COMPOSITE_SUC
);
EXCEPTION
when others then
l_save_result(l_save_result.last) := save_composite_rec(
saveRec.target_name,
saveRec.target_type,
saveRec.display_target_name,
saveRec.display_target_type,
saveRec.snapshot_type,
NULL,SQLERRM,SAVE_COMPOSITE_FAIL
);
END;
END IF;
END LOOP;
OPEN p_save_details FOR
SELECT target_name,
target_type,
display_target_name,
display_target_type,
snapshot_type,
collection_time,error_message,save_success
FROM TABLE(CAST(l_save_result AS save_composite_tab))
ORDER BY target_name;
END SAVE_COMPOSITE_CONFIG;
-- *********************************************************************
-- The ecm target deletion callback
PROCEDURE HANDLE_TARGET_DELETION (
p_target_name VARCHAR2,
p_target_type VARCHAR2,
p_target_guid RAW) IS
BEGIN
-- mark the current snapshots for deletion
UPDATE mgmt_ecm_gen_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE target_guid = p_target_guid
AND is_current = ECM_CT.G_IS_CURRENT_TYPE;
-- set target_guid to null in all snapshots for the deleted target
UPDATE mgmt_ecm_gen_snapshot
SET target_guid = NULL
WHERE target_guid = p_target_guid;
COMMIT;
END HANDLE_TARGET_DELETION;
-- *********************************************************************
-- Called by host config loading code to convert temporary snapshot to
-- current one and compute history
--
-- Inputs: v_snapshot_guid - guid of the temporary snapshot to be converted
PROCEDURE HOST_CONFIG_POSTLOAD (v_snapshot_guid RAW) IS
-- information about the input snapshot, which is about to become current
l_target_name MGMT_ECM_SNAPSHOT.TARGET_NAME%TYPE;
l_target_type MGMT_ECM_SNAPSHOT.TARGET_TYPE%TYPE;
l_snapshot_type MGMT_ECM_SNAPSHOT.SNAPSHOT_TYPE%TYPE;
l_snapshot_type_version MGMT_ECM_SNAPSHOT.SNAPSHOT_TYPE_VERSION%TYPE;
-- used to determine lock conditions
l_lock_snapshot_list T_SNAP_GUID_TABLE;
l_lock_guid MGMT_ECM_SNAPSHOT.SNAPSHOT_GUID%TYPE := NULL;
l_current_snapshot_guid MGMT_ECM_SNAPSHOT.SNAPSHOT_GUID%TYPE := NULL;
BEGIN
BEGIN
SELECT target_name, target_type, snapshot_type, snapshot_type_version
INTO l_target_name, l_target_type, l_snapshot_type,
l_snapshot_type_version
FROM mgmt_ecm_snapshot
WHERE snapshot_guid = v_snapshot_guid;
EXCEPTION
-- snapshot no longer exists for some reason, so nothing to do
WHEN NO_DATA_FOUND THEN
RETURN;
END;
--insert an entry for this snapshot's components and containers into
--mgmt_inv_summary
INSERT INTO
mgmt_inv_summary
(
snapshot_guid,
comp_external_name,
comp_version,
container_location,
container_guid,
is_patched,
map_target_type,
map_property_name,
map_property_value)
SELECT
v_snapshot_guid,
comp.external_name,
case when ps_patch.version is null then comp.version else ps_patch.version end as version,
cont.container_location,
cont.container_guid,
case
when
exists
(
SELECT *
FROM mgmt_inv_patch p
WHERE p.container_guid = cont.container_guid
)
then 1 else 0
end as is_patched,
map.target_type,
map.property_name,
map.property_value
FROM
mgmt_inv_container cont,
mgmt_inv_component comp,
mgmt_inv_versioned_patch ps_patch,
mgmt_target_type_component_map map
WHERE
cont.snapshot_guid = v_snapshot_guid
and comp.container_guid = cont.container_guid
and comp.component_guid = ps_patch.component_guid(+)
and map.component_name = comp.name;
COMMIT;
-- update the patches column in mgmt_hc_os_summary based on whether or not the os is patched
UPDATE mgmt_hc_os_summary
SET patches =
(SELECT count(*)
FROM mgmt_hc_os_components c
WHERE c.snapshot_guid = v_snapshot_guid
and c.type='Patch')
WHERE
snapshot_guid = v_snapshot_guid;
COMMIT;
BEGIN
SELECT snapshot_guid INTO l_current_snapshot_guid
FROM mgmt_ecm_snapshot
WHERE snapshot_type = l_snapshot_type
AND target_name = l_target_name
AND target_type = l_target_type
AND snapshot_type_version = l_snapshot_type_version
AND is_current = ECM_CT.G_IS_CURRENT_TYPE
FOR UPDATE OF is_current;
EXCEPTION
-- ignore when no current snapshot
WHEN NO_DATA_FOUND THEN NULL;
END;
-- Lock on new row if no lock obtained on current snapshot
IF (l_current_snapshot_guid IS NULL) THEN
INSERT_SNAPSHOT_INFO (
l_snapshot_type, l_snapshot_type_version,
l_target_name, l_target_type,
l_target_name, l_target_type,
NULL, SYSDATE, -1,
ECM_CT.G_IS_LOCKED_TYPE, NULL,
l_lock_guid);
COMMIT;
BEGIN
SELECT snapshot_guid BULK COLLECT INTO l_lock_snapshot_list
FROM mgmt_ecm_snapshot
WHERE snapshot_type = l_snapshot_type
AND target_name = l_target_name
AND target_type = l_target_type
AND snapshot_type_version = l_snapshot_type_version
AND is_current = ECM_CT.G_IS_LOCKED_TYPE
FOR UPDATE OF is_current;
-- bail out with error when lock is missing
EXCEPTION
WHEN NO_DATA_FOUND THEN
raise_application_error (MGMT_GLOBAL.SNAPSHOT_LOCK_ERR,
'Could not find just created host configuration lock row');
END;
END IF;
-- make current snapshot, if any, into DELTA snapshot type
-- If we had to create L snapshot above and meanwhile a
-- current snapshot appeared, we would get the lock here.
UPDATE mgmt_ecm_snapshot SET snapshot_type = 'DELTA'
WHERE snapshot_type = l_snapshot_type
AND target_name = l_target_name
AND target_type = l_target_type
AND snapshot_type_version = l_snapshot_type_version
AND is_current = ECM_CT.G_IS_CURRENT_TYPE;
-- make input (temporary) snapshot into current
UPDATE mgmt_ecm_snapshot SET is_current = ECM_CT.G_IS_CURRENT_TYPE
WHERE snapshot_guid = v_snapshot_guid;
-- compute history
MGMT_HC_DELTA.RECORD_HC_DIFFS(
l_target_name, l_target_type, l_snapshot_type,
l_target_name, l_target_type, 'DELTA', 'DELTA');
-- prepare DELTA snapshot (i.e. old current snapshot) for deletion
UPDATE mgmt_ecm_snapshot
SET snapshot_type = l_snapshot_type,
is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE snapshot_type = 'DELTA'
AND target_name = l_target_name
AND target_type = l_target_type
AND snapshot_type_version = l_snapshot_type_version
AND is_current = ECM_CT.G_IS_CURRENT_TYPE;
COMMIT;
-- Dues to bug 4254731, this delete is removed. It was identified as deadlocking
-- probably due to BLOCK locks. Removing the code is ok becuase the entry
-- we be garbage collected when the HOST CONFIG ORPHANS are cleaned up.
-- It is a single entry with no children, so it should not be a space usage
-- problem.
-- -- If we created a lock snapshot, remove it now
-- IF (l_lock_guid IS NOT NULL) THEN
--
-- BEGIN
-- DELETE FROM mgmt_ecm_snapshot
-- WHERE snapshot_guid = l_lock_guid;
-- COMMIT;
--
-- -- bail out when nothing to do
-- EXCEPTION
-- WHEN NO_DATA_FOUND THEN NULL;
-- END;
--
-- END IF;
END HOST_CONFIG_POSTLOAD;
-- *********************************************************************
PROCEDURE HOST_CONFIG_TARGET_DELETION (
p_target_name VARCHAR2,
p_target_type VARCHAR2,
p_target_guid RAW) IS
BEGIN
-- delete host config snapshot
UPDATE mgmt_ecm_snapshot
SET is_current = ECM_CT.G_IS_DELETED_TYPE
WHERE target_name = p_target_name
AND target_type = p_target_type
AND is_current = 'Y';
COMMIT;
UPDATE mgmt_ecm_savedhostconfig
SET target_guid = NULL
WHERE target_guid = p_target_guid;
COMMIT;
END HOST_CONFIG_TARGET_DELETION;
-- *********************************************************************
-- Test post load procedure. Call the procedure assuming there is a (single)
-- temporary snapshot for a given target name / type and snapshot type
PROCEDURE TEST_POST_LOAD (
p_target_name VARCHAR2,
p_target_type VARCHAR2,
p_snapshot_type VARCHAR2) IS
v_in1 SMP_EMD_NVPAIR_ARRAY;
v_in2 SMP_EMD_NVPAIR_ARRAY;
v_target_guid MGMT_ECM_GEN_SNAPSHOT.TARGET_GUID%TYPE;
v_snapshot_guid MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE;
BEGIN
SELECT snapshot_guid, target_guid INTO v_snapshot_guid, v_target_guid
FROM MGMT_ECM_GEN_SNAPSHOT
WHERE target_name = p_target_name
AND target_type = p_target_type
AND snapshot_type = p_snapshot_type
AND is_current = ECM_CT.G_IS_TRANSIENT_TYPE;
v_in1 := SMP_EMD_NVPAIR_ARRAY();
v_in1.extend(4);
v_in1(1) := SMP_EMD_NVPAIR(ECM_CT.G_COLLECTION_NAME, p_snapshot_type);
v_in1(2) := SMP_EMD_NVPAIR(ECM_CT.G_TARGET_GUID, v_target_guid);
v_in1(3) := SMP_EMD_NVPAIR(ECM_CT.G_COLLECTION_TIMESTAMP, SYSDATE());
v_in1(4) := SMP_EMD_NVPAIR(ECM_CT.G_CONFIG_METADATA_VERSION, '1.0');
v_in2 := SMP_EMD_NVPAIR_ARRAY();
v_in2.extend(1);
v_in2(1) := SMP_EMD_NVPAIR(ECM_CT.G_ECM_SNAPSHOT_ID, v_snapshot_guid);
POSTLOAD_CALLBACK(v_in1, v_in2);
END TEST_POST_LOAD;
-- *********************************************************************
-- Test
PROCEDURE AFTER_LOAD_PROC (
v_snapshot_guid IN RAW,
v_afterLoadInput IN SMP_EMD_NVPAIR_ARRAY) IS
BEGIN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Begin AFTER_LOAD_PROC');
CT_LOG('--> snapshot_guid = ''' || v_snapshot_guid || '''');
CT_LOG('-- End AFTER_LOAD_PROC');
END IF;
END AFTER_LOAD_PROC;
-- *********************************************************************
-- Test
PROCEDURE AFTER_IMPORT_PROC (
v_snapshot_guid IN RAW,
v_afterImportInput IN SMP_EMD_NVPAIR_ARRAY) IS
BEGIN
IF (ECM_CT.G_IS_DEBUG = 1) THEN
CT_LOG('-- Begin AFTER_IMPORT_PROC');
CT_LOG('--> snapshot_guid = ''' || v_snapshot_guid || '''');
CT_LOG('-- End AFTER_IMPORT_PROC');
END IF;
END AFTER_IMPORT_PROC;
-- *********************************************************************
PROCEDURE GET_HOST_CONFIG_INFO (
v_sguid IN VARCHAR2,
v_target_name IN VARCHAR2,
v_target_type IN VARCHAR2,
p_isStale OUT VARCHAR2,
p_systemConfig OUT VARCHAR2,
p_hardwareProvider OUT VARCHAR2,
p_numberOfCpus OUT INTEGER,
p_memory OUT INTEGER,
osName OUT VARCHAR2,
l_home_list OUT MGMT_ORACLESW_LIST,
l_vendor_list OUT MGMT_VENDORSW_LIST,
p_comps OUT sys_refcursor)
IS
p_snaprow NUMBER := 0;
BEGIN
p_isStale := 'FALSE';
BEGIN
SELECT 1 INTO p_snaprow
FROM MGMT_ECM_SNAPSHOT
WHERE snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_isStale := 'TRUE';
END;
open p_comps for
SELECT component_name, collection_status, collection_message FROM MGMT_ECM_SNAP_COMPONENT_INFO WHERE snapshot_guid = HEXTORAW(v_sguid) ;
BEGIN
SELECT s.system_config, s.vendor_name, s.cpu_count, s.memory_size_in_mb
into p_systemConfig, p_hardwareProvider,p_numberOfCpus,p_memory
FROM MGMT_HC_HARDWARE_MASTER s
WHERE s.snapshot_guid = HEXTORAW(v_sguid) ;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
SELECT
os.name || ' '|| os.base_version ||
decode(os.update_level, null,'',' '|| os.update_level) ||
decode ( os.address_length_in_bits, null, '', ' (' ||
os.address_length_in_bits || ')')
into osName
FROM MGMT_HC_OS_SUMMARY os
WHERE snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
SELECT MGMT_ORACLESW_RECORD(comp.name,
comp.external_name,
decode(patch.version, NULL, comp.version, patch.version),
comp.version,
cont.container_location,
cont.container_name,
comp.TIMESTAMP,
decode(advisory.home_location, NULL, 0, 1))
BULK COLLECT INTO l_home_list
FROM
MGMT_INV_COMPONENT comp,
mgmt_inv_container cont,
MGMT_INV_VERSIONED_PATCH patch,
(SELECT /*+ index(hrv) */ unique hrv.column_str_value as home_location
FROM
mgmt_policies p,
mgmt_current_violation v,
mgmt_violation_context hrv,
mgmt_targets t
WHERE t.target_name = v_target_name
AND t.target_type = v_target_type
AND p.author = 'ORACLE'
AND p.target_type = 'host'
AND p.policy_name = 'Critical Patch Advisories for Oracle Homes'
AND p.policy_guid = v.policy_guid
AND (v.exempt_code = 0 or
(v.exempt_code = 2 and v.exempt_until <= SYSDATE))
AND v.target_guid = hrv.target_guid
AND v.policy_guid = hrv.policy_guid
AND v.key_value = hrv.key_value
AND hrv.column_name = 'HOME_LOCATION' ) advisory
WHERE comp.is_top_level = 'Y'
and comp.component_guid = patch.component_guid(+)
and comp.container_guid = cont.container_guid
and cont.snapshot_guid = HEXTORAW(v_sguid)
and cont.container_location = advisory.home_location(+)
GROUP BY comp.name, comp.external_name,
decode(patch.version, NULL, comp.version, patch.version),
comp.version, cont.container_location, cont.container_name,
comp.TIMESTAMP, advisory.home_location;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
SELECT MGMT_VENDORSW_RECORD(s.name, s.vendor_name, s.version,
s.installed_location, count(*), s.installation_date)
BULK COLLECT INTO l_vendor_list
FROM MGMT_HC_VENDOR_SW_SUMMARY s
WHERE s.snapshot_guid = HEXTORAW(v_sguid)
GROUP BY s.name, s.vendor_name, s.version, s.installed_location,s.installation_date;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END GET_HOST_CONFIG_INFO;
PROCEDURE GET_HW_INFO (
v_sguid IN VARCHAR2,
p_isStale OUT VARCHAR2,
l_hwgenlist OUT MGMT_HWGEN_LIST,
l_cpulist OUT MGMT_CPUDETAILS_LIST,
l_iocardlist OUT MGMT_IOCARDDETAILS_LIST,
l_niclist OUT MGMT_NICDETAILS_LIST
)
IS
p_snaprow NUMBER := 0;
BEGIN
p_isStale := 'FALSE';
BEGIN
SELECT 1 INTO p_snaprow
FROM MGMT_ECM_SNAPSHOT
WHERE snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN
p_isStale := 'TRUE';
END;
BEGIN
select MGMT_HWGEN_RECORD
(system_config, machine_architecture, vendor_name, memory_size_in_mb,
local_disk_space_in_gb, clock_freq_in_mhz, cpu_count, cpu_board_count,
iocard_count, fan_count, power_supply_count)
BULK COLLECT INTO l_hwgenlist
from MGMT_HC_HARDWARE_MASTER where snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
select MGMT_CPUDETAILS_RECORD
(freq_in_mhz, vendor_name, revision, ecache_in_mb, impl, mask )
BULK COLLECT INTO l_cpulist
from MGMT_HC_CPU_DETAILS where snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
select MGMT_IOCARDDETAILS_RECORD
(name, vendor_name, bus, freq_in_mhz, revision )
BULK COLLECT INTO l_iocardlist
from MGMT_HC_IOCARD_DETAILS where snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
BEGIN
select MGMT_NICDETAILS_RECORD
( name, inet_address, max_transfer_unit, broadcast_address, mask,
flags, mac_address, hostname_aliases )
BULK COLLECT INTO l_niclist
from MGMT_HC_NIC_DETAILS where snapshot_guid = HEXTORAW(v_sguid);
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END;
END GET_HW_INFO;
END ECM_CT;
/
show errors;