Rem drv:
Rem
Rem $Header: availability_tables.sql 29-jun-2005.01:47:12 gsbhatia Exp $
Rem
Rem availability_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem availability_tables.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem gsbhatia 06/26/05 - New repmgr header impl
Rem rpinnama 05/16/05 - Fix bug 4345566 : Improve the perf of before
Rem insert trigger (Add max_avail_sev_ts)
Rem jsadras 03/30/05 - Convert mgmt_availability to iot
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 scgrover 01/27/05 - convert to iot
Rem scgrover 01/26/05 - make table iot
Rem ktlaw 01/11/05 - add repmgr header
Rem gan 08/20/04 - add monitoring
Rem mbhoopat 11/16/03 - master-changed callback changes
Rem streddy 07/22/03 - Added callback table
Rem streddy 04/29/03 - Added mgmt_master_agent table
Rem tzimmerm 10/10/02 - Removing mgmt_availability_1Day table
Rem tzimmerm 06/26/02 - Adding agent down state to 1day rollup table
Rem rpinnama 06/05/02 - Add availability marker table.
Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts
Rem rpinnama 05/14/02 - Created
Rem
rem
rem PURPOSE
rem
rem This table contains information for contiguous availability durations
rem for each target. Each duration is either up, down, or in blackout. The
rem endpoints of the duration are defined in start_collection_timestamp
rem and end_collection_timestamp.
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target
rem
rem SEVERITY_GUID
rem
rem - This is the guid of the severity record associated with this
rem availability record. It can be used to associate availability
rem records with annotations and/or severities.
rem
rem CURRENT_STATUS
rem
rem - The status of the target over this duration. Status codes
rem are:
rem 0 - target down (unscheduled)
rem 1 - target up
rem 2 - target status unknown (Response metric in error)
rem 3 - target status unmonitored (Monitoring EMD is down)
rem 5 - target in blackout
rem
rem START_COLLECTION_TIMESTAMP
rem
rem - The timestamp marking the beginning of the duration.
rem
rem END_COLLECTION_TIMESTAMP
rem
rem - The timestamp marking the end of the duration. The open
rem duration has an end_collection_timestamp of null (this
rem represents the current status of a target.
rem
rem NOTES
CREATE TABLE MGMT_AVAILABILITY
(target_guid RAW(16) NOT NULL,
severity_guid RAW(16) DEFAULT NULL,
current_status NUMBER NOT NULL,
start_collection_timestamp DATE NOT NULL,
end_collection_timestamp DATE DEFAULT NULL,
CONSTRAINT availability_primary_key
PRIMARY KEY (target_guid, start_collection_timestamp, current_status))
ORGANIZATION INDEX
COMPRESS 1
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem This table contains the latest availability status for all monitored
rem targets. This table will contain exactly one row per monitored target.
rem
rem COLUMNS
rem
rem Column definitions are identical to MGMT_AVAILABILITY. The only
rem difference is that end_collection_timestamp has been removed because
rem it has no meaning or value here.
rem
rem NOTES
rem
CREATE TABLE MGMT_CURRENT_AVAILABILITY
(target_guid RAW(16) NOT NULL,
severity_guid RAW(16) DEFAULT NULL,
current_status NUMBER NOT NULL,
start_collection_timestamp DATE NOT NULL,
CONSTRAINT MGMT_CURRENT_AVAILABILITY_PK PRIMARY KEY (target_guid)
)
ORGANIZATION INDEX MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_AVAILABILITY_MARKER table contains the timestamp and
rem the last known availability status up to which we reliably know the
rem availability status of the target.
rem This table will contain exactly one row per monitored target.
rem
rem COLUMNS
rem
rem TARGET_GUID - The unique id of the target
rem
rem MARKER_TIMESTAMP
rem
rem - The timestamp marking the time upto which we know
rem the availability of the target reliably.
rem
rem MARKER_AVAIL_STATUS
rem - The availability status of the target at the marker timestamp.
rem
rem NOTES
rem
CREATE TABLE MGMT_AVAILABILITY_MARKER
(target_guid RAW(16) NOT NULL,
marker_timestamp DATE NOT NULL,
marker_avail_status NUMBER NOT NULL,
CONSTRAINT MGMT_AVAIL_MARKER_PK PRIMARY KEY (target_guid)
)
ORGANIZATION INDEX MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_MASTER_AGENT table contains the master agent history for
rem OMS/Agent mediated multi-agent targets. Master agent is the agent that
rem is reponsible for monitoring/managing a multi-agent target. This data is
rem used by different components of the repository to discard unwanted metric
rem and severity data.
rem
rem COLUMNS
rem
rem TARGET_GUID : The unique ID of the target.
rem AGENT_GUID : The unique ID of the agent that is the new master.
rem START_TIMESTAMP : Timestamp marking the beginning of the duration
rem END_TIMESTAMP : Timestamp marking the end of the duration.
rem
rem NOTES
rem
rem
rem
CREATE TABLE MGMT_MASTER_AGENT
(target_guid RAW(16) NOT NULL,
agent_guid RAW(16) NOT NULL,
start_timestamp DATE NOT NULL,
end_timestamp DATE,
CONSTRAINT PK_MGMT_MASTER_AGENT
PRIMARY KEY(target_guid, agent_guid, start_timestamp))
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem The MGMT_MASTER_CHANGED_CALLBACKS table holds a set of callbacks
rem that will be executed when a target is added.
rem
rem COLUMNS
rem
rem CALLBACK_NAME The add callback name
rem
rem TARGET_NAME Target name for which this callback is applicable.
rem ' ' for any target instance.
rem
rem TARGET_TYPE Target type for which this callback is applicable.
rem
CREATE TABLE MGMT_MASTER_CHANGED_CALLBACK (
target_name VARCHAR2(256) DEFAULT ' ',
target_type VARCHAR2(64) NOT NULL,
callback_name VARCHAR2(64) NOT NULL,
CONSTRAINT MGMT_MASTER_CHANGED_CBACK_PK
PRIMARY KEY (target_name, target_type, callback_name)
) MONITORING;