Rem drv:
Rem
Rem $Header: notification_schema_upgrade.sql 21-aug-2007.12:39:18 rmaggarw Exp $
Rem
Rem notification_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem notification_schema_upgrade.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem rmaggarw 08/21/07 - Backport rmaggarw_bug-6324220 from main
Rem rmaggarw 11/26/06 - Backport rmaggarw_bug-5666142 from main
Rem rmaggarw 08/20/07 - bug 6324220
Rem rmaggarw 11/22/06 - start the queue before testing
Rem dcawley 08/25/05 - Remove stopping of queue
Rem chyu 07/25/05 - modifying the upgrade header
Rem chyu 07/18/05 - add new header
Rem dcawley 04/14/05 - Fix default in config table
Rem dcawley 03/25/05 - Upgrade gateway table
Rem dcawley 03/21/05 - Stop queue
Rem dcawley 03/15/05 - Add columns for composite keys
Rem eujang 03/07/05 - syncing up the table definitions
Rem dcawley 02/16/05 - Remove foreign keys to allow new table
Rem definition
Rem dcawley 02/11/05 - dcawley_user_model_mig_to_10_2
Rem dcawley 02/09/05 - Created
Rem
Rem
Rem Modify the queue object to account for larger user names
Rem
ALTER TYPE MGMT_NOTIFY_NOTIFICATION
MODIFY ATTRIBUTE device_owner VARCHAR2(256) CASCADE;
ALTER TYPE MGMT_NOTIFY_NOTIFICATION
MODIFY ATTRIBUTE rule_owner VARCHAR2(256) CASCADE;
-- Grant a privilege on the rule sets to make them valid
BEGIN
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET,
object_name => '&EM_REPOS_USER' || '.MGMT_NOTIFY_Q_R',
grantee => '&EM_REPOS_USER',
grant_option => false);
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => SYS.DBMS_RULE_ADM.ALL_ON_RULE_SET,
object_name => '&EM_REPOS_USER' || '.MGMT_NOTIFY_Q_N',
grantee => '&EM_REPOS_USER',
grant_option => false);
EXCEPTION WHEN OTHERS
THEN
NULL;
END;
/
Rem Do a test enqueue/dequeue to make sure the queue is valid
DECLARE
qMsg mgmt_notify_notification;
enq_options dbms_aq.enqueue_options_t;
qMsg_properties dbms_aq.message_properties_t;
qMsg_handle RAW(16);
recipient sys.aq$_agent;
dq_options dbms_aq.dequeue_options_t;
agents dbms_aq.aq$_agent_list_t;
agent sys.aq$_agent;
qmsg_id raw(16);
BEGIN
-- Explicitly start the queue in case it got stopped
dbms_aqadm.start_queue(queue_name => '&EM_REPOS_USER' || '.MGMT_NOTIFY_Q');
-- Enqueue and dequeue a test message from the notification queue
-- to make it valid
qMsg := mgmt_notify_notification(1,0,1,'000000000000000','d1','u1', 1,
'r1', 'u1', 'UPGRADE_TEST');
-- Set up the qname as the recipient name
recipient := sys.aq$_agent('UPGRADE_TEST', NULL, 0);
qMsg_properties.recipient_list(1) := recipient;
-- Add the message to the queue
DBMS_AQ.ENQUEUE(queue_name => '&EM_REPOS_USER' || '.MGMT_NOTIFY_Q',
enqueue_options => enq_options,
message_properties => qMsg_properties,
payload => qMsg,
msgid => qMsg_handle);
dq_options.consumer_name := 'UPGRADE_TEST';
dq_options.wait := 1;
dq_options.navigation := dbms_aq.first_message;
DBMS_AQ.DEQUEUE(queue_name => '&EM_REPOS_USER' || '.MGMT_NOTIFY_Q',
dequeue_options => dq_options,
message_properties => qMsg_properties,
payload => qMsg,
msgid => qmsg_id);
END;
/
CREATE OR REPLACE TYPE MGMT_NOTIFY_COLUMN AS OBJECT
(
column_label VARCHAR2(64),
column_label_nlsid VARCHAR2(64),
value VARCHAR2(256)
);
/
CREATE OR REPLACE TYPE MGMT_NOTIFY_COLUMNS IS TABLE OF MGMT_NOTIFY_COLUMN;
/
-- This is the base object
CREATE OR REPLACE TYPE MGMT_NOTIFY_OBJECT IS OBJECT
(rule_name VARCHAR2(132),
rule_owner VARCHAR2(64)
) NOT FINAL;
/
-- This contains details of a metric severity or a policy violation
CREATE OR REPLACE TYPE MGMT_NOTIFY_SEVERITY UNDER MGMT_NOTIFY_OBJECT
(target_name VARCHAR2(256),
target_type VARCHAR2(64),
timezone VARCHAR2(64),
host_name VARCHAR2(128),
metric_name VARCHAR2(64),
metric_description VARCHAR2(128),
metric_column VARCHAR2(64),
metric_value VARCHAR2(1024),
key_value_name VARCHAR2(512),
key_value VARCHAR2(1290),
key_value_guid VARCHAR2(256),
collection_timestamp DATE,
severity_code NUMBER,
message VARCHAR2(4000),
severity_guid RAW(16),
target_guid RAW(16),
metric_guid RAW(16),
ctxt_list MGMT_NOTIFY_COLUMNS
);
/
-- This contains details of a job state change
CREATE OR REPLACE TYPE MGMT_NOTIFY_JOB UNDER MGMT_NOTIFY_OBJECT
(job_name VARCHAR2(128),
job_owner VARCHAR2(256),
job_type VARCHAR2(32),
job_status NUMBER,
state_change_guid RAW(16),
job_guid RAW(16),
execution_id RAW(16),
targets SMP_EMD_NVPAIR_ARRAY
) NOT FINAL;
/
-- This contains details of a corrective action state change for both
-- metric severities and policy violations. For policy violations, the
-- metric_name stores the policy with metric_column not being used
CREATE OR REPLACE TYPE MGMT_NOTIFY_CORRECTIVE_ACTION UNDER MGMT_NOTIFY_JOB
(metric_name VARCHAR2(256),
metric_column VARCHAR2(64),
metric_value VARCHAR2(1024),
severity_code NUMBER,
key_value_name VARCHAR2(512),
key_value VARCHAR2(1290),
key_value_guid VARCHAR2(256),
ctxt_list MGMT_NOTIFY_COLUMNS
);
/
-- This contains details of a job state changes for a regular job aswell as
-- a corrective action
CREATE OR REPLACE TYPE MGMT_NOTIFY_JOB_SC AS OBJECT
(
job_name VARCHAR2(64),
job_type VARCHAR2(32),
job_type_nlsid VARCHAR2(64),
job_owner VARCHAR2(256),
target_names MGMT_MEDIUM_STRING_ARRAY,
target_types MGMT_MEDIUM_STRING_ARRAY,
target_timezone VARCHAR2(64),
logged DATE,
occurred DATE,
status NUMBER(2),
job_id RAW(16),
execution_id RAW(16),
step_id NUMBER(38),
metric_name VARCHAR2(256),
metric_column VARCHAR2(64),
metric_value VARCHAR2(1024),
violation_level NUMBER,
keys MGMT_NOTIFY_COLUMNS,
ctxt_list MGMT_NOTIFY_COLUMNS
);
/
Rem
Rem Drop unused types
Rem
DROP TYPE SMP_EMD_RULE_DESCRIPTIONS;
DROP TYPE SMP_EMD_RULE_DESCRIPTION;
DROP TYPE SMP_EMD_RULE_TARGETS;
DROP TYPE SMP_EMD_RULE_TARGET;
DROP TYPE SMP_EMD_RULE_METRICS;
DROP TYPE SMP_EMD_RULE_METRIC;
DROP TYPE SMP_EMD_EMAIL_DATA;
DROP TYPE SMP_EMD_GATEWAYS_DATA;
DROP TYPE SMP_EMD_ERR_MSGS;
DROP TYPE SMP_EMD_GATEWAY_DATA;
DROP TYPE SMP_EMD_NOTIFICATION;
Rem
Rem Drop old indexes
Rem
DROP INDEX NOTIFY_RULE_CONFIGS_IDX_02;
Rem
Rem Change the user name sizes
Rem
ALTER TABLE mgmt_notify_profiles MODIFY (profile_name VARCHAR2(256),
escalation_profile_name VARCHAR2(256));
ALTER TABLE mgmt_notify_devices MODIFY (profile_name VARCHAR2(256));
ALTER TABLE mgmt_notify_device_params MODIFY (profile_name VARCHAR2(256));
ALTER TABLE mgmt_notify_schedules MODIFY (schedule_owner VARCHAR2(256),
updated_by VARCHAR2(256));
ALTER TABLE mgmt_notify_dev_schedules MODIFY (schedule_owner VARCHAR2(256),
device_owner VARCHAR2(256));
ALTER TABLE mgmt_notify_rules MODIFY (owner VARCHAR2(256));
ALTER TABLE mgmt_notify_rule_configs MODIFY (owner VARCHAR2(256));
ALTER TABLE mgmt_notify_notifyees MODIFY (owner VARCHAR2(256),
profile_name VARCHAR2(256));
ALTER TABLE mgmt_notify_requeue MODIFY (device_owner VARCHAR2(256),
rule_owner VARCHAR2(256));
Rem
Rem Add new columns
Rem
ALTER TABLE MGMT_NOTIFY_EMAIL_GATEWAY ADD
(smtp_user VARCHAR2(256) DEFAULT '',
smtp_pwd VARCHAR2(256) DEFAULT '',
smtp_port NUMBER DEFAULT 25);
ALTER TABLE MGMT_NOTIFY_SCHEDULES ADD
(timezone_region VARCHAR2(64) DEFAULT TO_CHAR(SYSTIMESTAMP,'TZR'));
ALTER TABLE MGMT_NOTIFY_QUEUES ADD (windows NUMBER DEFAULT 0);
Rem
Rem Rename rules table and create new definition. The data will be
Rem migrated later on
ALTER TABLE MGMT_NOTIFY_RULES
RENAME CONSTRAINT MGMT_NOTIFY_RULES_PK TO MGMT_NOTIFY_RULES_PK_10_1;
ALTER INDEX MGMT_NOTIFY_RULES_PK RENAME TO MGMT_NOTIFY_RULES_PKI_10_1;
ALTER TABLE MGMT_NOTIFY_RULES
RENAME CONSTRAINT MGMT_NOTIFY_RULES_FK TO MGMT_NOTIFY_RULES_FK_10_1;
ALTER TABLE MGMT_NOTIFY_RULES RENAME TO MGMT_NOTIFY_RULES_10_1;
-- Remove this and replace after data migration
ALTER TABLE MGMT_NOTIFY_NOTIFYEES
DROP CONSTRAINT MGMT_NOTIFY_NOTIFYEES_FK_RULE CASCADE DROP INDEX;
DROP INDEX NOTIFY_RULE_CONFIGS_IDX_01;
ALTER TABLE MGMT_NOTIFY_RULE_CONFIGS RENAME TO MGMT_NOTIFY_RULE_CONFIGS_10_1;
ALTER TABLE MGMT_NOTIFY_RULE_CONFIGS_10_1
RENAME CONSTRAINT MGMT_NOTIFY_RULE_CONFIGS_FK TO MGMT_NOTIFY_RULE_CONFIGSFK10_1;
Rem
Rem Modify index for notifyees table to allow user subscription without
Rem having an e-mail address defined
Rem
ALTER TABLE MGMT_NOTIFY_NOTIFYEES
DROP CONSTRAINT MGMT_NOTIFY_NOTIFYEES_FKDEVICE CASCADE DROP INDEX;
ALTER TABLE MGMT_NOTIFY_NOTIFYEES
ADD CONSTRAINT MGMT_NOTIFY_NOTIFYEES_FKPROF
FOREIGN KEY (profile_name)
REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
ON DELETE CASCADE;
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
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)
MONITORING;
ALTER TABLE MGMT_NOTIFY_RULES
ADD CONSTRAINT MGMT_NOTIFY_RULES_PK
PRIMARY KEY (rule_name, owner);
ALTER TABLE MGMT_NOTIFY_RULES
ADD CONSTRAINT MGMT_NOTIFY_RULES_FK
FOREIGN KEY (owner)
REFERENCES MGMT_NOTIFY_PROFILES (profile_name)
ON DELETE CASCADE;
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;
ALTER TABLE MGMT_NOTIFY_RULE_CONFIGS
ADD CONSTRAINT MGMT_NOTIFY_RULE_CONFIGS_FK
FOREIGN KEY (rule_name, owner)
REFERENCES MGMT_NOTIFY_RULES (rule_name, owner)
ON DELETE CASCADE;
CREATE INDEX NOTIFY_RULE_CONFIGS_IDX_01
ON MGMT_NOTIFY_RULE_CONFIGS
(rule_name, owner, target_type);
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;
ALTER TABLE MGMT_NOTIFY_JOB_RULE_CONFIGS
ADD CONSTRAINT MGMT_NOTIFY_JOB_RULE_CFS_FK
FOREIGN KEY (rule_name, owner)
REFERENCES MGMT_NOTIFY_RULES (rule_name, owner)
ON DELETE CASCADE;
CREATE INDEX NOTIFY_RULE_JOB_CONFIGS_IDX_01
ON MGMT_NOTIFY_JOB_RULE_CONFIGS (rule_name, owner);
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;