Rem drv: <migrate type="schema_upgrade" version="11.2.0.0"/>
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/rac/rac_schema_upgrade.sql /st_emdbsa_11.2/8 2009/04/08 18:20:31 ajdsouza Exp $
Rem
Rem rac_schema_upgrade.sql
Rem
Rem Copyright (c) 2008, 2009, Oracle and/or its affiliates. 
Rem All rights reserved. 
Rem
Rem    NAME
Rem      rac_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    ajdsouza    03/25/09 - Bug fix# 7321196
Rem    shasingh    11/17/08 - lrg:3697066
Rem    shasingh    11/09/08 - added new column in mgmt_config_activities table
Rem                           to capture failed job as well as info
Rem    rsamaved    10/17/08 - change mgmt_config_activites constraint 
Rem    shasingh    09/17/08 - added index on taget_guid column of
Rem                           mgmt_config_activities table
Rem    shasingh    06/10/08 - lrg 3426315
Rem    sadattaw    05/05/08 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100


-- add new columns for TB services to the mgmt_rac_services 
ALTER TABLE MGMT_RAC_SERVICES ADD (
  "CLUSTER_NAME" VARCHAR2(64),
  "SERVER_GROUP" VARCHAR2(1024),
  "RESOURCE_NAME" VARCHAR2(64),
  "SERVICE_CENTRIC_TYPE" VARCHAR2(30),
  "SERVICE_CARDINALITY" NUMBER
);

CREATE OR REPLACE TYPE MGMT_SERV_PERF_INFO_TYPE IS object
   (dbname          VARCHAR2(64),
    srvnm         VARCHAR2(64),
    colltime      VARCHAR2(64),
    respval       VARCHAR2(64),
    cpuload        VARCHAR2(64),
    statval       VARCHAR2(64),
    runval        VARCHAR2(256),
    minresp       VARCHAR2(64),
    mininst       VARCHAR2(64),
    maxresp       VARCHAR2(64),
    maxinst       VARCHAR2(64),
    compkey       VARCHAR2(64)
   );
/

CREATE OR REPLACE TYPE MGMT_SERV_PERF_INFO_ARRAY_TYPE IS TABLE OF MGMT_SERV_PERF_INFO_TYPE;
/

CREATE TABLE MGMT_CONFIG_ACTIVITIES (
    "TXN_ID"   RAW(16) NOT NULL,
    "TARGET_GUID"   RAW(16) NOT NULL,
    "CLUSTER_NAME" VARCHAR2(64),
    "STATUS_CODE" NUMBER, 
    "CONFIG_TYPE" VARCHAR2(32), 
    "OCCURENCE_TIME" TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP,
    "HOST_NAME"     VARCHAR2(64),
    "SOURCE_HOST_NAME"  VARCHAR2(64),
    CONSTRAINT "MGMT_CONFIG_ACTIVITIES_PK" PRIMARY KEY ("TARGET_GUID","TXN_ID")
) MONITORING;

CREATE TABLE MGMT_FAILED_CONFIG_ACTIVITIES (
    "TXN_ID"      RAW(16) NOT NULL,
    "TARGET_GUID" RAW(16) NOT NULL,
    "PARAM_NAME"  VARCHAR2(64) NOT NULL,
    "PARAM_VALUE" VARCHAR2(64),
    CONSTRAINT "MGMT_FAILED_CONFIG_ACT_PK" PRIMARY KEY ("TARGET_GUID", "TXN_ID","PARAM_NAME")
 )  MONITORING;


--- Add temporary tables for get_cluster_db_list
Rem    ajdsouza    03/25/09 - Bug fix# 7321196


CREATE GLOBAL TEMPORARY TABLE 
mgmt_v_cluster_member_list
(
  member_target_guid   RAW(16),
  member_target_type   VARCHAR2(255),
  host_name            VARCHAR2(255)
)
ON COMMIT PRESERVE ROWS;

CREATE INDEX mgmt_v_cluster_mem_IDX1 ON mgmt_v_cluster_member_list (host_name);


CREATE GLOBAL TEMPORARY TABLE 
mgmt_v_rac_member_list
(
  composite_target_guid RAW(16),
  target_guid   RAW(16),
  target_type   VARCHAR2(255),
  host_name     VARCHAR2(255),
  version       VARCHAR2(255)
)
ON COMMIT PRESERVE ROWS;

CREATE INDEX mgmt_v_rac_men_IDX1 ON mgmt_v_rac_member_list (composite_target_guid);


CREATE GLOBAL TEMPORARY TABLE 
mgmt_v_rac_assoc_member_list
(
  composite_target_guid RAW(16),
  target_guid   RAW(16),
  target_type   VARCHAR2(255),
  host_name     VARCHAR2(255)
)
ON COMMIT PRESERVE ROWS;


CREATE GLOBAL TEMPORARY TABLE 
mgmt_v_cluster_rac_pol_detail
(
  composite_target_guid          RAW(16),
  information_count              NUMBER,
  warning_count                  NUMBER,
  critical_count                 NUMBER,
  compliance_score               NUMBER
)
ON COMMIT PRESERVE ROWS;

CREATE INDEX mgmt_v_cluster_rac_pol_IDX1 ON mgmt_v_cluster_rac_pol_detail (composite_target_guid);

---- end table creation