Rem drv: Rem Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/eml/eml_pkgbodys.sql /st_emdbsa_11.2/13 2010/02/08 10:59:07 yemeng Exp $ Rem Rem eml_pkgbodys.sql Rem Rem Copyright (c) 2002, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem eml_pkgbodys.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem yemeng 02/04/10 - optimize the metric sql Rem mpawelko 01/18/08 - XbranchMerge 6699755 disambiguate target_type in PL/SQL; XbranchMerge 7562407 use target_type in getASMTargetName Rem ajdsouza 03/03/09 - get has managed info from metric Rem manaraya 02/23/09 - Bug 7931482 Rem ysun 01/09/09 - update cell Rem ychan 12/22/08 - XbranchMerge ychan_bug-7562377 from Rem st_emdbgc_10.2.0.1.0 Rem ychan 11/24/08 - Fix bug 7562377 Rem ychan 11/22/08 - Fix bug 7428057 Rem swexler 11/13/08 - remove obsolete code Rem ysun 10/31/08 - integrate cell Rem yemeng 09/25/08 - XbranchMerge yemeng_bug-7293143_gc from Rem st_emdbgc_10.2.0.1.0 Rem dchakumk 03/10/08 - XbranchMerge dchakumk_6752_xbmsrc from Rem st_emdbsa_11.1 Rem kramarat 12/10/07 - SI HA Integration Rem ychan 05/21/07 - Remove sql Rem keiwong 04/01/07 - add incident to alert log status Rem mmootha 03/14/06 - 4759988 Rem pbantis 08/09/05 - Tweak HA query from mgmt_target_memberships. Rem kramarat 07/31/05 - Change DGPrimaryDBName into a metric Rem chyu 06/28/05 - New repmgr header impl Rem pbantis 06/03/05 - Fix flashback time. Rem yma 05/16/05 - fix bug 4359198 Rem pbantis 04/08/05 - Fix HA sql to support 9.2 rep db. Rem pbantis 03/16/05 - Get DG role from db. Rem pbantis 03/16/05 - Change dynamic sql. Rem pbantis 03/09/05 - Get HA real-time data for DB Control. Rem rreilly 02/16/05 - change db size to be total of the tablespace size Rem pbantis 02/08/05 - Change getCurrentMetricValues(). Rem jochen 12/14/04 - Allow ASM to use severity functions Rem kramarat 12/08/04 - Rem shaagarw 11/25/04 - Inserting DBVersion in mgmt_user_subtab_col_prefs table Rem swexler 11/16/04 - restructure Rem asawant 10/11/04 - Fix 3944274 Rem asawant 10/08/04 - Cutting over MGMT_USER_TYPE_METRIC_PREFS to Rem MGMT_USER_SUBTAB_COL_PREFS Rem swexler 08/16/04 - allow dynamicinstance Rem mnihalan 07/29/04 - Remove debug line Rem asawant 07/14/04 - Cut over dynamicinstnace to instance Rem rreilly 06/28/04 - add problemTbsp bytesFree Rem mnihalan 06/28/04 - Change GetStgPerf Rem mnihalan 06/24/04 - Change the getStgPerfValues Rem hsu 04/28/04 - fix 3589066 Rem jochen 01/14/04 - Seg findings status needs latest timestamp Rem ychan 02/16/04 - Fix bug 3438815 Rem rpatti 12/15/03 - fix timezone (bug 3316352) Rem jochen 11/13/03 - Count latest StgPerf sample only Rem jsoule 11/05/03 - dates are DATEs. period. Rem jochen 11/03/03 - Fix segment findings count Rem jsoule 10/30/03 - add error logging Rem jsoule 10/30/03 - protect database home page routine from exceptions Rem vchao 10/29/03 - Host CPU Utilization % Rem jsoule 10/27/03 - add OpenMode dynamic property Rem jsoule 10/25/03 - return date for time Rem pbantis 10/24/03 - Bug 3078964 - get the log_mode from ha_info table Rem ychan 10/15/03 - Fix bug 3155629 Rem jsoule 10/15/03 - support Sessions: Other instead of Active Sessions Rem hsu 10/03/03 - use page scan rate Rem vchao 09/17/03 - Remoe redundant columns from wait_bottlenecks Rem ychan 09/10/03 - Fix bug 3131388 Rem vchao 09/09/03 - Replace cpuLoad with cpuLoad_1min for runqueue Rem jsoule 09/02/03 - use user_wait_time_pct exclusively for group page Rem wait chart Rem kmckeen 08/19/03 - Add new 10i problemTbsp metrics Rem ychan 07/29/03 - Fix bug 3070417 Rem xuliu 07/07/03 - Fix emd_database package Rem hying 07/01/03 - Fix bug 2988520, archAreaCount Rem jochen 06/25/03 - wastedspace returns eod row Rem ychan 06/09/03 - Change summary ui Rem jochen 05/08/03 - Stg metrics changed to raw table Rem ychan 05/09/03 - Add active sess Rem vchao 04/18/03 - More metric versioning Rem lhan 04/08/03 - split problemTbsp into two Rem vchao 04/03/03 - Bug 2768204. Support metric versioning Rem jochen 03/28/03 - Add Storage Performance link Rem yfeng 02/14/03 - Fix bug 2802853 - return -1 for top sql count if not available Rem jsoule 01/31/03 - fix db group wait 7-day chart Rem vshah 01/21/03 - fix for 2688312 Rem hsu 12/26/02 - rm unused db group proc Rem vchao 01/16/03 - modify set_default_metric_preferences Rem jsoule 12/12/02 - initialize severity to 15 Rem jsoule 12/06/02 - update latest alert log entry query Rem swexler 11/19/02 - add response severity Rem jsoule 11/05/02 - update comment on All Sessions metric preference Rem jsoule 11/06/02 - use target's, not group's, timezone delta in wait instances for group Rem jsoule 11/01/02 - reorder defaults, based on reviews Rem jsoule 10/31/02 - load GUIDs instead Rem jsoule 10/30/02 - add user created callback for default metric preferences Rem jsoule 10/25/02 - avoid FIRST..LAST traversals of tables Rem yfeng 10/24/02 - Fix Count and severity for Top sql report and sql reuse Rem yfeng 10/22/02 - Remove one procedure Rem yfeng 10/21/02 - Fix bug 2633824 to check configuration for Resp SQL and SQL reuse Rem jsoule 10/23/02 - track severity by key Rem jsoule 10/16/02 - fix alert log value Rem vshah 10/11/02 - rm order by Rem jsoule 10/02/02 - add get top wait instance group routine Rem rpinnama 09/25/02 - Remove hardcoded references to mgmt_rep Rem xshen 09/19/02 - change group rule sql Rem ychan 09/13/02 - Add db group func Rem jsoule 09/13/02 - Rem yfeng 09/04/02 - Return -2 for sql count if it is not configured Rem jsoule 08/29/02 - drive alert log values from current data Rem jsoule 08/29/02 - update alert log indicators Rem jsoule 08/26/02 - update wrt. alert log changes Rem vchao 08/28/02 - Add shema to table name Rem vchao 08/21/02 - Add sql reuse procedure Rem yfeng 08/13/02 - Modify query for bad SQL count Rem jsoule 07/12/02 - remove support for the response metric Rem rpatti 06/13/02 - update for new avail states Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created Rem jsoule 04/23/02 - return non-NULL for latest alert log entry. Rem rpatti 04/10/02 - show availability details Rem jsoule 04/04/02 - return last known alert log tail. Rem tjaiswal 04/01/02 - Misc group fixes Rem jsoule 03/29/02 - . Rem jsoule 03/26/02 - fix compilation errors. Rem jsoule 03/19/02 - add db group page mods for alert log severity changes. Rem jsoule 03/19/02 - change alert log severity semantics. Rem jsoule 02/13/02 - rely on Instance Performance metric. Rem edemembe 02/01/02 - database package fix Rem jsoule 02/04/02 - . Rem jsoule 02/04/02 - fix typo. Rem rreilly 01/30/02 - use memUsedPct for getHostSeveritys Rem jsoule 01/31/02 - fix response baselines and sql severities. Rem jsoule 01/18/02 - pull instance data from properties instead of a metric. Rem rpatti 01/07/02 - more baslined data Rem jsoule 01/04/02 - fix bad sql counter. Rem rpatti 01/03/02 - move member query out of group main page Rem edemembe 01/08/02 - Metrics are now target independent Rem rpatti 12/27/01 - inventory changes for db group pages Rem ancheng 12/20/01 - add fields Rem jsoule 12/18/01 - eliminate string-number translation errors. Rem jsoule 12/14/01 - update response time distribution cursor. Rem rpatti 12/12/01 - fix query Rem rpatti 12/11/01 - fix condition error Rem rpatti 12/06/01 - add db group homepage queries Rem jsoule 11/06/01 - tune homepage queries. Rem rreilly 10/11/01 - use memUsedPct instead of memfreePct Rem groyal 10/05/01 - Fix memoryUsagePct Rem rpatti 10/02/01 - fix host related metric column name Rem mpawelko 09/25/01 - Pass back severity for avgResp Rem rpatti 09/26/01 - use metric_guid instead of metric name, column Rem rpatti 09/10/01 - use consistent timestamp formats Rem mpawelko 09/13/01 - Don't return null values for strings either Rem mpawelko 09/10/01 - Use target_guid instead of target_name, type Rem mpawelko 09/06/01 - Don't return 0 values if null Rem jsoule 09/05/01 - fix dummy condition. Rem mpawelko 08/31/01 - Get host status in getHostValues Rem mpawelko 08/30/01 - Add support for getting host status Rem jsoule 08/29/01 - update problem sql queries. Rem groyal 08/25/01 - Initialize variables Rem jsoule 08/03/01 - rename column of mgmt_sql_evaluation. Rem hjchu 08/09/01 - change logic to count alert log entries Rem hjchu 07/30/01 - change table names from smp_vdt to mgmt_ Rem qsong 07/19/01 - fix syntax error for aCursorVdtCurrentMetricsTbsp Rem hjchu 07/19/01 - fix alert log SQLs Rem jsoule 06/04/01 - update sql evaluation count Rem groyal 05/30/01 - Improve performance Rem glavash 05/29/01 - change column used for active sessions Rem hjchu 05/11/01 - Created Rem CREATE OR REPLACE PACKAGE body emd_database AS /* * set_default_metric_preferences - set the default metrics to show up in the * summary for a given user * * p_user_name - the user being modified * p_callback_type - type of modification; may be one of: * MGMT_USER.USER_CREATED_CALLBACK * MGMT_USER.USER_DROPPED_CALLBACK */ PROCEDURE set_default_metric_preferences(p_user_name IN VARCHAR2, p_callback_type IN NUMBER) AS TYPE guid_list IS TABLE OF RAW(16); -- metrics for summary UI k_metrics CONSTANT guid_list := guid_list ( HEXTORAW('F63470E537948FA4150647777C1C6F6C'), -- Sessions: CPU HEXTORAW('41F56D5BA13F1504351F427330294F67'), -- Sessions: I/O HEXTORAW('C2AB9BEC83DF961752257C9C5476427C'), -- Sessions: Other HEXTORAW('CADFA1BF3907CA34D1CD1DE4D3E46B9B') -- Instance CPU (%) ); cnt NUMBER(2); BEGIN IF p_callback_type = MGMT_USER.USER_CREATED_CALLBACK THEN -- Databases default generic columns EXECUTE IMMEDIATE 'DECLARE TYPE guid_list IS TABLE OF RAW(16); k_metrics CONSTANT guid_list := guid_list ( HEXTORAW(''F63470E537948FA4150647777C1C6F6C''), -- Sessions: CPU HEXTORAW(''41F56D5BA13F1504351F427330294F67''), -- Sessions: I/O HEXTORAW(''C2AB9BEC83DF961752257C9C5476427C''), -- Sessions: Other HEXTORAW(''CADFA1BF3907CA34D1CD1DE4D3E46B9B'') -- Instance CPU (%) ); BEGIN INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES ( :username_i, mgmt_global.g_database_target_type, emd_pref.mgmt_user_pref_avail_col, emd_pref.mgmt_user_pref_generic_col_tp, 1); INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES ( :username_i, mgmt_global.g_database_target_type, emd_pref.mgmt_user_pref_alerts_col, emd_pref.mgmt_user_pref_generic_col_tp, 2); INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES ( :username_i, mgmt_global.g_database_target_type, emd_pref.mgmt_user_pref_pol_viol_col, emd_pref.mgmt_user_pref_generic_col_tp, 3); INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES ( :username_i, mgmt_global.g_database_target_type, emd_pref.mgmt_user_pref_pol_comp_column, emd_pref.mgmt_user_pref_generic_col_tp, 4); INSERT INTO mgmt_user_subtab_col_prefs(user_name, subtab_name, column_id, column_type, display_order) VALUES ( :username_i, mgmt_global.g_database_target_type, ''DBVersion'', emd_pref.mgmt_user_pref_property_col_tp, 5); FOR l_metric_guid_i IN 1..k_metrics.COUNT LOOP INSERT INTO mgmt_user_subtab_col_prefs (user_name, subtab_name, column_id_guid, column_type, display_order) VALUES ( :username_i, mgmt_global.g_database_target_type, k_metrics(l_metric_guid_i), 0, 5 + l_metric_guid_i); END LOOP; END;' USING p_user_name; COMMIT; END IF; END set_default_metric_preferences; /* getCollectionTimestamp */ procedure getCollectionTimestamp( targetGuid IN VARCHAR2, lastUpdatedTime OUT VARCHAR2) IS /* Note: collection_timestamp cannot be null */ CURSOR aCursor (tg IN VARCHAR2) IS SELECT TO_CHAR(last_load_time) FROM mgmt_targets WHERE target_guid = HEXTORAW(tg); begin lastUpdatedTime := ' '; OPEN aCursor (targetGuid); FETCH aCursor INTO lastUpdatedTime; CLOSE aCursor; end; /* isArchiveLogMode */ function isArchiveLogMode( targetGuid IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) return NUMBER IS CURSOR aCursor (tg IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT DECODE(log_mode, 'NOARCHIVELOG', 0, 1) FROM mgmt$ha_info WHERE collection_timestamp = (SELECT MAX(collection_timestamp) FROM mgmt$ha_info WHERE target_guid = HEXTORAW(tg) ) AND target_guid = HEXTORAW(tg); archLogMode NUMBER := k_metric_status_inaccessible; begin OPEN aCursor (targetGuid, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursor INTO archLogMode; CLOSE aCursor; return archLogMode; end; /* getDbStringValues */ procedure getDbStringValues(targetGuid IN VARCHAR2, instanceName OUT VARCHAR2, instanceStartTime OUT DATE, instanceVersion OUT VARCHAR2, oracleHome OUT VARCHAR2, cpuCount OUT NUMBER, openMode OUT VARCHAR2) IS text_start_time VARCHAR2(1024) := NULL; property_time_format VARCHAR2(1024) := NULL; CURSOR aCursor (tg IN VARCHAR2) IS SELECT property_name, NVL(property_value, ' ') FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) AND property_type IN ('INSTANCE', 'DYNAMICINSTANCE') AND property_name IN ('InstanceName', 'StartTime', 'Version', 'OracleHome', 'OpenMode', 'TimeFormat', 'CPUCount'); property_name VARCHAR2(64); property_value VARCHAR2(1024); begin instanceName := ' '; instanceStartTime := NULL; instanceVersion := ' '; oracleHome := ' '; cpuCount := 1; openMode := ' '; OPEN aCursor (targetGuid); LOOP FETCH aCursor INTO property_name, property_value; exit when aCursor%NOTFOUND; IF property_name = 'InstanceName' THEN instanceName := property_value; ELSIF property_name = 'StartTime' THEN text_start_time := property_value; ELSIF property_name = 'TimeFormat' THEN property_time_format := property_value; ELSIF property_name = 'Version' THEN instanceVersion := property_value; ELSIF property_name = 'OracleHome' THEN oracleHome := property_value; ELSIF property_name = 'CPUCount' THEN cpuCount := property_value; ELSIF property_name = 'OpenMode' THEN openMode := property_value; END IF; END LOOP; CLOSE aCursor; IF (text_start_time IS NOT NULL AND property_time_format IS NOT NULL) THEN instanceStartTime := TO_DATE(text_start_time, property_time_format); END IF; end; PROCEDURE getDbStringValues(targetGuid IN VARCHAR2, instanceName OUT VARCHAR2, instanceStartTime OUT DATE, instanceVersion OUT VARCHAR2) IS openMode VARCHAR2(64); oracleHome VARCHAR2(4000); cpuCount NUMBER; BEGIN getDbStringValues(targetGuid, instanceName, instanceStartTime, instanceVersion, oracleHome, cpuCount, openMode); END; procedure getKeyProfile( targetGuid IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2, keyProfileCount OUT NUMBER, keyProfileEnable OUT VARCHAR2, autoImplEnable OUT VARCHAR2) IS CURSOR kCursor (tg IN VARCHAR2, in_target_type IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT NVL(data.value, k_metric_status_inaccessible) , NVL(data.string_value, ' ') , met.metric_column FROM mgmt_current_metrics data, mgmt_metrics met WHERE met.metric_name = 'key_profiles' AND met.target_type = in_target_type AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = data.metric_guid AND data.target_guid = CASE WHEN tg IN (SELECT member_target_guid FROM mgmt_target_memberships ms WHERE composite_target_type = 'rac_database') THEN (SELECT DISTINCT composite_target_guid FROM mgmt_target_memberships ms WHERE composite_target_type = 'rac_database' AND member_target_guid = HEXTORAW(tg)) ELSE HEXTORAW(tg) END; tempProfileCount NUMBER; tempProfileEnable VARCHAR2(32); tempAutoImplEnable VARCHAR2(32); tempMetricColumn VARCHAR2(64); begin keyProfileCount := k_metric_status_inaccessible; keyProfileEnable := ' '; autoImplEnable := ' '; OPEN kCursor (targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); LOOP FETCH kCursor INTO tempProfileCount, tempProfileEnable, tempMetricColumn; exit when kCursor%NOTFOUND; IF tempMetricColumn = 'key_profiles_count' THEN keyProfileCount := tempProfileCount; ELSIF tempMetricColumn = 'key_profiles_enable' THEN keyProfileEnable := tempProfileEnable; ELSIF tempMetricColumn = 'auto_impl_enable' THEN autoImplEnable := tempProfileEnable; END IF; END LOOP; CLOSE kCursor; end; /* getDbValues */ procedure getDbValues( targetGuid IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2, archAreaPctUsed OUT NUMBER, dumpAreaPctUsed OUT NUMBER) IS CURSOR aCursor (tg IN VARCHAR2, in_target_type IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT NVL(MAX(data.value), k_metric_status_inaccessible) FROM mgmt_current_metrics data, mgmt_metrics met WHERE data.target_guid = HEXTORAW(tg) AND (met.metric_name = 'dumpFull' AND met.metric_column = 'dumpUsedPercent') AND met.target_type = in_target_type AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = data.metric_guid; CURSOR aCursorArch (tg IN VARCHAR2, in_target_type IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT MAX(data.value) FROM mgmt_current_metrics data, mgmt_metrics met WHERE data.collection_timestamp = (SELECT MAX(data.collection_timestamp) FROM mgmt_current_metrics data, mgmt_metrics met WHERE data.target_guid = HEXTORAW(tg) AND met.target_type = in_target_type AND met.metric_name = 'archFull' AND met.metric_column = 'archUsedPercent' AND met.metric_guid = data.metric_guid AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') ) AND data.target_guid = HEXTORAW(tg) AND met.metric_name = 'archFull' AND met.metric_column = 'archUsedPercent' AND met.target_type = in_target_type AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = data.metric_guid; metric_name VARCHAR2(64); metric_column VARCHAR2(64); value NUMBER; begin archAreaPctUsed := k_metric_status_inaccessible; dumpAreaPctUsed := k_metric_status_inaccessible; OPEN aCursor (targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursor INTO dumpAreaPctUsed; CLOSE aCursor; OPEN aCursorArch (targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursorArch INTO archAreaPctUsed; CLOSE aCursorArch; end; FUNCTION extract_alertLog_timestamp(key_value IN VARCHAR2) RETURN DATE IS BEGIN RETURN TO_DATE(SUBSTR(key_value, 1, INSTR(key_value, '/') - 1), 'DY MON DD HH24:MI:SS YYYY'); EXCEPTION WHEN OTHERS THEN RETURN NULL; END; /* getDbAlertLogSeverity */ PROCEDURE getDbAlertLogSeverity( targetGuid IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2, alertLogTimestamp OUT DATE, alertLogSeverity OUT NUMBER) IS CURSOR worstCurrentAlertLogSeverity(tg IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT timestamp, NVL((SELECT MAX(sev.severity_code) FROM mgmt_current_severity sev WHERE sev.target_guid = dat.target_guid AND sev.metric_guid = dat.metric_guid AND sev.severity_code IN (15, 20, 25)), 15) severity FROM (SELECT MAX(extract_alertLog_timestamp(hist.key_value)) timestamp, hist.target_guid, hist.metric_guid FROM mgmt_metrics met, mgmt_string_metric_history hist WHERE met.target_type = targetType AND ((met.metric_name = 'alertLog' AND met.metric_column = 'genericErrStack') OR met.metric_name = 'adrAlertLogIncidentError') AND hist.metric_guid = met.metric_guid AND hist.target_guid = HEXTORAW(tg) AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') GROUP BY hist.target_guid, hist.metric_guid) dat; BEGIN alertLogSeverity := 15; OPEN worstCurrentAlertLogSeverity(targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH worstCurrentAlertLogSeverity INTO alertLogTimestamp, alertLogSeverity; CLOSE worstCurrentAlertLogSeverity; END; /* getDbCountValues */ procedure getDbCountValues( targetGuid IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2, sqlCount OUT NUMBER, archCount OUT NUMBER, badArchCount OUT NUMBER, topSqlCount OUT NUMBER, tbspCount OUT NUMBER) IS CURSOR aCursorVdtCurrentMetricsTbsp (tg IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT count(distinct(tbspname)) FROM (SELECT sev.key_value tbspname FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(tg) and (met.metric_name = 'problemTbsp' or met.metric_name = 'problemTbsp10iLoc' or met.metric_name = 'problemTbsp10iDct' or met.metric_name = 'problemSegTbsp') and met.target_type = targetType AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND (met.metric_column = 'pctUsed' or met.metric_column = 'bytesFree' or met.metric_column = 'segsChunkSmlCnt' or met.metric_column = 'segsMaxExtsCnt') and met.metric_guid = sev.metric_guid); CURSOR aCursorVdtCurrentMetricsArch (tg IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT count(distinct(data.key_value)) FROM mgmt_current_metrics data, mgmt_metrics met WHERE data.collection_timestamp = (SELECT MAX(data.collection_timestamp) FROM mgmt_current_metrics data, mgmt_metrics met WHERE data.target_guid = HEXTORAW(tg) AND met.metric_name = 'archFull' AND met.metric_column = 'archUsedPercent' AND met.metric_guid = data.metric_guid AND met.target_type = targetType AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') ) AND data.target_guid = HEXTORAW(tg) AND met.metric_name = 'archFull' AND met.metric_column = 'archUsedPercent' AND met.metric_guid = data.metric_guid; CURSOR aCursorCurrentMetricsBadArch (tg IN VARCHAR2, targetType IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2) IS SELECT count(distinct(arch_dest)) FROM (SELECT sev.key_value arch_dest FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(tg) and met.metric_name = 'archFull' and met.target_type = targetType AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_column = 'archUsedPercent' AND met.metric_guid = sev.metric_guid); CURSOR aCursorVdtSqlEvaluation (tg IN VARCHAR2) IS SELECT count(*) FROM (SELECT /*+ INDEX (d_e) */ 1 FROM (SELECT /*+ NO_MERGE INDEX(e) */ e.target_guid, max(e.eval_timestamp) eval_timestamp FROM mgmt_sql_evaluation e WHERE e.target_guid = HEXTORAW(tg) and e.eval_type = 1 GROUP BY e.target_guid) l_e, mgmt_sql_evaluation d_e WHERE l_e.target_guid = d_e.target_guid AND l_e.eval_timestamp = d_e.eval_timestamp AND d_e.severity >= 0 AND d_e.eval_type = 1 GROUP BY d_e.address, d_e.hash_value, d_e.plan_hash_value); CURSOR aCursorVdtSqlSnapshot (tg IN VARCHAR2) IS SELECT e.snap_id FROM mgmt_sql_evaluation e WHERE e.target_guid = HEXTORAW(tg) and e.eval_type = 1; CURSOR aCursorVdtSqlSummary (tg IN VARCHAR2, snap IN NUMBER) IS SELECT count(*) FROM mgmt_sql_summary ss WHERE ss.target_guid = HEXTORAW(tg) AND ss.piece = 0 AND ss.snap_id = snap; CURSOR aCursorVdtStatspackConfigured (tg IN VARCHAR2, pname IN VARCHAR2) IS SELECT property_value FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) and property_name = pname; value NUMBER; snap NUMBER; statspackInstalled NUMBER := 0; begin sqlCount := k_metric_status_inaccessible; archCount := k_metric_status_inaccessible; badArchCount := k_metric_status_inaccessible; tbspCount := k_metric_status_inaccessible; topSqlCount := k_metric_status_inaccessible; OPEN aCursorVdtCurrentMetricsTbsp (targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursorVdtCurrentMetricsTbsp INTO tbspCount; CLOSE aCursorVdtCurrentMetricsTbsp; OPEN aCursorVdtCurrentMetricsArch (targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursorVdtCurrentMetricsArch INTO archCount; CLOSE aCursorVdtCurrentMetricsArch; OPEN aCursorCurrentMetricsBadArch (targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursorCurrentMetricsBadArch INTO badArchCount; CLOSE aCursorCurrentMetricsBadArch; OPEN aCursorVdtStatspackConfigured (targetGuid, 'MCSpInst'); FETCH aCursorVdtStatspackConfigured INTO statspackInstalled; CLOSE aCursorVdtStatspackConfigured; IF statspackInstalled = 1 THEN OPEN aCursorVdtSqlEvaluation (targetGuid); FETCH aCursorVdtSqlEvaluation INTO sqlCount; CLOSE aCursorVdtSqlEvaluation; ELSIF statspackInstalled = 0 THEN sqlCount := k_metric_status_unconfigured; END IF; IF statspackInstalled = 1 THEN OPEN aCursorVdtSqlSnapshot (targetGuid); FETCH aCursorVdtSqlSnapshot INTO snap; CLOSE aCursorVdtSqlSnapshot; IF snap is not NULL THEN OPEN aCursorVdtSqlSummary (targetGuid, snap); FETCH aCursorVdtSqlSummary INTO topSqlCount; CLOSE aCursorVdtSqlSummary; END IF; ELSIF statspackInstalled = 0 THEN topSqlCount := k_metric_status_unconfigured; END IF; end; /* getDbSeveritys */ procedure getDbSeveritys( targetGuid IN VARCHAR2, target_type_in IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2, sqlSeverity OUT NUMBER, tbspSeverity OUT NUMBER, archAreaSeverity OUT NUMBER, dumpAreaSeverity OUT NUMBER, responseSeverity OUT NUMBER) IS CURSOR aCursorVdtCurrentSeverity (tg IN VARCHAR2, in_target_type IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2 ) IS SELECT /*+ INDEX(sev) */ met.metric_name, NVL(MAX(sev.severity_code),15) FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(tg) AND ((met.metric_name = 'archFull' AND met.metric_column = 'archUsedPercent') OR (met.metric_name = 'dumpFull' AND met.metric_column = 'dumpUsedPercent') OR (met.metric_name = k_sql_response_category AND met.metric_column = k_sql_response_metric)) AND met.target_type = in_target_type AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = sev.metric_guid GROUP BY met.metric_name; CURSOR aCursorVdtCurrentSeverityTbsp (tg IN VARCHAR2, in_target_type IN VARCHAR2, meta_ver IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2 ) IS SELECT NVL(MAX(severity_code),15) FROM (SELECT /*+ INDEX(sev) */ max(sev.severity_code) severity_code FROM mgmt_current_severity sev, mgmt_metrics met WHERE sev.target_guid = HEXTORAW(tg) and (met.metric_name = 'problemTbsp' or met.metric_name = 'problemTbsp10iLoc' or met.metric_name = 'problemTbsp10iDct' or met.metric_name = 'problemSegTbsp') and (met.metric_column = 'pctUsed' or met.metric_column = 'bytesFree' or met.metric_column = 'segsChunkSmlCnt' or met.metric_column = 'segsMaxExtsCnt') AND met.target_type = in_target_type AND met.type_meta_ver = meta_ver AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' ') AND met.metric_guid = sev.metric_guid ); CURSOR aCursorVdtSqlEvaluation (tg IN VARCHAR2) IS SELECT /*+ INDEX (d_e) */ NVL(MAX(d_e.severity),15) FROM (SELECT /*+ INDEX(e) */ e.target_guid, MAX(e.eval_timestamp) eval_timestamp FROM mgmt_sql_evaluation e WHERE e.target_guid = HEXTORAW(tg) and e.eval_type = 1 GROUP BY e.target_guid) l_e, mgmt_sql_evaluation d_e WHERE l_e.target_guid = d_e.target_guid AND d_e.eval_type = 1 AND l_e.eval_timestamp = d_e.eval_timestamp; metricName VARCHAR2(64); value NUMBER; wastedSpaceExists NUMBER; begin sqlSeverity := 15; tbspSeverity := 15; archAreaSeverity := 15; dumpAreaSeverity := 15; responseSeverity := 15; OPEN aCursorVdtCurrentSeverity (targetGuid, target_type_in, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); LOOP FETCH aCursorVdtCurrentSeverity INTO metricName, value; EXIT WHEN aCursorVdtCurrentSeverity%NOTFOUND; IF metricName = 'archFull' THEN archAreaSeverity := value; ELSIF metricName = 'dumpFull' THEN dumpAreaSeverity := value; ELSIF metricName = k_sql_response_category THEN responseSeverity := value; END IF; END LOOP; CLOSE aCursorVdtCurrentSeverity; OPEN aCursorVdtCurrentSeverityTbsp (targetGuid, target_type_in, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); FETCH aCursorVdtCurrentSeverityTbsp INTO tbspSeverity; CLOSE aCursorVdtCurrentSeverityTbsp; OPEN aCursorVdtSqlEvaluation (targetGuid); FETCH aCursorVdtSqlEvaluation INTO value; IF value < 0 THEN sqlSeverity := 15; ELSIF value >= 25 THEN sqlSeverity := 25; ELSIF value >= 20 THEN sqlSeverity := 20; END IF; CLOSE aCursorVdtSqlEvaluation; end; /* getHostName */ procedure getHostName( targetGuid IN VARCHAR2, hostName OUT VARCHAR2, hostGuid OUT VARCHAR2) IS /* Note: target_name and target_guid cannot be null */ CURSOR aCursor (tg IN VARCHAR2) IS SELECT target_name, target_guid FROM mgmt_targets WHERE target_type= 'host' and host_name = (SELECT host_name FROM mgmt_targets WHERE target_guid = HEXTORAW(tg)); begin hostName := ' '; hostGuid := ' '; OPEN aCursor (targetGuid); FETCH aCursor INTO hostName, hostGuid; CLOSE aCursor; end; procedure getListenerName( targetGuid IN VARCHAR2, hostName IN VARCHAR2, listenerName OUT VARCHAR2) IS CURSOR aCursor (tg IN VARCHAR2, hn IN VARCHAR2) IS SELECT m.target_name FROM mgmt_targets m WHERE m.target_type = 'oracle_listener' and m.host_name = (SELECT host_name FROM mgmt_targets WHERE target_guid=HEXTORAW(tg)) and m.target_guid in (SELECT target_guid FROM mgmt_target_properties WHERE property_name = 'Port' and property_value = (SELECT property_value FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) and property_name = 'Port')); begin listenerName := ' '; OPEN aCursor (targetGuid, hostName); FETCH aCursor INTO listenerName; CLOSE aCursor; end; procedure getMTTR( targetGuid IN VARCHAR2, MTTR OUT NUMBER) IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT estimated_mttr FROM mgmt_ha_mttr where target_guid = HEXTORAW(tg); CURSOR bCursor IS SELECT estimated_mttr FROM v$instance_recovery; begin MTTR := k_metric_status_inaccessible; -- DB Control - get values directly from the database IF emd_maintenance.is_central_mode() = 0 THEN OPEN bCursor; FETCH bCursor INTO MTTR; CLOSE bCursor; -- Grid Control - get values from the repository ELSE OPEN aCursor (targetGuid); FETCH aCursor INTO MTTR; CLOSE aCursor; END IF; end; procedure getAsmTargetName( targetName IN VARCHAR2, targetType IN VARCHAR2, hostName IN VARCHAR2, asmTargetName OUT VARCHAR2) IS CURSOR aCursor (tn IN VARCHAR2, tt IN VARCHAR2, hn IN VARCHAR2) IS SELECT target_name FROM mgmt_targets t, mgmt_target_properties p WHERE t.target_type = 'osm_instance' AND p.property_name = 'SID' AND t.target_guid = p.target_guid AND upper(p.property_value) = (SELECT upper(property_value) FROM mgmt$target_properties WHERE property_name = 'OSMInstance' AND target_name = tn AND target_type = tt AND property_value != ' ') AND t.host_name = hn; begin asmTargetName := NULL; OPEN aCursor (targetName, targetType, hostName); FETCH aCursor INTO asmTargetName; CLOSE aCursor; end; PROCEDURE getCellCount( targetName IN VARCHAR2, cellCount OUT VARCHAR2) IS status NUMBER; statusCount NUMBER; downCount NUMBER :=0; upCount NUMBER :=0; errorCount NUMBER := 0; agentDownCount NUMBER := 0; unreachableCount NUMBER := 0; blackOutCount NUMBER := 0; unknownCount NUMBER := 0; CURSOR aCursor IS SELECT a.current_status, COUNT(*) FROM mgmt_targets t, mgmt_current_availability a WHERE a.target_guid=t.target_guid AND t.target_type='oracle_cell' GROUP BY a.current_status; BEGIN OPEN aCursor; LOOP FETCH aCursor INTO status, statusCount; exit when aCursor%NOTFOUND; IF status = 0 THEN downCount := statusCount; ELSIF status =1 THEN upCount := statusCount; ELSIF status = 2 THEN errorCount := statusCount; ELSIF status = 3 THEN agentDownCount := statusCount; ELSIF status = 4 THEN unreachableCount := statusCount; ELSIF status = 5 THEN blackOutCount := statusCount; ELSIF status = 6 THEN unknownCount := statusCount; END IF; END LOOP; CLOSE aCursor; cellCount := downCount ||'|'|| upCount ||'|'|| errorCount ||'|'|| agentDownCount ||'|'|| unreachableCount ||'|'|| blackOutCount ||'|'|| unknownCount; END; PROCEDURE getSihaInfo( v_targetType IN VARCHAR2, v_targetGuid IN VARCHAR2, v_dbRegistered OUT VARCHAR2) IS CURSOR aCursor (v_metric_guid IN MGMT_METRICS.METRIC_GUID%TYPE) IS SELECT string_value FROM mgmt_current_metrics p WHERE p.metric_guid = v_metric_guid AND p.target_guid = HEXTORAW(v_targetGuid); l_metric_guid MGMT_METRICS.METRIC_GUID%TYPE; BEGIN v_dbRegistered := NULL; l_metric_guid := mgmt_metric.get_metric_guid(v_targetType,'isHasManaged','isHasManaged'); OPEN aCursor ( l_metric_guid ); FETCH aCursor INTO v_dbRegistered; CLOSE aCursor; END getSihaInfo; /* getStgPerfValues */ procedure getStgPerfValues( targetGuid IN VARCHAR2, target_type_in IN VARCHAR2, instanceVersion IN VARCHAR2, cat_prop_1 IN VARCHAR2, cat_prop_2 IN VARCHAR2, cat_prop_3 IN VARCHAR2, cat_prop_4 IN VARCHAR2, cat_prop_5 IN VARCHAR2, stgPerfCount OUT NUMBER, stgPerfSeverity OUT NUMBER) IS CURSOR aCursorVdtCurrentStgPerf (tg IN VARCHAR2) IS SELECT value1, collection_timestamp FROM mgmt_space_metrics WHERE object = 'wastedSpaceEOF' AND target_guid = HEXTORAW(tg) AND collection_timestamp = (SELECT MAX(collection_timestamp) FROM mgmt_space_metrics WHERE object = 'wastedSpaceEOF' AND target_guid = HEXTORAW(tg) AND metric_name='wastedSpace') AND metric_name='wastedSpace'; CURSOR aCursorStgPerfCnt (tg IN VARCHAR2, collTime IN DATE) IS SELECT count(*) - 1 FROM mgmt_space_metrics WHERE target_guid = HEXTORAW(tg) AND collection_timestamp = collTime AND metric_name='wastedSpace'; CURSOR aCursorSegAdvCnt (tg IN VARCHAR2, in_target_type IN VARCHAR2) IS SELECT value FROM mgmt_current_metrics data, mgmt_metrics met WHERE data.target_guid = HEXTORAW(tg) AND met.metric_name = 'segment_advisor_count' AND met.metric_column = 'recommendations' AND met.target_type = in_target_type AND met.metric_guid = data.metric_guid AND (met.category_prop_1 = cat_prop_1 OR met.category_prop_1 = ' ') AND (met.category_prop_2 = cat_prop_2 OR met.category_prop_2 = ' ') AND (met.category_prop_3 = cat_prop_3 OR met.category_prop_3 = ' ') AND (met.category_prop_4 = cat_prop_4 OR met.category_prop_4 = ' ') AND (met.category_prop_5 = cat_prop_5 OR met.category_prop_5 = ' '); configured VARCHAR2(100) := ''; collTime DATE; versionGT102 BOOLEAN := false; decimalPosition NUMBER; decimalPosition2 NUMBER; versionNum NUMBER; BEGIN stgPerfCount := 0; stgPerfSeverity := k_metric_status_inaccessible; decimalPosition := INSTR(instanceVersion, '.', 1, 1); IF (decimalPosition > 0) THEN versionNum := TO_NUMBER(SUBSTR(instanceVersion, 0, decimalPosition)); IF (versionNum > 10 ) THEN versionGT102 := true; ELSIF (versionNum < 10) THEN versionGT102 := false; ELSE decimalPosition2 := INSTR(instanceVersion, '.', 1, 2); versionNum := TO_NUMBER(SUBSTR(instanceVersion, decimalPosition + 1, decimalPosition2 - (decimalPosition + 1))); IF (versionNum >= 2) THEN versionGT102 := true; END IF; END IF; END IF; IF (versionGT102) THEN stgPerfCount := -1; OPEN aCursorSegAdvCnt (targetGuid, target_type_in); FETCH aCursorSegAdvCnt INTO stgPerfCount; CLOSE aCursorSegAdvCnt; IF (stgPerfCount > 0) THEN stgPerfSeverity := mgmt_global.g_severity_warning; ELSE stgPerfSeverity := mgmt_global.g_severity_clear; END IF; ELSE OPEN aCursorVdtCurrentStgPerf (targetGuid); FETCH aCursorVdtCurrentStgPerf INTO configured, colltime; CLOSE aCursorVdtCurrentStgPerf; IF (colltime IS NOT NULL) THEN OPEN aCursorStgPerfCnt (targetGuid, colltime); FETCH aCursorStgPerfCnt INTO stgPerfCount; CLOSE aCursorStgPerfCnt; END IF; IF (stgPerfCount < 0) THEN stgPerfCount := 0; END IF; IF (stgPerfCount = 0) THEN IF configured IS NOT NULL AND configured = 'true' THEN -- If we're checking something, then no problems were found stgPerfSeverity := mgmt_global.g_severity_clear; ELSE -- We return a -1 to indicate that nothing is being checked stgPerfCount := -1; END IF; ELSE -- problems exist. All of our problems are warnings. stgPerfSeverity := mgmt_global.g_severity_warning; END IF; END IF; END; /* getTargetGuid */ function getTargetGuid( targetName IN VARCHAR2, targetType IN VARCHAR2) return VARCHAR2 IS /* Note: target_guid cannot be null */ CURSOR aCursor (tn IN VARCHAR2, tt IN VARCHAR2) IS SELECT target_guid FROM mgmt_targets WHERE target_name = tn and target_type = tt; targetGuid VARCHAR2(32) := ' '; begin OPEN aCursor (targetName, targetType); FETCH aCursor INTO targetGuid; CLOSE aCursor; return targetGuid; end; /* getDBSiteMapInfo */ procedure getDBSiteMapInfo(targetName IN VARCHAR2, targetType IN VARCHAR2, dbSitemapInfo OUT SMP_EMD_DBSITEMAP_OBJ) IS k_error_msg_prefix CONSTANT VARCHAR2(128) := 'Oracle Enterprise Manager: ERROR in emd_database.getDBSiteMapInfo('; ---------------- -- variables ---------------- typeDisplayName VARCHAR2(256); lastUpdatedTime DATE; currentStatus INTEGER; availPct NUMBER; startTimestamp DATE; timeZone VARCHAR2(64); instanceName VARCHAR2(4000); instanceStartTime DATE; instanceVersion VARCHAR2(4000); oracleHome VARCHAR2(4000); cpuCount NUMBER; openMode VARCHAR2(64); sqlCount NUMBER; sqlSeverity NUMBER; tbspCount NUMBER; tbspSeverity NUMBER; archLogMode NUMBER; archAreaPctUsed NUMBER; archAreaSeverity NUMBER; dumpAreaPctUsed NUMBER; dumpAreaSeverity NUMBER; alertLogTimestamp DATE; alertLogSeverity NUMBER; hostName VARCHAR2(64); targetGuid VARCHAR2(32); hostGuid VARCHAR2(32); dupSqlCount NUMBER; responseSeverity NUMBER; topSqlCount NUMBER; stgPerfCount NUMBER; stgPerfSeverity NUMBER; archCount NUMBER; badArchCount NUMBER; listenerName VARCHAR2(4000); MTTR NUMBER; asmTargetName VARCHAR2(64); dbRegistered VARCHAR2(32); keyProfileCount NUMBER; keyProfileEnable VARCHAR(32); autoImplEnable VARCHAR(32); meta_ver VARCHAR2(8); cat_prop_1 VARCHAR2(64); cat_prop_2 VARCHAR2(64); cat_prop_3 VARCHAR2(64); cat_prop_4 VARCHAR2(64); cat_prop_5 VARCHAR2(64); BEGIN ---------------- -- target guid, type display name, last load time -- (this command represents the only really catastrophic error) ---------------- BEGIN SELECT target_guid, type_display_name, last_load_time, TYPE_META_VER, CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3, CATEGORY_PROP_4, CATEGORY_PROP_5, NVL(to_char(from_tz(CAST(last_load_time AS TIMESTAMP), timezone_region), 'TZD'), 'GMT ' || to_char(from_tz(CAST(last_load_time AS TIMESTAMP), timezone_region), 'TZR')) INTO targetGuid, typeDisplayName, lastUpdatedTime, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, timeZone FROM mgmt_targets WHERE target_name = targetName AND target_type = targetType; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE mgmt_global.target_does_not_exist; END; ---------------- -- get current status information ---------------- BEGIN currentStatus := mgmt_target.get_avail_current_status(targetGuid); startTimestamp := mgmt_target.get_current_status_timestamp(targetGuid); EXCEPTION WHEN OTHERS THEN currentStatus := mgmt_global.g_status_unknown; startTimestamp := NULL; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get current status.'); END; BEGIN ---------------- -- availability percentage ---------------- availPct := ROUND(mgmt_target.get_avail_pct_value(targetGuid, 1), 2); EXCEPTION WHEN OTHERS THEN availPct := k_metric_status_inaccessible; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get availability pct.'); END; ---------------- -- get archive log mode ---------------- BEGIN archLogMode := isArchiveLogMode(targetGuid, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5); EXCEPTION WHEN OTHERS THEN archLogMode := NULL; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get ARCHIVELOG mode.'); END; ---------------- -- get database meta-information ---------------- BEGIN getDbStringValues(targetGuid, instanceName, instanceStartTime, instanceVersion, oracleHome, cpuCount, openMode); EXCEPTION WHEN OTHERS THEN instanceName := NULL; instanceStartTime := NULL; instanceVersion := NULL; oracleHome := NULL; cpuCount := 1; openMode := NULL; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get instance attributes.'); END; ---------------- -- get important statitics ---------------- BEGIN getDbValues(targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, archAreaPctUsed, dumpAreaPctUsed); EXCEPTION WHEN OTHERS THEN archAreaPctUsed := k_metric_status_inaccessible; dumpAreaPctUsed := k_metric_status_inaccessible; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get critical statistics.'); END; ---------------- -- get Key Sql profiles ---------------- BEGIN getKeyProfile(targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, keyProfileCount, keyProfileEnable,autoImplEnable); EXCEPTION WHEN OTHERS THEN keyProfileCount := k_metric_status_inaccessible; keyProfileEnable := ' '; autoImplEnable := ' '; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get key sql profiles.'); END; ---------------- -- get alert log severity info ---------------- BEGIN getDbAlertLogSeverity(targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, alertLogTimestamp, alertLogSeverity); EXCEPTION WHEN OTHERS THEN alertLogTimestamp := NULL; alertLogSeverity := mgmt_global.g_severity_unknown; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get alert log summary.'); END; ---------------- -- get performance (storage-centric) severity info --------------- BEGIN getStgPerfValues(targetGuid, targetType, instanceVersion, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, stgPerfCount, stgPerfSeverity); EXCEPTION WHEN OTHERS THEN stgPerfCount := k_metric_status_inaccessible; stgPerfSeverity := mgmt_global.g_severity_unknown; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to storage (perf) summary.'); END; ---------------- -- get problem counts ---------------- BEGIN getDbCountValues(targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, sqlCount, archCount, badArchCount, topSqlCount, tbspCount); EXCEPTION WHEN OTHERS THEN sqlCount := k_metric_status_inaccessible; archCount := k_metric_status_inaccessible; badArchCount := k_metric_status_inaccessible; topSqlCount := k_metric_status_inaccessible; tbspCount := k_metric_status_inaccessible; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get problem counts.'); END; BEGIN getDbSeveritys(targetGuid, targetType, meta_ver, cat_prop_1, cat_prop_2, cat_prop_3, cat_prop_4, cat_prop_5, sqlSeverity, tbspSeverity, archAreaSeverity, dumpAreaSeverity, responseSeverity); EXCEPTION WHEN OTHERS THEN sqlSeverity := mgmt_global.g_severity_unknown; tbspSeverity := mgmt_global.g_severity_unknown; archAreaSeverity := mgmt_global.g_severity_unknown; dumpAreaSeverity := mgmt_global.g_severity_unknown; responseSeverity := mgmt_global.g_severity_unknown; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get problem severities.'); END; ---------------- -- get host information ---------------- BEGIN getHostName(targetGuid, hostName, hostGuid); EXCEPTION WHEN OTHERS THEN hostName := NULL; hostGuid := NULL; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get host name.'); END; ---------------- -- get duplicate SQL information ---------------- -- BEGIN -- getDupSqlCountValue(targetGuid, dupSqlCount); -- EXCEPTION -- WHEN OTHERS THEN -- dupSqlCount := k_metric_status_inaccessible; -- dbms_output.put_line(k_error_msg_prefix || targetName || -- ') -- unable to get duplicate SQL summary.'); -- END; -------------------- -- get listener name -------------------- BEGIN getListenerName(targetGuid, hostName, listenerName); EXCEPTION WHEN OTHERS THEN listenerName := NULL; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get listener name.'); END; --------------------- -- get MTTR --------------------- BEGIN getMTTR(targetGuid, MTTR); EXCEPTION WHEN OTHERS THEN MTTR := k_metric_status_inaccessible; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get MTTR.'); END; ----------------------- -- get ASM target name ----------------------- BEGIN getAsmTargetName(targetName, targetType, hostName, asmTargetName); EXCEPTION WHEN OTHERS THEN asmTargetName := NULL; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get asm target name.'); END; BEGIN getSihaInfo(targetType, targetGuid, dbRegistered); EXCEPTION WHEN OTHERS THEN dbRegistered := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get siha information.'); END; ---------------- -- return information ---------------- dbSitemapInfo := SMP_EMD_DBSITEMAP_OBJ(typeDisplayName, lastUpdatedTime, currentStatus, availPct, startTimestamp, timeZone, instanceName, instanceStartTime, instanceVersion, oracleHome, cpuCount, openMode, sqlCount, sqlSeverity, tbspCount, tbspSeverity, archLogMode, archAreaPctUsed, archAreaSeverity, dumpAreaPctUsed, dumpAreaSeverity, alertLogTimestamp, alertLogSeverity, hostName, targetGuid, dupSqlCount, responseSeverity, topSqlCount, stgPerfCount, stgPerfSeverity, archCount, badArchCount, listenerName, MTTR, asmTargetName, dbRegistered, keyProfileCount, keyProfileEnable, autoImplEnable); END; -- -- Returns the database size in MB. -- The database size is the sum of the size of the tablespaces. -- procedure getDbSize( targetGuid IN VARCHAR2, dbSize OUT NUMBER) IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT nvl(sum(tablespace_size)/1024/1024,0) FROM mgmt$db_tablespaces WHERE target_guid = HEXTORAW(tg); begin dbSize := 0; OPEN aCursor (targetGuid); FETCH aCursor INTO dbSize; CLOSE aCursor; end; procedure getPrmyDetails( targetGuid IN VARCHAR2, prmyEMName OUT VARCHAR2, prmyType OUT VARCHAR2) IS dgPrmyDBName VARCHAR2(64); CURSOR aCursor (tg IN VARCHAR2) IS SELECT value FROM mgmt$metric_current WHERE target_guid = HEXTORAW(tg) and metric_name = 'DGPrimaryDBName' and metric_column='db_name'; CURSOR a1Cursor IS select case when target_guid IN (select member_target_guid from mgmt_target_memberships ms where composite_target_type = 'rac_database') then (select composite_target_name from mgmt_target_memberships where composite_target_type = 'rac_database' and member_target_guid = (select target_guid from mgmt_target_properties where property_name = 'DBName' and property_value = dgPrmyDBName and rownum = 1)) else target_name end from mgmt_targets where target_guid = (select target_guid from mgmt_target_properties where property_name = 'DBName' and property_value = dgPrmyDBName and rownum = 1); CURSOR a2Cursor IS SELECT target_type FROM mgmt_targets WHERE target_name = prmyEMName; begin dgPrmyDBName := NULL; prmyEMName := NULL; prmyType := NULL; OPEN aCursor (targetGuid); FETCH aCursor INTO dgPrmyDBName; CLOSE aCursor; OPEN a1Cursor; FETCH a1Cursor INTO prmyEMName; CLOSE a1Cursor; OPEN a2Cursor; FETCH a2Cursor INTO prmyType; CLOSE a2Cursor; end; procedure getDGRoleFromDb( dgRole OUT VARCHAR2) IS CURSOR aCursor IS select case when (((select count(*) from v$archive_dest where target='STANDBY') > 0) OR (database_role like '%STANDBY')) then initcap(database_role) else ' ' end from v$database; begin dgRole := NULL; OPEN aCursor; FETCH aCursor INTO dgRole; CLOSE aCursor; end; procedure getDGRole( targetGuid IN VARCHAR2, dgRole OUT VARCHAR2) IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT property_value FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) and property_name = 'DataGuardStatus'; begin dgRole := NULL; OPEN aCursor (targetGuid); FETCH aCursor INTO dgRole; CLOSE aCursor; end; procedure getBackupValues( targetGuid IN VARCHAR2, backupStatus OUT VARCHAR2, backupTime OUT DATE) IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT status, end_time FROM mgmt_ha_backup WHERE target_guid = HEXTORAW(tg); begin backupStatus := NULL; BEGIN -- DB Control - get values directly from the database IF emd_maintenance.is_central_mode() = 0 THEN -- Last Backup Time and Status (10.2 view v$rman_backup_job_details) EXECUTE IMMEDIATE 'SELECT end_time, status FROM v$rman_backup_job_details ' || 'WHERE end_time = (select max(end_time) from v$rman_backup_job_details)' INTO backupTime, backupStatus; -- Grid Control - get values from the repository ELSE OPEN aCursor (targetGuid); FETCH aCursor INTO backupStatus, backupTime; CLOSE aCursor; END IF; EXCEPTION WHEN OTHERS THEN backupStatus := NULL; backupTime := NULL; END; end; procedure getFlashRecoveryValuesFromDb( timeFormat IN VARCHAR2, flashbackTime OUT DATE, recoveryPct OUT NUMBER, flashRecoveryArea OUT VARCHAR2) IS CURSOR bCursor IS SELECT value FROM v$parameter WHERE name='db_recovery_file_dest'; begin flashbackTime := NULL; recoveryPct := k_metric_status_inaccessible; flashRecoveryArea := NULL; -- Oldest Flashback Time (10.1 view v$flashback_database_log) BEGIN EXECUTE IMMEDIATE 'SELECT oldest_flashback_time FROM v$flashback_database_log' INTO flashbackTime; EXCEPTION WHEN OTHERS THEN flashbackTime := NULL; END; -- Flash Recovery Area OPEN bCursor; FETCH bCursor INTO flashRecoveryArea; CLOSE bCursor; -- Usable Flash Recovery Area (10.2 view v$flash_recovery_area_usage) BEGIN IF (length(flashRecoveryArea) > 0) THEN EXECUTE IMMEDIATE 'SELECT (CASE WHEN PERCENT_USED> 100 THEN 0 ELSE (100-PERCENT_USED) END) PERCENT_FREE FROM (SELECT (SUM(PERCENT_SPACE_USED)-SUM(PERCENT_SPACE_RECLAIMABLE)) PERCENT_USED FROM V$FLASH_RECOVERY_AREA_USAGE)' INTO recoveryPct; END IF; EXCEPTION WHEN OTHERS THEN recoveryPct := k_metric_status_inaccessible; END; end; procedure getFlashRecoveryValuesFromRep( targetName IN VARCHAR2, targetType IN VARCHAR2, timeFormat IN VARCHAR2, flashbackTime OUT DATE, recoveryPct OUT NUMBER, flashRecoveryArea OUT VARCHAR2) IS CURSOR aCursor (tn IN VARCHAR2, tt IN VARCHAR2) IS SELECT metric_column, value FROM mgmt$metric_current WHERE target_name = tn AND target_type = tt AND ((metric_name = 'ha_flashrecovery' AND metric_column = 'usable_area') OR (metric_name = 'ha_flashrecovery' AND metric_column = 'oldest_flashback_time') OR (metric_name = 'ha_flashrecovery' AND metric_column = 'flash_recovery_area')); CURSOR bCursor (tn IN VARCHAR2, tt IN VARCHAR2) IS SELECT composite_target_name, composite_target_type FROM mgmt_target_memberships WHERE member_target_name = tn AND member_target_type = tt AND composite_target_type = 'rac_database'; metric_column VARCHAR2(64); value VARCHAR2(128); l_targetName VARCHAR2(64) := targetName; l_targetType VARCHAR2(64) := targetType; begin flashbackTime := NULL; recoveryPct := k_metric_status_inaccessible; flashRecoveryArea := NULL; -- Data for ha_flashrecovery is not collected at the rac-instance level. -- Use rac database target name and type instead. OPEN bCursor (l_targetName, l_targetType); FETCH bCursor INTO l_targetName, l_targetType; CLOSE bCursor; OPEN aCursor (l_targetName, l_targetType); LOOP FETCH aCursor INTO metric_column, value; EXIT WHEN aCursor%NOTFOUND; IF metric_column = 'usable_area' THEN recoveryPct := value; ELSIF metric_column = 'oldest_flashback_time' THEN flashbackTime := to_date(value, timeFormat); ELSIF metric_column = 'flash_recovery_area' THEN flashRecoveryArea := value; END IF; END LOOP; CLOSE aCursor; end; procedure getLogModeAndFlashbackFromDb( targetGuid IN VARCHAR2, logMode OUT VARCHAR2, flashbackOn OUT VARCHAR2) IS begin logMode := NULL; flashbackOn := NULL; -- Log Mode and Flashback On (10.1 column flashback_on) EXECUTE IMMEDIATE 'SELECT log_mode, flashback_on FROM v$database' INTO logMode, flashbackOn; end; procedure getLogModeAndFlashbackFromRep( targetGuid IN VARCHAR2, targetName IN VARCHAR2, targetType IN VARCHAR2, instanceVersion IN VARCHAR2, logMode OUT VARCHAR2, flashbackOn OUT VARCHAR2) IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT log_mode, flashback_on FROM MGMT$HA_INFO WHERE target_guid = HEXTORAW(tg); CURSOR bCursor (tn IN VARCHAR2, tt IN VARCHAR2) IS SELECT composite_target_name, composite_target_type FROM mgmt_target_memberships WHERE member_target_name = tn AND member_target_type = tt AND composite_target_type = 'rac_database'; CURSOR cCursor (tn IN VARCHAR2, tt IN VARCHAR2) IS SELECT metric_column, value FROM mgmt$metric_current WHERE target_name = tn AND target_type = tt AND ((metric_name = 'ha_flashrecovery' AND metric_column = 'log_mode') OR (metric_name = 'ha_flashrecovery' AND metric_column = 'flashback_on')); l_targetName VARCHAR2(64) := targetName; l_targetType VARCHAR2(64) := targetType; metric_column VARCHAR2(64); value VARCHAR2(128); versionGT9i BOOLEAN := false; decimalPosition NUMBER; versionNum NUMBER; begin logMode := NULL; flashbackOn := NULL; decimalPosition := INSTR(instanceVersion, '.', 1, 1); IF (decimalPosition > 0) THEN versionNum := TO_NUMBER(SUBSTR(instanceVersion, 0, decimalPosition)); IF (versionNum > 9 ) THEN versionGT9i := true; END IF; END IF; IF (versionGT9i) THEN -- Data for ha_flashrecovery is not collected at the rac-instance level. -- Use rac database target name and type instead. OPEN bCursor (l_targetName, l_targetType); FETCH bCursor INTO l_targetName, l_targetType; CLOSE bCursor; OPEN cCursor (l_targetName, l_targetType); LOOP FETCH cCursor INTO metric_column, value; EXIT WHEN cCursor%NOTFOUND; IF metric_column = 'log_mode' THEN logMode := value; ELSIF metric_column = 'flashback_on' THEN flashbackOn := value; END IF; END LOOP; CLOSE cCursor; ELSE OPEN aCursor (targetGuid); FETCH aCursor INTO logMode, flashbackOn; CLOSE aCursor; END IF; end; function getTimeFormat(targetGuid IN VARCHAR2) return VARCHAR2 IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT NVL(property_value, ' ') FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) AND property_type IN ('INSTANCE', 'DYNAMICINSTANCE') AND property_name = 'TimeFormat'; timeFormat VARCHAR2(64) := 'YYYY-MM-DD HH24:MI:SS'; begin OPEN aCursor (targetGuid); FETCH aCursor INTO timeFormat; CLOSE aCursor; return timeFormat; end; function getDbInstanceVersion(targetGuid IN VARCHAR2) return VARCHAR2 IS CURSOR aCursor (tg IN VARCHAR2) IS SELECT NVL(property_value, ' ') FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) AND property_type IN ('INSTANCE', 'DYNAMICINSTANCE') AND property_name = 'Version'; instanceVersion VARCHAR2(4000) := ' '; begin OPEN aCursor (targetGuid); FETCH aCursor INTO instanceVersion; CLOSE aCursor; return instanceVersion; end; procedure getDBSiteMapCommonInfo(targetName IN VARCHAR2, targetType IN VARCHAR2, dbSitemapInfo OUT SMP_EMD_DBSITEMAP_COMMON_OBJ) IS k_error_msg_prefix CONSTANT VARCHAR2(128) := 'Oracle Enterprise Manager: ERROR in emd_database.getDBSiteMapCommonInfo('; ---------------- -- variables ---------------- timeFormat VARCHAR2(64); targetGuid VARCHAR2(32); dbSize NUMBER; dgRole VARCHAR2(64); prmyEMName VARCHAR2(64); prmyType VARCHAR2(64); backupStatus VARCHAR2(64); backupTime DATE; flashbackTime DATE; recoveryPct NUMBER; flashRecoveryArea VARCHAR2(512); logMode VARCHAR2(32); flashbackOn VARCHAR2(32); cellCount VARCHAR2(512); -- Used locally. instanceVersion VARCHAR2(4000); BEGIN BEGIN targetGuid := getTargetGuid(targetName, targetType); EXCEPTION WHEN OTHERS THEN targetGuid := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get target guid.'); END; BEGIN timeFormat := getTimeFormat(targetGuid); EXCEPTION WHEN OTHERS THEN timeFormat := 'YYYY-MM-DD HH24:MI:SS'; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get timeformat.'); END; BEGIN getDbSize(targetGuid, dbSize); EXCEPTION WHEN OTHERS THEN dbSize := 0; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get db size.'); END; BEGIN -- For DBControl, get the role from the database (not the repos). IF emd_maintenance.is_central_mode() = 0 THEN getDGRoleFromDb(dgRole); END IF; EXCEPTION WHEN OTHERS THEN dgRole := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get dataguard role from database.'); END; BEGIN IF dgRole IS NULL THEN getDGRole(targetGuid, dgRole); END IF; EXCEPTION WHEN OTHERS THEN dgRole := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get dataguard role.'); END; BEGIN prmyEMName := NULL; prmyType := NULL; IF dgRole IS NOT NULL THEN getPrmyDetails(targetGuid, prmyEMName, prmyType); END IF; EXCEPTION WHEN OTHERS THEN prmyEMName := NULL; prmyType := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get information related to primary.'); END; BEGIN getBackupValues(targetGuid, backupStatus, backupTime); EXCEPTION WHEN OTHERS THEN backupStatus := NULL; backupTime := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get backup information.'); END; BEGIN IF emd_maintenance.is_central_mode() = 0 THEN getFlashRecoveryValuesFromDb(timeFormat, flashbackTime, recoveryPct, flashRecoveryArea); ELSE getFlashRecoveryValuesFromRep(targetName, targetType, timeFormat, flashbackTime, recoveryPct, flashRecoveryArea); END IF; EXCEPTION WHEN OTHERS THEN flashbackTime := NULL; recoveryPct := k_metric_status_inaccessible; flashRecoveryArea := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get flash recovery values.'); END; BEGIN instanceVersion := getDbInstanceVersion(targetGuid); EXCEPTION WHEN OTHERS THEN instanceVersion := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get instance version.'); END; BEGIN IF emd_maintenance.is_central_mode() = 0 THEN getLogModeAndFlashbackFromDb(targetGuid, logMode, flashbackOn); ELSE getLogModeAndFlashbackFromRep(targetGuid, targetName, targetType, instanceVersion, logMode, flashbackOn); END IF; EXCEPTION WHEN OTHERS THEN logMode := NULL; flashbackOn := NULL; dbms_output.put_line(k_error_msg_prefix || targetGuid || ') -- unable to get log mode and flashback.'); END; -------------------- -- get cell count -------------------- BEGIN getCellCount(targetName, cellCount); EXCEPTION WHEN OTHERS THEN cellCount := '0|0|0|0|0|0|0'; dbms_output.put_line(k_error_msg_prefix || targetName || ') -- unable to get cell count information.'); END; ---------------- -- return information ---------------- dbSitemapInfo := SMP_EMD_DBSITEMAP_COMMON_OBJ(targetGuid, dbSize, dgRole, prmyEMName, prmyType, backupStatus, backupTime, flashbackTime, recoveryPct, flashRecoveryArea, logMode, flashbackOn, cellCount); END; /* || getDbCountValues -- Get the number of duplicate SQL statements */ procedure getDupSqlCountValue( targetGuid IN VARCHAR2, dupSqlCount OUT NUMBER) IS maxColTime VARCHAR2(100); CURSOR aCursorDupSqlCount (latestColTime VARCHAR2) IS SELECT COUNT(*) FROM mgmt_sql_reuse WHERE target_guid = HEXTORAW(targetGuid) AND to_char(collection_timestamp, 'YYYY-MON-DD HH24:MI:SS') = latestColTime; CURSOR aCursorVdtMgmtReuseInstalled (tg IN VARCHAR2, pname IN VARCHAR2) IS SELECT property_value FROM mgmt_target_properties WHERE target_guid = HEXTORAW(tg) and property_name = pname; v_mgmt_reuse_installed NUMBER := 0; begin dupSqlCount := 0; OPEN aCursorVdtMgmtReuseInstalled (targetGuid, 'MCReuse'); FETCH aCursorVdtMgmtReuseInstalled INTO v_mgmt_reuse_installed; CLOSE aCursorVdtMgmtReuseInstalled; IF v_mgmt_reuse_installed = 1 THEN SELECT max(to_char(e.collection_timestamp, 'YYYY-MON-DD HH24:MI:SS')) into maxColTime FROM mgmt_sql_evaluation e WHERE e.target_guid = HEXTORAW(targetGuid) AND e.reason_code = 2 AND e.eval_type =2; --dbms_output.put_line('Latest collection time: ' || maxColTime); OPEN aCursorDupSqlCount (maxColTime); FETCH aCursorDupSqlCount INTO dupSqlCount; CLOSE aCursorDupSqlCount; -- dbms_output.put_line('Duplicate SQL count: ' || dupSqlCount); ELSIF v_mgmt_reuse_installed = 0 THEN dupSqlCount := k_metric_status_unconfigured; END IF; exception when others then dupSqlCount := 0; end getDupSqlCountValue; /* * to_tz - translate a date from one timezone to another * * p_source_date - the original date * p_source_tz_delta - the timezone delta for the region of the original * date * p_destination_tz_delta - the timezone delta for the region of the final * date * * returns the date relative to the destination region */ FUNCTION to_tz(p_source_date IN DATE, p_source_tz_delta IN NUMBER, p_destination_tz_delta IN NUMBER) RETURN DATE IS BEGIN RETURN p_source_date + (p_destination_tz_delta - p_source_tz_delta)/1440; END to_tz; /* * get_top_wait_instances - return the wait data from instances of the named * oracle_database_group with the largest variance * on user_wait_pct * * p_group_name - the name of the oracle_database_group * p_top_count - the number of instances' worth of data to return * p_full_interval - the full display interval (in days) * p_plot_interval - the desired interval between datapoints (in minutes) * p_wait_breakdown - a cursor with the data (ordered by target, time) * returning: * target_name, * collection_timestamp, * user_wait_time, * user_cpu_time * p_timezone_delta - the client's locale, expressed in minutes off GMT * p_end_time - the end time, in the repository's time zone */ PROCEDURE get_top_wait_instances(p_group_name IN VARCHAR2, p_top_count IN NUMBER, p_full_interval IN NUMBER, p_plot_interval IN NUMBER, p_wait_breakdown OUT cursorType, p_timezone_delta IN NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60, p_end_time IN DATE DEFAULT SYSDATE) IS -- NOW k_now CONSTANT DATE := to_tz(p_end_time, TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60, p_timezone_delta); -- actual plot interval used l_plot_interval NUMBER; -- one-time computation of interesting GUIDs l_user_wait_pct_guid mgmt_metrics.metric_guid%TYPE; -- target data (return and temporary) l_target_data mgmt_db_svctime_array := mgmt_db_svctime_array(); l_single_target_data mgmt_db_svctime_array := mgmt_db_svctime_array(); -- -- Copy a single-target set of accumulators into a list maintained for all -- targets. -- PROCEDURE append_accumulators IS BEGIN -- -- Copy the counters into the accumulator array. -- FOR l_i IN 1..l_single_target_data.COUNT LOOP l_target_data.EXTEND; l_target_data(l_target_data.COUNT) := l_single_target_data(l_i); END LOOP; -- -- Clean out the temporary data store. -- l_single_target_data.DELETE; END append_accumulators; BEGIN -- -- Get the metric metadata (GUID) for the wait metric. -- SELECT metric_guid INTO l_user_wait_pct_guid FROM mgmt_metrics WHERE target_type = mgmt_global.g_database_target_type AND metric_name = 'wait_bottlenecks' AND metric_column = 'user_wait_time_pct' AND ROWNUM = 1; -- -- Iterate over the top N instances in the group (capturing the bounding -- collection timestamps for the interval of each instance). -- Top is measured by the greatest variance of the metric -- 'wait_bottlenecks'.'user_wait_time_pct'. -- IF (p_end_time - p_full_interval >= SYSDATE - 25/24) THEN -- -- Note: this code path has minute granularity. -- l_plot_interval := ROUND(GREATEST(p_plot_interval, 1)); -- -- The start time is less than 25 hours ago (one hour past the window main- -- tained for raw data) so sample from mgmt_metrics_raw. -- FOR l_target_times IN (SELECT target_guid, timezone_delta, min_collection_timestamp, max_collection_timestamp FROM (SELECT target_guid, timezone_delta, min_collection_timestamp, max_collection_timestamp FROM (SELECT g.member_target_guid target_guid, t.timezone_delta, MIN(d.value) min_wait_pct, MAX(d.value) max_wait_pct, MIN(d.collection_timestamp) min_collection_timestamp, MAX(d.collection_timestamp) max_collection_timestamp FROM mgmt_target_memberships g, mgmt_targets t, mgmt_metrics_raw d WHERE g.composite_target_name = p_group_name AND g.composite_target_type = mgmt_global.g_database_group_target_type AND t.target_guid = g.member_target_guid AND d.target_guid = t.target_guid AND d.metric_guid = l_user_wait_pct_guid AND d.collection_timestamp >= to_tz(k_now, p_timezone_delta, t.timezone_delta) - p_full_interval GROUP BY g.member_target_guid, t.timezone_delta) WHERE min_collection_timestamp <> max_collection_timestamp ORDER BY max_wait_pct - min_wait_pct DESC) WHERE ROWNUM < p_top_count + 1) LOOP -- -- For each instance, capture the wait percentage as seen in the -- interval. Drop datapoints according to the plot interval -- (but always keep the earliest). -- SELECT mgmt_db_svctime_obj((SELECT t.target_name FROM mgmt_targets t WHERE t.target_guid = d.target_guid), to_tz(d.collection_timestamp, l_target_times.timezone_delta, p_timezone_delta), d.value) BULK COLLECT INTO l_single_target_data FROM mgmt_metrics_raw d WHERE d.target_guid = l_target_times.target_guid AND d.metric_guid = l_user_wait_pct_guid AND d.collection_timestamp >= l_target_times.min_collection_timestamp AND d.collection_timestamp <= l_target_times.max_collection_timestamp AND (MOD(24*60*(TRUNC(d.collection_timestamp, 'MI') - TRUNC(d.collection_timestamp)), l_plot_interval) = MOD(24*60*(TRUNC(l_target_times.max_collection_timestamp, 'MI') - TRUNC(l_target_times.max_collection_timestamp)), l_plot_interval) OR d.collection_timestamp = l_target_times.min_collection_timestamp); -- -- Push this target's accumulators into the set for all targets. -- append_accumulators; END LOOP; ELSE -- -- Note: this code path has hour granularity -- l_plot_interval := 60*ROUND(GREATEST(p_plot_interval, 60)/60); -- -- The start time is at least 25 hours ago (one hour past the window main- -- tained for raw data) so look at hourly rollups. -- FOR l_target_times IN (SELECT target_guid, timezone_delta, min_rollup_timestamp, max_rollup_timestamp FROM (SELECT target_guid, timezone_delta, min_rollup_timestamp, max_rollup_timestamp FROM (SELECT g.member_target_guid target_guid, t.timezone_delta, MIN(d.value_minimum) min_wait_pct, MAX(d.value_maximum) max_wait_pct, MIN(d.rollup_timestamp) min_rollup_timestamp, MAX(d.rollup_timestamp) max_rollup_timestamp FROM mgmt_target_memberships g, mgmt_targets t, mgmt_metrics_1hour d WHERE g.composite_target_name = p_group_name AND g.composite_target_type = mgmt_global.g_database_group_target_type AND t.target_guid = g.member_target_guid AND d.target_guid = t.target_guid AND d.metric_guid = l_user_wait_pct_guid AND d.rollup_timestamp >= p_end_time - p_full_interval GROUP BY g.member_target_guid, t.timezone_delta) WHERE min_rollup_timestamp <> max_rollup_timestamp ORDER BY max_wait_pct - min_wait_pct DESC) WHERE ROWNUM < p_top_count + 1) LOOP -- -- For each instance, capture the base counters for wait and cpu time -- seen in the interval. Drop datapoints according to the plot interval -- (but always keep the earliest). -- SELECT mgmt_db_svctime_obj((SELECT t.target_name FROM mgmt_targets t WHERE t.target_guid = c.target_guid), to_tz(c.plot_timestamp, TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60, p_timezone_delta), c.plot_value) BULK COLLECT INTO l_single_target_data FROM (SELECT d.target_guid, d.rollup_timestamp plot_timestamp, d.value_average plot_value FROM mgmt_metrics_1hour d WHERE d.target_guid = l_target_times.target_guid AND d.metric_guid = l_user_wait_pct_guid AND d.rollup_timestamp >= l_target_times.min_rollup_timestamp AND d.rollup_timestamp <= l_target_times.max_rollup_timestamp AND (MOD(24*60*(TRUNC(d.rollup_timestamp, 'HH24') - TRUNC(d.rollup_timestamp)), l_plot_interval) = MOD(24*60*(TRUNC(l_target_times.max_rollup_timestamp, 'HH24') - TRUNC(l_target_times.max_rollup_timestamp)), l_plot_interval) OR d.rollup_timestamp = l_target_times.min_rollup_timestamp) UNION ALL SELECT d.target_guid, d.collection_timestamp plot_timestamp, d.value plot_value FROM mgmt_metrics_raw d WHERE d.target_guid = l_target_times.target_guid AND d.metric_guid = l_user_wait_pct_guid AND d.collection_timestamp >= l_target_times.max_rollup_timestamp AND MOD(24*60*(TRUNC(d.collection_timestamp, 'MI') - TRUNC(d.collection_timestamp)), 1) = MOD(24*60*(TRUNC(l_target_times.max_rollup_timestamp, 'MI') - TRUNC(l_target_times.max_rollup_timestamp)), 1)) c; -- -- Push this target's accumulators into the set for all targets. -- append_accumulators; END LOOP; END IF; -- -- Return the accumulated data in a cursor. -- OPEN p_wait_breakdown FOR SELECT target_name, collection_timestamp, user_wait_time_pct FROM TABLE(CAST(l_target_data AS mgmt_db_svctime_array)); END get_top_wait_instances; /* * get_metric_state_at - Get the state of all metrics of a particular target * at a point in time. * * p_target_guid - The target's identifier * p_point_in_time - The instant at which the state summary is needed * (relative to target's locale) * * Returns - cursor containing * * mgmt_severity.metric_guid, * mgmt_severity.key_value * mgmt_severity.severity_code, * p_point_in_time */ FUNCTION get_metric_state_at(p_target_guid IN RAW, p_point_in_time IN DATE) RETURN cursorType IS l_cursor cursorType; BEGIN -- -- Get severity state for all metrics of a target at this point in time. -- OPEN l_cursor FOR SELECT codes.metric_guid metric_guid, codes.key_value key_value, MAX(codes.severity_code) severity_code, p_point_in_time collection_timestamp FROM (SELECT target_guid, metric_guid, key_value, MAX(collection_timestamp) collection_timestamp FROM mgmt_severity WHERE target_guid = p_target_guid AND collection_timestamp <= p_point_in_time GROUP BY target_guid, metric_guid, key_value) times, mgmt_severity codes WHERE times.target_guid = codes.target_guid AND times.metric_guid = codes.metric_guid AND times.key_value = codes.key_value AND times.collection_timestamp = codes.collection_timestamp GROUP BY codes.metric_guid, codes.key_value; RETURN l_cursor; END get_metric_state_at; /* * get_top_alert_instances - return the alert profile from instances of the * named oracle_database_group with the priority * defined by: * 1) currently down * 2) most time down * 3) most time in critical * 4) most time in warning * 5) most occurrences of critical * 6) most occurrences of warning * * p_group_name - the name of the oracle_database_group * p_top_count - the number of instances' worth of data to return * p_full_interval - the full display interval (in days) * p_alert_breakdown - a cursor with the data (ordered by target, time) * returning: * target_name, * collection_timestamp, * alert_status * p_timezone_delta - the client's locale, expressed in minutes off GMT * p_end_time - the end time, in the repository's time zone */ PROCEDURE get_top_alert_instances(p_group_name IN VARCHAR2, p_top_count IN NUMBER, p_full_interval IN NUMBER, p_alert_breakdown OUT cursorType, p_timezone_delta IN NUMBER DEFAULT TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60, p_end_time IN DATE DEFAULT SYSDATE) IS -- NOW k_now CONSTANT DATE := to_tz(p_end_time, TO_NUMBER(TO_CHAR(SYSTIMESTAMP, 'TZH')) * 60, p_timezone_delta); -- availability guid l_updown_guid mgmt_metrics.metric_guid%TYPE; -- data structure for state details (per alert) TYPE state_detail_type IS RECORD (collection_timestamp mgmt_severity.collection_timestamp%TYPE, metric_guid mgmt_severity.metric_guid%TYPE, key_value mgmt_severity.key_value%TYPE, severity_code mgmt_severity.severity_code%TYPE, state VARCHAR2(16)); TYPE state_detail_array_type IS TABLE OF state_detail_type; -- data structure for target state summarization (per target) TYPE state_summary_type IS RECORD (current_status NUMBER, time_down NUMBER, time_in_critical NUMBER(30,5), time_in_warning NUMBER(30,5), critical_occurrences NUMBER(25), warning_occurrences NUMBER(25), target_name mgmt_targets.target_name%TYPE, init_detail_count NUMBER, state_details state_detail_array_type); TYPE state_summary_array_type IS TABLE OF state_summary_type; -- cursor for targets in this oracle_database_group CURSOR c_targets_of_group IS SELECT g.member_target_guid, g.member_target_name, t.timezone_delta FROM mgmt_target_memberships g, mgmt_targets t WHERE g.composite_target_name = p_group_name AND g.composite_target_type = mgmt_global.g_database_group_target_type AND t.target_guid = g.member_target_guid; -- current target l_target_guid mgmt_target_memberships.member_target_guid%TYPE; -- current target timezone delta (in minutes) l_target_timezone_delta NUMBER; -- summarization for current target l_current_summary state_summary_type; -- cursor for state changes (alerts + availability) for a target CURSOR c_state_changes_of_target(p_target_guid RAW, p_target_timezone_delta NUMBER) IS SELECT to_tz(collection_timestamp, p_target_timezone_delta, p_timezone_delta) collection_timestamp, CASE WHEN metric_guid = l_updown_guid THEN DECODE(severity_code, mgmt_global.g_severity_critical, g_state_down, g_state_up) ELSE DECODE(severity_code, mgmt_global.g_severity_critical, g_state_critical, mgmt_global.g_severity_warning, g_state_warning, mgmt_global.g_severity_blackout_start, g_state_blackout, mgmt_global.g_severity_blackout_end, g_state_unblackout, mgmt_global.g_severity_clear, g_state_clear) END, severity_code, metric_guid, key_value FROM (SELECT s.collection_timestamp, MAX(s.severity_code) severity_code, s.metric_guid, s.key_value FROM mgmt_severity s WHERE s.target_guid = p_target_guid AND s.collection_timestamp BETWEEN to_tz(k_now, p_timezone_delta, p_target_timezone_delta) - p_full_interval AND to_tz(k_now, p_timezone_delta, p_target_timezone_delta) GROUP BY s.target_guid, s.metric_guid, s.key_value, s.collection_timestamp) ORDER BY collection_timestamp; -- current state being processed l_state_detail state_detail_type; -- data structure for alert state (per metric) TYPE alert_map_type IS RECORD (metric_guid mgmt_severity.metric_guid%TYPE, key_value mgmt_severity.key_value%TYPE, severity_code mgmt_severity.severity_code%TYPE, collection_timestamp mgmt_severity.collection_timestamp%TYPE); TYPE alert_map_array_type IS TABLE OF alert_map_type; -- alert state for this target l_alert_map alert_map_array_type; l_current_criticals NUMBER; l_current_warnings NUMBER; -- the top N state summaries l_summaries state_summary_array_type := state_summary_array_type(); -- object-type version l_alert_summaries mgmt_db_statesummary_array := mgmt_db_statesummary_array(); -- latest values l_collection_timestamp DATE; l_state VARCHAR2(16); -- -- Initialize the severity mapping with state at the beginning of the -- interval. -- -- p_target_guid: the target whose severities are being captured -- -- Returns: The initial availability state of the target ('DOWN', 'BLACKOUT', -- or 'UP'). -- FUNCTION initialize_severity_mapping(p_target_guid RAW, p_target_timezone_delta NUMBER) RETURN VARCHAR2 IS -- availability status l_avail_status VARCHAR2(16) := g_state_up; -- capture initial state l_init_state cursorType; -- alert map entry l_alert_map_entry alert_map_type; BEGIN -- -- Reset the alert mapping. -- l_alert_map := alert_map_array_type(); l_current_criticals := 0; l_current_warnings := 0; -- -- Reset the details from the previous current summary. -- IF l_current_summary.state_details IS NULL THEN l_current_summary.state_details := state_detail_array_type(); ELSE l_current_summary.state_details.DELETE; END IF; -- -- For each metric's severity state at the beginning of the interval... -- l_init_state := get_metric_state_at(p_target_guid, to_tz(k_now, p_timezone_delta, p_target_timezone_delta) - p_full_interval); LOOP FETCH l_init_state INTO l_alert_map_entry; EXIT WHEN l_init_state%NOTFOUND; -- -- ...translate date, -- l_alert_map_entry.collection_timestamp := to_tz(l_alert_map_entry.collection_timestamp, p_target_timezone_delta, p_timezone_delta); l_alert_map.EXTEND; l_alert_map(l_alert_map.COUNT) := l_alert_map_entry; -- -- ...count criticals, warnings, and log initial availability, -- IF l_alert_map_entry.severity_code = mgmt_global.g_severity_warning THEN l_current_warnings := l_current_warnings + 1; ELSIF l_alert_map_entry.severity_code = mgmt_global.g_severity_critical THEN IF l_alert_map_entry.metric_guid = l_updown_guid THEN l_avail_status := g_state_down; ELSE l_current_criticals := l_current_criticals + 1; END IF; ELSIF l_alert_map_entry.severity_code = mgmt_global.g_severity_blackout_start THEN l_avail_status := g_state_blackout; END IF; -- -- ...add the initial state as a "severity" to the details, and... -- l_state_detail.collection_timestamp := l_alert_map_entry.collection_timestamp; l_state_detail.severity_code := l_alert_map_entry.severity_code; l_state_detail.metric_guid := l_alert_map_entry.metric_guid; l_state_detail.key_value := l_alert_map_entry.key_value; IF l_alert_map_entry.metric_guid = l_updown_guid THEN l_state_detail.state := CASE l_alert_map_entry.severity_code WHEN mgmt_global.g_severity_critical THEN g_state_down ELSE g_state_up END; ELSE l_state_detail.state := CASE l_alert_map_entry.severity_code WHEN mgmt_global.g_severity_critical THEN g_state_critical WHEN mgmt_global.g_severity_warning THEN g_state_warning WHEN mgmt_global.g_severity_blackout_start THEN g_state_blackout WHEN mgmt_global.g_severity_blackout_end THEN g_state_unblackout WHEN mgmt_global.g_severity_clear THEN g_state_clear END; END IF; -- -- ...add this initial severity state to the mapping. -- l_current_summary.state_details.EXTEND; l_current_summary.state_details(l_current_summary.state_details.COUNT) := l_state_detail; END LOOP; CLOSE l_init_state; l_current_summary.init_detail_count := l_current_summary.state_details.COUNT; RETURN l_avail_status; END initialize_severity_mapping; -- -- Update the severity mapping for a metric given its new code. -- -- p_metric_guid: the metric whose severity is being processed -- p_key_value: the key for this metric's severity -- p_severity_code: the current code for the severity -- p_start_collection_timestamp: the time of the severity change -- p_time_in_critical: the time spent in critical -- p_time_in_warning: the time spent in warning -- PROCEDURE update_mapped_severity(p_metric_guid IN RAW, p_key_value IN VARCHAR2, p_severity_code IN NUMBER, p_collection_timestamp IN DATE, p_time_in_critical IN OUT NUMBER, p_time_in_warning IN OUT NUMBER) IS l_found_i NUMBER := 0; BEGIN -- -- Search for this entry in the map. -- FOR l_severity_i IN 1..l_alert_map.COUNT LOOP IF l_alert_map(l_severity_i).metric_guid = p_metric_guid AND l_alert_map(l_severity_i).key_value = p_key_value THEN l_found_i := l_severity_i; -- -- When found, reduce appropriate severity watermark. -- IF l_alert_map(l_found_i).severity_code = mgmt_global.g_severity_warning THEN l_current_warnings := l_current_warnings - 1; p_time_in_warning := p_time_in_warning + 86400 * (p_collection_timestamp - l_alert_map(l_found_i).collection_timestamp); ELSIF l_alert_map(l_found_i).severity_code = mgmt_global.g_severity_critical THEN l_current_criticals := l_current_criticals - 1; p_time_in_critical := p_time_in_critical + 86400 * (p_collection_timestamp - l_alert_map(l_found_i).collection_timestamp); END IF; END IF; END LOOP; -- -- Add a new entry, if necessary. -- IF l_found_i = 0 THEN l_alert_map.EXTEND; l_found_i := l_alert_map.COUNT; l_alert_map(l_found_i).metric_guid := p_metric_guid; l_alert_map(l_found_i).key_value := p_key_value; END IF; -- -- Set the severity code in the new entry. -- l_alert_map(l_found_i).severity_code := p_severity_code; l_alert_map(l_found_i).collection_timestamp := p_collection_timestamp; -- -- Increment appropriate severity watermark. -- Note: this is the new value. -- IF l_alert_map(l_found_i).severity_code = mgmt_global.g_severity_warning THEN l_current_warnings := l_current_warnings + 1; ELSIF l_alert_map(l_found_i).severity_code = mgmt_global.g_severity_critical THEN l_current_criticals := l_current_criticals + 1; END IF; END update_mapped_severity; -- -- Close open severities (with respect to aggregate times). -- -- p_time_in_critical: time spent in critical -- p_time_in_warning: time spent in warning -- -- Note: finalization of time spent down is computed elsewhere. -- PROCEDURE finalize_severities(p_time_in_critical IN OUT NUMBER, p_time_in_warning IN OUT NUMBER) IS BEGIN -- -- For each entry in the map... -- FOR l_severity_i IN 1..l_alert_map.COUNT LOOP -- -- ...add in the contribution of open criticals and warnings -- IF l_alert_map(l_severity_i).severity_code = mgmt_global.g_severity_warning THEN p_time_in_warning := p_time_in_warning + 86400 * (k_now - l_alert_map(l_severity_i).collection_timestamp); ELSIF l_alert_map(l_severity_i).severity_code = mgmt_global.g_severity_critical THEN p_time_in_critical := p_time_in_critical + 86400 * (k_now - l_alert_map(l_severity_i).collection_timestamp); END IF; END LOOP; END finalize_severities; -- -- Complete the summarization of a summary with details filled in. -- -- p_state_summary: the summary record, partially filled in -- to be -- completed here -- PROCEDURE summarize(p_state_summary IN OUT state_summary_type) IS l_current_status NUMBER := mgmt_global.g_status_up; l_time_down NUMBER := 0; l_time_in_critical NUMBER := 0; l_time_in_warning NUMBER := 0; l_critical_occurrences NUMBER := 0; l_warning_occurrences NUMBER := 0; l_start_down DATE := NULL; BEGIN -- -- Initialize the state of alerts at the start of the interval. -- IF l_state = g_state_down THEN l_start_down := k_now; END IF; FOR l_detail_i IN 1..p_state_summary.state_details.COUNT LOOP -- -- Status changes override severity changes. -- IF p_state_summary.state_details(l_detail_i).state = g_state_up THEN IF l_start_down IS NOT NULL THEN l_time_down := l_time_down + (p_state_summary.state_details(l_detail_i).collection_timestamp - l_start_down); l_start_down := NULL; END IF; l_state := g_state_up; ELSIF p_state_summary.state_details(l_detail_i).state = g_state_down THEN l_start_down := p_state_summary.state_details(l_detail_i).collection_timestamp; l_state := g_state_down; ELSIF p_state_summary.state_details(l_detail_i).state = g_state_blackout THEN l_state := g_state_blackout; ELSIF p_state_summary.state_details(l_detail_i).state = g_state_unblackout THEN IF l_start_down IS NULL THEN l_state := g_state_up; ELSE l_state := g_state_down; END IF; ELSIF l_detail_i > p_state_summary.init_detail_count THEN -- -- Process a new severity, ignoring availability. -- IF p_state_summary.state_details(l_detail_i).state = g_state_critical THEN update_mapped_severity(p_state_summary.state_details(l_detail_i).metric_guid, p_state_summary.state_details(l_detail_i).key_value, mgmt_global.g_severity_critical, p_state_summary.state_details(l_detail_i).collection_timestamp, l_time_in_critical, l_time_in_warning); l_critical_occurrences := l_critical_occurrences + 1; ELSIF p_state_summary.state_details(l_detail_i).state = g_state_warning THEN update_mapped_severity(p_state_summary.state_details(l_detail_i).metric_guid, p_state_summary.state_details(l_detail_i).key_value, mgmt_global.g_severity_warning, p_state_summary.state_details(l_detail_i).collection_timestamp, l_time_in_critical, l_time_in_warning); l_warning_occurrences := l_warning_occurrences + 1; ELSIF p_state_summary.state_details(l_detail_i).state = g_state_clear THEN update_mapped_severity(p_state_summary.state_details(l_detail_i).metric_guid, p_state_summary.state_details(l_detail_i).key_value, mgmt_global.g_severity_clear, p_state_summary.state_details(l_detail_i).collection_timestamp, l_time_in_critical, l_time_in_warning); END IF; END IF; -- -- Readjust the current state value. -- p_state_summary.state_details(l_detail_i).state := CASE WHEN l_state = g_state_down OR l_state = g_state_blackout THEN l_state WHEN l_current_criticals > 0 THEN g_state_critical WHEN l_current_warnings > 0 THEN g_state_warning ELSE g_state_clear END; END LOOP; -- -- Add in the contribution of the final severity. -- finalize_severities(l_time_in_critical, l_time_in_warning); -- -- Set current status according to availability -- IF l_state = g_state_down THEN l_current_status := mgmt_global.g_status_down; IF l_start_down IS NOT NULL THEN l_time_down := l_time_down + (k_now - l_start_down); END IF; ELSIF l_state = g_state_blackout THEN l_current_status := mgmt_global.g_status_blackout; ELSE l_current_status := mgmt_global.g_status_up; END IF; p_state_summary.current_status := l_current_status; p_state_summary.time_down := l_time_down; p_state_summary.time_in_critical := l_time_in_critical; p_state_summary.time_in_warning := l_time_in_warning; p_state_summary.critical_occurrences := l_critical_occurrences; p_state_summary.warning_occurrences := l_warning_occurrences; END summarize; -- -- Compare two completed summarizations -- -- p_state_summary1: first state summarization -- p_state_summary2: second state summarization -- -- Returns: typical compare result for sorting. I.e., -- 1 => p_state_summary1 worse than p_state_summary2 -- 0 => p_state_summary1 equivalent to p_state_summary2 -- -1 => p_state_summary1 better than p_state_summary2 -- (break ties using target_name to ensure predictability) -- FUNCTION compare_summaries(p_state_summary1 IN state_summary_type, p_state_summary2 IN state_summary_type) RETURN NUMBER IS -- resulting comparison value l_comparison NUMBER := 0; BEGIN IF p_state_summary1.current_status <> p_state_summary2.current_status THEN l_comparison := SIGN(p_state_summary2.current_status - p_state_summary1.current_status); ELSIF p_state_summary1.time_down <> p_state_summary2.time_down THEN l_comparison := SIGN(p_state_summary1.time_down - p_state_summary2.time_down); ELSIF p_state_summary1.time_in_critical <> p_state_summary2.time_in_critical THEN l_comparison := SIGN(p_state_summary1.time_in_critical - p_state_summary2.time_in_critical); ELSIF p_state_summary1.time_in_warning <> p_state_summary2.time_in_warning THEN l_comparison := SIGN(p_state_summary1.time_in_warning - p_state_summary2.time_in_warning); ELSIF p_state_summary1.critical_occurrences <> p_state_summary2.critical_occurrences THEN l_comparison := SIGN(p_state_summary1.critical_occurrences - p_state_summary2.critical_occurrences); ELSIF p_state_summary1.warning_occurrences <> p_state_summary2.warning_occurrences THEN l_comparison := SIGN(p_state_summary1.warning_occurrences - p_state_summary2.warning_occurrences); ELSE IF p_state_summary1.target_name < p_state_summary2.target_name THEN l_comparison := 1; ELSIF p_state_summary1.target_name > p_state_summary2.target_name THEN l_comparison := -1; END IF; END IF; RETURN l_comparison; END compare_summaries; -- -- Insert a summarization into the top list. -- -- p_state_summary: the summarization just completed -- PROCEDURE insert_summary(p_state_summary IN state_summary_type) IS -- position to insert l_insert_at NUMBER := l_summaries.COUNT + 1; -- original summarization count l_prev_count NUMBER := l_summaries.COUNT; -- running result of compare l_compare NUMBER; BEGIN -- -- Find the insertion point. -- FOR l_summary_i IN REVERSE 1..l_summaries.COUNT LOOP -- abort when this should not precede the current summary EXIT WHEN compare_summaries(p_state_summary, l_summaries(l_summary_i)) <= 0; -- if yes, move to the next element l_insert_at := l_summary_i; END LOOP; -- -- Does this fall in the top N? -- IF l_insert_at <= p_top_count THEN -- make room, if necessary IF l_prev_count < p_top_count THEN l_summaries.EXTEND; END IF; -- shift elements down, where necessary, potentially dropping the last IF l_prev_count = p_top_count THEN l_prev_count := l_prev_count - 1; END IF; FOR l_summary_i IN REVERSE l_insert_at..l_prev_count LOOP l_summaries(l_summary_i + 1) := l_summaries(l_summary_i); END LOOP; -- add this summary l_summaries(l_insert_at) := p_state_summary; END IF; END insert_summary; BEGIN -- -- Grab the availability metric guid once. -- SELECT MAX(m.metric_guid) INTO l_updown_guid FROM mgmt_metrics m WHERE m.target_type = mgmt_global.g_database_target_type AND m.metric_name = mgmt_global.g_avail_metric_name AND m.metric_column = mgmt_global.g_avail_metric_column; -- -- For each target in the group... -- OPEN c_targets_of_group; LOOP FETCH c_targets_of_group INTO l_target_guid, l_current_summary.target_name, l_target_timezone_delta; EXIT WHEN c_targets_of_group%NOTFOUND; -- -- ...initialize the severity mapping,... -- l_state := initialize_severity_mapping(l_target_guid, l_target_timezone_delta); -- -- ...load the details for the target,... -- OPEN c_state_changes_of_target(l_target_guid, l_target_timezone_delta); LOOP FETCH c_state_changes_of_target INTO l_state_detail.collection_timestamp, l_state_detail.state, l_state_detail.severity_code, l_state_detail.metric_guid, l_state_detail.key_value; EXIT WHEN c_state_changes_of_target%NOTFOUND; -- -- (add each detail to the summary) -- l_current_summary.state_details.EXTEND; l_current_summary.state_details(l_current_summary.state_details.COUNT) := l_state_detail; END LOOP; CLOSE c_state_changes_of_target; -- -- ...produce a summary from the details,... -- summarize(l_current_summary); -- -- ...and add the summary to the top set. -- insert_summary(l_current_summary); END LOOP; CLOSE c_targets_of_group; -- -- Package up the data to expose through a cursor. -- FOR l_target_i IN 1..l_summaries.COUNT LOOP -- -- Each target begins in a state different from expected states, and with -- a current timestamp preceding expected timestamps. -- l_state := 'NONE'; l_collection_timestamp := k_now - (p_full_interval + 1/1440); FOR l_detail_i IN 1..l_summaries(l_target_i).state_details.COUNT LOOP IF l_summaries(l_target_i).state_details(l_detail_i).collection_timestamp > l_collection_timestamp AND l_summaries(l_target_i).state_details(l_detail_i).state <> l_state THEN -- -- We've moved on to a new state change at a new time, so add it to -- the result. -- l_collection_timestamp := l_summaries(l_target_i).state_details(l_detail_i).collection_timestamp; l_state := l_summaries(l_target_i).state_details(l_detail_i).state; l_alert_summaries.EXTEND; l_alert_summaries(l_alert_summaries.COUNT) := mgmt_db_statesummary_obj(l_summaries(l_target_i).target_name, l_collection_timestamp, l_state); END IF; END LOOP; END LOOP; -- -- Return the packaged data in the cursor. -- OPEN p_alert_breakdown FOR SELECT target_name, collection_timestamp, state FROM TABLE(CAST(l_alert_summaries AS mgmt_db_statesummary_array)); END get_top_alert_instances; end emd_database; / show errors;