Rem drv: <migrate type="schema_upgrade" version="10.2.0.1" params="EM_ECM_CSA_TABLESPACE_NAME,EM_ECM_CSA_DATAFILE_NAME,EM_ECM_CSA_DATAFILE_SIZE,EM_ECM_CSA_DATAFILE_EXT_SIZE"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/v102020/ecm_schema_upgrade.sql /st_emcore_10.2.0.4.2db11.2/1 2009/07/05 22:24:35 ravpatel Exp $
Rem
Rem ecm_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      ecm_schema_upgrade.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    ravpatel    07/03/09 - Fix for bug 8242240
Rem    asaraswa    10/04/05 - fixing 4651101 
Rem    abhalla     09/19/05 - Adding CPF related changes
Rem    pdasika     09/22/05 - Adding new types for get_homes_all pkg. Dropping unused types 
Rem    pdasika     09/14/05 - Adding new types CPF_HOME_RECORD and CPF_HOME_ARRAY 
Rem    nitijain    09/09/05 - Creating new indices on ecm tables 
Rem    asaraswa    09/07/05 - rolling back new CSA tablespace 
Rem    akskumar    08/31/05 - add new table mgmt_comp_snapshot_to_step_map 
Rem    abhalla     09/01/05 - changes for bug 4579215 
Rem    mningomb    08/28/05 - Addition for CPF policy related upgrades 
Rem    ranmath     08/28/05 - bug-4573095: Add CREDS_TYPE column to 
Rem                           MGMT_ECM_HOSTPATCH_GROUPS. 
Rem    asaraswa    08/24/05 - fxing bug 4571497 
Rem    asaraswa    07/26/05 - adding csa tablespace and moving csa tables 
Rem    chyu        07/25/05 - modifying the upgrade header 
Rem    dsukhwal    07/15/05 - dsukhwal_perf_ui_bug_fixes
Rem    rmadampa    07/13/05 - Created
Rem

Rem create the CSA tablespace and move the CSA tables into it

Rem Defines for CSA tablespace
DEFINE EM_ECM_CSA_TABLESPACE_NAME = "&1"
DEFINE EM_ECM_CSA_DATAFILE_NAME = "&2"
DEFINE EM_ECM_CSA_DATAFILE_SIZE = "&3"
DEFINE EM_ECM_CSA_DATAFILE_EXT_SIZE = "&4"

Rem Create the CSA tablespace and then move the tables

/*
DECLARE
    l_obj_name VARCHAR2(30);
    l_obj_exists NUMBER;
BEGIN
    l_obj_exists := 0;
    --
    -- Create tablespace for storing CSA snapshots
    -- An individual snapshot should only take up a few KB of space,
    -- but we may have a very large number of snapshots
    BEGIN 
        l_obj_name := '';
        l_obj_exists := 0;
        SELECT tablespace_name INTO l_obj_name
          FROM dba_tablespaces 
         WHERE tablespace_name=upper('&EM_ECM_CSA_TABLESPACE_NAME');

	l_obj_exists := 1;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
	    l_obj_exists := 0;
        WHEN OTHERS THEN
	    RAISE;
    END;

    -- If tablespace doesn't exist, create one..
    IF (l_obj_exists = 0) THEN
        BEGIN
	    EXECUTE IMMEDIATE 'CREATE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME ' ||
                              ' DATAFILE ''' || '&EM_ECM_CSA_DATAFILE_NAME' || '''' ||
                              ' SIZE &EM_ECM_CSA_DATAFILE_SIZE ' ||
                              ' REUSE ' ||
                              ' AUTOEXTEND ON ' ||
                              ' NEXT &EM_ECM_CSA_DATAFILE_EXT_SIZE ' ||
                              ' MAXSIZE UNLIMITED ' ||
                              ' EXTENT MANAGEMENT LOCAL AUTOALLOCATE ' ||
                              ' PERMANENT ONLINE';
	EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('CreateRep: Failed to create ' ||
	                           ' &EM_ECM_CSA_TABLESPACE_NAME. ');
            RAISE;
	END;
    END IF;
END;
/


ALTER TABLE MGMT_ECM_CSA_SNAPSHOT_INFO MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_CSA_GENERAL_INFO MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_CSA MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_CSA_COOKIES MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_CSA_CUSTOM MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_HW MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_HW_CPU MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_HW_IOCARD MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_HW_NIC MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_OS MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_OS_PROPERTY MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_OS_COMPONENT MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_OS_FILESYSTEM MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_OS_REGISTERED_SW MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_OS_REGISTERED_SW_COMP MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_CSA_RULES MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
ALTER TABLE MGMT_ECM_CSA_FAILED MOVE TABLESPACE &EM_ECM_CSA_TABLESPACE_NAME;
 */

