Rem drv:
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/rac/rac_tables.sql /st_emdbsa_11.2/9 2010/01/04 10:35:46 sksantha Exp $
Rem
Rem rac_tables.sql
Rem
Rem Copyright (c) 2004, 2010, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem rac_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem pardutta 10/28/09 - Add database type column to mgmt_rac_services.
Rem ajdsouza 03/25/09 - Bug fix# 7321196
Rem pardutta 03/18/09 - mgmt_rac_service.server_group size increased to
Rem 1024
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/20/08 - change mgmt_config_activities index
Rem shasingh 09/17/08 - added index on taget_guid column of
Rem mgmt_config_activities table
Rem sadattaw 11/26/07 - adding server groups, cardinality columns to
Rem mgmt_rac_services table
Rem shasingh 08/09/07 - XbranchMerge
Rem shasingh_11g_19509a_targets_discovery from main
Rem shasingh 12/11/06 - Added reconfig activity repository table
Rem sadattaw 11/22/06 - renaming db name to db_unique name for
Rem mgmt_rac_services table
Rem sadattaw 10/04/06 - add commands for rac_services metric tables
Rem chyu 06/28/05 - New repmgr header impl
Rem xuliu 01/05/05 - xuliu_bug-4092412_main
Rem xuliu 12/28/04 - Created
Rem
-----------------------------
-- Cluster Interconnects
------------------------------
-- Do not use substitution variables in this script so that there is no
-- additional restrictions on the string values that are being inserted (such as ampersand
-- being interpreted specially by SQLPlus).
SET DEFINE OFF
--------------------------------------------
-- Table (re)creation statements based on metadata Cluster Interconnects for target type cluster and snapshot type ha_cls_intrconn
-- 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_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;
-- End of table (re)creation for metadata Cluster Interconnects
------------------------
-- Rac Instance Interconnects
-----------------------
--------------------------------------------
-- Table (re)creation statements based on metadata Rac Instance Interconnects for target type oracle_database and snapshot type ha_rac_intrconn
-- 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_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;
-- End of table (re)creation for metadata Rac Instance Interconnects
--------------------------------------------
-- 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),
"CLUSTER_NAME" VARCHAR2(64),
"SERVER_GROUP" VARCHAR2(1024),
"RESOURCE_NAME" VARCHAR2(64),
"SERVICE_CENTRIC_TYPE" VARCHAR2(30),
"SERVICE_CARDINALITY" NUMBER,
"DATABASE_TYPE" VARCHAR2(64),
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
---------------------------------------------------
-- Table contains the successful reconfiguration activity record
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) 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;
---- end table creation
--- 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
SET DEFINE ON