Rem drv:
Rem
Rem $Header: ecm_schema_upgrade.sql 09-sep-2005.03:02:55 pdasika Exp $
Rem
Rem ecm_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem ecm_schema_upgrade.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem pdasika 09/07/05 - Moving types to v101050/ecm_schema_upgrade.sql
Rem mningomb 08/28/05 - Changes for CPF related entries
Rem chyu 08/26/05 - removing SMT_FK,SMC_IDX2 since it is in
Rem the 10.2.0.1 upgrade file.
Rem asaraswa 08/24/05 - removing upgrade statements from after beta
Rem branch
Rem chyu 07/25/05 - fixing the mgmt_bug_advisor_violation dependency
Rem error
Rem chyu 07/25/05 - modifying the upgrade header
Rem chyu 07/25/05 - removing the / from the create table statement
Rem since it would prompt the upgrade to execute it
Rem twice
Rem mningomb 07/22/05 - Fix for 4506651
Rem chyu 07/18/05 - modify the version number for the header
Rem abhalla 07/15/05 - Changes to MGMT_CPF_METRIC_SOURCE,MGMT_BUG_ADV_HOME_PATCH tables
Rem abhalla 07/07/05 - Create new table MGMT_CPF_METRIC_SOURCE
Rem smalathe 06/22/05 - Bug4430894: Check for existing index before
Rem creating
Rem agor 06/20/05 - drop delta 102 indexes not being used - bug
Rem 4338052
Rem pdasika 06/14/05 - Added creating of new types for use in patch
Rem related pl/sql packages
Rem asaraswa 06/07/05 - fixing bug 4408305
Rem ajdsouza 05/18/05 - add inet/mac address index to mgmt_ecm_hw_nic
Rem asaraswa 05/18/05 - adding new ECM indices
Rem asaraswa 05/16/05 - adding mgmt$csa_failed view
Rem mgoodric 05/04/05 - add MGMT_INV_SUMMARY_IDX
Rem niramach 04/15/05 - Change upgrade header version from
Rem 10.2.0.1 to 10.2.0.1.0
Rem asaraswa 04/08/05 - adding patches column to mgmt_hc_os_summary
Rem chyu 03/18/05 - removing the drop table statement
Rem eujang 03/05/05 - debugging
Rem eujang 03/04/05 - added CLONE_SOURCE_RECORD
Rem eujang 03/03/05 - debugging the create table statement
Rem eujang 03/03/05 - add missing emc indexes to the upgrade schema
Rem asaraswa 03/03/05 -
Rem mgoodric 03/01/05 - add more indexs
Rem asaraswa 02/28/05 - adding 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 vkhizder 02/15/05 - making MGMT_INV_COMPONENT_IDX unique
Rem asaraswa 02/16/05 - adding default values for appid and overall
Rem compliance for csa
Rem mgoodric 02/13/05 - add history data upgrades
Rem mgoodric 02/10/05 - add more tables and indexes
Rem ranmath 02/09/05 - Upgrade schema for Host Patching.
Rem mgoodric 02/08/05 - add CSA/Config History/CPF/Search/Provisioning
Rem upgrades
Rem ktlaw 01/13/05 - add repmgr header
Rem vkhizder 01/05/05 - vkhizder_bug-4092001
Rem vkhizder 01/02/05 - Schema upgrade script to 10.2.0.1.0 (from 10.1.0.4.0)
Rem
Rem Defines for column sizes
DEFINE GUID_RAW_LENGTH = 16
DEFINE MAX_VARCHAR2_LENGTH = 4000
DEFINE TARGET_NAME_LENGTH = 256
DEFINE TARGET_TYPE_LENGTH = 64
DEFINE DISPLAY_TARGET_NAME_LENGTH = 256
DEFINE DISPLAY_TARGET_TYPE_LENGTH = 128
DEFINE SNAPSHOT_TYPE_LENGTH = 64
DEFINE USER_NAME_LENGTH = 256
DEFINE NAME_LENGTH = 128
DEFINE DESCRIPTION_LENGTH = 2000
DEFINE SHORT_DESCRIPTION_LENGTH = 500
DEFINE VERSION_LENGTH = 100
DEFINE TYPE_LENGTH = 100
DEFINE LOCATION_LENGTH = 1024
DEFINE INET_ADDRESS_LENGTH = 20
DEFINE DOMAIN_NAME_LENGTH = 500
DEFINE IP_LENGTH = 20
DEFINE COMPNAME = 128
DEFINE COMPEXTNAME = 128
DEFINE COMPVERSION = 64
DEFINE COMPBASEVERSION = 64
DEFINE COMPHOMELOCATION = 1024
DEFINE COMPHOME = 1024
DEFINE NIC_FLAGS = 1024
DEFINE DISPLAY_NAME_LENGTH = 128
Rem Add/Drop new/changed types
CREATE OR REPLACE TYPE SAVE_COMPOSITE_REC IS OBJECT
(
target_name VARCHAR2(256),
target_type VARCHAR2(64),
display_target_name VARCHAR2(256),
display_target_type VARCHAR2(128),
snapshot_type VARCHAR2(64),
collection_time DATE,
error_message VARCHAR2(1024),
save_success SMALLINT
);
/
--Next a table type is defined using the previous object type as a rowtype:
CREATE OR REPLACE TYPE SAVE_COMPOSITE_TAB IS TABLE OF SAVE_COMPOSITE_REC;
/
CREATE OR REPLACE TYPE PATCH_ADV_TYPE AS OBJECT
(
ADV_NAME VARCHAR2(128),
ADV_URL VARCHAR2(128),
ADV_IMPACT VARCHAR2(256),
ADV_ABSTRACT VARCHAR2(1024),
HOST_COUNT INTEGER,
HOME_COUNT INTEGER
);
/
CREATE OR REPLACE TYPE PATCH_ADVISORIES_TYPE IS TABLE OF PATCH_ADV_TYPE;
/
DROP TYPE CLONE_SOURCE_TABLE;
Rem OUI_PLATFORM -- oui platform id, 453 for solaris
Rem HOME_TYPE -- db or ias, NULL if home is built-in-clonable
Rem IS_AD_HOC_CLONABLE -- true if home is ad-hoc-clonable
Rem PRODUCT_VERSION -- 9.2.0.4.0, 9.2.0.3.0
Rem PRODUCT_INTERNAL_NAMES -- oracle.rdbms, oracle.iappserver.iapptop
CREATE OR REPLACE TYPE CLONE_SOURCE_RECORD AS OBJECT
(
HOME_LOCATION VARCHAR2(128),
HOME_NAME VARCHAR2(64),
HOST_NAME VARCHAR2(256),
PLATFORM VARCHAR2(150),
ADDRESS_SIZE VARCHAR2(10),
TARGETS VARCHAR2(4000),
PRODUCTS VARCHAR2(4000),
EMD_URL VARCHAR2(2000),
OUI_PLATFORM VARCHAR2(16),
HOME_TYPE VARCHAR2(16),
IS_AD_HOC_CLONABLE VARCHAR2(16),
PRODUCT_VERSIONS VARCHAR2(256),
PRODUCT_INTERNAL_NAMES VARCHAR2(2000)
);
/
CREATE OR REPLACE TYPE CLONE_SOURCE_TABLE AS TABLE OF CLONE_SOURCE_RECORD;
/
CREATE OR REPLACE TYPE MGMT_ORACLESW_RECORD AS OBJECT
(
compName VARCHAR2(&COMPNAME),
compExternalName VARCHAR2(&COMPEXTNAME),
compVersion VARCHAR2(&COMPVERSION),
compBaseVersion VARCHAR2(&COMPBASEVERSION),
compHomeLocation VARCHAR2(&COMPHOMELOCATION),
compHome VARCHAR2(&COMPHOME),
compTime DATE,
hasAdvisory NUMBER
);
/
CREATE OR REPLACE TYPE MGMT_ORACLESW_LIST AS TABLE OF MGMT_ORACLESW_RECORD;
/
CREATE OR REPLACE TYPE MGMT_VENDORSW_RECORD AS OBJECT
(
compName VARCHAR2(&NAME_LENGTH),
vendorName VARCHAR2(&NAME_LENGTH),
compVersion VARCHAR2(&VERSION_LENGTH),
compLocation VARCHAR2(&LOCATION_LENGTH),
numberOfComps NUMBER,
compTime DATE
);
/
CREATE OR REPLACE TYPE MGMT_VENDORSW_LIST AS TABLE OF MGMT_VENDORSW_RECORD;
/
CREATE OR REPLACE TYPE MGMT_HWGEN_RECORD AS OBJECT
(
system_config VARCHAR2(&MAX_VARCHAR2_LENGTH),
machine_architecture VARCHAR2(&SHORT_DESCRIPTION_LENGTH),
vendor_name VARCHAR2(&NAME_LENGTH),
memory_size_in_mb VARCHAR2(1024),
local_disk_space_in_gb VARCHAR2(1024),
clock_freq_in_mhz VARCHAR2(1024),
cpu_count VARCHAR2(1024),
cpu_board_count VARCHAR2(1024),
iocard_count VARCHAR2(1024),
fan_count VARCHAR2(1024),
power_supply_count VARCHAR2(1024)
);
/
CREATE OR REPLACE TYPE MGMT_HWGEN_LIST AS TABLE OF MGMT_HWGEN_RECORD;
/
CREATE OR REPLACE TYPE MGMT_CPUDETAILS_RECORD AS OBJECT
(
freq_in_mhz VARCHAR2(1024),
vendor_name VARCHAR2(&NAME_LENGTH),
revision VARCHAR2(&DESCRIPTION_LENGTH),
ecache_in_mb VARCHAR2(1024),
impl VARCHAR2(&SHORT_DESCRIPTION_LENGTH),
mask VARCHAR2(&SHORT_DESCRIPTION_LENGTH)
);
/
CREATE OR REPLACE TYPE MGMT_CPUDETAILS_LIST AS TABLE OF MGMT_CPUDETAILS_RECORD;
/
CREATE OR REPLACE TYPE MGMT_IOCARDDETAILS_RECORD AS OBJECT
(
name VARCHAR2(&NAME_LENGTH),
vendor_name VARCHAR2(&NAME_LENGTH),
bus VARCHAR2(&SHORT_DESCRIPTION_LENGTH),
freq_in_mhz VARCHAR2(1024),
revision VARCHAR2(&DESCRIPTION_LENGTH)
);
/
CREATE OR REPLACE TYPE MGMT_IOCARDDETAILS_LIST AS TABLE OF MGMT_IOCARDDETAILS_RECORD;
/
CREATE OR REPLACE TYPE MGMT_NICDETAILS_RECORD AS OBJECT
(
name VARCHAR2(&NAME_LENGTH),
inet_address VARCHAR2(&INET_ADDRESS_LENGTH),
max_transfer_unit VARCHAR2(1024),
broadcast_address VARCHAR2(&INET_ADDRESS_LENGTH),
mask VARCHAR2(&INET_ADDRESS_LENGTH),
flags VARCHAR2(&NIC_FLAGS),
mac_address VARCHAR2(&INET_ADDRESS_LENGTH),
hostname_aliases VARCHAR2(&MAX_VARCHAR2_LENGTH)
);
/
CREATE OR REPLACE TYPE MGMT_NICDETAILS_LIST AS TABLE OF MGMT_NICDETAILS_RECORD;
/
DROP TYPE MGMT_INV_TARGET_LIST_OBJ_ARRAY;
CREATE OR REPLACE TYPE MGMT_INV_TARGET_LIST_OBJ AS OBJECT
(
target_type VARCHAR2(25),
property_name VARCHAR2(25),
property_value VARCHAR2(25)
);
/
CREATE OR REPLACE TYPE MGMT_INV_TARGET_LIST_OBJ_ARRAY AS TABLE OF MGMT_INV_TARGET_LIST_OBJ;
/
Rem Add/Drop new/changed indexes
DROP INDEX MGMT_HC_OS_COMPONENTS_IDX1;
CREATE INDEX MGMT_HC_OS_COMPONENTS_IDX1
ON MGMT_HC_OS_COMPONENTS(SNAPSHOT_GUID, TYPE, NAME) COMPUTE STATISTICS;
Rem Create MGMT_ECM_GEN_SNAP_IDX(2) index if it does not exist
DECLARE
l_idx_name VARCHAR2(30);
BEGIN
BEGIN
SELECT index_name INTO l_idx_name
FROM all_indexes WHERE index_name = 'MGMT_ECM_GEN_SNAP_IDX';
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE
'CREATE INDEX MGMT_ECM_GEN_SNAP_IDX
ON MGMT_ECM_GEN_SNAPSHOT (TARGET_TYPE,
SNAPSHOT_TYPE, IS_CURRENT, TARGET_NAME) COMPUTE STATISTICS';
END;
BEGIN
SELECT index_name INTO l_idx_name
FROM all_indexes WHERE index_name = 'MGMT_ECM_GEN_SNAP_IDX2';
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXECUTE IMMEDIATE
'CREATE INDEX MGMT_ECM_GEN_SNAP_IDX2
ON MGMT_ECM_GEN_SNAPSHOT (IS_CURRENT, TARGET_GUID,
SNAPSHOT_TYPE) COMPUTE STATISTICS';
END;
END;
/
CREATE INDEX MGMT_ECM_SNAP_IDX2
ON MGMT_ECM_SNAPSHOT(IS_CURRENT, TARGET_TYPE,
SNAPSHOT_TYPE, TARGET_NAME) COMPRESS 1 COMPUTE STATISTICS;
Rem Add/Drop new CSA columns/tables
DROP VIEW EM$ECM_TARGET_INSTALL;
ALTER TABLE MGMT_ECM_CSA ADD
(
CONNECTION_TYPE NUMBER DEFAULT 3,
IS_WINDOWS_ADMIN VARCHAR2(1),
WINDOWS_DOMAIN VARCHAR2(100),
AUTO_CONFIG_URL VARCHAR2(4000),
BROWSER_PROXY_ENABLED VARCHAR2(1)
);
ALTER TABLE MGMT_ECM_CSA_GENERAL_INFO ADD
(
APPID VARCHAR2(128) DEFAULT 'Default' NOT NULL,
WORST_RULE_STATUS NUMBER DEFAULT 15 NOT NULL
);
CREATE INDEX MGMT_ECM_CSA_GENERAL_INFO_IDX2
ON MGMT_ECM_CSA_GENERAL_INFO(APPID) COMPUTE STATISTICS;
ALTER TABLE MGMT_ECM_HW ADD
(
SYSTEM_SERIAL_NUMBER VARCHAR2(100)
);
CREATE TABLE MGMT_ECM_CSA_RULES
(
ECM_SNAPSHOT_ID RAW(16) NOT NULL
CONSTRAINT MGMT_ECM_CSA_RULES0 REFERENCES MGMT_ECM_GEN_SNAPSHOT (SNAPSHOT_GUID) ON DELETE CASCADE,
NAME VARCHAR2(128),
DESCRIPTION VARCHAR2(256),
STATUS NUMBER,
MOREINFO VARCHAR2(1024),
CONSTRAINT MGMT_ECM_CSA_RULES_PK PRIMARY KEY (ECM_SNAPSHOT_ID, NAME)
) MONITORING;
CREATE TABLE MGMT_ECM_CSA_OUT_OF_BOX
(
STATUS NUMBER DEFAULT 0 NOT NULL
) MONITORING;
CREATE TABLE MGMT_ECM_CSA_FAILED
(
ID RAW(16) DEFAULT SYS_GUID() NOT NULL,
TIMESTAMP DATE,
TIMEZONE_DELTA NUMBER,
SAVED_TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
EFFECTIVE_IP VARCHAR2(20),
APPID VARCHAR2(128),
REFERRING_URL_HEADER VARCHAR2(4000),
REFERRING_URL_PARAMS VARCHAR2(4000),
CSA_URL_HEADER VARCHAR2(4000),
CSA_URL_PARAMS VARCHAR2(4000),
DESTINATION_URL_HEADER VARCHAR2(4000),
DESTINATION_URL_PARAMS VARCHAR2(4000),
BROWSER_TYPE VARCHAR2(100),
BROWSER_VERSION VARCHAR2(20),
BROWSER_JVM_VENDOR VARCHAR2(100),
BROWSER_JVM_VERSION VARCHAR2(20),
OS_ARCH VARCHAR2(100),
OS_NAME VARCHAR2(100),
HTTP_REQUEST_USER_AGENT VARCHAR2(100),
ERROR_CODE VARCHAR2(1),
ERROR_TEXT VARCHAR2(1024)
) MONITORING;
CREATE INDEX MGMT_ECM_CSA_FAILED_TS_IDX
ON MGMT_ECM_CSA_FAILED(SAVED_TIMESTAMP) COMPUTE STATISTICS;
CREATE INDEX MGMT_ECM_CSA_FAILED_IDX2
ON MGMT_ECM_CSA_FAILED(APPID) COMPUTE STATISTICS;
CREATE INDEX MGMT_ECM_CSA_FAILED_IDX3
ON MGMT_ECM_CSA_FAILED(ID) COMPUTE STATISTICS;
CREATE TABLE MGMT_ECM_CSA_APPID_TARGET_MAP
(
APPID VARCHAR2(128),
TARGET_GUID RAW(16) NOT NULL,
CONSTRAINT MGMT_ECM_CSA_APPID_TARGET_UK UNIQUE (APPID, TARGET_GUID)
) MONITORING;
CREATE UNIQUE INDEX MGMT_ECM_CSA_APPID_TARGET_IDX
ON MGMT_ECM_CSA_APPID_TARGET_MAP(TARGET_GUID, APPID) COMPUTE STATISTICS;
Rem Add new COMPARE columns/tables
ALTER TABLE MGMT_COMP_RESULT_TO_JOB_MAP ADD
(
RHS_SNAPSHOT_GUID RAW(&GUID_RAW_LENGTH)
);
Rem Add new CONFIG HISTORY columns/tables
ALTER TABLE MGMT_ECM_SNAPSHOT_METADATA ADD
(
COMPARE_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL,
HISTORY_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL
-- UI_NAME VARCHAR2(256) NOT NULL
);
ALTER TABLE MGMT_ECM_SNAPSHOT_MD_TABLES ADD
(
COMPARE_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL,
HISTORY_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL,
FULL_TABLE_PATH VARCHAR2(1000) -- NOT NULL
-- UI_NAME VARCHAR2(256) NOT NULL
);
ALTER TABLE MGMT_ECM_SNAPSHOT_MD_COLUMNS ADD
(
COMPARE_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL,
HISTORY_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL
-- UI_NAME VARCHAR2(256) NOT NULL
);
CREATE MATERIALIZED VIEW MGMT_ECM_MD_ALL_TBL_COLUMNS
BUILD DEFERRED
REFRESH ON DEMAND
AS
SELECT c.METADATA_ID,
m.TARGET_TYPE,
m.SNAPSHOT_TYPE,
t.NAME AS TABLE_NAME,
c.NAME,
c.UI_NAME,
c.TYPE, c.TYPE_FORMAT,
c.UI_ON, c.COMPARE_ON, c.COMPARE_UI_ON, c.HISTORY_ON, c.HISTORY_UI_ON,
c.IS_KEY, c.IS_CONTEXT, c.IS_SUMMARY,
c.IS_CHILD_LINK, c.LINK_COLUMN_NAME,
RANK() OVER (PARTITION BY c.METADATA_ID, t.NAME
ORDER BY anc.TBL_ORDER ASC, c.COL_ORDER ASC) AS COL_ORDER,
c.TABLE_NAME AS SOURCE_TABLE_NAME
FROM
MGMT_ECM_SNAPSHOT_METADATA m,
MGMT_ECM_SNAPSHOT_MD_TABLES t,
MGMT_ECM_SNAPSHOT_MD_TABLES anc,
MGMT_ECM_SNAPSHOT_MD_COLUMNS c
WHERE
(anc.NAME, anc.METADATA_ID) IN
(SELECT anc_t.NAME, anc_t.METADATA_ID
FROM MGMT_ECM_SNAPSHOT_MD_TABLES anc_t
START WITH anc_t.NAME = t.NAME
AND anc_t.METADATA_ID = t.METADATA_ID
CONNECT BY anc_t.NAME = PRIOR anc_t.PARENT_TABLE_NAME
AND anc_t.METADATA_ID = PRIOR anc_t.METADATA_ID)
AND c.METADATA_ID = anc.METADATA_ID
AND c.TABLE_NAME = anc.NAME
AND c.METADATA_ID = m.METADATA_ID
AND m.KIND = 'P'
AND ((c.IS_KEY = 'Y') OR (anc.NAME = t.NAME));
ALTER TABLE MGMT_ECM_MD_ALL_TBL_COLUMNS MONITORING;
CREATE TABLE MGMT_ECM_MD_HIST_TBLS
(
METADATA_ID RAW(16) NOT NULL,
TARGET_TYPE VARCHAR2(64) NOT NULL,
SNAPSHOT_TYPE VARCHAR2(64) NOT NULL,
NAME VARCHAR2(30) NOT NULL,
NUM_HIST_UI_KEYS NUMBER(7) NOT NULL,
HIST_UI_KEY1 VARCHAR2(30),
HIST_UI_KEY2 VARCHAR2(30),
HIST_UI_KEY3 VARCHAR2(30),
HIST_UI_KEY4 VARCHAR2(30),
HIST_UI_KEY5 VARCHAR2(30),
HIST_UI_KEY6 VARCHAR2(30),
UI_NAME VARCHAR2(256) NOT NULL,
UI_ON VARCHAR2(1) NOT NULL,
COMPARE_ON VARCHAR2(1) NOT NULL,
COMPARE_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL,
HISTORY_ON VARCHAR2(1) NOT NULL,
HISTORY_UI_ON VARCHAR2(1) DEFAULT 'Y' NOT NULL,
PARENT_TABLE_NAME VARCHAR2(30),
FULL_TABLE_PATH VARCHAR2(1000) NOT NULL,
IS_SINGLE_ROW VARCHAR2(1) NOT NULL,
TBL_ORDER NUMBER(10) NOT NULL,
CONSTRAINT SMHT_PK PRIMARY KEY(METADATA_ID, NAME),
CONSTRAINT SMHT_SMHT_FK FOREIGN KEY(METADATA_ID, PARENT_TABLE_NAME)
REFERENCES MGMT_ECM_MD_HIST_TBLS(METADATA_ID, NAME)
ON DELETE CASCADE
) MONITORING;
Rem Add new SEARCH columns/tables
CREATE TABLE MGMT_AVAILABLE_SEARCHES
(
TARGET_TYPE VARCHAR2(64) NOT NULL,
CLASSNAME VARCHAR2(256) NOT NULL,
SRCH_TYPE VARCHAR2(64) NOT NULL,
CONSTRAINT MGMT_ECM_AS_KEY PRIMARY KEY (TARGET_TYPE, CLASSNAME,SRCH_TYPE)
) MONITORING;
Rem Add new HOST CONFIG columns/tables
CREATE TABLE MGMT_ECM_SAVEDHOSTCONFIG
(
TARGET_GUID RAW(16),
CREATOR VARCHAR2(256) DEFAULT NULL,
HOSTCONFIG_CONTENTS_GUID RAW(16) NOT NULL,
FILENAME VARCHAR2(1024) DEFAULT NULL,
LOAD_TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT MGMT_ECM_SAVEDHOSTCONFIG_PK PRIMARY KEY (HOSTCONFIG_CONTENTS_GUID),
CONSTRAINT MGMT_ECM_SAVEDHOSTCONFIG_FK FOREIGN KEY (HOSTCONFIG_CONTENTS_GUID)
REFERENCES MGMT_ECM_SNAPSHOT(SNAPSHOT_GUID)
ON DELETE CASCADE
) MONITORING;
CREATE INDEX MGMT_ECM_SAVEDHC_IDX
ON MGMT_ECM_SAVEDHOSTCONFIG(TARGET_GUID) COMPUTE STATISTICS;
Rem Add/Drop new CPF columns/tables
CREATE TABLE MGMT_BUG_PATCH_CERTIFICATE
(
PATCH_ID NUMBER NOT NULL,
RELEASE_ID NUMBER NOT NULL,
PLATFORM_ID NUMBER NOT NULL,
PRODUCT NUMBER NOT NULL,
PATCH_TYPE VARCHAR2(32) NOT NULL,
CERTIFY_DATE DATE NOT NULL,
CONSTRAINT MBPC_PK PRIMARY KEY (PATCH_ID, RELEASE_ID, PLATFORM_ID)
) MONITORING;
Rem The MGMT_BUG_ADV_HOME_PATCH table contains information about which homes are affected with which advisory
Rem We introduced a new column BUG_NUMBER - which is part of primary Key
Rem One way is to drop the PK constraint, add the column and create a new PK constraint
Rem Another way is to completely drop the table and recreate it
Rem since the dependent views, procs and even data will be recreated - I am going for the second way
DROP TABLE MGMT_BUG_ADV_HOME_PATCH;
CREATE TABLE MGMT_BUG_ADV_HOME_PATCH
(
ADVISORY_NAME VARCHAR2(&DISPLAY_NAME_LENGTH) NOT NULL,
BUG_NUMBER NUMBER NOT NULL,
HOST_NAME VARCHAR2(&TARGET_NAME_LENGTH) 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),
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 The MGMT_INV_SUMMARY table can be dropped and re-created, since we
Rem will re-populate it during the data upgrade
DECLARE
l_count NUMBER;
BEGIN
SELECT count(*)
INTO l_count
FROM ALL_OBJECTS
WHERE OBJECT_NAME='MGMT_INV_SUMMARY';
IF (l_count > 0)
THEN
EXECUTE IMMEDIATE 'DROP TABLE MGMT_INV_SUMMARY';
END IF;
END;
/
CREATE TABLE MGMT_INV_SUMMARY
(
SNAPSHOT_GUID RAW(16) NOT NULL,
COMP_EXTERNAL_NAME VARCHAR2(128) NOT NULL,
COMP_VERSION VARCHAR2(64) NOT NULL,
CONTAINER_LOCATION VARCHAR2(128) NOT NULL,
CONTAINER_GUID RAW(16) NOT NULL,
IS_PATCHED NUMBER(1) NOT NULL,
MAP_TARGET_TYPE VARCHAR2(64) NOT NULL,
MAP_PROPERTY_NAME VARCHAR2(64) DEFAULT NULL,
MAP_PROPERTY_VALUE VARCHAR2(1024) DEFAULT NULL,
CONSTRAINT SNAP_GUID_FK FOREIGN KEY(SNAPSHOT_GUID)
REFERENCES MGMT_ECM_SNAPSHOT(SNAPSHOT_GUID)
ON DELETE CASCADE
)
MONITORING;
-- 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 );
Rem entries to create types to be used in patching pl/sql
CREATE OR REPLACE TYPE VARCHAR_TABLE AS TABLE OF VARCHAR2(2500);
/
CREATE OR REPLACE TYPE DEST_PLATFORM_REC_TYPE AS OBJECT (
platform_id varchar2(20),
id_type varchar2(100)
);
/
CREATE OR REPLACE TYPE DEST_PLATFORM_ARRAY AS TABLE OF DEST_PLATFORM_REC_TYPE;
/
Rem Add new Host Patching tables and indices.
@&EM_SQL_ROOT/core/v102010/ecm/ecm_hostpatch_tables.sql
@&EM_SQL_ROOT/core/v102010/ecm/ecm_hostpatch_indexes.sql
show errors;