Rem drv:
Rem
Rem $Header: provision_tables.sql 09-aug-2006.06:35:51 rattipal Exp $
Rem
Rem provision_tables.sql
Rem
Rem Copyright (c) 2004, 2006, Oracle. All rights reserved.
Rem
Rem NAME
Rem provision_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem rattipal 08/09/06 - Backport rattipal_typo_fix from main
Rem rattipal 07/31/06 - To change UNIQUE key constraint for MGMT_PROV_STAGED_COMPS
Rem prayarot 07/07/05 - To enforce UNIQUE key constraint for
Rem MGMT_PROV_STAGED_COMPS
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem rkaggarw 06/04/05 - added member_component_urn in suiteinstance
Rem members table
Rem prayarot 05/26/05 - Assignment Subtype is added
Rem rkaggarw 05/19/05 - added SuiteInstance related tables
Rem rmadampa 05/11/05 - added Cluster related tables
Rem pshroff 04/30/05 - changing property type to CLOB from Blob
Rem rmadampa 04/27/05 - data model enhancements 1
Rem ashwikum 04/20/05 - Adding Unique constraint to admin tables
Rem ashwikum 04/15/05 - Modifying Net Config Table structure
Rem jhazra 04/14/05 - Hardware Specific properties
Rem pshroff 03/16/05 - to fix issues in bug#4221658
Rem gsbhatia 02/13/05 - updating repmgr header
Rem gsbhatia 02/09/05 - updating repmgr header
Rem gsbhatia 02/07/05 - updating repmgr header
Rem ashwikum 02/09/05 - Add tables for network config
Rem pshroff 02/01/05 - adding label/desciption field for assignment
Rem ktlaw 01/11/05 - add repmgr header
Rem prayarot 12/21/04 - Added metric collection table
Rem pshroff 11/26/04 - adding bootserverurn to assignment table
Rem prayarot 11/19/04 - table name modifed
Rem prayarot 11/17/04 - to add stage & admin tables
Rem pshroff 11/10/04 - Modifying Assignment Table for Suite Assignment
Rem pshroff 11/05/04 - Adding STATUS Column in ASSIGNMENT Table
Rem pshroff 10/26/04 - Modified DB Schema for Deafult-Image
Rem modification
Rem pshroff 10/18/04 - Created
Rem
Rem
Rem This script requires the following arguments
Rem 1. Name of the ecm_depot tablespace where all LOBs are stored
Rem
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"
@&EM_SQL_ROOT/core/latest/ecm/ecm_defines.sql
CREATE TABLE MGMT_PROV_TGT_STATUS
(
PROV_TGT_GUID RAW(16) NOT NULL,
PROV_TARGET_TYPE VARCHAR2(30),
CURRENT_ASN_GUID RAW(16) DEFAULT NULL,
LAST_SUC_ASN_GUID RAW(16) DEFAULT NULL,
COMPONENT_URN VARCHAR2(255) DEFAULT NULL,
NETWORK_URN VARCHAR2(255),
STATUS VARCHAR2(30),
CONSTRAINT MGMT_PROV_TGT_STATUS_PK PRIMARY KEY(PROV_TGT_GUID)
) MONITORING;
CREATE TABLE MGMT_PROV_HARDWARE
(
HW_GUID RAW(16) NOT NULL,
NAME VARCHAR2(255),
DESCRIPTION VARCHAR2(255),
HOSTNAME VARCHAR2(255) DEFAULT NULL,
NEW_HOSTNAME VARCHAR2(255) DEFAULT NULL,
MAC_ADDRESS1 VARCHAR2(30) ,
MAC_ADDRESS2 VARCHAR2(30) ,
MAC_ADDRESS3 VARCHAR2(30) ,
MAC_ADDRESS4 VARCHAR2(30) ,
INTERFACE_NAME1 VARCHAR2(128),
INTERFACE_NAME2 VARCHAR2(128),
INTERFACE_NAME3 VARCHAR2(128),
INTERFACE_NAME4 VARCHAR2(128),
SERIAL_NUMBER VARCHAR2(255) DEFAULT NULL,
RF_ID VARCHAR2(255) DEFAULT NULL,
PURPOSE VARCHAR2(30),
CONSTRAINT MGMT_PROV_HARDWARE_PK PRIMARY KEY(HW_GUID)
) MONITORING;
CREATE TABLE MGMT_PROV_CLUSTER
(
CLUSTER_GUID RAW(16) NOT NULL,
NAME VARCHAR2(255) NOT NULL,
DESCRIPTION VARCHAR2(255) DEFAULT NULL,
IMAGE_CLUSTER_URN VARCHAR2(255),
PURPOSE VARCHAR2(30),
CONSTRAINT MGMT_PROV_CLUSTER_PK PRIMARY KEY(CLUSTER_GUID),
CONSTRAINT MGMT_PROV_CLUSTER_UNIQ UNIQUE (NAME)
) MONITORING;
CREATE TABLE MGMT_PROV_CLUSTER_NODES
(
NODE_GUID RAW(16) NOT NULL,
CLUSTER_GUID RAW(16) NOT NULL,
STATUS VARCHAR2(30),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
PROV_ASN_GUID RAW(16) DEFAULT NULL,
CONSTRAINT MGMT_PROV_CLUS_NODES_PK PRIMARY KEY(NODE_GUID, CLUSTER_GUID)
) MONITORING;
CREATE TABLE MGMT_PROV_SUITE_INSTANCE
(
SUITE_INST_GUID RAW(16) NOT NULL,
NAME VARCHAR2(255) NOT NULL,
DESCRIPTION VARCHAR2(255) DEFAULT NULL,
SUITE_URN VARCHAR2(255),
PURPOSE VARCHAR2(30),
CONSTRAINT MGMT_PROV_SUITE_INSTANCE_PK PRIMARY KEY(SUITE_INST_GUID),
CONSTRAINT MGMT_PROV_SUITE_INSTANCE_UNIQ UNIQUE (NAME)
) MONITORING;
CREATE TABLE MGMT_PROV_SUITE_INST_MEMBERS
(
MEMBER_GUID RAW(16) NOT NULL,
SUITE_INST_GUID RAW(16) NOT NULL,
MEMBER_TYPE VARCHAR2(20),
MEMBER_COMPONENT_URN VARCHAR2(255),
STATUS VARCHAR2(30),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
PROV_ASN_GUID RAW(16) DEFAULT NULL,
CONSTRAINT MGMT_PROV_SUITE_MEMBERS_PK PRIMARY KEY(MEMBER_GUID, SUITE_INST_GUID)
) MONITORING;
CREATE TABLE MGMT_PROV_OPERATION
(
OP_GUID RAW(16) NOT NULL,
CREATION_TIME TIMESTAMP WITH TIME ZONE,
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
FRACTION_COMPLETE FLOAT,
STATUS_MSG VARCHAR2(255),
OP_TYPE VARCHAR2(30),
JOB_ID VARCHAR2(255) default null,
CONSTRAINT MGMT_PROV_OPERATION_PK PRIMARY KEY (OP_GUID)
) MONITORING;
CREATE TABLE MGMT_PROV_ASSIGNMENT
(
ASSIGNMENT_GUID RAW(16) NOT NULL,
NAME VARCHAR2(255) DEFAULT NULL,
DESCRIPTION VARCHAR2(255) DEFAULT NULL,
ASSIGNMENT_TYPE VARCHAR2(30),
ASSIGNMENT_SUBTYPE VARCHAR2(30) DEFAULT NULL,
STATUS VARCHAR2(30) ,
USERNAME VARCHAR2(255),
COMPONENT_URN VARCHAR2(255),
NETWORK_URN VARCHAR2(255),
BOOT_SERVER_URN VARCHAR2(255),
STAGE_URN VARCHAR2(255),
START_TIME TIMESTAMP WITH TIME ZONE,
PROPERTIES CLOB DEFAULT EMPTY_CLOB(),
STAGE_USERNAME VARCHAR2(255),
STAGE_PASSWORD VARCHAR2(255),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
TARGET_RESET_TIME NUMBER,
CONSTRAINT MGMT_PROV_ASSIGNMENT_PK PRIMARY KEY (ASSIGNMENT_GUID)
) MONITORING
LOB(PROPERTIES) STORE AS ( TABLESPACE &EM_ECM_DEPOT_TABLESPACE );
CREATE TABLE MGMT_PROV_ASN_DEPENDENCIES
(
PARENT_ASN_GUID RAW(16) NOT NULL,
CHILD_ASN_GUID RAW(16) NOT NULL,
DEP_ON_ASN_GUID RAW(16)
)MONITORING;
CREATE TABLE MGMT_PROV_ASN_TARGETS
(
ASSIGNMENT_GUID RAW(16) NOT NULL,
PROV_TGT_GUID RAW(16) NOT NULL,
PROPERTIES BLOB DEFAULT EMPTY_BLOB(),
STATUS VARCHAR2(30) ,
CONSTRAINT MGMT_PROV_ASN_TARGETS_PK PRIMARY KEY (ASSIGNMENT_GUID,
PROV_TGT_GUID)
) MONITORING
LOB(PROPERTIES) STORE AS ( TABLESPACE &EM_ECM_DEPOT_TABLESPACE );
CREATE TABLE MGMT_PROV_HISTORY
(
PROV_TGT_GUID RAW(16) NOT NULL,
OP_GUID RAW(16) NOT NULL,
ASSIGNMENT_GUID RAW(16),
HOSTNAME VARCHAR2(255)
) MONITORING;
CREATE TABLE MGMT_PROV_BOOTSERVER
(
BOOT_GUID RAW(16) NOT NULL,
BOOT_HOST_NAME VARCHAR2(255),
BOOT_CONFIG_DIR VARCHAR2(255),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
CONSTRAINT MGMT_PROV_BOOTSERVER_PK PRIMARY KEY (BOOT_GUID),
CONSTRAINT MGMT_PROV_BOOTSERVER_UNIQ UNIQUE (BOOT_HOST_NAME , BOOT_CONFIG_DIR)
) MONITORING;
CREATE TABLE MGMT_PROV_DEFAULT_IMAGE
(
DEFAULT_GUID RAW(16) NOT NULL,
NAME VARCHAR2(255) DEFAULT NULL,
DESCRIPTION VARCHAR2(255) DEFAULT NULL,
IP_ADDRESS_PREFIX VARCHAR2(255),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
CONSTRAINT MGMT_PROV_DEFAULT_IMAGE_PK PRIMARY KEY (DEFAULT_GUID),
CONSTRAINT MGMT_PROV_DEFAULT_IMAGE_UNIQ UNIQUE (IP_ADDRESS_PREFIX)
) MONITORING;
CREATE TABLE MGMT_PROV_RPM_REP
(
RPM_GUID RAW(16) NOT NULL,
RPM_NAME VARCHAR2(255),
RPM_DIR VARCHAR2(255),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
CONSTRAINT MGMT_PROV_RPM_REP_PK PRIMARY KEY (RPM_GUID),
CONSTRAINT MGMT_PROV_RPM_REP_UNIQ UNIQUE (RPM_NAME, RPM_DIR)
) MONITORING;
CREATE TABLE MGMT_PROV_STAGING_DIRS
(
STAGE_GUID RAW(16) NOT NULL,
STAGE_SERVER_HOSTNAME VARCHAR2(255),
NFS_EXPOSED_DIR VARCHAR2(255),
BASE_URL VARCHAR2(255),
SIZE_LIMIT FLOAT,
CURRENT_SIZE FLOAT,
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
CONSTRAINT MGMT_PROV_STAGING_DIRS_PK PRIMARY KEY (STAGE_GUID),
CONSTRAINT MGMT_PROV_STAGING_DIRS_UNIQ UNIQUE (STAGE_SERVER_HOSTNAME , NFS_EXPOSED_DIR)
) MONITORING;
CREATE TABLE MGMT_PROV_STAGED_COMPS
(
STAGE_GUID RAW(16) NOT NULL,
COMP_URN VARCHAR2(255),
COMP_DIR VARCHAR2(255),
CONSTRAINT MGMT_PROV_STAGED_COMPS_UNIQ UNIQUE (COMP_URN , STAGE_GUID)
) MONITORING;
CREATE TABLE MGMT_PROV_NET_CONFIG
(
NET_CONFIG_GUID RAW(16),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
NET_CONFIG_NAME VARCHAR2(255),
NET_DOMAIN_NAME VARCHAR2(255),
NET_SUBNET_MASK VARCHAR2(255),
NET_GATEWAY_ADDRS VARCHAR2(512),
NET_DNS_ADDRS VARCHAR2(512),
CONSTRAINT MGMT_PROV_NET_CONFIG_PK PRIMARY KEY (NET_CONFIG_GUID),
CONSTRAINT MGMT_PROV_NET_CONFIG_UK UNIQUE (NET_CONFIG_NAME)
) MONITORING;
CREATE TABLE MGMT_PROV_IP_RANGE
(
IP_RANGE_GUID RAW(16),
LAST_MODIFIED_TIME TIMESTAMP WITH TIME ZONE,
IP_RANGE_FIRST VARCHAR2(255),
IP_RANGE_LAST VARCHAR2(255),
IP_RANGE_COUNT INTEGER,
IP_RANGE_NAME_PATTERN VARCHAR2(255),
IP_RANGE_START_VALUE INTEGER,
IP_RANGE_STATE VARCHAR2(32),
NET_CONFIG_GUID RAW(16),
CONSTRAINT MGMT_PROV_IP_RANGE_PK PRIMARY KEY (IP_RANGE_GUID),
CONSTRAINT MGMT_PROV_IP_RANGE_FK FOREIGN KEY (NET_CONFIG_GUID)
REFERENCES MGMT_PROV_NET_CONFIG(NET_CONFIG_GUID)
ON DELETE CASCADE,
CONSTRAINT MGMT_PROV_IP_RANGE_POS CHECK (IP_RANGE_COUNT >= 0)
) MONITORING;
CREATE TABLE MGMT_PROV_IP_RESERVED
(
IP_ADDR VARCHAR2(255),
IP_OWNER_URN VARCHAR2(255),
IP_RANGE_GUID RAW(16),
HOSTNAME VARCHAR2(255),
NET_CONFIG_GUID RAW(16),
CONSTRAINT MGMT_PROV_IP_RESERVED_PK PRIMARY KEY (IP_ADDR),
CONSTRAINT MGMT_PROV_IP_RESERVED_FK1 FOREIGN KEY (IP_RANGE_GUID)
REFERENCES MGMT_PROV_IP_RANGE(IP_RANGE_GUID)
ON DELETE CASCADE,
CONSTRAINT MGMT_PROV_IP_RESERVED_FK2 FOREIGN KEY (NET_CONFIG_GUID)
REFERENCES MGMT_PROV_NET_CONFIG(NET_CONFIG_GUID)
ON DELETE CASCADE,
CONSTRAINT MGMT_PROV_IP_RESERVED_UNQ UNIQUE(HOSTNAME)
) MONITORING;
CREATE TABLE MGMT_PROV_COLLECTION
(
COLLECTED_TIME VARCHAR2(255)
) MONITORING;
show errors;