Rem drv: <migrate type="schema_upgrade" version="10.2.0.0" pos="-"/>
Rem
Rem $Header: db_comp_schema_upgrade.sql 28-nov-2006.16:04:29 chyu Exp $
Rem
Rem db_comp_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2006, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      db_comp_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    chyu        11/28/06 - removing the condition since the 'fix' to
Rem                           10.2.0.1 db upgrade was due to DB version error
Rem    chyu        10/16/06 - fixing upgrade from 10.2.0.1 dbcontrol
Rem    chyu        07/13/05 - chyu_new_repmgr_upg_db
Rem    chyu        07/12/05 - Created;Wrapper for the db schema upgrade code
Rem                           that was in the original schema upgrade script
Rem

-- Functional unit schema upgrade scripts have to be added here
-- Default ordering is alphabetical 
-- (Any exception to this order has to be explicitly commented).

Rem
Rem    SCHAUDHR        04/10/04 
Rem


CREATE TABLE sysman.MGMT_DIROBJ_USERS_HOTLIST
    (
    TARGET_GUID RAW(16)   NOT NULL, 
    dir_name VARCHAR2(30) NOT NULL,
    user_name VARCHAR2(30) NOT NULL,
    CONSTRAINT mgmt_dirobj_users_hotlist_pk primary key( TARGET_GUID,dir_name,user_name)
    )
    ORGANIZATION INDEX MONITORING;


Rem
Rem    PBANTIS        04/10/05 
Rem

 
-- Create the 10gR2 Backup and Recovery tables.
-- Table for storing list of configured recovery catalogs.
CREATE TABLE MGMT_RCVCAT_CONFIG (
  RCVCAT_GUID        RAW(16) DEFAULT SYS_GUID(),
  RCVCAT_VERSION     VARCHAR(15),
  RCVCAT_CONNECT_STR VARCHAR(1024),
  RCVCAT_USERNAME    VARCHAR(128),
  RCVCAT_PASSWORD    RAW(128),
  TARGET_GUID        RAW(16),
  UNIQUE(RCVCAT_GUID)   ) MONITORING;

-- Table for storing list of RMAN repositories for database targets.
CREATE TABLE MGMT_RCVCAT_REPOS (
  TARGET_GUID            RAW(16) NOT NULL,
  USE_RCVCAT             VARCHAR(4),
  RCVCAT_GUID            RAW(16),
  UNIQUE(TARGET_GUID)
) MONITORING;

-- Changes to the Backup/Recovery tables.
ALTER TABLE MGMT_BACKUP_CONFIGURATION
    MODIFY(TAPE_PARMS VARCHAR(1024));

-- Oracle Backup tables.
CREATE TABLE MGMT_OB_ADMIN_CLIENT_DB (
  OB_TARGET_GUID         RAW(16) NOT NULL UNIQUE,
  OB_CLIENT_HOST         VARCHAR(128),
  OB_ADMIN_HOST          VARCHAR(128) NOT NULL
) MONITORING;
CREATE TABLE MGMT_OB_ADMIN_HOSTS (
  OB_ADMIN_HOST          VARCHAR(128) NOT NULL UNIQUE,
  OB_PATH                VARCHAR(1024) NOT NULL,
  OB_USERNAME            VARCHAR(128),
  OB_PASSWORD            RAW(128)
) MONITORING;

-- configuration 
ALTER TABLE MGMT_HA_BACKUP
    ADD("SESSION_KEY" NUMBER,
    "SESSION_RECID" NUMBER,
    "SESSION_STAMP" NUMBER,
    "COMMAND_ID" VARCHAR2(33),
    "START_TIME" DATE,
    "TIME_TAKEN_DISPLAY" VARCHAR2(4000),
    "INPUT_TYPE" VARCHAR2(13),
    "OUTPUT_DEVICE_TYPE" VARCHAR2(17),
    "INPUT_BYTES_DISPLAY" VARCHAR2(4000),
    "OUTPUT_BYTES_DISPLAY" VARCHAR2(4000),
    "OUTPUT_BYTES_PER_SEC_DISPLAY" VARCHAR2(4000));


Rem
Rem    YSUN        04/10/06 
Rem


CREATE TABLE "MGMT_HA_CLS_INTR_CONN" (
  "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_HA_CLS_INTR_CONN0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
  "IC_NAME" VARCHAR2(50),
  "IC_NODE" VARCHAR2(100),
  "IC_SUBNET" VARCHAR2(16),
  "IC_IP" VARCHAR2(16),
  "IC_PUBLIC" VARCHAR2(10),
  CONSTRAINT "MGMT_HA_CLS_INTR_CONN_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "IC_NAME", "IC_NODE")
) MONITORING;

CREATE TABLE "MGMT_HA_RAC_INTR_CONN" (
  "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_HA_RAC_INTR_CONN0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
  "IC_NAME" VARCHAR2(50),
  "IC_IP" VARCHAR2(16),
  "IC_PUBLIC" VARCHAR2(10),
  "IC_SOURCE" VARCHAR2(100),
  CONSTRAINT "MGMT_HA_RAC_INTR_CONN_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "IC_NAME", "IC_IP")
) MONITORING;


Rem
Rem    RREILLY        04/10/06 
Rem


-- -----------------------------------------------
-- Create OSM Tables
-- -----------------------------------------------

