Rem drv: Rem Rem $Header: ecm_data_upgrade.sql 16-sep-2005.01:43:56 pdasika Exp $ Rem Rem ecm_data_upgrade.sql Rem Rem Copyright (c) 2005, Oracle. All rights reserved. Rem Rem NAME Rem ecm_data_upgrade.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem pdasika 09/16/05 - Changing SETUP_CPF_ADV_HOME_PATCH to RUN_SETUP_CPF_ADV_HOME_PATCH Rem mningomb 08/26/05 - Move beta to 10.2.0.1 upgrade code out Rem asaraswa 08/24/05 - removing upgrade statements from after beta Rem branch Rem mningomb 07/27/05 - Rem chyu 07/26/05 - modifying the migrate header position Rem chyu 07/18/05 - updating the new rep manager header Rem abhalla 07/07/05 - Changes to set up data in MGMT_CPF_METRIC_SOURCE Rem asaraswa 07/05/05 - Rem mgoodric 06/23/05 - fix for 12M row upgrade Rem mningomb 06/14/05 - Entry to create cpf policy Rem niramach 04/27/05 - Remove the compression of policy indexes since Rem those are removed as part of obsolete policy Rem table drop. Rem asaraswa 04/08/05 - populating patches column of mgmt_hc_os_summary Rem mgoodric 03/30/05 - grant MGMT$ECM_CONFIG_HISTORY views to MGMT_USER Rem eujang 03/04/05 - register job commands GetFileSWLib and Rem PutFileSWLib Rem asaraswa 02/28/05 - populating mgmt_inv_summary table Rem apbharga 02/22/05 - include MGMT_BUG_ADV_HOME_PATCH in upgrade Rem script Rem mgoodric 02/17/05 - add more history data upgrades Rem mgoodric 02/13/05 - add history data upgrades Rem vkhizder 02/11/05 - purging related changes Rem mgoodric 02/08/05 - add CSA/Config History/CPF/Search/Provisioning upgrades Rem scgrover 02/01/05 - scgrover_compress_indexes_050131 Rem scgrover 01/31/05 - Created Rem Rem put index compression here since we need to call a plsql procedure to do it. BEGIN EMD_MAINTENANCE.COMPRESS_INDEX('CON_UK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('ENT_PK', 2, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MDIV_PK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_ARU_PRD_RLS_PK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_DELTA_ENTRY_ROWGUID_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_DELTA_ENTRY_TIME_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_DELTA_ENTRY_SHOULD_BE_UK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_DELTA_SNAP_IDX2', 3, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_ECM_SNAP_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_INV_COM_CONT_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_INV_COMPONENT_IDX_1', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_INV_DEP_ER_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_HC_OS_COMPONENTS_IDX1', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_HC_VENDOR_SW_SUMMARY_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_DELTA_IDS_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_HC_VENDOR_SW_COMPS_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_HC_CPU_DETAILS_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_HC_IOCARD_DETAILS_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_HC_NIC_DETAILS_IDX', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_ESCI_PK', 1, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('OSP_SNP_PK', 2, FALSE); EMD_MAINTENANCE.COMPRESS_INDEX('MGMT_INV_VERSIONED_PATCH_PIDX', 1, FALSE); END; / -- registering correct purging policies BEGIN EM_PURGE.drop_purge_policy('CSA'); -- Purge policy for marking for deletion CSA snapshots EM_PURGE.add_purge_policy('CSA', EM_PURGE.G_POLICY_TYPE_SYSTEM, 'MGMT_ECM_CSA_PKG.AUTO_PURGE', 0, -- use number of retention hours used by -- CONFIG retention group below NULL, -- No rollup procedure 'Purge policy for CSA clients.', EM_PURGE.G_RETENTION_GROUP_CONFIG ); EM_PURGE.drop_purge_policy('HOST_CONFIG'); -- Purge policy for host config snapshots EM_PURGE.add_purge_policy('HOST_CONFIG', EM_PURGE.G_POLICY_TYPE_SYSTEM, 'ECM_CT.DELETE_HOST_CONFIG_SNAPSHOTS', 0, -- use number of retention hours used by -- CONFIG retention group below NULL, -- No rollup procedure 'Purge policy for host config snapshots.', EM_PURGE.G_RETENTION_GROUP_CONFIG ); -- Purge policy for generic ECM snapshots EM_PURGE.add_purge_policy('ECM_SNAPSHOTS', EM_PURGE.G_POLICY_TYPE_SYSTEM, 'ECM_CT.DELETE_SNAPSHOTS', 0, -- use number of retention hours used by -- CONFIG retention group below NULL, -- No rollup procedure 'Purge policy for generic ECM snapshots.', EM_PURGE.G_RETENTION_GROUP_CONFIG ); COMMIT; END; / Rem convert 10.1.0.2.0 NULL columns to 10.2.0.1.0 NOT NULL columns ALTER TABLE MGMT_ECM_SNAPSHOT_METADATA DISABLE ALL TRIGGERS; ALTER TABLE MGMT_ECM_SNAPSHOT_MD_TABLES DISABLE ALL TRIGGERS; ALTER TABLE MGMT_ECM_SNAPSHOT_MD_COLUMNS DISABLE ALL TRIGGERS; BEGIN UPDATE MGMT_ECM_SNAPSHOT_METADATA SET UI_NAME = SNAPSHOT_TYPE || ' for ' || TARGET_TYPE WHERE UI_NAME = ' ' OR UI_NAME IS NULL; UPDATE MGMT_ECM_SNAPSHOT_MD_TABLES SET UI_NAME = NAME WHERE UI_NAME = ' ' OR UI_NAME IS NULL; UPDATE MGMT_ECM_SNAPSHOT_MD_TABLES SET FULL_TABLE_PATH = NAME WHERE FULL_TABLE_PATH = ' ' OR FULL_TABLE_PATH IS NULL; UPDATE MGMT_ECM_SNAPSHOT_MD_COLUMNS SET UI_NAME = NAME WHERE UI_NAME = ' ' OR UI_NAME IS NULL; COMMIT; END; / ALTER TABLE MGMT_ECM_SNAPSHOT_METADATA MODIFY ( UI_NAME NOT NULL ); ALTER TABLE MGMT_ECM_SNAPSHOT_MD_TABLES MODIFY ( FULL_TABLE_PATH NOT NULL, UI_NAME NOT NULL ); ALTER TABLE MGMT_ECM_SNAPSHOT_MD_COLUMNS MODIFY ( UI_NAME NOT NULL ); ALTER TABLE MGMT_ECM_SNAPSHOT_METADATA ENABLE ALL TRIGGERS; ALTER TABLE MGMT_ECM_SNAPSHOT_MD_TABLES ENABLE ALL TRIGGERS; ALTER TABLE MGMT_ECM_SNAPSHOT_MD_COLUMNS ENABLE ALL TRIGGERS; Rem update ECM metadata tables/columns @&EM_SQL_ROOT/core/v102010/ecm/ecm_csa_metadata.sql @&EM_SQL_ROOT/core/v102010/ecm/ecm_hc_host_config.sql @&EM_SQL_ROOT/core/v102010/ecm/ecm_cfw_host_config.sql Rem update derived materialized views BEGIN ECM_UTIL.UPDATE_ECM_METADATA; COMMIT; END; / Rem grant access to Configuration History views to MGMT_USER BEGIN FOR crec IN ( SELECT object_name FROM all_objects WHERE (object_name LIKE 'MGMT$ECM_CONFIG_HISTORY%') AND (object_type='VIEW') ) LOOP BEGIN EXECUTE IMMEDIATE 'GRANT SELECT ON ' || crec.object_name || ' TO MGMT_USER'; EXECUTE IMMEDIATE 'CREATE OR REPLACE PUBLIC SYNONYM ' || crec.object_name || ' FOR ' || crec.object_name; EXCEPTION -- if the synonym already exists, continue WHEN OTHERS THEN NULL; END; END LOOP; COMMIT; END; / Rem set CSA out-of-box status BEGIN INSERT INTO MGMT_ECM_CSA_OUT_OF_BOX (STATUS) VALUES(0); COMMIT; END; / BEGIN -- Remove existing registered searches DELETE FROM MGMT_AVAILABLE_SEARCHES; COMMIT; -- Host Related Searches -- ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.OracleProductsSearchPage','ORACLE_PRODUCTS'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.OSRegisteredCompSearchPage','OSREGSW'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.InstalledPatchSetsSearchPage','PATCHSETS_INSTALLED'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.InstalledOSComponentsSearchPage','OSCOMPONENTS'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.InstalledOSPatchesSearchPage','OSPATCHES'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.OSPropertySettingsSearchPage','OSPROPERTYSETTINGS'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.OSPropertyChangesSearchPage','OSPROPERTYCHANGES'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.OSandHWSummariesSearchPage','HARDWARESUMMARIES'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.NICConfSearchPage','NICCONFIGS'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('host', 'oracle.sysman.eml.ecm.search.FileSystemsSearchPage','FSYSTEMS'); -- Begin Database Searches --------------------------------------------------- ECM_UTIL.INSERT_AVAIL_SEARCHES ('oracle_database', 'oracle.sysman.db.rprt.search.InitializationParameterSearchPage','initParams'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('oracle_database', 'oracle.sysman.db.rprt.search.InitializationParameterChangeSearchPage','initParamsDelta'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('oracle_database', 'oracle.sysman.db.rprt.search.TablespaceSearchPage','tbsp'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('oracle_database', 'oracle.sysman.db.rprt.search.DatafileSearchPage','datafile'); ECM_UTIL.INSERT_AVAIL_SEARCHES ('oracle_database', 'oracle.sysman.db.rprt.search.FeatureUsageSearchPage','featureUsage'); -- End Database Available Searches ------------------------------------------- COMMIT; END; / -- Target deletion callbacks and exceptions BEGIN DECLARE del_exceptions SMP_EMD_STRING_ARRAY := SMP_EMD_STRING_ARRAY(); BEGIN -- Target deletion exceptions del_exceptions.extend(1); del_exceptions(1) := 'MGMT_ECM_SAVEDHOSTCONFIG'; MGMT_ADMIN.add_target_deletion_exceptions(del_exceptions); COMMIT; END; -- new logging modules BEGIN MGMT_LOG.register_logging_module(ECM_CT.ECM_PURGE_LOG_MODULE_NAME); COMMIT; END; -- Post-creation processing logic for Host Patching. BEGIN -- Register a Target deletion callback with the Core Framework so -- that we can clean up our tables if a Group or Host is deleted -- from elsewhere. MGMT_ADMIN.add_target_deletion_callback( 'mgmt_ecm_hostpatch.group_or_host_deleted' ); COMMIT; END; END; / --populate MGMT_INV_SUMMARY with data DECLARE l_counter NUMBER := 0; BEGIN FOR item IN ( SELECT snapshot_guid FROM mgmt_ecm_snapshot s WHERE s.snapshot_type = 'host_configuration' and s.target_type = 'host' ) LOOP l_counter := l_counter + 1; 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 item.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 = item.snapshot_guid and comp.container_guid = cont.container_guid and comp.component_guid = ps_patch.component_guid(+) and map.component_name = comp.name; IF l_counter >= MGMT_GLOBAL.MAX_COMMIT THEN l_counter := 0; COMMIT; END IF; END LOOP; COMMIT; END; / -- Register commands used by cloning jobs BEGIN mgmt_jobs.register_command('putFileSWLib', 'oracle.sysman.emdrep.jobs.commands.PutFileSWLib', MGMT_JOBS.LONG_RUNNING_COMMAND); mgmt_jobs.register_command('getFileSWLib', 'oracle.sysman.emgc.provision.jobs.commands.GetFileSWLib', MGMT_JOBS.LONG_RUNNING_COMMAND); COMMIT; END; / @&EM_SQL_ROOT/core/v102010/ecm/ecm_aru_info_load.sql @&EM_SQL_ROOT/core/v102010/ecm/patch/critical/create_cpf_policy.sql -- Populate MGMT_BUG_ADV_HOME_PATCH with new data BEGIN CPF_POLICY.RUN_SETUP_CPF_ADV_HOME_PATCH(); COMMIT; END; /