Rem drv:
Rem
Rem $Header: severity_schema_upgrade.sql 24-aug-2005.07:13:21 ramalhot Exp $
Rem
Rem severity_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.
Rem
Rem NAME
Rem severity_schema_upgrade.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem ramalhot 08/24/05 - Moved mgmt_last_violation to v102020
Rem jsadras 08/16/05 - fix null violation type
Rem chyu 07/25/05 - modifying the upgrade header
Rem chyu 07/18/05 - adding the header
Rem chyu 07/05/05 - bug fixing 4476392
Rem rpinnama 06/26/05 -
Rem rpinnama 06/10/05 - Add mgmt_last_violation table
Rem rpinnama 05/17/05 - Change the violation_duration default to NULL
Rem rpinnama 06/14/05 - Fix 4430070: Make the keyvalue buffer 256 long
Rem dkjain 04/15/05 - upgrade mgmt_violations_idx_06
Rem eujang 03/03/05 - add the missing mgmt_current_violation_idx_04
Rem index
Rem rpinnama 02/06/05 - rpinnama_add_upgrade_scripts
Rem rpinnama 02/02/05 - Created
Rem
CREATE OR REPLACE TYPE mgmt_viol_name_value AS OBJECT
(
name VARCHAR2(64),
type NUMBER,
value VARCHAR2(4000)
);
/
CREATE OR REPLACE TYPE mgmt_viol_name_value_array IS TABLE OF mgmt_viol_name_value;
/
Rem MGMT_VIOLATIONS table
Rem
Rem As RENAME command fails with current_schema set to SYSMAN, we
Rem need to copy all the severities
Rem
CREATE TABLE mgmt_violations
(
target_guid RAW(16) NOT NULL,
policy_guid RAW(16) NOT NULL,
key_value VARCHAR2(256) DEFAULT ' ',
collection_timestamp DATE DEFAULT SYSDATE,
violation_guid RAW(16) DEFAULT SYS_GUID(),
violation_level NUMBER NOT NULL,
violation_type NUMBER DEFAULT 0 NOT NULL,
violation_duration NUMBER DEFAULT NULL,
annotated_flag NUMBER DEFAULT 0,
notification_status NUMBER DEFAULT 1,
value NUMBER DEFAULT NULL,
string_value VARCHAR2(1024) DEFAULT NULL,
message VARCHAR2(4000) DEFAULT NULL,
message_nlsid VARCHAR2(64) DEFAULT NULL,
message_params VARCHAR2(4000) DEFAULT NULL,
action_message VARCHAR2(4000) DEFAULT NULL,
action_message_nlsid VARCHAR2(64) DEFAULT NULL,
action_message_params VARCHAR2(4000) DEFAULT NULL,
advisory_id VARCHAR2(64) DEFAULT NULL,
cfg_coll_name VARCHAR2(64) DEFAULT NULL,
cfg_key_value VARCHAR2(256) DEFAULT NULL,
cfg_key_operator NUMBER DEFAULT NULL,
load_timestamp DATE DEFAULT SYSDATE,
user_name VARCHAR2(64) DEFAULT '',
cycle_guid RAW(16)
)
MONITORING;
ALTER TABLE mgmt_violations
ADD CONSTRAINT mgmt_violations_pk
PRIMARY KEY(target_guid, policy_guid, key_value, collection_timestamp, violation_level)
USING INDEX STORAGE (FREELISTS 4) INITRANS 4;
Rem
Rem Move data from severity table to violations table
Rem
PROMPT Moving records from mgmt_severity to mgmt_violations table
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
DECLARE
type rawtab IS TABLE OF RAW(16) index by binary_integer ;
type varchar64tab IS TABLE OF VARCHAR2(64) index by binary_integer ;
type varchar256tab IS TABLE OF VARCHAR2(256) index by binary_integer ;
type varchar4ktab IS TABLE OF VARCHAR2(4000) index by binary_integer ;
type datetab IS TABLE OF DATE index by binary_integer ;
type numtab is TABLE of NUMBER index by binary_integer;
l_target_guids rawtab;
l_metric_guids rawtab;
l_key_values varchar256tab;
l_coll_times datetab;
l_sev_codes numtab;
l_sev_guids rawtab;
l_sev_types numtab;
l_sev_durs numtab;
l_load_times datetab;
l_annot_flgs numtab;
l_notif_stats numtab;
l_messages varchar4ktab;
l_message_ids varchar64tab;
l_message_params varchar4ktab;
l_action_msgs varchar4ktab;
l_action_ids varchar64tab;
l_action_params varchar4ktab;
l_adv_ids varchar64tab;
l_user_names varchar64tab;
l_commit_frequency NUMBER := 10000 ;
CURSOR severity_cur IS
SELECT target_guid, metric_guid, key_value, collection_timestamp, severity_code,
NVL(severity_guid,SYS_GUID()), NVL(severity_type,0) ,
severity_duration, load_timestamp,
annotated_flag, notification_status,
message, message_nlsid, message_params,
action_message, action_nlsid, action_message_params, advisory_id, user_name
FROM mgmt_severity
ORDER BY target_guid, metric_guid, key_value, collection_timestamp, severity_code;
BEGIN
BEGIN
SELECT parameter_value
INTO l_commit_frequency
FROM mgmt_parameters
WHERE parameter_name = 'upgrade.upgrade_severities_batch_size' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_commit_frequency := 10000 ;
END ;
OPEN severity_cur;
FETCH severity_cur BULK COLLECT INTO
l_target_guids, l_metric_guids, l_key_values, l_coll_times, l_sev_codes,
l_sev_guids, l_sev_types, l_sev_durs, l_load_times, l_annot_flgs,
l_notif_stats, l_messages, l_message_ids, l_message_params,
l_action_msgs, l_action_ids, l_action_params, l_adv_ids, l_user_names
LIMIT l_commit_frequency;
WHILE ( (l_target_guids IS NOT NULL) AND (l_target_guids.COUNT > 0) )
LOOP
-- Columns value, string_value,
-- cfg_coll_name, cfg_key_value, cfg_key_operator, cycle_guids are
-- not populated.
FORALL i IN 1..l_target_guids.COUNT
INSERT INTO mgmt_violations
(target_guid, policy_guid, key_value, collection_timestamp,
violation_level, violation_type, violation_duration, violation_guid,
annotated_flag, notification_status,
message, message_nlsid, message_params,
action_message, action_message_nlsid, action_message_params, advisory_id,
load_timestamp, user_name)
VALUES
(l_target_guids(i), l_metric_guids(i), l_key_values(i), l_coll_times(i),
l_sev_codes(i), l_sev_types(i), l_sev_durs(i), l_sev_guids(i),
l_annot_flgs(i), l_notif_stats(i),
l_messages(i), l_message_ids(i), l_message_params(i),
l_action_msgs(i), l_action_ids(i), l_action_params(i), l_adv_ids(i),
l_load_times(i), l_user_names(i) );
COMMIT;
FETCH severity_cur BULK COLLECT INTO
l_target_guids, l_metric_guids, l_key_values, l_coll_times, l_sev_codes,
l_sev_guids, l_sev_types, l_sev_durs, l_load_times, l_annot_flgs,
l_notif_stats, l_messages, l_message_ids, l_message_params,
l_action_msgs, l_action_ids, l_action_params, l_adv_ids, l_user_names
LIMIT l_commit_frequency;
END LOOP;
CLOSE severity_cur;
END;
/
PROMPT Done moving records from mgmt_severity to mgmt_violations table
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
Rem RENAME mgmt_severity TO mgmt_violations;
Rem ALTER TABLE mgmt_violations RENAME COLUMN metric_guid TO policy_guid;
Rem ALTER TABLE mgmt_violations RENAME COLUMN severity_guid TO violation_guid;
Rem ALTER TABLE mgmt_violations RENAME COLUMN severity_code TO violation_level;
Rem ALTER TABLE mgmt_violations RENAME COLUMN severity_type TO violation_type;
Rem ALTER TABLE mgmt_violations RENAME COLUMN severity_duration TO violation_duration;
Rem ALTER TABLE mgmt_violations
Rem ADD (
Rem value NUMBER DEFAULT NULL,
Rem string_value VARCHAR2(1024) DEFAULT NULL,
Rem cfg_coll_name VARCHAR2(64) DEFAULT NULL,
Rem cfg_key_value VARCHAR2(256) DEFAULT NULL,
Rem cfg_key_operator NUMBER DEFAULT NULL,
Rem cycle_guid RAW(16)
Rem );
Rem
Rem Create other indexes on violation table
CREATE INDEX mgmt_violations_idx_02
ON mgmt_violations (violation_level desc, collection_timestamp desc)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE INDEX mgmt_violations_idx_03
ON mgmt_violations(policy_guid, key_value)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE INDEX mgmt_violations_idx_04
ON mgmt_violations(load_timestamp)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE UNIQUE INDEX mgmt_violations_idx_05
ON mgmt_violations (violation_guid)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE INDEX mgmt_violations_idx_06
ON mgmt_violations (policy_guid, violation_type, collection_timestamp)
STORAGE (FREELISTS 4) INITRANS 4;
Rem
Rem Drop the severity table
Rem
DROP TABLE mgmt_severity ;
Rem The following are commented as the drop table should drop these also
Rem ALTER TABLE mgmt_severity DROP CONSTRAINT severity_primary_key;
Rem DROP INDEX mgmt_severity_idx_02;
Rem DROP INDEX mgmt_severity_idx_03;
Rem DROP INDEX mgmt_severity_idx_04;
Rem DROP INDEX mgmt_severity_idx_05;
Rem
Rem MGMT_CURRENT_VIOLATION table.
Rem
CREATE TABLE mgmt_current_violation
(
target_guid RAW(16) NOT NULL,
policy_guid RAW(16) NOT NULL,
key_value VARCHAR2(256) DEFAULT ' ',
collection_timestamp DATE DEFAULT SYSDATE,
violation_guid RAW(16) DEFAULT SYS_GUID(),
violation_level NUMBER NOT NULL,
violation_type NUMBER DEFAULT 0 NOT NULL,
annotated_flag NUMBER DEFAULT 0,
value NUMBER DEFAULT NULL,
string_value VARCHAR2(1024) DEFAULT NULL,
message VARCHAR2(4000) DEFAULT NULL,
message_nlsid VARCHAR2(64) DEFAULT NULL,
message_params VARCHAR2(4000) DEFAULT NULL,
action_message VARCHAR2(4000) DEFAULT NULL,
action_message_nlsid VARCHAR2(64) DEFAULT NULL,
action_message_params VARCHAR2(4000) DEFAULT NULL,
advisory_id VARCHAR2(64) DEFAULT NULL,
exempt_code NUMBER DEFAULT 0,
exempt_until DATE DEFAULT NULL,
exempt_by VARCHAR2(256) DEFAULT NULL,
load_timestamp DATE DEFAULT SYSDATE
)
MONITORING;
ALTER TABLE mgmt_current_violation
ADD CONSTRAINT mgmt_current_violation_pk
PRIMARY KEY(target_guid, policy_guid, key_value)
USING INDEX STORAGE (FREELISTS 4) INITRANS 4;
Rem
Rem Move data from current severity table to current violations table
Rem
PROMPT Moving records from mgmt_current_severity to mgmt_current_violation table
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
DECLARE
type rawtab IS TABLE OF RAW(16) index by binary_integer ;
type varchar64tab IS TABLE OF VARCHAR2(64) index by binary_integer ;
type varchar256tab IS TABLE OF VARCHAR2(256) index by binary_integer ;
type varchar4ktab IS TABLE OF VARCHAR2(4000) index by binary_integer ;
type datetab IS TABLE OF DATE index by binary_integer ;
type numtab is TABLE of NUMBER index by binary_integer;
l_target_guids rawtab;
l_metric_guids rawtab;
l_key_values varchar256tab;
l_coll_times datetab;
l_sev_codes numtab;
l_sev_guids rawtab;
l_sev_types numtab;
l_load_times datetab;
l_annot_flgs numtab;
l_messages varchar4ktab;
l_message_ids varchar64tab;
l_message_params varchar4ktab;
l_action_msgs varchar4ktab;
l_action_ids varchar64tab;
l_action_params varchar4ktab;
l_adv_ids varchar64tab;
l_commit_frequency NUMBER := 10000 ;
CURSOR curr_severity_cur IS
SELECT target_guid, metric_guid, key_value, collection_timestamp, severity_code,
severity_guid, severity_type, load_timestamp, annotated_flag,
message, message_nlsid, message_params,
action_message, action_nlsid, action_message_params, advisory_id
FROM mgmt_current_severity
ORDER BY target_guid, metric_guid, key_value;
BEGIN
BEGIN
SELECT parameter_value
INTO l_commit_frequency
FROM mgmt_parameters
WHERE parameter_name = 'upgrade.upgrade_severities_batch_size' ;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_commit_frequency := 10000 ;
END ;
OPEN curr_severity_cur;
FETCH curr_severity_cur BULK COLLECT INTO
l_target_guids, l_metric_guids, l_key_values, l_coll_times, l_sev_codes,
l_sev_guids, l_sev_types, l_load_times, l_annot_flgs,
l_messages, l_message_ids, l_message_params,
l_action_msgs, l_action_ids, l_action_params, l_adv_ids
LIMIT l_commit_frequency;
WHILE ( (l_target_guids IS NOT NULL) AND (l_target_guids.COUNT > 0) )
LOOP
-- Columns value, string_value,
-- exempt_code, exempt_until, exempt_by
-- not populated.
FORALL i IN 1..l_target_guids.COUNT
INSERT INTO mgmt_current_violation
(target_guid, policy_guid, key_value, collection_timestamp,
violation_level, violation_type, violation_guid, annotated_flag,
message, message_nlsid, message_params,
action_message, action_message_nlsid, action_message_params, advisory_id,
load_timestamp)
VALUES
(l_target_guids(i), l_metric_guids(i), l_key_values(i), l_coll_times(i),
l_sev_codes(i), l_sev_types(i), l_sev_guids(i), l_annot_flgs(i),
l_messages(i), l_message_ids(i), l_message_params(i),
l_action_msgs(i), l_action_ids(i), l_action_params(i), l_adv_ids(i),
l_load_times(i) );
COMMIT;
FETCH curr_severity_cur BULK COLLECT INTO
l_target_guids, l_metric_guids, l_key_values, l_coll_times, l_sev_codes,
l_sev_guids, l_sev_types, l_load_times, l_annot_flgs,
l_messages, l_message_ids, l_message_params,
l_action_msgs, l_action_ids, l_action_params, l_adv_ids
LIMIT l_commit_frequency;
END LOOP;
END;
/
PROMPT Done moving records from mgmt_current_severity to mgmt_current_violations table
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
Rem RENAME mgmt_current_severity TO mgmt_current_violation;
Rem ALTER TABLE mgmt_current_violation RENAME COLUMN metric_guid TO policy_guid;
Rem ALTER TABLE mgmt_current_violation RENAME COLUMN severity_guid TO violation_guid;
Rem ALTER TABLE mgmt_current_violation RENAME COLUMN severity_code TO violation_level;
Rem ALTER TABLE mgmt_current_violation RENAME COLUMN severity_type TO violation_type;
Rem ALTER TABLE mgmt_current_violation
Rem ADD (
Rem value NUMBER DEFAULT NULL,
Rem string_value VARCHAR2(1024) DEFAULT NULL,
Rem exempt_code NUMBER DEFAULT 0,
Rem exempt_until DATE DEFAULT NULL,
Rem exempt_by VARCHAR2(256) DEFAULT NULL
Rem );
Rem
Rem Drop the severity table
Rem
DROP TABLE mgmt_current_severity ;
Rem
Rem ALTER TABLE mgmt_current_violation DROP CONSTRAINT current_severity_primary_key;
Rem DROP INDEX mgmt_current_severity_idx_02;
Rem DROP INDEX mgmt_current_severity_idx_03;
CREATE UNIQUE INDEX mgmt_current_violation_idx_01
ON mgmt_current_violation (violation_guid)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE INDEX mgmt_current_violation_idx_03
ON mgmt_current_violation(policy_guid, key_value, collection_timestamp)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE INDEX mgmt_current_violation_idx_04
ON mgmt_current_violation(violation_type, target_guid, policy_guid)
STORAGE (FREELISTS 4) INITRANS 4;
CREATE TABLE mgmt_violation_context
(
target_guid RAW(16) NOT NULL,
policy_guid RAW(16) NOT NULL,
key_value VARCHAR2(256) DEFAULT ' ',
collection_timestamp DATE DEFAULT SYSDATE,
column_name VARCHAR2(64) DEFAULT ' ',
column_type NUMBER DEFAULT 1,
column_value NUMBER DEFAULT NULL,
column_str_value VARCHAR2(4000) DEFAULT NULL
)
MONITORING;
ALTER TABLE mgmt_violation_context
ADD CONSTRAINT mgmt_violation_context_pk
PRIMARY KEY(target_guid, policy_guid, key_value, collection_timestamp, column_name);
CREATE OR REPLACE VIEW mgmt_severity
(target_guid, metric_guid, key_value, collection_timestamp,
severity_code, severity_type, severity_duration, severity_guid,
annotated_flag, notification_status,
message, message_nlsid, message_params,
action_message, action_nlsid, action_message_params, advisory_id,
load_timestamp, user_name)
AS
SELECT target_guid, policy_guid, key_value, collection_timestamp,
violation_level, violation_type, violation_duration, violation_guid,
annotated_flag, notification_status,
message, message_nlsid, message_params,
action_message, action_message_nlsid, action_message_params, advisory_id,
load_timestamp, user_name
FROM mgmt_violations
WHERE violation_type IN (0, 1, 2);
COMMENT ON TABLE mgmt_severity IS
'The MGMT_SEVERITY view contains the severity information.
Severities are alerts that inform the user when a metric threshold
Test or a policy violation has failed.';
COMMENT ON COLUMN mgmt_severity.target_guid IS
' The target guid of the severity';
COMMENT ON COLUMN mgmt_severity.metric_guid IS
' The metric guid of the severity';
COMMENT ON COLUMN mgmt_severity.key_value IS
' The key value of the severity';
COMMENT ON COLUMN mgmt_severity.collection_timestamp IS
' The timestamp at which the severity occurred';
COMMENT ON COLUMN mgmt_severity.severity_guid IS
' The unique id of the severity. Defaults to SYS_GUID()';
COMMENT ON COLUMN mgmt_severity.severity_code IS
'The severity codes for error, warnin, critical etc.
These codes are backwards compatible with EM 9i and EM 10gR1.
15 - CLEAR
18 - INFO
20 - WARNING
25 - CRITICAL
115 - AGENT UNREACHABLE CLEART
125 - AGENT UNREACHABLE START
215 - BLACKOUT END
225 - BLACKOUT START
315 - METRIC ERROR END
325 - METRIC ERROR START
Codes 115 and above are applicable only for response/status metric';
COMMENT ON COLUMN mgmt_severity.severity_type IS
' The severity type allows an application that is selecting
from this table to filter the rows returned by the type of
severity. Values in this column are:
0 - UNKNOWN
1 - AVAILABILITY
2 - RESOURCE
3 - POLICY VIOLATION';
COMMENT ON COLUMN mgmt_severity.severity_duration IS
'The delta time, in hours, from when the severity was logged
until it was cleared.';
COMMENT ON COLUMN mgmt_severity.annotated_flag IS
'A flag to indicate whether the severity is annotated or not.';
COMMENT ON COLUMN mgmt_severity.notification_status IS
'The column used by the notification sub system to determine
notification status of the severity.';
COMMENT ON COLUMN mgmt_severity.message IS
'The message of the severity. The messages usually contain
details about what triggered this severity.';
COMMENT ON COLUMN mgmt_severity.message_nlsid IS
'The NLS ID of the severity message.';
COMMENT ON COLUMN mgmt_severity.message_params IS
'URL encoded parameters separated by "&" to be used to
format the severity message.';
COMMENT ON COLUMN mgmt_severity.action_message IS
'Suggested action message in english for this severity';
COMMENT ON COLUMN mgmt_severity.action_nlsid IS
'The NLS ID of the action message.';
COMMENT ON COLUMN mgmt_severity.action_message_params IS
'URL encoded parameters for translating action message';
COMMENT ON COLUMN mgmt_severity.advisory_id IS
'Advisory ID of the severity';
COMMENT ON COLUMN mgmt_severity.load_timestamp IS
'Date and time when the severity was loaded';
COMMENT ON COLUMN mgmt_severity.user_name IS
'Name of the user to load the severity';
CREATE OR REPLACE VIEW mgmt_current_severity
(target_guid, metric_guid, key_value, collection_timestamp,
severity_code, severity_type, severity_guid,
annotated_flag, message, message_nlsid, message_params,
action_message, action_nlsid, action_message_params, advisory_id,
load_timestamp )
AS
SELECT target_guid, policy_guid, key_value, collection_timestamp,
violation_level, violation_type, violation_guid,
annotated_flag, message, message_nlsid, message_params,
action_message, action_message_nlsid, action_message_params, advisory_id,
load_timestamp
FROM mgmt_current_violation
WHERE violation_type IN (0, 1, 2);
COMMENT ON TABLE mgmt_current_severity IS
'The mgmt_current_severity view contains the severity information.
Severities are alerts that inform the user when a metric threshold
Test or a policy violation has failed.';
COMMENT ON COLUMN mgmt_current_severity.target_guid IS
' The target guid of the severity';
COMMENT ON COLUMN mgmt_current_severity.metric_guid IS
' The metric guid of the severity';
COMMENT ON COLUMN mgmt_current_severity.key_value IS
' The key value of the severity';
COMMENT ON COLUMN mgmt_current_severity.collection_timestamp IS
' The timestamp at which the severity occurred';
COMMENT ON COLUMN mgmt_current_severity.severity_guid IS
' The unique id of the severity. Defaults to SYS_GUID()';
COMMENT ON COLUMN mgmt_current_severity.severity_code IS
'The severity codes for error, warnin, critical etc.
These codes are backwards compatible with EM 9i and EM 10gR1.
15 - CLEAR
18 - INFO
20 - WARNING
25 - CRITICAL
115 - AGENT UNREACHABLE CLEART
125 - AGENT UNREACHABLE START
215 - BLACKOUT END
225 - BLACKOUT START
315 - METRIC ERROR END
325 - METRIC ERROR START
Codes 115 and above are applicable only for response/status metric';
COMMENT ON COLUMN mgmt_current_severity.severity_type IS
' The severity type allows an application that is selecting
from this table to filter the rows returned by the type of
severity. Values in this column are:
0 - UNKNOWN
1 - AVAILABILITY
2 - RESOURCE';
COMMENT ON COLUMN mgmt_current_severity.message IS
'The message of the severity. The messages usually contain
details about what triggered this severity.';
COMMENT ON COLUMN mgmt_current_severity.message_nlsid IS
'The NLS ID of the severity message.';
COMMENT ON COLUMN mgmt_current_severity.message_params IS
'URL encoded parameters separated by "&" to be used to
format the severity message.';
COMMENT ON COLUMN mgmt_current_severity.action_message IS
'Suggested action message in english for this severity';
COMMENT ON COLUMN mgmt_current_severity.action_nlsid IS
'The NLS ID of the action message.';
COMMENT ON COLUMN mgmt_current_severity.action_message_params IS
'URL encoded parameters for translating action message';
COMMENT ON COLUMN mgmt_current_severity.advisory_id IS
'Advisory ID of the severity';
COMMENT ON COLUMN mgmt_current_severity.load_timestamp IS
'Date and time when the severity was loaded';