Rem Rem $Header: ecm_csa_pkgbody.sql 12-jul-2006.07:02:35 asaraswa Exp $ Rem Rem ecm_csa_pkgbody.sql Rem Rem Copyright (c) 2003, 2006, Oracle. All rights reserved. Rem Rem NAME Rem ecm_csa_pkgbody.sql - Rem Rem ESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem asaraswa 07/07/06 - Backport asaraswa_bug-5240726 from main Rem asaraswa 06/19/06 - fixing bug 5240726 Rem mgoodric 07/20/05 - add additional parameter to after_load proc Rem asaraswa 04/19/05 - handling duplicate values in create_target_association Rem asaraswa 02/16/05 - removing calculation of appid and overall Rem compliance in after load proc Rem vkhizder 02/10/05 - changes to purge Rem vkhizder 12/22/04 - logging corrections Rem asaraswa 12/01/04 - fixing rule status bug in csa after-load Rem asaraswa 11/10/04 - changing remove_failed procedure to use single Rem delete statement Rem asaraswa 11/09/04 - adding procedure to remove failed collections Rem asaraswa 09/21/04 - adding procedure to associate targets with Rem application ids Rem vkhizder 07/13/04 - appid and other misc additions Rem vkhizder 02/17/04 - making csa clients non-targets Rem vkhizder 10/17/03 - fixing start_timestamp and elapsed_time in the Rem snapshot table Rem mgoodric 09/23/03 - Change client target_type Rem vkhizder 08/07/03 - vkhizder_trans_jul21_03 Rem vkhizder 07/25/03 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_ECM_CSA_PKG IS -- a list of snapshot IDs TYPE SNAPSHOT_ID_TABLE IS TABLE OF MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE; -- Private constants -- target type for CSA client targets, NOT CSA collector / proxy targets -- This is not really a target type as no corresponding target will show up in -- the targets table. CSA_TARGET_TYPE_C CONSTANT MGMT_TARGETS.TARGET_TYPE%TYPE := 'oracle_csa_client'; -- display target type for CSA client targets, NOT CSA collector targets -- This will not actually show up in the targets table (see above comment). CSA_DISPLAY_TARGET_TYPE_C CONSTANT MGMT_TARGETS.TYPE_DISPLAY_NAME%TYPE := 'CSA Client'; -- Number of the latest CSA failures to retain (gets using in the purging policy) CSA_LATEST_FAILURES_TO_KEEP CONSTANT NUMBER := 10000; -------------------------------------------------------------------------- -- This is an after-load procedure for the CSA snapshots that will -- convert the snapshot to a non-target CSA snapshot for a new -- (if necessary) client. -- -- Input: p_snapshot_id ID of the loaded snapshot -- Input: p_afterLoadInput Additional after-load input parameters PROCEDURE AFTER_LOAD (p_snapshot_id IN MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_GUID%TYPE, p_afterLoadInput IN SMP_EMD_NVPAIR_ARRAY) IS -- Target identifying information /* l_os_user_name ECM_CSA_GENERAL_INFO.OS_USER_NAME%TYPE := NULL; l_boot_disk_serial_number ECM_CSA_GENERAL_INFO.BOOT_DISK_VOLUME_SERIAL_NUM%TYPE := NULL; l_hostname ECM_CSA_GENERAL_INFO.HOSTNAME%TYPE := NULL; l_domain ECM_CSA_GENERAL_INFO.DOMAIN%TYPE := NULL; l_appid NUMBER := NULL; l_target_key1 ECM_CSA_GENERAL_INFO.TARGET_KEY1%TYPE := NULL; l_target_key2 ECM_CSA_GENERAL_INFO.TARGET_KEY2%TYPE := NULL; l_target_key3 ECM_CSA_GENERAL_INFO.TARGET_KEY3%TYPE := NULL; */ -- new CSA client target information l_proxy_target_guid MGMT_TARGETS.TARGET_GUID%TYPE := NULL; l_proxy_target_name MGMT_TARGETS.TARGET_NAME%TYPE := NULL; l_proxy_target_display_name MGMT_TARGETS.DISPLAY_NAME%TYPE := NULL; l_target_name MGMT_TARGETS.TARGET_NAME%TYPE := NULL; l_display_target_name MGMT_TARGETS.DISPLAY_NAME%TYPE := NULL; -- l_agent_url MGMT_TARGETS.EMD_URL%TYPE := NULL; -- l_agent_hostname MGMT_TARGETS.HOST_NAME%TYPE := NULL; l_snapshot_type MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE := NULL; l_warning_message MGMT_ECM_GEN_SNAPSHOT.MESSAGE%TYPE := NULL; l_start_timestamp MGMT_ECM_GEN_SNAPSHOT.START_TIMESTAMP%TYPE := NULL; l_elapsed_time MGMT_ECM_GEN_SNAPSHOT.ELAPSED_TIME%TYPE := NULL; -- list of snapshots that may already have needed target information l_snapshot_ids SNAPSHOT_ID_TABLE := NULL; -- flag that indicates whether a target already exists given the -- identifying columns l_target_already_exists BOOLEAN := FALSE; l_matching_tzs_count NUMBER := NULL; l_db_timezone_region MGMT_ECM_OS.TIMEZONE_REGION%TYPE := NULL; BEGIN -- Get new CSA client snapshot info SELECT SNAPSHOT_TYPE, MESSAGE, START_TIMESTAMP, ELAPSED_TIME, DISPLAY_TARGET_NAME INTO l_snapshot_type, l_warning_message, l_start_timestamp, l_elapsed_time, l_display_target_name FROM MGMT_ECM_CSA_SNAPSHOT_INFO WHERE ECM_SNAPSHOT_ID = p_snapshot_id; -- Length of the display target name field in the -- MGMT_ECM_CSA_SNAPSHOT_INFO table is the same as in the targets table. -- So, we don't have to worry about the latter exceeding and not fitting -- into the former. -- Get proxy target info SELECT TARGET_NAME, DISPLAY_TARGET_NAME, TARGET_GUID INTO l_proxy_target_name, l_proxy_target_display_name, l_proxy_target_guid FROM MGMT_ECM_GEN_SNAPSHOT WHERE SNAPSHOT_GUID = p_snapshot_id; -- Do not fail whole collection if start timestamp did not get collected -- for some reason. IF l_start_timestamp IS NULL THEN l_start_timestamp := TO_DATE('2000-01-01', 'YYYY-MM-DD'); END IF; -- Lock on all rows in snapshot table that have the same target identifying -- columns. At least one row will be locked: the one with snapshot id -- p_snapshot_id. -- This could be later extended with an additional join on the -- MGMT_ECM_GEN_SNAPSHOT table and limiting selected snapshot ids only to -- Y or T snapshots.. For now we do not expect any other snapshots anyway, -- and thus a lock on such snapshots is not as important. Thus, we'll -- leave the join for the next query where it has to happen anyway. SELECT info2.ECM_SNAPSHOT_ID BULK COLLECT INTO l_snapshot_ids FROM MGMT_ECM_GEN_SNAPSHOT s1, MGMT_ECM_CSA_GENERAL_INFO info1, MGMT_ECM_CSA_GENERAL_INFO info2 WHERE s1.SNAPSHOT_GUID = p_snapshot_id AND s1.SNAPSHOT_GUID = info1.ECM_SNAPSHOT_ID AND info1.TARGET_ID_METHOD = info2.TARGET_ID_METHOD AND info1.OS_USER_NAME = info2.OS_USER_NAME AND info1.BOOT_DISK_VOLUME_SERIAL_NUM = info2.BOOT_DISK_VOLUME_SERIAL_NUM AND info1.HOSTNAME = info2.HOSTNAME AND info1.DOMAIN = info2.DOMAIN AND info1.APPID = info2.APPID --as of 2/16/05 this column cannot be null -- AND NVL(info1.APPID, 'Default') = NVL(info2.APPID, 'Default') AND NVL(info1.TARGET_KEY1, ' ') = NVL(info2.TARGET_KEY1, ' ') AND NVL(info1.TARGET_KEY2, ' ') = NVL(info2.TARGET_KEY2, ' ') AND NVL(info1.TARGET_KEY3, ' ') = NVL(info2.TARGET_KEY3, ' ') FOR UPDATE OF info2.ECM_SNAPSHOT_ID; -- At this point we are definitely locked IF l_snapshot_ids IS NOT NULL AND l_snapshot_ids.COUNT > 0 THEN FOR i in l_snapshot_ids.FIRST..l_snapshot_ids.LAST LOOP BEGIN -- Select information for an existing client (if any). Only -- current / almost current snapshots (for all CSA snapshot types) -- are considered. Deleted / imported / saved snapshots are -- thought of as different clients potentially (i.e. different if -- there is no current / almost current snapshot) -- Note: The last condition that checks for target type is -- important to eliminate ALL 'T' snapshots that are still recorded -- as proxy target snapshots. At least one of -- such snapshots is the snapshot which is currently being loaded. SELECT TARGET_NAME INTO l_target_name FROM MGMT_ECM_GEN_SNAPSHOT WHERE SNAPSHOT_GUID = l_snapshot_ids(i) AND (IS_CURRENT = ECM_CT.G_IS_CURRENT_TYPE OR IS_CURRENT = ECM_CT.G_IS_TRANSIENT_TYPE) AND TARGET_TYPE = CSA_TARGET_TYPE_C; l_target_already_exists := TRUE; EXIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; -- proceed to next or exit if last END; END LOOP; END IF; IF NOT l_target_already_exists THEN -- Adding a new client -- Generate internal client target name l_target_name := 'csa_client_' || RAWTOHEX(SYS_GUID()); END IF; -- Update the currently loaded snapshot row with new client information UPDATE MGMT_ECM_GEN_SNAPSHOT SET TARGET_GUID = NULL, TARGET_NAME = l_target_name, DISPLAY_TARGET_NAME = l_display_target_name, TARGET_TYPE = CSA_TARGET_TYPE_C, DISPLAY_TARGET_TYPE = CSA_DISPLAY_TARGET_TYPE_C, SNAPSHOT_TYPE = l_snapshot_type, MESSAGE = l_warning_message, START_TIMESTAMP = l_start_timestamp, ELAPSED_TIME = l_elapsed_time WHERE SNAPSHOT_GUID = p_snapshot_id; -- Insert the proxy target information in the snapshot (update from NULL values) -- and set overall compliance, or worst rule status -- 2/16/05: appid can never be null and has a default value of 'Default', and worst rule -- status is computed by the applet UPDATE MGMT_ECM_CSA_GENERAL_INFO SET PROXY_TARGET_NAME = l_proxy_target_name, PROXY_TARGET_DISPLAY_NAME = l_proxy_target_display_name, PROXY_TARGET_ID = l_proxy_target_guid -- APPID = NVL(APPID, 'Default'), -- WORST_RULE_STATUS = GREATEST(MGMT_GLOBAL.G_SEVERITY_CLEAR, -- NVL((SELECT MAX(r.STATUS) -- FROM MGMT_ECM_CSA_RULES r -- WHERE r.ECM_SNAPSHOT_ID = p_snapshot_id), -- MGMT_GLOBAL.G_SEVERITY_CLEAR)) WHERE ECM_SNAPSHOT_ID = p_snapshot_id; -- Fix for bug 5240726: We need to check the incoming timezone region to -- see if it is in a format recognized by Oracle. To do this we check the -- v$timezone_names view to make sure that the timezone region in the -- snapshot is recognized. If not, we default to the repository's timezone -- region. If we can't find the repository's timezone region, we default to -- America/New York BEGIN SELECT dbtimezone INTO l_db_timezone_region FROM DUAL; EXCEPTION WHEN NO_DATA_FOUND THEN l_db_timezone_region := 'America/New_York'; END; BEGIN SELECT count(*) into l_matching_tzs_count FROM V$TIMEZONE_NAMES v, MGMT_ECM_OS os WHERE os.ECM_SNAPSHOT_ID = p_snapshot_id and v.tzname = os.timezone_region; EXCEPTION WHEN OTHERS THEN NULL; END; IF l_matching_tzs_count = 0 THEN BEGIN UPDATE MGMT_ECM_OS SET TIMEZONE_REGION = l_db_timezone_region WHERE ECM_SNAPSHOT_ID = p_snapshot_id; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; /* -- Just in case add_target call commits, we perform it AFTER updating the -- snapshot table with the new target information. IF NOT l_target_already_exists THEN mgmt_target.add_target(p_target_name => l_target_name, p_target_type => CSA_TARGET_TYPE_C, p_host_name => l_agent_hostname, p_properties => NULL, p_groups => NULL, p_target_display_name => l_display_target_name, p_agent_url => l_agent_url ); END IF; */ COMMIT; END AFTER_LOAD; -------------------------------------------------------------------------- -------------------------------------------------------------------------- -- PURGE -- -- PURPOSE -- Cleans up CSA clients / snapshots and their histories. -- The arguments are used to limit the purged snapshots. -- IMPORTANT: This call COMMITS its changes as it goes along deleting -- information. -- -- PARAMETERS -- -- p_numberOfDays Only snapshots that have not been updated within -- the last p_numberOfDays days are removed. If the number is -- the default 0, all snapshots are removed (perhaps limited -- by other parameters). SAVED_TIMESTAMP column is used for -- comparison. -- p_snapshotType If not null, only snapshots (and their histories) of a given -- type are removed. -- p_targetName The exact internal target name of the CSA client. -- p_removeHistory Should history be removed? -- -- RETURNS The number of deleted snapshots. -- FUNCTION PURGE( p_numberOfDays NUMBER := 0, p_snapshotType VARCHAR2 := NULL, p_targetName VARCHAR2 := NULL, p_removeHistory BOOLEAN := TRUE ) RETURN NUMBER IS l_snapshot_ids SNAPSHOT_ID_TABLE := NULL; TYPE SNAPSHOT_TYPE_TABLE IS TABLE OF MGMT_ECM_GEN_SNAPSHOT.SNAPSHOT_TYPE%TYPE; l_snapshot_types SNAPSHOT_TYPE_TABLE := NULL; TYPE TARGET_NAME_TABLE IS TABLE OF MGMT_ECM_GEN_SNAPSHOT.TARGET_NAME%TYPE; l_target_names TARGET_NAME_TABLE := NULL; -- function result to be returned l_result NUMBER := 0; l_garbage NUMBER; -- used to store result of a function which is not used. BEGIN -- Identify all snapshots that need to be removed. SELECT SNAPSHOT_GUID, SNAPSHOT_TYPE, TARGET_NAME BULK COLLECT INTO l_snapshot_ids, l_snapshot_types, l_target_names FROM MGMT_ECM_GEN_SNAPSHOT WHERE SAVED_TIMESTAMP < SYSDATE - p_numberOfDays AND (p_snapshotType IS NULL OR p_snapshotType = SNAPSHOT_TYPE) AND (p_targetName IS NULL OR p_targetName = TARGET_NAME) AND TARGET_TYPE = CSA_TARGET_TYPE_C AND IS_CURRENT = ECM_CT.G_IS_CURRENT_TYPE ; -- If snapshot ID changes after the query, that is OK. In effect, that -- snapshot got removed already. In other words, say the snapshot ID changed -- due to arrival of a new snapshot.. Now imagine the new snapshot arrived -- just after this function is called.. We'd still end up with the new CSA -- snapshot in repository. We may still remove the snapshot's -- history however. IF l_snapshot_ids IS NOT NULL AND l_snapshot_ids.COUNT > 0 THEN FOR i in l_snapshot_ids.FIRST..l_snapshot_ids.LAST LOOP -- Delete the snapshot BEGIN -- ECM_CT.DELETE_SNAPSHOT(l_snapshot_ids(i)); UPDATE mgmt_ecm_gen_snapshot SET is_current = ECM_CT.G_IS_DELETED_TYPE WHERE snapshot_guid = l_snapshot_ids(i); l_result := l_result + 1; EXCEPTION -- cannot throw NO_DATA_FOUND WHEN OTHERS THEN -- log exception and proceed MGMT_LOG.LOG_ERROR(CSA_LOGGING_MODULE_NAME, PURGE_CSA_SNAPSHOT_ERR, 'Encountered problems while deleting CSA snapshot of type "' || l_snapshot_types(i) || '" for client "' || l_target_names(i) || '": ' || SQLCODE || ': ' || SQLERRM, v_log_level_in => MGMT_GLOBAL.G_WARN); END; -- Delete the history IF p_removeHistory THEN BEGIN l_garbage := MGMT_DELTA.PURGE_HISTORY( p_snapshotType => l_snapshot_types(i), p_targetType => CSA_TARGET_TYPE_C, p_targetName => l_target_names(i)); EXCEPTION WHEN OTHERS THEN -- log exception and proceed MGMT_LOG.LOG_ERROR(CSA_LOGGING_MODULE_NAME, PURGE_CSA_SNAPSHOT_HISTORY_ERR, 'Encountered problems while deleting history for CSA ' || 'snapshot of type "' || l_snapshot_types(i) || '" for client "' || l_target_names(i) || '": ' || SQLCODE || ': ' || SQLERRM, v_log_level_in => MGMT_GLOBAL.G_WARN); END; END IF; COMMIT; END LOOP; END IF; RETURN l_result; END PURGE; PROCEDURE AUTO_PURGE(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS l_failures_purged NUMBER := 0; BEGIN -- Not really rows processed but rather CSA snapshots processed in this -- case (do not remove history as history purge takes care of that) pcb_params.rows_processed := PURGE( p_numberOfDays => SYSDATE - pcb_params.purge_upto_time, p_removeHistory => FALSE ); pcb_params.callback_result := 0; pcb_params.error_code := 0; pcb_params.error_msg := NULL; -- Remove all oldest CSA collection failures except for the latest -- BEGIN SELECT GREATEST(0, (COUNT(*) - CSA_LATEST_FAILURES_TO_KEEP)) INTO l_failures_purged FROM MGMT_ECM_CSA_FAILED; -- At this point another failure might have come in, but that's fine; -- the result of the deletion below will be equivalent to actually removing -- oldest failures in the line above DELETE FROM MGMT_ECM_CSA_FAILED WHERE ROWID IN (SELECT ROWID FROM (SELECT ROWID FROM MGMT_ECM_CSA_FAILED ORDER BY SAVED_TIMESTAMP ASC) WHERE ROWNUM <= l_failures_purged); pcb_params.rows_processed := pcb_params.rows_processed + l_failures_purged; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; WHEN OTHERS THEN -- log exception and proceed MGMT_LOG.LOG_ERROR(CSA_LOGGING_MODULE_NAME, PURGE_CSA_FAILURES_ERR, 'Encountered problems while purging old failed CSA collections: ' || SQLCODE || ': ' || SQLERRM, v_log_level_in => MGMT_GLOBAL.G_WARN); END; END AUTO_PURGE; PROCEDURE CREATE_TARGET_ASSOCIATION(target_guids_in IN VARCHAR2_TABLE, app_ids_in IN VARCHAR2_TABLE) IS BEGIN FOR i in 1 .. app_ids_in.count LOOP BEGIN INSERT INTO MGMT_ECM_CSA_APPID_TARGET_MAP (APPID, TARGET_GUID) VALUES(app_ids_in(i), target_guids_in(i)); EXCEPTION WHEN dup_val_on_index THEN null; --raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, SQLERRM()); WHEN OTHERS THEN raise_application_error(SQLCODE(), SQLERRM()); END; END LOOP; END CREATE_TARGET_ASSOCIATION; PROCEDURE REMOVE_ASSOC(target_guids_in IN VARCHAR2_TABLE, app_ids_in IN VARCHAR2_TABLE) IS BEGIN FOR i in 1 .. app_ids_in.count LOOP DELETE FROM mgmt_ecm_csa_appid_target_map WHERE target_guid = HEXTORAW(target_guids_in(i)) and appid = app_ids_in(i); END LOOP; END REMOVE_ASSOC; PROCEDURE REMOVE_FAILED(ids_in IN VARCHAR2_TABLE) IS BEGIN delete from mgmt_ecm_csa_failed where id in (select HEXTORAW(idlist.column_value) from TABLE(ids_in) idlist); END REMOVE_FAILED; END MGMT_ECM_CSA_PKG; / show errors;