-- OSM Disk Group Policy Table
CREATE TABLE "MGMT_OSM_DISK_GROUP_ECM" 
  (
    "ECM_SNAPSHOT_ID" RAW(16) 
         NOT NULL CONSTRAINT "MGMT_OSM_DISK_GROUP_ECM0" 
         REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
    "DISK_GROUP"   VARCHAR2(30),
    "PROBLEM_CODE" NUMBER(2),
    "VALUE1_N"     NUMBER(10),
    "VALUE2_N"     NUMBER(10),
    "VALUE3_S"     VARCHAR2(100),
    "VALUE4_S"     VARCHAR2(100),
    CONSTRAINT "MGMT_OSM_DISK_GROUP_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID",
                                                         "DISK_GROUP",
                                                         "PROBLEM_CODE")
  ) MONITORING;

ALTER TABLE MGMT_SQL_PLAN
DROP CONSTRAINT MGMT_SQL_PLAN_PK;

ALTER TABLE MGMT_SQL_PLAN
ADD CONSTRAINT MGMT_SQL_PLAN_PK PRIMARY KEY
(target_guid, snap_id, address, hash_value, plan_hash_value, id);

ALTER TABLE MGMT_SQL_SUMMARY
DROP CONSTRAINT MGMT_SQL_SUMMARY_PK;

ALTER TABLE MGMT_SQL_SUMMARY
ADD CONSTRAINT MGMT_SQL_SUMMARY_PK PRIMARY KEY
(target_guid, snap_id, address, hash_value, plan_hash_value, piece);

COMMIT;


-- -----------------------------------------------
-- Create or Replace OSM Types
-- -----------------------------------------------

create or replace type SMP_EMD_OSMSITEMAP_OBJ as OBJECT (
    TYPE_DISPLAY_NAME     VARCHAR2(256),
    LAST_UPDATED_TIME     VARCHAR2(4000),
    CURRENT_STATUS        INTEGER,
    AVAIL_PCT             NUMBER,
    START_TIMESTAMP       Date,
    INSTANCE_NAME         VARCHAR2(1024),
    INSTANCE_STARTTIME    DATE,
    INSTANCE_STARTSTR     VARCHAR2(1024),
    INSTANCE_VERSION      VARCHAR2(1024),
    ALERTLOG_TIMESTAMP    DATE,
    ALERTLOG_SEVERITY     NUMBER,
    HOST_NAME             VARCHAR2(64),   
    TARGET_GUID           VARCHAR2(32),
    META_VER              VARCHAR2(8),
    CAT_PROP_1            VARCHAR2(64),
    CAT_PROP_2            VARCHAR2(64),
    CAT_PROP_3            VARCHAR2(64),
    CAT_PROP_4            VARCHAR2(64),
    CAT_PROP_5            VARCHAR2(64)
);
/

-----------------------------------------------
-- Create or replace sitemap objects
-----------------------------------------------

create or replace type SMP_EMD_DBSITEMAP_OBJ as OBJECT (
    TYPE_DISPLAY_NAME     VARCHAR2(256),
    LAST_UPDATED_TIME     DATE,
    CURRENT_STATUS        INTEGER,
    AVAIL_PCT             NUMBER,
    START_TIMESTAMP       DATE,
    timeZone              VARCHAR2(64),
    INSTANCE_NAME         VARCHAR2(4000),
    INSTANCE_STARTTIME    DATE,
    INSTANCE_VERSION      VARCHAR2(4000),
    ORACLE_HOME           VARCHAR2(4000),
    CPU_COUNT             NUMBER,
    OPEN_MODE             VARCHAR2(64),
    SQL_COUNT             NUMBER,
    SQL_SEVERITY          NUMBER,
    TBSP_COUNT            NUMBER,
    TBSP_SEVERITY         NUMBER,
    ARCHAREA_MODE         NUMBER,
    ARCHAREA_PCTUSED      NUMBER,
    ARCHAREA_SEVERITY     NUMBER,
    DUMPAREA_PCTUSED      NUMBER,
    DUMPAREA_SEVERITY     NUMBER,
    ALERTLOG_TIMESTAMP    DATE,
    ALERTLOG_SEVERITY     NUMBER,
    HOST_NAME             VARCHAR2(64),
    TARGET_GUID           VARCHAR2(32),
    DUP_SQL_COUNT         NUMBER,
    RESPONSE_SEVERITY     NUMBER,
    TOP_SQL_COUNT         NUMBER,
    STG_PERF_COUNT        NUMBER,
    STG_PERF_SEVERITY     NUMBER,    
    ARCH_COUNT            NUMBER,
    BADARCH_COUNT         NUMBER,
    LISTENER_NAME         VARCHAR2(4000),
    MTTR                  NUMBER,
    ASM_TARGET_NAME       VARCHAR2(64)
);
/

create or replace type SMP_EMD_DBSITEMAP_COMMON_OBJ as OBJECT (
    TARGET_GUID           VARCHAR2(32),
    DB_SIZE               NUMBER,
    DG_ROLE               VARCHAR2(64),
    PRMY_EM_NAME          VARCHAR2(64),
    PRMY_TYPE             VARCHAR2(64),
    BACKUP_STATUS         VARCHAR2(64),
    BACKUP_TIMESTAMP      DATE,
    FLASHBACK_TIMESTAMP   DATE,
    RECOVERY_PERCENT      NUMBER,
    FLASH_RECOVERY_AREA   VARCHAR2(512),
    LOG_MODE              VARCHAR2(32),
    FLASHBACK_ON          VARCHAR2(32)
);
/