Rem drv:
Rem
Rem $Header: blackout_tables.sql 04-jan-2008.03:05:29 smudumba Exp $
Rem
Rem blackout_tables.sql
Rem
Rem Copyright (c) 2002, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem blackout_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem smudumba 12/27/07 - Blackout Reasons NLS
Rem sbhagwat 09/19/06 - Retroactive Blackout
Rem rkpandey 08/02/06 - Backport rkpandey_fix_host_notif from main
Rem rkpandey 03/24/06 - Fix comments on MGMT_BLACKOUT_FLAT_TARGETS
Rem rkpandey 07/20/06 - Backport rkpandey_bug-5061352 from main
Rem gsbhatia 06/26/05 - New repmgr header impl
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 skini 01/13/05 - PK constraint for reasons table
Rem ktlaw 01/11/05 - add repmgr header
Rem dcawley 07/07/04 - Increase user name size
Rem skini 12/12/03 - Fix bug 3300351
Rem skini 12/05/03 - Add MGMT_BLACKOUT_PROXY_TARGETS
Rem skini 09/25/03 - Convert repos columns to utc columns
Rem skini 08/28/03 - Add occurrence_number column to bo windows table
Rem skini 08/19/03 - Add unique key
Rem skini 08/19/03 - Add blackout occurrence, history table
Rem skini 06/18/03 - Add MONITORING clause
Rem rpinnama 02/27/03 - Parameterize the ECM depot table space
Rem skini 12/09/02 - Add status to primary key
Rem skini 11/24/02 - Add repos timestamps to windows table
Rem skini 11/12/02 - Add status to blackout window table
Rem skini 11/06/02 - Remove primary key constraint from BW table
Rem skini 09/18/02 - blackout windows
Rem kduvvuri 09/04/02 - update the comments for MGMT_BLACKOUT_STATE w.r.t blackout_code and target_status
Rem skini 08/25/02 - Add edit_state to flat targets table
Rem skini 08/20/02 - New blackout states
Rem skini 08/02/02 - Move job_ids to blackouts table
Rem skini 07/26/02 - Change schedule table to refer to blackouts
Rem skini 07/25/02 - Change schedule table definition
Rem skini 07/23/02 - Handle overlapping blackouts
Rem skini 07/17/02 - Continue blackout development
Rem rpatti 07/18/02 - remove post creation sql
Rem skini 07/17/02 - Continue blackout development
Rem skini 06/20/02 - Fix syntax
Rem skini 06/14/02 - Continue implementing blackout support
Rem skini 06/12/02 - Create blackout schedule table
Rem skini 06/05/02 - Repository blackout project
Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Created
Rem
rem
rem This script requires the following arguments
rem 1. Name of the ecm_depot tablespace where all LOBs are stored
rem
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"
rem
rem MGMT_BLACKOUT_SCHEDULE
rem
rem PURPOSE
rem The schedule table
rem
rem COLUMNS
rem BLACKOUT_GUID The id of the schedule
rem SCHEDULE_INDEX The index of the schedule
rem FREQUENCY_CODE The frequency code (type) of the schedule.
rem Possible Values are
rem 1 ONE_TIME_FREQUENCY_CODE
rem 2 INTERVAL_FREQUENCY_CODE
rem 3 DAILY_FREQUENCY_CODE
rem 4 WEEK_FREQUENCY_CODE
rem 5 MONTH_FREQUENCY_CODE
rem 6 YEAR_FREQUENCY_CODE
rem
rem START_TIME The schedule start time. Use SYSDATE if the job is to
rem start immediately. For daily, days of week and days of month
rem schedules, the start_time denotes when the job should start.
rem Only the time part is used, and the date part is ignored.
rem
rem END_TIME For all periodic schedules, the last date (and time) to
rem run the job. For daily, day of week and day of month schedules,
rem only the date portion is used. For interval schedules, the time is
rem taken into account as well.
rem
rem EXECUTION_HOURS The "execution time" for daily, day of
rem week/month/year schedules. Indicates the time of the day at which
rem the job will execute. Hours are specified in 24-hour format
rem (0 to 23), and minutes is a number between 0 and 59.
rem
rem EXECUTION_MINUTES See EXECUTION_HOURS
rem
rem INTERVAL The interval, in minutes, for interval jobs.
rem
rem MONTHS For days-of-year schedules, this indicates the "month"
rem portion of each (month, day) pair in the schedule. The "days"
rem portion is specified using "days", below.
rem
rem DAYS For day of week/month jobs, the day(s) of the week,
rem or month as the case may be.
rem For days of week, this is a set of numbers from 1-7 indicating
rem the days sunday through saturday, in that order.
rem For days of month, this is a set of numbers from 1-31 indicating the
rem date of the month. A special constant called LAST_DAY (-1) can be used
rem for day-of-month schedules to indicate the last day of the month
rem For days of the year, this is a set of numbers from 1-32 indicating
rem the date of the month. A special constant called LAST_DAY (-1) can
rem be used for day-of-year schedules to indicate the last day of the month.
rem For day-of-year schedules, the corresponding months are taken from the
rem "months" field above
rem
rem DURATION This applies to blackout schedules only. This indicates
rem the duration of the blackout, in minutes
rem
rem TIMEZONE_INFO Specifies which timezone the start and end times
rem are specified in
rem TIMEZONE_REPOSITORY: The timezone of the repository
rem TIMEZONE_SPECIFIED: The timezone delta is specified, and stored
rem in the TIMEZONE_OFFSET column
rem
rem
rem TIMEZONE_OFFSET The offset between GMT and the timezone of the
rem schedule. Only relevant when timezone_info=TIMEZONE_SPECIFIED
rem
rem DURATION_SOURCE Specifies whether the duration came from the
rem duration or end time. Possible values are
rem 1 End time
rem 2 Duration
rem 3 Indefinite
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_BLACKOUT_SCHEDULE (
blackout_guid RAW(16),
schedule_index NUMBER(3),
frequency_code NUMBER(2),
start_time DATE,
end_time DATE,
execution_hours NUMBER(3),
execution_minutes NUMBER(3),
interval NUMBER,
months MGMT_JOB_INT_ARRAY,
days MGMT_JOB_INT_ARRAY,
duration NUMBER DEFAULT 0,
timezone_info NUMBER(1),
timezone_offset NUMBER,
timezone_region VARCHAR2(64),
duration_source NUMBER(1) DEFAULT 1
) TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
rem
rem PURPOSE
rem
rem Blackout master table
rem
rem The MGMT_BLACKOUTS table contains information on
rem 1. what blackouts are created.
rem 2. General information such as description, annotation(reason), and
rem schedule of the blackout
rem 3. The blackout is created from GUI or CLI.
rem 4. Blackout history : User who last modified the blackout.
rem
rem COLUMNS
rem
rem BLACKOUT_GUID - The unique is of this blackout.
rem
rem BLACKOUT_NAME - Display name of the blackout.
rem
rem REASON_ID - Purpose of the blackout. requirement from GIT.
rem
rem BLACKOUT_DESC - More description of this blackout.
rem
rem BLACKOUT_STATUS - status of blackout
rem blackout_status is a number from 0-2
rem 0 - PENDING
rem 1 - SCHEDULED
rem 2 - STARTED
rem 3 - COMPLETED
rem
rem
rem JOB_FLAG - A flag that indicates whether jobs should execute
rem during the blackout
rem
rem
rem CREATED_BY - User who creates this blackout. EMD would be sending
rem for blackouts created from CLI.
rem
rem CREATED_THRU - Contains the EMD_URL for blackouts created by CLI.
rem don't care for blackouts from console.
rem
rem LAST_UPDATED_BY - User who last updated/modified this blackout.
rem
rem LAST_UPDATED_TIME - Date/Time when this blackoutwas last updated.
rem
rem START_JOB_ID - The id of the job that starts the blackout
rem
rem END_JOB_ID - The id of the job that ends the blackout
rem
rem OCCURRENCE_NUMBER - The blackout occurrence number
rem NOTES
rem
rem
CREATE TABLE MGMT_BLACKOUTS
(blackout_guid RAW(16) NOT NULL,
blackout_name VARCHAR2(64) NOT NULL,
reason_id NUMBER,
blackout_desc VARCHAR2(2000),
blackout_status NUMBER(2) DEFAULT 0,
job_flag NUMBER(1) DEFAULT 0,
created_by VARCHAR2(256) NOT NULL,
created_thru VARCHAR2(256),
last_updated_by VARCHAR2(256),
last_updated_time DATE DEFAULT SYSDATE,
last_start_time DATE DEFAULT NULL,
last_end_time DATE DEFAULT NULL,
scheduled_time DATE DEFAULT NULL,
start_job_id RAW(16) DEFAULT NULL,
end_job_id RAW(16) DEFAULT NULL,
occurrence_number NUMBER(9) DEFAULT 1,
CONSTRAINT blackouts_primary_key
PRIMARY KEY (blackout_guid)
) STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_TARGET_DETAILS table contains blackout information
rem per target.
rem
rem COLUMNS
rem
rem BLACKOUT_GUID - The unique id of the blackout.
rem TARGET_GUID - The unique id of target
rem INCLUDE_MEMBERS - True (1) if member targets are to be considered
rem for host, composite targets and groups. This has no effect on
rem other types of targets
rem LAST_UPDATED_TIME - Date/time when this blackout info was last updated
rem EDIT_STATE - For internal use only; used when editing the blackout
rem
CREATE TABLE MGMT_BLACKOUT_TARGET_DETAILS
(blackout_guid RAW(16) NOT NULL,
target_guid RAW(16) NOT NULL,
include_members NUMBER(1) DEFAULT 0,
last_updated_time DATE DEFAULT SYSDATE,
edit_state NUMBER(1) DEFAULT 0
)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_FLAT_TARGETS table contains blackout information
rem per each flattened out target.
rem
rem COLUMNS
rem
rem BLACKOUT_GUID - The unique id of the blackout.
rem TARGET_GUID - The unique id of target
rem HOST_BLACKOUT - True (1) for a host if a host blackout
rem must be performed on the agent. Has no effect on other
rem target types.
rem JOB_STATUS - status of the job submission
rem 0 - START PENDING
rem 1 - START FAILURE
rem 2 - START SUCCESS
rem 3 - STOP PENDING
rem 4 - STOP FAILURE
rem 5 - STOP SUCCESS
rem BLACKOUT_STATUS - current status of the blackout on this target
rem 1 - In blackout
rem 0 - Not in blackout
rem ERROR_MESSAGE - error message from job
rem LAST_UPDATED_TIME - Date/time whenthis blackout info was last updated
rem
CREATE TABLE MGMT_BLACKOUT_FLAT_TARGETS
(blackout_guid RAW(16) NOT NULL,
target_guid RAW(16) NOT NULL,
host_blackout NUMBER(1) DEFAULT 0,
job_status NUMBER(2) DEFAULT 0,
blackout_status NUMBER(2) DEFAULT 0,
error_message VARCHAR2(2000) DEFAULT NULL,
last_updated_time DATE DEFAULT SYSDATE,
edit_state NUMBER DEFAULT 0
)
MONITORING;
rem
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_REASON table contains blackout reason
rem information.
rem
rem COLUMNS
rem
rem REASON_ID - The unique id of reason
rem REASON - The reason.
rem REASON_NLS_ID - The NLS ID for the reason
CREATE TABLE MGMT_BLACKOUT_REASON
(reason_id NUMBER NOT NULL,
reason VARCHAR2(64) NOT NULL,
reason_nls_id VARCHAR2(256) DEFAULT NULL,
CONSTRAINT PK_BLACKOUT_REASON PRIMARY KEY(reason_id)
)
MONITORING;
rem
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_HISTORY table keeps track of blackout history
rem (for each occurrence of the blackout)
rem
rem COLUMNS
rem
rem BLACKOUT_GUID - The blackout guid
rem OCCURRENCE_NUMBER - The blackout occurrence number
rem START_TIME - The start time of the blackout
rem END_TIME - The end time of the blackout
rem
CREATE TABLE MGMT_BLACKOUT_HISTORY
(blackout_guid RAW(16),
occurrence_number NUMBER(9) DEFAULT 0,
start_time DATE,
end_time DATE,
CONSTRAINT PK_BLK_HISTORY
PRIMARY KEY(blackout_guid, occurrence_number),
CONSTRAINT FK_BLK_HISTORY
FOREIGN KEY(blackout_guid)
REFERENCES MGMT_BLACKOUTS(blackout_guid)
ON DELETE CASCADE
)
MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_REASON_SEQ sequence is used to allocate unique
rem IDs for reason table
rem
rem NOTES
rem
CREATE SEQUENCE MGMT_BLACKOUT_REASON_SEQ INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 20 NOORDER;
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_STATE table contains the information on blackout
rem start and end periods for a target
rem
rem
rem COLUMNS
rem
rem BLACKOUT_GUID - The unique id of the blackout resulting in this
rem blackout.
rem
rem TARGET_GUID - The unique id of the target
rem
rem COLLECTION_TIMESTAMP
rem
rem - the date/time when the alert was triggered
rem
rem BLACKOUT_CODE
rem
rem - a code that indicates whether the record contains a
rem start or end of a blackout period
rem
rem 1 - blackout start
rem 0 - blackout end
rem
rem TARGET_STATUS
rem - a code that indicates whether the target has entered or
rem exited a blackout.
rem
rem 1 - blackout start.
rem 0 - blackout end.
rem 2 - in blackout.
rem
rem NOTES
rem
rem
CREATE TABLE MGMT_BLACKOUT_STATE
(blackout_guid RAW(16),
target_guid RAW(16) NOT NULL,
collection_timestamp DATE NOT NULL,
load_timestamp DATE DEFAULT SYSDATE,
blackout_code NUMBER(1) NOT NULL,
target_status NUMBER(1) )
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
CREATE PUBLIC SYNONYM MGMT_TARGET_BLACKOUTS FOR MGMT_BLACKOUT_STATE;
rem
rem PURPOSE
rem
rem The MGMT_BLACKOUT_WINDOWS table keeps a record of the
rem start and end periods for a blackout and target. Windows
rem could also indicate future times (ie when the specified
rem blackout has not yet started)
rem
rem
rem COLUMNS
rem
rem BLACKOUT_GUID - The unique id of the blackout resulting in this
rem blackout.
rem
rem TARGET_GUID - The unique id of the target
rem
rem START_TIME, END_TIME: The start and end times of the blackout.
rem For indefinite blackouts, end_time is null.
rem
rem UTC_START_TIME, UTC_END_TIME: The start and end times of the
rem blackout in utc, used by the job system to put executions under
rem blackout
rem
CREATE TABLE MGMT_BLACKOUT_WINDOWS (
target_guid RAW(16),
end_time DATE,
start_time DATE,
utc_start_time DATE,
utc_end_time DATE,
blackout_guid RAW(16),
occurrence_number NUMBER(9) DEFAULT 1,
status NUMBER(2) DEFAULT 0,
CONSTRAINT PK_MGMT_BWINDOWS PRIMARY KEY(blackout_guid, target_guid, start_time, status)
) ORGANIZATION INDEX
MONITORING;
REM
REM PURPOSE
REM
REM This table is used as a proxy table for locking rows in the
REM blackout state insert trigger. This is an internal table only
REM and is there because we want to avoid locking MGMT_TARGETS
REM
CREATE TABLE MGMT_BLACKOUT_PROXY_TARGETS (
target_guid RAW(16),
CONSTRAINT PK_BLK_PROXY_TARGETS PRIMARY KEY(target_guid)
) MONITORING;
-- Calling the table creation script from the migration path
@&EM_SQL_ROOT/core/10.2.0.3/blackout/blackout_tables.sql