Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\rca\rca_tables.sql
Rem drv: <create type="tables" params="EM_ECM_DEPOT_TABLESPACE"/> 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 - <one-line expansion of the name> Rem Rem DESCRIPTION Rem <short description of component this file declares/defines> Rem Rem NOTES Rem <other useful comments, qualifications, etc.> 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;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de