Rem drv:
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/11.2.0.0/rac/rac_data_upgrade.sql /st_emdbsa_11.2/8 2009/11/10 16:17:25 ajdsouza Exp $
Rem
Rem rac_data_upgrade.sql
Rem
Rem Copyright (c) 2007, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem rac_data_upgrade.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem ajdsouza 10/30/09 - Bug fix 9055114 ( Response metric for cluster for metaver > 4.1 )
Rem shasingh 04/17/09 - host target type add callback
Rem pardutta 03/18/09 - mgmt_rac_services.server_group column size
Rem increased to 1024
Rem ajdsouza 02/17/09 - Big fix For Bug#7160826
Rem pbantis 10/06/08 - Call rac_post_creation.sql.
Rem shasingh 06/10/08 - lrg 3426315
Rem sadattaw 05/05/08 -
Rem ajdsouza 11/30/07 - removed end meta ver from response metric
Rem as response metric cannot be implemented
Rem as agent side metric, so reverting back to
Rem oms side metric
Rem ajdsouza 07/03/07 - Created
Rem
SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
Rem
Rem Register response metrics for cluster target
Rem
BEGIN
MGMT_METRIC.SET_END_TYPE_META_VER( 'cluster', 'Response', '5.1');
EMD_RAC.REGISTER_AVAILABILITY( 'cluster', 'Response', 'Status', 'host', '5.0', '5.0');
EMD_RAC.REGISTER_AVAILABILITY( 'cluster', 'Response', 'Status', 'host', '5.1', '5.1');
END;
/
-- Register RAC Database Configuration metadata for
-- target type rac_database and snapshot type oracle_racconfig
@&EM_SQL_ROOT/db/11.2.0.0/rac/rac_post_creation.sql
--------------------------------------------
-- Registering metadata Cluster Managaged Services for target type cluster and snapshot type mgmt_rac_services
--------------------------------------------
ALTER SESSION SET CONSTRAINTS = DEFERRED;
DECLARE
metadata_id RAW(16) := SYS_GUID();
BEGIN
-- Remove all related metadatas
DELETE FROM MGMT_ECM_SNAPSHOT_MD_TABLES WHERE METADATA_ID IN (SELECT METADATA_ID FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'cluster' AND SNAPSHOT_TYPE = 'mgmt_rac_services');
DELETE FROM MGMT_ECM_SNAPSHOT_METADATA WHERE TARGET_TYPE = 'cluster' AND SNAPSHOT_TYPE = 'mgmt_rac_services';
-- Register metadata as loader metadata
INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE)
VALUES ('mgmt_rac_services', 'cluster', 'L', metadata_id, 'Cluster Managaged Services', 'Y', 'Y', 'Y', 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'H');
-- Register metadata as presentation metadata
INSERT INTO MGMT_ECM_SNAPSHOT_METADATA (SNAPSHOT_TYPE, TARGET_TYPE, KIND, METADATA_ID, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, LINK_TABLE_NAME, AFTER_LOAD_PROC_NAME, AFTER_IMPORT_PROC_NAME, METADATA_VERSION, VIEW_URL, COMPARE_URL, HISTORY_URL, SAME_TARGET_COMPARE)
VALUES ('mgmt_rac_services', 'cluster', 'P', metadata_id, 'Cluster Managaged Services', 'Y', 'Y', 'Y', 'Y', 'Y', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'H');
-- Register metadata for table MGMT_RAC_SERVICES
INSERT INTO MGMT_ECM_SNAPSHOT_MD_TABLES (METADATA_ID, NAME, UI_NAME, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, PARENT_TABLE_NAME, IS_SINGLE_ROW, TBL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'Rac_Services', 'Y', 'Y', 'Y', 'Y', 'Y', NULL, 'N', 0);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'DATABASE_UNIQUE_NAME', 'Database Unique Name', 'S', '30', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', NULL, 0);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'SERVICE_NAME', 'Service Name', 'S', '30', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', NULL, 1);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'SERVICE_TYPE', 'Service Type', 'S', '30', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 2);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'ENABLED', 'Enabled', 'S', '30', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 3);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'TAFPOLICY', 'TAF Policy', 'S', '30', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 4);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'PREFERRED_INSTANCES', 'Preferred Instances', 'S', '1024', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 5);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'AVAILABLE_INSTANCES', 'Available Instances', 'S', '1024', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 6);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'RUNNING_INSTANCES', 'Running Instances', 'S', '1024', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 7);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'CLUSTER_NAME', 'Cluster Name', 'S', '64', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 8);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'SERVER_GROUP', 'Server Group', 'S', '1024', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 9);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'RESOURCE_NAME', 'Resource Name', 'S', '64', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 10);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'SERVICE_CENTRIC_TYPE', 'Service Centric Type', 'S', '30', 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 11);
INSERT INTO MGMT_ECM_SNAPSHOT_MD_COLUMNS (METADATA_ID, TABLE_NAME, NAME, UI_NAME, TYPE, TYPE_FORMAT, UI_ON, COMPARE_ON, COMPARE_UI_ON, HISTORY_ON, HISTORY_UI_ON, IS_KEY, IS_CONTEXT, IS_SUMMARY, IS_CHILD_LINK, LINK_COLUMN_NAME, COL_ORDER)
VALUES (metadata_id, 'MGMT_RAC_SERVICES', 'SERVICE_CARDINALITY', 'Service Cardinality', 'N', NULL, 'Y', 'Y', 'Y', 'Y', 'Y', 'N', 'N', 'N', 'N', NULL, 12);
END;
/
ALTER SESSION SET CONSTRAINTS = IMMEDIATE;
COMMIT;
-------------------------------------------------------------------------
-- Update of Source of the Repository Metric for Cluster Private Interconnect Traffic
------------------------------------------------------------------------
DECLARE
METRIC_GUID_HOST_NETWORK_RDWR VARCHAR2(32) := ' ';
METRIC_GUID_CLUSTER_PRIV VARCHAR2(32) := ' ';
BEGIN
METRIC_GUID_HOST_NETWORK_RDWR := mgmt_metric.get_metric_guid( P_TARGET_TYPE =>'host',
P_METRIC_NAME => 'Network',
P_METRIC_COLUMN =>'readRate');
METRIC_GUID_CLUSTER_PRIV := mgmt_metric.get_metric_guid( P_TARGET_TYPE =>'cluster',
P_METRIC_NAME => 'priv_intrcon',
P_METRIC_COLUMN =>' ');
UPDATE MGMT_METRICS
SET SOURCE =
' SELECT ta.source_target_guid target_guid, SUM(c.value) privTransferRate
FROM mgmt_current_metrics c, mgmt_current_availability a,
mgmt_targets t2,
mgmt_target_assocs ta, mgmt_target_assoc_defs td,
mgmt_ha_cls_intr_conn i, mgmt_ecm_gen_snapshot s
WHERE c.metric_guid = HEXTORAW('''||METRIC_GUID_HOST_NETWORK_RDWR||''')
AND c.target_guid = t2.target_guid
AND a.target_guid = t2.target_guid
AND a.current_status > 0
AND t2.target_guid = ta.assoc_target_guid
AND ta.assoc_guid = td.assoc_guid
AND td.assoc_def_name = ''contains''
AND td.scope_target_type = '' ''
AND (i.ic_node = ''global'' or i.ic_node = t2.target_name)
AND i.ic_public = ''NO''
AND i.ecm_snapshot_id = s.snapshot_guid
AND s.snapshot_type = ''ha_cls_intrconn''
AND s.is_current = ''Y''
AND s.target_guid = ta.source_target_guid
AND i.ic_name = c.key_value
AND t2.target_type = ''host''
GROUP BY ta.source_target_guid '
WHERE metric_guid = HEXTORAW(METRIC_GUID_CLUSTER_PRIV)
AND type_meta_ver IN ('4.0','4.1','4.2','5.0');
EXCEPTION
WHEN OTHERS THEN NULL;
END;
/
COMMIT;
----------------------------------------------------
-- Bug#7160826
DECLARE
l_schedule mgmt_coll_schedule_obj ;
CURSOR c1 IS SELECT DISTINCT m.type_meta_ver
FROM mgmt_metrics m
WHERE m.target_type = 'cluster'
AND m.metric_name = 'priv_intrcon'
AND m.metric_column = ' '
AND m.type_meta_ver != '4.0'
AND NOT EXISTS
(
SELECT 1
FROM mgmt_coll_items c
WHERE c.target_type = 'cluster'
AND c.type_meta_ver = m.type_meta_ver
AND c.coll_name = 'priv_intrcon_collection'
);
BEGIN
-- Define schedule
l_schedule := mgmt_coll_schedule_obj.interval_schedule(5,sysdate,null) ;
FOR x in C1 LOOP
mgmt_collection.add_default_collection
(p_target_type=>'cluster' ,
p_type_meta_ver=> x.type_meta_ver,
p_coll_name=>'priv_intrcon_collection',
p_metrics_list=>mgmt_short_string_array('priv_intrcon'),
p_coll_schedule=>l_schedule) ;
END LOOP;
END ;
/
COMMIT;
BEGIN
-- register callbac to enable collections for newer metvers that are added
-- Register a target type addition callback
mgmt_target.add_tgttype_addition_callback(
p_callback_name => 'mgmt_rac.handle_cluster_tgttype_add');
COMMIT;
END;
/
-- End of metadata registration for metadata Cluster Managaged Services
BEGIN
-- Register the event based callback procedure.
MGMT_LOADER.register_cluster_crs_event_cbk('emd_rac.rac_crs_callback_proc');
--Register Host target type add callback
mgmt_admin.add_target_addition_callback('emd_rac.host_add_callback', mgmt_global.g_host_target_type);
-- Register event based processing command .
mgmt_jobs.register_command('proccessCRSEvent',
'oracle.sysman.emo.rac.commands.EventBasedProcessing');
commit;
END;
/
SET DEFINE ON
-- Update derived materialized views
BEGIN ECM_UTIL.UPDATE_ECM_METADATA; END;
/