Rem drv: Rem Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/self_monitor/self_monitor_pkgbodys.sql /st_emcore_10.2.0.4.2db11.2/2 2008/11/06 00:39:30 tsubrama Exp $ Rem Rem self_monitor_pkgbodys.sql Rem Rem Copyright (c) 2002, 2008, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem self_monitor_pkgbodys.sql - Rem Rem DESCRIPTION Rem The sql procs used by the admin ui. These are not part of the SDK. Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem tsubrama 10/31/08 - 7479617 -not show emgc invalid jobs in dbconsole Rem jsadras 10/21/08 - Backport jsadras_bug-7479372 from Rem st_emcore_10.2.0.1.0 Rem tsubrama 09/05/07 - fix for 6379184 Rem tsubrama 04/19/07 - Backport tsubrama_bug-4897258 from main Rem tsubrama 04/16/07 - fix for 4897258 Rem rmaggarw 03/05/07 - bug5856156 - use seconds internally and convert Rem to minutes while collecting metric Rem rmaggarw 03/12/07 - Backport rmaggarw_bug-5856156 from main Rem aholser 12/09/05 - Backport aholser_bug-4738438 from main Rem aholser 12/01/05 - See bug 4758438 Rem jpyang 09/15/05 - add_metaver_catprop_callback Rem aholser 09/13/05 - fix error getting metric guid Rem aholser 09/08/05 - another performance issue.... Rem aholser 08/26/05 - fix performance problem with loader dir query Rem aholser 08/26/05 - separate host from oc4j and ias Rem aholser 08/15/05 - fix metric retrieval Rem aholser 08/17/05 - metric collections Rem aholser 07/25/05 - Rem jpyang 07/13/05 - upgrade issue Rem gsbhatia 07/14/05 - Rem aholser 07/13/05 - Rem rmaggarw 06/29/05 - modify metric_loader_status query Rem gsbhatia 07/01/05 - New repmgr header impl Rem gsbhatia 06/26/05 - New repmgr header impl Rem aholser 06/27/05 - performance Rem jsadras 06/10/05 - Bug:4401166, Perf changes metric_loader_status Rem aholser 06/02/05 - Windows Rem aholser 05/18/05 - add rac Rem aholser 05/10/05 - freq changes Rem aholser 04/28/05 - leave room for last , Rem rpinnama 05/13/05 - Start the compliance collection on adding emrep Rem target Rem dcawley 04/14/05 - Change parameter from boolean Rem aholser 03/21/05 - change agent q Rem aholser 03/17/05 - remove date metrics Rem aholser 02/16/05 - use api Rem gsbhatia 02/13/05 - updating repmgr header Rem aholser 02/08/05 - fix 24hr formatting Rem gsbhatia 02/07/05 - updating repmgr header Rem jsadras 01/07/05 - repometric Rem aholser 01/21/05 - fix errors with unregistered jobs Rem ramalhot 01/17/05 - g_member_guid->g_contains_guid Rem ktlaw 01/11/05 - add repmgr header Rem rpinnama 01/06/05 - Fix duplicate target count (list only unresolved Rem entries) Rem aholser 12/28/04 - fix multiple hosts Rem aholser 12/14/04 - Rem aholser 08/26/04 - multiple host targets Rem ramalhot 08/25/04 - cutover to new assoc tables Rem aholser 07/15/04 - Bug 3760873 - TBSP query optimization Rem dcawley 06/24/04 - Change query for getting notification backlog Rem aholser 06/22/04 - indicate if host is target for agent node info Rem aholser 06/03/04 - support multiple oc4js on host Rem aholser 05/29/04 - Rem aholser 05/12/04 - 10.2 metrics Rem aholser 03/18/04 - fix missing alerts Rem aholser 03/16/04 - add no agents metric Rem aholser 03/15/04 - fix numeric error for error conditions Rem aholser 03/15/04 - return 'since' as timestamp for nlsing Rem aholser 03/15/04 - fix performance problems on overview page when Rem large mgmt_metrics table Rem aholser 03/10/04 - support multiple dbms job of same name Rem aholser 02/09/04 - remove date dbms metrics Rem aholser 02/05/04 - change job dispatcher backlog metric Rem aholser 02/03/04 - 3277539 Rem aholser 01/06/04 - fix multiple job dispatcher entries Rem aholser 12/01/03 - fix for missing host Rem aholser 11/14/03 - fix bad oms name parsing Rem aholser 11/06/03 - improve loader graph performance Rem lgloyd 11/05/03 - remove tz fn call from query Rem lgloyd 11/03/03 - performance work Rem aholser 10/23/03 - no emrep, misc Rem aholser 10/17/03 - some minor fixes Rem aholser 10/10/03 - flag availability invalid if no agent Rem aholser 09/18/03 - Rem aholser 09/07/03 - Rem aholser 09/05/03 - add management Loader Status Rem aholser 08/20/03 - target add callback to register target metrics Rem aholser 08/19/03 - 3097990 Rem dcawley 07/24/03 - Add exception block for notification queue Rem dcawley 07/23/03 - Get notification queue stats every 10 minutes Rem aholser 06/18/03 - fix administrators to match admin ui Rem aholser 06/03/03 - add ck for dbmsjob too old Rem aholser 05/30/03 - add deleted targets Rem aholser 05/28/03 - get agent upload data Rem aholser 05/23/03 - add dup target metric Rem aholser 05/16/03 - get agent metric thresholds Rem aholser 05/14/03 - fix no-data conditions Rem aholser 04/21/03 - targets_not_uploading changes Rem aholser 03/31/03 - 4.1 changes Rem jpyang 04/01/03 - nls support Rem ancheng 02/13/03 - target version fix Rem jpyang 01/31/03 - add 24 hour availability to agent info Rem aholser 01/20/03 - use mgmt_user.get_repository_targets Rem aholser 12/30/02 - bytes used instead of allocated Rem ancheng 12/16/02 - target version support Rem aholser 11/22/02 - fix merge error Rem aholser 11/18/02 - add message to unresponsive data Rem rpatti 12/23/02 - add repos url Rem jpyang 12/03/02 - use mgmt_current_availability for status Rem rpinnama 11/05/02 - Uncomment duplicate target setup Rem aholser 11/12/02 - use connectdescriptor Rem aholser 10/26/02 - use internal name for display name Rem jpyang 10/23/02 - support disabled targets Rem jpyang 10/21/02 - take out instance prop check Rem aholser 10/22/02 - fix for incorrect host Rem aholser 09/23/02 - emd and emrep when no db target Rem aholser 09/20/02 - dup targets and metric errors count Rem jpyang 08/12/02 - add select host info Rem aholser 08/02/02 - werners changes Rem aholser 07/24/02 - add unresponsive targets Rem aholser 07/23/02 - remove duplicate alerts Rem skini 07/12/02 - target_name column change Rem aholser 07/10/02 - performance changes Rem jpyang 07/01/02 - Regular Files name changed Rem jpyang 06/21/02 - include performance data Rem jpyang 06/20/02 - add agent status Rem jpyang 06/12/02 - add single agent homepage Rem aholser 05/29/02 - system health. Rem aholser 05/20/02 - merge performance. Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Restructured. Rem aholser 04/26/02 - fix duplicate urls. Rem aholser 04/08/02 - notification changes. Rem aholser 03/31/02 - Switch to new tables Rem aholser 02/06/02 - fix divide by zero error. Rem aholser 02/02/02 - add loader back in. Rem skini 01/02/02 - Remove reference to MGM_ADMIN_REP Rem aholser 12/06/01 - more perf data. Rem aholser 11/09/01 - add chart details. Rem aholser 11/02/01 - add more rollup, loader data and charts. Rem rpinnama 10/15/01 - Rollback TIMESTAMP WITH TIMEZONE changes Rem aholser 10/11/01 - remove extraneous data Rem aholser 10/15/01 - get db and emd start times Rem aholser 10/04/01 - info page changes Rem aholser 09/28/01 - page layout changes Rem aholser 08/09/01 - review changes Rem aholser 07/17/01 - Created Rem CREATE OR REPLACE PACKAGE BODY mgmt_admin_data AS G_LOADER_JOB CONSTANT VARCHAR2(6) := 'LOADER' ; G_LOADER_MODULE CONSTANT VARCHAR2(10) := 'XMLLoader' ; G_LOADER_POOLSIZE_PARAM CONSTANT VARCHAR2(20) := 'loaderThreadPoolSize'; G_LOADER_NAME_RECORDS CONSTANT VARCHAR2(20) := 'RECORDS'; -- -- Procedure GET_HOME_DATA -- -- PURPOSE: To retrieve the data for the MainData page -- IN parameters: -- rep_connectdescriptor_in: VARCHAR2 The connectdescriptor from the configuration file, used -- OUT parameters: -- agemts_up_out: NUMBER The count of agents that are up -- agemts_down_out: NUMBER The count of agents that are down -- rep_string_out: SMP_EMD_STRING_ARRAY The targetname for the repository if it is -- currently being monitored, else a null string. If instances of a service are -- being monitored independently, these are listed individually -- host_string_out: SMP_EMD_STRING_ARRAY The targetname for the repository hosts if it -- is being monitored. -- emd_string_out: SMP_EMD_STRING_ARRAY The agents monitoring the repository -- tablespace_string_array_out: SMP_EMD_STRING_ARRAY The repository tablespace names -- emrep_string_out: VARCHAR2 The repository -- emrep_emd_string_out: VARCHAR2 The agent monitoring the emrep target -- emrep_emd_disp_string_out: VARCHAR2 The agent monitoring the emrep target -- avail_pct_out: NUMBER the percent of time the emrep target was up last 24 hrs -- dup_target_count_out: NUMBER The count of duplicate targets -- metric_error_ct_out: NUMBER The count of metric errors for our target -- rel_metric_error_ct_out: NUMBER The count of metric errors for related targets -- session_count_out: NUMBER The count of active oms repository sessions -- deleted_target_count_out: NUMBER The count of targets being deleted -- target_count_out: NUMBER The count of targets -- user_count_out: NUMBER The count of users -- job_backlog_sev_out NUMBER the job backlog severity -- related_alerts_out: NUMBER The count of related alerts -- job_backlog_out NUMBER the number of job steps backlog -- used_rep_space_out: NUMBER The space used by the repository in MB -- total_rep_space_out: NUMBER The total space allocated to the repository in MB -- oms_status_out: CURSOR The host_url, timestamp and status (UP/DOWN) for -- contacted oms's -- alert_data_out: CURSOR The alerts for the system components - rep db, host and -- emrep targets -- ERROR CODES: -- PROCEDURE get_home_data (rep_connectdescriptor_in IN VARCHAR2, agents_up_out OUT NUMBER, agents_down_out OUT NUMBER, rep_string_array_out OUT SMP_EMD_STRING_ARRAY, host_string_array_out OUT SMP_EMD_STRING_ARRAY, emd_string_array_out OUT SMP_EMD_STRING_ARRAY, tablespace_string_array_out OUT SMP_EMD_STRING_ARRAY, emrep_string_out OUT VARCHAR2, emrep_emd_string_out OUT VARCHAR2, emrep_emd_disp_string_out OUT VARCHAR2, avail_pct_out OUT NUMBER, dup_target_count_out OUT NUMBER, metric_error_ct_out OUT NUMBER, rel_metric_error_ct_out OUT NUMBER, session_count_out OUT NUMBER, deleted_target_count_out OUT NUMBER, target_count_out OUT NUMBER, user_count_out OUT NUMBER, job_backlog_sev_out OUT NUMBER, related_alerts_out OUT NUMBER, job_backlog_out OUT NUMBER, used_rep_space_out OUT NUMBER, total_rep_space_out OUT NUMBER, oms_status_out OUT CURSORTYPE, alert_data_out OUT CURSORTYPE) IS l_emdrep VARCHAR2(128) := ''; l_host VARCHAR2(128) := ''; l_emd_url VARCHAR2(128) := ''; l_found_target_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_this_guid RAW(16); l_found NUMBER := 0; err NUMBER; BEGIN -- initialize our out arrays rep_string_array_out := SMP_EMD_STRING_ARRAY(); host_string_array_out := SMP_EMD_STRING_ARRAY(); emd_string_array_out := SMP_EMD_STRING_ARRAY(); tablespace_string_array_out := SMP_EMD_STRING_ARRAY(); BEGIN SELECT SUM(DECODE(b.current_status,1,1,0)), SUM(1-DECODE(b.current_status,1,1,0)) INTO agents_up_out, agents_down_out FROM mgmt_targets a, mgmt_current_availability b WHERE a.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND b.target_guid = a.target_guid; EXCEPTION WHEN NO_DATA_FOUND THEN agents_up_out := 0; agents_down_out := 0; END; SELECT DISTINCT ts.name BULK COLLECT INTO tablespace_string_array_out FROM sys.obj$ o, sys.tab$ t, sys.ts$ ts WHERE o.owner# = userenv('SCHEMAID') AND o.obj# = t.obj# AND BITAND(t.property, 1) = 0 AND BITAND(o.flags, 128) = 0 AND t.ts# = ts.ts# AND BITAND(t.property,2151678048) = 0; OPEN oms_status_out FOR SELECT DISTINCT a.host_url, a.value, DECODE(nvl(b.host_url,'down'), 'down', 'down', 'up') status FROM mgmt_oms_parameters a, mgmt_failover_table b WHERE a.name='TIMESTAMP' AND a.host_url IS NOT NULL AND ((SYSDATE-b.last_time_stamp)*(24*60) < 5) AND a.host_url=b.host_url(+); l_found_target_guids := get_related_targets(1); FOR crec IN (SELECT target_name, target_type, target_guid FROM mgmt_targets WHERE target_guid in (SELECT * FROM TABLE(CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY)))) LOOP IF crec.target_type IN (MGMT_GLOBAL.G_DATABASE_TARGET_TYPE, MGMT_GLOBAL.G_RAC_DATABASE_TARGET_TYPE) THEN rep_string_array_out.extend(1); rep_string_array_out(rep_string_array_out.LAST) := crec.target_name; BEGIN FOR ccrec IN (SELECT a.target_name FROM mgmt_targets a, mgmt_targets b WHERE a.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND a.emd_url = b.emd_url AND b.target_guid = crec.target_guid) LOOP l_found := 0; FOR i IN 1..host_string_array_out.COUNT LOOP IF host_string_array_out(i) = ccrec.target_name THEN l_found := 1; END IF; END LOOP; IF l_found = 0 THEN host_string_array_out.extend(1); host_string_array_out(host_string_array_out.LAST) := ccrec.target_name; END IF; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END LOOP; -- there should only be one emrep target BEGIN SELECT target_name, target_guid, emd_url INTO l_emdrep, l_this_guid, l_emd_url FROM mgmt_targets WHERE target_type=MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND target_guid IN (SELECT * FROM TABLE(CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY))); EXCEPTION WHEN NO_DATA_FOUND THEN l_emdrep := ''; l_emd_url := 'unknown'; l_this_guid := mgmt_user.NO_GUID; avail_pct_out := -1; END; IF l_this_guid <> mgmt_user.NO_GUID THEN BEGIN -- get the avail pct of the emrep target for 1 day avail_pct_out := mgmt_target.get_avail_pct_value( l_this_guid, 1 ); EXCEPTION WHEN NO_DATA_FOUND THEN avail_pct_out := -1; END; BEGIN SELECT a.value INTO used_rep_space_out FROM mgmt_current_metrics a, mgmt_targets b, mgmt_metrics c WHERE b.target_guid = l_this_guid AND a.target_guid = b.target_guid AND c.metric_guid = a.metric_guid AND c.metric_name = 'Configuration' AND c.metric_column = 'usedRepSpace' AND c.type_meta_ver = b.type_meta_ver AND (c.category_prop_1 = b.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = b.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = b.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = b.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = b.category_prop_5 OR c.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN used_rep_space_out := -1; END; BEGIN SELECT a.value INTO total_rep_space_out FROM mgmt_current_metrics a, mgmt_targets b, mgmt_metrics c WHERE b.target_guid = l_this_guid AND a.target_guid = b.target_guid AND c.metric_guid = a.metric_guid AND c.metric_name = 'Configuration' AND c.metric_column = 'totalRepSpace' AND c.type_meta_ver = b.type_meta_ver AND (c.category_prop_1 = b.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = b.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = b.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = b.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = b.category_prop_5 OR c.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN total_rep_space_out := -1; END; BEGIN SELECT a.value INTO job_backlog_out FROM mgmt_current_metrics a, mgmt_targets b, mgmt_metrics c WHERE b.target_guid = l_this_guid AND a.target_guid = b.target_guid AND c.metric_guid = a.metric_guid AND c.metric_name = 'Repository_Job_Dispatcher' AND c.metric_column = 'repository_job_backlog' AND c.type_meta_ver = b.type_meta_ver AND (c.category_prop_1 = b.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = b.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = b.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = b.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = b.category_prop_5 OR c.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN job_backlog_out := -1; END; BEGIN SELECT a.severity_code INTO job_backlog_sev_out FROM mgmt_current_severity a, mgmt_targets b, mgmt_metrics c WHERE b.target_guid = l_this_guid AND a.target_guid = b.target_guid AND c.metric_guid = a.metric_guid AND c.metric_name = 'Repository_Job_Dispatcher' AND c.metric_column = 'repository_job_backlog' AND c.type_meta_ver = b.type_meta_ver AND (c.category_prop_1 = b.category_prop_1 OR c.category_prop_1 = ' ') AND (c.category_prop_2 = b.category_prop_2 OR c.category_prop_2 = ' ') AND (c.category_prop_3 = b.category_prop_3 OR c.category_prop_3 = ' ') AND (c.category_prop_4 = b.category_prop_4 OR c.category_prop_4 = ' ') AND (c.category_prop_5 = b.category_prop_5 OR c.category_prop_5 = ' '); EXCEPTION WHEN NO_DATA_FOUND THEN job_backlog_sev_out := MGMT_GLOBAL.G_SEVERITY_UNKNOWN; END; END IF; emrep_string_out := l_emdrep; BEGIN SELECT target_name, display_name into emrep_emd_string_out, emrep_emd_disp_string_out FROM mgmt_targets WHERE target_type=MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND emd_url = l_emd_url; EXCEPTION WHEN NO_DATA_FOUND THEN emrep_emd_string_out := ''; emrep_emd_disp_string_out := ''; END; -- Get the agents SELECT target_name BULK COLLECT INTO emd_string_array_out FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND emd_url IN (SELECT DISTINCT emd_url FROM mgmt_targets WHERE target_guid IN (SELECT * FROM TABLE(CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY)))); dup_target_count_out := 0; BEGIN SELECT count(d.target_name) INTO dup_target_count_out FROM mgmt_duplicate_targets d, mgmt_targets t, mgmt_targets ca WHERE d.target_guid = t.target_guid (+) AND d.duplicate_emd_url = ca.emd_url AND d.resolved_time IS NULL AND ca.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE; EXCEPTION WHEN OTHERS THEN dup_target_count_out := 0; END; target_count_out := 0; BEGIN SELECT count(target_guid) INTO target_count_out FROM mgmt_targets; EXCEPTION WHEN OTHERS THEN err := SQLCODE; target_count_out := 0; mgmt_log.log_error(MGMT_LOG_NAME, 0,'ERROR '||TO_CHAR(err)||' getting target count', '','','','',MGMT_GLOBAL.G_ERROR); END; BEGIN SELECT count(user_name) INTO user_count_out FROM mgmt_created_users WHERE deleting = 0; EXCEPTION WHEN OTHERS THEN err := SQLCODE; target_count_out := 0; mgmt_log.log_error(MGMT_LOG_NAME, 0,'ERROR '||TO_CHAR(err)||' getting user count', '','','','',MGMT_GLOBAL.G_ERROR); END; SELECT count(a.target_name) INTO rel_metric_error_ct_out FROM mgmt_targets a, mgmt_metrics b, mgmt_current_metric_errors c WHERE c.target_guid=a.target_guid AND c.metric_guid=b.metric_guid AND c.metric_error_message IS NOT NULL AND a.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = a.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = a.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = a.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = a.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = a.category_prop_5 OR b.category_prop_5 = ' ') AND a.target_type <> MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND a.target_guid IN (SELECT * FROM TABLE (CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY))); SELECT count(a.target_name) INTO metric_error_ct_out FROM mgmt_targets a, mgmt_metrics b, mgmt_current_metric_errors c WHERE c.target_guid=a.target_guid AND c.metric_guid=b.metric_guid AND c.metric_error_message IS NOT NULL AND a.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = a.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = a.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = a.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = a.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = a.category_prop_5 OR b.category_prop_5 = ' ') AND a.target_guid = l_this_guid; OPEN alert_data_out FOR SELECT a.column_label, b.severity_code, b.collection_timestamp, d.value, d.key_value, c.target_name as display_name, c.type_display_name, d.timestamp, c.target_name, c.target_type, a.metric_name, a.metric_column, a.key_column, a.column_label_nlsid FROM mgmt_metrics a, mgmt_current_severity b, mgmt_targets c, (SELECT metric_guid metric_guid, target_guid target_guid, collection_timestamp timestamp, value value, key_value key_value FROM mgmt_current_metrics WHERE value IS NOT NULL) d WHERE b.severity_code NOT IN (10, 15) AND a.target_type = c.target_type AND c.target_guid = b.target_guid AND a.metric_guid = b.metric_guid AND c.target_guid = d.target_guid AND b.metric_guid = d.metric_guid AND b.key_value = d.key_value AND c.target_guid IN (SELECT * FROM TABLE(CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY))) 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 = ' ') ORDER BY b.severity_code DESC; SELECT COUNT(client_info) INTO session_count_out FROM gv$session WHERE username='SYSMAN' AND client_info IN (SELECT DISTINCT host_url FROM mgmt_oms_parameters WHERE name='TIMESTAMP'); SELECT COUNT(target_name) INTO deleted_target_count_out FROM mgmt_targets_delete; l_found_target_guids := get_related_targets(0); SELECT count(b.severity_code) INTO related_alerts_out FROM mgmt_metrics a, mgmt_current_severity b, mgmt_targets c, (SELECT metric_guid metric_guid, target_guid target_guid, collection_timestamp timestamp, value value, key_value key_value FROM mgmt_current_metrics WHERE value IS NOT NULL) d WHERE b.severity_code NOT IN (10, 15) AND a.target_type = c.target_type AND c.target_guid = b.target_guid AND a.metric_guid = b.metric_guid AND c.target_guid = d.target_guid AND b.metric_guid = d.metric_guid AND b.key_value = d.key_value AND c.target_guid IN (SELECT * FROM TABLE(CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY))) AND c.target_type <> MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE 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 = ' '); END; -- -- Procedure GET_RELATED_ALERTS -- -- PURPOSE: To retrieve the alerts for targets related to the emrep target -- IN parameters: -- none -- OUT parameters: -- alert_data_out: CURSOR The alerts for the related system components - rep db, host -- PROCEDURE get_related_alerts (alert_data_out OUT CURSORTYPE) IS l_found_target_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); BEGIN l_found_target_guids := get_related_targets(0); OPEN alert_data_out FOR SELECT a.column_label, b.severity_code, b.collection_timestamp, d.value, d.key_value, c.target_name as display_name, c.type_display_name, d.timestamp, c.target_name, c.target_type, a.metric_name, a.metric_column, a.key_column, a.column_label_nlsid FROM mgmt_metrics a, mgmt_current_severity b, mgmt_targets c, (SELECT metric_guid metric_guid, target_guid target_guid, collection_timestamp timestamp, value value, key_value key_value FROM mgmt_current_metrics WHERE value IS NOT NULL) d WHERE b.severity_code NOT IN (10, 15) AND a.target_type = c.target_type AND c.target_guid = b.target_guid AND a.metric_guid = b.metric_guid AND c.target_guid = d.target_guid AND b.metric_guid = d.metric_guid AND b.key_value = d.key_value AND c.target_guid IN (SELECT * FROM TABLE(CAST(l_found_target_guids AS MGMT_USER_GUID_ARRAY))) AND c.target_type <> MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE 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 = ' ') ORDER BY b.severity_code DESC; END; -- -- Procedure GET_OMS_DATA -- -- PURPOSE: To retrieve the data for the individual oms page -- IN parameters: -- oms_name_in: VARCHAR2 The name of the oms -- OUT parameters: -- host_name_out: VARCHAR2 The target_name of the host the oms is on -- oc4j_name_out: VARCHAR2 The name of the ooc4j instance the oms is running under -- ias_name_out: VARCHAR2 The name of the IAS instance the oms is running under -- filesystem_out: VARCHAR2 The name of the filesystem the loader directory is in -- upload_port_out: VARCHAR2 The name of the upload port -- secure_upload_port_out: VARCHAR2 The name of the upload port -- oc4j_composite_key_out: RAW The composite key for the oc4j page hit metric -- cpu_pct_out: NUMBER the percent of time the process containing the oms is running -- cpu_pctwarn_out: NUMBER the warning threshold percent of time the process containing the oms is running -- cpu_pctcrit_out: NUMBER the critical threshold percent of time the process containing the oms is running -- mem_mb_out: NUMBER the number of mb occupied by the process containing the oms -- mem_mbwarn_out: NUMBER the warning threshold number of mb occupied by the process containing the oms -- mem_mbcrit_out: NUMBER the critical threshold number of mb occupied by the process containing the oms -- jvm_mem_out: NUMBER the number of MB occupied by the jvm the oms is using -- jvm_memwarn_out: NUMBER the warning threshold number of MB occupied by the jvm the oms is using -- jvm_memcrit_out: NUMBER the critical threshold number of MB occupied by the jvm the oms is using -- fsspace_out: NUMBER the percent of the filesystem that is used -- fsspacewarn_out: NUMBER the warning threshold percent of the filesystem that is used -- fsspacecrit_out: NUMBER the critical threshold percent of the filesystem that is used -- since_out: DATE the timestamp in date format for the oms - down since or up since -- ERROR CODES: -- PROCEDURE get_oms_data (oms_name_in IN VARCHAR2, host_name_out OUT VARCHAR2, oc4j_name_out OUT VARCHAR2, ias_name_out OUT VARCHAR2, filesystem_out OUT VARCHAR2, upload_port_out OUT VARCHAR2, secure_upload_port_out OUT VARCHAR2, oc4j_composite_key_out OUT RAW, cpu_pct_out OUT NUMBER, cpu_pctwarn_out OUT NUMBER, cpu_pctcrit_out OUT NUMBER, mem_mb_out OUT NUMBER, mem_mbwarn_out OUT NUMBER, mem_mbcrit_out OUT NUMBER, jvm_mem_out OUT NUMBER, jvm_memwarn_out OUT NUMBER, jvm_memcrit_out OUT NUMBER, fsspace_out OUT NUMBER, fsspacewarn_out OUT NUMBER, fsspacecrit_out OUT NUMBER, since_out OUT DATE) IS l_oc4j VARCHAR2(128) := ''; l_oms_name VARCHAR2(128) := ''; l_oracle_home VARCHAR2(128) := ''; l_host_guid RAW(16); l_oc4j_guid RAW(16); l_len NUMBER := 0; BEGIN l_len := INSTR(oms_name_in,'.'); IF l_len > 0 THEN l_oms_name := SUBSTR(oms_name_in, 1, l_len-1); ELSE l_oms_name := oms_name_in; END IF; BEGIN SELECT TO_DATE(value, 'DD-Mon-YYYY HH:MI:SS') INTO since_out FROM mgmt_oms_parameters WHERE name='TIMESTAMP' AND host_url = oms_name_in AND ROWNUM=1; EXCEPTION WHEN OTHERS THEN BEGIN SELECT TO_DATE(value, 'DD-Mon-YYYY HH24:MI:SS') INTO since_out FROM mgmt_oms_parameters WHERE name='TIMESTAMP' AND host_url = oms_name_in AND ROWNUM=1; EXCEPTION WHEN OTHERS THEN NULL; END; END; BEGIN SELECT NVL(value,'') INTO l_oracle_home FROM mgmt_oms_parameters WHERE name = 'ORACLE_HOME' AND host_url = oms_name_in AND ROWNUM=1; EXCEPTION WHEN OTHERS THEN l_oracle_home := ''; END; BEGIN SELECT DECODE(value, -1, 'None', TO_CHAR(value)) INTO upload_port_out FROM mgmt_oms_parameters WHERE name = 'upload_port' AND host_url = oms_name_in AND ROWNUM=1; EXCEPTION WHEN OTHERS THEN BEGIN SELECT DECODE(value, -1, 'None', TO_CHAR(value)) INTO upload_port_out FROM mgmt_oms_parameters WHERE name = 'OMS_URL' AND host_url = oms_name_in AND ROWNUM=1; upload_port_out := upload_port_out || 'em/upload'; EXCEPTION WHEN OTHERS THEN upload_port_out := 'None'; END; END; BEGIN SELECT DECODE(value, -1, 'None', TO_CHAR(value)) INTO secure_upload_port_out FROM mgmt_oms_parameters WHERE name = 'secure_upload_port' AND host_url = oms_name_in AND ROWNUM=1; EXCEPTION WHEN OTHERS THEN secure_upload_port_out := 'None'; END; BEGIN SELECT NVL(a.target_name,'None'), a.target_guid INTO host_name_out, l_host_guid FROM mgmt_targets a WHERE a.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND a.host_name LIKE '%'||l_oms_name||'%' AND ROWNUM = 1; EXCEPTION WHEN OTHERS THEN host_name_out := ''; END; BEGIN SELECT NVL(b.target_name,'None'), NVL(b.target_guid,NO_TARGET_GUID), NVL(ct.target_name, 'None') INTO l_oc4j, l_oc4j_guid, ias_name_out FROM mgmt_targets b, mgmt_target_properties c, mgmt_target_properties d, mgmt_target_assocs e, mgmt_targets ct WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name like '%OC4J_EM' AND c.target_guid = b.target_guid AND d.target_guid = b.target_guid AND c.property_name = 'HTTPMachine' AND d.property_name = 'OracleHome' AND c.property_value LIKE '%'||l_oms_name||'%' AND d.property_value LIKE '%'||l_oracle_home||'%' AND ct.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE AND e.assoc_guid = MGMT_ASSOC.g_contains_guid AND e.source_target_guid = ct.target_guid ANd e.assoc_target_guid = b.target_guid AND ROWNUM = 1; EXCEPTION WHEN OTHERS THEN l_oc4j := ''; ias_name_out := ''; END; BEGIN SELECT NVL(bb.value,-1), NVL(aa.filesystem,'Unknown') INTO fsspace_out, filesystem_out FROM (SELECT SUBSTR(d.value, 1, NVL(MAX(LENGTH(a.key_value)),1)) filesystem FROM mgmt_current_metrics a, mgmt_metrics b, mgmt_targets c, (SELECT value FROM mgmt_oms_parameters a WHERE name='loaderCanonicalDir' AND host_url = oms_name_in) d WHERE a.metric_guid = b.metric_guid AND INSTR(d.value,a.key_value) > 0 AND b.type_meta_ver = c.type_meta_ver AND b.target_type = c.target_type AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' ') AND c.target_guid = l_host_guid AND b.metric_name = 'Filesystems' GROUP BY(d.value)) aa, mgmt_current_metrics bb, mgmt_metrics cc, mgmt_targets dd WHERE bb.metric_guid = cc.metric_guid AND bb.target_guid = l_host_guid AND dd.target_guid = l_host_guid AND cc.metric_name = 'Filesystems' AND cc.metric_column = 'pctAvailable' AND bb.key_value = aa.filesystem AND cc.type_meta_ver = dd.type_meta_ver AND cc.target_type = dd.target_type AND (cc.category_prop_1 = dd.category_prop_1 OR cc.category_prop_1 = ' ') AND (cc.category_prop_2 = dd.category_prop_2 OR cc.category_prop_2 = ' ') AND (cc.category_prop_3 = dd.category_prop_3 OR cc.category_prop_3 = ' ') AND (cc.category_prop_4 = dd.category_prop_4 OR cc.category_prop_4 = ' ') AND (cc.category_prop_5 = dd.category_prop_5 OR cc.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN filesystem_out := 'Unknown'; fsspace_out := -1; END; IF filesystem_out = 'Unknown' THEN fsspacewarn_out := -1; fsspacecrit_out := -1; ELSE BEGIN SELECT NVL(ee.warning_threshold,-1), NVL(ee.critical_threshold,-1) INTO fsspacewarn_out, fsspacecrit_out FROM mgmt_current_metrics bb, mgmt_metrics cc, mgmt_targets dd, mgmt_metric_thresholds ee WHERE bb.metric_guid = cc.metric_guid AND bb.target_guid = l_host_guid AND dd.target_guid = l_host_guid AND cc.metric_name = 'Filesystems' AND cc.metric_column = 'pctAvailable' AND ee.metric_guid = bb.metric_guid AND ee.target_guid = bb.target_guid AND bb.key_value = filesystem_out AND cc.type_meta_ver = dd.type_meta_ver AND (cc.category_prop_1 = dd.category_prop_1 OR cc.category_prop_1 = ' ') AND (cc.category_prop_2 = dd.category_prop_2 OR cc.category_prop_2 = ' ') AND (cc.category_prop_3 = dd.category_prop_3 OR cc.category_prop_3 = ' ') AND (cc.category_prop_4 = dd.category_prop_4 OR cc.category_prop_4 = ' ') AND (cc.category_prop_5 = dd.category_prop_5 OR cc.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN fsspacewarn_out := -1; fsspacecrit_out := -1; END; END IF; oc4j_name_out := l_oc4j; IF l_oc4j = '' THEN cpu_pct_out := -1; cpu_pctwarn_out := -1; cpu_pctcrit_out := -1; mem_mb_out := -1; mem_mbwarn_out := -1; mem_mbcrit_out := -1; jvm_mem_out := -1; jvm_memwarn_out := -1; jvm_memcrit_out := -1; oc4j_composite_key_out := NO_TARGET_GUID; ELSE BEGIN SELECT NVL(c.value, -1) INTO cpu_pct_out FROM mgmt_metrics a, mgmt_targets b, mgmt_current_metrics c WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name = l_oc4j AND b.target_guid = c.target_guid AND a.metric_guid = c.metric_guid AND a.type_meta_ver = b.type_meta_ver AND (a.category_prop_1 = b.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = b.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = b.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = b.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = b.category_prop_5 OR a.category_prop_5 = ' ') AND a.metric_name = 'ResourceUsage' AND a.metric_column = 'cpu.component'; EXCEPTION WHEN OTHERS THEN cpu_pct_out := -1; END; BEGIN SELECT NVL(d.warning_threshold,-1), NVL(d.critical_threshold, -1) INTO cpu_pctwarn_out, cpu_pctcrit_out FROM mgmt_metrics a, mgmt_targets b, mgmt_metric_thresholds d WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name = l_oc4j AND d.metric_guid = a.metric_guid AND d.target_guid = b.target_guid AND a.type_meta_ver = b.type_meta_ver AND (a.category_prop_1 = b.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = b.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = b.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = b.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = b.category_prop_5 OR a.category_prop_5 = ' ') AND a.metric_name = 'ResourceUsage' AND a.metric_column = 'cpu.component'; EXCEPTION WHEN OTHERS THEN cpu_pctwarn_out := -1; cpu_pctcrit_out := -1; END; BEGIN SELECT NVL(c.value, -1) INTO mem_mb_out FROM mgmt_metrics a, mgmt_targets b, mgmt_current_metrics c WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name = l_oc4j AND b.target_guid = c.target_guid AND a.metric_guid = c.metric_guid AND a.type_meta_ver = b.type_meta_ver AND (a.category_prop_1 = b.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = b.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = b.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = b.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = b.category_prop_5 OR a.category_prop_5 = ' ') AND a.metric_name = 'ResourceUsage' AND a.metric_column = 'memory.component'; EXCEPTION WHEN OTHERS THEN mem_mb_out := -1; END; BEGIN SELECT NVL(d.warning_threshold, -1), NVL(d.critical_threshold, -1) INTO mem_mbwarn_out, mem_mbcrit_out FROM mgmt_metrics a, mgmt_targets b, mgmt_metric_thresholds d WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name = l_oc4j AND d.metric_guid = a.metric_guid AND d.target_guid = b.target_guid AND a.type_meta_ver = b.type_meta_ver AND (a.category_prop_1 = b.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = b.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = b.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = b.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = b.category_prop_5 OR a.category_prop_5 = ' ') AND a.metric_name = 'ResourceUsage' AND a.metric_column = 'memory.component'; EXCEPTION WHEN OTHERS THEN mem_mbwarn_out := -1; mem_mbcrit_out := -1; END; BEGIN SELECT NVL(c.value, -1) INTO jvm_mem_out FROM mgmt_metrics a, mgmt_targets b, mgmt_current_metrics c WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name = l_oc4j AND b.target_guid = c.target_guid AND a.metric_guid = c.metric_guid AND a.type_meta_ver = b.type_meta_ver AND (a.category_prop_1 = b.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = b.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = b.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = b.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = b.category_prop_5 OR a.category_prop_5 = ' ') AND a.metric_name = 'oc4j_instance_rollup' AND a.metric_column = 'heap.value'; EXCEPTION WHEN OTHERS THEN jvm_mem_out := -1; END; BEGIN SELECT NVL(d.warning_threshold, -1), NVL(d.critical_threshold, -1) INTO jvm_memwarn_out, jvm_memcrit_out FROM mgmt_metrics a, mgmt_targets b, mgmt_metric_thresholds d WHERE b.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND b.target_name = l_oc4j AND d.metric_guid = a.metric_guid AND d.target_guid = b.target_guid AND a.type_meta_ver = b.type_meta_ver AND (a.category_prop_1 = b.category_prop_1 OR a.category_prop_1 = ' ') AND (a.category_prop_2 = b.category_prop_2 OR a.category_prop_2 = ' ') AND (a.category_prop_3 = b.category_prop_3 OR a.category_prop_3 = ' ') AND (a.category_prop_4 = b.category_prop_4 OR a.category_prop_4 = ' ') AND (a.category_prop_5 = b.category_prop_5 OR a.category_prop_5 = ' ') AND a.metric_name = 'oc4j_instance_rollup' AND a.metric_column = 'heap.value'; EXCEPTION WHEN OTHERS THEN jvm_memwarn_out := -1; jvm_memcrit_out := -1; END; BEGIN SELECT composite_key INTO oc4j_composite_key_out FROM mgmt_metrics_composite_keys WHERE target_guid = l_oc4j_guid AND key_part1_value = 'console' AND key_part2_value = 'em' AND key_part3_value = 'em'; EXCEPTION WHEN OTHERS THEN oc4j_composite_key_out := NO_TARGET_GUID; END; END IF; END; PROCEDURE do_type_roles_exist (roles_exist_out OUT VARCHAR2) IS counter INTEGER := 0; BEGIN SELECT count(*) INTO counter FROM dba_roles WHERE role LIKE('MGMT_%') AND role NOT IN(ROLE_ALL_TARGETS); IF counter < 1 THEN roles_exist_out := 'N'; ELSE roles_exist_out := 'Y'; END IF; END; -- -- Function getOmsUrls -- -- PURPOSE: To get a comma-separated list of connected omsurls -- IN parameters: -- No IN parameters -- RETURNS: -- The list of connected host urls or an empty string if none are -- FUNCTION GETOMSURLS return VARCHAR2 IS l_urls VARCHAR2(4000); l_count NUMBER; BEGIN l_urls := ''; l_count := 0; FOR crec in (SELECT DISTINCT host_url FROM mgmt_failover_table WHERE ((SYSDATE-last_time_stamp)*(24*60) < 5) ORDER BY host_url) LOOP IF l_count = 0 THEN l_urls := crec.host_url; ELSE BEGIN l_urls := l_urls || ',' || crec.host_url; EXCEPTION WHEN OTHERS THEN EXIT; END; END IF; l_count := l_count + 1; END LOOP; RETURN l_urls; END GETOMSURLS; FUNCTION GET_AGENT_INFO(p_target_name IN VARCHAR2) RETURN AGENTINFO_CURSOR IS l_agent_info AGENTINFO_CURSOR; l_tguid mgmt_targets.target_guid%TYPE; l_emd_url mgmt_targets.emd_url%TYPE; l_host_name mgmt_targets.host_name%TYPE; l_ms_name mgmt_targets.target_name%TYPE; l_oms_url mgmt_emd_ping.heartbeat_recorder_url%TYPE; l_current_status mgmt_current_availability.current_status%TYPE; l_version mgmt_target_properties.property_value%TYPE; l_oracle_home mgmt_target_properties.property_value%TYPE; l_last_load_time mgmt_targets.last_load_time%TYPE; l_cpu_usage_guid mgmt_metrics.metric_guid%TYPE; l_mem_usage_guid mgmt_metrics.metric_guid%TYPE; l_files_open_guid mgmt_metrics.metric_guid%TYPE; l_fh_open_guid mgmt_metrics.metric_guid%TYPE; l_num_threads_guid mgmt_metrics.metric_guid%TYPE; l_user_guid mgmt_metrics.metric_guid%TYPE; l_upload_guid mgmt_metrics.metric_guid%TYPE; l_restart_guid mgmt_metrics.metric_guid%TYPE; l_avail_pct_out NUMBER; l_cpu_warn_thresh NUMBER; l_cpu_crit_thresh NUMBER; l_cpu_sev NUMBER; l_mem_warn_thresh NUMBER; l_mem_crit_thresh NUMBER; l_mem_sev NUMBER; l_files_warn_thresh NUMBER; l_files_crit_thresh NUMBER; l_files_sev NUMBER; l_fh_warn_thresh NUMBER; l_fh_crit_thresh NUMBER; l_fh_sev NUMBER; l_threads_warn_thresh NUMBER; l_threads_crit_thresh NUMBER; l_threads_sev NUMBER; l_upload_kb NUMBER; l_restart_warn_thresh NUMBER; l_restart_crit_thresh NUMBER; l_restart_count NUMBER; l_restart_sev NUMBER; l_host_count NUMBER; l_total_cpu NUMBER; l_total_mem NUMBER; l_file_open NUMBER; l_fh_open NUMBER; l_num_thread NUMBER; l_user_name VARCHAR2(512); BEGIN SELECT target_guid, host_name, emd_url INTO l_tguid, l_host_name, l_emd_url FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND target_name = p_target_name; BEGIN SELECT COUNT(target_name) INTO l_host_count FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND host_name = l_host_name; EXCEPTION WHEN OTHERS THEN l_host_count := 0; END; BEGIN SELECT target_name INTO l_ms_name FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND rownum = 1; EXCEPTION WHEN OTHERS THEN l_ms_name := ''; END; -- get avail percent l_avail_pct_out := ROUND(mgmt_target.get_avail_pct_value(l_tguid, 1), 2); -- get max of the last load time for all targets on this agent SELECT MAX(last_load_time) INTO l_last_load_time FROM mgmt_targets WHERE emd_url = l_emd_url; -- agent status BEGIN SELECT NVL(current_status,MGMT_GLOBAL.G_STATUS_UNKNOWN) INTO l_current_status FROM mgmt_current_availability WHERE target_guid = l_tguid; EXCEPTION WHEN NO_DATA_FOUND THEN l_current_status := MGMT_GLOBAL.G_STATUS_UNKNOWN; END; -- OMS agent is uploading to SELECT heartbeat_recorder_url INTO l_oms_url FROM mgmt_emd_ping WHERE target_guid = l_tguid; -- agent version BEGIN SELECT property_value INTO l_version FROM mgmt_target_properties WHERE target_guid = l_tguid AND property_name='Version'; EXCEPTION WHEN NO_DATA_FOUND THEN l_version := NULL; END; -- agent oracle home BEGIN SELECT property_value INTO l_oracle_home FROM mgmt_target_properties WHERE target_guid = l_tguid AND property_name='OracleHome'; EXCEPTION WHEN NO_DATA_FOUND THEN l_oracle_home := NULL; END; BEGIN l_user_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'EMDIdentity','username'); EXCEPTION WHEN OTHERS THEN l_user_guid := mgmt_user.NO_GUID; END; BEGIN l_cpu_usage_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'ProcessInfo', 'HostInfocpu'); BEGIN SELECT NVL(md.warning_threshold, -1), NVL(md.critical_threshold, -1) INTO l_cpu_warn_thresh, l_cpu_crit_thresh FROM mgmt_metric_thresholds md WHERE md.metric_guid = l_cpu_usage_guid AND md.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_cpu_warn_thresh := -1; l_cpu_crit_thresh := -1; END; BEGIN SELECT NVL(ms.severity_code, -1) INTO l_cpu_sev FROM mgmt_current_severity ms WHERE ms.metric_guid = l_cpu_usage_guid AND ms.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_cpu_sev := -1; END; EXCEPTION WHEN OTHERS THEN l_cpu_warn_thresh := -1; l_cpu_crit_thresh := -1; l_cpu_sev := -1; END; mgmt_log.log_error(MGMT_LOG_NAME, 0, 'cpu_warn_thresh '||to_char(l_cpu_warn_thresh), '','','','',MGMT_GLOBAL.G_DEBUG); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'cpu_crit_thresh '||to_char(l_cpu_crit_thresh), '','','','',MGMT_GLOBAL.G_DEBUG); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'cpu_sev '||to_char(l_cpu_sev), '','','','',MGMT_GLOBAL.G_DEBUG); BEGIN l_mem_usage_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'ProcessInfo','HostInfovsz'); BEGIN SELECT NVL(md.warning_threshold, -1), NVL(md.critical_threshold, -1) INTO l_mem_warn_thresh, l_mem_crit_thresh FROM mgmt_metric_thresholds md WHERE md.metric_guid = l_mem_usage_guid AND md.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_mem_warn_thresh := -1; l_mem_crit_thresh := -1; END; BEGIN SELECT NVL(ms.severity_code, -1) INTO l_mem_sev FROM mgmt_current_severity ms WHERE ms.metric_guid = l_mem_usage_guid AND ms.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_mem_sev := -1; END; EXCEPTION WHEN OTHERS THEN l_mem_warn_thresh := -1; l_mem_crit_thresh := -1; l_mem_sev := -1; END; BEGIN l_files_open_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'ProcessInfo','HostInforegf'); BEGIN SELECT NVL(md.warning_threshold, -1), NVL(md.critical_threshold, -1) INTO l_files_warn_thresh, l_files_crit_thresh FROM mgmt_metric_thresholds md WHERE md.metric_guid = l_files_open_guid AND md.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_files_warn_thresh := -1; l_files_crit_thresh := -1; END; BEGIN SELECT NVL(ms.severity_code, -1) INTO l_files_sev FROM mgmt_current_severity ms WHERE ms.metric_guid = l_files_open_guid AND ms.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_files_sev := -1; END; EXCEPTION WHEN OTHERS THEN l_files_warn_thresh := -1; l_files_crit_thresh := -1; l_files_sev := -1; END; BEGIN l_fh_open_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'ProcessInfo','HostInforegh'); BEGIN SELECT NVL(md.warning_threshold, -1), NVL(md.critical_threshold, -1) INTO l_fh_warn_thresh, l_fh_crit_thresh FROM mgmt_metric_thresholds md WHERE md.metric_guid = l_fh_open_guid AND md.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_fh_warn_thresh := -1; l_fh_crit_thresh := -1; END; BEGIN SELECT NVL(ms.severity_code, -1) INTO l_fh_sev FROM mgmt_current_severity ms WHERE ms.metric_guid = l_fh_open_guid AND ms.target_guid = l_tguid; EXCEPTION WHEN OTHERS THEN l_fh_sev := -1; END; EXCEPTION WHEN OTHERS THEN l_fh_warn_thresh := -1; l_fh_crit_thresh := -1; l_fh_sev := -1; END; BEGIN l_num_threads_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'ProcessInfo','HostInfonumt'); BEGIN SELECT NVL(md.warning_threshold, -1), NVL(md.critical_threshold, -1) INTO l_threads_warn_thresh, l_threads_crit_thresh FROM mgmt_metric_thresholds md WHERE md.metric_guid = l_num_threads_guid AND md.target_guid(+) = l_tguid; EXCEPTION WHEN OTHERS THEN l_threads_warn_thresh := -1; l_threads_crit_thresh := -1; END; BEGIN SELECT NVL(ms.severity_code, -1) INTO l_threads_sev FROM mgmt_current_severity ms WHERE ms.metric_guid(+) = l_num_threads_guid AND ms.target_guid(+) = l_tguid; EXCEPTION WHEN OTHERS THEN l_threads_sev := -1; END; EXCEPTION WHEN OTHERS THEN l_threads_warn_thresh := -1; l_threads_crit_thresh := -1; l_threads_sev := -1; END; BEGIN l_upload_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, p_target_name, 'EMDUploadStats','uploadrate'); SELECT AVG(value)*(60*60) INTO l_upload_kb FROM mgmt_metrics_raw WHERE target_guid=l_tguid AND metric_guid=l_upload_guid AND collection_timestamp > (SYSDATE-1/24); EXCEPTION WHEN OTHERS THEN l_upload_kb := -1; END; BEGIN l_restart_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, p_target_name,'Agent_Status','restart_count'); BEGIN SELECT NVL(md.warning_threshold, -1), NVL(md.critical_threshold, -1) INTO l_restart_warn_thresh, l_restart_crit_thresh FROM mgmt_metric_thresholds md WHERE md.metric_guid = l_restart_guid; EXCEPTION WHEN OTHERS THEN l_restart_warn_thresh := -1; l_restart_crit_thresh := -1; END; BEGIN SELECT NVL(ms.severity_code, -1) INTO l_restart_sev FROM mgmt_current_severity ms WHERE ms.metric_guid(+) = l_restart_guid; EXCEPTION WHEN OTHERS THEN l_restart_sev := -1; END; BEGIN SELECT NVL(a.value,0) INTO l_restart_count FROM mgmt_current_metrics a, mgmt_targets c, mgmt_metrics b WHERE a.key_value = p_target_name AND a.metric_guid=l_restart_guid AND a.collection_timestamp > (SYSDATE-1) AND a.metric_guid=b.metric_guid AND a.target_guid=c.target_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_restart_count := 0; END; EXCEPTION WHEN OTHERS THEN l_restart_warn_thresh := -1; l_restart_crit_thresh := -1; l_restart_sev := -1; l_restart_count := 0; END; BEGIN SELECT /*+ INDEX(m1h) */ ROUND(NVL(mlh.value, -1), 2) INTO l_total_cpu FROM mgmt_current_metrics mlh, mgmt_metrics b, mgmt_targets c WHERE mlh.target_guid = l_tguid AND mlh.metric_guid = l_cpu_usage_guid AND mlh.target_guid = c.target_guid AND b.metric_guid = mlh.metric_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_total_cpu := -1; END; BEGIN SELECT /*+ INDEX(m1h) */ NVL(mlh.value, -1) into l_total_mem FROM mgmt_current_metrics mlh, mgmt_metrics b, mgmt_targets c WHERE mlh.target_guid = l_tguid AND mlh.metric_guid = l_mem_usage_guid AND mlh.target_guid = c.target_guid AND b.metric_guid = mlh.metric_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_total_mem := -1; END; BEGIN SELECT /*+ INDEX(m1h) */ ROUND(NVL(mlh.value, -1), 0) INTO l_file_open FROM mgmt_current_metrics mlh, mgmt_metrics b, mgmt_targets c WHERE mlh.target_guid = l_tguid AND mlh.metric_guid = l_files_open_guid AND mlh.target_guid = c.target_guid AND b.metric_guid = mlh.metric_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_file_open := -1; END; BEGIN SELECT /*+ INDEX(m1h) */ ROUND(NVL(mlh.value, -1), 0) INTO l_fh_open FROM mgmt_current_metrics mlh, mgmt_metrics b, mgmt_targets c WHERE mlh.target_guid = l_tguid AND mlh.metric_guid = l_fh_open_guid AND mlh.target_guid = c.target_guid AND b.metric_guid = mlh.metric_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_fh_open := -1; END; BEGIN SELECT /*+ INDEX(m1h) */ ROUND(NVL(mlh.value, -1), 0) INTO l_num_thread FROM mgmt_current_metrics mlh, mgmt_metrics b, mgmt_targets c WHERE mlh.target_guid = l_tguid AND mlh.metric_guid = l_num_threads_guid AND mlh.target_guid = c.target_guid AND b.metric_guid = mlh.metric_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_num_thread := -1; END; BEGIN SELECT /*+ INDEX(m1h) */ NVL(mlh.string_value, 'Unknown') INTO l_user_name FROM mgmt_current_metrics mlh, mgmt_metrics b, mgmt_targets c WHERE mlh.target_guid = l_tguid AND mlh.metric_guid = l_user_guid AND mlh.target_guid = c.target_guid AND b.metric_guid = mlh.metric_guid AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); EXCEPTION WHEN OTHERS THEN l_user_name := ''; END; -- Open a cursor to return General information for the single agent homepage OPEN l_agent_info FOR SELECT /*+ INDEX(t) INDEX(t2) INDEX(tp) */ l_host_name AS host_name, l_host_count AS host_count, l_emd_url AS emd_url, l_oms_url AS oms_url, l_ms_name AS ms_name, l_current_status emd_status, l_last_load_time last_load_time, l_version emd_version, l_oracle_home oracle_home, l_avail_pct_out availPct, l_total_cpu total_cpu, l_cpu_warn_thresh cpu_warning_threshold, l_cpu_crit_thresh cpu_critical_threshold, l_cpu_sev cpu_severity, l_total_mem total_mem, l_mem_warn_thresh mem_warning_threshold, l_mem_crit_thresh mem_critical_threshold, l_mem_sev mem_severity, l_file_open file_open, l_files_warn_thresh files_warning_threshold, l_files_crit_thresh files_critical_threshold, l_files_sev files_severity, l_fh_open fh_open, l_fh_warn_thresh fh_warning_threshold, l_fh_crit_thresh fh_critical_threshold, l_fh_sev fh_severity, l_num_thread num_thread, l_threads_warn_thresh threads_warning_threshold, l_threads_crit_thresh threads_critical_threshold, l_threads_sev threads_severity, l_user_name user_name, l_upload_kb upload_kb, l_restart_warn_thresh restart_warn_threshold, l_restart_crit_thresh restart_crit_threshold, l_restart_sev restart_sev, l_restart_count restart_count FROM dual; RETURN l_agent_info; END GET_AGENT_INFO; FUNCTION GET_NODE_LIST_WITH_PRIVILEGE RETURN NODELIST_CURSOR IS l_hosts_with_priv NODELIST_CURSOR; l_cpu_usage_guid mgmt_metrics.metric_guid%TYPE; l_mem_usage_guid mgmt_metrics.metric_guid%TYPE; l_restart_guid mgmt_metrics.metric_guid%TYPE; l_repository_guid mgmt_targets.target_guid%TYPE; l_emrep VARCHAR2(256); BEGIN BEGIN SELECT target_name, target_guid INTO l_emrep, l_repository_guid FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND ROWNUM = 1; EXCEPTION WHEN OTHERS THEN l_emrep := ''; l_repository_guid := mgmt_user.NO_GUID; END; BEGIN -- get the metric guid which are constant anyway l_cpu_usage_guid := mgmt_target.get_metric_guid( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, 'ProcessInfo','HostInfocpu'); EXCEPTION WHEN OTHERS THEN l_cpu_usage_guid := mgmt_user.NO_GUID; END; BEGIN l_mem_usage_guid := mgmt_target.get_metric_guid( MGMT_GLOBAL.G_AGENT_TARGET_TYPE, 'ProcessInfo','HostInfovsz'); EXCEPTION WHEN OTHERS THEN l_mem_usage_guid := mgmt_user.NO_GUID; END; BEGIN l_restart_guid := mgmt_metric.get_metric_guid_for_target( MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, l_emrep, 'Agent_Status','restart_count'); EXCEPTION WHEN OTHERS THEN l_restart_guid := mgmt_user.NO_GUID; END; -- Open a cursor for a unique set of hosts that the targets are on. -- Filter out targets that do not have valid emd urls OPEN l_hosts_with_priv FOR SELECT t.host_name, t.target_name emd_name, l_emrep ms_name, LOWER(t.emd_url) emd_url, LOWER(p.heartbeat_recorder_url) oms_url, a.current_status status, t2.last_load_time last_load_time, tp.property_value emd_version, t2.unbroken_targets, t2.broken_targets, NVL(e.cnt,0) number_of_errors, NVL(mrs_count.value,-1) restart_count, NVL(mrs_severity.violation_level, -1) restart_severity, NVL(cm.t_cpu,-1) total_cpu, NVL(cv.cpu_severity,-1) cpu_severity, NVL(cm.t_mem,-1) total_mem, NVL(cv.mem_severity,-1) mem_severity FROM mgmt_targets t, mgmt_emd_ping p, mgmt_current_availability a, mgmt_target_properties tp, (SELECT emd_url, MAX(last_load_time) last_load_time, SUM(DECODE(broken_reason,0,1,256,1,0)) unbroken_targets, SUM(DECODE(broken_reason,0,0,256,0,1)) broken_targets FROM mgmt_targets WHERE emd_url IS NOT NULL GROUP BY emd_url ) t2, (SELECT agent_guid, COUNT(*) cnt FROM mgmt_current_metric_errors GROUP BY agent_guid ) e, ( SELECT tgts.target_guid, mc.value FROM mgmt_current_metrics mc, mgmt_targets tgts WHERE mc.target_guid = l_repository_guid AND mc.metric_guid = l_restart_guid AND mc.key_value = tgts.target_name AND tgts.target_type= MGMT_GLOBAL.G_AGENT_TARGET_TYPE ) mrs_count, ( SELECT tgts.target_guid, ms.violation_level FROM mgmt_current_violation ms, mgmt_targets tgts WHERE ms.target_guid = l_repository_guid AND ms.policy_guid = l_restart_guid AND ms.key_value = tgts.target_name AND tgts.target_type= MGMT_GLOBAL.G_AGENT_TARGET_TYPE ) mrs_severity, (SELECT target_guid, SUM(DECODE(metric_guid,l_cpu_usage_guid,value,0)) t_cpu, SUM(DECODE(metric_guid,l_mem_usage_guid,value,0)) t_mem FROM MGMT_CURRENT_METRICS WHERE metric_guid IN (l_cpu_usage_guid,l_mem_usage_guid) GROUP BY target_guid ) cm, (SELECT target_guid, SUM(DECODE(policy_guid,l_cpu_usage_guid,violation_level,0)) cpu_severity, SUM(DECODE(policy_guid,l_mem_usage_guid,violation_level,0)) mem_severity FROM MGMT_CURRENT_VIOLATION WHERE policy_guid IN (l_cpu_usage_guid,l_mem_usage_guid) AND violation_type IN (0, 2) GROUP BY target_guid ) cv WHERE a.target_guid = p.target_guid AND t.target_guid = p.target_guid AND tp.target_guid = p.target_guid AND tp.property_name = 'Version' AND t2.emd_url = t.emd_url AND cm.target_guid(+) = t.target_guid AND cv.target_guid(+) = t.target_guid AND e.agent_guid(+) = t.target_guid AND mrs_count.target_guid(+) = t.target_guid AND mrs_severity.target_guid(+) = t.target_guid; RETURN l_hosts_with_priv; END GET_NODE_LIST_WITH_PRIVILEGE; FUNCTION get_unresponsive_targets return VARCHAR2 IS l_target_string varchar2(4000) := 'No targets are unresponsive'; l_count NUMBER := 0; BEGIN FOR crec IN (SELECT a.target_name, a.target_type FROM mgmt_targets a, mgmt_current_availability b WHERE a.target_guid = b.target_guid AND b.current_status = MGMT_GLOBAL.G_STATUS_UP AND a.target_type NOT IN (MGMT_GLOBAL.G_AGENT_TARGET_TYPE, MGMT_GLOBAL.G_BEACON_TARGET_TYPE) AND a.last_load_time < CAST(systimestamp at time zone a.timezone_region as DATE)-1/12) LOOP IF l_count = 0 THEN l_target_string := 'No metric data from '||crec.target_name; ELSE -- leave three spaces for the final ... IF LENGTH(l_target_string) + LENGTH(crec.target_name) > 3996 THEN l_target_string := l_target_string || '...'; RETURN l_target_string; END IF; BEGIN l_target_string := l_target_string || ',' || crec.target_name; EXCEPTION WHEN OTHERS THEN RETURN l_target_string; END; END IF; l_count := l_count + 1; END LOOP; RETURN l_target_string; END get_unresponsive_targets; -- -- PURPOSE -- To get the list of targets associated with the repository target -- FUNCTION GET_RELATED_TARGETS(oms_hosts_in IN NUMBER DEFAULT 0) RETURN MGMT_USER_GUID_ARRAY IS l_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_local_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_ret_guids MGMT_USER_GUID_ARRAY := MGMT_USER_GUID_ARRAY(); l_this_guid RAW(16); l_found INTEGER :=0; counter INTEGER := 0; counter1 INTEGER := 0; l_host_name VARCHAR2(64); BEGIN l_local_guids := mgmt_user.get_repository_targets(1); IF oms_hosts_in = 0 THEN SELECT target_guid BULK COLLECT INTO l_guids FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND emd_url IN (SELECT DISTINCT emd_url FROM mgmt_targets WHERE target_guid IN (SELECT * FROM TABLE(CAST(l_local_guids AS MGMT_USER_GUID_ARRAY)))) UNION ALL SELECT c.target_guid FROM mgmt_oms_parameters a, mgmt_targets c WHERE a.name='TIMESTAMP' AND a.host_url IS NOT NULL AND c.target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE AND c.host_name LIKE '%'||SUBSTR(a.host_url, 1, NVL(INSTR(a.host_url, '.')-1,length(a.host_url)))||'%' AND ROWNUM = 1 UNION ALL SELECT * FROM TABLE(CAST(l_local_guids AS MGMT_USER_GUID_ARRAY)); ELSE SELECT target_guid BULK COLLECT INTO l_guids FROM mgmt_targets WHERE target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND emd_url IN (SELECT DISTINCT emd_url FROM mgmt_targets WHERE target_guid IN (SELECT * FROM TABLE(CAST(l_local_guids AS MGMT_USER_GUID_ARRAY)))) UNION ALL SELECT * FROM TABLE(CAST(l_local_guids AS MGMT_USER_GUID_ARRAY)); END IF; FOR crec IN (SELECT DISTINCT c.target_guid FROM mgmt_oms_parameters a, mgmt_failover_table b, mgmt_targets c, mgmt_target_properties d WHERE a.name='TIMESTAMP' AND a.host_url IS NOT NULL AND a.host_url=b.host_url(+) AND c.target_type = MGMT_GLOBAL.G_OC4J_TARGET_TYPE AND c.target_name like '%OC4J_EM' AND d.target_guid = c.target_guid AND d.property_name = 'HTTPMachine' AND d.property_value LIKE '%'||SUBSTR(a.host_url, 1, NVL(INSTR(a.host_url, '.')-1,length(a.host_url)))||'%') LOOP l_guids.extend(1); l_guids(l_guids.LAST) := crec.target_guid; BEGIN -- now get the ias targets - the oc4j will be a member of the composites FOR ccrec in -- now get the ias target - the oc4j will be a member of that composite (SELECT a.source_target_guid FROM mgmt_target_assocs a, mgmt_targets b WHERE a.assoc_guid = MGMT_ASSOC.g_contains_guid AND a.assoc_target_guid = crec.target_guid AND b.target_guid = a.source_target_guid AND b.target_type = MGMT_GLOBAL.G_IAS_TARGET_TYPE) LOOP l_guids.extend(1); l_guids(l_guids.LAST) := ccrec.source_target_guid; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END LOOP; counter := l_guids.COUNT; -- add unique guids SELECT DISTINCT * BULK COLLECT INTO l_ret_guids FROM TABLE(CAST(l_guids AS MGMT_USER_GUID_ARRAY)); RETURN l_ret_guids; END get_related_targets; -------------------------------------------------------------------------------------------- --- --- --- BEGIN METRICS SECTION --- --- --- -------------------------------------------------------------------------------------------- -- Procedure EVALUATE_MGMT_METRICS -- -- PURPOSE: To evaluate the management system metrics and make appropriate entries -- IN parameters: -- target_guid_in IN The target guid for the metric -- metric_guid_in IN The metric guid for the specific metric -- OUT parameters: -- metric_result_out OUT The result of the metric evaluation PROCEDURE EVALUATE_MGMT_METRICS ( target_guid_in IN RAW, metric_guid_in IN RAW, metric_result_out OUT MGMT_METRIC_RESULTS ) IS l_timestamp DATE := SYSDATE; l_duration NUMBER; l_count NUMBER; l_metric_name VARCHAR2(64); l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_metric_value VARCHAR2(256); BEGIN metric_result_out := NULL; SELECT TRIM(b.metric_name) INTO l_metric_name FROM mgmt_targets a, mgmt_metrics b WHERE b.metric_guid = metric_guid_in AND a.target_type = b.target_type AND a.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = a.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = a.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = a.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = a.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = a.category_prop_5 OR b.category_prop_5 = ' ') AND a.target_guid=target_guid_in; mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Entry into EVALUATE_MGMT_METRICS for metric '||l_metric_name, '','','','',MGMT_GLOBAL.G_DEBUG); -- run the required sql based on the metric guid passed in. Determine the -- severity, type, annotation and message etc. CASE l_metric_name WHEN METRIC_NAMES(RESPONSE_INDEX) THEN metric_result_out := get_metric_response(); WHEN METRIC_NAMES(SERVLET_INDEX) THEN metric_result_out := get_metric_servlet(); WHEN METRIC_NAMES(DBMS_INDEX) THEN metric_result_out := get_metric_dbms(); WHEN METRIC_NAMES(NOTIFICATION_INDEX) THEN metric_result_out := get_metric_notification(); WHEN METRIC_NAMES(JOB_DISPATCHER_INDEX) THEN metric_result_out := get_metric_JOB_DISPATCHER(); WHEN METRIC_NAMES(CONFIGURATION_INDEX) THEN metric_result_out := get_metric_configuration(); WHEN METRIC_NAMES(SESSIONS_INDEX) THEN metric_result_out := get_metric_sessions(); WHEN METRIC_NAMES(DUP_TARGETS_INDEX) THEN metric_result_out := get_metric_dup_targets(); WHEN METRIC_NAMES(LOADER_STATUS_INDEX) THEN metric_result_out := get_metric_loader_status(); WHEN METRIC_NAMES(REP_JOB_DISPATCHER_INDEX) THEN metric_result_out := get_metric_rep_dispatcher(); WHEN METRIC_NAMES(NO_AGENTS_INDEX) THEN metric_result_out := get_metric_no_agents(); WHEN METRIC_NAMES(AGENT_STATUS_INDEX) THEN metric_result_out := get_metric_agent_status(); WHEN METRIC_NAMES(NOTIF_METHODS_INDEX) THEN metric_result_out := get_metric_notif_methods(); ELSE mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Invalid metric name in EVALUATE_MGMT_METRICS: '||l_metric_name, '','','','',MGMT_GLOBAL.G_WARN); END CASE; l_count := metric_result_out.COUNT; mgmt_log.log_performance(MGMT_LOG_NAME, (SYSDATE-l_timestamp)*24*60*60,SYSDATE, 'Y','metrics',TO_CHAR(l_count),l_metric_name); END EVALUATE_MGMT_METRICS; FUNCTION GET_METRIC_RESPONSE RETURN MGMT_METRIC_RESULTS IS l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_metric_value VARCHAR2(256); l_metric_value_1 VARCHAR2(256); l_results MGMT_METRIC_RESULTS; l_index NUMBER := 1; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Entry into MGMT_METRIC_RESPONSE', '','','','',MGMT_GLOBAL.G_DEBUG); l_key_column_value := ' '; l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(RESPONSE_INDEX).COUNT); BEGIN SELECT COUNT(DISTINCT host_url) INTO l_metric_value FROM mgmt_failover_table WHERE sysdate-last_time_stamp < 300; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_failover_table in MGMT_METRIC_RESPONSE', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := '0'; END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(RESPONSE_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(RESPONSE_INDEX)(1)||' value='||l_metric_value,'','','','',MGMT_GLOBAL.G_DEBUG); IF l_metric_value = '0' THEN l_metric_value_1 := 'No active Management Services were found.'; ELSE l_metric_value_1 := l_metric_value||' Management Services are active.'; END IF; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(RESPONSE_INDEX)(l_index), l_key_column_value, l_metric_value_1); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(RESPONSE_INDEX)(2)||' value='||l_metric_value_1,'','','','',MGMT_GLOBAL.G_DEBUG); RETURN l_results; END GET_METRIC_RESPONSE; FUNCTION GET_METRIC_SERVLET RETURN MGMT_METRIC_RESULTS IS l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_metric_value VARCHAR2(256); l_results MGMT_METRIC_RESULTS; l_index NUMBER := 1; l_loop_index NUMBER := 2; l_status NUMBER; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Entry into MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_DEBUG); -- For servlet status, the host_url is our 'key' value. -- We loop through each key, retrieving the columns for -- each oms FOR crec IN (SELECT DISTINCT host_url FROM mgmt_oms_parameters) LOOP -- set our column index to 2 each time, since index 1 is our key column l_loop_index := 2; mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding servlet data for '||crec.host_url, '','','','',MGMT_GLOBAL.G_DEBUG); -- we extend the results by columns-1, since our -- first column is our key column l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(SERVLET_INDEX).COUNT -1); l_key_column_value := crec.host_url; BEGIN SELECT DECODE(count(client_info), 0, 'down','up') INTO l_metric_value FROM gv$session WHERE client_info = crec.host_url; EXCEPTION WHEN NO_DATA_FOUND THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'No sessions found for '||crec.host_url||' in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_INFO); l_metric_value := 'down'; WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from gv$session for '||crec.host_url||' in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_ERROR); l_metric_value := 'down'; END; IF l_metric_value = 'down' THEN BEGIN l_status := mgmt_admin.get_oms_status(crec.host_url); IF l_status = MGMT_GLOBAL.G_STATUS_UP THEN l_metric_value := 'up'; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'No entry found for '||crec.host_url||' in mgmt_failover_table in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_INFO); l_metric_value := 'down'; WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_failover_table for '||crec.host_url||' in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_ERROR); l_metric_value := 'down'; END; END IF; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index), l_key_column_value, l_metric_value); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index)||' value='||l_metric_value|| 'for '||crec.host_url,'','','','',MGMT_GLOBAL.G_DEBUG); l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN SELECT NVL(TO_NUMBER(value),0) INTO l_metric_value FROM mgmt_oms_parameters WHERE name='loaderFileCount' AND host_url = crec.host_url; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_oms_parameters - loaderFileCount in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := '0'; END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index), l_key_column_value, l_metric_value); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index)||' value='||l_metric_value|| 'for '||crec.host_url,'','','','',MGMT_GLOBAL.G_DEBUG); l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN SELECT NVL(value,'None') INTO l_metric_value FROM mgmt_oms_parameters where name='loaderDirectory' AND host_url = crec.host_url; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_oms_parameters - loaderDirectory in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := 'None'; END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index), l_key_column_value, l_metric_value); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index)||' value='||l_metric_value|| 'for '||crec.host_url,'','','','',MGMT_GLOBAL.G_DEBUG); l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN SELECT GREATEST(0,NVL(TRUNC(TO_NUMBER(value)/60,2),0)) INTO l_metric_value FROM mgmt_oms_parameters WHERE name='loaderOldestFile' AND host_url = crec.host_url; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_oms_parameters - loaderOldestFile in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := '0'; END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index), l_key_column_value, l_metric_value); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index)||' value='||l_metric_value|| 'for '||crec.host_url,'','','','',MGMT_GLOBAL.G_DEBUG); l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN SELECT NVL(TO_NUMBER(sum(value)),0) INTO l_metric_value FROM mgmt_system_performance_log WHERE job_name = 'EMD_NOTIFICATION.NotificationDelivery Subsystem' AND time > (sysdate-(1/(24*6))) AND host_url = crec.host_url; EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_value := '0'; WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving notification total in MGMT_METRIC_SERVLET', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := '0'; END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index), l_key_column_value, l_metric_value) ; mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(SERVLET_INDEX)(l_loop_index)||' value='||l_metric_value|| 'for '||crec.host_url,'','','','',MGMT_GLOBAL.G_DEBUG); l_index := l_index + 1; END LOOP; RETURN l_results; END GET_METRIC_SERVLET; FUNCTION GET_METRIC_DBMS RETURN MGMT_METRIC_RESULTS IS l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_throughput VARCHAR2(64); l_runtime VARCHAR2(64); l_metric_value VARCHAR2(256); l_results MGMT_METRIC_RESULTS; l_index NUMBER := 1; l_loop_index NUMBER := 2; err NUMBER; l_ok VARCHAR2(2) := 'OK'; l_invalid VARCHAR2(7) := 'INVALID'; l_job NUMBER; l_dbms_jobs CURSORTYPE := GET_REPOS_DBMS_JOBS(); l_job_name mgmt_performance_names.display_name%TYPE; l_next_time user_jobs.next_date%TYPE; l_broken VARCHAR2(64); BEGIN l_results := MGMT_METRIC_RESULTS(); LOOP FETCH l_dbms_jobs INTO l_job_name, l_next_time, l_broken; EXIT WHEN l_dbms_jobs%NOTFOUND; l_loop_index := 2; -- we extend the results by columns-1, since our -- first column is our key column l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(DBMS_INDEX).COUNT -1); l_key_column_value := l_job_name; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_broken); l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN IF(l_broken = 'UP') THEN IF((SYSDATE - l_next_time) > 1/12) THEN l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_invalid); mgmt_log.log_error(MGMT_LOG_NAME, err, 'Set interval Invalid in MGMT_METRIC_DBMS for '|| l_key_column_value||' '||TO_CHAR(l_next_time,'Mon DD, YYYY HH:MI:SS AM'), '','','','',MGMT_GLOBAL.G_DEBUG); ELSE IF((SYSDATE - l_next_time) < -356) THEN l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_invalid); mgmt_log.log_error(MGMT_LOG_NAME, err, 'Set interval Invalid in MGMT_METRIC_DBMS for '|| l_key_column_value||' '||TO_CHAR(l_next_time,'Mon DD, YYYY HH:MI:SS AM'), '','','','',MGMT_GLOBAL.G_DEBUG); ELSE l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_ok); mgmt_log.log_error(MGMT_LOG_NAME, err, 'Set interval OK in MGMT_METRIC_DBMS for '||l_key_column_value, '','','','',MGMT_GLOBAL.G_DEBUG); END IF; END IF; ELSE l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_ok); mgmt_log.log_error(MGMT_LOG_NAME, err, 'Set interval OK in MGMT_METRIC_DBMS for '||l_key_column_value, '','','','',MGMT_GLOBAL.G_DEBUG); END IF; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error for invalid schedule MGMT_METRIC_DBMS for '||l_key_column_value, '','','','',MGMT_GLOBAL.G_WARN); l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_ok); END; l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN SELECT NVL(AVG((value*1000.0)/duration),0), NVL((((sum(duration)/(60*1000))/60)*100),0) INTO l_throughput, l_runtime FROM mgmt_system_performance_log a, mgmt_performance_names b WHERE l_key_column_value = b.display_name AND a.job_name = b.job_name AND a.time > (sysdate-(1/24)) AND a.is_total='Y' AND a.duration > 0; mgmt_log.log_error(MGMT_LOG_NAME, err, 'MGMT_METRIC_DBMS for '||l_key_column_value||' l_throughput='||l_throughput||' l_runtime='||l_runtime, '','','','',MGMT_GLOBAL.G_DEBUG); EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_system_performance_log in MGMT_METRIC_DBMS for '||l_key_column_value, '','','','',MGMT_GLOBAL.G_WARN); l_throughput := 0; l_runtime := 0; END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_runtime); l_index := l_index + 1; l_loop_index := l_loop_index + 1; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DBMS_INDEX)(l_loop_index), l_key_column_value, l_throughput); l_index := l_index + 1; END LOOP; RETURN l_results; END GET_METRIC_DBMS; FUNCTION GET_METRIC_NOTIFICATION RETURN MGMT_METRIC_RESULTS IS l_metric_name VARCHAR2(64); l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_run VARCHAR2(256); l_throughput VARCHAR2(256); l_metric_value VARCHAR2(256); l_results MGMT_METRIC_RESULTS; l_count NUMBER := 0; l_index NUMBER := 1; l_num_ready NUMBER := 0; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_key_column_value := ' '; -- No key column for this metric, so we'll make entries for them all l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(NOTIFICATION_INDEX).COUNT); BEGIN SELECT count(broken) INTO l_count FROM user_jobs WHERE what = 'EMD_NOTIFICATION.CHECK_FOR_SEVERITIES'; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from user_jobs in MGMT_METRIC_NOTIFICATION', '','','','',MGMT_GLOBAL.G_WARN); l_count := 0; END; IF(l_count > 0) THEN l_metric_value := 'UP'; ELSE l_metric_value := 'DOWN'; END IF; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIFICATION_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; BEGIN SELECT NVL((((sum(duration)/(60*1000))/60)*100),0), NVL(AVG((value*1000.0)/duration),0) INTO l_run, l_throughput FROM mgmt_system_performance_log WHERE is_total='Y' AND duration > 0 AND time > (sysdate-(1/24)) AND job_name = 'EMD_NOTIFICATION.NotificationDelivery Subsystem' ; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_run := 0; l_throughput := 0; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from performance table in MGMT_METRIC_NOTIFICATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIFICATION_INDEX)(l_index), l_key_column_value, l_run); l_index := l_index + 1; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIFICATION_INDEX)(l_index), l_key_column_value, l_throughput); l_index := l_index + 1; BEGIN SELECT count(msg_id) INTO l_num_ready FROM aq$mgmt_notify_qtable WHERE msg_state = 'READY'; IF l_num_ready > 0 THEN mgmt_log.log_performance(EMD_NOTIFICATION.DELIVER_MODULE_NAME, 0, SYSDATE, 'N', EMD_NOTIFICATION.PERF_WAITING_IN_QUEUE, l_num_ready); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from aq$mgmt_notify_qtable - Queued Notifications in MGMT_METRIC_NOTIFICATION', '','','','',MGMT_GLOBAL.G_WARN); END; BEGIN SELECT NVL(AVG(value),0) INTO l_metric_value FROM mgmt_system_performance_log WHERE job_name=EMD_NOTIFICATION.DELIVER_MODULE_NAME AND name=EMD_NOTIFICATION.PERF_WAITING_IN_QUEUE AND time>(SYSDATE-(1/24)); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_system_performance_log - Queued Notifications in MGMT_METRIC_NOTIFICATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIFICATION_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; BEGIN SELECT NVL(AVG(value) ,0) INTO l_metric_value FROM mgmt_system_performance_log WHERE job_name='Notification Delivery' AND name='Delivery Time' AND time>(SYSDATE-(1/24)); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_system_performance_log - Delivery Time in MGMT_METRIC_NOTIFICATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIFICATION_INDEX)(l_index), l_key_column_value, l_metric_value); RETURN l_results; END GET_METRIC_NOTIFICATION; FUNCTION GET_METRIC_JOB_DISPATCHER RETURN MGMT_METRIC_RESULTS IS l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_metric_value VARCHAR2(256); l_metric_value_1 VARCHAR2(256); l_results MGMT_METRIC_RESULTS; l_index NUMBER := 1; l_loop_index NUMBER := 2; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); FOR crec IN (SELECT DISTINCT host_url FROM mgmt_oms_parameters WHERE name='TIMESTAMP') LOOP l_loop_index := 2; l_key_column_value := crec.host_url; -- for each loop, extend columns -1, since this is a keyed metric l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(JOB_DISPATCHER_INDEX).COUNT-1); BEGIN SELECT NVL((((sum(duration)/(60*1000))/60)*100),0), NVL(AVG((value*1000.0)/duration),0) INTO l_metric_value, l_metric_value_1 FROM mgmt_system_performance_log WHERE is_total='Y' AND duration > 0 AND time > (sysdate-(1/24)) AND job_name = 'JOB_DISPATCHER' AND host_url = crec.host_url; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; l_metric_value_1 := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgm_system_performance_log in MGMT_JOB_DISPATCHER for oms '||crec.host_url, '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(JOB_DISPATCHER_INDEX)(l_loop_index), l_key_column_value, l_metric_value); l_index := l_index + 1; l_loop_index := l_loop_index + 1; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(JOB_DISPATCHER_INDEX)(l_loop_index), l_key_column_value, l_metric_value_1); l_index := l_index + 1; END LOOP; RETURN l_results; END GET_METRIC_JOB_DISPATCHER; FUNCTION GET_METRIC_CONFIGURATION RETURN MGMT_METRIC_RESULTS IS l_metric_column VARCHAR2(64); l_key_column_value VARCHAR2(256); l_metric_value VARCHAR2(256); l_metric_value_1 VARCHAR2(256); l_results MGMT_METRIC_RESULTS; l_index NUMBER := 1; l_used NUMBER := 0; l_total NUMBER := 0; l_used_1 NUMBER := 0; l_total_1 NUMBER := 0; l_old_count NUMBER := 0; l_targets NUMBER := 0; l_users NUMBER := 0; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_key_column_value := ' '; -- No key column for this metric, so we'll make entries for them all l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(CONFIGURATION_INDEX).COUNT); BEGIN SELECT count(target_name) INTO l_targets FROM mgmt_targets; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_targets := 0; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_targets in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_targets); l_index := l_index + 1; BEGIN IF OWNER_NAME IS NULL THEN SELECT owner INTO OWNER_NAME FROM dba_procedures WHERE object_name = 'MGMT_USER' AND procedure_name = 'DROP_USER'; END IF; SELECT count(user_name) INTO l_users FROM mgmt_created_users WHERE user_name <> MGMT_VIEW_NAME AND user_name <> OWNER_NAME; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_users := 0; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from sys.dba_role_privs in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_users); l_index := l_index + 1; BEGIN SELECT count(role_name) INTO l_metric_value FROM mgmt_roles; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_roles in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; BEGIN SELECT count(target_name) INTO l_metric_value FROM mgmt_targets WHERE is_group='1'; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data from mgmt_targets (groups) in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; BEGIN SELECT SUM(used), SUM(total) INTO l_metric_value_1, l_metric_value FROM (SELECT /*+ ORDERED */ SUM(d.bytes)/(1024*1024)-MAX(s.bytes) used, SUM(d.bytes)/(1024*1024) total FROM (SELECT tablespace_name, SUM(bytes)/(1024*1024) bytes FROM (SELECT /*+ ORDERED USE_NL(obj tab) */ DISTINCT ts.name FROM sys.obj$ obj, sys.tab$ tab, sys.ts$ ts WHERE obj.owner# = userenv('SCHEMAID') AND obj.obj# = tab.obj# AND tab.ts# = ts.ts# AND bitand(tab.property,1) = 0 AND bitand(tab.property,4194400) = 0) tn, dba_free_space sp WHERE sp.tablespace_name = tn.name GROUP BY sp.tablespace_name) s, dba_data_files d WHERE d.tablespace_name = s.tablespace_name GROUP BY d.tablespace_name); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; l_metric_value_1 := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data for tables in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_metric_value_1); l_index := l_index + 1; BEGIN SELECT value INTO l_old_count FROM mgmt_current_metrics a, mgmt_metrics b, mgmt_targets c WHERE b.target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND a.metric_guid = b.metric_guid AND b.metric_column = METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(1) AND b.metric_name = METRIC_NAMES(CONFIGURATION_INDEX) AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); l_metric_value := TO_CHAR(l_targets - l_old_count); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_old_count := 0; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving target rate in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_metric_value); l_index := l_index + 1; BEGIN SELECT value INTO l_old_count FROM mgmt_current_metrics a, mgmt_metrics b, mgmt_targets c WHERE b.target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE AND a.metric_guid = b.metric_guid AND b.metric_column = METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(2) AND b.metric_name = METRIC_NAMES(CONFIGURATION_INDEX) AND c.type_meta_ver = b.type_meta_ver AND (b.category_prop_1 = c.category_prop_1 OR b.category_prop_1 = ' ') AND (b.category_prop_2 = c.category_prop_2 OR b.category_prop_2 = ' ') AND (b.category_prop_3 = c.category_prop_3 OR b.category_prop_3 = ' ') AND (b.category_prop_4 = c.category_prop_4 OR b.category_prop_4 = ' ') AND (b.category_prop_5 = c.category_prop_5 OR b.category_prop_5 = ' '); l_metric_value := TO_CHAR(l_users - l_old_count); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_old_count := 0; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving target rate in MGMT_METRIC_CONFIGURATION', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(CONFIGURATION_INDEX)(l_index), l_key_column_value, l_metric_value); RETURN l_results; END GET_METRIC_CONFIGURATION; FUNCTION GET_METRIC_SESSIONS RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_metric_value VARCHAR2(40); l_index NUMBER := 1; l_loop_index NUMBER := 2; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); IF OWNER_NAME IS NULL THEN BEGIN SELECT owner INTO OWNER_NAME FROM dba_procedures WHERE object_name = 'MGMT_USER' AND procedure_name = 'DROP_USER'; EXCEPTION WHEN OTHERS THEN NULL; END; END IF; BEGIN FOR crec IN (SELECT DISTINCT host_url FROM mgmt_oms_parameters) LOOP -- this is not really necessary since we only have one column, but will make it easier to maintain -- if future changes add columns l_loop_index := 2; BEGIN SELECT COUNT(client_info) INTO l_metric_value FROM gv$session WHERE username=OWNER_NAME AND client_info = crec.host_url GROUP BY client_info; EXCEPTION WHEN NO_DATA_FOUND THEN l_metric_value := '0'; WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error '||TO_CHAR(err)||' retrieving data in MGMT_METRIC_SESSIONS', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := '0'; END; l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(SESSIONS_INDEX).COUNT -1); l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(SESSIONS_INDEX)(l_loop_index), crec.host_url,l_metric_value); l_index := l_index + 1; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'No omss', '','','','',MGMT_GLOBAL.G_INFO); WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error '||TO_CHAR(err)||' retrieving data in MGMT_METRIC_SESSIONS', '','','','',MGMT_GLOBAL.G_WARN); END; RETURN l_results; END GET_METRIC_SESSIONS; FUNCTION GET_METRIC_DUP_TARGETS RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_metric_value VARCHAR2(10); l_key_column_value VARCHAR2(2); err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_results.extend(1); l_key_column_value := ''; BEGIN SELECT TO_CHAR(COUNT(d.target_name)) INTO l_metric_value FROM mgmt_duplicate_targets d, mgmt_targets t, mgmt_targets ca WHERE d.target_guid = t.target_guid (+) AND d.duplicate_emd_url = ca.emd_url AND d.resolved_time IS NULL AND ca.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'MGMT_METRIC_DUP_TARGETS count='||l_metric_value, '','','','',MGMT_GLOBAL.G_DEBUG); EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving data in MGMT_METRIC_DUP_TARGETS', '','','','',MGMT_GLOBAL.G_WARN); l_metric_value := '-1'; END; l_results(1) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(DUP_TARGETS_INDEX)(1), l_key_column_value, l_metric_value); RETURN l_results; END GET_METRIC_DUP_TARGETS; FUNCTION GET_METRIC_LOADER_STATUS RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_index NUMBER := 0; l_loader_name VARCHAR2(256) ; l_hr_throughput VARCHAR(64); l_runtime VARCHAR(64); l_sec_throughput VARCHAR(64); l_loader_list mgmt_medium_string_table := mgmt_medium_string_table() ; err NUMBER; -- Check if a result record was generated for the loader FUNCTION found(p_loader_name IN VARCHAR2) RETURN BOOLEAN IS l_found BOOLEAN := FALSE ; BEGIN IF l_loader_list IS NOT NULL AND l_loader_list.COUNT > 0 THEN FOR i IN l_loader_list.FIRST..l_loader_list.LAST LOOP IF l_loader_list(i) = p_loader_name THEN l_found := TRUE ; EXIT ; END IF ; END LOOP ; END IF ; RETURN l_found ; END found ; -- Create a metric result record set for the given key_column_value (loader) PROCEDURE add_to_results(p_key_column_value IN VARCHAR2, p_hr_throughput IN NUMBER, p_runtime IN NUMBER, p_sec_throughput IN NUMBER) IS BEGIN -- extend columns -1, since this is a keyed metric ( 1 key column) l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(LOADER_STATUS_INDEX).COUNT-1); l_index := l_index + 1; l_results(l_index) := MGMT_METRIC_RESULT (METRIC_COLUMN_NAMES(LOADER_STATUS_INDEX)(2), p_key_column_value, TO_CHAR(p_hr_throughput)); l_index := l_index + 1; l_results(l_index) := MGMT_METRIC_RESULT (METRIC_COLUMN_NAMES(LOADER_STATUS_INDEX)(3), p_key_column_value, TO_CHAR(p_runtime)); l_index := l_index + 1; l_results(l_index) := MGMT_METRIC_RESULT( METRIC_COLUMN_NAMES(LOADER_STATUS_INDEX)(4), p_key_column_value, TO_CHAR(p_sec_throughput)); END add_to_results ; BEGIN l_results := MGMT_METRIC_RESULTS(); BEGIN FOR crec IN (SELECT host_url, module, NVL(SUM(value),0) hr_throughput, NVL(SUM(duration/1000.0),0) runtime, NVL((SUM(value)*1000.0),0)/ DECODE(NVL(SUM(duration),0),0,1,SUM(duration)) sec_throughput FROM mgmt_system_performance_log WHERE job_name = G_LOADER_JOB AND module LIKE G_LOADER_MODULE||'%' AND name = G_LOADER_NAME_RECORDS AND is_total = 'Y' AND duration > 0 AND time > (SYSDATE - (1/24)) GROUP BY host_url, module ) LOOP mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(LOADER_STATUS_INDEX)(1)|| ' '||crec.host_url,'','','','',MGMT_GLOBAL.G_DEBUG); l_loader_name := SUBSTR(crec.host_url||','||crec.module,1,256); l_loader_list.extend(1) ; l_loader_list(l_loader_list.COUNT) := l_loader_name ; -- Create metric results for the loader add_to_results(l_loader_name, crec.hr_throughput, crec.runtime, crec.sec_throughput) ; END LOOP ; IF SQL%NOTFOUND THEN mgmt_log.log_error(MGMT_LOG_NAME, 0, 'No oms', '','','','',MGMT_GLOBAL.G_INFO); END IF ; -- Now loop through and log a 0 record for all the -- XMLLoader threads which did not log into performance FOR rec in ( SELECT host_url,value FROM mgmt_oms_parameters WHERE name = G_LOADER_POOLSIZE_PARAM ) LOOP FOR i IN 1..rec.value LOOP l_loader_name := SUBSTR(rec.host_url||','||G_LOADER_MODULE||(i-1),1,256); IF NOT found(l_loader_name) THEN add_to_results(l_loader_name,0,0,0) ; END IF ; END LOOP ; END LOOP ; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error '||TO_CHAR(err)||' Adding '|| METRIC_COLUMN_NAMES(LOADER_STATUS_INDEX)(1),'','','','',MGMT_GLOBAL.G_WARN); END; RETURN l_results; END GET_METRIC_LOADER_STATUS; FUNCTION GET_METRIC_REP_DISPATCHER RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_metric_value VARCHAR2(20); l_key_column_value VARCHAR2(2); l_index NUMBER := 1; l_loop_index NUMBER := 1; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_results.extend(1); l_key_column_value := ''; BEGIN BEGIN SELECT COUNT(start_time) INTO l_metric_value FROM mgmt_job_execution WHERE step_type IN ( MGMT_JOB_ENGINE.STEPTYPE_STEP, MGMT_JOB_ENGINE.STEPTYPE_PARAMSRC, MGMT_JOB_ENGINE.STEPTYPE_PARAMSRC_RETRY ) AND step_status = MGMT_JOBS.SCHEDULED_STATUS AND start_time <= SYS_EXTRACT_UTC(SYSTIMESTAMP); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '0'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving start_time count from mgmt_job_execution in GET_METRIC_REP_DISPATCHER', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(REP_JOB_DISPATCHER_INDEX)(l_loop_index), l_key_column_value, l_metric_value); END; RETURN l_results; END GET_METRIC_REP_DISPATCHER; FUNCTION GET_METRIC_NO_AGENTS RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_metric_value VARCHAR2(20); l_key_column_value VARCHAR2(2); l_index NUMBER := 1; l_loop_index NUMBER := 1; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_results.extend(1); l_key_column_value := ''; BEGIN BEGIN SELECT COUNT(a.current_status) INTO l_metric_value FROM mgmt_current_availability a, mgmt_targets t WHERE a.target_guid = t.target_guid AND t.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND a.current_status = MGMT_GLOBAL.G_STATUS_UP; EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_value := '-1'; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving start_time count from mgmt_job_execution in GET_METRIC_NO_AGENTS', '','','','',MGMT_GLOBAL.G_WARN); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NO_AGENTS_INDEX)(l_loop_index), null, l_metric_value); END; RETURN l_results; END GET_METRIC_NO_AGENTS; FUNCTION GET_METRIC_AGENT_STATUS RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_metric_value VARCHAR2(20); l_key_column_value VARCHAR2(256); l_index NUMBER := 1; l_loop_index NUMBER := 2; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_key_column_value := ''; BEGIN FOR crec IN (SELECT t.target_name, COUNT(*) down_count FROM mgmt_availability a, mgmt_targets t WHERE a.start_collection_timestamp = a.end_collection_timestamp AND a.target_guid = t.target_guid AND t.target_type = MGMT_GLOBAL.G_AGENT_TARGET_TYPE AND a.start_collection_timestamp > SYSDATE-1 GROUP BY t.target_name) LOOP mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Adding '||METRIC_COLUMN_NAMES(AGENT_STATUS_INDEX)(1)||' '||crec.target_name,'','','','',MGMT_GLOBAL.G_DEBUG); -- the first one is the key column l_loop_index := 2; l_key_column_value := crec.target_name; -- for each loop, extend columns -1, since this is a keyed metric l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(AGENT_STATUS_INDEX).COUNT-1); l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(AGENT_STATUS_INDEX)(l_loop_index), l_key_column_value,TO_CHAR(crec.down_count)); l_index := l_index + 1; END LOOP; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving down agent count in GET_METRIC_AGENT_STATUS', '','','','',MGMT_GLOBAL.G_ERROR); END; RETURN l_results; END GET_METRIC_AGENT_STATUS; FUNCTION GET_METRIC_NOTIF_METHODS RETURN MGMT_METRIC_RESULTS IS l_results MGMT_METRIC_RESULTS; l_metric_value VARCHAR2(20); l_key_column_value VARCHAR2(256); l_method_like VARCHAR2(32); l_count NUMBER := 0; l_queued NUMBER := 0; l_total_delivery NUMBER := 0; l_index NUMBER := 1; l_loop_index NUMBER := 2; err NUMBER; BEGIN l_results := MGMT_METRIC_RESULTS(); l_key_column_value := ''; BEGIN FOR i IN 1..EMD_NOTIFICATION.METHOD_COUNT LOOP mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Checking '||EMD_NOTIFICATION.METHOD_NAMES(i),'','','','',MGMT_GLOBAL.G_DEBUG); -- the first one is the key column l_loop_index := 2; l_key_column_value := EMD_NOTIFICATION.METHOD_NAMES(i); -- for each loop, extend columns -1, since this is a keyed metric l_results.extend(MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(NOTIF_METHODS_INDEX).COUNT-1); BEGIN SELECT NVL(SUM(value),0) INTO l_queued FROM mgmt_system_performance_log WHERE name = l_key_column_value || 'S_QUEUED' AND TIME > (SYSDATE-(1/(24*6))); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_queued := 0; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving QUEUED for NOTIF_METHOD_STATUS '|| l_key_column_value, '','','','',MGMT_GLOBAL.G_ERROR); END; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIF_METHODS_INDEX)(l_loop_index), l_key_column_value,TO_CHAR(l_queued)); l_index := l_index + 1; l_loop_index := l_loop_index + 1; BEGIN SELECT NVL(SUM(value),0),count(*) INTO l_total_delivery, l_count FROM mgmt_system_performance_log WHERE name = l_key_column_value || '_TOTAL_DELIVERY_TIME' AND TIME > (SYSDATE-(1/(24*6))); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_total_delivery := 0; l_count := 0; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving TOTAL_DELIVERY_TIME for NOTIF_METHOD_STATUS '|| l_key_column_value, '','','','',MGMT_GLOBAL.G_ERROR); END; IF l_total_delivery > 0 THEN l_total_delivery := l_total_delivery/l_count; END IF; l_results(l_index) := MGMT_METRIC_RESULT(METRIC_COLUMN_NAMES(NOTIF_METHODS_INDEX)(l_loop_index), l_key_column_value,TO_CHAR(l_total_delivery)); l_index := l_index + 1; mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Values for '||EMD_NOTIFICATION.METHOD_NAMES(i)||' '|| l_key_column_value||' '||to_char(l_queued)|| ' '||to_char(l_total_delivery),'','','','',MGMT_GLOBAL.G_DEBUG); END LOOP; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error retrieving NOTIF_METHODS', '','','','',MGMT_GLOBAL.G_ERROR); END; RETURN l_results; END GET_METRIC_NOTIF_METHODS; PROCEDURE eval_repo_performance(p_target_guid IN RAW, p_metric_guid IN RAW, p_coll_name IN VARCHAR2, p_metric_values OUT mgmt_metric_value_array) IS l_worker_count NUMBER ; l_waiting_tasks NUMBER ; l_throughput NUMBER ; l_tasks_processed NUMBER ; l_duration NUMBER ; l_worker_count_list mgmt_integer_array := mgmt_integer_array() ; l_waiting_task_list mgmt_integer_array := mgmt_integer_array() ; l_throughput_list mgmt_integer_array := mgmt_integer_array() ; l_tasks_processed_list mgmt_integer_array := mgmt_integer_array() ; l_duration_list mgmt_integer_array := mgmt_integer_array() ; l_worker_task_class_list mgmt_short_string_array := mgmt_short_string_array() ; l_task_task_class_list mgmt_short_string_array := mgmt_short_string_array() ; l_log_task_class_list mgmt_short_string_array := mgmt_short_string_array() ; l_idx1 NUMBER ; l_idx2 NUMBER ; l_idx3 NUMBER ; FUNCTION index_in_array(p_array IN mgmt_short_string_array, p_task_class IN VARCHAR2) RETURN NUMBER IS BEGIN IF p_array IS NOT NULL AND p_array.COUNT > 0 THEN FOR i IN 1..p_array.COUNT LOOP IF p_array(i) = p_task_class THEN RETURN(i) ; END IF ; END LOOP ; END IF ; RETURN(0) ; END index_in_array ; BEGIN SELECT task_class_list, COUNT(*) BULK COLLECT INTO l_worker_task_class_list, l_worker_count_list FROM mgmt_collection_workers WHERE worker_status != EM_TASK.G_WORKER_STATUS_STOP_PENDING GROUP BY task_class_list ; SELECT task_class,COUNT(*) pending BULK COLLECT INTO l_task_task_class_list, l_waiting_task_list FROM mgmt_collection_tasks WHERE next_collection_timestamp <= SYS_EXTRACT_UTC(systimestamp) AND frequency_code != MGMT_GLOBAL.G_SCHED_FREQUENCY_ONDEMAND GROUP by task_class ; SELECT task_class, ROUND(NVL(SUM(throughput),0),2), NVL(SUM(total_value),0), NVL(SUM(total_duration),0) BULK COLLECT INTO l_log_task_class_list, l_throughput_list, l_tasks_processed_list, l_duration_list FROM (SELECT client_data worker_id, action task_class, NVL(AVG(value*1000/ DECODE(DURATION, 0,1000,DURATION)),0) throughput, SUM(value) total_value, SUM(duration)/1000 total_duration, COUNT(*) records FROM mgmt_system_performance_log log WHERE log.JOB_NAME = MGMT_COLLECTION.G_MODULE_NAME AND log.module = EM_TASK.G_MODULE_NAME AND log.time > (SYSDATE-REPO_COLLECT_INTERVAL/1440) AND log.is_total = 'Y' GROUP BY client_data,action ) GROUP by task_class ; p_metric_values := mgmt_metric_value_array() ; FOR i IN EM_TASK.G_TASK_CLASS_MIN..EM_TASK.G_TASK_CLASS_MAX LOOP l_idx1 := index_in_array(l_worker_task_class_list,i) ; l_idx2 := index_in_array(l_task_task_class_list,i) ; l_idx3 := index_in_array(l_log_task_class_list,i) ; l_worker_count := 0 ; l_waiting_tasks := 0 ; l_throughput := 0 ; l_tasks_processed := 0 ; l_duration := 0 ; IF l_idx1 >0 THEN l_worker_count := l_worker_count_list(l_idx1) ; END IF ; IF l_idx2 > 0 THEN l_waiting_tasks := l_waiting_task_list(l_idx2) ; END IF ; IF l_idx3 > 0 THEN l_throughput := l_throughput_list(l_idx3) ; l_tasks_processed := l_tasks_processed_list(l_idx3) ; l_duration := l_duration_list(l_idx3) ; END IF ; p_metric_values.extend(1) ; p_metric_values(p_metric_values.COUNT) := mgmt_metric_value_obj.new(p_target_guid,null,null, mgmt_namevalue_array( mgmt_namevalue_obj.new(repo_performance_columns(1), EM_TASK.g_task_class_desc(i+1)), mgmt_namevalue_obj.new(repo_performance_columns(2),l_throughput), mgmt_namevalue_obj.new(repo_performance_columns(3),l_worker_count), mgmt_namevalue_obj.new(repo_performance_columns(4),l_waiting_tasks), mgmt_namevalue_obj.new(repo_performance_columns(5),l_tasks_processed), mgmt_namevalue_obj.new(repo_performance_columns(6),l_duration) )) ; END LOOP ; IF (l_worker_task_class_list IS NULL OR l_worker_task_class_list.COUNT = 0) AND EMD_MAINT_UTIL.IS_CENTRAL_MODE =0 THEN -- in dbconsole there is only one thread which picks up both short/long class tasks -- so just sum them up into the short thread and delete for the long running thread p_metric_values(1).metric_values(2).value := p_metric_values(1).metric_values(2).value + p_metric_values(2).metric_values(2).value ; p_metric_values(1).metric_values(3).value := p_metric_values(1).metric_values(3).value + p_metric_values(2).metric_values(3).value ; p_metric_values(1).metric_values(4).value := p_metric_values(1).metric_values(4).value + p_metric_values(2).metric_values(4).value ; p_metric_values(1).metric_values(5).value := p_metric_values(1).metric_values(5).value + p_metric_values(2).metric_values(5).value ; p_metric_values(1).metric_values(6).value := p_metric_values(1).metric_values(6).value + p_metric_values(2).metric_values(6).value ; p_metric_values.DELETE(2) ; END IF ; END eval_repo_performance ; PROCEDURE EVAL_AGENT_METRICS(target_guid_in IN mgmt_target_guid_array, metric_guid_in IN RAW, coll_name_in IN VARCHAR2, metric_values_out OUT mgmt_metric_value_array) IS l_metric_value VARCHAR2(40); l_property VARCHAR2(40); l_property_n NUMBER; err NUMBER; BEGIN metric_values_out := mgmt_metric_value_array() ; metric_values_out.extend(target_guid_in.COUNT) ; FOR i IN 1..target_guid_in.COUNT LOOP BEGIN l_property := EM_COLL_UTIL.get_coll_item_property( p_object_guid => target_guid_IN(i), p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_metric_guid => metric_guid_in, p_coll_name => MGMT_ADMIN_DATA.AGENT_REPO_METRIC, p_property_name => MGMT_ADMIN_DATA.AGENT_REPO_PROPERTY_NAME); IF l_property IS NULL THEN l_property := MGMT_ADMIN_DATA.AGENT_REPO_WINDOW_DEF_VALUE; END IF; l_property_n := TO_NUMBER(l_property); mgmt_log.log_error(MGMT_LOG_NAME, err, 'EVAL_AGENT_METRICS (Targets not uploading data) l_property='||l_property, '','','','',MGMT_GLOBAL.G_DEBUG); l_metric_value := 0; SELECT COUNT(a.target_name) INTO l_metric_value FROM mgmt_targets a, mgmt_current_availability b, mgmt_targets c WHERE c.target_guid = target_guid_in(i) AND c.emd_url = a.emd_url AND a.target_guid = b.target_guid AND b.current_status = 1 AND a.target_type NOT IN (mgmt_global.G_AGENT_TARGET_TYPE, mgmt_global.G_BEACON_TARGET_TYPE, mgmt_global.G_REPOSITORY_TARGET_TYPE) AND a.last_load_time < ((SYSDATE - ((TO_NUMBER(TO_CHAR(systimestamp, 'TZH'))* 60 + TO_NUMBER(TO_CHAR(systimestamp, 'TZM'))) / (24 * 60)) + (a.timezone_delta / (24 * 60))) - (l_property_n / (24 * 60))); EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'Error in EVAL_AGENT_METRICS (Targets not uploading data) for agent guid '||TO_CHAR(target_guid_in(i))||' err='||sqlerrm(err), '','','','',MGMT_GLOBAL.G_ERROR); l_metric_value := 0; END; metric_values_out(i) := mgmt_metric_value_obj.new(target_guid_in(i),null,null, mgmt_namevalue_array(mgmt_namevalue_obj.new(agent_repo_metric_cols(1),l_metric_value))); END LOOP; END EVAL_AGENT_METRICS; -------------------------------------------------------------------------------------------- --- --- --- END OF THE METRIC SECTION --- --- --- -------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------- --- --- --- BEGIN CALLBACKS SECTION --- --- --- -------------------------------------------------------------------------------------------- -- Procedure HANDLE_EMREP_METAVER_UPDATE -- -- PURPOSE: Add target meta ver update call back to add oracle_emrep for the new type meta ver. -- 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 HANDLE_EMREP_METAVER_UPDATE (p_callback_obj IN MGMT_TARGET_META_VER_CBK_OBJ) IS l_target_name MGMT_TARGETS.target_name%TYPE; l_target_type MGMT_TARGETS.target_type%TYPE; BEGIN -- if to and from meta ver don't match, register/start the collection -- in new meta ver IF NOT (p_callback_obj.to_meta_ver = p_callback_obj.from_meta_ver) THEN IF (p_callback_obj.target_type = MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE) THEN SELECT target_name, target_type INTO l_target_name, l_target_type FROM MGMT_TARGETS WHERE p_callback_obj.target_guid = target_guid; IF (p_callback_obj.to_meta_ver = mgmt_global.G_MGMT_SYS_TYPE_META_VER) THEN ADD_EMREP_COLLECTIONS(l_target_name, l_target_type, p_callback_obj.target_guid); END IF; END IF; END IF; END HANDLE_EMREP_METAVER_UPDATE; -- Procedure ADD_EMREP_CALLBACK -- -- PURPOSE: The proc called when the emrep target is added. This creates the collection interval and thresholds -- and starts the metric collection -- 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_EMREP_CALLBACK ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW ) IS l_type_meta_ver mgmt_targets.type_meta_ver%TYPE; BEGIN SELECT type_meta_ver INTO l_type_meta_ver FROM mgmt_targets WHERE target_guid = target_guid_in; -- only register collection when l_type_meta_ver is latest IF (l_type_meta_ver = mgmt_global.G_MGMT_SYS_TYPE_META_VER) THEN ADD_EMREP_COLLECTIONS(target_name_in, target_type_in, target_guid_in); END IF; END ADD_EMREP_CALLBACK; -- Procedure ADD_AGENT_CALLBACK -- -- PURPOSE: The proc called when an agent 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_AGENT_CALLBACK ( target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW ) IS l_metric_guid mgmt_metrics.metric_guid%TYPE; l_metric_column_guid RAW(16); l_metric_interval NUMBER := mgmt_admin_data.AGENT_INTERVAL; l_property VARCHAR2(40); l_count NUMBER; err NUMBER := 0; BEGIN BEGIN l_metric_guid := MGMT_METRIC.get_metric_guid( target_type_in, MGMT_ADMIN_DATA.AGENT_REPO_METRIC, ' '); l_metric_column_guid := MGMT_METRIC.get_metric_guid( target_type_in, MGMT_ADMIN_DATA.AGENT_REPO_METRIC, MGMT_ADMIN_DATA.AGENT_REPO_METRIC_COLS(1)); SELECT count(target_guid) INTO l_count FROM mgmt_metric_thresholds WHERE target_guid = target_guid_in AND metric_guid = l_metric_column_guid; IF (l_count < 1) THEN BEGIN INSERT INTO mgmt_metric_thresholds (target_guid, metric_guid, coll_name, key_value, warning_operator, warning_threshold, critical_operator, critical_threshold, num_occurences, num_warnings, num_criticals, eval_order, fixit_job) VALUES (target_guid_in, l_metric_column_guid, MGMT_ADMIN_DATA.AGENT_REPO_METRIC, NULL, MGMT_GLOBAL.G_THRESHOLD_GT, 0, MGMT_GLOBAL.G_THRESHOLD_GT, 0, 1, 0, 0, 0, ''); EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for insert threshold '||MGMT_ADMIN_DATA.AGENT_REPO_METRIC||' err '|| err, '','','','',MGMT_GLOBAL.G_DEBUG); END; END IF; l_property := EM_COLL_UTIL.get_coll_item_property( p_object_guid => target_guid_IN, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_metric_guid => l_metric_guid, p_coll_name => MGMT_ADMIN_DATA.AGENT_REPO_METRIC, p_property_name => MGMT_ADMIN_DATA.AGENT_REPO_PROPERTY_NAME); if(l_property IS NULL) THEN EM_COLL_UTIL.add_coll_item_property(p_object_guid => target_guid_IN, p_object_type => MGMT_GLOBAL.G_OBJECT_TYPE_TARGET, p_metric_guid => l_metric_guid, p_coll_name => MGMT_ADMIN_DATA.AGENT_REPO_METRIC, p_property_name => MGMT_ADMIN_DATA.AGENT_REPO_PROPERTY_NAME, p_property_value => MGMT_ADMIN_DATA.AGENT_REPO_WINDOW_DEF_VALUE); l_property := MGMT_ADMIN_DATA.AGENT_REPO_WINDOW_DEF_VALUE; END IF; BEGIN MGMT_COLLECTION.start_collection (p_target_name=>target_name_in, p_target_type=>target_type_in, p_metric_name=>MGMT_ADMIN_DATA.AGENT_REPO_METRIC, p_coll_name=>MGMT_ADMIN_DATA.AGENT_REPO_METRIC, p_coll_schedule=> mgmt_coll_schedule_obj.interval_schedule(MGMT_ADMIN_DATA.AGENT_INTERVAL,null,null), p_store_metric=>MGMT_GLOBAL.G_FALSE); EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for start_collection 1 '||MGMT_ADMIN_DATA.AGENT_REPO_METRIC||' err '|| err, '','','','',MGMT_GLOBAL.G_ERROR); END; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for add_agent_callback '||MGMT_ADMIN_DATA.AGENT_REPO_METRIC||' err='|| SQLERRM(err), '','','','',MGMT_GLOBAL.G_ERROR); END ; END ADD_AGENT_CALLBACK; -- Procedure ADD_EMREP_COLLECTIONS -- -- PURPOSE: register and start emrep target collection -- 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_EMREP_COLLECTIONS (target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW) IS l_metric_list_len NUMBER := 6; l_metric_guid RAW(16); l_metric_column_guid RAW(16); l_metric_interval SMP_EMD_INTEGER_ARRAY; l_metric_column_len SMP_EMD_INTEGER_ARRAY; l_metric_columns_col_array SMP_EMD_COL_DEF_ARRAY_ARRAY; l_default_collection SMP_EMD_COL_DEF; l_count NUMBER := 0; l_policy_trend_intvl NUMBER := 360; err NUMBER := 0; BEGIN l_metric_column_len := SMP_EMD_INTEGER_ARRAY(); l_metric_interval := SMP_EMD_INTEGER_ARRAY(); l_metric_columns_col_array := SMP_EMD_COL_DEF_ARRAY_ARRAY(); l_default_collection := SMP_EMD_COL_DEF(0,'',0,'',0,'',0); -- The metric intervals - initialize to default 10 minute l_metric_interval.extend(MGMT_ADMIN_DATA.METRIC_COUNT); FOR i IN 1..MGMT_ADMIN_DATA.METRIC_COUNT LOOP l_metric_interval(i) := mgmt_admin_data.DEFAULT_INTERVAL; END LOOP; -- change those that differ l_metric_interval(MGMT_ADMIN_DATA.RESPONSE_INDEX) := mgmt_admin_data.RESPONSE_INTERVAL; l_metric_interval(MGMT_ADMIN_DATA.CONFIGURATION_INDEX) := mgmt_admin_data.CONFIG_INTERVAL; l_metric_interval(MGMT_ADMIN_DATA.LOADER_STATUS_INDEX) := mgmt_admin_data.LOADER_INTERVAL; l_metric_interval(MGMT_ADMIN_DATA.NO_AGENTS_INDEX) := mgmt_admin_data.AGENT_INTERVAL; l_metric_interval(MGMT_ADMIN_DATA.AGENT_STATUS_INDEX) := mgmt_admin_data.AGENT_INTERVAL; l_metric_column_len.extend(MGMT_ADMIN_DATA.METRIC_COUNT); l_metric_columns_col_array.extend(MGMT_ADMIN_DATA.METRIC_COUNT); -- The number of columns for each of the metrics FOR i IN 1..MGMT_ADMIN_DATA.METRIC_COUNT LOOP l_metric_column_len(i) := MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i).COUNT; l_metric_columns_col_array(i) := SMP_EMD_COL_DEF_ARRAY(); l_metric_columns_col_array(i).extend(l_metric_column_len(i)); END LOOP; ------------------------------- -- Response -- -- This is not currently being registered due to a problem with it also being registered by the agent -- ------------------------------- l_metric_columns_col_array(RESPONSE_INDEX)(1) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_EQ,'0',MGMT_GLOBAL.G_THRESHOLD_EQ,'0',1); ------------------------------- -- Management_Servlet_Status -- ------------------------------- l_metric_columns_col_array(SERVLET_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_MATCH,'DOWN',MGMT_GLOBAL.G_THRESHOLD_MATCH, 'DOWN',1); ----------------------------- -- DBMS_Job_Status -- ----------------------------- l_metric_columns_col_array(DBMS_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_MATCH,'DOWN',MGMT_GLOBAL.G_THRESHOLD_MATCH, 'DOWN',1); l_metric_columns_col_array(DBMS_INDEX)(3) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_MATCH,'INVALID',MGMT_GLOBAL.G_THRESHOLD_MATCH,'INVALID',1); l_metric_columns_col_array(DBMS_INDEX)(4) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'50',MGMT_GLOBAL.G_THRESHOLD_GT,'75',1); ------------------------------ -- Notification_Performance -- ------------------------------ l_metric_columns_col_array(NOTIFICATION_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'50',MGMT_GLOBAL.G_THRESHOLD_GT,'75',1); l_metric_columns_col_array(NOTIFICATION_INDEX)(4) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'300',MGMT_GLOBAL.G_THRESHOLD_GT,'500', 3); -------------------------------- -- Job_Dispatcher_Performance -- -------------------------------- l_metric_columns_col_array(JOB_DISPATCHER_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'50',MGMT_GLOBAL.G_THRESHOLD_GT,'75',1); ----------------------------- -- Configuration -- ----------------------------- -- target and user addition rate - warning 25/hr crit 50/hr over 3 hours l_metric_columns_col_array(CONFIGURATION_INDEX)(7) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'25',MGMT_GLOBAL.G_THRESHOLD_GT,'50',3); l_metric_columns_col_array(CONFIGURATION_INDEX)(8) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'25',MGMT_GLOBAL.G_THRESHOLD_GT,'50',3); ----------------------------- -- Session count -- ----------------------------- l_metric_columns_col_array(SESSIONS_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'25',MGMT_GLOBAL.G_THRESHOLD_GT,'50',1); ----------------------------- -- Duplicate targets -- ----------------------------- l_metric_columns_col_array(DUP_TARGETS_INDEX)(1) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'0',MGMT_GLOBAL.G_THRESHOLD_GT,'0',1); ----------------------------- -- Loader Status -- ----------------------------- l_metric_columns_col_array(LOADER_STATUS_INDEX)(4) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'2700',MGMT_GLOBAL.G_THRESHOLD_GT,'3000',2); -------------------------------- -- Repository Job_Dispatcher -- -------------------------------- l_metric_columns_col_array(REP_JOB_DISPATCHER_INDEX)(1) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'500',MGMT_GLOBAL.G_THRESHOLD_GT,'1000', 1); ----------------------------- -- No_Agents -- ----------------------------- l_metric_columns_col_array(NO_AGENTS_INDEX)(1) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_LT,'1',MGMT_GLOBAL.G_THRESHOLD_LT,'1',1); ----------------------------- -- Agent Status -- ----------------------------- l_metric_columns_col_array(AGENT_STATUS_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'3',MGMT_GLOBAL.G_THRESHOLD_GT,'5',1); ----------------------------- -- Notification Methods -- ----------------------------- l_metric_columns_col_array(NOTIF_METHODS_INDEX)(2) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'300',MGMT_GLOBAL.G_THRESHOLD_GT,'500', 3); l_metric_columns_col_array(NOTIF_METHODS_INDEX)(3) := SMP_EMD_COL_DEF(1,' ',MGMT_GLOBAL.G_THRESHOLD_GT,'600000',MGMT_GLOBAL.G_THRESHOLD_GT,'900000', 3); -- stop existing collections for emrep BEGIN mgmt_collection.stop_collection(p_target_name=>target_name_in, p_target_type=>target_type_in); DELETE FROM mgmt_metric_collections WHERE target_guid = target_guid_in ; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for stop metric column threshold ' ||' err '|| TO_CHAR(err), '','','','',MGMT_GLOBAL.G_ERROR); END ; -- We are not registering the Response metric for the time being FOR i in 2..MGMT_ADMIN_DATA.METRIC_COUNT LOOP BEGIN l_metric_guid := mgmt_metric.get_metric_guid_for_target(MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, target_name_in, MGMT_ADMIN_DATA.METRIC_NAMES(i), ' '); EXCEPTION WHEN OTHERS THEN err := SQLCODE; l_metric_guid := mgmt_user.NO_GUID; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for get metric guid '||MGMT_ADMIN_DATA.METRIC_NAMES(i) ||' err '|| TO_CHAR(err), '','','','',MGMT_GLOBAL.G_ERROR); END; --- add the thresholds for this metric FOR j in 1..l_metric_column_len(i) LOOP BEGIN l_metric_column_guid := mgmt_target.get_metric_guid(MGMT_GLOBAL.G_REPOSITORY_TARGET_TYPE, MGMT_ADMIN_DATA.METRIC_NAMES(i), MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i)(j)); EXCEPTION WHEN OTHERS THEN l_metric_column_guid := mgmt_user.NO_GUID; err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for get metric guid '||MGMT_ADMIN_DATA.METRIC_NAMES(i)||' '|| MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i)(j) ||' err '|| TO_CHAR(err), '','','','',MGMT_GLOBAL.G_ERROR); END; BEGIN -- Add threshold for this metric IF(l_metric_columns_col_array(i)(j).create_col = 1) THEN INSERT INTO mgmt_metric_thresholds (target_guid, metric_guid, coll_name, key_value, warning_operator, warning_threshold, critical_operator, critical_threshold, num_occurences, num_warnings, num_criticals, eval_order, fixit_job) VALUES (target_guid_in, l_metric_column_guid, MGMT_ADMIN_DATA.METRIC_NAMES(i), l_metric_columns_col_array(i)(j).key_value, l_metric_columns_col_array(i)(j).warning_operator, l_metric_columns_col_array(i)(j).warning_threshold, l_metric_columns_col_array(i)(j).critical_operator, l_metric_columns_col_array(i)(j).critical_threshold, l_metric_columns_col_array(i)(j).num_occurences, 0, 0, 0, ''); mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Added threshold for '||mgmt_admin_data.metric_names(i)||' ' ||mgmt_admin_data.metric_column_names(i)(j),'','','','',MGMT_GLOBAL.G_DEBUG); END IF; -- if an exception occurs, log the error and continue EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for add metric column threshold ' ||MGMT_ADMIN_DATA.METRIC_NAMES(i) ||' column ' ||MGMT_ADMIN_DATA.METRIC_COLUMN_NAMES(i)(j) ||' err '|| TO_CHAR(err), '','','','',MGMT_GLOBAL.G_ERROR); END; END LOOP; BEGIN -- start collecting the data mgmt_log.log_error(MGMT_LOG_NAME, 0, 'Start collection for '||mgmt_admin_data.metric_names(i)|| ' interval '||to_char(l_metric_interval(i)),'','','','',MGMT_GLOBAL.G_DEBUG); MGMT_COLLECTION.start_collection (p_target_type=>target_type_in, p_target_name=>target_name_in, p_metric_name=>MGMT_ADMIN_DATA.METRIC_NAMES(i), p_coll_name=>MGMT_ADMIN_DATA.METRIC_NAMES(i), p_coll_schedule=> mgmt_coll_schedule_obj.interval_schedule( l_metric_interval(i), null,null)) ; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION 1 for start_collection '||mgmt_admin_data.metric_names(i) ||' err '|| to_char(err), '','','','',MGMT_GLOBAL.G_ERROR); END; END LOOP; -- Start the repo performance metric collection BEGIN MGMT_COLLECTION.start_collection (p_target_type=>target_type_in, p_target_name=>target_name_in, p_metric_name=>repo_performance_metric, p_coll_name=>repo_performance_metric, p_coll_schedule=> mgmt_coll_schedule_obj.interval_schedule(repo_collect_interval, null,null)) ; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, sqlcode, 'EXCEPTION 2 for start_collection '||repo_performance_metric||' err '|| to_char(err), '','','','',MGMT_GLOBAL.G_ERROR); END ; -- Start the target compliance metric collection BEGIN mgmt_collection.start_collection( p_target_type=>target_type_in, p_target_name=>target_name_in, p_metrics_list=>MGMT_SHORT_STRING_ARRAY('TARGET_COMPLIANCE', 'TARGET_VIOLATIONS', 'GROUP_COMPLIANCE', 'GROUP_VIOLATIONS', 'GROUP_TARGET_COMPLIANCE'), p_coll_schedule=>mgmt_coll_schedule_obj.interval_Schedule( p_interval=>l_policy_trend_intvl, p_start_time=>sysdate, p_end_time=>null) ); EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, sqlcode, 'EXCEPTION for start_collection target_compliance err '|| to_char(err), '','','','',MGMT_GLOBAL.G_ERROR); END ; EXCEPTION WHEN OTHERS THEN err := SQLCODE; mgmt_log.log_error(MGMT_LOG_NAME, err, 'EXCEPTION for create metric ' ||' err '|| SQLCODE, '','','','',MGMT_GLOBAL.G_ERROR); -- RAISE; END ADD_EMREP_COLLECTIONS; --bug 4897258 FUNCTION GET_REPOS_DBMS_JOBS RETURN CURSORTYPE AS repos_dbms_jobs CURSORTYPE; BEGIN IF ( EMD_MAINT_UTIL.IS_CENTRAL_MODE != 0 ) THEN /* if it is EMGC -bug 7479617*/ OPEN repos_dbms_jobs FOR SELECT NVL(regisjob.display_name,regisjob.job_name) what, usrjobs.next_date next_time, DECODE(NVL(usrjobs.broken,'Y'),'Y','DOWN','UP') broken FROM user_jobs usrjobs, mgmt_performance_names regisjob WHERE usrjobs.what(+) LIKE regisjob.dbms_jobname || '%' AND regisjob.is_dbmsjob='Y' AND (usrjobs.job IS NULL OR usrjobs.job IN( SELECT MAX(c.job) FROM user_jobs c, mgmt_performance_names d WHERE c.what like d.dbms_jobname || '%' AND d.is_dbmsjob='Y' GROUP BY d.dbms_jobname ) ) ORDER BY 1; ELSE OPEN repos_dbms_jobs FOR SELECT what, next_date next_time, DECODE(NVL(broken,'Y'),'Y','DOWN','UP') broken FROM user_jobs WHERE what like '%EXECUTE_EM_DBMS_JOB%'; --bug 7479617 END IF; RETURN repos_dbms_jobs; END GET_REPOS_DBMS_JOBS; END mgmt_admin_data; / SHOW ERRORS;