Rem drv:
Rem
Rem $Header: collections_schema_upgrade.sql 16-may-2007.17:05:48 dgiaimo Exp $
Rem
Rem collections_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem collections_schema_upgrade.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem dgiaimo 05/16/07 - Backport dgiaimo_bug-5984641 from main
Rem dgiaimo 05/14/07 - Removing mgmt_collection_tasks_time table
Rem chyu 08/23/05 - removing the task_class from the schema
Rem upgrade since it is introduced after BETA.
Rem chyu 07/25/05 - modifying the upgrade header
Rem chyu 07/18/05 - modify the version number for the header
Rem jsadras 06/08/05 - add index on next_collection_time
Rem jsadras 06/02/05 - add task class
Rem jsadras 05/03/05 - add task class support
Rem jsadras 04/15/05 - Bug:4039961, handle multiple metrics per
Rem collection
Rem jsadras 03/29/05 - add is_transient
Rem chyu 03/16/05 - remove the extra table
Rem jsadras 02/28/05 - remove task_time
Rem pmodi 02/24/05 - Adding indexes
Rem jsadras 02/08/05 - add data move
Rem rpinnama 02/06/05 - rpinnama_add_upgrade_scripts
Rem rpinnama 02/02/05 - Created
Rem
Rem Upgrading types
Rem PURPOSE
Rem Used to hold a name value pair for a metric
Rem COLUMNS
Rem NAME : Name of the metric column
Rem VALUE : Value of the metric, will be stored as VARCHAR2
Rem as in current mgmt_metric_results
Rem index_position returns the index location of a particular name in the array
Rem value_of returns the value of a particular name in the array
CREATE OR REPLACE TYPE mgmt_namevalue_obj IS OBJECT
(
name VARCHAR2(64),
value VARCHAR2(4000),
STATIC FUNCTION NEW(p_name IN VARCHAR2,
p_value IN VARCHAR2
)
RETURN mgmt_namevalue_obj
)
/
CREATE OR REPLACE TYPE mgmt_namevalue_array as TABLE of mgmt_namevalue_obj
/
Rem PURPOSE
Rem mgmt_metric_value is used to hold the results of metric evaluation
Rem This will eventually replace mgmt_metric_results type
Rem Target_guid has been introduced into the object to support bulk collections
Rem COLUMNS
Rem TARGET_GUID : Target Guid for which the metric result has been obtained
Rem STATUS : Status of the message
Rem 0=Success, 1=Skip 2=Error
Rem STATUS_MESSAGE: Status message
Rem METRIC_VALUES : List of (metric_column,value) pairs
Rem The function metric_value returns the value of a particular metric column
Rem The function index_position returns the index in the array of the column
CREATE OR REPLACE TYPE mgmt_metric_value_obj IS OBJECT
(
target_guid RAW(16),
status NUMBER(1),
status_message VARCHAR2(256),
metric_values mgmt_namevalue_array,
collection_timestamp DATE,
STATIC FUNCTION NEW(p_target_guid IN RAW,
p_status IN NUMBER,
p_status_message IN VARCHAR2,
p_metric_values IN mgmt_namevalue_array,
p_collection_timestamp IN DATE DEFAULT NULL
)
RETURN mgmt_metric_value_obj,
MEMBER FUNCTION metric_value(p_metric_column IN VARCHAR2)
RETURN VARCHAR2,
MEMBER FUNCTION index_position(p_column_name IN VARCHAR2)
RETURN NUMBER
)
/
CREATE OR REPLACE TYPE mgmt_metric_value_array as TABLE of mgmt_metric_value_obj
/
Rem
Rem PURPOSE
Rem
Rem Used to hold Collection Tasks information in AQ
Rem
Rem COLUMNS
Rem
Rem TASK_ID : Task_ID of the collection
Rem MESSAGE_CODE : Message_code=0 Means Data
Rem Message_Code=N Where N>0 Means stop worker N
Rem SCHEDULED_TIME: Time when this task is to be run
Rem Time is in Repository Timezone
CREATE OR REPLACE TYPE mgmt_coll_queue_obj IS OBJECT
(
task_id NUMBER,
task_class NUMBER,
message_code NUMBER,
scheduled_time DATE,
STATIC FUNCTION NEW( p_task_id IN NUMBER,
p_task_class IN NUMBER,
p_message_code IN NUMBER,
p_scheduled_time IN DATE
)
RETURN mgmt_coll_queue_obj
)
/
BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE (
queue_table=> '&&EM_REPOS_USER' || '.MGMT_TASK_QTABLE',
sort_list=> 'PRIORITY,ENQ_TIME',
queue_payload_type=> '&&EM_REPOS_USER' || '.MGMT_COLL_QUEUE_OBJ') ;
DBMS_AQADM.CREATE_QUEUE ( queue_name=> '&&EM_REPOS_USER' || '.MGMT_TASK_Q',
queue_table=> '&&EM_REPOS_USER' || '.MGMT_TASK_QTABLE',
max_retries=>10,
retry_delay=>0,
retention_time=>0) ;
DBMS_AQADM.START_QUEUE( queue_name=> '&&EM_REPOS_USER' || '.MGMT_TASK_Q');
COMMIT ;
END ;
/
--
--This is to speed up collections which queries on
--collection time
-- Index is created here because the table is created
-- when queue is created which is after type is created
--
CREATE INDEX MGMT_TASK_QTABLE_IDX01
ON &&EM_REPOS_USER..mgmt_task_qtable(user_data.scheduled_time,user_data.task_class) ;
Rem PURPOSE
Rem Used to hold Collection scheduling information
Rem Column Values mean the same as in mgmt_blackout_schedule_record
Rem
Rem Use the NEW() function to create instances of MGMT_COLL_SCHEDULE object. This
Rem is important to support type evolution.
CREATE OR REPLACE TYPE mgmt_coll_schedule_obj IS OBJECT
(
frequency_code NUMBER,
start_time DATE,
end_time DATE,
execution_hours NUMBER,
execution_minutes NUMBER,
interval NUMBER,
months MGMT_INTEGER_ARRAY,
days MGMT_INTEGER_ARRAY,
STATIC FUNCTION NEW(p_frequency_code IN NUMBER,
p_execution_hours IN NUMBER,
p_execution_minutes IN NUMBER,
p_interval IN NUMBER,
p_months IN mgmt_integer_array,
p_days IN mgmt_integer_array,
p_start_time IN DATE,
p_end_time IN DATE
)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION one_time(p_start_time IN DATE)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION interval_schedule(p_interval IN NUMBER,
p_start_time IN DATE,
p_end_time IN DATE)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION daily_schedule(p_execution_hours IN NUMBER,
p_execution_minutes IN NUMBER,
p_start_time IN DATE,
p_end_time IN DATE)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION days_of_week(p_execution_hours IN NUMBER,
p_execution_minutes IN NUMBER,
p_days IN mgmt_integer_array,
p_start_time IN DATE,
p_end_time IN DATE)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION days_of_month(p_execution_hours IN NUMBER,
p_execution_minutes IN NUMBER,
p_days IN mgmt_integer_array,
p_start_time IN DATE,
p_end_time IN DATE)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION days_of_year(p_execution_hours IN NUMBER,
p_execution_minutes IN NUMBER,
p_days IN mgmt_integer_array,
p_months IN mgmt_integer_array,
p_start_time IN DATE,
p_end_time IN DATE)
RETURN mgmt_coll_schedule_obj,
STATIC FUNCTION on_demand_schedule
RETURN mgmt_coll_schedule_obj
);
/
CREATE TABLE MGMT_COLL_ITEMS
( target_type VARCHAR2(64) NOT NULL,
type_meta_ver VARCHAR2(8) DEFAULT '1.0' NOT NULL,
coll_name VARCHAR2(64) DEFAULT ' ' NOT NULL,
category_prop_1 VARCHAR2(64) DEFAULT ' ' NOT NULL,
category_prop_2 VARCHAR2(64) DEFAULT ' ' NOT NULL,
category_prop_3 VARCHAR2(64) DEFAULT ' ' NOT NULL,
category_prop_4 VARCHAR2(64) DEFAULT ' ' NOT NULL,
category_prop_5 VARCHAR2(64) DEFAULT ' ' NOT NULL,
is_enabled NUMBER(1) DEFAULT 1 NOT NULL,
is_required NUMBER(1) DEFAULT 0 NOT NULL,
CONSTRAINT mgmt_coll_items_pk
PRIMARY KEY (target_type,type_meta_ver,coll_name,
category_prop_1,
category_prop_2,
category_prop_3,
category_prop_4,
category_prop_5)
)
ORGANIZATION INDEX
MONITORING;
COMMENT ON TABLE mgmt_coll_items IS
'This table contains details of the default collections set up for
a target type.';
COMMENT ON COLUMN mgmt_coll_items.target_type IS
'Target type of the collection.';
COMMENT ON COLUMN mgmt_coll_items.type_meta_ver IS
'Target type meta version of the collection item.';
COMMENT ON COLUMN mgmt_coll_items.category_prop_1 IS
'Category property 1 ' ;
COMMENT ON COLUMN mgmt_coll_items.category_prop_2 IS
'Category property 2 ' ;
COMMENT ON COLUMN mgmt_coll_items.category_prop_3 IS
'Category property 3 ' ;
COMMENT ON COLUMN mgmt_coll_items.category_prop_4 IS
'Category property 4 ' ;
COMMENT ON COLUMN mgmt_coll_items.category_prop_5 IS
'Category property 5 ' ;
COMMENT ON COLUMN mgmt_coll_items.is_enabled IS
'Identifies whether the collection item is enabled or not';
COMMENT ON COLUMN mgmt_coll_items.is_required IS
'Identifies whether the collection item can be disabled or not,
Required collections cannot be disabled. Default is 0,
so collections can be disabled.';
CREATE TABLE mgmt_coll_item_metrics
(
target_type VARCHAR2(64) NOT NULL,
type_meta_ver VARCHAR2(8) DEFAULT '1.0' NOT NULL,
coll_name VARCHAR2(64) DEFAULT ' ' NOT NULL ,
metric_guid RAW(16) NOT NULL,
CONSTRAINT mgmt_coll_item_metrics_pk
PRIMARY KEY(target_type, type_meta_ver, coll_name, metric_guid)
)
ORGANIZATION INDEX
MONITORING;
COMMENT ON TABLE mgmt_coll_item_metrics IS
'Table to hold the metrics associated with a collection item' ;
COMMENT ON COLUMN mgmt_coll_item_metrics.target_type IS
'Target type' ;
COMMENT ON COLUMN mgmt_coll_item_metrics.type_meta_ver IS
'Target type meta version' ;
COMMENT ON COLUMN mgmt_coll_item_metrics.coll_name IS
'Name of the collection item.' ;
COMMENT ON COLUMN mgmt_coll_item_metrics.metric_guid IS
'Metric associated with the collection name.' ;
CREATE TABLE mgmt_coll_item_properties
( object_guid RAW(16) NOT NULL,
metric_guid RAW(16) NOT NULL,
coll_name VARCHAR2(64) DEFAULT ' ' NOT NULL,
property_name VARCHAR2(64) NOT NULL,
object_type NUMBER(1) DEFAULT 1,
property_value VARCHAR2(1024) NOT NULL,
CONSTRAINT mgmt_coll_items_properties_pk
PRIMARY KEY (object_guid,
metric_guid,
coll_name,
property_name)
)
ORGANIZATION INDEX
OVERFLOW INCLUDING property_value
MONITORING;
COMMENT ON TABLE mgmt_coll_item_properties IS
'This table contains the list of properties defined for a collection.';
COMMENT ON COLUMN mgmt_coll_item_properties.object_guid IS
'The guid for which the collection properties are stored.';
COMMENT ON COLUMN mgmt_coll_item_properties.metric_guid IS
'The metric guid for which the collection properties are stored.';
COMMENT ON COLUMN mgmt_coll_item_properties.coll_name IS
'The collection name associated with the collection property.';
COMMENT ON COLUMN mgmt_coll_item_properties.property_name IS
'The name of the collection property.';
COMMENT ON COLUMN mgmt_coll_item_properties.object_type IS
'The type of object for which the collection properties are stored.
Possible values are 1 - DEFAULT, 2- TARGET, 3 - TEMPLATE.';
COMMENT ON COLUMN mgmt_coll_item_properties.property_value IS
'The value of the collection property.';
CREATE TABLE mgmt_collections
(
object_guid RAW(16) NOT NULL,
coll_name VARCHAR2(64) DEFAULT ' ' NOT NULL,
object_type NUMBER(1) DEFAULT 1,
is_enabled NUMBER(1) DEFAULT 1 NOT NULL,
schedule_ex VARCHAR2(1024) DEFAULT ' ',
store_metric NUMBER(1) DEFAULT 1 NOT NULL,
upload_frequency NUMBER DEFAULT 1,
is_transient NUMBER(1) DEFAULT 0 NOT NULL,
frequency_code NUMBER DEFAULT 2 NOT NULL,
start_time DATE DEFAULT NULL,
end_time DATE DEFAULT NULL,
execution_hours NUMBER DEFAULT NULL,
execution_minutes NUMBER DEFAULT NULL,
interval NUMBER DEFAULT NULL,
months MGMT_INTEGER_ARRAY DEFAULT NULL,
days MGMT_INTEGER_ARRAY DEFAULT NULL
)
TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
COMMENT ON TABLE mgmt_collections IS
'This table contains details of the collections set up for a metric.';
COMMENT ON COLUMN mgmt_collections.object_guid IS
'The guid of the target/template/default associated with the collection.';
COMMENT ON COLUMN mgmt_collections.coll_name IS
'Collection Item' ;
COMMENT ON COLUMN mgmt_collections.object_type IS
'The type of guid stored in object_guid. Possible values are:
= 0, for default collection
= 1, for target
= 2, for template';
COMMENT ON COLUMN mgmt_collections.schedule_ex IS
'The schedule column used for agent collections';
COMMENT ON COLUMN mgmt_collections.store_metric IS
'The flag to indicate whether values returned by the collection should
be stored in the MGMT_METRIC_RAW.
1 - STORE
0 - DO NOT';
COMMENT ON COLUMN mgmt_collections.is_transient IS
'1=Transient collection, 0 - Non-transient collection DEFAULT,
The agent uses the value to determine whether to upload data or not' ;
COMMENT ON COLUMN mgmt_collections.frequency_code IS
' Refer mgmt_blackout_schedule' ;
COMMENT ON COLUMN mgmt_collections.start_time IS
'start time when collection becomes active' ;
COMMENT ON COLUMN mgmt_collections.end_time IS
'end time when collection becomes inactive ' ;
COMMENT ON COLUMN mgmt_collections.execution_hours IS
'hour of the day when to execute' ;
COMMENT ON COLUMN mgmt_collections.execution_minutes IS
'the minute of the hour when to execute' ;
COMMENT ON COLUMN mgmt_collections.interval IS
'interval in minutes' ;
COMMENT ON COLUMN mgmt_collections.months IS
' months of the year' ;
COMMENT ON COLUMN mgmt_collections.days IS
'days of the month' ;
CREATE TABLE mgmt_collection_tasks
(
task_id NUMBER NOT NULL,
task_type NUMBER NOT NULL,
priority NUMBER NOT NULL,
timezone_region VARCHAR2(64) NOT NULL,
frequency_code NUMBER DEFAULT 2 NOT NULL,
start_time DATE,
end_time DATE,
execution_hours NUMBER,
execution_minutes NUMBER,
interval NUMBER,
months MGMT_INTEGER_ARRAY ,
days MGMT_INTEGER_ARRAY ,
task_proc VARCHAR2(4000),
task_status NUMBER DEFAULT 0 NOT NULL,
task_start_time DATE,
last_collection_timestamp DATE,
next_collection_timestamp DATE,
error_message VARCHAR2(4000),
failures NUMBER,
worker_id NUMBER,
total_runs NUMBER DEFAULT 0 NOT NULL,
min_wait_time NUMBER DEFAULT 0 NOT NULL,
max_wait_time NUMBER DEFAULT 0 NOT NULL,
avg_wait_time NUMBER DEFAULT 0 NOT NULL,
min_run_time NUMBER DEFAULT 0 NOT NULL,
max_run_time NUMBER DEFAULT 0 NOT NULL,
avg_run_time NUMBER DEFAULT 0 NOT NULL
)
TABLESPACE &EM_ECM_DEPOT_TABLESPACE
MONITORING;
COMMENT ON TABLE mgmt_collection_tasks IS
' Table to Hold list of collection tasks' ;
COMMENT ON COLUMN mgmt_collection_tasks.task_id iS
' Unique Task Id' ;
COMMENT ON COLUMN mgmt_collection_tasks.frequency_code is
'Frequency of the schedule, see mgmt_blackout_schedule' ;
COMMENT ON COLUMN mgmt_collection_tasks.start_time IS
' Start time when the collection has to start' ;
COMMENT ON COLUMN mgmt_collection_tasks.end_time IS
' End time for repeating collections' ;
COMMENT ON COLUMN mgmt_collection_tasks.execution_hours IS
'Hour of the day when the collection is scheduled' ;
COMMENT ON COLUMN mgmt_collection_tasks.execution_minutes IS
'Minute of the hour when the collection is to end' ;
COMMENT ON COLUMN mgmt_collection_tasks.interval IS
' Interval in minutes for interval schedule' ;
COMMENT ON COLUMN mgmt_collection_tasks.months IS
'Months of the year' ;
COMMENT ON COLUMN mgmt_collection_tasks.days IS
'Days of the month' ;
COMMENT ON COLUMN mgmt_collection_tasks.task_proc IS
'procedure to be run, should accept task_id(number)
and context(mgmt_namevalue_array) as parameter' ;
COMMENT ON COLUMN mgmt_collection_tasks.task_status IS
'Status of the task 0=Scheduled, 1=Running' ;
COMMENT ON COLUMN mgmt_collection_tasks.task_start_time IS
'Time when the task started running' ;
COMMENT ON COLUMN mgmt_collection_tasks.last_collection_timestamp IS
'Last time the task was run' ;
COMMENT ON COLUMN mgmt_collection_tasks.next_collection_timestamp IS
'Next time when the task is scheduled' ;
COMMENT ON COLUMN mgmt_collection_tasks.error_message IS
'Last error message encountered when running this task' ;
COMMENT ON COLUMN mgmt_collection_tasks.failures IS
'failures encountered so far, reset on successful execution' ;
COMMENT ON COLUMN mgmt_collection_tasks.worker_id IS
' Worker running this task' ;
COMMENT ON COLUMN mgmt_collection_tasks.total_runs IS
' Total number of times the task has run' ;
COMMENT ON COLUMN mgmt_collection_tasks.min_wait_time IS
' Minimum wait time in seconds for the collection to be picked up by collections engine,
Wait time=collection_start_time-scheduled_time' ;
COMMENT ON COLUMN mgmt_collection_tasks.max_wait_time IS
' Maximum wait time in seconds for the collection to be picked up by collections engine,
Wait time=collection_start_time-scheduled_time' ;
COMMENT ON COLUMN mgmt_collection_tasks.avg_wait_time IS
'Average wait time in seconds for the collection to be picked up by collections engine,
Wait time=collection_start_time-scheduled_time' ;
COMMENT ON COLUMN mgmt_collection_tasks.min_run_time IS
'Minimum run time for the collection in seconds' ;
COMMENT ON COLUMN mgmt_collection_tasks.max_run_time IS
'Maximum run time for the collection in seconds' ;
COMMENT ON COLUMN mgmt_collection_tasks.avg_run_time IS
'Average run time for the collection in seconds' ;
CREATE TABLE mgmt_collection_task_context
( task_id NUMBER NOT NULL,
name VARCHAR2(64) NOT NULL,
value VARCHAR2(4000),
CONSTRAINT mgmt_collection_task_ctx_pk
PRIMARY KEY (task_id,name)
)
ORGANIZATION INDEX
OVERFLOW INCLUDING value
MONITORING ;
COMMENT ON COLUMN mgmt_collection_task_context.task_id IS
' Task id' ;
COMMENT ON COLUMN mgmt_collection_task_context.name IS
'name of the context' ;
COMMENT ON COLUMN mgmt_collection_task_context.value IS
'value of the context' ;
Rem
Rem table to store worker counts for each task class
Rem
CREATE TABLE mgmt_task_worker_counts
(task_class_list VARCHAR2(64) NOT NULL,
worker_count NUMBER(2) NOT NULL,
CONSTRAINT mgmt_task_worker_counts_pk
PRIMARY KEY (task_class_list)
)
ORGANIZATION INDEX
OVERFLOW INCLUDING worker_count
MONITORING ;
COMMENT ON COLUMN mgmt_task_worker_counts.task_class_list IS
' task class or a list of task class seperated by comma, no whitespace' ;
COMMENT ON COLUMN mgmt_task_worker_counts.worker_count IS
' Number of workers to start up for a given task class ' ;
CREATE SEQUENCE mgmt_task_sequence INCREMENT BY 1 START WITH 1
MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE
CACHE 5 NOORDER;
CREATE TABLE mgmt_collection_metric_tasks
(
target_guid RAW(16) NOT NULL,
coll_name VARCHAR2(64) DEFAULT ' ' NOT NULL,
metric_guid RAW(16) NOT NULL,
task_id NUMBER,
last_collected_timestamp DATE DEFAULT NULL,
status_message VARCHAR2(256) DEFAULT ' ',
CONSTRAINT mgmt_coll_metric_tasks_pk
PRIMARY KEY(target_guid,coll_name,metric_guid)
)
ORGANIZATION INDEX
MONITORING;
COMMENT ON TABLE mgmt_collection_metric_tasks IS
'The table stores the target-metric to collection task association.';
COMMENT ON COLUMN mgmt_collection_metric_tasks.target_guid IS
'The guid of the target associated with the collection.';
COMMENT ON COLUMN mgmt_collection_metric_tasks.metric_guid IS
'The GUID of the metric associated with the collection.
Now one task can have only one metric,
metric_guid is in tasks table to enforce that and for performance' ;
COMMENT ON COLUMN mgmt_collection_metric_tasks.coll_name IS
'The name of the collection.';
COMMENT ON COLUMN mgmt_collection_metric_tasks.task_id IS
'The collection task that executes this collection.
If collection for the metric is suspended then task_id is null';
CREATE TABLE mgmt_collection_workers
(
worker_id NUMBER(3) NOT NULL,
task_class_list VARCHAR2(64) DEFAULT 0 NOT NULL,
worker_status NUMBER DEFAULT 0 NOT NULL,
job_id NUMBER DEFAULT NULL,
worker_start_time DATE,
CONSTRAINT mgmt_collection_workers_pk
PRIMARY KEY (worker_id)
)
ORGANIZATION INDEX
MONITORING;
COMMENT ON COLUMN mgmt_collection_workers.worker_id IS
'Unique Worker id' ;
COMMENT ON COLUMN mgmt_collection_workers.job_id IS
'Job ID associated with the worker, null if the worker is run directly' ;
COMMENT ON COLUMN mgmt_collection_workers.worker_status IS
'Status of worker, 0=Worker Starting 1=Worker Started 2=Stop pending' ;
COMMENT ON COLUMN mgmt_collection_workers.worker_start_time IS
'Time when worker started running first
When worker status changed from 0 to 1' ;
COMMENT ON COLUMN mgmt_collection_workers.task_class_list IS
'Comma seperated list of task classes the worker can pick up' ;
Rem
Rem Indices for mgmt_collections table
Rem
ALTER TABLE mgmt_collections
ADD CONSTRAINT mgmt_collections_pk
PRIMARY KEY (object_guid, coll_name) ;
Rem
Rem Indices on mgmt_collection_tasks table
Rem
ALTER TABLE mgmt_collection_tasks
ADD CONSTRAINT mgmt_collection_tasks_pk
PRIMARY KEY (task_id) ;
CREATE INDEX mgmt_collection_tasks_idx01
ON mgmt_collection_tasks
(next_collection_timestamp) ;
Rem
Rem No index on task_status for now to benefit
Rem repo collections performance at tha cost of verify jobs
Rem
Rem
Rem Indices for mgmt_collection_metric_tasks
Rem
CREATE INDEX mgmt_coll_metric_tasks_idx_01
ON mgmt_collection_metric_tasks
(task_id, target_guid) ;
Rem
Rem ** Data Migration from old tables to new tables
Rem
Rem
Rem Migrate data from mgmt_metric_collections to the new tables
Rem
BEGIN
FOR rec in ( SELECT *
FROM mgmt_metric_collections
)
LOOP
BEGIN
INSERT INTO mgmt_collections
(object_guid, coll_name,object_type,
schedule_ex, frequency_code,start_time,
interval,
store_metric,
is_enabled)
VALUES
(rec.target_guid,rec.coll_name,2,
rec.schedule_ex,2,sys_extract_utc(systimestamp),
rec.schedule,
decode(rec.store_metric,'Y',1,0),
decode(rec.suspended,1,0,2)) ;
EXCEPTION
-- If there are 2 metrics in the collection then only the schedule of
-- the first is used.
WHEN DUP_VAL_ON_INDEX THEN NULL ;
END ;
INSERT INTO mgmt_collection_metric_tasks
(target_guid, metric_guid, coll_name,
last_collected_timestamp,status_message)
VALUES
(rec.target_guid,rec.metric_guid,rec.coll_name,
rec.last_collected_timestamp,rec.status_message) ;
END LOOP ;
EXECUTE IMMEDIATE ' drop table mgmt_metric_collections ' ;
END ;
/
--
-- Migrate data from mgmt_collection_properties to mgmt_coll_item_properties
--
BEGIN
INSERT INTO mgmt_coll_item_properties
(object_guid,object_type,metric_guid,
coll_name, property_name,property_value)
SELECT target_guid, 2, metric_guid,
coll_name, property_name, property_value
FROM mgmt_collection_properties ;
EXECUTE IMMEDIATE ' drop table mgmt_collection_properties ' ;
END ;
/
Rem
Rem Migrate data from mgmt_metric_thresholds to the new tables
Rem