DROP INDEX MGMT_ECM_CSA_COOKIES_IDX;
DROP INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX;
DROP INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX2;
DROP INDEX MGMT_ECM_HW_CPU_IDX;
DROP INDEX MGMT_ECM_HW_IOCARD_IDX;
DROP INDEX MGMT_ECM_HW_NIC_IDX;
DROP INDEX MGMT_ECM_HW_NIC_IDX2;
DROP INDEX MGMT_ECM_HW_NIC_IDX3;
DROP INDEX MGMT_ECM_OS_COMPONENT_IDX;
DROP INDEX MGMT_ECM_OS_FILESYSTEM_IDX;
DROP INDEX MGMT_ECM_OS_REG_SW_IDX;
DROP INDEX MGMT_ECM_OS_REG_SW_COMP_IDX;
DROP INDEX MGMT_ECM_CSA_FAILED_TS_IDX;
DROP INDEX MGMT_ECM_CSA_FAILED_IDX2;
DROP INDEX MGMT_ECM_CSA_FAILED_IDX3;



CREATE TABLE MGMT_COMP_SNAPSHOT_TO_STEP_MAP
(
  STEP_ID NUMBER NOT NULL,
  RHS_SNAPSHOT_GUID RAW(16) NOT NULL,
  EXECUTION_ID RAW(16) NOT NULL,
  CONSTRAINT MGMT_COMP_SNAPSHOT_STEP_MAP_PK PRIMARY KEY(STEP_ID,EXECUTION_ID)
) MONITORING;

CREATE INDEX MGMT_ECM_CSA_COOKIES_IDX
  ON MGMT_ECM_CSA_COOKIES( ECM_SNAPSHOT_ID );

CREATE INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX 
  ON MGMT_ECM_CSA_GENERAL_INFO( TARGET_ID_METHOD, OS_USER_NAME, 
                                BOOT_DISK_VOLUME_SERIAL_NUM,
                                HOSTNAME, DOMAIN ); 

CREATE INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX2
  ON MGMT_ECM_CSA_GENERAL_INFO( APPID );

-- should this one be on all columns due to the presentation view with COUNT(*)
-- what about host config index then?
CREATE INDEX MGMT_ECM_HW_CPU_IDX
  ON MGMT_ECM_HW_CPU( ECM_SNAPSHOT_ID );

-- should this one be on all columns due to the presentation view with COUNT(*)
CREATE INDEX MGMT_ECM_HW_IOCARD_IDX
  ON MGMT_ECM_HW_IOCARD( ECM_SNAPSHOT_ID );

-- should this one be on all columns due to the presentation view with COUNT(*)
CREATE INDEX MGMT_ECM_HW_NIC_IDX
  ON MGMT_ECM_HW_NIC( ECM_SNAPSHOT_ID );

-- the inet and mac address index on the hw_nic table will speed storage post processing
-- look up of this table
CREATE INDEX MGMT_ECM_HW_NIC_IDX2
  ON MGMT_ECM_HW_NIC( INET_ADDRESS );

CREATE INDEX MGMT_ECM_HW_NIC_IDX3
  ON MGMT_ECM_HW_NIC( MAC_ADDRESS );

CREATE INDEX MGMT_ECM_OS_COMPONENT_IDX
  ON MGMT_ECM_OS_COMPONENT( ECM_SNAPSHOT_ID, NAME, TYPE );

CREATE INDEX MGMT_ECM_OS_FILESYSTEM_IDX
  ON MGMT_ECM_OS_FILESYSTEM( ECM_SNAPSHOT_ID, RESOURCE_NAME, 
                             MOUNT_LOCATION, TYPE );

CREATE INDEX MGMT_ECM_OS_REG_SW_IDX
  ON MGMT_ECM_OS_REGISTERED_SW( ECM_SNAPSHOT_ID, NAME, VENDOR_NAME, 
                                VERSION, INSTALLED_LOCATION );

CREATE INDEX MGMT_ECM_OS_REG_SW_COMP_IDX
  ON MGMT_ECM_OS_REGISTERED_SW_COMP( ECM_SNAPSHOT_ID, ID );

CREATE INDEX MGMT_ECM_CSA_FAILED_TS_IDX
  ON MGMT_ECM_CSA_FAILED( SAVED_TIMESTAMP );

CREATE INDEX MGMT_ECM_CSA_FAILED_IDX2
  ON MGMT_ECM_CSA_FAILED( APPID );

CREATE INDEX MGMT_ECM_CSA_FAILED_IDX3
  ON MGMT_ECM_CSA_FAILED( ID );

CREATE INDEX MGMT_ECM_IDX_TOP_LEVEL
 ON MGMT_INV_COMPONENT ( IS_TOP_LEVEL );

CREATE INDEX MGMT_ECM_IDX_PATCHSET_VERSION
 ON MGMT_INV_VERSIONED_PATCH ( VERSION );

