Rem drv: Rem Rem $Header: rac_schema_upgrade.sql 10-jun-2008.03:50:10 shasingh Exp $ Rem Rem rac_schema_upgrade.sql Rem Rem Copyright (c) 2007, 2008, Oracle. All rights reserved. Rem Rem NAME Rem rac_schema_upgrade.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem shasingh 06/10/08 - lrg 3426315 Rem sadattaw 05/05/08 - Rem dchakumk 03/10/08 - XbranchMerge dchakumk_6752_xbmsrc from Rem st_emdbsa_11.1 Rem sadattaw 11/26/07 - Rem shasingh 08/09/07 - XbranchMerge Rem shasingh_11g_19509a_targets_discovery from main Rem sadattaw 03/23/07 - adding keyval to service alert and problem service types Rem ajdsouza 02/08/07 - bug fix 5647975,5848019, Rem added interconnect related changes Rem from 10.2.0.4 Rem rsamaved 02/02/07 - adding types created for service queries Rem sadattaw 01/16/07 - schema upgrade script for racdb Rem sadattaw 01/16/07 - Created Rem -------------------------------------------- -- Table (re)creation statements based on metadata Cluster Managaged Services for target type cluster and snapshot type mgmt_rac_services -- Please REVIEW the table (re)creation statements below and modify them if necessary. Examples: -- (1) You meant for a TABLE tag in the metadata to represent a view. Then, remove the corresponding drop-table / create-table statements. -- (2) Generated PRIMARY KEY constraints are supposed to be uniqueness constraints instead. -- (3) You want to impose additional constraints on the tables. -------------------------------------------- -- Create tables CREATE TABLE MGMT_RAC_SERVICES ( "ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_RAC_SERVICES0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE, "ECM_DATA_VERSION" NUMBER , "IVID" RAW(16) DEFAULT SYS_GUID() NOT NULL , "DATABASE_UNIQUE_NAME" VARCHAR2(30), "SERVICE_NAME" VARCHAR2(30), "SERVICE_TYPE" VARCHAR2(30), "ENABLED" VARCHAR2(30), "TAFPOLICY" VARCHAR2(30), "PREFERRED_INSTANCES" VARCHAR2(1024), "AVAILABLE_INSTANCES" VARCHAR2(1024), "RUNNING_INSTANCES" VARCHAR2(1024), CONSTRAINT "MGMT_RAC_SERVICES_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "ECM_DATA_VERSION", "DATABASE_UNIQUE_NAME", "SERVICE_NAME") ) MONITORING; -- End of table (re)creation for metadata Cluster Managaged Services CREATE OR REPLACE TYPE MGMT_SERV_ALERT_INFO_TYPE IS OBJECT (srvnm VARCHAR2(64), severityCode NUMBER, metricnm VARCHAR2(64), metriccol VARCHAR2(64), alerttime DATE, target_name VARCHAR2(64), target_type VARCHAR2(64), metricval VARCHAR2(256), keyval VARCHAR2(256) ); / CREATE OR REPLACE TYPE MGMT_SERV_ALERT_ARRAY_TYPE IS TABLE OF MGMT_SERV_ALERT_INFO_TYPE; / CREATE OR REPLACE TYPE MGMT_PROBLEM_SERV_INFO_TYPE IS OBJECT (srvnm VARCHAR2(64), srvstat VARCHAR2(32), preflist VARCHAR2(1024), runlist VARCHAR2(1024), alertstatval VARCHAR2(64), severityCode NUMBER, metricnm VARCHAR2(64), metriccol VARCHAR2(64), alerttime DATE, target_name VARCHAR2(64), target_type VARCHAR2(64), metricval VARCHAR2(256), keyval VARCHAR2(256) ); / CREATE OR REPLACE TYPE MGMT_PROBLEM_SERV_ARRAY_TYPE IS TABLE OF MGMT_PROBLEM_SERV_INFO_TYPE; / Rem Rem Cluster and database Interconnect related types Rem CREATE OR REPLACE TYPE mgmt_cls_member AS OBJECT ( composite_target_guid RAW(16), composite_target_name VARCHAR2(256), composite_target_type VARCHAR2(256), member_target_guid RAW(16), member_target_name VARCHAR2(256), member_target_type VARCHAR2(256) ) / CREATE OR REPLACE TYPE mgmt_cls_member_table AS TABLE OF mgmt_cls_member / CREATE OR REPLACE TYPE mgmt_cls_host_ic AS OBJECT ( composite_target_guid RAW(16), composite_target_name VARCHAR2(256), composite_target_type VARCHAR2(256), member_target_guid RAW(16), member_target_name VARCHAR2(256), member_target_type VARCHAR2(256), ic_name VARCHAR2(256), totrate NUMBER, toterr NUMBER, inrate NUMBER, collection_timestamp DATE ) / CREATE OR REPLACE TYPE mgmt_cls_host_ic_table AS TABLE OF mgmt_cls_host_ic / CREATE OR REPLACE TYPE mgmt_cls_ic AS OBJECT ( CLUSTER_NAME VARCHAR2(256), HOST_NAME VARCHAR2(256), HOST_STATUS NUMBER, IF_NAME VARCHAR2(256), IF_SUBNET VARCHAR2(16), IF_PUBLIC VARCHAR2(10), TOTRATE_5MIN NUMBER, TOTERR_5MIN NUMBER, INRATE_5MIN NUMBER, CURR_WARNING NUMBER, CURR_CRITICAL NUMBER, LATEST_COLLECTION_TIMESTAMP DATE ) / CREATE OR REPLACE TYPE mgmt_cls_ic_table AS TABLE OF mgmt_cls_ic / CREATE OR REPLACE TYPE mgmt_racdb_ic AS OBJECT ( ic_name VARCHAR2(256), host_name VARCHAR2(256), db_target_guid RAW(16), sid VARCHAR2(256), ic_ip VARCHAR2(256), ic_public VARCHAR2(256), ic_source VARCHAR2(256), db_tgt_name VARCHAR2(256), inst_tgt_name VARCHAR2(256), racdb_guid RAW(16) ) / CREATE OR REPLACE TYPE mgmt_racdb_ic_table AS TABLE OF mgmt_racdb_ic / CREATE OR REPLACE TYPE mgmt_racdb_icstat AS OBJECT ( CLUSTER_NAME VARCHAR2(1024), DB_TARGET VARCHAR2(256), INSTANCE_TARGET VARCHAR2(256), INSTANCE_STATUS NUMBER, DB_NAME VARCHAR2(1024), SID VARCHAR2(1024), IF_NAME VARCHAR2(256), HOST_NAME VARCHAR2(256), IF_IP VARCHAR2(256), IF_PUBLIC VARCHAR2(256), IF_SOURCE VARCHAR2(256), XFERRATE_5MIN NUMBER, CURR_WARNING NUMBER, CURR_CRITICAL NUMBER, LATEST_COLLECTION_TIMESTAMP DATE ) / CREATE OR REPLACE TYPE mgmt_racdb_icstat_table AS TABLE OF mgmt_racdb_icstat / Rem Rem Interface Stats types Rem CREATE OR REPLACE TYPE mgmt_racdb_tb_metric AS OBJECT ( target_guid RAW(16), key_value VARCHAR2(256), value NUMBER, collection_timestamp DATE ) / CREATE OR REPLACE TYPE mgmt_racdb_tb_metric_table AS TABLE OF mgmt_racdb_tb_metric / CREATE OR REPLACE TYPE mgmt_racdb_interface_stats AS OBJECT ( tguid RAW(16), hostname VARCHAR2(256), targetname VARCHAR2(256), ifname VARCHAR2(256), totrate NUMBER, toterr NUMBER, inrate NUMBER, collection_timestamp DATE ) / CREATE OR REPLACE TYPE mgmt_racdb_iface_stats_table AS TABLE OF mgmt_racdb_interface_stats / -- added types for getting cluster status CREATE OR REPLACE TYPE mgmt_cluster_collection_object AS OBJECT ( target_guid RAW(16), target_name VARCHAR2(256), target_type VARCHAR2(256), key_value VARCHAR2(256), value NUMBER, collection_timestamp DATE ) / CREATE OR REPLACE TYPE mgmt_cluster_collection_table AS TABLE OF mgmt_cluster_collection_object / CREATE OR REPLACE TYPE mgmt_cluster_status_object AS OBJECT ( target_guid RAW(16), target_name VARCHAR2(256), target_type VARCHAR2(256), key_value VARCHAR2(256), collection_timestamp DATE, status NUMBER, failed_count NUMBER ) / CREATE OR REPLACE TYPE mgmt_cluster_status_table AS TABLE OF mgmt_cluster_status_object /