Rem drv:
Rem
Rem $Header: rca_tables.sql 29-jun-2005.01:47:49 gsbhatia Exp $
Rem
Rem rca_tables.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem rca_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 jriel 05/12/05 - no event result duplication
Rem jriel 04/21/05 - add recovery table
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 ktlaw 01/11/05 - add repmgr header
Rem jriel 11/12/04 - add event action
Rem jriel 11/05/04 - add run hash
Rem jriel 10/05/04 - add confidence
Rem jriel 09/29/04 - add net event tables
Rem jriel 08/30/04 - add target_guid to summary
Rem jriel 08/17/04 - remove exception table
Rem jriel 08/13/04 - add composite key support
Rem jriel 08/12/04 - add status and exception to run
Rem jriel 07/28/04 - add operator to test table
Rem jriel 07/23/04 - change to lob
Rem jriel 07/22/04 - switch test event relationship
Rem jriel 07/01/04 - jriel_rca1
Rem jriel 06/14/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
DEFINE EM_ECM_DEPOT_TABLESPACE = "&1"
rem
rem PURPOSE
rem
rem This table is used to store all events introduced into the RCA system that are part of
rem an analysis. All events are related to other events as either causes or symptoms.
rem Because a given event may be associated with multiple other events, these relationships
rem are not stored in the event table but are stored in the association table (MGMT_RCA_EVENT_ASSOC).
rem
rem The primary key to this table is the EVENT_GUID. The EVENT_GUID is generated from the SOURCE_TYPE,
rem SOURCE_GUID and COLLECTION_TIME).
rem
rem The EVENT_GUID is used to locate the details related to this event occurrence, which are
rem stored in either the MGMT_RCA_TEST_RESULT or MGMT_SEVERITY tables depending on the SOURCE_TYPE.
rem See the table below for details related to each SOURCE_TYPE.
rem
rem The METRIC_TEST_GUID is used to identify the test from which the event was generated. If
rem the event is based on a metric test added by the user to a service definition, then the
rem METRIC_TEST_GUID will refer to a corresponding test record in the MGMT_RCA_METRIC_TEST table.
rem If, however, testing the status of a target produces the event, then no corresponding test
rem definition will exist (since status tests are done implicitly). In this case the METRIC_TEST_GUID
rem is the METRIC_GUID of the status metric for the target type being tested.
rem
rem Source Type Val Description
rem STATUS_SEVERITY 0 Events of this type correspond to status severities that are encountered during
rem RCA processing. The details related to this event are retrieved from the mgmt_severity
rem table where the severity_guid = event.source_guid from the row. Tests of target status
rem are NOT defined directly by the user, but are done implicitly by RCA. Therefore, there
rem are no corresponding test definitions in the mgmt_rca_metric_test table.For events of
rem this type, the metric_test_guid will correspond to the metric_guid from the mgmt_metrics
rem table for the status metric for the target tested.
rem
rem STATUS_TEST 1 Events of this type correspond to tests of target/service status performed during RCA
rem which are not directly associated with a severity. Because these events do not correspond
rem to a severity, the results for these events are stored in the mgmt_rca_test_result table
rem where the result_guid = event.source_guid.Tests of target status are NOT defined directly
rem by the user, but are done implicitly by RCA. Therefore, there are no corresponding test
rem definitions in the mgmt_rca_metric_test table.For events of this type, the metric_test_guid
rem will correspond to the metric_guid from the mgmt_metrics table for the status metric for
rem the target tested.
rem
rem METRIC_ SEVERITY 2 Events of this type correspond to RCA tests of non-status metrics performed during RCA,
rem which have been associated with a severity record for the same target and metric.
rem These tests are added to the topology by the user and are defined in mgmt_rca_metric_test
rem table where the metric_test_guid = event.metric_test_guid.The details related to this event
rem are retrieved from the mgmt_severity table where the severity_guid = event.source_guid from
rem the row.
rem
rem METRIC_TEST 3 Events of this type correspond to RCA tests of non-status metrics performed during RCA,
rem which have NOT been associated with a severity record for the same target and metric.
rem These tests are added to the topology by the user and are defined in mgmt_rca_metric_test
rem table where the metric_test_guid = event.metric_test_guid.Because these events do not
rem correspond to a severity, the results for these events are stored in the mgmt_rca_test_result
rem table where the result_guid = event.source_guid.
rem
rem COLUMNS
rem
rem EVENT_GUID - The unique identifier of the event
rem
rem SOURCE_TYPE - The source of the event (see above)
rem
rem SOURCE_GUID - The identifier of the source (severity_guid, result_guid, other...)
rem
rem TARGET_GUID - The target from which the event originated
rem
rem COLLECTION_TIME - The time the event was collected
rem
rem TEST_GUID - The test for which was performed, may be null or reference metric_guid or metric_test_guid
rem
rem NOTES
CREATE TABLE MGMT_RCA_EVENT
(event_guid RAW(16) NOT NULL,
source_type NUMBER NOT NULL,
source_guid RAW(16) NOT NULL,
target_guid RAW(16) NOT NULL,
collection_time TIMESTAMP NOT NULL,
test_guid RAW(16) DEFAULT NULL,
event_action VARCHAR2(512))
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem Keeps track of if RCA may be performed on a target by the OMS or if its
rem effectively disabled (still may be performed on-demand by the end-user).
rem
rem TARGET_GUID - the target
rem INTERACTIVE_RCA - if 0 (false) then system may do RCA, if 1 (true) then only interactive (user) RCA
rem COLLECT_ON_DEMAND - if 0 (false) then no collection allowed, if 1 (true) then colleciton allowed
rem
rem NOTES
rem If no row exists in this table then RCA may be performed by the system.
rem
CREATE TABLE MGMT_RCA_TARGET_PROPS
(target_guid RAW(16) NOT NULL,
interactive_rca NUMBER NOT NULL,
collect_on_demand NUMBER NOT NULL)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem Keeps track of if RCA may collect data on-demand (through Agent) for
rem a particular target and metric.
rem
rem TARGET_GUID - the target
rem METRIC_GUID - the metric
rem COLLECT_ON_DEMAND - if 0 (false) then no collection allowed, if 1 (true) then colleciton allowed
rem
rem NOTES
rem If no row exists in this table then RCA may collect on-demand.
rem Longer term this may want to be folded into the target definition.
rem
CREATE TABLE MGMT_RCA_METRIC_PROPS
(target_guid RAW(16) NOT NULL,
metric_guid RAW(16) NOT NULL,
interactive_rca NUMBER NOT NULL)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem This table contains the information associated with each RCA run against a particular RCA
rem event. It includes the event_guid column that references the RCA event for which the RCA
rem process was triggered. The rca_type column is used to identify the type of RCA to be
rem performed. For 10gR2 this will always be SERVICE_FAULT.
rem Each time RCA is updated for a particular event (service failure in 10gR2) a new row is
rem inserted into this table with the update_sequence incremented. All other RCA data stored
rem during the analysis is keyed off of this column pair (event_guid, update_sequence).
rem
rem COLUMNS
rem
rem EVENT_GUID - The identifier of the event with which this RCA (analysis) is associated
rem
rem UPDATE_ID - The update or run number associated with this analysis; each time the analysis
rem is updated the updated information is stored under a new id (incremented)
rem
rem RCA_STATUS - The status of this analysis (RUNNING, COMPLETE)
rem
rem RCA_TYPE - (SERVICE_FAILURE), reserved for future use
rem
rem CAUSE_COUNT - number of root causes associated with this analysis
rem
rem EXCEPTION_COUNT - number of exceptions encountered during this analysis
rem
rem START_TIME - time the analysis started
rem
rem END_TIME - time the analysis completed
rem
rem LAST_RUN_TIME - the last time the analysis was run, there may not be a corresponding update record if nothing changed
rem
rem NOTES
rem
CREATE TABLE MGMT_RCA_SUMMARY
(event_guid RAW(16) NOT NULL,
last_update_id NUMBER NOT NULL,
severity_guid RAW(16) DEFAULT NULL,
target_guid RAW(16) NOT NULL,
rca_type NUMBER NOT NULL,
rca_status NUMBER NOT NULL,
last_run_time DATE NOT NULL)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
CREATE TABLE MGMT_RCA_RUN
(event_guid RAW(16) NOT NULL,
update_id NUMBER NOT NULL,
cause_count NUMBER DEFAULT -1,
exception_count NUMBER DEFAULT -1,
start_time DATE NOT NULL,
end_time DATE NOT NULL,
status NUMBER NOT NULL,
error_text VARCHAR(256),
event_hash VARCHAR(256),
confidence_actual NUMBER,
confidence_possible NUMBER)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem
rem PURPOSE
rem
rem This table contains the associations between events in the context of a particular
rem RCA run. The symptom_event_guid is the event in the association most closely
rem positioned to the triggering event and the cause_event_guid is the next event
rem in the topology arc. The event_guid and update_sequence identify the RCA run
rem with which the data is associated. The is_root_cause column indicates if the
rem cause_event_guid identifies an event that RCA has determined to be a root cause.
rem
rem COLUMNS
rem
rem EVENT_GUID - The identifier of the event with which this RCA (analysis) is associated
rem
rem UPDATE_ID - The update or run number associated with this analysis; each time the analysis
rem is updated the updated information is stored under a new id (incremented)
rem
rem SYMPTOM_EVENT_GUID - The source of the association arc
rem
rem CAUSE_EVENT_GUID - The destination of the association arc
rem
rem IS_ROOT_CAUSE - Identifies if the cause event at the end of the association is a root
rem cause (leaf node).
rem
rem NOTES
rem
CREATE TABLE MGMT_RCA_EVENT_ASSOC
(event_guid RAW(16) NOT NULL,
update_id NUMBER NOT NULL,
symptom_event_guid RAW(16) NOT NULL,
cause_event_guid RAW(16) NOT NULL,
is_root_cause NUMBER NOT NULL,
cause_confidence NUMBER)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem This table contains the set of exceptions encountered during a specific RCA run.
rem The key to this table is the EVENT_GUID and UPDATE_SEQUENCE. If the exception can
rem be associated with a particular target in the service topology, then the SOURCE_GUID
rem column is populated with the target guid.
rem
rem COLUMNS
rem
rem EVENT_GUID - The identifier of the event with which this RCA (analysis) is associated
rem
rem UPDATE_ID - The update or run number associated with this analysis; each time the analysis
rem is updated the updated information is stored under a new id (incremented)
rem
rem EXCEPTION_ID - The numeric order of this exception in the complete list associated with
rem this analysis.
rem
rem SOURCE_TYPE - The source of the exception
rem Type Description
rem AGENT_UNREACHABLE The status of the target at the time RCA attempted to interact with
rem it was unreachable
rem METRIC_COLL_ERROR An error or timeout occurred during the collection of a particular
rem metric during RCA
rem TARGET_BLACKOUT A target that participates in the service topology is currently
rem blacked out and therefore no interrogation of the target is included
rem in the RCA for the failed service.
rem TOPOLOGY_ERROR An error has occurred while attempting to retrieve the topology for the service
rem SYNC_ERROR An error or timeout occurred while attempting to synchronize with the
rem RCA for a dependent service
rem UNHANDLED An exception that is not expected by RCA (see below)
rem
rem SOURCE_GUID - Identifier of the exception source, may be target_guid or metric_guid
rem
rem MESSAGE - Text of exception, should only be used if the exception text is not
rem localized
rem
rem MESSAGE_NLSID - The resource id of the exception that should be available in the
rem RCA resource bundle.
rem
rem MESSAGE_PARAMS - Delimited set of strings that are substituted into message string
rem to format the final exception message.
rem
rem NOTES
rem
-- as of 08/17/2004 we will keep exceptions in trace in mgmt_rca_trace rows...
--
-- CREATE TABLE MGMT_RCA_EXCEPTION
-- (event_guid RAW(16) NOT NULL,
-- update_id NUMBER NOT NULL,
-- exception_id NUMBER NOT NULL,
-- source_type NUMBER NOT NULL,
-- source_guid RAW(16) DEFAULT NULL,
-- message VARCHAR2(4000) DEFAULT NULL,
-- message_nlsid VARCHAR2(64) DEFAULT NULL,
-- message_params VARCHAR2(4000) DEFAULT NULL)
-- STORAGE
-- (FREELISTS 4)
-- INITRANS 2
-- MONITORING;
rem
rem PURPOSE
rem
rem This table contains the information describing a fault test added to a service topology.
rem The key to this table is the METRIC_TEST_GUID that is generated from SCOPE_GUID, SCOPE,
rem TARGET_GUID, METRIC_GUID, and KEY_VALUE columns. If a threshold is defined for the test,
rem then the THRESHOLD_SOURCE column is set to USER, and the RCA_THRESHOLD column is populated.
rem If this column is set to CRITICAL then the critical threshold is used for the test and is
rem retrieved from the MGMT_METRIC_THRESHOLDS table. To retrieve this threshold, the code
rem must look for records for the specific key, and if not found use the default threshold
rem specified for all keys.
rem For 10gR2 the scope of all RCA metric tests is to the service for which RCA will be
rem performed, therefore the SCOPE column will always contain the value TARGET and the
rem SCOPE_GUID column will contain the target guid of the service for which the test was defined.
rem
rem COLUMNS
rem
rem METRIC_TEST_GUID - identifier of the test
rem
rem TARGET_GUID - identifier of the target to which the test applies
rem
rem METRIC_GUID - identifier of the metric on which the test is performed
rem
rem KEY_VALUE - metric key on which the test is performed
rem
rem SCOPE - scope to which the test applies (only SERVICE for 10gR2)
rem
rem SCOPE_GUID - identifier of the item/domain on which the scope of the test applies
rem
rem THRESHOLD_SOURCE - identifies if the threshold is user supplied or the critical threshold
rem
rem RCA_THRESHOLD - the threshold which the user has supplied if other than the critical threshold
rem
rem NOTES
rem
CREATE TABLE MGMT_RCA_METRIC_TEST
(metric_test_guid RAW(16) NOT NULL,
target_guid RAW(16) NOT NULL,
metric_guid RAW(16) NOT NULL,
key_value VARCHAR2(256) DEFAULT NULL,
key_part1_value VARCHAR2(256) DEFAULT NULL,
key_part2_value VARCHAR2(256) DEFAULT NULL,
key_part3_value VARCHAR2(256) DEFAULT NULL,
key_part4_value VARCHAR2(256) DEFAULT NULL,
key_part5_value VARCHAR2(256) DEFAULT NULL,
scope NUMBER NOT NULL,
scope_guid RAW(16) NOT NULL,
threshold_source NUMBER NOT NULL,
operator NUMBER,
rca_threshold VARCHAR2(256) DEFAULT NULL,
message_nlsid VARCHAR2(128),
message_params VARCHAR2(256))
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem This table contains the results of an RCA test defined in mgmt_rca_metric_test
rem (with the exception of target status tests). The key to this table is the RESULT_GUID
rem which is generated from the target_guid, metric_test_guid and collection_timestamp columns.
rem For status tests the metric_test_guid references the mgmt_metrics.metric_guid for the
rem status metric, and the is_status column is set to true to indicate this case.
rem
rem COLUMNS
rem
rem RESULT_GUID - the identifier of the test result
rem
rem TARGET_GUID - the identifier of the target to which the result applies
rem
rem METRIC_TEST_GUID - the identifier of the test that was performed
rem
rem COLLECTION_TIMESTAMP - the time that the test was performed
rem
rem TEST_VALUE - the value tested
rem
rem MESSAGE - the text associated with the test result, should only be used
rem in cases where the message is not localized
rem
rem MESSAGE_NLSID - the resource id of the test result text; the resource bundle
rem is the generated bundle associated with the target type of
rem target identified by the target_guid
rem
rem MESSAGE_PARAMS - delimited set of strings substituted during message formatting
rem
rem LAST_COLL_TIMESTAMP - the last time the result was updated
rem
rem LAST_TEST_VALUE - the last value reported for this test
rem
rem RESULT_STATUS - if this test is open or clear
rem
rem NOTES
rem
CREATE TABLE MGMT_RCA_TEST_RESULT
(result_guid RAW(16) NOT NULL,
metric_test_guid RAW(16) NOT NULL,
target_guid RAW(16) NOT NULL,
collection_timestamp DATE NOT NULL,
test_value VARCHAR2(256) NOT NULL,
last_coll_timestamp DATE,
last_test_value VARCHAR2(256),
result_status NUMBER(1) NOT NULL,
message VARCHAR2(4000) DEFAULT NULL,
message_nlsid VARCHAR2(64) DEFAULT NULL,
message_params VARCHAR2(4000) DEFAULT NULL,
is_status NUMBER NOT NULL)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem This table contains the trace records associated with an RCA run. The key to the
rem table is the EVENT_GUID and UPDATE_SEQUENCE. If tracing is enabled for RCA (see
rem properties) then each time RCA runs the steps taken during analysis are recorded
rem in this trace. Each RCA run has a single corresponding trace row, and the TRACE_DOC
rem column is a blob containing and XML document corresponding to the RCA trace DTD
rem (to be defined).
rem
rem COLUMNS
rem
rem EVENT_GUID - The identifier of the event with which this RCA (analysis) is associated
rem
rem UPDATE_ID - The update or run number associated with this analysis; each time the analysis
rem is updated the updated information is stored under a new id (incremented)
rem
rem TRACE_DOC - The XML document that describes the trace of the analysis.
rem
rem NOTES
rem
rem -- wanted to make trace_doc sys.XMLTYPE --
CREATE TABLE MGMT_RCA_TRACE
(event_guid RAW(16) NOT NULL,
update_id NUMBER NOT NULL,
trace_doc CLOB)
MONITORING
LOB(trace_doc) STORE AS (TABLESPACE &EM_ECM_DEPOT_TABLESPACE);
rem
rem PURPOSE
rem
rem Table to hold events generated by the external Network integration
rem system that are associated with an IP address, and therefore a beacon
rem and service that uses the beacon.
rem
CREATE TABLE MGMT_NET_EVENTS
( net_event_guid RAW(16) NOT NULL,
ip_address VARCHAR2(64) NOT NULL,
collection_time DATE NOT NULL,
message VARCHAR2(4000) DEFAULT NULL,
message_nlsid VARCHAR2(64) DEFAULT NULL,
message_params VARCHAR2(4000) DEFAULT NULL)
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;
rem
rem PURPOSE
rem
rem Table to hold recovery records which are used to locate any orphaned
rem RCA tasks where the task was killed before it could store results.
rem
CREATE TABLE MGMT_RCA_RECOVERY
(source_guid RAW(16) NOT NULL,
start_time DATE NOT NULL,
recovery_type NUMBER(1))
STORAGE
(FREELISTS 4)
INITRANS 2
MONITORING;