Rem drv:
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/config/config_tables.sql /st_emdbsa_11.2/3 2009/02/12 23:34:29 hpalitan Exp $
Rem
Rem config_tables.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem config_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem amahalin 01/28/09 - Database Vault Changes
Rem gallison 01/08/09 - Add supplemental logging
Rem pbantis 09/30/08 - Retain 11.1 fixes.
Rem rimmidi 07/29/08 - Code slap from 10205 to 11.2DBControl
Rem xshen 08/18/06 - V$PARAMETER.VALUE in 11g is of length 4000
Rem chyu 05/08/06 - RCU Compliant
Rem xshen 02/22/06 - change banner column
Rem pbantis 08/31/05 - Change FLASHBACK_ON size from 3 to 18.
Rem chyu 06/28/05 - New repmgr header impl
Rem rreilly 03/18/05 - remove dbrollup and controlfiles_size
Rem scgrover 02/02/05 - compress indexes
Rem xshen 01/13/05 - changing metrics
Rem rreilly 11/19/04 - add new seg management column
Rem rreilly 10/08/04 - bug 3941770 move mgmt_db_tablespaces_ecm
Rem used_size column to be last
Rem pbantis 09/15/04 - Update ha_backup.
Rem xshen 09/14/04 - bug 3883209 - make init table compressed iot
Rem rpinnama 06/12/04 - Remove SET ECHO
Rem ajdsouza 05/10/04 - add column os_storage_entity
Rem sbadrina 05/06/04 - add dbrollup and cfsize
Rem xshen 11/12/03 - remove rule fw backend
Rem xshen 10/03/03 - oracle_dbconfig and oracle_racconfig
Rem xshen 10/02/03 - rename snapshot type name
Rem xshen 08/11/03 - remove target assoc tables
Rem pbantis 07/21/03 - Remove old HA table
Rem skini 07/01/03 - Blocksize issues
Rem xshen 06/16/03 - add next extent column to tablespace
Rem xshen 05/28/03 - change err msg size
Rem xshen 05/19/03 - cutting over to snapshot framework
Rem lhan 04/18/03 -
Rem lhan 04/18/03 - bug 2877273 - add key to redoLog metric
Rem pbantis 03/20/03 - Add ha_rman_config
Rem pbantis 01/06/03 - Add High Availability tables
Rem xshen 09/24/02 - remove foreign key constraints on mgmt_targets
Rem xshen 09/18/02 - rule results stored as rule/target/value entries
Rem xshen 09/17/02 - config tables fk on delete cascade mgmt_targets
Rem xshen 09/16/02 - add target type, level for rule tables
Rem xshen 08/19/02 - support rule
Rem xshen 08/08/02 - remove compatibility metric table
Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/15/02 - Created
Rem
Rem------------------------------------------------------------------------------------------
Rem High Availability - RMAN Backup table
create table MGMT_HA_BACKUP (
TARGET_GUID RAW(16) not null,
COLLECTION_TIMESTAMP date default SYSDATE,
END_TIME DATE,
STATUS varchar2(23),
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),
constraint MGMT_HA_BACKUP_PK primary key (TARGET_GUID))
monitoring;
Rem------------------------------------------------------------------------------------------
Rem High Availability - Mean Time To Recover (MTTR) table
create table MGMT_HA_MTTR (
TARGET_GUID RAW(16) not null,
COLLECTION_TIMESTAMP date default SYSDATE,
ESTIMATED_MTTR number,
constraint MGMT_HA_MTTR_PK primary key (TARGET_GUID))
monitoring;
-- 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 Database Configuration for target type oracle_database and snapshot type oracle_dbconfig
-- 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.
--------------------------------------------
-- Drop tables
-- DROP TABLE "MGMT_DB_DBNINSTANCEINFO_ECM";
-- DROP TABLE "MGMT_DB_INIT_PARAMS_ECM";
-- DROP TABLE "MGMT_DB_CONTROLFILES_ECM";
-- DROP TABLE "MGMT_DB_REDOLOGS_ECM";
-- DROP TABLE "MGMT_DB_ROLLBACK_SEGS_ECM";
-- DROP TABLE "MGMT_DB_SGA_ECM";
-- DROP TABLE "MGMT_DB_LICENSE_ECM";
-- DROP TABLE "MGMT_DB_OPTIONS_ECM";
-- DROP TABLE "MGMT_DB_DATAFILES_ECM";
-- DROP TABLE "MGMT_DB_TABLESPACES_ECM";
-- DROP TABLE "MGMT_HA_INFO_ECM";
-- DROP TABLE "MGMT_HA_INIT_PARAMS_ECM";
-- DROP TABLE "MGMT_HA_FILES_ECM";
-- DROP TABLE "MGMT_HA_RMAN_CONFIG_ECM";
-- Create tables
CREATE TABLE "MGMT_DB_DBNINSTANCEINFO_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_DBNINSTANCEINFO_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"DATABASE_NAME" VARCHAR2(9),
"GLOBAL_NAME" VARCHAR2(4000),
"BANNER" VARCHAR2(80),
"HOST_NAME" VARCHAR2(64),
"INSTANCE_NAME" VARCHAR2(16),
"STARTUP_TIME" DATE,
"LOGINS" VARCHAR2(10),
"LOG_MODE" VARCHAR2(12),
"OPEN_MODE" VARCHAR2(10),
"DEFAULT_TEMP_TABLESPACE" VARCHAR2(30),
"CHARACTERSET" VARCHAR2(64),
"NATIONAL_CHARACTERSET" VARCHAR2(64),
"DV_STATUS_CODE" NUMBER(1,0),
CONSTRAINT "MGMT_DB_DBNINSTANCEINFO_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID")
) MONITORING;
CREATE TABLE "MGMT_DB_INIT_PARAMS_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_INIT_PARAMS_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"NAME" VARCHAR2(80),
"VALUE" VARCHAR2(4000),
"ISDEFAULT" VARCHAR2(9),
CONSTRAINT "MGMT_DB_INIT_PARAMS_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) ORGANIZATION INDEX
COMPRESS 1
OVERFLOW INCLUDING NAME
MONITORING;
CREATE TABLE "MGMT_DB_CONTROLFILES_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_CONTROLFILES_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"FILE_NAME" VARCHAR2(512),
"STATUS" VARCHAR2(10),
"CREATION_DATE" DATE,
"SEQUENCE_NUM" NUMBER,
"CHANGE_NUM" NUMBER,
"MOD_DATE" DATE,
"OS_STORAGE_ENTITY" VARCHAR2(512),
CONSTRAINT "MGMT_DB_CONTROLFILES_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "FILE_NAME") USING INDEX
(CREATE UNIQUE INDEX "MGMT_DB_CONTROLFILES_ECM_PK" ON "MGMT_DB_CONTROLFILES_ECM" ("ECM_SNAPSHOT_ID", "FILE_NAME") COMPRESS 1)
) MONITORING;
CREATE TABLE "MGMT_DB_REDOLOGS_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_REDOLOGS_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"GROUP_NUM" NUMBER,
"STATUS" VARCHAR2(16),
"MEMBERS" NUMBER,
"FILE_NAME" VARCHAR2(512),
"ARCHIVED" VARCHAR2(3),
"LOGSIZE" NUMBER,
"SEQUENCE_NUM" NUMBER,
"FIRST_CHANGE_SCN" NUMBER,
"OS_STORAGE_ENTITY" VARCHAR2(512),
"THREAD_NUM" NUMBER,
CONSTRAINT "MGMT_DB_REDOLOGS_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "GROUP_NUM", "FILE_NAME")
) MONITORING;
CREATE TABLE "MGMT_DB_ROLLBACK_SEGS_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_ROLLBACK_SEGS_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"ROLLNAME" VARCHAR2(64),
"STATUS" VARCHAR2(16),
"TABLESPACE_NAME" VARCHAR2(30),
"EXTENTS" NUMBER,
"ROLLSIZE" NUMBER,
"INITIAL_SIZE" NUMBER,
"NEXT_SIZE" NUMBER,
"MAXIMUM_EXTENTS" NUMBER,
"MINIMUM_EXTENTS" NUMBER,
"PCT_INCREASE" NUMBER,
"OPTSIZE" NUMBER,
"AVEACTIVE" NUMBER,
"WRAPS" NUMBER,
"SHRINKS" NUMBER,
"AVESHRINK" NUMBER,
"HWMSIZE" NUMBER,
CONSTRAINT "MGMT_DB_ROLLBACK_SEGS_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "ROLLNAME") USING INDEX
(CREATE UNIQUE INDEX "MGMT_DB_ROLLBACK_SEGS_ECM_PK" ON "MGMT_DB_ROLLBACK_SEGS_ECM" ("ECM_SNAPSHOT_ID", "ROLLNAME") COMPRESS 1)
) MONITORING;
CREATE TABLE "MGMT_DB_SGA_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_SGA_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"SGANAME" VARCHAR2(64),
"SGASIZE" NUMBER,
CONSTRAINT "MGMT_DB_SGA_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "SGANAME") USING INDEX
(CREATE UNIQUE INDEX "MGMT_DB_SGA_ECM_PK" ON "MGMT_DB_SGA_ECM" ("ECM_SNAPSHOT_ID", "SGANAME") COMPRESS 1)
) MONITORING;
CREATE TABLE "MGMT_DB_LICENSE_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_LICENSE_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"SESSIONS_MAX" NUMBER,
"SESSIONS_WARNING" NUMBER,
"SESSIONS_CURRENT" NUMBER,
"SESSIONS_HIGHWATER" NUMBER,
"USERS_MAX" NUMBER,
CONSTRAINT "MGMT_DB_LICENSE_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "SESSIONS_MAX")
) MONITORING;
CREATE TABLE "MGMT_DB_OPTIONS_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_OPTIONS_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"NAME" VARCHAR2(30),
"SELECTED" VARCHAR2(5),
CONSTRAINT "MGMT_DB_OPTIONS_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) MONITORING;
CREATE TABLE "MGMT_DB_TABLESPACES_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_TABLESPACES_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"TABLESPACE_NAME" VARCHAR2(30),
"CONTENTS" VARCHAR2(9),
"STATUS" VARCHAR2(10),
"EXTENT_MANAGEMENT" VARCHAR2(10),
"ALLOCATION_TYPE" VARCHAR2(10),
"LOGGING" VARCHAR2(10),
"TABLESPACE_SIZE" NUMBER,
"INITIAL_EXT_SIZE" NUMBER,
"NEXT_EXTENT" NUMBER,
"INCREMENT_BY" NUMBER,
"MAX_EXTENTS" NUMBER,
"TABLESPACE_USED_SIZE" NUMBER,
"SEGMENT_SPACE_MANAGEMENT" VARCHAR2(6),
"BLOCK_SIZE" NUMBER,
"MIN_EXTENTS" NUMBER,
"MIN_EXTLEN" NUMBER,
"BIGFILE" VARCHAR2(3),
CONSTRAINT "MGMT_DB_TABLESPACES_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "TABLESPACE_NAME") USING INDEX
(CREATE UNIQUE INDEX "MGMT_DB_TABLESPACES_ECM_PK" ON "MGMT_DB_TABLESPACES_ECM" ("ECM_SNAPSHOT_ID", "TABLESPACE_NAME") COMPRESS 1)
) MONITORING;
CREATE TABLE "MGMT_DB_DATAFILES_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_DB_DATAFILES_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"TABLESPACE_NAME" VARCHAR2(30),
"FILE_NAME" VARCHAR2(512),
"STATUS" VARCHAR2(9),
"FILE_SIZE" NUMBER,
"AUTOEXTENSIBLE" VARCHAR2(3),
"INCREMENT_BY" NUMBER,
"MAX_FILE_SIZE" NUMBER,
"OS_STORAGE_ENTITY" VARCHAR2(512),
CONSTRAINT "MGMT_DB_DATAFILES_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "TABLESPACE_NAME", "FILE_NAME") USING INDEX
(CREATE UNIQUE INDEX "MGMT_DB_DATAFILES_ECM_PK" ON "MGMT_DB_DATAFILES_ECM" ("ECM_SNAPSHOT_ID", "TABLESPACE_NAME", "FILE_NAME") COMPRESS 1)
) MONITORING;
CREATE TABLE "MGMT_HA_INFO_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_HA_INFO_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"DBID" NUMBER,
"LOG_MODE" VARCHAR2(12),
"FORCE_LOGGING" VARCHAR2(3),
"DATABASE_ROLE" VARCHAR2(16),
"FLASHBACK_ON" VARCHAR2(18),
"SUPPLEMENTAL_LOGGING" VARCHAR2(8),
CONSTRAINT "MGMT_HA_INFO_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID")
) MONITORING;
CREATE TABLE "MGMT_HA_INIT_PARAMS_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_HA_INIT_PARAMS_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"NAME" VARCHAR2(64),
"VALUE" VARCHAR2(512),
CONSTRAINT "MGMT_HA_INIT_PARAMS_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) MONITORING;
CREATE TABLE "MGMT_HA_FILES_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_HA_FILES_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"NAME" VARCHAR2(64),
"TOTALSIZE" NUMBER,
CONSTRAINT "MGMT_HA_FILES_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) MONITORING;
CREATE TABLE "MGMT_HA_RMAN_CONFIG_ECM" (
"ECM_SNAPSHOT_ID" RAW(16) NOT NULL CONSTRAINT "MGMT_HA_RMAN_CONFIG_ECM0" REFERENCES "MGMT_ECM_GEN_SNAPSHOT" ("SNAPSHOT_GUID") ON DELETE CASCADE,
"NAME" VARCHAR2(65),
"VALUE" VARCHAR2(1025),
CONSTRAINT "MGMT_HA_RMAN_CONFIG_ECM_PK" PRIMARY KEY ("ECM_SNAPSHOT_ID", "NAME")
) MONITORING;
-- End of table (re)creation for metadata Database Configuration
SET DEFINE ON