CREATE INDEX MGMT_ECM_IDX_PLAT_NAME
 ON MGMT_ARU_PLATFORMS( PLATFORM_NAME );

                                                                                                                                              
Rem Create MGMT_ECM_GEN_SNAP_IDX(3) index if it does not exist
                                                                                                                                              
DECLARE
  l_idx_name VARCHAR2(30);
BEGIN
  --Although idx MGMT_ECM_GEN_SNAP_IDX2(IS_CURRENT, TARGET_GUID,
  --SNAPSHOT_TYPE) existed, did not want to modify it by swapping
  --TARGET_GUID and SNAPSHOT_TYPE as it would affect queries using
  --only IS_CURRENT and TARGET_GUID columns. Hence creating a new
  --index on IS_CURRENT and SNAPSHOT_TYPE columns.
  BEGIN
    SELECT index_name INTO l_idx_name
      FROM all_indexes WHERE index_name = 'MGMT_ECM_GEN_SNAP_IDX3';
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      EXECUTE IMMEDIATE
        'CREATE INDEX MGMT_ECM_GEN_SNAP_IDX3
           ON MGMT_ECM_GEN_SNAPSHOT (IS_CURRENT,
             SNAPSHOT_TYPE) COMPUTE STATISTICS';
  END;
END;
/

Rem create mgmt$csa_failed view
CREATE OR REPLACE VIEW MGMT$CSA_FAILED AS
  SELECT * FROM mgmt_ecm_csa_failed
  WITH READ ONLY;


CREATE INDEX SMHT_FK
  ON MGMT_ECM_MD_HIST_TBLS (METADATA_ID, PARENT_TABLE_NAME);

CREATE INDEX SMHT_IDX1
  ON MGMT_ECM_MD_HIST_TBLS(NUM_HIST_UI_KEYS, TARGET_TYPE, SNAPSHOT_TYPE, NAME);

CREATE INDEX SMT_FK
  ON MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, PARENT_TABLE_NAME);

CREATE INDEX SMC_IDX2
  ON MGMT_ECM_SNAPSHOT_MD_COLUMNS (IS_KEY, METADATA_ID, TABLE_NAME, HISTORY_ON, COMPARE_ON, UI_ON);

CREATE INDEX MDAT_IDX1
  ON MGMT_ECM_MD_ALL_TBL_COLUMNS (HISTORY_UI_ON, IS_KEY, HISTORY_ON);

CREATE INDEX MDAT_IDX2
  ON MGMT_ECM_MD_ALL_TBL_COLUMNS (TARGET_TYPE, SNAPSHOT_TYPE, TABLE_NAME, NAME, HISTORY_UI_ON);

ALTER TABLE MGMT_HC_OS_SUMMARY ADD 
(
  PATCHES NUMBER DEFAULT 0 NOT NULL
);

Rem Remove delta indexes for 102
DROP INDEX MGMT_DELTA_PREV_DE;
DROP INDEX MGMT_DELTA_VALUES_IDX;
DROP INDEX MGMT_DELTA_ID_VALUES_IDX;

Rem
Rem Indices for MGMT_INV_SUMMARY table
Rem
CREATE INDEX MGMT_INV_SUMMARY_IDX
    ON MGMT_INV_SUMMARY(SNAPSHOT_GUID, MAP_TARGET_TYPE) COMPRESS 2;


Rem bug-4573095: Add CREDS_TYPE column to MGMT_ECM_HOSTPATCH_GROUPS.
ALTER TABLE MGMT_ECM_HOSTPATCH_GROUPS ADD
(
  CREDS_TYPE NUMBER(1) DEFAULT -1 NOT NULL
);

DROP TABLE MGMT_BUG_ADV_HOME_PATCH;
CREATE TABLE MGMT_BUG_ADV_HOME_PATCH
(
  ADVISORY_NAME VARCHAR2(128) NOT NULL,
  BUG_NUMBER NUMBER NOT NULL,
  HOST_NAME VARCHAR2(256) NOT NULL,
  HOME_LOCATION VARCHAR2( 128 ) NOT NULL,
  PATCH_GUID RAW(16) NOT NULL,
  PREREQ_RELEASE VARCHAR2(256),
  HOME_LOCATION_DISPLAY VARCHAR2(256),
  TARGET_GUID RAW(16),
  PATCH_ID NUMBER NOT NULL,
  PATCH_RELEASE_ID NUMBER NOT NULL,
  PATCH_PLATFORM_ID NUMBER NOT NULL,
  CONTAINER_GUID RAW(16) NOT NULL,
  PATCH_VALID_STATUS VARCHAR2(10) NOT NULL,
  CONSTRAINT MBAHP_PK PRIMARY KEY ( ADVISORY_NAME, HOST_NAME, HOME_LOCATION, BUG_NUMBER, PATCH_GUID )
) MONITORING;

