Rem drv: Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/rac/rac_pkgbodys.sql /st_emdbsa_11.2/54 2011/07/21 19:51:26 shasingh Exp $ Rem Rem rac_pkgbodys.sql Rem Rem Copyright (c) 2002, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem rac_pkgbodys.sql - getA Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem shasingh 12/05/10 - Backport shasingh_bug-10118817_1 from main Rem rsamaved 01/20/11 - this is a temporary fix for bug 10252580 Rem ajdsouza 01/10/11 - change delimiter for crs_event message fields to Rem #sEp# Rem rsamaved 05/28/10 - fix nls bug Rem sksantha 04/28/10 - change RACONENODE to RACOneNode Rem prjaiswa 04/14/10 - get_is_rac_listener added Rem pardutta 11/05/09 - RAC One Node: Add get_database_type that Rem determines if the database is RAC,RACOne or SI. Rem ajdsouza 10/30/09 - Bug fix 9055114 ( Response metric for cluster for metaver > 4.1 ) Rem make changes to REGISTER_AVAILABILITY Rem shasingh 07/14/09 - delete add node alert from mgmt_violations on Rem host add callback Rem shasingh 06/30/09 - pass rac db memebr count info for status update Rem event Rem pardutta 06/08/09 - Uses CRS_SERVER_POOL_STATE_CHANGE to execute Rem command if the operation is ASSIGNED. Rem shasingh 06/05/09 - Consider of all status for gpnp job Rem shasingh 04/10/09 - scan listener relocate support Rem shasingh 04/03/09 - nslid for add node alert action msg Rem rsamaved 03/28/09 - add node alert message fixes. Rem ajdsouza 03/25/09 - Bug fix# 7321196 Rem shasingh 03/25/09 - bug : 8362748 Rem pardutta 03/24/09 - Handle server pool size change and database Rem server pool change events. Rem shasingh 03/16/09 - Third party callBack support Rem mpawelko 03/13/09 - XbranchMerge: bug 6699755 pass targetType into getStgPerfValues Rem rsamaved 03/14/09 - perf fix for metrics query Rem ysun 03/09/09 - add cell related alerts Rem manaraya 03/05/09 - Bug 7931482 Rem shasingh 02/25/09 - show alert for missing node Rem ajdsouza 02/17/09 - added subs to get resourcename and is has managed Rem ajdsouza 02/09/09 - bug fix 6911416 Rem rsamaved 01/06/09 - fix listener alerts Rem yemeng 12/23/08 - bug 7554977 Rem shasingh 11/19/08 - support for event based db srv pools update Rem shasingh 11/10/08 - modified proc for support of error handling of Rem reconfig activities Rem shasingh 09/22/08 - crs event support for new node add and em config Rem on same Rem ajdsouza 10/30/08 - bug fix 6911416 Rem ajdsouza 10/30/08 - add resource procedures for cluster ui Rem ajdsouza 09/17/08 - tuning of interconnects metric Rem shasingh 09/05/08 - changed 'SEVICE_CENTRIC' db type string with Rem 'POLICY_MANAGED' Rem rsamaved 08/11/08 - fix alerts query - impact column Rem sadattaw 07/23/08 - fixes from 11gc Rem shasingh 07/21/08 - lrg 3399897: Disable tracing Rem sadattaw 05/16/08 - merge from 11gc Rem shasingh 05/14/08 - improved logic Rem sadattaw 05/10/08 - Rem shasingh 03/18/08 - GPnP: Improved perf Rem sadattaw 12/12/07 - Rem shasingh 11/27/07 - event based target processing Rem shasingh 08/09/07 - XbranchMerge Rem shasingh_11g_19509a_targets_discovery from main Rem rsamaved 05/18/07 - add impact column to cluster alerts query Rem sadattaw 03/14/07 - added keyvalue to service queries results Rem ajdsouza 02/08/07 - bug fix 5647975,5848019 Rem rsamaved 01/29/07 - account for new col added to get_alerts_info Rem result set Rem rsamaved 01/27/07 - Rem pboopath 01/24/07 - XbranchMerge pboopath_wo_msg-bug_5684931 from Rem main Rem kramarat 01/25/07 - XbranchMerge kramarat_cluster_db_services_fix2 Rem from main Rem kramarat 01/12/07 - XbranchMerge kramarat_cluster_db_services_fix Rem from main Rem sadattaw 01/04/07 - adding cluster services queries Rem rsamaved 01/19/07 - add impact column for alerts table Rem sadattaw 01/04/07 - adding cluster services queries Rem shasingh 01/15/07 - Rem rsamaved 01/15/07 - fix compile error Rem shasingh 10/11/06 - Rem ajdsouza 08/23/06 - fix bug# 5087180 Rem ysun 01/03/06 - Rem xuliu 09/20/05 - fix 4613787 Rem ysun 09/19/05 - Rem ysun 08/08/05 - add new API Rem chyu 06/28/05 - New repmgr header impl Rem xuliu 05/05/05 - rollup alerts&policy in all targets Rem xuliu 04/28/05 - return cluster target in getAllTargets Rem xuliu 04/14/05 - cluster db version Rem pbantis 04/07/05 - Fix getAllTargetsInCluster(). Rem pbantis 04/01/05 - Get policy violations and compliance. Rem ysun 02/25/05 - addStorageInfo Rem ysun 02/03/05 - update association Rem ysun 01/06/05 - add all alerts api Rem ysun 11/23/04 - add topology support Rem dsahrawa 11/04/04 - use mgmt_jobs.get_job_schedule_record Rem ysun 10/27/04 - procedures for roll up metrics Rem xuliu 10/07/04 - 3695898: nls alert msg Rem dcawley 07/08/04 - Increase user name size Rem dsahrawa 06/22/04 - use MGMT_JOBS.get_job_schedule_record Rem ysun 04/26/04 - fix typo Rem ysun 04/21/04 - fix time zone Rem ysun 11/18/03 - update master call back Rem ysun 11/13/03 - remove setemcontext per Diarmuid Rem ysun 11/14/03 - add timezone info Rem ysun 10/15/03 - add call back Rem ysun 10/14/03 - update tbspace metric info Rem ysun 09/29/03 - add credentials support Rem streddy 09/25/03 - Always add r/s metric Rem ysun 09/25/03 - pass out by date Rem ysun 09/18/03 - Rem skini 09/09/03 - Change in schedule_record structure Rem ysun 08/27/03 - add updateRacTargets Rem ysun 08/06/03 - add version info Rem ysun 06/18/03 - update dynamic properties Rem xuliu 05/12/03 - all targets in cluster Rem ysun 04/14/03 - availability Rem xuliu 04/09/03 - listener alerts Rem xuliu 04/01/03 - sync with single db Rem ysun 12/02/02 - update alerts numbers Rem ysun 10/30/02 - clean up Rem rpinnama 09/25/02 - Remove hardcoded references of mgmt_rep Rem ysun 09/05/02 - add cluster db list Rem ysun 08/28/02 - update rac package Rem ysun 06/26/02 - ysun_rac_support_2 Rem ysun 06/24/02 - Created CREATE OR REPLACE PACKAGE BODY EMD_RAC as G_MODULE constant VARCHAR2(100) := 'emd_rac'; -- -- PROCEDURE: get_overview_info -- returns general info like status and avail pct -- PROCEDURE get_overview_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name OUT VARCHAR2, last_updated_time OUT DATE, current_status OUT INTEGER, avail_pct OUT NUMBER, time_zone OUT VARCHAR2, target_guid OUT VARCHAR2 ) IS BEGIN -- target guid, type display name, last load time begin SELECT target_guid, type_display_name, last_load_time, NVL(to_char(from_tz(CAST(last_load_time AS TIMESTAMP), timezone_region), 'TZD'), 'GMT ' || to_char(from_tz(CAST(last_load_time AS TIMESTAMP), timezone_region), 'TZR')) INTO target_guid, type_display_name, last_updated_time, time_zone FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; end; -- current status current_status := mgmt_target.get_avail_current_status(target_guid); -- availability percentage avail_pct := ROUND(mgmt_target.get_avail_pct_value(target_guid, 1), 2); END get_overview_info; -- -- PROCEDURE: get_db_overview_info -- -- PURPOSE: -- returns db general info -- PROCEDURE get_db_overview_info (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name OUT VARCHAR2, last_updated_time OUT DATE, current_status OUT INTEGER, avail_pct OUT NUMBER, db_home OUT VARCHAR2, cluster_name OUT VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, service_name OUT VARCHAR2, archive_mode OUT VARCHAR2, tbsp_severity OUT NUMBER, tbsp_count OUT NUMBER, time_zone OUT VARCHAR2, target_guid OUT VARCHAR2, auto_impl_enable OUT VARCHAR2, key_profile_enable OUT VARCHAR2, key_profile_count OUT NUMBER) IS BEGIN -- target guid, type display name, last load time -- current status -- availability percentage get_overview_info( target_name_in, target_type_in, type_display_name, last_updated_time, current_status, avail_pct, time_zone, target_guid); /* get database information */ -- dbHome, dbVersion, dbName, clusterName, archive mode get_db_properties( target_guid, db_home, cluster_name, db_version, db_name, service_name, archive_mode); get_tbsp_severity ( target_guid, tbsp_severity, tbsp_count); get_key_profile ( target_guid, auto_impl_enable,key_profile_enable, key_profile_count); END get_db_overview_info; procedure getStorageInfo(targetName IN VARCHAR2, targetType IN VARCHAR2, tbspCount OUT NUMBER, tbspSeverity OUT NUMBER, stgPerfCount OUT NUMBER, stgPerfSeverity OUT NUMBER) IS k_error_msg_prefix CONSTANT VARCHAR2(128) := 'Oracle Enterprise Manager: ERROR in emd_rac.getStroageInfo()'; ---------------- -- variables ---------------- instanceVersion VARCHAR2(4000); targetGuid VARCHAR2(32); meta_ver VARCHAR2(8); cat_prop_1 VARCHAR2(64); cat_prop_2 VARCHAR2(64); cat_prop_3 VARCHAR2(64); cat_prop_4 VARCHAR2(64); cat_prop_5 VARCHAR2(64); BEGIN ------------- -- target_guid BEGIN SELECT target_guid, TYPE_META_VER, CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3, CATEGORY_PROP_4, CATEGORY_PROP_5 INTO targetGuid, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5 FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE mgmt_global.target_does_not_exist; END; -------------------- -- instance version ------------------- BEGIN instanceVersion := emd_database.getDbInstanceVersion(targetGuid); EXCEPTION WHEN OTHERS THEN instanceVersion := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get instance version.'); END; ---------------- -- get performance (storage-centric) severity info --------------- BEGIN emd_database.getStgPerfValues(targetGuid, targetType, instanceVersion, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, stgPerfCount, stgPerfSeverity); EXCEPTION WHEN OTHERS THEN stgPerfCount := -1; stgPerfSeverity := mgmt_global.g_severity_unknown; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to storage (perf) summary.'); END; ---------------- -- get problem tablespace counts ---------------- BEGIN tbspCount := -1; SELECT count(distinct(tbspname)) INTO tbspCount FROM (SELECT sev.key_value tbspname FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(targetGuid) and (met.metric_name = 'problemTbsp' or met.metric_name = 'problemTbsp10iLoc' or met.metric_name = 'problemTbsp10iDct' or met.metric_name = 'problemSegTbsp') and met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND (met.metric_column = 'pctUsed' or met.metric_column = 'bytesFree' or met.metric_column = 'segsChunkSmlCnt' or met.metric_column = 'segsMaxExtsCnt') and met.metric_guid = sev.metric_guid); END; ---------------- -- get problem tablespace severity ---------------- BEGIN tbspSeverity := mgmt_global.g_severity_unknown; SELECT NVL(MAX(severity_code),15) INTO tbspSeverity FROM (SELECT /*+ INDEX(sev) */ max(sev.severity_code) severity_code FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(targetGuid) and (met.metric_name = 'problemTbsp' or met.metric_name = 'problemTbsp10iLoc' or met.metric_name = 'problemTbsp10iDct' or met.metric_name = 'problemSegTbsp') and (met.metric_column = 'pctUsed' or met.metric_column = 'bytesFree' or met.metric_column = 'segsChunkSmlCnt' or met.metric_column = 'segsMaxExtsCnt') AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = sev.metric_guid ); END; END; -- PROCEDURE: get_alerts_info -- -- PURPOSE: -- returns rac_database and its components rac_instance's alerts list -- PROCEDURE get_alerts_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); -- Severity, Target, Type, Open Since, Message OPEN alerts_info FOR select a.target_name, a.target_type, a.type_display_name, b.severity_code, b.message, b.collection_timestamp, d.value, d.collection_timestamp, c.metric_name, c.metric_column, b.key_value, c.metric_label, c.column_label, b.message_nlsid, b.message_params, case when c.metric_name != 'adrAlertLogIncidentError' then NULL else (select distinct p.string_value from mgmt_current_metrics p, mgmt_metrics q where p.metric_guid = q.metric_guid and q.metric_name = 'adrAlertLogIncidentError' and q.metric_column = 'impact' and p.key_value = b.key_value) end as impact, e.metric_label_nlsid, c.column_label_nlsid from mgmt_targets a, mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, mgmt_metrics e where a.target_guid = b.target_guid and b.metric_guid = c.metric_guid and b.metric_guid = d.metric_guid(+) and b.target_guid = d.target_guid(+) and b.key_value = d.key_value(+) and c.type_meta_ver = a.type_meta_ver AND (c.category_prop_1 = a.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = a.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = a.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = a.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = a.category_prop_5 OR c.category_prop_5 = ' ') AND e.type_meta_ver = c.type_meta_ver AND e.target_type = c.target_type AND e.metric_name = c.metric_name AND e.metric_column = ' ' AND (e.category_prop_1 = c.category_prop_1 OR e.category_prop_1 = ' ') AND (e.category_prop_2 = c.category_prop_2 OR e.category_prop_2 = ' ') AND (e.category_prop_3 = c.category_prop_3 OR e.category_prop_3 = ' ') AND (e.category_prop_4 = c.category_prop_4 OR e.category_prop_4 = ' ') AND (e.category_prop_5 = c.category_prop_5 OR e.category_prop_5 = ' ') AND (a.target_guid IN (select l_target_guid from dual union all SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid and member_target_guid not in (select target_guid from mgmt_target_properties where property_name='TargetDeleteTimestamp'))); --ORDER BY 4, 6 desc; -- SELECT DISTINCT tgt.target_name, tgt.target_type, tgt.type_display_name, -- met.metric_name, met.metric_column, met.metric_label, met.column_label, cs.key_value, -- cs.collection_timestamp, -- cs.severity_code, cs.severity_guid, -- cs.message, cs.load_timestamp -- FROM mgmt_targets tgt, mgmt_metrics met, mgmt_current_severity cs -- WHERE severity_code!=15 AND -- tgt.target_guid = cs.target_guid AND -- met.metric_guid = cs.metric_guid -- AND (tgt.target_guid IN -- (SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid) -- OR -- tgt.target_guid = l_target_guid); END get_alerts_info; PROCEDURE get_all_alerts_in_cluster( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); -- Severity, Target, Type, Open Since, Message OPEN alerts_info FOR select a.target_name, a.target_type, a.type_display_name, b.severity_code, b.message, b.collection_timestamp, d.value, d.collection_timestamp, c.metric_name, c.metric_column, b.key_value, c.metric_label, c.column_label, b.message_nlsid, b.message_params, case when c.metric_name != 'adrAlertLogIncidentError' then NULL else (select p.string_value from mgmt_current_metrics p, mgmt_metrics q where p.metric_guid = q.metric_guid and q.metric_name = 'adrAlertLogIncidentError' and q.metric_column = 'impact' and p.key_value = b.key_value) end as impact, e.metric_label_nlsid, c.column_label_nlsid from mgmt_targets a, mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, mgmt_metrics e where a.target_guid = b.target_guid and b.metric_guid = c.metric_guid and b.metric_guid = d.metric_guid(+) and b.target_guid = d.target_guid(+) and b.key_value = d.key_value(+) and c.type_meta_ver = a.type_meta_ver AND (c.category_prop_1 = a.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = a.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = a.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = a.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = a.category_prop_5 OR c.category_prop_5 = ' ') AND e.type_meta_ver = c.type_meta_ver AND e.target_type = c.target_type AND e.metric_name = c.metric_name AND e.metric_column = ' ' AND (e.category_prop_1 = c.category_prop_1 OR e.category_prop_1 = ' ') AND (e.category_prop_2 = c.category_prop_2 OR e.category_prop_2 = ' ') AND (e.category_prop_3 = c.category_prop_3 OR e.category_prop_3 = ' ') AND (e.category_prop_4 = c.category_prop_4 OR e.category_prop_4 = ' ') AND (e.category_prop_5 = c.category_prop_5 OR e.category_prop_5 = ' ') AND (a.host_name IN (SELECT mt.host_name host_name FROM mgmt_target_assocs a, mgmt_target_assoc_defs d, mgmt_targets ct, mgmt_targets mt WHERE d.assoc_def_name = 'contains' AND d.scope_target_type = ' ' AND a.assoc_guid = d.assoc_guid AND ct.target_guid = a.source_target_guid AND mt.target_guid = a.assoc_target_guid AND ct.target_guid = l_target_guid)) AND (a.target_type IN (mgmt_global.g_host_target_type, 'oracle_database', 'oracle_listener', 'osm_instance', mgmt_global.g_rac_database_target_type)); END get_all_alerts_in_cluster; -- -- PROCEDURE: get_listener_alerts_info -- -- PURPOSE: -- returns list of alerts on all hosts in the cluster -- target_name_in : cluster name -- target_type_in : always be 'cluster' -- PROCEDURE get_listener_alerts_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); -- Severity, Target, Type, Open Since, Message OPEN alerts_info FOR select a.target_name, a.target_type, a.type_display_name, b.severity_code, b.message, b.collection_timestamp, d.value, d.collection_timestamp, c.metric_name, c.metric_column, b.key_value, c.metric_label, c.column_label, b.message_nlsid, b.message_params, '' impact, e.metric_label_nlsid, c.column_label_nlsid from mgmt_targets a, mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, mgmt_metrics e where a.target_guid = b.target_guid and b.metric_guid = c.metric_guid and b.metric_guid = d.metric_guid(+) and b.target_guid = d.target_guid(+) and b.key_value = d.key_value(+) and c.type_meta_ver = a.type_meta_ver AND (c.category_prop_1 = a.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = a.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = a.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = a.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = a.category_prop_5 OR c.category_prop_5 = ' ') AND e.type_meta_ver = c.type_meta_ver AND e.target_type = c.target_type AND e.metric_name = c.metric_name AND e.metric_column = ' ' AND (e.category_prop_1 = c.category_prop_1 OR e.category_prop_1 = ' ') AND (e.category_prop_2 = c.category_prop_2 OR e.category_prop_2 = ' ') AND (e.category_prop_3 = c.category_prop_3 OR e.category_prop_3 = ' ') AND (e.category_prop_4 = c.category_prop_4 OR e.category_prop_4 = ' ') AND (e.category_prop_5 = c.category_prop_5 OR e.category_prop_5 = ' ') AND a.target_type = 'oracle_listener' AND (a.host_name IN (SELECT target_name FROM mgmt_targets, mgmt_target_memberships WHERE target_guid = member_target_guid AND composite_target_guid=l_target_guid) ); --ORDER BY 4, 6 desc; END get_listener_alerts_info; -- -- PROCEDURE: get_cell_alerts_info -- -- PURPOSE: -- returns list of cell alerts on all hosts in the cluster -- target_name_in : cluster name -- target_type_in : always be 'cluster' -- PROCEDURE get_cell_alerts_info( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); -- Severity, Target, Type, Open Since, Message OPEN alerts_info FOR select a.target_name, a.target_type, a.type_display_name, b.severity_code, b.message, b.collection_timestamp, d.value, d.collection_timestamp, c.metric_name, c.metric_column, b.key_value, c.metric_label, c.column_label, b.message_nlsid, b.message_params, '' impact, e.metric_label_nlsid, c.column_label_nlsid from mgmt_targets a, mgmt_current_severity b, mgmt_metrics c, mgmt_current_metrics d, mgmt_metrics e where a.target_guid = b.target_guid and b.metric_guid = c.metric_guid and b.metric_guid = d.metric_guid(+) and b.target_guid = d.target_guid(+) and b.key_value = d.key_value(+) and c.type_meta_ver = a.type_meta_ver AND (c.category_prop_1 = a.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = a.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = a.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = a.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = a.category_prop_5 OR c.category_prop_5 = ' ') AND e.type_meta_ver = c.type_meta_ver AND e.target_type = c.target_type AND e.metric_name = c.metric_name AND e.metric_column = ' ' AND (e.category_prop_1 = c.category_prop_1 OR e.category_prop_1 = ' ') AND (e.category_prop_2 = c.category_prop_2 OR e.category_prop_2 = ' ') AND (e.category_prop_3 = c.category_prop_3 OR e.category_prop_3 = ' ') AND (e.category_prop_4 = c.category_prop_4 OR e.category_prop_4 = ' ') AND (e.category_prop_5 = c.category_prop_5 OR e.category_prop_5 = ' ') AND a.target_type = 'oracle_cell' AND (a.host_name IN (SELECT target_name FROM mgmt_targets, mgmt_target_memberships WHERE target_guid = member_target_guid AND composite_target_guid=l_target_guid) ); --ORDER BY 4, 6 desc; END get_cell_alerts_info; -- Procedure get_cluster_db_list -- -- PURPOSE: this function is called for getting -- the cursor which gives the list of db's -- in the cluster -- -- IN parameters: -- target_name_in IN cluster target name -- target_type_in IN cluster target name -- list_type_in IN -- OUT CURSOR_TYPE for list of dbs PROCEDURE get_cluster_db_list( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, list_type_in IN VARCHAR2, target_summary_out OUT CURSOR_TYPE ) IS v_cluster_target_guid MGMT_TARGETS.TARGET_GUID%TYPE := NULL; BEGIN DELETE FROM mgmt_v_cluster_member_list; DELETE FROM mgmt_v_rac_member_list; DELETE FROM mgmt_v_rac_assoc_member_list; DELETE FROM mgmt_v_cluster_rac_pol_detail; v_cluster_target_guid := MGMT_TARGET.GET_TARGET_GUID(target_name_in,target_type_in); -- host members of the cluster INSERT INTO mgmt_v_cluster_member_list ( member_target_guid , member_target_type , host_name ) SELECT member_target_guid, member_target_type, mt.host_name FROM mgmt_target_memberships m, mgmt_targets mt WHERE m.composite_target_guid = v_cluster_target_guid AND m.member_target_guid = mt.target_guid; -- oracle_database+rac_database members of the cluster INSERT INTO mgmt_v_rac_member_list ( composite_target_guid, target_guid , target_type , host_name , version ) SELECT m.composite_target_guid, m.member_target_guid, m.member_target_type, ct.host_name, p.property_value FROM mgmt_target_memberships m, mgmt_targets mt, mgmt_targets ct, mgmt_v_cluster_member_list cl, mgmt_target_properties p WHERE m.composite_target_type = 'rac_database' AND m.member_target_type = 'oracle_database' AND m.member_target_guid = mt.target_guid AND cl.host_name = ct.host_name AND ct.target_guid = m.composite_target_guid AND p.target_guid(+) = m.member_target_guid AND p.property_name(+) = 'Version' UNION SELECT t.target_guid, t.target_guid, t.target_type, t.host_name, NULL FROM mgmt_targets t, mgmt_v_cluster_member_list cl WHERE t.host_name = cl.host_name AND t.target_type = 'rac_database'; -- oracle_database+rac_database members of the cluster from assocs table INSERT INTO mgmt_v_rac_assoc_member_list ( composite_target_guid, target_guid , target_type , host_name ) SELECT m.source_target_guid, m.assoc_target_guid, mt.target_type, ct.host_name FROM mgmt_target_assocs m, mgmt_targets mt, mgmt_targets ct, mgmt_v_cluster_member_list cl WHERE m.assoc_guid = MGMT_ASSOC.g_contains_guid AND ct.target_guid = m.source_target_guid AND mt.target_guid = m.assoc_target_guid AND ct.target_type = 'rac_database' AND mt.target_type = 'oracle_database' AND cl.host_name = ct.host_name UNION SELECT t.target_guid, t.target_guid, t.target_type, t.host_name FROM mgmt_targets t, mgmt_v_cluster_member_list cl WHERE t.host_name = cl.host_name AND t.target_type = 'rac_database'; INSERT INTO mgmt_v_cluster_rac_pol_detail ( composite_target_guid, information_count, warning_count, critical_count, compliance_score ) SELECT t.composite_target_guid , SUM(DECODE(max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,non_exempt_violations_logged,0)), SUM(DECODE(max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,non_exempt_violations_logged,0)), SUM(DECODE(max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,non_exempt_violations_logged,0)), ROUND(AVG(compliance_score)) FROM mgmt_v_rac_assoc_member_list t, mgmt_policy_assoc_eval_summ e, mgmt_policies r WHERE r.policy_guid = e.policy_guid AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY AND e.target_guid = t.target_guid GROUP BY t.composite_target_guid; OPEN target_summary_out FOR SELECT tgt.target_name, tgt.target_type, tgt.type_display_name, tgt.host_name, ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, 1), 0) AS avail_pct, mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status, NVL(sev_detail.warning_count, 0) AS warning_count, tgt.last_load_time, NVL(sev_detail.critical_count, 0) AS critical_count, p.property_value AS target_oraclehome, NVL(policy_detail.information_count, 0) AS policy_information_count, NVL(policy_detail.warning_count, 0) AS policy_warning_count, NVL(policy_detail.critical_count, 0) AS policy_critical_count, NVL(policy_detail.compliance_score, -1) AS target_compliance_score, p1.version AS target_version FROM mgmt_targets tgt, mgmt_target_properties p, ( SELECT DISTINCT m.composite_target_guid , m.version FROM mgmt_v_rac_member_list m WHERE m.target_type='oracle_database' AND m.version IS NOT NULL ) p1, ( SELECT rm.composite_target_guid, SUM(DECODE(s.severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(s.severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM mgmt_current_severity s, mgmt_v_rac_member_list rm WHERE s.target_guid = rm.target_guid GROUP BY rm.composite_target_guid ) sev_detail, mgmt_v_cluster_rac_pol_detail policy_detail, mgmt_v_cluster_member_list mem WHERE tgt.host_name = mem.host_name AND tgt.target_type=list_type_in AND tgt.target_guid=p.target_guid (+) AND p.property_name(+)='OracleHome' AND tgt.target_guid=p1.composite_target_guid(+) AND tgt.target_guid = sev_detail.composite_target_guid (+) AND tgt.target_guid = policy_detail.composite_target_guid(+) ORDER BY tgt.type_display_name, tgt.target_name; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END get_cluster_db_list; -- get the topology target list for a cluster -- for now oracle_database, rac_database and asm are considered PROCEDURE get_cluster_topo_targets( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_summary_out OUT CURSOR_TYPE ) IS BEGIN OPEN target_summary_out FOR SELECT tgt.target_name, tgt.target_type, tgt.target_guid, tgt.type_display_name, tgt.host_name, mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status, NVL(sev_detail.warning_count, 0) AS warning_count, NVL(sev_detail.critical_count, 0) AS critical_count FROM mgmt_targets tgt, (SELECT target_guid, SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM (SELECT target_guid, severity_code FROM mgmt_current_severity UNION ALL SELECT composite_target_guid, severity_code FROM mgmt_current_severity, mgmt_target_memberships WHERE member_target_guid=target_guid AND composite_target_type = 'rac_database' AND member_target_type = 'oracle_database') GROUP BY target_guid) sev_detail, (SELECT t.host_name host_name from mgmt_target_memberships m, mgmt_targets t where composite_target_name='crac_cluster' and composite_target_type='cluster' and m.member_target_name=t.target_name and m.member_target_type=t.target_type ) mem WHERE NOT EXISTS ( select m.member_target_guid from mgmt_target_memberships m where m.composite_target_type='rac_database' and tgt.target_guid=m.member_target_guid) AND tgt.target_guid = sev_detail.target_guid (+) AND tgt.host_name = mem.host_name AND tgt.target_type in ('rac_database', 'oracle_database') ORDER BY tgt.type_display_name, tgt.target_name; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END get_cluster_topo_targets; -- get dynamic properties from components PROCEDURE get_db_dyn_properties( target_guid_in IN VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, archive_mode OUT VARCHAR2) IS CURSOR bCursor (tg IN VARCHAR2) IS SELECT p.property_name, NVL(p.property_value, ' ') FROM mgmt_target_properties p, mgmt_target_memberships m WHERE m.composite_target_guid = HEXTORAW(tg) AND p.target_guid=m.member_target_guid AND p.property_name IN ('Version','DBName', 'log_archive_mode'); property_name VARCHAR2(64); property_value VARCHAR2(1024); BEGIN db_version := ' '; db_name := ' '; archive_mode := ' '; OPEN bCursor( target_guid_in); LOOP FETCH bCursor INTO property_name, property_value; exit when bCursor%NOTFOUND; IF property_name = 'Version' THEN db_version := property_value; ELSIF property_name = 'DBName' THEN db_name := property_value; ELSIF property_name = 'log_archive_mode' THEN archive_mode := property_value; END IF; END LOOP; CLOSE bCursor; END; -- get dynamic properties from components PROCEDURE get_db_dyn_properties( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, archive_mode OUT VARCHAR2) IS target_guid mgmt_targets.target_guid%TYPE; BEGIN target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); get_db_dyn_properties( target_guid, db_version, db_name, archive_mode ); END; -- -- PROCEDURE: get_rac_database_properties -- -- PURPOSE: -- returns the rac database target properties -- PROCEDURE get_db_properties( target_guid_in IN VARCHAR2, oracle_home OUT VARCHAR2, cluster_name OUT VARCHAR2, db_version OUT VARCHAR2, db_name OUT VARCHAR2, service_name OUT VARCHAR2, archive_mode OUT VARCHAR2) IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT property_name, NVL(property_value, ' ') FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) AND property_name IN ('OracleHome', 'ClusterName', 'ServiceName'); property_name VARCHAR2(64); property_value VARCHAR2(1024); BEGIN oracle_home := ' '; cluster_name := ' '; db_version := ' '; db_name := ' '; service_name := ' '; archive_mode := ' '; OPEN aCursor (target_guid_in); LOOP FETCH aCursor INTO property_name, property_value; exit when aCursor%NOTFOUND; IF property_name = 'OracleHome' THEN oracle_home := property_value; ELSIF property_name = 'ClusterName' THEN cluster_name := property_value; ELSIF property_name = 'ServiceName' THEN service_name := property_value; END IF; END LOOP; CLOSE aCursor; get_db_dyn_properties( target_guid_in, db_version, db_name, archive_mode); END; PROCEDURE get_dbname( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, db_name OUT VARCHAR2, db_domain OUT VARCHAR2) IS target_guid mgmt_targets.target_guid%TYPE; CURSOR bCursor (tg IN VARCHAR2) IS SELECT p.property_name, NVL(p.property_value, ' ') FROM mgmt_target_properties p, mgmt_target_memberships m WHERE m.composite_target_guid = HEXTORAW(tg) AND p.target_guid=m.member_target_guid AND p.property_name IN ('DBName', 'DBDomain'); property_name VARCHAR2(64); property_value VARCHAR2(1024); BEGIN target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); db_name := ' '; db_domain := ' '; OPEN bCursor( target_guid); LOOP FETCH bCursor INTO property_name, property_value; exit when bCursor%NOTFOUND; IF property_name = 'DBName' THEN db_name := property_value; ELSIF property_name = 'DBDomain' THEN db_domain := property_value; END IF; END LOOP; CLOSE bCursor; END; -- PURPOSE -- Calculate the key SQL Profile for Auo Sql Tune -- PARAMETERS -- targetGuid Target guid -- keyProfileEnable check if Key Sql Profile is enabled or disabled -- keyprofileCount check the number of Key Sql Profile procedure get_key_profile( targetGuid IN VARCHAR2, autoImplEnable OUT VARCHAR2, keyProfileEnable OUT VARCHAR2, keyProfileCount OUT NUMBER) IS CURSOR kCursor (tg IN VARCHAR2) IS SELECT NVL(data.value, -1) , NVL(data.string_value, ' ') , met.metric_column FROM mgmt_current_metrics data, mgmt_metrics met WHERE met.metric_name = 'key_profiles' AND met.metric_guid = data.metric_guid AND data.target_guid = HEXTORAW(tg); tempProfileCount NUMBER; tempProfileEnable VARCHAR2(20); tempMetricColumn VARCHAR2(64); begin keyProfileCount := -1; keyProfileEnable := ' '; autoImplEnable := ' '; OPEN kCursor (targetGuid); LOOP FETCH kCursor INTO tempProfileCount, tempProfileEnable, tempMetricColumn; exit when kCursor%NOTFOUND; IF tempMetricColumn = 'key_profiles_count' THEN keyProfileCount := tempProfileCount; ELSIF tempMetricColumn = 'key_profiles_enable' THEN keyProfileEnable := tempProfileEnable; ELSIF tempMetricColumn = 'auto_impl_enable' THEN autoImplEnable := tempProfileEnable; END IF; END LOOP; CLOSE kCursor; end; procedure get_tbsp_severity( targetGuid IN VARCHAR2, tbspSeverity OUT NUMBER, tbspCount OUT NUMBER) IS meta_ver VARCHAR2(8); cat_prop_1 VARCHAR2(64); cat_prop_2 VARCHAR2(64); cat_prop_3 VARCHAR2(64); cat_prop_4 VARCHAR2(64); cat_prop_5 VARCHAR2(64); begin BEGIN SELECT TYPE_META_VER, CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3, CATEGORY_PROP_4, CATEGORY_PROP_5 INTO meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5 FROM mgmt_targets WHERE target_guid = HEXTORAW(targetGuid); EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; tbspSeverity := 15; SELECT NVL(MAX(severity_code),15) into tbspSeverity FROM (SELECT /*+ INDEX(sev) */ max(sev.severity_code) severity_code FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(targetGuid) and (met.metric_name = 'problemTbsp' or met.metric_name = 'problemTbsp10iLoc' or met.metric_name = 'problemTbsp10iDct' or met.metric_name = 'problemSegTbsp') and (met.metric_column = 'pctUsed' or met.metric_column = 'segsChunkSmlCnt' or met.metric_column = 'segsMaxExtsCnt') AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = sev.metric_guid ); tbspCount := 0; SELECT count(distinct(tbspname)) into tbspCount FROM (SELECT sev.key_value tbspname FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(targetGuid) and (met.metric_name = 'problemTbsp' or met.metric_name = 'problemTbsp10iLoc' or met.metric_name = 'problemTbsp10iDct' or met.metric_name = 'problemSegTbsp') and met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND (met.metric_column = 'pctUsed' or met.metric_column = 'segsChunkSmlCnt' or met.metric_column = 'segsMaxExtsCnt') and met.metric_guid = sev.metric_guid); end; -- PURPOSE -- Calculate the new Rollup severity state of a target based -- on the dependency metric severities using OR predicate logic. This -- proc assumes that at least one of the member severities have to in CLEAR -- state for the overall target to be in CLEAR state. The possible severity -- codes it returns are CLEAR, CRITICAL and WARNING -- PARAMETERS -- v_target_guid Target guid -- v_metric_guid Metric guid -- v_dependency_list List of metric dependencies and their severity -- values -- v_sev_code New severity code -- v_message Message for the severity -- v_message_nlsid Optional NLS ID for the message -- v_message_params Message parameters for NLS message PROCEDURE rollup_or_eval_proc(v_target_guid IN RAW, v_metric_guid IN RAW, v_dependency_list IN MGMT_METRIC_DETAILS_ARRAY, v_sev_code OUT NUMBER, v_message OUT VARCHAR2, v_message_nlsid OUT VARCHAR2, v_message_params OUT VARCHAR2) IS BEGIN -- initialize it to unknown v_sev_code := MGMT_GLOBAL.G_SEVERITY_UNKNOWN; -- Return unknown if we are called with empty list IF (v_dependency_list.COUNT = 0) THEN return; END IF; -- Use AND logic statuses FOR i IN 1..v_dependency_list.COUNT LOOP v_sev_code := v_dependency_list(i).severity_code; IF (v_sev_code = MGMT_GLOBAL.G_SEVERITY_CLEAR) THEN EXIT; END IF; END LOOP; -- Construct a message v_message := 'Alerts for Rolled up Health Check Metric'; END rollup_or_eval_proc; -- -- PROCEDURE: db_availability -- -- PURPOSE: -- evaluate rac db availability -- PROCEDURE register_availability ( targetType IN VARCHAR2, metricName IN VARCHAR2, metricColumn IN VARCHAR2, memTargetType IN VARCHAR2, metaVer IN VARCHAR2, endMetaVersion IN VARCHAR2 DEFAULT NULL ) IS l_mguid RAW(16); l_rac_rs MGMT_METRIC_DESC; l_dependency_list MGMT_METRIC_DESC_ARRAY; l_metric_column_list MGMT_METRIC_COLUMN_ARRAY; l_dummy NUMBER(10); BEGIN BEGIN SELECT DISTINCT metric_guid INTO l_mguid FROM MGMT_METRICS WHERE target_type = targetType AND metric_name = metricName AND metric_column = metricColumn AND type_meta_ver=metaVer; EXCEPTION WHEN NO_DATA_FOUND THEN l_mguid := NULL; -- Create the metric if one does not exist for this meta_ver IF l_mguid IS NULL THEN l_metric_column_list := MGMT_METRIC_COLUMN_ARRAY(); l_metric_column_list.extend(1) ; l_metric_column_list(1) := MGMT_METRIC_COLUMN_OBJ.NEW( p_column_name => MGMT_GLOBAL.G_AVAIL_METRIC_COLUMN, p_column_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_EVENT, p_source => MGMT_SEVERITY_HELPER.G_OR_AVAIL_EVAL_PROC_NAME, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_SEVEVAL_PLSQL, p_column_label => 'Status', p_column_label_nlsid => 'resp_status'); BEGIN mgmt_metric.create_metric( p_target_type => targetType, p_metric_name => MGMT_GLOBAL.G_AVAIL_METRIC_NAME, p_metric_label => 'Response', p_metric_label_nlsid => 'resp', p_type_meta_ver => metaVer, p_metric_type => MGMT_GLOBAL.G_METRIC_TYPE_REPOS_TABLE, p_source => MGMT_SEVERITY_HELPER.G_OR_AVAIL_EVAL_PROC_NAME, p_source_type => MGMT_GLOBAL.G_METRIC_SOURCE_SEVEVAL_PLSQL, p_metric_column_list => l_metric_column_list, p_is_repository => 1, p_end_type_meta_ver => endMetaVersion ); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END; END IF; END; -- Check if dependencies have been created and create them BEGIN IF l_mguid IS NULL THEN l_mguid := mgmt_metric.get_metric_guid(targetType,metricName,metricColumn) ; END IF; EXCEPTION WHEN MGMT_GLOBAL.no_such_metric_found THEN RETURN; END; BEGIN SELECT 1 INTO l_dummy FROM mgmt_metric_dependency_def md WHERE md.target_type = targetType AND md.type_meta_ver= metaVer AND md.metric_guid = l_mguid AND md.dep_target_type = memTargetType AND ROWNUM = 1; EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN -- register RAC instance Response/status as a dependency for -- RAC database Response/Status l_rac_rs := MGMT_METRIC_DESC.NEW(targetType, metricName, metricColumn, metaVer); l_dependency_list := MGMT_METRIC_DESC_ARRAY(); l_dependency_list.extend(1); l_dependency_list(1) := MGMT_METRIC_DESC.NEW( memTargetType, metricName, metricColumn, metaVer); MGMT_TARGET.ADD_METRIC_SEVERITY_DEPS(l_rac_rs, l_dependency_list); EXCEPTION WHEN MGMT_GLOBAL.already_exists OR MGMT_GLOBAL.no_such_metric_found THEN NULL; END; END; END REGISTER_AVAILABILITY; -- Function cluterware_status -- -- PURPOSE: this function is called for getting the status of cluster target -- , status of member host targets from the crs status metric -- -- IN parameters: -- cluster target_name IN The p_cname -- OUT parameters: -- cluster status as mgmt_cluster_status_table -- FUNCTION clusterware_status ( p_cname IN MGMT_TARGETS.TARGET_NAME%TYPE ) RETURN mgmt_cluster_status_table IS l_cluster_status_table mgmt_cluster_status_table; l_cls_members mgmt_cls_member_table; l_cluster_alerts mgmt_cluster_collection_table; l_host_alerts mgmt_cluster_collection_table; l_cluster_fail_cnt_table mgmt_cluster_collection_table; l_cluster_crs_stat_table mgmt_cluster_collection_table; l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_metric_clustercrs MGMT_METRICS.METRIC_GUID%TYPE; l_metric_clusterfn MGMT_METRICS.METRIC_GUID%TYPE; l_metric_hostcrs MGMT_METRICS.METRIC_GUID%TYPE; l_metric_cssnodes MGMT_METRICS.METRIC_GUID%TYPE; l_CRSVersionCategory MGMT_TARGET_PROPERTIES.PROPERTY_VALUE%TYPE; CURSOR C1( v_target_guid MGMT_TARGETS.TARGET_GUID%TYPE, v_metric_guid MGMT_METRICS.METRIC_GUID%TYPE ) IS SELECT mgmt_cluster_collection_object ( b.target_guid, NULL, NULL, b.key_value, b.value, b.collection_timestamp ) FROM mgmt_current_metrics b WHERE b.metric_guid = v_metric_guid AND b.target_guid = v_target_guid AND ROWNUM = 1; CURSOR C2 (v_target_guid MGMT_TARGETS.TARGET_GUID%TYPE ) IS SELECT property_value FROM mgmt_target_properties WHERE target_guid = v_target_guid AND property_name = 'CRSVersionCategory' AND ROWNUM = 1; BEGIN IF p_cname IS NULL THEN RETURN l_cluster_status_table; END IF; l_target_guid := mgmt_target.get_target_guid(p_cname, 'cluster'); l_metric_clustercrs := mgmt_metric.get_metric_guid('cluster','CRSStatus','CRS_status'); l_metric_clusterfn := mgmt_metric.get_metric_guid('cluster','CRSStatus','CRS_failed_node_count'); l_metric_hostcrs := mgmt_metric.get_metric_guid('host','CRSStatus','CRS_status'); BEGIN OPEN C2(l_target_guid); FETCH C2 INTO l_CRSVersionCategory; CLOSE C2; EXCEPTION WHEN OTHERS THEN l_CRSVersionCategory := NULL; END; -- get the members of the cluster target SELECT mgmt_cls_member ( a.composite_target_guid, a.composite_target_name, a.composite_target_type, a.member_target_guid, a.member_target_name, a.member_target_type ) BULK COLLECT INTO l_cls_members FROM ( SELECT a.composite_target_guid, a.composite_target_name, a.composite_target_type, a.member_target_guid, a.member_target_name, a.member_target_type FROM mgmt_target_memberships a WHERE a.composite_target_guid = l_target_guid UNION SELECT a.target_guid, a.target_name, a.target_type, a.target_guid, a.target_name, a.target_type FROM mgmt$target a WHERE a.target_guid = l_target_guid ) a; -- get the alerts for the cluster and its members -- css_nodes metric if cluster is 11gR2 -- CRSStatus is cluster is < 11gR2 SELECT mgmt_cluster_collection_object ( s.target_guid, p_cname, 'cluster', s.key_value, -- 25-critical, 20 warning , unknown DECODE(s.VIOLATION_LEVEL,25,0,20,0,0), s.collection_timestamp ) BULK COLLECT INTO l_cluster_alerts FROM mgmt_current_violation s WHERE s.target_guid = l_target_guid AND s.policy_guid = l_metric_clustercrs AND s.violation_type IN (0,1,2); BEGIN l_metric_cssnodes := mgmt_metric.get_metric_guid('cluster','css_nodes','css_server_state'); EXCEPTION WHEN OTHERS THEN l_metric_cssnodes := NULL; END; IF l_CRSVersionCategory = '11gR2' AND l_metric_cssnodes IS NOT NULL THEN SELECT mgmt_cluster_collection_object ( d.member_target_guid, d.member_target_name, d.member_target_type, s.key_value, -- 25-critical, 20 warning , unknown DECODE(s.VIOLATION_LEVEL,25,0,20,0,0), s.collection_timestamp ) BULK COLLECT INTO l_host_alerts FROM mgmt_current_violation s, mgmt_target_properties p, TABLE ( CAST ( l_cls_members AS mgmt_cls_member_table ) ) d WHERE s.target_guid = l_target_guid AND p.target_guid = d.member_target_guid AND p.property_name = 'NodeName' AND s.key_value = p.property_value AND s.policy_guid = l_metric_cssnodes -- 0 threshold violation, 1 availability 2 resource AND s.violation_type IN (0,1,2); ELSE SELECT mgmt_cluster_collection_object ( d.member_target_guid, d.member_target_name, d.member_target_type, s.key_value, -- 25-critical, 20 warning , unknown DECODE(s.VIOLATION_LEVEL,25,0,20,0,0), s.collection_timestamp ) BULK COLLECT INTO l_host_alerts FROM mgmt_current_violation s, TABLE ( CAST ( l_cls_members AS mgmt_cls_member_table ) ) d WHERE s.target_guid = d.member_target_guid AND s.policy_guid = l_metric_hostcrs AND s.violation_type IN (0,1,2); END IF; -- get cluvfy based status for cluster -- Get the metric colum data in to the 3 temp tables OPEN C1(l_target_guid, l_metric_clusterfn); FETCH C1 BULK COLLECT INTO l_cluster_fail_cnt_table; CLOSE C1; OPEN C1(l_target_guid, l_metric_clustercrs); FETCH C1 BULK COLLECT INTO l_cluster_crs_stat_table; CLOSE C1; IF l_cluster_alerts IS NOT NULL AND l_cluster_alerts.COUNT > 0 THEN IF l_host_alerts IS NOT NULL AND l_host_alerts.COUNT > 0 THEN l_host_alerts.EXTEND; l_host_alerts(l_host_alerts.LAST) := l_cluster_alerts(l_cluster_alerts.FIRST); ELSE l_host_alerts := l_cluster_alerts; END IF; END IF; --DBMS_OUTPUT.PUT_LINE('size of host table is '||l_host_alerts(l_host_alerts.FIRST).target_type); -- Fetch the cluster status from the 3 temp tables SELECT /*+ cardinality (8) cardinality (m 8) cardinality ( a 2 ) cardinality ( c 1) cardinality ( d 1) */ mgmt_cluster_status_object ( m.member_target_guid, m.member_target_name, m.member_target_type, a.key_value, a.collection_timestamp, -- script returms -1 for error,EmlConstants.AVAIL_ERROR is 2 , so decode DECODE(m.member_target_type, -- for the cluster if there is no alert then target is up provided crsstatus metric -- has been collected 'cluster',DECODE(a.value,NULL,NVL(d.value,0),a.value), DECODE(a.value,NULL,1,a.value) ), NVL(c.value,0) ) BULK COLLECT INTO l_cluster_status_table FROM TABLE ( CAST ( l_host_alerts AS mgmt_cluster_collection_table ) ) a, TABLE ( CAST ( l_cluster_fail_cnt_table AS mgmt_cluster_collection_table ) ) c, TABLE ( CAST ( l_cluster_crs_stat_table AS mgmt_cluster_collection_table ) ) d, TABLE ( CAST ( l_cls_members AS mgmt_cls_member_table ) ) m WHERE m.member_target_guid = a.target_guid(+) AND m.composite_target_guid = c.target_guid(+) AND m.composite_target_guid = d.target_guid(+); RETURN l_cluster_status_table; EXCEPTION WHEN OTHERS THEN RAISE; END clusterware_status; -- PROCEDURE getAllTargetsInCluster -- PURPOSE: -- Return all targets in the cluster specified by target_name_in and target_type_in -- target_type_in should always be 'cluster' PROCEDURE getAllTargetsInCluster( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, type_display_name_out OUT VARCHAR2, target_summary_out OUT CURSOR_TYPE ) IS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN -- Get the cluster's display name and guid BEGIN SELECT type_display_name, target_guid INTO type_display_name_out, l_target_guid FROM mgmt_targets WHERE target_name = target_name_in AND target_type = target_type_in; EXCEPTION WHEN NO_DATA_FOUND THEN raise MGMT_GLOBAL.target_does_not_exist; END; -- now for the given cluster find all the targets related data OPEN target_summary_out FOR SELECT tgt.target_name || NVL2(p2.property_value,' **','') as target_name, tgt.target_type, tgt.type_display_name, tgt.host_name, ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, 1), 0) AS avail_pct, mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status, NVL(sev_detail.warning_count, 0) AS warning_count, tgt.last_load_time, NVL(sev_detail.critical_count, 0) AS critical_count, p.property_value AS target_oraclehome, NVL(policy_detail.information_count, 0) AS policy_information_count, NVL(policy_detail.warning_count, 0) AS policy_warning_count, NVL(policy_detail.critical_count, 0) AS policy_critical_count, NVL(policy_detail.compliance_score, -1) AS target_compliance_score FROM mgmt_targets tgt, mgmt_target_properties p, mgmt_target_properties p2, (SELECT target_guid, SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_WARNING,1,0)) AS warning_count, SUM(DECODE(severity_code,MGMT_GLOBAL.G_SEVERITY_CRITICAL,1,0)) AS critical_count FROM ( SELECT target_guid, severity_code FROM mgmt_current_severity WHERE target_guid IN (SELECT target_guid FROM mgmt_targets WHERE host_name in (SELECT host_name FROM mgmt_targets WHERE target_guid IN (SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid)) AND target_type IN ('rac_database', 'oracle_database', 'host', 'osm_instance', 'oracle_listener')) UNION ALL SELECT source_target_guid as target_guid, severity_code FROM mgmt_current_severity, mgmt_flat_target_assoc WHERE assoc_target_guid = target_guid AND is_membership=1 AND target_guid IN (SELECT target_guid FROM mgmt_targets WHERE host_name in (SELECT host_name FROM mgmt_targets WHERE target_guid IN (SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid)) AND target_type IN ('rac_database', 'oracle_database', 'host', 'osm_instance', 'oracle_listener')) ) GROUP BY target_guid ) sev_detail, (SELECT target_guid as tguid, SUM(DECODE(max_violation_level,MGMT_GLOBAL.G_SEVERITY_INFORMATIONAL,non_exempt_violations_logged,0)) information_count, SUM(DECODE(max_violation_level,MGMT_GLOBAL.G_SEVERITY_WARNING,non_exempt_violations_logged,0)) warning_count, SUM(DECODE(max_violation_level,MGMT_GLOBAL.G_SEVERITY_CRITICAL,non_exempt_violations_logged,0)) critical_count, ROUND(AVG(compliance_score)) as compliance_score FROM ( --Self target level viol data SELECT distinct e.target_guid, r.policy_guid, e.max_violation_level, e.non_exempt_violations_logged, e.compliance_score, e.target_guid as policy_target_guid FROM mgmt_policy_assoc_eval_summ e, mgmt_policies r WHERE r.policy_guid = e.policy_guid AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY AND e.target_guid IN (SELECT target_guid FROM mgmt_targets WHERE host_name in (SELECT host_name FROM mgmt_targets WHERE target_guid IN (SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid)) AND target_type IN ('rac_database', 'oracle_database', 'host', 'osm_instance', 'oracle_listener')) --Include the member target level policy data UNION SELECT distinct mem.source_target_guid as target_guid, r.policy_guid, e.max_violation_level, e.non_exempt_violations_logged, e.compliance_score, mem.assoc_target_guid as policy_target_guid FROM mgmt_flat_target_assoc mem, mgmt_policy_assoc_eval_summ e, mgmt_policies r WHERE mem.assoc_target_guid=e.target_guid AND mem.is_membership=1 AND r.policy_guid = e.policy_guid AND r.policy_type = MGMT_GLOBAL.G_TYPE_POLICY AND mem.source_target_guid IN (SELECT target_guid FROM mgmt_targets WHERE host_name in (SELECT host_name FROM mgmt_targets WHERE target_guid IN (SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid)) AND target_type IN ('rac_database', 'oracle_database', 'host', 'osm_instance', 'oracle_listener')) ) GROUP BY target_guid) policy_detail WHERE tgt.target_guid = sev_detail.target_guid (+) AND tgt.host_name IN (SELECT host_name FROM mgmt_targets WHERE target_guid IN (SELECT member_target_guid FROM mgmt_target_memberships WHERE composite_target_guid=l_target_guid)) AND tgt.target_guid=p.target_guid (+) AND p.property_name(+)='OracleHome' AND tgt.target_guid=p2.target_guid (+) AND p2.property_name(+)='TargetDeleteTimestamp' AND tgt.target_guid = policy_detail.tguid(+) AND tgt.target_type IN ('rac_database', 'oracle_database', 'host', 'osm_instance', 'oracle_listener') ORDER BY tgt.type_display_name, tgt.target_name; END getAllTargetsInCluster; -- update the target properties in the repository -- could be a rac_database target or oracle_database target PROCEDURE pre_update_target( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN MGMT_TARGET_PROPERTY_LIST, p_credentials IN MGMT_TARGET_CRED_ARRAY ) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_target_guid MGMT_TARGETS.target_guid%TYPE; BEGIN -- Check that the user has permissions to modify the target IF MGMT_USER.has_priv(l_current_user, 'OPERATOR_TARGET', p_target_name, p_target_type) = 0 THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'The current user does not have enough privileges to perform this operation'); END IF; -- assume the member target already exists in the repository SELECT target_guid INTO l_target_guid FROM MGMT_TARGETS WHERE target_name=p_target_name AND target_type=p_target_type; -- Try updating the properties for the target. If that doesn't work, insert -- them IF p_properties IS NOT NULL THEN FOR i IN 1..p_properties.count LOOP UPDATE MGMT_TARGET_PROPERTIES SET property_value=p_properties(i).property_value WHERE target_guid=l_target_guid AND property_name=p_properties(i).property_name AND property_type=p_properties(i).property_type; IF SQL%ROWCOUNT=0 THEN INSERT INTO MGMT_TARGET_PROPERTIES(target_guid, property_name, property_type, property_value) VALUES (l_target_guid, p_properties(i).property_name, p_properties(i).property_type, p_properties(i).property_value); END IF; END LOOP; END IF; -- update the credentials as well mgmt_credential.set_target_credentials(p_credentials); END pre_update_target; -- This procedure will be only sent to one of the agent to update the rac instance and database info -- p_target_name is the rac instance name -- p_target_type is the rac instance type -- p_update_instance is to indicate if the instance info should be updated to the agent -- p_monitoring_credentials is to carry the monitoring credentials PROCEDURE update_one_rac_target( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN SMP_EMD_NVPAIR_ARRAY, p_update_instance IN INTEGER DEFAULT 0 ) IS l_job_targets MGMT_JOB_TARGET_LIST; l_job_params MGMT_JOB_PARAM_LIST; l_job_name MGMT_JOB.job_name%TYPE; l_job_id MGMT_JOB.job_id%TYPE; l_execution_id MGMT_JOB_EXEC_SUMMARY.execution_id%TYPE; l_schedule MGMT_JOB_SCHEDULE_RECORD; l_property_names MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS(); l_property_values MGMT_JOB_VECTOR_PARAMS := MGMT_JOB_VECTOR_PARAMS(); l_count NUMBER; BEGIN -- assume the repository info is ready: rac_database and oracle_database -- AND synchronous update has failed -- Submit a job to asynchronously update the EMD l_job_targets := MGMT_JOB_TARGET_LIST(); l_job_targets.extend(1); l_job_targets(1) := MGMT_JOB_TARGET_RECORD(p_target_name, p_target_type); IF p_properties IS NOT NULL THEN l_count := p_properties.count; l_property_names.extend(l_count); l_property_values.extend(l_count); FOR i IN 1..p_properties.count LOOP BEGIN l_property_names(i) := p_properties(i).name; l_property_values(i) := p_properties(i).value; END; END LOOP; END IF; l_job_params := MGMT_JOB_PARAM_LIST(); l_job_params.extend(5); l_job_params(1) := MGMT_JOB_PARAM_RECORD('propertyNames', MGMT_JOBS.PARAM_TYPE_VECTOR, null, l_property_names); l_job_params(2) := MGMT_JOB_PARAM_RECORD('propertyValues', MGMT_JOBS.PARAM_TYPE_VECTOR, null, l_property_values); l_job_params(3) := MGMT_JOB_PARAM_RECORD('updateInstance', 1, p_update_instance, null); l_job_params(4) := MGMT_JOB_PARAM_RECORD('targetName', 1, p_target_name, null); l_job_params(5) := MGMT_JOB_PARAM_RECORD('targetType', 1, p_target_type, null); l_schedule := MGMT_JOBS.get_job_schedule_record(MGMT_JOBS.ONE_TIME_FREQUENCY_CODE, SYSDATE, null, 0, 0, 0, null, null, MGMT_JOBS.TIMEZONE_REPOSITORY, 0, 0, null); l_job_name := UPDATE_RAC_JOB_TYPE || SYS_GUID(); MGMT_JOBS.submit_job(l_job_name, UPDATE_RAC_JOB_DESCRIPTION, UPDATE_RAC_JOB_TYPE, l_job_targets, l_job_params, l_schedule, l_job_id, l_execution_id, null, MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY); END update_one_rac_target; -- PROCEDURE: update_rac_target -- -- PURPOSE: -- update the specified target to the Agents. Since RAC_DATABASE is a multiple agent target, -- the properties will be retrieved from the repoistory and updated on multiple agents. -- -- IN Parameters: -- p_target_name: The rac target name -- p_target_type: The rac target type -- p_update_intance: decide if we want to update instance members -- if the interface is used, then only the properties which can be applied to all the hosts should be provided. -- OUT Parameters: -- NONE -- PROCEDURE update_rac_target( p_target_name IN VARCHAR2, p_target_type IN VARCHAR2, p_properties IN SMP_EMD_NVPAIR_ARRAY, p_update_instance IN INTEGER DEFAULT 0 ) IS CURSOR memCursor ( targetName IN VARCHAR2, targetType IN VARCHAR2) IS SELECT member_target_name, member_target_type FROM mgmt_target_memberships WHERE composite_target_name = targetName AND composite_target_type = targetType; l_inst_target_name mgmt_targets.target_name%TYPE; l_inst_target_type mgmt_targets.target_type%TYPE; BEGIN -- get all the members and call update_one_rac_target OPEN memCursor ( p_target_name, p_target_type ); LOOP FETCH memCursor INTO l_inst_target_name, l_inst_target_type; exit when memCursor%NOTFOUND; dbms_output.put_line('l_inst_target_name is:'||l_inst_target_name); update_one_rac_target( l_inst_target_name, l_inst_target_type, p_properties, p_update_instance); END LOOP; CLOSE memCursor; END update_rac_target; -- Procedure ADD_RAC_CALLBACK -- -- PURPOSE: The proc called when the rac_database target is added. -- IN parameters: -- target_name_in IN The target name -- target_type_in IN The target type -- target_guid_in IN the target guid -- OUT parameters: PROCEDURE add_rac_callback ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW ) IS BEGIN em_master_agent.add_master_changed_callback( target_name_in, target_type_in, 'emd_rac.update_master_properties'); END add_rac_callback; -- Procedure update_master_properties -- -- PURPOSE: The proc called when the master EMD switches -- IN parameters: -- target_name_in IN The target name -- target_type_in IN The target type -- OUT parameters: PROCEDURE update_master_properties ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, master_agent_url_in IN VARCHAR2 ) IS CURSOR propCursor (targetGuid IN VARCHAR2) IS SELECT property_name, property_type, property_value FROM mgmt_target_properties WHERE target_guid= HEXTORAW(targetGuid) AND property_type='INSTANCE'; l_target_guid MGMT_TARGETS.target_guid%TYPE; l_mastermem_guid MGMT_TARGETS.target_guid%TYPE; l_mastermem_target mgmt_targets.target_name%TYPE; l_mastermem_type mgmt_targets.target_type%TYPE; l_property_name mgmt_target_properties.property_name%TYPE; l_property_type mgmt_target_properties.property_type%TYPE; l_property_value mgmt_target_properties.property_value%TYPE; BEGIN IF target_name_in IS NOT NULL THEN SELECT m.composite_target_guid, m.member_target_name, m.member_target_type, t.target_guid INTO l_target_guid, l_mastermem_target, l_mastermem_type, l_mastermem_guid FROM mgmt_target_memberships m, mgmt_targets t WHERE m.member_target_guid=t.target_guid AND m.composite_target_name=target_name_in AND m.composite_target_type=target_type_in and t.emd_url = master_agent_url_in and rownum=1; OPEN propCursor( l_mastermem_guid ); LOOP FETCH propCursor INTO l_property_name, l_property_type, l_property_value; exit when propCursor%NOTFOUND; UPDATE mgmt_target_properties SET property_value=l_property_value WHERE target_guid = l_target_guid AND property_name = l_property_name AND property_type = l_property_type; END LOOP; CLOSE propCursor; END IF; EXCEPTION WHEN OTHERS THEN NULL; END update_master_properties; -- Function racdb_interconnects -- -- PURPOSE: this function is called for populating the view -- MGMT$RACDB_INTERCONNECTS -- IN parameters: -- cluster target_name IN The p_name -- OUT parameters: -- rac interconnects as mgmt_rac_ic_table -- FUNCTION racdb_interconnects ( p_name IN MGMT_TARGETS.TARGET_NAME%TYPE DEFAULT NULL ) RETURN mgmt_racdb_icstat_table IS v_cluster_target_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_racdb_members mgmt_cls_member_table; l_racdb_ic_table mgmt_racdb_ic_table; l_racdb_icstat_table mgmt_racdb_icstat_table; BEGIN -- Get the cluster targets if one has not been passed IF p_name IS NULL THEN -- host members of the cluster SELECT mgmt_cls_member ( rc.target_guid, rc.target_name, rc.target_type, od.target_guid, od.target_name, od.target_type ) BULK COLLECT INTO l_racdb_members FROM mgmt_target_memberships m, mgmt_targets mt, mgmt_targets rc, mgmt_targets od, MGMT_TARGET_ASSOCS i, MGMT_TARGET_ASSOC_DEFS a WHERE m.composite_target_type = 'cluster' AND m.member_target_guid = mt.target_guid AND rc.host_name = mt.host_name AND i.source_target_guid = rc.target_guid AND od.target_guid = i.assoc_target_guid AND i.assoc_guid = a.assoc_guid AND a.assoc_def_name = 'contains' AND a.scope_target_type = ' ' AND od.target_type = 'oracle_database'; ELSE v_cluster_target_guid := MGMT_TARGET.GET_TARGET_GUID(p_name,'cluster'); -- host members of the cluster SELECT mgmt_cls_member ( rc.target_guid, rc.target_name, rc.target_type, od.target_guid, od.target_name, od.target_type ) BULK COLLECT INTO l_racdb_members FROM mgmt_target_memberships m, mgmt_targets mt, mgmt_targets rc, mgmt_targets od, MGMT_TARGET_ASSOCS i, MGMT_TARGET_ASSOC_DEFS a WHERE m.composite_target_guid = v_cluster_target_guid AND m.member_target_guid = mt.target_guid AND rc.host_name = mt.host_name AND i.source_target_guid = rc.target_guid AND od.target_guid = i.assoc_target_guid AND i.assoc_guid = a.assoc_guid AND a.assoc_def_name = 'contains' AND a.scope_target_type = ' ' AND od.target_type = 'oracle_database'; END IF; -- Get the interconnects for each of the db members SELECT mgmt_racdb_ic ( ic_name, a.host_name, a.target_guid, property_value, ic_ip, ic_public, ic_source, b.composite_target_name, b.member_target_name, b.composite_target_guid ) BULK COLLECT INTO l_racdb_ic_table from mgmt_targets a, TABLE ( CAST ( l_racdb_members AS mgmt_cls_member_table ) ) b, mgmt_ha_rac_intr_conn c, mgmt$ecm_current_snapshots d, mgmt_target_properties e where a.target_guid = b.member_target_guid and a.target_type = d.target_type and a.target_name = d.target_name and d.snapshot_type='ha_rac_intrconn' and d.ecm_snapshot_id = c.ecm_snapshot_id and a.target_guid = e.target_guid and property_name='SID'; SELECT mgmt_racdb_icstat ( cls_name , db_tgt_name , inst_tgt_name , current_status, db_name, sid, ic_name, host_name, ic_ip , ic_public , ic_source, interconnect_rate , NVL(warning_count, 0), NVL(critical_count, 0) , rate_col_time ) BULK COLLECT INTO l_racdb_icstat_table FROM TABLE ( CAST ( l_racdb_ic_table AS mgmt_racdb_ic_table ) ) t1, ( SELECT b.target_guid, key_value key, value interconnect_rate, collection_timestamp rate_col_time FROM mgmt_metrics a, mgmt_current_metrics b, mgmt_targets c WHERE a.metric_guid = b.metric_guid AND a.metric_name='ha_rac_intrconn_traffic' AND a.metric_column='interconnect_rate' AND b.target_guid = c.target_guid AND c.target_type='oracle_database' AND a.type_meta_ver = c.type_meta_ver AND (a.category_prop_1 = c.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = c.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = c.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = c.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = c.category_prop_5 OR a.category_prop_5 = ' ') ) t2, ( select a.target_guid, a.property_value db_name from mgmt_target_properties a, TABLE ( CAST ( l_racdb_members AS mgmt_cls_member_table ) ) b where a.property_name='DBName' AND a.target_guid = b.member_target_guid ) t3, ( select target_guid, property_value cls_name from mgmt_target_properties where property_name='ClusterName' ) t4, (SELECT SUM(DECODE(severity_code,20,1,0)) warning_count, SUM(DECODE(severity_code,25,1,0)) critical_count, sev.target_guid tguid FROM mgmt_current_severity sev, ( select metric_guid from mgmt_metrics where metric_name = 'ha_rac_intrconn_type' and metric_column in ('interconnect_rate','if_type') ) t1, TABLE ( CAST (l_racdb_members AS mgmt_cls_member_table ) ) mm WHERE sev.metric_guid = t1.metric_guid AND mm.member_target_guid = sev.target_guid GROUP BY sev.target_guid ) t5, mgmt_current_availability avail where t1.db_target_guid = t2.target_guid(+) and t1.sid = t2.key(+) and t1.db_target_guid = t3.target_guid and t1.racdb_guid = t4.target_guid and t1.db_target_guid = avail.target_guid and t1.db_target_guid = t5.tguid (+); RETURN l_racdb_icstat_table; EXCEPTION WHEN OTHERS THEN RAISE; END racdb_interconnects; -- Function cls_interconnects -- -- PURPOSE: this function is called for populating view -- MGMT$CLUSTER_INTERCONNECTS -- -- IN parameters: -- cluster_name IN The p_cname -- OUT parameters: -- cluster interconnects as mgmt_cls_ic_table -- FUNCTION cls_interconnects ( p_cname IN MGMT_TARGETS.TARGET_NAME%TYPE DEFAULT NULL ) RETURN mgmt_cls_ic_table IS l_cl_table MGMT_GUID_ARRAY; l_cls_members mgmt_cls_member_table; l_cls_host_ic mgmt_cls_host_ic_table; l_cls_ic_table mgmt_cls_ic_table; BEGIN -- Get the cluster targets if one has not been passed IF p_cname IS NULL THEN SELECT MGMT_GUID_OBJ ( target_guid, target_name ) BULK COLLECT INTO l_cl_table FROM mgmt_targets WHERE target_type = 'cluster'; ELSE SELECT MGMT_GUID_OBJ ( target_guid, target_name ) BULK COLLECT INTO l_cl_table FROM mgmt_targets WHERE target_type = 'cluster' AND target_name = p_cname; END IF; -- get the members of the cluster target SELECT mgmt_cls_member ( i.source_target_guid , t1.target_name , t1.target_type , i.assoc_target_guid , t2.target_name , t2.target_type ) BULK COLLECT INTO l_cls_members FROM MGMT_TARGET_ASSOCS i, MGMT_TARGET_ASSOC_DEFS a, MGMT_TARGETS t1, MGMT_TARGETS t2, TABLE( CAST( l_cl_table AS MGMT_GUID_ARRAY ) ) cl WHERE t1.target_guid = i.source_target_guid AND t2.target_guid = i.assoc_target_guid AND i.assoc_guid = a.assoc_guid AND a.assoc_def_name = 'contains' AND a.scope_target_type = ' ' AND t2.target_type = 'host' AND t1.target_guid = cl.guid; -- get the network interface statistics for the cluster -- member targets IF p_cname IS NULL THEN SELECT mgmt_cls_host_ic ( a.composite_target_guid, a.composite_target_name, a.composite_target_type, a.member_target_guid, a.member_target_name, a.member_target_type, b.ifname, b.totrate, b.toterr, b.inrate, b.collection_timestamp ) BULK COLLECT INTO l_cls_host_ic FROM TABLE ( CAST ( l_cls_members AS mgmt_cls_member_table) ) a, mgmt$interface_stats b WHERE a.member_target_guid = b.tguid; ELSE SELECT mgmt_cls_host_ic ( a.composite_target_guid, a.composite_target_name, a.composite_target_type, a.member_target_guid, a.member_target_name, a.member_target_type, b.ifname, b.totrate, b.toterr, b.inrate, b.collection_timestamp ) BULK COLLECT INTO l_cls_host_ic FROM TABLE ( CAST ( l_cls_members AS mgmt_cls_member_table) ) a, TABLE ( EMD_RAC.INTERFACE_STATS(p_cname) ) b WHERE a.member_target_guid = b.tguid; END IF; -- get the cluster interconnects SELECT mgmt_cls_ic( mt.composite_target_name , --cluster_name mt.member_target_name , --host_name current_status , --host_status mt.ic_name , --if_name t1.ic_subnet , --if_subnet t1.ic_public , --if_public mt.totrate , --totrate_5min mt.toterr , --toterr_5min mt.inrate , --inrate_5min NVL(warning_count, 0) , --curr_warning NVL(critical_count, 0) ,--curr_critical mt.collection_timestamp --latest_collection_timestamp ) BULK COLLECT INTO l_cls_ic_table FROM ( select mm.composite_target_guid, mm.member_target_guid, b.ic_name, b.ic_subnet, b.ic_public FROM TABLE ( CAST (l_cls_members AS mgmt_cls_member_table ) ) mm, mgmt_ha_cls_intr_conn b, mgmt$ecm_current_snapshots c where mm.composite_target_guid = c.target_guid and c.snapshot_type = 'ha_cls_intrconn' and c.ecm_snapshot_id = b.ecm_snapshot_id and (b.ic_node = 'global' or b.ic_node = mm.member_target_name) ) t1, (SELECT SUM(DECODE(sev.severity_code,20,1,0)) warning_count, SUM(DECODE(sev.severity_code,25,1,0)) critical_count, sev.target_guid tguid, sev.key_value if_name FROM mgmt_current_severity sev, mgmt_metrics m, mgmt_targets t, TABLE ( CAST (l_cls_members AS mgmt_cls_member_table ) ) mm WHERE sev.metric_guid = m.metric_guid AND mm.member_target_guid = sev.target_guid AND m.metric_name = 'Network' AND m.metric_column IN ('totalRate','totalErrorRate') AND m.target_type = 'host' AND mm.member_target_guid = t.target_guid AND t.type_meta_ver = m.type_meta_ver AND ( m.category_prop_1 = ' ' OR m.category_prop_1 = t.category_prop_1 ) AND ( m.category_prop_2 = ' ' OR m.category_prop_2 = t.category_prop_2 ) AND ( m.category_prop_3 = ' ' OR m.category_prop_3 = t.category_prop_3 ) AND ( m.category_prop_4 = ' ' OR m.category_prop_4 = t.category_prop_4 ) GROUP BY sev.target_guid, sev.key_value ) t3, mgmt_current_availability avail, TABLE ( CAST (l_cls_host_ic AS mgmt_cls_host_ic_table ) ) mt where mt.composite_target_guid = t1.composite_target_guid (+) and mt.member_target_guid = t1.member_target_guid (+) and mt.ic_name = t1.ic_name (+) and mt.member_target_guid = t3.tguid (+) and mt.member_target_guid = avail.target_guid and mt.ic_name = t3.if_name (+); RETURN l_cls_ic_table; EXCEPTION WHEN OTHERS THEN RAISE; END cls_interconnects; PROCEDURE get_service_performance_info( target_name_in IN VARCHAR2, service_info_csr OUT CURSOR_TYPE) IS target_guid mgmt_targets.target_guid%TYPE; CURSOR bCursor (tg IN VARCHAR2) IS select v.target_guid tgtguid, v.value val, TO_CHAR(v.collection_timestamp , 'YYYY/MM/DD HH24-MI-SS') as collTime, v.metric_column, v.key_value dbname, v.key_value2 srvname, metkey.composite_key from mgmt$metric_current v, mgmt_metrics_composite_keys metkey where v.target_type = 'rac_database' AND v.target_guid = tg AND v.metric_name = 'service_performance' AND v.target_guid = metkey.target_guid AND v.key_value = metkey.key_part1_value AND v.key_value2 = metkey.key_part2_value AND EXISTS (SELECT service_name from mgmt_rac_services where service_name=v.key_value2 and service_type='user' and database_unique_name= v.key_value) order by v.key_value2, v.metric_column; l_serv_info_array MGMT_SERV_PERF_INFO_ARRAY_TYPE; l_serv_info_rec MGMT_SERV_PERF_INFO_TYPE; tgt_guid VARCHAR(64); value VARCHAR(256); colnm VARCHAR(64); dbname VARCHAR(64); srvnm VARCHAR(64); collectiontime VARCHAR(64); compositekey VARCHAR2(64); l_rec INTEGER; l_srvrec INTEGER; BEGIN target_guid := MGMT_TARGET.get_target_guid(target_name_in, 'rac_database'); l_serv_info_array := MGMT_SERV_PERF_INFO_ARRAY_TYPE(); OPEN bCursor( target_guid); LOOP tgt_guid := ''; value := ''; colnm := ''; dbname := ''; srvnm := ''; collectiontime := ''; compositekey := ''; l_srvrec := 0; FETCH bCursor INTO tgt_guid, value, collectiontime, colnm, dbname, srvnm, compositekey; exit when bCursor%NOTFOUND; FOR l_rec IN 1..l_serv_info_array.COUNT LOOP IF (l_serv_info_array(l_rec).srvnm = srvnm) THEN l_srvrec := l_rec; END IF; EXIT WHEN l_srvrec > 0; END LOOP; IF (l_srvrec = 0) THEN l_serv_info_array.extend; l_serv_info_array(l_serv_info_array.COUNT) := MGMT_SERV_PERF_INFO_TYPE( dbname, srvnm, collectiontime, null, null, null, null, null, 0, null, 0, compositekey); l_srvrec := l_serv_info_array.COUNT; END IF; IF l_srvrec > 0 THEN IF colnm = 'avg_response_time' THEN l_serv_info_array(l_srvrec).respval := value; ELSIF colnm = 'cpuload' THEN l_serv_info_array(l_srvrec).cpuload := value; ELSIF colnm = 'status' THEN l_serv_info_array(l_srvrec).statval := value; ELSIF colnm = 'running_instlist' THEN l_serv_info_array(l_srvrec).runval := value; ELSIF colnm = 'min_response_time' THEN l_serv_info_array(l_srvrec).minresp := value; ELSIF colnm = 'min_response_inst' THEN l_serv_info_array(l_srvrec).mininst := value; ELSIF colnm = 'max_response_time' THEN l_serv_info_array(l_srvrec).maxresp := value; ELSIF colnm = 'max_response_inst' THEN l_serv_info_array(l_srvrec).maxinst := value; END IF; END IF; END LOOP; CLOSE bCursor; OPEN service_info_csr FOR SELECT dbname, srvnm, colltime, respval, cpuload, statval, runval, minresp, mininst, maxresp, maxinst, compkey FROM TABLE(CAST(l_serv_info_array AS MGMT_SERV_PERF_INFO_ARRAY_TYPE ) ) ORDER BY srvnm; END get_service_performance_info; ---------------------------------------------------------------------------- PROCEDURE get_ha_top5_services_keys( target_name_in IN VARCHAR2, metric_column_in IN VARCHAR2, service_info_csr OUT CURSOR_TYPE) AS l_target_guid mgmt_targets.target_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(target_name_in, 'rac_database'); OPEN service_info_csr FOR SELECT t1.dbname, t1.srvname FROM ( SELECT curmet.value response, metkey.key_part1_value dbname, metkey.key_part2_value srvname, metkey.composite_key FROM mgmt_metrics met, mgmt_current_metrics curmet, mgmt_targets t, mgmt_metrics_composite_keys metkey WHERE met.metric_name = 'service_performance' AND met.metric_column = metric_column_in AND met.target_type = t.target_type AND met.type_meta_ver = t.type_meta_ver AND (met.category_prop_1 = t.category_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = t.category_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = t.category_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = t.category_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = t.category_prop_5 OR met.category_prop_5 = ' ') AND (met.metric_guid = curmet.metric_guid) AND (curmet.target_guid = t.target_guid) AND (curmet.key_value = metkey.composite_key) AND (curmet.target_guid = metkey.target_guid) AND t.target_guid = l_target_guid AND EXISTS (SELECT service_name from mgmt_rac_services where service_name=metkey.key_part2_value and service_type='user' and database_unique_name= metkey.key_part1_value) ) t1 WHERE t1.dbname = (select property_value from mgmt_target_properties where property_name = 'DBName' and target_guid = l_target_guid ) and rownum <= 5 order by t1.response desc; END get_ha_top5_services_keys; ---------------------------------------------------------------------------- PROCEDURE get_services_for_cluster( cluster_name_in IN VARCHAR2, services_csr OUT CURSOR_TYPE) AS l_targetguid VARCHAR2(64); BEGIN l_targetguid := MGMT_TARGET.get_target_guid(cluster_name_in, 'cluster'); OPEN services_csr FOR SELECT database_unique_name, service_name, service_type, enabled, tafpolicy, preferred_instances, available_instances, running_instances , cluster_name, server_group, service_cardinality, service_centric_type, TO_CHAR(CAST(s.saved_timestamp AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') FROM mgmt_rac_services m, ( SELECT saved_timestamp, ecm_snapshot_id FROM mgmt$ecm_current_snapshots WHERE target_guid=l_targetguid AND snapshot_type ='mgmt_rac_services' AND target_name = cluster_name_in ) s WHERE m.ecm_snapshot_id = s.ecm_snapshot_id AND m.service_type='user' AND m.cluster_name = cluster_name_in; END; ---------------------------------------------------------------------------- PROCEDURE get_services_for_db( target_name_in IN VARCHAR2, cluster_name_in IN VARCHAR2, services_csr OUT CURSOR_TYPE) AS l_targetguid VARCHAR2(64); BEGIN l_targetguid := MGMT_TARGET.get_target_guid(cluster_name_in, 'cluster'); OPEN services_csr FOR SELECT service_name, service_type, enabled, tafpolicy, preferred_instances, available_instances, running_instances , cluster_name, server_group, service_cardinality, service_centric_type, TO_CHAR(CAST(s.saved_timestamp AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') FROM mgmt_rac_services m, ( SELECT saved_timestamp, ecm_snapshot_id FROM mgmt$ecm_current_snapshots WHERE target_guid=l_targetguid AND snapshot_type ='mgmt_rac_services' AND target_name = cluster_name_in ) s, ( SELECT p.property_value dbName FROM mgmt_target_properties p, mgmt_targets t WHERE t.target_name=target_name_in AND t.target_type='rac_database' AND t.target_guid = p.target_guid AND p.property_name='DBName' ) tprop WHERE m.database_unique_name=tprop.dbName AND m.ecm_snapshot_id = s.ecm_snapshot_id AND m.service_type='user' AND m.cluster_name = cluster_name_in; END; ---------------------------------------------------------------------------- PROCEDURE string_tokenizer ( sPattern in VARCHAR2, sBuffer in VARCHAR2, sResult OUT CHAR_ARR_TYPE) AS nPos1 NUMBER; nPos2 NUMBER; iStart NUMBER; tok VARCHAR2(64); BEGIN sResult := CHAR_ARR_TYPE(); iStart := 1; nPos1 := Instr (sBuffer ,sPattern ,iStart); if (nPos1 = 0) THEN tok := sBuffer; sResult.EXTEND; sResult(sResult.COUNT) := Rtrim(Ltrim(tok)); --dbms_output.put_line('token = ' || tok || ' , ' ); ELSE tok := Substr(sBuffer ,iStart , nPos1 - 1); sResult.EXTEND; --sResult(sResult.COUNT) := tok; sResult(sResult.COUNT) := Rtrim(Ltrim(tok)); --dbms_output.put_line('token = ' || tok || ' , ' ); iStart := nPos1; WHILE (iStart <> 0) LOOP nPos1 := Instr (sBuffer ,sPattern ,iStart); IF nPos1 = 0 then tok := NULL ; ELSE nPos2 := Instr (sBuffer ,sPattern ,nPos1 + 1); IF nPos2 = 0 then tok := Rtrim(Ltrim(Substr(sBuffer ,nPos1+1))); iStart := nPos2; ELSE tok := Substr(sBuffer ,nPos1 + 1 , nPos2 - nPos1 - 1); iStart := nPos2; END IF; END IF; sResult.EXTEND; sResult(sResult.COUNT) := tok; --dbms_output.put_line('token = ' || tok || ' , ' ); END LOOP; END IF; END string_tokenizer ; procedure long_string_tokenizer ( sPattern in varchar2, sBuffer in varchar2, sResult out long_string_array) as nPos1 number; nPos2 number; iStart number; tok varchar2(4000); pLen number := length(sPattern); BEGIN sResult := long_string_array(); iStart := 1; nPos1 := Instr (sBuffer ,sPattern ,iStart); if (nPos1 = 0) THEN tok := sBuffer; sResult.EXTEND; sResult(sResult.COUNT) := Rtrim(Ltrim(tok)); ELSE tok := Substr(sBuffer ,iStart , nPos1 - 1); sResult.EXTEND; --sResult(sResult.COUNT) := tok; sResult(sResult.COUNT) := Rtrim(Ltrim(tok)); --dbms_output.put_line('token = ' || tok || ' , ' ); iStart := nPos1; WHILE (iStart <> 0) LOOP nPos1 := Instr (sBuffer ,sPattern ,iStart); IF nPos1 = 0 then tok := NULL ; ELSE nPos2 := Instr (sBuffer ,sPattern ,nPos1 + pLen); IF nPos2 = 0 then tok := Rtrim(Ltrim(Substr(sBuffer ,nPos1+pLen))); iStart := nPos2; ELSE tok := Substr(sBuffer ,nPos1 + pLen , nPos2 - nPos1 - pLen); iStart := nPos2; END IF; END IF; sResult.EXTEND; sResult(sResult.COUNT) := tok; --dbms_output.put_line('token = ' || tok || ' , ' ); END LOOP; END IF; END long_string_tokenizer ; ---------------------------------------------------------------------------- PROCEDURE get_problem_services_for_db( target_name_in IN VARCHAR2, cluster_name_in IN VARCHAR2, l_problem_srv_csr OUT CURSOR_TYPE) AS l_srv_info CURSOR_TYPE; BEGIN get_problemsrv_srvperf_for_db(target_name_in, cluster_name_in, l_problem_srv_csr, l_srv_info); END get_problem_services_for_db; ---------------------------------------------------------------------------- function get_dbtype( target_name_in varchar2 ) return varchar2 is l_cardinality_type varchar2(32); l_dbtype varchar2(32); begin begin l_dbtype := 'POLICY_MANAGED'; select service_centric_type into l_cardinality_type from mgmt$ecm_current_snapshots s, mgmt_rac_services r where s.ecm_snapshot_id = r.ecm_snapshot_id and database_unique_name = (select p.property_value from mgmt_targets t, mgmt_target_properties p where t.target_guid = p.target_guid and t.target_name = target_name_in and t.target_type = mgmt_global.g_rac_database_target_type and p.property_name = 'DBName') and service_type = 'internal'; if(l_cardinality_type is null or l_cardinality_type != 'UNIFORM') then l_dbtype := 'ADMIN_MANAGED'; end if; exception when no_data_found then l_dbtype := 'ADMIN_MANAGED'; end; return l_dbtype; end get_dbtype; -- Procedure to determine if a database is -- SI, RAC or RAC One procedure get_database_type( target_name in varchar2, db_type out varchar2 ) is begin begin select database_type into db_type from mgmt$ecm_current_snapshots s, mgmt_rac_services r where s.ecm_snapshot_id = r.ecm_snapshot_id and database_unique_name = (select p.property_value from mgmt_targets t, mgmt_target_properties p where t.target_guid = p.target_guid and t.target_name = target_name and t.target_type = mgmt_global.g_rac_database_target_type and p.property_name = 'DBName') and service_type = 'internal'; exception when no_data_found then raise mgmt_global.target_does_not_exist; end; end get_database_type; -- Procedure to determine database config type procedure get_db_config_type( p_target_name in varchar2, p_db_config_type out varchar2 ) is begin p_db_config_type := get_dbtype(p_target_name); end get_db_config_type; PROCEDURE get_problemsrv_srvperf_for_db( target_name_in IN VARCHAR2, cluster_name_in IN VARCHAR2, l_problem_srv_csr OUT CURSOR_TYPE, l_srv_perf_info OUT CURSOR_TYPE) AS l_srv_info CURSOR_TYPE; l_srv_list CURSOR_TYPE; l_alert_list CURSOR_TYPE; l_tgt_nm VARCHAR2(64); l_srvnm VARCHAR2(64); l_colltime VARCHAR2(64); l_srvtyp VARCHAR2(64); l_enabled VARCHAR2(64); l_taf VARCHAR2(64); l_pfl VARCHAR2(1024); l_avl VARCHAR2(1024); l_rnl VARCHAR2(1024); l_cls VARCHAR2(64); l_srvgrp VARCHAR2(64); l_card NUMBER; l_serv_info_array MGMT_SERV_PERF_INFO_ARRAY_TYPE; l_serv_info_rec MGMT_SERV_PERF_INFO_TYPE; l_pfl_tok_list CHAR_ARR_TYPE; l_runval_tok_list CHAR_ARR_TYPE; listmatch NUMBER; l_problem_srv MGMT_PROBLEM_SERV_INFO_TYPE; l_problem_srv_array MGMT_PROBLEM_SERV_ARRAY_TYPE; alert VARCHAR2(32) := 'Critical'; l_arec MGMT_SERV_ALERT_INFO_TYPE; l_alert_rec MGMT_SERV_ALERT_INFO_TYPE; l_dbtype VARCHAR2(256); l_targetguid VARCHAR2(64); l_cardtype VARCHAR2(32); BEGIN l_targetguid := MGMT_TARGET.get_target_guid(target_name_in, 'rac_database'); get_services_for_db(target_name_in, cluster_name_in, l_srv_list); get_service_performance_info(target_name_in, l_srv_info); l_serv_info_array := MGMT_SERV_PERF_INFO_ARRAY_TYPE(); l_problem_srv_array := MGMT_PROBLEM_SERV_ARRAY_TYPE(); LOOP l_serv_info_rec := MGMT_SERV_PERF_INFO_TYPE('','','','','','','','','','', '', ''); fetch l_srv_info into l_serv_info_rec.dbname, l_serv_info_rec.srvnm, l_serv_info_rec.colltime, l_serv_info_rec.respval, l_serv_info_rec.cpuload, l_serv_info_rec.statval, l_serv_info_rec.runval, l_serv_info_rec.minresp, l_serv_info_rec.mininst, l_serv_info_rec.maxresp, l_serv_info_rec.maxinst, l_serv_info_rec.compkey; exit when l_srv_info%NOTFOUND; l_serv_info_array.EXTEND; l_serv_info_array(l_serv_info_array.COUNT) := l_serv_info_rec; END LOOP; l_dbtype := get_dbtype(target_name_in); LOOP fetch l_srv_list into l_srvnm, l_srvtyp, l_enabled, l_taf, l_pfl, l_avl, l_rnl, l_cls, l_srvgrp, l_card, l_cardtype, l_colltime; exit when l_srv_list%NOTFOUND; FOR l_rec IN 1..l_serv_info_array.COUNT LOOP IF (l_serv_info_array(l_rec).srvnm = l_srvnm) THEN -- check if the running instances and preferred instances match -- only running inst number and preferred inst number needs to be matched -- as its enough to ensure service is running as configured -- if service is down on any preferred instance, its a warning condition string_tokenizer ( ',', l_serv_info_array(l_rec).runval, l_runval_tok_list); listmatch := 0; if (l_dbtype = 'ADMIN_MANAGED') then string_tokenizer ( ',', l_pfl, l_pfl_tok_list); FOR l_pf IN 1..l_pfl_tok_list.COUNT LOOP FOR l_rn IN 1..l_runval_tok_list.COUNT LOOP IF (l_pfl_tok_list(l_pf) = l_runval_tok_list(l_rn)) THEN listmatch := listmatch + 1; EXIT WHEN listmatch > 0; END IF; END LOOP; END LOOP; end if; get_alerts_info_for_service( target_name_in , 'rac_database', l_srvnm, target_name_in, l_alert_list); LOOP l_alert_rec := MGMT_SERV_ALERT_INFO_TYPE('',0, '','','','','','',''); fetch l_alert_list into l_alert_rec.srvnm, l_alert_rec.severityCode, l_alert_rec.metricnm, l_alert_rec.metriccol, l_alert_rec.alerttime, l_alert_rec.target_name, l_alert_rec.target_type, l_alert_rec.metricval, l_alert_rec.keyval ; exit when l_alert_list%NOTFOUND; IF (l_alert_rec.severityCode = 20) THEN alert:= 'Warning'; END IF; IF (l_alert_rec.severityCode = 25) THEN alert:= 'Critical'; END IF; dbms_output.put_line('alert found for service ' || l_alert_rec.srvnm || ' code ' || l_alert_rec.severityCode || ' for metric ' || l_alert_rec.metricnm || ' alert ' || alert); l_problem_srv_array.EXTEND; l_problem_srv_array(l_problem_srv_array.COUNT) := MGMT_PROBLEM_SERV_INFO_TYPE( l_serv_info_array(l_rec).srvnm, l_serv_info_array(l_rec).statval, l_pfl, l_serv_info_array(l_rec).runval, alert, l_alert_rec.severityCode, l_alert_rec.metricnm, l_alert_rec.metriccol, l_alert_rec.alerttime, l_alert_rec.target_name, l_alert_rec.target_type, l_alert_rec.metricval, l_alert_rec.keyval ); END LOOP; IF (l_serv_info_array(l_rec).statval != 'Down') THEN IF ( l_dbtype = 'POLICY_MANAGED') then IF (l_runval_tok_list.COUNT != l_card) THEN alert:= 'Warning'; dbms_output.put_line('Warning, dbtype ' || l_dbtype || ', card type ' || l_cardtype || l_serv_info_array(l_rec).dbname ||' ,' || l_serv_info_array(l_rec).srvnm || ' runcount ' || l_runval_tok_list.COUNT || ', cardinl ' || l_card); ELSE alert:= 'Normal'; END IF; ELSE IF ( (l_runval_tok_list.COUNT != l_pfl_tok_list.COUNT) or (listmatch != l_pfl_tok_list.COUNT) ) THEN alert:= 'Warning'; dbms_output.put_line(' !(service centric + uniform) - Warning ' || l_serv_info_array(l_rec).srvnm ||' , runcount ' || l_runval_tok_list.COUNT ||' ,preferred count ' || l_pfl_tok_list.COUNT ||' , list match ' || listmatch); ELSE alert:= 'Normal'; dbms_output.put_line('Up ' || l_serv_info_array(l_rec).dbname ||' ,' || l_serv_info_array(l_rec).srvnm ||' ,' || l_serv_info_array(l_rec).colltime || ' ,' || l_serv_info_array(l_rec).respval ||' ,' || l_serv_info_array(l_rec).statval ||' ,' || l_serv_info_array(l_rec).runval); END IF; END IF; l_problem_srv_array.EXTEND; l_problem_srv_array(l_problem_srv_array.COUNT) := MGMT_PROBLEM_SERV_INFO_TYPE( l_serv_info_array(l_rec).srvnm, l_serv_info_array(l_rec).statval, l_pfl, l_serv_info_array(l_rec).runval, alert, 0, '', '', '', '', '', '', '' ); END IF; EXIT WHEN (l_serv_info_array(l_rec).srvnm = l_srvnm); END IF; END LOOP; END LOOP; close l_srv_list; close l_srv_info; OPEN l_problem_srv_csr FOR SELECT srvnm, srvstat, preflist, runlist, alertstatval, severityCode, metricnm, metriccol, alerttime, target_name, target_type, metricval, keyval FROM TABLE(CAST(l_problem_srv_array AS MGMT_PROBLEM_SERV_ARRAY_TYPE) ) ORDER BY srvnm; OPEN l_srv_perf_info FOR SELECT dbname, srvnm, colltime, respval, cpuload, statval, runval, minresp, mininst, maxresp, maxinst, compkey FROM TABLE(CAST(l_serv_info_array AS MGMT_SERV_PERF_INFO_ARRAY_TYPE ) ) ORDER BY srvnm; END get_problemsrv_srvperf_for_db; ---------------------------------------------------------------------------- -- -- PROCEDURE: get_alerts_info_for_service -- -- PURPOSE: -- returns rac_database and its components rac_instance's alerts list -- for a given service -- PROCEDURE get_alerts_info_for_service( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, service_name_in IN VARCHAR2, db_name_in IN VARCHAR2, alerts_info OUT CURSOR_TYPE) IS l_alert_info emd_rac.CURSOR_TYPE; l_tgt_nm VARCHAR2(64); l_tgt_typ VARCHAR2(64); l_typnm VARCHAR2(64); l_code NUMBER; l_msg VARCHAR2(4000); l_time DATE; l_val VARCHAR2(256); l_alerttime DATE; l_metricnm VARCHAR2(64); l_metriccol VARCHAR2(64); l_keyval VARCHAR2(256); l_metlabel VARCHAR2(64); l_collabel VARCHAR2(64); l_nlsid VARCHAR2(64); l_params VARCHAR2(4000); l_impact VARCHAR2(4000); l_metlabel_nlsid VARCHAR2(64); l_collabel_nlsid VARCHAR2(64); l_target_guid mgmt_targets.target_guid%TYPE; l_db_unique_nm VARCHAR2(64); l_alert_arr MGMT_SERV_ALERT_ARRAY_TYPE; BEGIN l_target_guid := MGMT_TARGET.get_target_guid(target_name_in, target_type_in); select property_value into l_db_unique_nm from mgmt_target_properties where target_guid = l_target_guid and property_name = 'DBName'; -- dbms_output.put_line('calling emd_rac.get_alerts_info for srv ' || service_name_in || ' db unique name ' || l_db_unique_nm); emd_rac.get_alerts_info(target_name_in, target_type_in, l_alert_info); l_alert_arr := MGMT_SERV_ALERT_ARRAY_TYPE(); LOOP fetch l_alert_info into l_tgt_nm, l_tgt_typ, l_typnm, l_code, l_msg, l_alerttime, l_val, l_time, l_metricnm, l_metriccol, l_keyval, l_metlabel, l_collabel, l_nlsid, l_params, l_impact, l_metlabel_nlsid, l_collabel_nlsid; exit when l_alert_info%NOTFOUND; -- dbms_output.put_line(l_tgt_nm || ',' || l_tgt_typ ||' ,' || l_code || ' ,' || l_val ||' ,' || l_alerttime || ' ,' || l_metricnm || ' ,' || l_metriccol || ' ,' || l_keyval || ' ,' || l_metlabel ); -- dbms_output.new_line(); IF (l_metricnm='service' and l_tgt_typ='oracle_database' and l_keyval=service_name_in) THEN l_alert_arr.EXTEND; l_alert_arr(l_alert_arr.COUNT) := MGMT_SERV_ALERT_INFO_TYPE( l_keyval, l_code, l_metricnm, l_metriccol, l_alerttime, l_tgt_nm, l_tgt_typ, l_val, l_keyval ); -- dbms_output.put_line('--- service has instance level alert ' || l_keyval || ' , ' || l_metricnm || ' ,' || l_metriccol || ' ,' || l_val || ' ,' || l_alerttime || ' , ' || l_code ); END IF; IF (l_metricnm='service_performance' and l_tgt_typ='rac_database' ) THEN FOR rec IN (SELECT key_part1_value dbnm, key_part2_value srvnm FROM mgmt_metrics_composite_keys WHERE composite_key = l_keyval and key_part1_value = l_db_unique_nm and target_guid = hextoraw(l_target_guid) ) LOOP -- key_part1_value = db_name_in and -- dbms_output.put_line('compare service ' || rec.srvnm || ' , ' || rec.dbnm ); IF (rec.srvnm = service_name_in) THEN l_alert_arr.EXTEND; l_alert_arr(l_alert_arr.COUNT) := MGMT_SERV_ALERT_INFO_TYPE( rec.srvnm, l_code, l_metricnm, l_metriccol, l_alerttime, l_tgt_nm, l_tgt_typ, l_val, l_keyval ); -- dbms_output.put_line('--- service has cpuload alert ' || l_keyval || ' , ' || l_metricnm || ' ,' || l_metriccol || ' ,' || l_val || ' ,' || l_alerttime || ' , ' || l_code); END IF; END LOOP; END IF; END LOOP; close l_alert_info; OPEN alerts_info FOR SELECT srvnm, severityCode, metricnm, metriccol, alerttime, target_name, target_type, metricval, keyval FROM TABLE(CAST(l_alert_arr AS MGMT_SERV_ALERT_ARRAY_TYPE) ); END get_alerts_info_for_service; ---------------------------------------------------------------------------- -- Function interface_stats -- -- PURPOSE: this function is called for populating the view -- MGMT$INTERFACE_STATS -- IN parameters: -- cluster target_name IN The p_cname -- OUT parameters: -- rac interconnects as mgmt_racdb_iface_stats_table -- FUNCTION interface_stats ( p_cname IN MGMT_TARGETS.TARGET_NAME%TYPE DEFAULT NULL ) RETURN mgmt_racdb_iface_stats_table IS l_cls_members mgmt_cls_member_table; l_total_rate_table mgmt_racdb_tb_metric_table; l_total_error_rate_table mgmt_racdb_tb_metric_table; l_read_rate_table mgmt_racdb_tb_metric_table; l_interface_stats mgmt_racdb_iface_stats_table; CURSOR C1 ( v_metric_column VARCHAR2) IS SELECT mgmt_racdb_tb_metric ( b.target_guid, key_value, value, collection_timestamp ) FROM mgmt_metrics a, mgmt_current_metrics b, mgmt_targets c, TABLE ( CAST ( l_cls_members AS mgmt_cls_member_table ) ) d WHERE a.metric_guid = b.metric_guid AND a.metric_name='Network' AND a.target_type = c.target_type AND c.target_type = 'host' AND a.metric_column= v_metric_column AND b.target_guid = c.target_guid AND a.type_meta_ver = c.type_meta_ver AND (a.category_prop_1 = c.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = c.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = c.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = c.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = c.category_prop_5 OR a.category_prop_5 = ' ') AND c.target_guid = d.member_target_guid; BEGIN -- get the members of the cluster target SELECT mgmt_cls_member ( a.composite_target_guid, a.composite_target_name, a.composite_target_type, a.member_target_guid, a.member_target_name, a.member_target_type ) BULK COLLECT INTO l_cls_members FROM mgmt_target_memberships a WHERE a.composite_target_type||'' = 'cluster' AND a.composite_target_name = NVL(p_cname,a.composite_target_name); -- Get the metric colum data in to the 3 temp tables OPEN C1('totalRate'); FETCH C1 BULK COLLECT INTO l_total_rate_table; CLOSE C1; OPEN C1('totalErrorRate'); FETCH C1 BULK COLLECT INTO l_total_error_rate_table; CLOSE C1; OPEN C1('readRate'); FETCH C1 BULK COLLECT INTO l_read_rate_table; CLOSE C1; -- Fetch the interface stats from the 3 temp tables SELECT mgmt_racdb_interface_stats ( t.target_guid, t.host_name, t.target_name, a.key_value, a.value, b.value, c.value, a.collection_timestamp ) BULK COLLECT INTO l_interface_stats FROM mgmt_targets t, TABLE ( CAST ( l_total_rate_table AS mgmt_racdb_tb_metric_table ) ) a, TABLE ( CAST ( l_total_error_rate_table AS mgmt_racdb_tb_metric_table ) ) b, TABLE ( CAST ( l_read_rate_table AS mgmt_racdb_tb_metric_table ) ) c WHERE a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.key_value = b.key_value AND a.key_value = c.key_value AND a.target_guid = t.target_guid AND t.target_type = 'host'; RETURN l_interface_stats; EXCEPTION WHEN OTHERS THEN RAISE; END interface_stats; --Logging proc for logging info procedure log_info ( p_message varchar2, p_module varchar2 default 'em.rac' ) is begin --emdw_log.set_session_trace_level(4); if (emdw_log.p_is_info_set) then emdw_log.info(p_message, p_module); end if; --emdw_log.set_session_trace_level(0); end log_info; function emd_url_to_target ( p_emd_url varchar2 ) return varchar2 is l_emd_target varchar2(64); begin begin select target_name into l_emd_target from mgmt_targets where target_type='oracle_emd' and emd_url=p_emd_url; exception when no_data_found then l_emd_target :=NULL; end; return l_emd_target; end emd_url_to_target; procedure submit_target_proc_job ( p_job_targets in mgmt_job_target_list, p_job_params in mgmt_job_param_list, p_job_id out raw ) is pragma autonomous_transaction; l_exec_id raw(16); l_schedule mgmt_job_schedule_record; l_running_job_count number; l_max_running_job constant number := 20; begin select count(job_id) into l_running_job_count from mgmt_job where job_type='EventBasedProcessing' and system_job =1 and is_corrective_action=0; if l_running_job_count >= l_max_running_job then commit; log_info('Droping job as no. of running gpnp job reached threshold of '|| l_max_running_job); return; end if; l_schedule := mgmt_jobs.get_immediate_schedule_record(); mgmt_jobs.submit_job(sys_guid(), 'EventBased Target processing', 'EventBasedProcessing',p_job_targets, p_job_params, l_schedule, p_job_id, l_exec_id, null, 1, null, null, null); commit; end submit_target_proc_job; function get_host_from_node ( p_node_name varchar2 ) return varchar2 is l_host_name varchar2(64); begin begin select target_name into l_host_name from mgmt$target_properties where target_type= mgmt_global.g_host_target_type and property_name = 'NodeName' and property_value = p_node_name; exception when no_data_found then l_host_name := null; end; -- TODO get host name from metric using node -- TODO still null then use like operator begin if l_host_name is null then select target_name into l_host_name from mgmt_targets where target_type = mgmt_global.g_host_target_type and (target_name like p_node_name||'.'||'%' or target_name = p_node_name); end if; exception when no_data_found then l_host_name := null; end; return l_host_name; end get_host_from_node; --- --- function parse_nv_pair ( p_text varchar2, p_sep varchar2, p_nv_sep varchar2 ) return mgmt_namevalue_array is nv_arr MGMT_NAMEVALUE_ARRAY := MGMT_NAMEVALUE_ARRAY(); l_tok long_string_array; l_nv_tok long_string_array; l_cnt number; begin begin long_string_tokenizer(p_sep , p_text, l_tok); for l_cnt in 1..l_tok.count loop long_string_tokenizer(p_nv_sep , l_tok(l_cnt), l_nv_tok); if(l_nv_tok.count >=2) then nv_arr.extend(); nv_arr(nv_arr.count) := mgmt_namevalue_obj.new(l_nv_tok(1), l_nv_tok(2)); end if ; end loop; exception when others then nv_arr := null; end; return nv_arr; end parse_nv_pair; -- -- --- --- function get_attr_val ( p_attr_list mgmt_namevalue_array, p_attr_name varchar2 ) return varchar2 is l_cnt number; l_val varchar2(64); l_nvobj mgmt_namevalue_obj; begin for l_cnt in 1..p_attr_list.count loop l_nvobj := p_attr_list(l_cnt); if(upper(l_nvobj.name) = upper(p_attr_name)) then l_val := l_nvobj.value; exit; end if; end loop; return l_val; end get_attr_val; -- -- function get_target_name ( p_name mgmt_targets.target_name%type, p_type mgmt_targets.target_type%type ) return mgmt_targets.target_name%type is l_target_name mgmt_targets.target_name%type := p_name ; l_cnt number := 0; begin begin select count(*) into l_cnt from mgmt_targets where target_name = l_target_name and target_type = p_type; if l_cnt = 0 then return l_target_name; else return null; end if; exception when others then null; end; return l_target_name; end get_target_name; procedure clear_delete_flag ( p_target_guid mgmt_targets.target_guid%type ) is begin delete from mgmt_target_properties where target_guid = p_target_guid and property_name = 'TargetDeleteTimestamp' and property_type = 'INSTANCE'; commit; end clear_delete_flag; -- -- -- function get_action ( p_target_guid mgmt_targets.target_guid%type, p_state varchar2, p_host mgmt_targets.host_name%type, p_agent_status_evt boolean ) return varchar2 is l_action varchar2(12) := null; l_src_host mgmt_targets.host_name%type; l_target_status number; begin log_info('Target guid: '||p_target_guid ||' Resource State: ' || p_state); if p_target_guid is null and p_state in ('ONLINE') then l_action :='add'; elsif p_target_guid is not null then select host_name into l_src_host from mgmt_targets where target_guid = p_target_guid; case when p_state in ('ONLINE') and p_host !=l_src_host then l_action :='relocate'; when p_state in ('ONLINE', 'OFFLINE', 'UNKNOWN') then if p_agent_status_evt then select current_status into l_target_status from mgmt_current_availability where target_guid=p_target_guid; if( (p_state not in ('ONLINE') and l_target_status = mgmt_global.g_status_up) or (p_state not in ('OFFLINE', 'UNKNOWN') and l_target_status = mgmt_global.g_status_down)) then l_action :='update'; end if; else l_action :='update'; end if; when p_state = 'MODIFY' then l_action :='modify'; when p_state = 'DELETE' then l_action :='remove'; end case; end if; log_info('ACTION : '|| l_action); return l_action; end get_action; function get_curr_jobs_name ( p_job_params mgmt_job_param_list, p_excd_param mgmt_short_string_array ) return char_arr_type is l_jobs char_arr_type := char_arr_type(); l_job_params mgmt_job_param_list := mgmt_job_param_list(); l_cnt number; begin for item in (select /*+ cardinality(jp 100)*/ param_name, scalar_value from TABLE(CAST(p_job_params as mgmt_job_param_list)) jp where param_name not in (select /*+ cardinality(exjp 50)*/ column_value from TABLE(CAST(p_excd_param as mgmt_short_string_array)) exjp) ) loop l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record(item.param_name, mgmt_jobs.param_type_scalar,item.scalar_value, null); end loop; for item in (select job_name, job_id from mgmt_job where job_type='EventBasedProcessing' and system_job =1 and is_corrective_action=0 and not regexp_like(job_name,'^EVT_')) -- regexp_like(j.job_name,'^EVT_') exclude target cleanup job loop select count(*) into l_cnt from (select /*+ cardinality(jp 100)*/ param_name, scalar_value from TABLE(CAST(l_job_params as mgmt_job_param_list)) jp minus select parameter_name, scalar_value from mgmt_job_parameter where job_id = item.job_id ); if l_cnt = 0 then l_jobs.extend(); l_jobs(l_jobs.count) := item.job_name; end if; end loop; return l_jobs; end get_curr_jobs_name; procedure handle_node_event ( p_host varchar2, p_state varchar2, p_cls_guid mgmt_targets.target_guid%type, p_job_params mgmt_job_param_list, p_emd_url mgmt_targets.emd_url%type ) is l_job_targets mgmt_job_target_list := mgmt_job_target_list(); l_action varchar2(12); l_cls_target mgmt_targets.target_name%type; l_cls_agent mgmt_targets.emd_url%type; l_tgt_agent mgmt_targets.emd_url%type; l_job_params mgmt_job_param_list := p_job_params; l_cnt number; l_excd_param mgmt_short_string_array; l_curr_jobs char_arr_type; l_job_id raw(16); begin if(p_state in ('ONLINE') and p_host is not null) then select count(*) into l_cnt from mgmt_target_memberships where composite_target_guid= p_cls_guid and member_target_name=p_host and member_target_type=mgmt_global.g_host_target_type; if(l_cnt = 0) then l_action := 'add'; else log_info('handle_node_evnt returning: host=> ' || p_host || ' already part of cluster'); return; end if; end if; log_info('Action: ' || l_action); select target_name, emd_url into l_cls_target, l_cls_agent from mgmt_targets where target_guid = p_cls_guid; if p_host is not null then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('target', mgmt_jobs.param_type_scalar,p_host, null); l_tgt_agent := p_emd_url; else l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('target', mgmt_jobs.param_type_scalar,l_cls_target, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('emdUrl', mgmt_jobs.param_type_scalar,l_cls_agent, null); l_tgt_agent := l_cls_agent; end if; l_cnt := l_job_params.count; l_job_params.extend(4); l_job_params(l_cnt+1) := mgmt_job_param_record('parentEmdUrl', mgmt_jobs.param_type_scalar, l_cls_agent , null); l_job_params(l_cnt+2) := mgmt_job_param_record('parentTargetName', mgmt_jobs.param_type_scalar, l_cls_target , null); l_job_params(l_cnt+3) := mgmt_job_param_record('action', mgmt_jobs.param_type_scalar, l_action , null); l_job_params(l_cnt+4) := mgmt_job_param_record('type', mgmt_jobs.param_type_scalar, mgmt_global.g_host_target_type , null); l_excd_param := mgmt_short_string_array('eventTime','parentEmdUrl'); l_curr_jobs := get_curr_jobs_name (l_job_params, l_excd_param); if l_curr_jobs.count > 0 then commit; log_info('Discarding this job as duplicate job'); return; end if; log_info('Target Agent: ' || l_tgt_agent); l_job_targets.extend(1); l_job_targets(1) := mgmt_job_target_record(emd_url_to_target(l_tgt_agent), mgmt_global.g_agent_target_type); log_info('Submitting job for node/host target'); submit_target_proc_job(l_job_targets,l_job_params, l_job_id); end handle_node_event; procedure handle_listener_event ( p_lsnr_name varchar2, p_host varchar2, p_state_or_action varchar2, p_job_params mgmt_job_param_list, p_emd_url mgmt_targets.emd_url%type, p_crsd_attrs varchar2, p_event_time varchar2, p_is_scan boolean, p_agent_status_evt boolean ) is l_job_targets mgmt_job_target_list := mgmt_job_target_list(); l_action varchar2(12); l_job_params mgmt_job_param_list := p_job_params; l_target_name mgmt_targets.target_name%type; l_target_guid mgmt_targets.target_guid%type; l_src_emd_url mgmt_targets.emd_url%type; l_src_host mgmt_targets.host_name%type; l_src_node_name varchar2(64); l_tmp_host mgmt_targets.host_name%type; l_oracle_home mgmt_target_properties.property_value%type := null; l_lsnr_port mgmt_target_properties.property_value%type := null; l_crs_attr mgmt_namevalue_array; l_nvobj MGMT_NAMEVALUE_OBJ; l_cnt number; l_target_exist boolean; l_excd_param mgmt_short_string_array; l_curr_jobs char_arr_type; l_job_id raw(16); l_tmp_jobs char_arr_type; begin if p_is_scan =true then l_tmp_host := '%'; else l_tmp_host := p_host; end if; begin select tgt.target_guid into l_target_guid from mgmt_targets tgt, mgmt_target_properties props where tgt.target_guid = props.target_guid and tgt.target_type = mgmt_global.g_listener_target_type and props.property_name = 'LsnrName' and props.property_value = p_lsnr_name and tgt.host_name like l_tmp_host; exception when no_data_found then l_target_guid := null; when too_many_rows then begin if p_state_or_action = 'CREATE' then l_crs_attr := parse_nv_pair(p_crsd_attrs,',','='); l_oracle_home := get_attr_val(l_crs_attr,'ORACLE_HOME'); l_lsnr_port := get_attr_val(l_crs_attr,'PORT'); select t1.target_guid into l_target_guid from mgmt$target_properties t1, (select target_guid from mgmt_target_properties where property_name = 'LsnrName' and property_value = p_lsnr_name) t2, (select target_guid from mgmt_target_properties where property_name = 'OracleHome' and property_value = l_oracle_home) t3, (select target_guid from mgmt_target_properties where property_name = 'Port' and property_value = l_lsnr_port) t4 where t1.target_type = mgmt_global.g_listener_target_type and t1.target_guid = t2.target_guid and t1.target_guid = t3.target_guid and t1.target_guid = t4.target_guid; end if; exception when others then l_target_guid := null; end; end; if l_target_guid is null and p_is_scan=true then log_info('returning '|| l_target_guid); return; end if; l_action := get_action(l_target_guid, p_state_or_action, p_host, p_agent_status_evt); -- listener modify acheived by (netca) delete then add if l_action='update' and p_state_or_action='ONLINE' and not p_is_scan then l_action :='add'; -- listener modify acheived by (netca) delete then add log_info('Modified action to '|| l_action); end if; if l_action is null then --TO raise alert saying unknown action return; end if; if l_target_guid is null then l_target_exist := false; else l_target_exist := true; clear_delete_flag(l_target_guid); end if; if l_action='add' and l_target_exist=false then l_target_name := get_target_name(p_lsnr_name||'_'||p_host, mgmt_global.g_listener_target_type); if l_target_name is null then log_info('Target name exist aborting further processing'); return; end if; else select target_name, host_name, emd_url into l_target_name, l_src_host, l_src_emd_url from mgmt_targets where target_guid = l_target_guid; end if; if l_target_exist=true then --select host_name, emd_url into l_src_host, l_src_emd_url from mgmt_targets --where target_guid = l_target_guid; if l_action ='relocate' then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcEmdUrl', mgmt_jobs.param_type_scalar, l_src_emd_url, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcEmdTgt', mgmt_jobs.param_type_scalar, emd_url_to_target(l_src_emd_url), null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcHost', mgmt_jobs.param_type_scalar, l_src_host, null); -- Get the node name of source host and add as jjob parameter begin select props.property_value into l_src_node_name from mgmt_targets tgt, mgmt_target_properties props where tgt.target_guid = props.target_guid and props.property_name='NodeName' and tgt.target_type= mgmt_global.g_host_target_type and tgt.target_name = l_src_host; exception when others then l_src_node_name := substr(l_src_host,1,instr(l_src_host,'.',1)-1) ; end; if l_src_node_name is not null then log_info('srcNodeName: '||l_src_node_name); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcNodeName' ,mgmt_jobs.param_type_scalar, l_src_node_name, null); end if; -- Get the static target properties with not null value and set as job parameter. -- It is used to construct target incase target could not be fetched from src agent for item in ( select props.property_name, props.property_value from mgmt_target_properties props, mgmt_target_prop_defs defs , mgmt_targets tgt where props.property_name = defs.property_name and props.property_type='INSTANCE' and defs.computed_flag=0 and props.target_guid = tgt.target_guid and tgt.target_type = defs.target_type and defs.type_meta_ver = tgt.type_meta_ver and tgt.target_name = l_target_name and tgt.target_type = mgmt_global.g_listener_target_type and (props.property_value is not null or trim(props.property_value) != '') ) loop l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record(item.property_name, mgmt_jobs.param_type_scalar,item.property_value, null); end loop; end if; end if; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('target', mgmt_jobs.param_type_scalar,l_target_name, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('type', mgmt_jobs.param_type_scalar, mgmt_global.g_listener_target_type, null); if l_target_exist = true then select property_value into l_lsnr_port from mgmt_target_properties where target_guid = l_target_guid and property_name = 'Port'; select property_value into l_oracle_home from mgmt_target_properties where target_guid = l_target_guid and property_name = 'OracleHome'; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('targetExist', mgmt_jobs.param_type_scalar, 'true', null); end if; if l_action='add' then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('LsnrName', mgmt_jobs.param_type_scalar, p_lsnr_name, null); if l_oracle_home is not null then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('OracleHome', mgmt_jobs.param_type_scalar ,l_oracle_home, null); end if; if l_lsnr_port is not null then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('Port' ,mgmt_jobs.param_type_scalar ,l_lsnr_port ,null); end if; elsif p_crsd_attrs is not null and l_action = 'modify' then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('crsdAtrrs', mgmt_jobs.param_type_scalar,p_crsd_attrs, null); end if; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('action', mgmt_jobs.param_type_scalar, l_action , null); l_excd_param := mgmt_short_string_array('eventTime'); l_curr_jobs := get_curr_jobs_name (l_job_params, l_excd_param); if l_curr_jobs.count > 0 then commit; log_info('Discarding this job as duplicate job'); return; end if; l_excd_param := mgmt_short_string_array('eventTime','Machine','srcHost','srcEmdUrl', 'nodeName', 'hostName', 'emdUrl'); l_curr_jobs := get_curr_jobs_name (l_job_params, l_excd_param); -- Look for pending/suspended relocate job on same scan listener target -- and stop/delete them in case current action happens to be update with status ONLINE. if p_is_scan and l_action='update' and p_state_or_action='ONLINE' then l_excd_param.extend(); l_excd_param(l_excd_param.count) := 'action'; l_tmp_jobs := get_curr_jobs_name (l_job_params, l_excd_param); for i in 1..l_tmp_jobs.count loop select count(1) into l_cnt from mgmt_job job, mgmt_job_parameter jp where job.job_type='EventBasedProcessing' and job.job_id=jp.job_id and job.job_name=l_tmp_jobs(i) and jp.parameter_name='action' and jp.scalar_value='relocate'; if(l_cnt > 0) then l_curr_jobs.extend(); l_curr_jobs(l_curr_jobs.count) := l_tmp_jobs(i); log_info('Added relocate job to be deleted :'|| l_tmp_jobs(i)); end if; end loop; end if; for i in 1..l_curr_jobs.count loop begin mgmt_jobs.stop_all_executions(l_curr_jobs(i)); log_info('Deleted similar job :'|| l_curr_jobs(i)); commit; exception when others then log_info('handle_listener_event(): Exception: ' || SQLERRM); end ; end loop; l_job_targets.extend(1); l_job_targets(1) := mgmt_job_target_record(emd_url_to_target(p_emd_url), mgmt_global.g_agent_target_type); log_info('Submitting job for listener target'); submit_target_proc_job(l_job_targets,l_job_params, l_job_id); end handle_listener_event; procedure handle_asm_event ( p_asm_name varchar2, p_oracle_home varchar2, p_host varchar2, p_state_or_action varchar2, p_job_params mgmt_job_param_list, p_emd_url mgmt_targets.emd_url%type, p_crsd_attrs varchar2, p_event_time varchar2, p_agent_status_evt boolean ) is l_job_targets mgmt_job_target_list := mgmt_job_target_list(); l_action varchar2(12); l_job_params mgmt_job_param_list := p_job_params; l_target_name mgmt_targets.target_name%type; l_target_guid mgmt_targets.target_guid%type; l_lsnr_port mgmt_target_properties.property_value%type; l_sib_tg_name mgmt_targets.target_name%type; l_sib_tg_ag_url mgmt_targets.emd_url%type; l_cnt number; l_excd_param mgmt_short_string_array; l_curr_jobs char_arr_type; l_job_id raw(16); begin begin select distinct t1.target_guid into l_target_guid from mgmt$target_properties t1, (select target_guid from mgmt_target_properties where property_name = 'OracleHome' and property_value = p_oracle_home) t2, (select target_guid from mgmt_target_properties where property_name = 'SID' and property_value = p_asm_name ) t3 where t1.target_type = mgmt_global.g_asm_target_type and t1.target_guid = t2.target_guid and t1.target_guid = t3.target_guid; exception when others then l_target_guid := null; end; l_action := get_action(l_target_guid, p_state_or_action, p_host, p_agent_status_evt); if l_action is null then --TO raise alert saying unknown action return; end if; if l_action='add' then select count(*) into l_cnt from mgmt_job job, mgmt_job_parameter jp where job.job_type='Config' and job.is_corrective_action=0 and job.job_id = jp.job_id and jp.parameter_name='configType' and jp.scalar_value='CONFIG_TYPE_CREATE_ASM'; if l_cnt >0 then log_info('Create ASM job of type Config exist, aborting futher processing for add action' ); commit; return; end if; l_target_name := get_target_name(p_asm_name||'_'||p_host, mgmt_global.g_asm_target_type); if l_target_name is null then log_info('Target name exist aborting further processing'); return; end if; else select target_name into l_target_name from mgmt_targets where target_guid = l_target_guid; clear_delete_flag(l_target_guid); end if; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('target', mgmt_jobs.param_type_scalar, l_target_name, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('type', mgmt_jobs.param_type_scalar, mgmt_global.g_asm_target_type, null); if l_action='add' then begin select distinct t1.property_value into l_lsnr_port from mgmt$target_properties t1, (select target_guid from mgmt_target_properties where property_name = 'OracleHome' and property_value = p_oracle_home) t2 where t1.target_type = mgmt_global.g_asm_target_type and t1.property_name = 'Port' and t1.target_guid = t2.target_guid ; exception when others then l_lsnr_port := null; end; -- if asm instance port is null get default listener port if l_lsnr_port is null then begin select distinct property_value into l_lsnr_port from mgmt_target_properties where property_name='Port' and target_guid in (select distinct target_guid from mgmt$target_properties where target_type=mgmt_global.g_listener_target_type and property_name='LsnrName' and property_value='LISTENER'); exception when others then l_lsnr_port := null; end; end if; l_cnt := l_job_params.count; l_job_params.extend(3); l_job_params(l_cnt+1) := mgmt_job_param_record('SID', mgmt_jobs.param_type_scalar, p_asm_name ,null); l_job_params(l_cnt+2) := mgmt_job_param_record('OracleHome', mgmt_jobs.param_type_scalar ,p_oracle_home, null); l_job_params(l_cnt+3) := mgmt_job_param_record('MachineName', mgmt_jobs.param_type_scalar ,p_host, null); if l_lsnr_port is not null then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('Port' ,mgmt_jobs.param_type_scalar ,l_lsnr_port ,null); end if; begin select tgt.target_name, tgt.emd_url into l_sib_tg_name, l_sib_tg_ag_url from mgmt_targets tgt, mgmt_targets agt, mgmt_current_availability ca where tgt.target_type =mgmt_global.g_asm_target_type and tgt.emd_url = agt.emd_url and agt.target_type =mgmt_global.g_agent_target_type and agt.target_guid = ca.target_guid and ca.current_status = mgmt_global.g_status_up and rownum = 1; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('sibTargetName' ,mgmt_jobs.param_type_scalar ,l_sib_tg_name ,null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('sibEmdUrl' ,mgmt_jobs.param_type_scalar ,l_sib_tg_ag_url ,null); exception when others then log_info('Error occured while fetching/adding asm sibling target info, message: '|| SQLERRM ); end; elsif p_crsd_attrs is not null and l_action = 'modify' then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('crsdAtrrs', mgmt_jobs.param_type_scalar,p_crsd_attrs, null); end if; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('action', mgmt_jobs.param_type_scalar, l_action , null); l_job_targets.extend(1); l_excd_param := mgmt_short_string_array('eventTime','sibTargetName','sibEmdUrl'); l_curr_jobs := get_curr_jobs_name (l_job_params, l_excd_param); if l_curr_jobs.count > 0 then commit; log_info('Discarding this job as duplicate job'); return; end if; l_job_targets(1) := mgmt_job_target_record(emd_url_to_target(p_emd_url), mgmt_global.g_agent_target_type); log_info('Submitting job for asm target'); submit_target_proc_job(l_job_targets,l_job_params, l_job_id); end handle_asm_event; function get_instance_vip ( p_db_target_guid mgmt_targets.target_guid%type, p_host mgmt_target_properties.property_value%type ) return varchar2 is l_target_guid mgmt_targets.target_guid%type; l_lsnr_port mgmt_target_properties.property_value%type; l_lsnr_name mgmt_target_properties.property_value%type; l_lsnr_machine mgmt_target_properties.property_value%type; l_lsnr_vip mgmt_target_properties.property_value%type; begin select property_value into l_lsnr_port from mgmt_target_properties where target_guid=p_db_target_guid and property_Name='Port'; for item in (select member_target_guid from mgmt_target_memberships where composite_target_guid= p_db_target_guid) loop begin select property_value into l_lsnr_machine from mgmt_target_properties where target_guid=item.member_target_guid and property_Name='MachineName'; log_info('l_lsnr_machine'||l_lsnr_machine); select t1.target_guid into l_target_guid from mgmt_targets t1, (select target_guid from mgmt_target_properties where property_name = 'Machine' and ( regexp_like(property_value,'^'||l_lsnr_machine||'(\.?)','i') or regexp_like(l_lsnr_machine,'^'||property_value||'(\.?)','i')) ) t2, (select target_guid from mgmt_target_properties where property_name = 'Port' and property_value = l_lsnr_port) t3 where t1.target_guid = t2.target_guid and t1.target_guid = t3.target_guid and t1.target_type = 'oracle_listener' and rownum=1; exception when others then log_info('Exception occured'||SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); null; end; log_info('l_target_guid'||l_target_guid); exit when l_target_guid is not null; end loop; select property_value into l_lsnr_name from mgmt_target_properties where target_guid=l_target_guid and property_Name='LsnrName'; log_info('l_lsnr_name'||l_lsnr_name); select t1.target_guid INTO l_target_guid from mgmt_targets t1, (select target_guid from mgmt_target_properties where property_name = 'LsnrName' and property_value = l_lsnr_name) t2, (select target_guid from mgmt_target_properties where property_name = 'Port' and property_value = l_lsnr_port) t3 where t1.target_guid = t2.target_guid and t1.target_guid = t3.target_guid and t1.target_type = 'oracle_listener' and t1.host_name=p_host and rownum=1; log_info('l_target_guid'||l_target_guid); select property_value into l_lsnr_vip from mgmt_target_properties where target_guid=l_target_guid and property_Name='Machine'; log_info('l_lsnr_vip'||l_lsnr_vip); return l_lsnr_vip; exception when others then log_info('Exception occured'||SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); return l_lsnr_vip; end get_instance_vip; procedure handle_instance_event ( p_db_name varchar2, p_inst_name varchar2, p_host varchar2, p_cls_name varchar2, p_state_or_action varchar2, p_job_params mgmt_job_param_list, p_emd_url mgmt_targets.emd_url%type, p_crsd_attrs varchar2, p_event_time varchar2, p_agent_status_evt boolean, p_db_type_computed_val varchar2 ) is l_job_targets mgmt_job_target_list := mgmt_job_target_list(); l_action varchar2(12); l_db_target mgmt_targets.target_name%type; l_db_agent mgmt_targets.emd_url%type; l_src_emd_url mgmt_targets.emd_url%type; l_src_host mgmt_targets.host_name%type; l_job_params mgmt_job_param_list := p_job_params; l_target_name mgmt_targets.target_name%type; l_target_guid mgmt_targets.target_guid%type; l_par_target_guid mgmt_targets.target_guid%type; l_oracle_home mgmt_target_properties.property_value%type; l_lsnr_port mgmt_target_properties.property_value%type; l_lsnr_vip mgmt_target_properties.property_value%type; l_service_db_str constant varchar2(64) := 'POLICY_MANAGED:'; l_cnt number; l_pos number; l_db_conf_type varchar2(32); l_db_type varchar2(32); l_src_node_name varchar2(64); l_curr_jobs char_arr_type; l_excd_param mgmt_short_string_array; l_status number; l_dest_target_name mgmt_targets.target_name%TYPE; l_dest_host mgmt_targets.target_name%TYPE; l_job_id raw(16); begin log_info('p_db_name: '||p_db_name ||' p_inst_name: ' ||p_inst_name ||' p_host: ' ||p_host||' p_state_or_action: '||p_state_or_action ||' p_emd_url: '||p_emd_url ||' p_crsd_attrs: '||p_crsd_attrs ||' p_event_time: '||p_event_time ||' p_db_type_computed_val: '||p_db_type_computed_val); begin select t1.target_guid, t1.target_name, t1.emd_url INTO l_par_target_guid, l_db_target, l_db_agent from mgmt_targets t1, (select target_guid from mgmt_target_properties where property_name = 'DBName' and lower(property_value) = lower(p_db_name)) t2, (select target_guid from mgmt_target_properties where property_name = 'RACOption' and property_value = 'YES') t3 where t1.target_type = mgmt_global.g_rac_database_target_type and t1.target_guid = t2.target_guid and t1.target_guid = t3.target_guid; exception when others then log_info('Error occured during retrieving parent rac databae target info (abort process), message: '|| SQLERRM ); return; end; if p_db_type_computed_val is null then l_db_conf_type := get_dbtype(l_db_target); elsif p_db_type_computed_val='YES' then l_db_conf_type := 'POLICY_MANAGED'; else l_db_conf_type := 'ADMIN_MANAGED'; end if; log_info('Database Configuration type :'|| l_db_conf_type); get_database_type(l_db_target, l_db_type); log_info('Database type :'|| l_db_type); if (l_db_conf_type = 'ADMIN_MANAGED' and l_db_type != 'RACOneNode' ) then log_info('Returning as only Policy Managed databases and RAC One Node databases are handled'); return; end if; begin select target_guid into l_target_guid from mgmt$target_properties where property_name = 'SID' and property_value = p_inst_name and target_type = mgmt_global.g_database_target_type; exception when no_data_found then l_target_guid := null; when others then begin select distinct t1.target_guid into l_target_guid from mgmt$target_properties t1, (select target_guid from mgmt_target_properties where property_name = 'MetricScope' and property_value = 'RACINST') t2, (select target_guid from mgmt_target_properties where property_name = 'DBName' and lower(property_value) = lower(p_db_name)) t3, (select target_guid from mgmt_target_properties where property_name = 'InstanceName' and property_value = p_inst_name) t4 where t1.target_type = mgmt_global.g_database_target_type and t1.target_guid = t2.target_guid and t1.target_guid = t3.target_guid and t1.target_guid = t4.target_guid and rownum = 1; exception when others then l_target_guid := null; end; end; l_action := get_action(l_target_guid, p_state_or_action, p_host, p_agent_status_evt); if l_action is null then --TO raise alert saying unknown action return; end if; if l_action = 'update' and p_state_or_action in ('ONLINE') then select count(*) into l_cnt from mgmt_target_memberships mem, mgmt_targets tgt where tgt.target_guid=mem.member_target_guid and mem.composite_target_guid = l_par_target_guid and mem.member_target_type=mgmt_global.g_database_target_type and tgt.host_name = p_host ; log_info('Assoc target instance count : '|| l_cnt); if l_cnt >= 2 then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('racTargetName' ,mgmt_jobs.param_type_scalar, l_db_target, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('checkAssocInstOrder' ,mgmt_jobs.param_type_scalar, 'true', null); end if; end if; if l_action in ('relocate', 'update') then select host_name,emd_url into l_src_host,l_src_emd_url from mgmt_targets where target_guid = l_target_guid; if l_action != 'update' then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcEmdUrl', mgmt_jobs.param_type_scalar, l_src_emd_url, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcEmdTgt', mgmt_jobs.param_type_scalar, emd_url_to_target(l_src_emd_url), null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcHost', mgmt_jobs.param_type_scalar, l_src_host, null); begin select property_value into l_src_node_name from mgmt$target_properties where property_name='NodeName' and target_type= mgmt_global.g_host_target_type and target_name = l_src_host; exception when others then l_src_node_name := substr(l_src_host,1,instr(l_src_host,'.',1)-1) ; end; if l_src_node_name is not null then log_info('srcNodeName: '||l_src_node_name); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('srcNodeName' ,mgmt_jobs.param_type_scalar, l_src_node_name, null); end if; end if; end if; if l_action='add' then l_target_name := get_target_name(l_db_target||'_'||p_inst_name, mgmt_global.g_database_target_type); if l_target_name is null then log_info('Target name exist aborting further processing'); return; end if; else select target_name into l_target_name from mgmt_targets where target_guid = l_target_guid; end if; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('target', mgmt_jobs.param_type_scalar,l_target_name, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('type', mgmt_jobs.param_type_scalar, mgmt_global.g_database_target_type , null); if l_action in ('add','relocate') then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('parentTargetName' ,mgmt_jobs.param_type_scalar, l_db_target, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('parentEmdUrl' ,mgmt_jobs.param_type_scalar, l_db_agent, null); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('SID', mgmt_jobs.param_type_scalar, p_inst_name ,null); l_lsnr_vip := get_instance_vip(l_par_target_guid, p_host); if l_lsnr_vip is not null then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('MachineName' ,mgmt_jobs.param_type_scalar, l_lsnr_vip, null); end if; end if; if l_action='add' then select property_value into l_oracle_home from mgmt_target_properties where property_name = 'OracleHome' and target_guid = l_par_target_guid; select property_value into l_lsnr_port from mgmt_target_properties where property_name = 'Port' and target_guid = l_par_target_guid; l_cnt := l_job_params.count; l_job_params.extend(2); l_job_params(l_cnt+1) := mgmt_job_param_record('OracleHome', mgmt_jobs.param_type_scalar ,l_oracle_home, null); l_job_params(l_cnt+2) := mgmt_job_param_record('Port' ,mgmt_jobs.param_type_scalar ,l_lsnr_port ,null); elsif p_crsd_attrs is not null and l_action ='modify' then l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('crsdAtrrs', mgmt_jobs.param_type_scalar,p_crsd_attrs, null); end if; l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('action', mgmt_jobs.param_type_scalar, l_action , null); l_excd_param := mgmt_short_string_array('eventTime','parentEmdUrl','checkAssocInstOrder'); l_curr_jobs := get_curr_jobs_name (l_job_params, l_excd_param); if l_curr_jobs.count > 0 then commit; log_info('Discarding this job as duplicate job'); return; end if; l_excd_param := mgmt_short_string_array('eventTime','MachineName','parentEmdUrl', 'srcNodeName', 'srcHost','srcEmdTgt','srcEmdUrl','checkAssocInstOrder', 'action','nodeName', 'hostName', 'emdUrl'); l_curr_jobs := get_curr_jobs_name (l_job_params, l_excd_param); begin for i in 1..l_curr_jobs.count loop mgmt_jobs.stop_all_executions(l_curr_jobs(i)); log_info('Deleted similar job :'|| l_curr_jobs(i)); commit; end loop; exception when others then log_info('handle_instance_event(): Exception: ' || SQLERRM); end ; l_job_targets.extend(1); l_job_targets(1) := mgmt_job_target_record(emd_url_to_target(p_emd_url), mgmt_global.g_agent_target_type); log_info('Submitting job for db instance target'); submit_target_proc_job(l_job_targets, l_job_params, l_job_id); log_info('RAVI l_db_type = ' || l_db_type || ' p_state_or_action = ' || p_state_or_action); log_info('RAVI l_db_target = ' || l_db_target || ' p_inst_name = ' || p_inst_name); if (l_db_type = 'RACOneNode' and p_state_or_action = 'OFFLINE') then log_info('RAVI get dest target name and status'); select m.member_target_name, a.current_status into l_dest_target_name, l_status from mgmt_target_memberships m, mgmt_current_availability a where m.composite_target_name = l_db_target and m.composite_target_type = 'rac_database' and m.member_target_name != (select target_name from mgmt_targets where target_guid = l_target_guid) and m.member_target_type = 'oracle_database' and a.target_guid = mgmt_target.get_target_guid(m.member_target_name, m.member_target_type); select host_name into l_dest_host from mgmt_targets where target_name = l_dest_target_name and target_type = 'oracle_database'; if (l_status = 1) then log_info('Omotion of ' || l_db_target || ' database detected from ' || p_host || ' to ' || l_dest_host); update_config_activities_table(l_job_id, l_dest_target_name, 'oracle_database', 0, -- status succeeded 'OMOTION', sysdate, p_cls_name, l_dest_host, p_host, null); commit; log_info('RAVI done upate'); end if; end if; end handle_instance_event; -- Accept job that has custom parameters from the specific -- handlers, setup the common job parameters and then, -- submit the job. procedure submit_custom_job ( tokens in long_string_array, add_attr in mgmt_namevalue_array, tgt_guid in mgmt_targets.target_guid%type, action in varchar2, p_job_params in mgmt_job_param_list ) is job_params mgmt_job_param_list := p_job_params; l_job_targets mgmt_job_target_list := mgmt_job_target_list(); l_cls_name varchar2(64); l_emd_url varchar2(512); l_cls_agent varchar2(512); l_cls_target varchar2(512); l_target varchar2(512); l_curr_jobs char_arr_type; l_job_id raw(16); begin job_params.extend(); job_params(job_params.count) := mgmt_job_param_record('eventTime', mgmt_jobs.param_type_scalar, tokens(5), null); log_info('eventTime: ' || tokens(5)); select property_value into l_cls_name from mgmt_target_properties where target_guid = tgt_guid and property_name = 'ClusterName'; job_params.extend(); job_params(job_params.count) := mgmt_job_param_record('clusterName', mgmt_jobs.param_type_scalar, l_cls_name, null); log_info('clusterName: ' || l_cls_name); select emd_url into l_emd_url from mgmt_targets where target_type = 'cluster' and target_name = l_cls_name; job_params.extend(); job_params(job_params.count) := mgmt_job_param_record('target', mgmt_jobs.param_type_scalar, l_cls_name, null); log_info('target: ' || l_cls_name); job_params.extend(); job_params(job_params.count) := mgmt_job_param_record('emdUrl', mgmt_jobs.param_type_scalar, l_emd_url, null); log_info('emdUrl: ' || l_emd_url); job_params.extend(); job_params(job_params.count) := mgmt_job_param_record('action', mgmt_jobs.param_type_scalar, action , null); job_params.extend(); job_params(job_params.count) := mgmt_job_param_record('type', mgmt_jobs.param_type_scalar, mgmt_global.g_cluster_target_type , null); l_target := emd_url_to_target(l_emd_url); log_info('action: ' || action); log_info('type: ' || mgmt_global.g_cluster_target_type); log_info('Target: ' || l_target); l_curr_jobs := get_curr_jobs_name (job_params, mgmt_short_string_array('eventTime')); if l_curr_jobs.count > 0 then commit; log_info('Discarding this job as duplicate job'); return; end if; l_job_targets.extend(1); l_job_targets(1) := mgmt_job_target_record(l_target, mgmt_global.g_agent_target_type); submit_target_proc_job(l_job_targets, job_params, l_job_id); log_info('Job submission successful'); exception when others then log_info('submit_custom_job(): Exception: ' || SQLERRM); end submit_custom_job; -- Submits job for database server pool list change procedure handle_dbconfig_srvpool_change ( dbname in varchar2, srvpool in varchar2, tgt_guid in mgmt_targets.target_guid%type, tokens in long_string_array, add_attr in mgmt_namevalue_array ) is l_jobparams mgmt_job_param_list := mgmt_job_param_list(); l_change_log varchar2(1024); l_action constant varchar2(32) := 'dbServerPoolChange'; begin log_info('handle_dbconfig_srvpool_change()'); l_jobparams.extend(); l_jobparams(l_jobparams.count) := mgmt_job_param_record('dbName', mgmt_jobs.param_type_scalar, dbname, null); l_jobparams.extend(); l_jobparams(l_jobparams.count) := mgmt_job_param_record('serverPool', mgmt_jobs.param_type_scalar, srvpool, null); submit_custom_job(tokens, add_attr, tgt_guid, l_action, l_jobparams); exception when others then log_info('handle_dbconfig_srvpool_change(): Exception: ' || SQLERRM); end handle_dbconfig_srvpool_change; -- Submits job for server pool min/max size change procedure handle_srvpool_state_assigned ( name in varchar2, affected_server in varchar2, reason in varchar2, tgt_guid in mgmt_targets.target_guid%type, tokens in long_string_array, add_attr in mgmt_namevalue_array ) is l_jobparams mgmt_job_param_list := mgmt_job_param_list(); l_action constant varchar2(32) := 'serverPoolSizeChange'; begin log_info('handle_srvpool_state_assigned()'); l_jobparams.extend(); l_jobparams(l_jobparams.count) := mgmt_job_param_record('serverPoolName', mgmt_jobs.param_type_scalar, name, null); l_jobparams.extend(); l_jobparams(l_jobparams.count) := mgmt_job_param_record('affectedServer', mgmt_jobs.param_type_scalar, affected_server, null); l_jobparams.extend(); l_jobparams(l_jobparams.count) := mgmt_job_param_record('reason', mgmt_jobs.param_type_scalar, reason, null); submit_custom_job(tokens, add_attr, tgt_guid, l_action, l_jobparams); exception when others then log_info('handle_srvpool_state_assigned(): Exception: ' || SQLERRM); end handle_srvpool_state_assigned; -- Resource profile change event handler procedure handle_resource_profile_change ( tokens in long_string_array, add_attr in mgmt_namevalue_array, tgt_guid in mgmt_targets.target_guid%type ) is l_resource_type varchar2(64); l_operation varchar2(64); l_srvpool varchar2(1024); l_dbname varchar2(64); begin -- Determine if this event is related to db server pool modification. l_resource_type := tokens(3); l_operation := tokens(4); l_srvpool := get_attr_val(add_attr,'SERVER_POOLS'); l_dbname := get_attr_val(add_attr,'DB_UNIQUE_NAME'); if((l_resource_type = 'ora.database.type') and (l_operation = 'MODIFY') and (l_srvpool is not null)) then log_info('CRS_RESOURCE_PROFILE_CHANGE with operation: MODIFY, and server pools : ' || l_srvpool || ' for db : ' || l_dbname); handle_dbconfig_srvpool_change(l_dbname, l_srvpool, tgt_guid, tokens, add_attr); else log_info('CRS_RESOURCE_PROFILE_CHANGE event ignored as it does not meet required criteria.'); end if; exception when others then log_info('handle_resource_profile_change(): Exception: ' || SQLERRM); end handle_resource_profile_change; -- Server pool state change event handler procedure handle_srv_pool_state_change ( tokens in long_string_array, add_attr in mgmt_namevalue_array, tgt_guid in mgmt_targets.target_guid%type ) is l_operation varchar2(64); l_srvpool varchar2(64); l_affected_server varchar2(64); l_reason varchar2(64); begin -- Determine if this event is for ASSIGNED server pool state change operation l_operation := tokens(4); if(l_operation = 'ASSIGNED') then l_srvpool := tokens(2); l_affected_server := get_attr_val(add_attr,'AFFECTED_SERVER'); l_reason := get_attr_val(add_attr,'REASON'); log_info('CRS_SERVER_POOL_STATE_CHANGE received in handle_srv_pool_state_change() with server pool: ' || l_srvpool ||', Affected Server: ' || l_affected_server || ', Cause: ' || l_reason); handle_srvpool_state_assigned(l_srvpool, l_affected_server, l_reason, tgt_guid, tokens, add_attr); end if; exception when others then log_info('handle_srv_pool_state_change(): Exception: ' || SQLERRM); end handle_srv_pool_state_change; /* message format event name(1), resource name(2), resource type(3), status(4), event time(5), metric source (6), event body (7) */ PROCEDURE rac_crs_callback_proc ( violation in mgmt_violation_summary_rec, saveRow out number ) is pragma autonomous_transaction; type res_target_type_map is table of varchar2(32) index by varchar2(32); l_crs_event_type varchar2(64); l_status varchar2(64); l_message varchar2(4000); l_tokens long_string_array; l_temp_tokens long_string_array; l_resource_host varchar2(64); l_resource_type varchar2(64); l_crs_event_name varchar2(64); l_inst_name varchar2(64); l_target_guid mgmt_targets.target_guid%TYPE; l_name varchar2(64); l_node_name varchar2(64); l_emd_url varchar2(64); l_crsd_atrr varchar2(1024); l_job_params mgmt_job_param_list; l_action_or_state varchar2(32); l_crs_attr mgmt_namevalue_array; l_add_attr mgmt_namevalue_array; l_nvobj mgmt_namevalue_obj; l_target_name mgmt_targets.target_name%type; l_cls_home mgmt_target_properties.property_value%type; l_cls_name mgmt_target_properties.property_value%type; l_cnt number; l_agent_status_evt boolean :=false; l_target_guid_array mgmt_target_guid_array; type_map res_target_type_map; l_stat_evt constant varchar2(64) := 'CRS_RESOURCE_STATE_CHANGE'; l_prof_evt constant varchar2(64) := 'CRS_RESOURCE_PROFILE_CHANGE'; l_node_evt constant varchar2(64) := 'CRS_SERVER_STATE_CHANGE'; l_srvpool_state_evt constant varchar2(64) := 'CRS_SERVER_POOL_STATE_CHANGE'; begin saveRow := 0; log_info('Inside rac_crs_callback_proc(). Event callback recieved at ' || sysdate); l_message := violation.MESSAGE; log_info('Violation message: ' || l_message); long_string_tokenizer('#sEp#', violation.MESSAGE, l_tokens); if l_tokens.count != 7 then log_info('Violation message: token count not equal to 7 saving msg and aboprt futher processing'); saveRow := 1; return; end if; l_crs_event_name :=l_tokens(1); l_resource_type := l_tokens(3); l_agent_status_evt := (l_tokens(6)='AGENT_GENERATED'); type_map('ora.database.type') := mgmt_global.g_database_target_type; type_map('ora.listener.type') := mgmt_global.g_listener_target_type; type_map('ora.scan_listener.type') := mgmt_global.g_listener_target_type; type_map('ora.asm.type') := mgmt_global.g_asm_target_type; if l_crs_event_name=l_prof_evt and l_tokens(4)='DELETE' and mgmt_global.g_database_target_type != type_map(l_tokens(3)) then log_info('Inside detete target block'); begin l_target_guid_array := get_target_guid_from_resource(type_map(l_tokens(3)) ,l_tokens(2)); for l_cnt in 1..l_target_guid_array.count loop l_target_guid := l_target_guid_array(l_cnt); log_info('Inside detete target block : '|| rawtohex(l_target_guid)); begin log_info('Inside detete target block : insert delete flag'); insert into mgmt_target_properties (property_value, property_name, property_type, target_guid) values (to_timestamp(l_tokens(5),'YYYY-MM-DD HH24:MI:SS'),'TargetDeleteTimestamp','INSTANCE', l_target_guid); exception when others then log_info('Inside detete target block : insert failed (property exist) trying update'); update mgmt_target_properties set property_value = to_timestamp(l_tokens(5),'YYYY-MM-DD HH24:MI:SS') where target_guid = l_target_guid and property_name = 'TargetDeleteTimestamp' and property_type = 'INSTANCE'; end; end loop; exception when others then log_info('Error during delete operation: '|| SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); end; commit; return; end if ; if l_crs_event_name not in (l_stat_evt, l_node_evt, l_prof_evt, l_srvpool_state_evt) then log_info('Aborting further processing, not supported event: ' || l_crs_event_name); commit; return; end if; l_add_attr := parse_nv_pair(l_tokens(7), ';', '='); -- Determine if this is a resource profile change event. if(l_crs_event_name = l_prof_evt) then handle_resource_profile_change(l_tokens, l_add_attr, violation.target_guid); return; end if; -- Determine if this a server pool state change event if(l_crs_event_name = l_srvpool_state_evt) then handle_srv_pool_state_change(l_tokens, l_add_attr, violation.target_guid); return; end if; if l_crs_event_name = l_node_evt then if l_agent_status_evt then l_node_name := l_tokens(2); -- Resource Name else l_node_name := get_attr_val(l_add_attr,'SERVER_NAME'); end if; else l_node_name := get_attr_val(l_add_attr,'RESOURCE_LOCATION'); end if; l_resource_host := get_host_from_node(l_node_name); if l_resource_host is null and violation.message != 'New node(s) added to cluster' and l_crs_event_name = l_node_evt and l_tokens(4) in ('ONLINE') then log_info('Resource host not monitored raising alert'); mgmt_violation.log_violation( p_target_guid => violation.target_guid, p_policy_guid => violation.policy_guid, p_key_value => 'add_node_alert_'||l_node_name, p_collection_timestamp => violation.collection_timestamp, p_violation_level => MGMT_GLOBAL.G_SEVERITY_WARNING, p_violation_type => 0, p_value => violation.value, p_string_value => l_node_name, p_message => 'New node(s) added to cluster', p_message_nlsid => 'add_node_alert', p_message_params => l_node_name, p_action_message => 'Invoke Enterprise Manager reconfiguration from Reconfiguration Pending drilldown in Cluster home page', p_action_message_nlsid => 'add_node_alert_action', p_action_message_params => 'p_action_message_params' ); commit; return; end if; if l_resource_host is null then log_info('Abort : Resource host not monitored'); commit; return; end if; if l_resource_type not in ('ora.database.type', 'ora.listener.type', 'ora.scan_listener.type', 'ora.asm.type') then log_info('Abort : Resource Type '||l_resource_type||'not supported'); commit; return; end if; l_job_params := mgmt_job_param_list(); log_info('Node: ' || l_node_name||' Host Name : '|| l_resource_host); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('nodeName', mgmt_jobs.param_type_scalar, l_node_name, null); log_info('Event Time: ' || l_tokens(5)); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('eventTime', mgmt_jobs.param_type_scalar, to_char(mgmt_global.from_utc(to_date(l_tokens(5),'YYYY-MM-DD HH24:MI:SS'),em_target.get_repository_timezone()),'YYYY-MM-DD HH24:MI:SS'), null); select property_value into l_cls_home from mgmt_target_properties where target_guid = violation.target_guid and property_name = 'OracleHome'; log_info('CRS Home: ' || l_cls_home); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('crsHome', mgmt_jobs.param_type_scalar, l_cls_home, null); -- At crs stack startup cluster name property might not be evaluated in first attemt. -- To handle such case substitute the cluster name with cluster target name -- (may have unpredictable effect in case cluster target name and cluster name are diffrent in EM) -- This WA is to process the event instead of dropping same. select nvl(props.property_value, tgt.target_name) into l_cls_name from mgmt_targets tgt, mgmt_target_properties props where tgt.target_guid = props.target_guid and tgt.target_guid = violation.target_guid and props.property_name = 'ClusterName'; log_info('Cluster Name: ' || l_cls_name); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('clusterName', mgmt_jobs.param_type_scalar, l_cls_name, null); if l_resource_host is not null then log_info('Resource Host: ' || l_resource_host); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('hostName', mgmt_jobs.param_type_scalar, l_resource_host, null); select emd_url into l_emd_url from mgmt_targets where target_name = l_resource_host and target_type = mgmt_global.g_host_target_type; log_info('Agent Url: ' || l_emd_url); l_job_params.extend(); l_job_params(l_job_params.count) := mgmt_job_param_record('emdUrl', mgmt_jobs.param_type_scalar, l_emd_url , null); end if; l_action_or_state := l_tokens(4); log_info('Action or State: ' || l_action_or_state); if(l_crs_event_name = l_node_evt) then handle_node_event(l_resource_host, l_action_or_state, violation.target_guid, l_job_params, l_emd_url); return; end if; if l_resource_type = 'ora.database.type' or l_resource_type = 'ora.asm.type' then l_inst_name := get_attr_val(l_add_attr,'INSTANCE_NAME'); -- TODO delete below check in future if(l_inst_name is null ) then l_inst_name := get_attr_val(l_add_attr,'GEN_USR_ORA_INST_NAME'); end if; log_info('database/asm instance name: ' || l_inst_name ); end if; long_string_tokenizer('.', l_tokens(2), l_temp_tokens); l_name := l_temp_tokens(2); log_info('Resource Logical name: ' || l_name ); if l_crs_event_name = l_stat_evt or l_crs_event_name = l_prof_evt then l_crsd_atrr := get_attr_val(l_add_attr,'PROFILE_CHANGE'); case when l_resource_type = 'ora.database.type' then log_info('Calling handle_instance_event() proc'); handle_instance_event(l_name, l_inst_name, l_resource_host, l_cls_name, l_action_or_state, l_job_params, l_emd_url, l_crsd_atrr, l_tokens(5), l_agent_status_evt, get_attr_val(l_add_attr,'DB_POLICY_MANAGED_COMPUTED')); when l_resource_type = 'ora.listener.type' then log_info('handle_listener_event() proc for regular listener'); handle_listener_event(l_name, l_resource_host, l_action_or_state, l_job_params, l_emd_url, l_crsd_atrr, l_tokens(5), false, l_agent_status_evt); when l_resource_type = 'ora.scan_listener.type' then log_info('handle_listener_event() proc for scan listener'); handle_listener_event(l_name, l_resource_host, l_action_or_state, l_job_params, l_emd_url, l_crsd_atrr, l_tokens(5), true, l_agent_status_evt); when l_resource_type = 'ora.asm.type' then log_info('handle_asm_event() proc'); handle_asm_event(l_inst_name, l_cls_home,l_resource_host, l_action_or_state, l_job_params, l_emd_url, l_crsd_atrr,l_tokens(5), l_agent_status_evt); end case; end if; exception when others then saveRow := 0; log_info('Error occured during event call back processing, message: '|| SQLERRM||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); end rac_crs_callback_proc; procedure update_config_activities_table ( p_txn_id in mgmt_config_activities.txn_id%type, p_name in mgmt_targets.target_name%type, p_type in mgmt_targets.target_type%type, p_status_code in mgmt_config_activities.status_code%type, p_config_type in mgmt_config_activities.config_type%type, p_time in varchar2 default to_char(sysdate), p_cluster_name in mgmt_config_activities.cluster_name%type, p_host_name in mgmt_config_activities.host_name%type, p_src_host_name in mgmt_config_activities.host_name%type default '', p_param_nv in mgmt_namevalue_array default null ) is l_target_guid mgmt_targets.target_guid%type; l_event_time mgmt_config_activities.occurence_time%type; begin l_event_time := to_timestamp(p_time,'YYYY-MM-DD HH24:MI:SS'); begin select target_guid into l_target_guid from mgmt_targets where target_name=p_name and target_type=p_type; exception when no_data_found then l_target_guid := mgmt_target.generate_target_guid(p_name,p_type); when others then log_info('Error occured during info fetching, message: '|| SQLERRM); return; end; log_info('Recording activity in activity table'); insert into mgmt_config_activities values(p_txn_id, l_target_guid, p_cluster_name, p_status_code, p_config_type, l_event_time,p_host_name, p_src_host_name); if p_param_nv is not null then log_info('Recording activity in failed activity table'); for i in 1..p_param_nv.count loop insert into mgmt_failed_config_activities values(hextoraw(p_txn_id), l_target_guid, p_param_nv(i).name, p_param_nv(i).value); end loop; end if; exception when others then log_info('Error occured during config activities table update, message: '|| SQLERRM); end update_config_activities_table; procedure host_add_callback ( p_target_name in mgmt_targets.target_name%type, p_target_type in mgmt_targets.target_type%type, p_target_guid in mgmt_targets.target_guid%type ) is begin log_info('Host add call back recieved for host:'|| p_target_name); delete from mgmt_violations where policy_guid = hextoraw('2B3903B93E8D6BC74BD3E41815A6CEAF') and key_value like 'add_node_alert_%' and string_value in (select substr(p_target_name,1,instr(p_target_name,'.',1)-1) node from dual union select p_target_name node from dual union select property_value node from mgmt$target_properties where property_name='NodeName' and target_type= mgmt_global.g_host_target_type and target_name = p_target_name); commit; exception when others then log_info('Error occured during executing host add callback: '|| SQLERRM); end host_add_callback; -- Procedure get_cluster_resource_summary -- -- PURPOSE: this function is called for getting -- Cluster managed resource summary -- This queries mgmt_current_metrics table to get latest -- metric information from resource_status and resource_instamce metric -- given cluster -- -- IN parameters: -- target_name_in IN cluster target name -- OUT parameters: -- resource_csr as cursor of -- following ( -- resource_count, -- online, -- offline -- unknown -- intermediate -- critical -- warning PROCEDURE get_cluster_resources_summary ( v_cluster_name_in IN VARCHAR2, v_resources_csr_out OUT CURSOR_TYPE ) AS l_target_guid mgmt_targets.target_guid%TYPE; l_mg_etyp mgmt_metrics.metric_guid%TYPE; l_mg_esub mgmt_metrics.metric_guid%TYPE; l_mg_enam mgmt_metrics.metric_guid%TYPE; l_mg_comp mgmt_metrics.metric_guid%TYPE; l_mg_msrc mgmt_metrics.metric_guid%TYPE; l_last_coll mgmt_current_metrics.collection_timestamp%TYPE; l_critical INTEGER; l_warning INTEGER; BEGIN l_target_guid := MGMT_TARGET.GET_TARGET_GUID(v_cluster_name_in, 'cluster'); l_mg_etyp := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_entity_type'); l_mg_esub := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_entity_subtype'); l_mg_enam := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_entity_name'); l_mg_comp := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_event_composite_status'); l_mg_msrc := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','metric_source'); SELECT max(a.collection_timestamp) INTO l_last_coll FROM mgmt_current_metrics a WHERE a.target_guid = l_target_guid AND a.metric_guid = l_mg_msrc AND a.string_value = 'Fetchlet'; IF l_last_coll IS NULL THEN l_last_coll := SYSDATE - 1; END IF; SELECT SUM(DECODE(s.VIOLATION_LEVEL,25,1,0)) "critical", SUM(DECODE(s.VIOLATION_LEVEL,20,1,0)) "warning" INTO l_critical, l_warning FROM mgmt_current_violation s WHERE s.target_guid = l_target_guid AND s.policy_guid = l_mg_comp AND s.key_value LIKE 'resource%' AND s.violation_type IN (0,1,2); -- AND s.collection_timestamp >= l_last_coll; OPEN v_resources_csr_out FOR SELECT COUNT(*) "resources", SUM(DECODE(a."composite_status",'ONLINE',1,0)) "online", SUM(DECODE(a."composite_status",'ONLINE',0,1)) "offline", SUM(DECODE(a."composite_status",'UNKNOWN',1,0)) "unknown", SUM(DECODE(a."composite_status",'INTERMEDIATE',1,0)) "intermediate", l_critical "critical", l_warning "warning" FROM ( SELECT c.string_value "resource_name", b.string_value "resource_type", NVL(d.string_value,DECODE(e.string_value,'Receivelet','OFFLINE','UNKNOWN')) "composite_status", a.key_value "key_value" FROM mgmt_current_metrics a, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_current_metrics d, mgmt_current_metrics e WHERE a.target_guid = l_target_guid AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.target_guid = d.target_guid AND a.target_guid = e.target_guid AND a.metric_guid = l_mg_etyp AND b.metric_guid = l_mg_esub AND c.metric_guid = l_mg_enam AND d.metric_guid = l_mg_comp AND e.metric_guid = l_mg_msrc AND a.key_value = b.key_value AND a.key_value = c.key_value AND a.key_value = d.key_value AND a.key_value = e.key_value AND a.string_value = 'resource' AND a.collection_timestamp >= l_last_coll ) a WHERE a."key_value" LIKE 'resource%'; END get_cluster_resources_summary; -- Procedure get_cluster_resources -- -- PURPOSE: this function is called for getting -- Cluster managed resource information -- This queries mgmt_current_metrics table to get latest -- metric information from resource_status and resource_instamce metric -- given cluster -- -- IN parameters: -- target_name_in IN cluster target name -- OUT parameters: -- resource_csr as cursor of -- following ( -- resource_name, -- resource_type, -- status, -- cardinality, -- degree, -- online, -- offline -- unknown -- intermediate -- metric_source -- critical -- warning PROCEDURE get_cluster_resources ( v_cluster_name_in IN VARCHAR2, v_resources_csr_out OUT CURSOR_TYPE) AS l_target_guid mgmt_targets.target_guid%TYPE; l_mg_etyp mgmt_metrics.metric_guid%TYPE; l_mg_esub mgmt_metrics.metric_guid%TYPE; l_mg_enam mgmt_metrics.metric_guid%TYPE; l_mg_comp mgmt_metrics.metric_guid%TYPE; l_mg_card mgmt_metrics.metric_guid%TYPE; l_mg_degr mgmt_metrics.metric_guid%TYPE; l_mg_onlc mgmt_metrics.metric_guid%TYPE; l_mg_oflc mgmt_metrics.metric_guid%TYPE; l_mg_unkc mgmt_metrics.metric_guid%TYPE; l_mg_intc mgmt_metrics.metric_guid%TYPE; l_mg_msrc mgmt_metrics.metric_guid%TYPE; l_last_coll mgmt_current_metrics.collection_timestamp%TYPE; BEGIN l_target_guid := MGMT_TARGET.GET_TARGET_GUID(v_cluster_name_in, 'cluster'); l_mg_etyp := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_entity_type'); l_mg_esub := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_entity_subtype'); l_mg_enam := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_entity_name'); l_mg_comp := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','crs_event_composite_status'); l_mg_card := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','resource_status_cardinality'); l_mg_degr := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','resource_status_degree'); l_mg_onlc := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','resource_status_online_count'); l_mg_oflc := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','resource_status_offline_count'); l_mg_unkc := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','resource_status_unknown_count'); l_mg_intc := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','resource_status_intermediate_count'); l_mg_msrc := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_status','metric_source'); SELECT max(a.collection_timestamp) INTO l_last_coll FROM mgmt_current_metrics a WHERE a.target_guid = l_target_guid AND a.metric_guid = l_mg_msrc AND a.string_value = 'Fetchlet'; IF l_last_coll IS NULL THEN l_last_coll := SYSDATE - 1; END IF; OPEN v_resources_csr_out FOR SELECT c.string_value "resource_name", b.string_value "resource_type", d.string_value "composite_status", e.value "cardinality", f.value "degree", g.value "online_count", h.value "offline_count", i.value "unknown_count", j.value "intermediate", k.string_value "metric_source", DECODE(s.VIOLATION_LEVEL,25,1,0) "critical", DECODE(s.VIOLATION_LEVEL,20,1,0) "warning" FROM mgmt_current_metrics a, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_current_metrics d, mgmt_current_metrics e, mgmt_current_metrics f, mgmt_current_metrics g, mgmt_current_metrics h, mgmt_current_metrics i, mgmt_current_metrics j, mgmt_current_metrics k, ( SELECT s.violation_level, s.key_value, s.string_value FROM mgmt_current_violation s WHERE s.target_guid = l_target_guid AND s.policy_guid = l_mg_comp AND s.key_value LIKE 'resource%' AND s.violation_type IN (0,1,2) -- AND s.collection_timestamp >= l_last_coll ) s WHERE a.target_guid = l_target_guid AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.target_guid = d.target_guid AND a.target_guid = e.target_guid AND a.target_guid = f.target_guid AND a.target_guid = g.target_guid AND a.target_guid = h.target_guid AND a.target_guid = i.target_guid AND a.target_guid = j.target_guid AND a.target_guid = k.target_guid AND a.metric_guid = l_mg_etyp AND b.metric_guid = l_mg_esub AND c.metric_guid = l_mg_enam AND d.metric_guid = l_mg_comp AND e.metric_guid = l_mg_card AND f.metric_guid = l_mg_degr AND g.metric_guid = l_mg_onlc AND h.metric_guid = l_mg_oflc AND i.metric_guid = l_mg_unkc AND j.metric_guid = l_mg_intc AND k.metric_guid = l_mg_msrc AND a.key_value = b.key_value AND a.key_value = c.key_value AND a.key_value = d.key_value AND a.key_value = e.key_value AND a.key_value = f.key_value AND a.key_value = g.key_value AND a.key_value = h.key_value AND a.key_value = i.key_value AND a.key_value = j.key_value AND a.key_value = k.key_value AND a.key_value = s.key_value(+) AND a.string_value = 'resource' AND a.key_value LIKE 'resource%' AND a.collection_timestamp >= l_last_coll; END get_cluster_resources; -- Procedure get_cluster_resource_instances -- -- PURPOSE: this function is called for getting -- Cluster managed resource instance information -- This queries mgmt_current_metrics table to get latest -- metric information from resource_instance metric -- given cluster -- -- IN parameters: -- target_name_in IN cluster target name -- OUT parameters: -- resource_csr as cursor of -- following ( -- resource_name, -- resource_type, -- resource_instance_id, -- status, -- host PROCEDURE get_cluster_resource_instances ( v_cluster_name_in IN VARCHAR2, v_resources_csr_out OUT CURSOR_TYPE) AS l_target_guid mgmt_targets.target_guid%TYPE; l_mg_etyp mgmt_metrics.metric_guid%TYPE; l_mg_esub mgmt_metrics.metric_guid%TYPE; l_mg_enam mgmt_metrics.metric_guid%TYPE; l_mg_rsid mgmt_metrics.metric_guid%TYPE; l_mg_stat mgmt_metrics.metric_guid%TYPE; l_mg_node mgmt_metrics.metric_guid%TYPE; BEGIN l_target_guid := MGMT_TARGET.GET_TARGET_GUID(v_cluster_name_in, 'cluster'); l_mg_etyp := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_instance','crs_entity_type'); l_mg_esub := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_instance','crs_entity_subtype'); l_mg_enam := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_instance','crs_entity_name'); l_mg_rsid := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_instance','crs_entity_id'); l_mg_stat := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_instance','crs_event_value'); l_mg_node := MGMT_METRIC.GET_METRIC_GUID_FOR_TARGET('cluster',v_cluster_name_in,'resource_instance','crs_event_node_name'); OPEN v_resources_csr_out FOR SELECT c.string_value "resource_name", b.string_value "resource_type", f.string_value "resource_instance_id", d.string_value "status", e.string_value "node" FROM mgmt_current_metrics a, mgmt_current_metrics b, mgmt_current_metrics c, mgmt_current_metrics d, mgmt_current_metrics e, mgmt_current_metrics f WHERE a.target_guid = l_target_guid AND a.target_guid = b.target_guid AND a.target_guid = c.target_guid AND a.target_guid = d.target_guid AND a.target_guid = e.target_guid AND a.target_guid = f.target_guid AND a.metric_guid = l_mg_etyp AND b.metric_guid = l_mg_esub AND c.metric_guid = l_mg_enam AND d.metric_guid = l_mg_stat AND e.metric_guid = l_mg_node AND f.metric_guid = l_mg_rsid AND a.key_value = b.key_value AND a.key_value = c.key_value AND a.key_value = d.key_value AND a.key_value = e.key_value AND a.key_value = f.key_value AND a.string_value = 'resource' AND a.collection_timestamp > SYSDATE-2; END get_cluster_resource_instances; -- Function get_first_metric_value -- -- PURPOSE: -- return the string_value from mgmt_current_metrics -- -- IN parameters: -- v_target_name IN target name -- v_target_type IN target type -- v_metric_name IN metric name -- v_metric_column IN metric column -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_first_metric_value ( v_target_name IN VARCHAR2, v_target_type IN VARCHAR2, v_metric_name IN VARCHAR2, v_metric_column IN VARCHAR2 ) RETURN VARCHAR2 AS l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE; l_metric_guid MGMT_METRICS.METRIC_GUID%TYPE; l_value MGMT_CURRENT_METRICS.STRING_VALUE%TYPE; BEGIN l_target_guid := mgmt_target.get_target_guid(v_target_name,v_target_type); l_metric_guid := mgmt_metric.get_metric_guid(v_target_type,v_metric_name,v_metric_column); SELECT string_value INTO l_value FROM mgmt_current_metrics m WHERE m.target_guid = l_target_guid AND m.metric_guid = l_metric_guid AND ROWNUM = 1; RETURN l_value; END get_first_metric_value; -- Function get_resource_name_for_target -- -- PURPOSE: -- return resource name for em oracle internal em target -- database, listener, asm -- -- IN parameters: -- v_target_name IN target name -- v_target_type IN target type -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_resource_name_for_target ( v_target_name IN VARCHAR2, v_target_type IN VARCHAR2 ) RETURN VARCHAR2 AS BEGIN RETURN emd_rac.get_first_metric_value(v_target_name,v_target_type,'cluster_resource_name','resource_name'); END get_resource_name_for_target; -- Function get_is_has_managed -- -- PURPOSE: -- -- IN parameters: -- v_target_name IN target name -- v_target_type IN target type -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_is_has_managed ( v_target_name IN VARCHAR2, v_target_type IN VARCHAR2 ) RETURN VARCHAR2 AS BEGIN RETURN emd_rac.get_first_metric_value(v_target_name,v_target_type,'isHasManaged','isHasManaged'); EXCEPTION WHEN OTHERS THEN RETURN NULL; END get_is_has_managed; -- Function get_is_listener_scan -- -- PURPOSE: -- -- IN parameters: -- v_target_name IN target name -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_is_listener_scan ( v_target_name IN VARCHAR2 ) RETURN VARCHAR2 AS BEGIN RETURN emd_rac.get_first_metric_value(v_target_name,'oracle_listener','cluster_resource_name','isScan'); EXCEPTION WHEN OTHERS THEN RETURN NULL; END get_is_listener_scan; -- Function get_is_rac_listener -- -- PURPOSE: -- -- IN parameters: -- v_target_name IN listener target name -- OUT -- 'true' if listener target is RAC -- 'false' if not RAC Function get_is_rac_listener ( v_target_name IN VARCHAR2 ) RETURN varchar2 AS crs_resource_name VARCHAR2(100); host_cluster_name VARCHAR2(100); is_rac_listener VARCHAR2(10); BEGIN is_rac_listener := 'false'; crs_resource_name := emd_rac.get_resource_name_for_target(v_target_name,'oracle_listener'); --DBMS_OUTPUT.PUT_LINE('Cluster resource Name for the listener is '|| crs_resource_name ); -- Host containing Listener should be part of cluster select NVL(composite_target_name,'no_cluster') into host_cluster_name from mgmt_target_memberships where member_target_name= (select host_name from mgmt_targets where target_name=v_target_name and TARGET_TYPE='oracle_listener' ) and member_target_type='host' and composite_target_type='cluster'; --DBMS_OUTPUT.PUT_LINE('Is host containing listener part of cluster '|| host_cluster_name ); IF host_cluster_name != 'no_cluster' THEN IF crs_resource_name IS NOT NULL AND LENGTH(crs_resource_name) > 0 THEN is_rac_listener := 'true'; END IF; END IF; return is_rac_listener; EXCEPTION WHEN OTHERS THEN --DBMS_OUTPUT.PUT_LINE(' Exception occured '); RETURN 'false'; END get_is_rac_listener; -- Function get_target_guid_from_resource -- -- PURPOSE: -- return array of target guid for a resource name -- -- IN parameters: -- v_target_type IN target type -- v_resource_name IN resource name -- -- ERROR -- throw exception NO_DATA_FOUND FUNCTION get_target_guid_from_resource ( v_target_type IN VARCHAR2, v_resource_name IN VARCHAR2 ) RETURN MGMT_TARGET_GUID_ARRAY AS l_metric_guid MGMT_METRICS.METRIC_GUID%TYPE; l_target_guid_array MGMT_TARGET_GUID_ARRAY; BEGIN l_metric_guid := mgmt_metric.get_metric_guid(v_target_type,'cluster_resource_name','resource_name'); SELECT target_guid BULK COLLECT INTO l_target_guid_array FROM mgmt_current_metrics m WHERE m.metric_guid = l_metric_guid AND m.string_value = v_resource_name; RETURN l_target_guid_array; END get_target_guid_from_resource; END emd_rac; / show errors; CREATE OR REPLACE PACKAGE BODY mgmt_rac AS G_MODULE constant VARCHAR2(100) := 'mgmt_rac'; -- Procedure handle_cluster_tgttype_add -- -- PURPOSE: -- This is the callback function registered to be called -- when a new type_meta_ver is registered, -- for cluster target type it adds a default_collection for -- priv_intrcon -- -- IN parameters: -- target_type IN cluster target name -- meta ver IN -- PROCEDURE handle_cluster_tgttype_add( p_target_type IN VARCHAR2, p_type_meta_ver IN VARCHAR2) AS l_schedule mgmt_coll_schedule_obj ; l_dummy INTEGER(1) ; l_count INTEGER ; BEGIN IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('handle_cluster_tgttype_add:Enter' || p_target_type || '/' || p_type_meta_ver, G_MODULE) ; END IF ; IF p_target_type != 'cluster' THEN RETURN; END IF; -- check if the cluster has priv_intrcon metric defined for its meta_ver -- leave out meta_ver 4.0 as metric is defined for 4.0 BEGIN SELECT 1 INTO l_dummy FROM dual WHERE EXISTS ( SELECT 1 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 m.type_meta_ver = p_type_meta_ver ); SELECT count(*) INTO l_count FROM mgmt_coll_items WHERE target_type = p_target_type AND type_meta_ver = p_type_meta_ver AND coll_name = 'priv_intrcon_collection' ; IF l_count > 0 THEN EMDW_LOG.DEBUG('handle_cluster_tgttype_add: priv_intrcon_collection is already added for cluster '||p_type_meta_ver, G_MODULE) ; RETURN; END IF; -- Define schedule l_schedule := mgmt_coll_schedule_obj.interval_schedule(5,sysdate,null) ; mgmt_collection.add_default_collection (p_target_type=>'cluster' , p_type_meta_ver=> p_type_meta_ver, p_coll_name=>'priv_intrcon_collection', p_metrics_list=>mgmt_short_string_array('priv_intrcon'), p_coll_schedule=>l_schedule) ; EMDW_LOG.DEBUG('handle_cluster_tgttype_add: Registered priv_intrcon_collection for cluster '||p_type_meta_ver, G_MODULE) ; EXCEPTION WHEN NO_DATA_FOUND THEN EMDW_LOG.ERROR('handle_cluster_tgttype_add:Exception metric priv_intrcon not found in mgmt_metrics for type_meta_ver' ||p_type_meta_ver|| ' Message : ' || SQLERRM, G_MODULE); END; IF EMDW_LOG.P_IS_INFO_SET THEN EMDW_LOG.INFO('handle_cluster_tgttype_add:exit', G_MODULE) ; END IF ; END handle_cluster_tgttype_add; -- Procedure cluster_10_2_0_4_upgrade_post_proc -- -- PURPOSE: -- This is the post upgrade meta data change for cluster target -- when upgrading from 10.2.0.4 -- It starts the collection for metric priv_intrcon for -- already existing cluster targets with ver > 4.0 -- -- IN parameters: -- PROCEDURE cluster_10204_upgr_post_proc IS l_schedule mgmt_coll_schedule_obj ; l_metric_guid RAW(16); CURSOR c1 IS SELECT DISTINCT t.target_type, t.type_meta_ver FROM mgmt_targets t, mgmt_metrics m WHERE t.target_type = m.target_type AND t.type_meta_ver = m.type_meta_ver AND ( m.category_prop_1 = ' ' OR m.category_prop_1 = t.category_prop_1 ) AND ( m.category_prop_2 = ' ' OR m.category_prop_2 = t.category_prop_2 ) AND ( m.category_prop_3 = ' ' OR m.category_prop_3 = t.category_prop_3 ) AND ( m.category_prop_4 = ' ' OR m.category_prop_4 = t.category_prop_4 ) AND ( m.category_prop_5 = ' ' OR m.category_prop_5 = t.category_prop_5 ) AND m.metric_name = 'priv_intrcon' AND t.target_type = 'cluster' AND m.metric_column = ' ' AND NOT EXISTS ( SELECT 1 FROM mgmt_metric_collections c WHERE c.target_guid = t.target_guid AND c.metric_guid = m.metric_guid ) AND 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) ; l_metric_guid := MGMT_METRIC.get_metric_guid( 'cluster', 'priv_intrcon', ' '); --FOR x IN c1 LOOP -- em_coll_util.start_collections( -- p_target_type => x.target_type, -- p_metric_guid => l_metric_guid, -- p_coll_name=>'priv_intrcon_collection', -- p_store_metric=> MGMT_GLOBAL.G_TRUE, -- p_schedule=>l_schedule, -- p_type_meta_Ver => x.type_meta_ver -- ); -- END LOOP; COMMIT; END cluster_10204_upgr_post_proc; END mgmt_rac; / show errors;