Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\notification\notification_tables.sql
Rem drv: <create type="tables"/> Rem Rem $Header: notification_tables.sql 03-jun-2008.12:16:38 jashukla Exp $ Rem Rem notification_tables.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem notification_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 jashukla 06/03/08 - Bug 7144619 remove internal identifiers Rem neearora 05/03/07 - added rule_guid, repeat in MGMT_NOTIFY_RULES Rem gsbhatia 06/26/05 - New repmgr header impl Rem dcawley 03/24/05 - Add username, password and port for gateway Rem dcawley 03/15/05 - Add columns for wildcard composite keys 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 dcawley 01/11/05 - Update comments Rem ktlaw 01/11/05 - add repmgr header Rem dcawley 10/11/04 - Add target guid for jobs Rem dcawley 09/29/04 - Add column for RCA Rem dcawley 08/19/04 - Add windows column to queue table Rem dcawley 07/28/04 - Increase owner Rem dcawley 07/22/04 - Make severity states per metric Rem skini 07/16/04 - Move MGMT_JOB_STATE_CHANGES to jobs package Rem dcawley 07/16/04 - Rename column Rem dcawley 07/13/04 - Use date Rem dcawley 07/07/04 - Increase user name size Rem dcawley 06/30/04 - Backout changes to gateway Rem dcawley 06/14/04 - Table changes for 10gR2 Rem dcawley 03/25/04 - Add timezone Rem dcawley 07/23/03 - Change notification log Rem streddy 07/10/03 - Added notification log table Rem dcawley 05/21/03 - Changes for moving delivery to OMS Rem dcawley 02/12/03 - Add disabling period for schedule Rem dcawley 02/03/03 - Add comments for short email format Rem dcawley 01/14/03 - Add schedule length Rem dcawley 01/10/03 - Add notification schedules Rem dcawley 12/18/02 - Modify comments on key_value Rem dcawley 11/08/02 - Add metric indexes Rem skini 07/12/02 - Change in target_name column size Rem dcawley 06/06/02 - Remove notify flag from MGMT_NOTIFY_DEVICES. Rem dcawley 05/24/02 - Add rule_name and rule_owner to MGMT_NOTIFY_RETRY. Rem dcawley 05/23/02 - Add availability support Rem dcawley 05/20/02 - Add MGMT_NOTIFY_DEVICE_ASSOC. Rem rpinnama 05/14/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/14/02 - Created Rem rem rem PURPOSE rem Contains the details of the SMTP gateway and the address rem of the outgoing email user rem rem COLUMNS rem MAIL_HOST - the name of the outgoing mail gateway rem EMAIL_ADDRESS - the email address of the user from which all rem outgoing mails will be sent rem EMAIL_NAME - the name associated with the outgoing email rem SMTP_USER - the name of the SMTP user for the mail host rem SMTP_PWD - the password of the SMTP user for the mail host rem SMTP_PORT - the SMTP port to connect to rem PRECEDENCE - the order of the gateway e.g. primary, secondary rem rem NOTES rem Sample values would be 'imap.example.com', 'foo.bar@example.com' rem and 'EMD Notification Manager' which would result in a From line in the rem email of From: "EMD Notification Manager" <foo.bar@example.com> rem rem Some mail gateways require authentication before accepting emails rem for delivery. The mail_host_user and mail_host_password are used rem for this purpose and can be NULL if no authentication is required rem rem CREATE TABLE MGMT_NOTIFY_EMAIL_GATEWAY (mail_host VARCHAR2(128) NOT NULL, email_address VARCHAR2(256) DEFAULT '' NOT NULL, email_name VARCHAR2(64) DEFAULT '' NOT NULL, smtp_user VARCHAR2(256) DEFAULT '', smtp_pwd VARCHAR2(256) DEFAULT '', smtp_port NUMBER DEFAULT 25, precedence NUMBER DEFAULT 1) MONITORING; rem rem PURPOSE rem Contains the list of administrators rem rem COLUMNS rem PROFILE_NAME - the name of the administrator profile rem DESCRIPTION - the description of the profile rem ESCALATION_PROFILE_NAME - the name of the profile to which escalated rem notifications are sent rem ESCALATION_INTERVAL - the number of minutes after which an escalation rem takes place. The default is 60 minutes. rem NOTIFICATION_TTL - the time to live (in days) of a notification after rem which no attempt will be made to deliver it. rem rem NOTES rem CREATE TABLE MGMT_NOTIFY_PROFILES (profile_name VARCHAR2(256) NOT NULL, description VARCHAR2(256) DEFAULT '', escalation_profile_name VARCHAR2(256) DEFAULT '', escalation_interval NUMBER DEFAULT 60, notification_ttl NUMBER DEFAULT 1) MONITORING; rem rem PURPOSE rem Contains the master list of notification devices. Each device must rem have at least one association defined in MGMT_NOTIFY_DEVICE_ASSOC rem rem COLUMNS rem DEVICE_NAME - the name of the device rem DEVICE_DESCRIPTION - the desciption of the device rem PROFILE_NAME - the name of the admin profile rem TYPE - the type of the notification device rem 0 - UNKNOWN rem 1 - LONG_EMAIL rem 2 - OS PROGRAM rem 3 - SNMP TRAP rem 4 - PL/SQL CALLOUT rem 5 - SHORT_EMAIL rem EMAIL_ADDRESS - if TYPE is 1, then this is the email address of the rem person to notify rem PROGRAM - if TYPE is 2, then this is the program/script to run. If type rem is 4, then this is the name of the PL/SQL callout rem SNMP_HOST - if TYPE is 3, then this is the name of the SNMP host to rem send an SNMP Trap to rem SNMP_PORT - if TYPE is 3, then this is the port of the SNMP host to rem send an SNMP Trap to rem SNMP_COMMUNITY - if TYPE is 3, then this is the name of the SNMP rem community that the SNMP_HOST is part of rem NOTIFY - this is a flag used to indicate that the device needs to be rem notified after a rule has been evaluated rem STATUS - whether the device is up or down rem 0 - DOWN rem 1 - UP rem 2 - BEING PINGED rem CONTACT_TIMESTAMP - the time when the Notification Manager last attempted rem to contact the device rem STATUS_MESSAGE - log message from last attempted contact rem rem NOTES rem rem The PL/SQL callout must be a procedure of the following form, see the rem EMD_NOTIFICATION package for a description of the SEVERITY record: rem rem PROCEDURE CALLOUT(severity_in IN MGMT_NOTIFY_SEVERITY); rem CREATE TABLE MGMT_NOTIFY_DEVICES (device_name VARCHAR2(132) NOT NULL, device_description VARCHAR2(256) DEFAULT '', profile_name VARCHAR2(256) NOT NULL, type NUMBER(2) DEFAULT 0 NOT NULL, email_address VARCHAR2(128) DEFAULT '', subject_prefix VARCHAR2(32) DEFAULT '', program VARCHAR2(512) DEFAULT '', snmp_host VARCHAR2(128) DEFAULT '', snmp_port NUMBER DEFAULT 162, snmp_community VARCHAR2(128) DEFAULT 'public', status NUMBER(1) DEFAULT 1 NOT NULL, contact_timestamp DATE DEFAULT SYSDATE NOT NULL, status_message VARCHAR2(512) DEFAULT '') MONITORING; rem rem PURPOSE rem Contains the list of parameters from a severity record to send to rem a notification device rem rem COLUMNS rem DEVICE_NAME - the name of the device rem PROFILE_NAME - the name of the admin profile rem PARAMETER - the name/value of the parameter. Names are preceeded by $ rem for example $TARGET_NAME and are substituted with the rem actual value from a severity occurrence. Values do not have rem a $ as the first character and are passed onto the device as rem they are rem POSITION - the position of the parameter. For program devices this is the rem position on the command line. For email devices this is the rem line number which the parameter occurs. rem ABBREVIATED - indicates whether the parameter should be abbreviated rem TIMESTAMP_FORMAT - the format of parameters if it is a date CREATE TABLE MGMT_NOTIFY_DEVICE_PARAMS (device_name VARCHAR2(132) NOT NULL, profile_name VARCHAR2(256) NOT NULL, parameter VARCHAR2(256) NOT NULL, position NUMBER NOT NULL, abbreviated NUMBER DEFAULT 0 NOT NULL, timestamp_format VARCHAR2(32) DEFAULT '') MONITORING; rem rem PURPOSE rem Contains the notification schedule for each user rem rem COLUMNS rem SCHEDULE_OWNER - the owner of the schedule rem SCHEDULE_NAME - the name of the schedule rem START_DATE - the day the schedule starts from rem NUM_WEEKS - the number of weeks before this schedule repeats rem UPDATED_BY - the user who last updated the schedule rem UPDATED - when the schedule was last updated rem DISABLE_START - the start of a disabling period rem DISABLE_END - the end of a disabling period rem TIMEZONE_REGION - the timezone region of the schedule owner rem rem NOTES rem There is only one schedule per user but the schedule name is rem included in case the need for multiple schedules arise CREATE TABLE MGMT_NOTIFY_SCHEDULES (schedule_owner VARCHAR2(256) NOT NULL, schedule_name VARCHAR2(64) DEFAULT ' ' NOT NULL, start_date DATE DEFAULT SYSDATE NOT NULL, num_weeks NUMBER DEFAULT 1 NOT NULL, updated_by VARCHAR2(256) NOT NULL, updated DATE DEFAULT SYSDATE NOT NULL, disable_start DATE, disable_end DATE, timezone_region VARCHAR2(64) DEFAULT TO_CHAR(SYSTIMESTAMP,'TZR')) MONITORING; rem rem PURPOSE rem Contains the notification schedule for each device rem rem COLUMNS rem SCHEDULE_OWNER - the owner of the schedule rem SCHEDULE_NAME - the name of the schedule rem DEVICE_NAME - the name of the device rem DEVICE_OWNER - the owner of the device rem SCHEDULE - the times when the device is scheduled to be available. The rem schedule can be a maximum of 8 weeks long with a granularity rem of 1 hour. There are 1344 hours in an 8 week period so each rem hour is represented in a 1344 character array. If the device rem is scheduled to be available at a certain hour then the rem corresponding hour will have 'Y' otherwise it will have 'N'. rem Position 1 of the array is from 12:00 midnight on the start rem date of the schedule rem CREATE TABLE MGMT_NOTIFY_DEV_SCHEDULES (schedule_owner VARCHAR2(256) NOT NULL, schedule_name VARCHAR2(64) DEFAULT ' ' NOT NULL, device_name VARCHAR2(132) NOT NULL, device_owner VARCHAR2(256) NOT NULL, schedule VARCHAR2(1344) NOT NULL) MONITORING; rem rem PURPOSE rem Contains the list of notification rules and their descriptions. rem Also contains properties of a notification rule which can be compared rem against a severity to see whether a notification rule should be rem applied to the severity occurrence rem rem COLUMNS rem RULE_NAME - the name of the notification rule rem OWNER - the name of the administrator profile that owns the rule rem DESCRIPTION - the description of the rule rem PUBLIC_RULE - indicates whether the rule is public and available to rem other profiles rem REPEAT - : 0-Repeat notification disabled, 1- Repeat notificaion Enabled rem CREATE TABLE MGMT_NOTIFY_RULES (rule_name VARCHAR2(64) NOT NULL, owner VARCHAR2(256) NOT NULL, description VARCHAR2(256) DEFAULT '', public_rule NUMBER(1) DEFAULT 0, repeat NUMBER DEFAULT 0, rule_guid RAW(16) DEFAULT SYS_GUID() NOT NULL) MONITORING; rem rem PURPOSE rem Contains the information to determine which severity occurrences rem this notification rule applies to. rem rem COLUMNS rem RULE_NAME - the name of the notification rule rem OWNER - the name of the administrator profile that owns the rule rem TARGET_TYPE - the types of targets that this rule applies to rem TARGET_NAME - the name of a target that this rule applies to rem TARGET_GUID - the name of a group target that this rule applies to rem METRIC_NAME - the name of a metric or policy that this rule applies to rem METRIC_COLUMN - the name of a metric column that this rule applies to rem KEY_VALUE - the key value for the metric column that this rule rem applies to: rem % is for metrics without key columns rem %% is for all key values for metrics with key columns, rem so match all values rem For a particular composite key values this is set to rem the composite guid rem Can also be set to a particular key value e.g. /disk1 rem KEY_PART_1 - wildcard value for matching first part of composite key rem KEY_PART_2 - wildcard value for matching second part of composite key rem KEY_PART_3 - wildcard value for matching third part of composite key rem KEY_PART_4 - wildcard value for matching fourth part of composite key rem KEY_PART_5 - wildcard value for matching fifth part of composite key rem WANT_CLEARS - whether this rule applies to clear records rem WANT_WARNINGS - whether this rule applies to warnings rem WANT_CRITICAL_ALERTS - whether this rule applies to critical alerts rem WANT_TARGET_UP - whether this rule applies to target state up severities rem WANT_TARGET_DOWN - whether this rule applies to target state down rem severities rem WANT_TARGET_UNREACHABLE_START - whether this rule applies to severities rem indicating the start of a target being rem unreachable rem WANT_TARGET_UNREACHABLE_END - whether this rule applies to severities rem indicating the end of a target being rem unreachable and thus reachable again rem WANT_TARGET_METRIC_ERR_START - whether this rule applies to severities rem indicating the start of an error in a rem target metric rem WANT_TARGET_METRIC_ERR_END - whether this rule applies to severities rem indicating the end of an error in a rem target metric rem WANT_TARGET_BLACKOUT_START - whether this rule applies to target rem blackout start severities rem WANT_TARGET_BLACKOUT_END - whether this rule applies to target blackout rem end severities rem WANT_POLICY_CLEARS - whether this rule applies to policy cleared rem WANT_POLICY_VIOLATIONS - whether this rule applies to policy violations rem WANT_WARNING_JOB_SUCCEEDED - whether this rule applies to succeeded rem state changes for a warning severity rem WANT_WARNING_JOB_PROBLEMS - whether this rule applies to problem rem state changes for a warning severity rem WANT_CRITICAL_JOB_SUCCEEDED - whether this rule applies to succeeded rem state changes for a critical severity rem WANT_CRITICAL_JOB_PROBLEMS - whether this rule applies to problem rem state changes for a critical severity rem WANT_POLICY_JOB_SUCCEEDED - whether this rule applies to succeeded rem state changes for a policy rem WANT_POLICY_JOB_PROBLEMS - whether this rule applies to problem rem state changes for a policy rem IGNORE_RCA - whether this rule should ignore RCA and deliver rem service up/down notifictions immediately rem rem NOTES rem All the want_ columns can have values of: rem 0 - DON'T WANT THEM rem 1 - DO WANT THEM rem CREATE TABLE MGMT_NOTIFY_RULE_CONFIGS (rule_name VARCHAR2(64) NOT NULL, owner VARCHAR2(256) NOT NULL, target_type VARCHAR2(64) DEFAULT '%' NOT NULL, target_name VARCHAR2(256) DEFAULT '%' NOT NULL, target_guid RAW(16) DEFAULT NULL, metric_name VARCHAR2(64) DEFAULT '%' NOT NULL, metric_column VARCHAR2(64) DEFAULT '%' NOT NULL, key_value VARCHAR2(256) DEFAULT '%' NOT NULL, key_part_1 VARCHAR2(256) DEFAULT ' ' NOT NULL, key_part_2 VARCHAR2(256) DEFAULT ' ' NOT NULL, key_part_3 VARCHAR2(256) DEFAULT ' ' NOT NULL, key_part_4 VARCHAR2(256) DEFAULT ' ' NOT NULL, key_part_5 VARCHAR2(256) DEFAULT ' ' NOT NULL, want_clears NUMBER(1) DEFAULT 0, want_warnings NUMBER(1) DEFAULT 0, want_critical_alerts NUMBER(1) DEFAULT 0, want_target_up NUMBER(1) DEFAULT 0, want_target_down NUMBER(1) DEFAULT 0, want_target_unreachable_start NUMBER(1) DEFAULT 0, want_target_unreachable_end NUMBER(1) DEFAULT 0, want_target_metric_err_start NUMBER(1) DEFAULT 0, want_target_metric_err_end NUMBER(1) DEFAULT 0, want_target_blackout_start NUMBER(1) DEFAULT 0, want_target_blackout_end NUMBER(1) DEFAULT 0, want_policy_clears NUMBER(1) DEFAULT 0, want_policy_violations NUMBER(1) DEFAULT 0, want_warning_job_succeeded NUMBER(1) DEFAULT 0, want_warning_job_problems NUMBER(1) DEFAULT 0, want_critical_job_succeeded NUMBER(1) DEFAULT 0, want_critical_job_problems NUMBER(1) DEFAULT 0, want_policy_job_succeeded NUMBER(1) DEFAULT 0, want_policy_job_problems NUMBER(1) DEFAULT 0, ignore_rca NUMBER(1) DEFAULT 0) MONITORING; rem rem PURPOSE rem Contains the information to determine which job state rem changes this notification rule applies to. rem rem COLUMNS rem RULE_NAME - the name of the notification rule rem OWNER - the name of the administrator profile that owns the rule rem TARGET_TYPE - the type of target that this rule applies to rem TARGET_NAME - the name of a target that this rule applies to rem TARGET_GUID - the name of a group target that this rule applies to rem JOB_NAME - the name of a job that this rule applies to rem JOB_OWNER - the owner of a job that this rule applies to rem JOB_TYPE - the type of a job that this rule applies to rem WANT_JOB_SCHEDULED - whether this rule applies to scheduled state changes rem WANT_JOB_RUNNING - whether this rule applies to running state changes rem WANT_JOB_SUCCEEDED - whether this rule applies to succeeded state changes rem WANT_JOB_SUSPENDED - whether this rule applies to suspended state changes rem WANT_JOB_PROBLEMS - whether this rule applies to problem state changes rem rem NOTES rem All the want_ columns can have values of: rem 0 - DON'T WANT THEM rem 1 - DO WANT THEM rem CREATE TABLE MGMT_NOTIFY_JOB_RULE_CONFIGS (rule_name VARCHAR2(64) NOT NULL, owner VARCHAR2(256) NOT NULL, target_type VARCHAR2(64) DEFAULT '%' NOT NULL, target_name VARCHAR2(256) DEFAULT '%' NOT NULL, target_guid RAW(16) DEFAULT NULL, job_name VARCHAR2(64) DEFAULT '%' NOT NULL, job_owner VARCHAR2(256) DEFAULT '%' NOT NULL, job_type VARCHAR2(32) DEFAULT '%' NOT NULL, want_job_scheduled NUMBER(1) DEFAULT 0, want_job_running NUMBER(1) DEFAULT 0, want_job_succeeded NUMBER(1) DEFAULT 0, want_job_suspended NUMBER(1) DEFAULT 0, want_job_problems NUMBER(1) DEFAULT 0) MONITORING; rem rem PURPOSE rem Contains the list of devices to notify rem rem COLUMNS rem RULE_NAME - the name of the notification rule rem OWNER - the name of the administrator profile that owns the rule rem DEVICE_NAME - the name of the device. Note if this is set to ' ' rem the actual e-mail addresses belonging to the user rem specified in PROFILE_NAME will be looked up when rem queueing notifications rem PROFILE_NAME - the name of the admin profile rem rem NOTES rem rem CREATE TABLE MGMT_NOTIFY_NOTIFYEES (rule_name VARCHAR2(64) NOT NULL, owner VARCHAR2(256) NOT NULL, device_name VARCHAR2(132) NOT NULL, profile_name VARCHAR2(256) NOT NULL, CONSTRAINT MGMT_NOTIFY_NOTIFYEES_PK PRIMARY KEY (rule_name, owner, device_name, profile_name)) ORGANIZATION INDEX MONITORING; rem rem PURPOSE rem Contains the list of devices that either failed to be notified or need rem to have the notification repeated. The RETRY devices are rem automatically retried until either the notification has been successful rem or the severity has been cleared rem rem COLUMNS rem SOURCE_GUID - the globally unique id of the source occurrence rem SOURCE_TYPE - the type of the source rem NOTIFICATION_TYPE - the type of notification rem DEVICE_NAME - the name of the device rem DEVICE_OWNER - the name of the admin profile that owns the device rem DEVICE_TYPE - the type of the device rem RULE_NAME - the name of the rule that caused this device to be notified rem RULE_OWNER - the owner of the rule rem NUM_RETRIES - the number of times an attempt has been made to notify the rem device rem MAX_RETRIES - the maximum number of times a device can be notified rem INSERTION_TIMESTAMP - the time the device was inserted into this table rem LAST_TIMESTAMP - the last time the device was contacted rem NEXT_TIMESTAMP - the next time the device should be contacted rem rem NOTES rem See notification_pkgdefs.sql for values used in *_type fields rem CREATE TABLE MGMT_NOTIFY_REQUEUE (source_guid RAW(16) NOT NULL, source_type NUMBER NOT NULL, notification_type NUMBER NOT NULL, device_name VARCHAR2(132) NOT NULL, device_owner VARCHAR2(256) NOT NULL, device_type NUMBER NOT NULL, rule_name VARCHAR2(64) NOT NULL, rule_owner VARCHAR2(256) NOT NULL, num_requeues NUMBER DEFAULT 0 NOT NULL, max_requeues NUMBER DEFAULT 1 NOT NULL, insertion_timestamp DATE DEFAULT SYSDATE NOT NULL, last_timestamp DATE DEFAULT SYSDATE NOT NULL, next_timestamp DATE DEFAULT SYSDATE NOT NULL) MONITORING; rem rem PURPOSE rem Contains the names of the queues and the OMS that is handling them rem rem COLUMNS rem rem QNAME - the name of the queue rem OMS_ID - The failover id of the oms. This is the same as the failover_id rem in the mgmt_failover_table rem CREATE TABLE MGMT_NOTIFY_QUEUES (qname VARCHAR2(30) NOT NULL PRIMARY KEY, oms_id NUMBER DEFAULT 0, windows NUMBER DEFAULT 0) MONITORING; rem rem PURPOSE rem rem Maintains the log of notification actions. rem rem COLUMNS rem rem SOURCE_OBJ_TYPE - Source object type. Possible values are: rem mgmt_global.G_ANNOTATION_SOURCE_SEVERITY rem mgmt_global.G_ANNOTATION_SOURCE_JOB rem mgmt_global.G_ANNOTATION_SOURCE_POLICY rem rem SOURCE_OBJ_GUID - Source object guid. rem rem TIMESTAMP - Timestamp at which the log message is created rem rem DELIVERED - 'Y' means the delivery succeded, 'N' means the delivery rem failed. The reason for the failure is in the DELIVERY_ERROR rem column rem rem MESSAGE - the notification that was sent or the reason for any rem delivery failure rem CREATE TABLE MGMT_NOTIFICATION_LOG ( source_obj_type NUMBER(2) NOT NULL, source_obj_guid RAW(16) NOT NULL, timestamp DATE DEFAULT SYSDATE, delivered VARCHAR2(1), message VARCHAR2(1024) ) MONITORING; rem rem PURPOSE rem rem To store the mapping of notification source types to format handlers rem The format handlers are Java classes that must reside on the OMS rem machine. They are loaded by the OMS to format a notification object rem for delivery. Each class must implement the rem oracle.sysman.emdrep.notification.NotificationContents interface. rem rem PARAMETERS rem rem SOURCE_OBJ_TYPE - the source object type which can be one of rem EMD_NOTIFICATION.METRIC_SEVERITY rem EMD_NOTIFICATION.JOB_STATE_CHANGE rem EMD_NOTIFICATION.POLICY_VIOLATION rem EMD_NOTIFICATION.CORRECTIVE_ACTION rem rem CLASS_NAME - the name of the class CREATE TABLE MGMT_NOTIFY_FORMAT_HANDLERS ( source_obj_type NUMBER(2) PRIMARY KEY, class_name VARCHAR2(512) NOT NULL ) MONITORING;
Ms-Dos/Windows
Unix
Write backup
jsp File Browser version 1.2 by
www.vonloesch.de