Rem Indices for MGMT_BUG_ADV_HOME_PATCH table
CREATE INDEX MGMT_BUG_ADV_HOME_PATCH_IDX
  ON MGMT_BUG_ADV_HOME_PATCH(TARGET_GUID) COMPUTE STATISTICS;

Rem Removing the re-create index as its not required in upgrade [bug 8242240]
Rem We are making best case effort to compress this index 
BEGIN
    EXECUTE IMMEDIATE 'ALTER INDEX MGMT_INV_COM_CONT_IDX REBUILD COMPRESS 1';
EXCEPTION
	WHEN OTHERS THEN NULL;
END;
/

Rem Create table to store the data for the CPF METRIC source.
Rem The table will be populated at the time of RefreshFromMetalink job execution.
Rem The procedure CPF_POLICY.SETUP_CPF_METRIC_SOURCE will populate this table.
CREATE TABLE MGMT_CPF_METRIC_SOURCE
(
  ADVISORY_NAME VARCHAR2(128) NOT NULL,
  IMPACT VARCHAR2(128),
  ADVISORY_ABSTRACT VARCHAR2(1024),
  HOME_LOCATION_DISPLAY VARCHAR2(1000),
  HOME_LOCATION VARCHAR2(128)  NOT NULL,
  HOST_NAME VARCHAR2(256)  NOT NULL,
  ADVISORY_URL VARCHAR2(256),
  PATCH_GUIDS VARCHAR2(4000)  NOT NULL,
  TARGET_GUID RAW(16)  NOT NULL,
  PATCHES VARCHAR2(2000),
  CONTAINER_GUID RAW(16)  NOT NULL,
  IS_VALID VARCHAR2(10) NOT NULL,
  CPF_VIOLATION_GUID RAW(16) DEFAULT SYS_GUID() NOT NULL,
  CONSTRAINT MCMS_PK PRIMARY KEY ( CONTAINER_GUID, TARGET_GUID, HOST_NAME, HOME_LOCATION, ADVISORY_NAME)
) MONITORING;

Rem Indices for MGMT_CPF_METRIC_SOURCE table
CREATE INDEX MGMT_CPF_METRIC_SOURCE_IDX
  ON MGMT_CPF_METRIC_SOURCE(TARGET_GUID) COMPUTE STATISTICS;

rem   need to drop the type MGMT_BUG_ADVISORY_VIOLATIONS first
DROP TYPE MGMT_BUG_ADVISORY_VIOLATIONS;

rem
rem   This object is used to hold the policy violation values temporarily while
rem   being passed back from a function used to calculate the violations.
rem

CREATE OR REPLACE TYPE MGMT_BUG_ADVISORY_VIOLATION AS OBJECT
(
  ADVISORY_NAME VARCHAR2(128),
  IMPACT VARCHAR2(128),
  PATCHES VARCHAR2(2000),
  ADVISORY_ABSTRACT VARCHAR2(1024),
  HOME_LOCATION_DISPLAY VARCHAR2(1000),
  HOME_LOCATION VARCHAR2(128),
  HOST_NAME VARCHAR2(256),
  ADVISORY_URL VARCHAR2(256),
  PATCH_GUIDS VARCHAR2(4000),
  REF_PATCH_GUID RAW(16),
  TARGET_GUID RAW(16) ,
  CONTAINER_GUID RAW(16),
  PATCH_VALID_STATUS VARCHAR2(10)
);
/
CREATE OR REPLACE TYPE HOME_REC_TYPE AS OBJECT (
  host_name     varchar2(256),
  home_location varchar2(256),
  home_index    number
  );
/
CREATE OR REPLACE TYPE HOME_REC_ARRAY AS TABLE OF HOME_REC_TYPE;
/
CREATE OR REPLACE TYPE CPF_HOME_RECORD AS OBJECT (
HOME_HOST               VARCHAR2(256),   
HOME_DIRECTORY          VARCHAR2(256),   
HOME_NAME               VARCHAR2(320),   
HOME_VERSION            VARCHAR2(64),
HOST_TARGET_GUID        RAW(16),
HOME_ID                 RAW(16),
HOME_PLATFORM_TYPE      VARCHAR2(3),   
HOME_HOST_BIT_LENGTH    VARCHAR2(20),
HOME_TARGET_TYPE        VARCHAR2(64),    
HOME_ARU_PLATFORM       NUMBER      
);
/

DROP TYPE HOME_CRED_ARRAY;


DROP TYPE HOME_CREDS_REC_TYPE;


CREATE OR REPLACE TYPE CPF_HOME_ARRAY AS TABLE OF CPF_HOME_RECORD;
/

CREATE OR REPLACE TYPE MGMT_BUG_ADVISORY_VIOLATIONS AS
  TABLE OF MGMT_BUG_ADVISORY_VIOLATION;
/
show errors;