Rem drv:
Rem
Rem $Header: emdb/source/oracle/sysman/emdrep/sql/db/latest/osm/osm_pkgbodys.sql /st_emdbsa_11.2/6 2009/03/26 11:00:10 mpawelko Exp $
Rem
Rem common_types.sql
Rem
Rem Copyright (c) 2002, 2009, Oracle and/or its affiliates.
Rem All rights reserved.
Rem
Rem NAME
Rem osm_pkgbodys.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem mpawelko 03/17/09 - XbranchMerge 6595433: rework queries inside getDatabasesInfo to improve perf
Rem chanchan 02/09/09 - Add space used by snapshot
Rem mnihalan 10/22/08 - Fix the OFS Space Usage
Rem asubba 08/13/08 - do not show failgroup for diskgroup with extrenal
Rem redundancy
Rem mnihalan 07/18/08 - Change ofs metric usage
Rem dchakumk 03/10/08 - XbranchMerge dchakumk_6752_xbmsrc from
Rem st_emdbsa_11.1
Rem mnihalan 01/25/08 - Fix OFS procedures
Rem mnihalan 01/24/08 - Change ofs procedures
Rem asubba 09/25/07 - XbranchMerge asubba_ui_improvements from main
Rem qsong 07/03/07 - bug 6165631
Rem qsong 05/15/07 - change ofs_state to ofs_mount_state
Rem qsong 04/02/07 -
Rem gmahaney 02/01/07 - XbranchMerge gmahaney_emdb_pt_main from main
Rem qsong 12/15/06 - add getDiskGroupUsageInfo
Rem qsong 11/06/06 -
Rem chanchan 08/23/06 -
Rem qsong 04/11/06 - add OFS related procedures
Rem qsong 09/01/06 - bug 4431301
Rem chyu 06/28/05 - New repmgr header impl
Rem rajeshar 03/13/05 - bug 4234709
Rem jochen 12/14/04 - Pass target type to getDbAlertLogSeverity
Rem jochen 07/13/04 - Handle null start date
Rem jochen 06/10/04 - Deal with db versions
Rem jochen 05/13/04 - Make alertLogTimestamp, instanceStartTime dates
Rem lhan 01/08/04 - Fix bug 3358266
Rem lhan 11/20/03 - add UPPER
Rem lhan 10/09/03 - Fix bug 3182564
Rem xshen 06/11/03 - set is_current for snapshot
Rem xshen 06/05/03 - dbconfig change to ecm tables
Rem spanchum 10/28/02 - reuse db procedures
Rem spanchum 10/14/02 - spanchum_osm_homepage
Rem spanchum 10/07/02 - Created
Rem
CREATE OR REPLACE PACKAGE body emd_osm AS
/*
getOsmSiteMapInfo
*/
procedure getOsmSiteMapInfo(
targetName IN VARCHAR2,
targetType IN VARCHAR2,
osmSitemapInfo OUT SMP_EMD_OSMSITEMAP_OBJ) IS
/* variables */
typeDisplayName VARCHAR2(256);
lastUpdatedTime VARCHAR2(4000);
currentStatus INTEGER;
availPct NUMBER;
startTimestamp Date;
instanceName VARCHAR2(1024);
instanceStartTime DATE;
instanceStartString VARCHAR2(1024);
instanceVersion VARCHAR2(1024);
alertLogTimestamp DATE;
alertLogSeverity NUMBER;
hostName VARCHAR2(64);
targetGuid VARCHAR2(32);
hostGuid VARCHAR2(32);
meta_ver VARCHAR2(8);
cat_prop_1 VARCHAR2(64);
cat_prop_2 VARCHAR2(64);
cat_prop_3 VARCHAR2(64);
cat_prop_4 VARCHAR2(64);
cat_prop_5 VARCHAR2(64);
CURSOR aCursor (tg IN VARCHAR2) IS
SELECT NVL(property_value, ' ')
FROM mgmt_target_properties
WHERE target_guid = HEXTORAW(tg)
AND property_type = 'INSTANCE'
AND property_name = 'StartTime';
begin
-- target guid, type display name, last load time
BEGIN
SELECT target_guid, type_display_name, TO_CHAR(last_load_time),
TYPE_META_VER, CATEGORY_PROP_1, CATEGORY_PROP_2, CATEGORY_PROP_3,
CATEGORY_PROP_4, CATEGORY_PROP_5
INTO targetGuid, typeDisplayName, lastUpdatedTime,
meta_ver, cat_prop_1, cat_prop_2, cat_prop_3,
cat_prop_4, cat_prop_5
FROM mgmt_targets
WHERE target_name = targetName
AND target_type = targetType;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE mgmt_global.target_does_not_exist;
END;
-- current status
currentStatus := mgmt_target.get_avail_current_status(targetGuid);
-- availability percentage
availPct := ROUND(mgmt_target.get_avail_pct_value(targetGuid, 1), 2);
-- start timestamp of current status
startTimestamp := mgmt_target.get_current_status_timestamp(targetGuid);
emd_database.getDbStringValues(targetGuid,
instanceName,
instanceStartTime,
instanceVersion);
--
-- Pre 10.2 agents will not have the time format set, so the
-- start time from getDbStringValues will be null. In that case,
-- just fetch the string value and display it.
--
instanceStartString := ' ';
if (instanceStartTime is null) then
OPEN aCursor (targetGuid);
FETCH aCursor INTO instanceStartString;
end if;
emd_database.getDbAlertLogSeverity(targetGuid, 'osm_instance', meta_ver,
cat_prop_1, cat_prop_2, cat_prop_3,
cat_prop_4, cat_prop_5,
alertLogTimestamp, alertLogSeverity);
emd_database.getHostName(targetGuid,
hostName,
hostGuid);
/* return information */
osmSitemapInfo := SMP_EMD_OSMSITEMAP_OBJ(
typeDisplayName,
lastUpdatedTime,
currentStatus,
availPct,
startTimestamp,
instanceName,
instanceStartTime,
instanceStartString,
instanceVersion,
alertLogTimestamp,
alertLogSeverity,
hostName,
targetGuid,
meta_ver,
cat_prop_1,
cat_prop_2,
cat_prop_3,
cat_prop_4,
cat_prop_5
);
end;
PROCEDURE getDatabasesInfo(target_guid_in IN VARCHAR2,
metaVer_in IN VARCHAR2,
catProp1_in IN VARCHAR2,
catProp2_in IN VARCHAR2,
catProp3_in IN VARCHAR2,
catProp4_in IN VARCHAR2,
catProp5_in IN VARCHAR2,
databases_cur_out OUT cursorType,
diskgroups_cur_out OUT cursorType,
dgusage_cur_out OUT cursorType)
IS
hostName VARCHAR2(64);
l_db_guids MGMT_GUID_ARRAY := MGMT_GUID_ARRAY();
CURSOR aCursor (tg IN VARCHAR2) IS
SELECT NVL(host_name, ' ')
FROM mgmt_targets
WHERE target_guid = HEXTORAW(tg);
BEGIN
hostName := ' ';
OPEN aCursor (target_guid_in);
FETCH aCursor INTO hostName;
CLOSE aCursor;
-- Find all databases serviced by this ASM instance on this host.
-- Most reliable way to do this is using mgmt_target_properties: all serviced
-- db's will have an OSMInstance property containing the ASM instance name.
-- Note: We include mgmt_targets in this query to enforce VPD.
SELECT MGMT_GUID_OBJ(p.target_guid, UPPER(p.property_value)) BULK COLLECT INTO l_db_guids
FROM mgmt_target_properties p, mgmt_targets t,
(SELECT p1.target_guid
FROM mgmt_target_properties p1,
(SELECT property_value
FROM mgmt_target_properties
WHERE target_guid = HEXTORAW(target_guid_in)
AND property_name = 'InstanceName') asm
WHERE p1.property_name = 'OSMInstance'
AND p1.property_value = asm.property_value) asmdb
WHERE t.target_guid = asmdb.target_guid
AND t.host_name = hostName
AND t.target_type = 'oracle_database'
AND p.target_guid = asmdb.target_guid
AND p.property_name = 'DBName';
OPEN databases_cur_out FOR
SELECT tgt.target_name,
tgt.target_type,
ROUND(mgmt_target.get_avail_pct_value(tgt.target_guid, 1), 0) AS avail_pct,
mgmt_target.get_avail_current_status(tgt.target_guid) AS current_status,
NVL(sev_detail.warning_count, 0) AS warning_count,
NVL(sev_detail.critical_count, 0) AS critical_count,
sev_detail.dbname
FROM mgmt_targets tgt,
( SELECT /*+ cardinality( db_guids 4 ) */ db_guids.guid AS target_guid,
db_guids.name AS dbname,
SUM(DECODE(severity_code, MGMT_GLOBAL.G_SEVERITY_WARNING, 1, 0) ) AS warning_count,
SUM(DECODE(severity_code, MGMT_GLOBAL.G_SEVERITY_CRITICAL, 1, 0) ) AS critical_count
FROM mgmt_current_severity cs,
TABLE(CAST(l_db_guids AS MGMT_GUID_ARRAY)) db_guids
WHERE db_guids.guid = cs.target_guid (+)
GROUP BY db_guids.guid, db_guids.name) sev_detail
WHERE tgt.target_guid = sev_detail.target_guid
ORDER BY tgt.target_name;
OPEN diskgroups_cur_out FOR
select dg_total.key_value, dg_total.total_mb, dg_free.free_mb
FROM ( select key_value , TO_CHAR(NVL(value,0)) AS total_mb
from mgmt_metrics m1 , mgmt_current_metrics m2
where m1.metric_guid = m2.metric_guid
and m2.target_guid = HEXTORAW(target_guid_in)
and m1.metric_name = 'DiskGroup_Usage'
and m1.metric_column = 'total_mb'
and m1.target_type = 'osm_instance'
AND m1.type_meta_ver = metaVer_in
AND (m1.category_prop_1 = catProp1_in OR
m1.category_prop_1 = ' ')
AND (m1.category_prop_2 = catProp2_in OR
m1.category_prop_2 = ' ')
AND (m1.category_prop_3 = catProp3_in OR
m1.category_prop_3 = ' ')
AND (m1.category_prop_4 = catProp4_in OR
m1.category_prop_4 = ' ')
AND (m1.category_prop_5 = catProp5_in OR
m1.category_prop_5 = ' ')
) dg_total,
( select key_value , TO_CHAR(NVL(value,0)) AS free_mb
from mgmt_metrics m1 , mgmt_current_metrics m2
where m1.metric_guid = m2.metric_guid
and m2.target_guid = HEXTORAW(target_guid_in)
and m1.metric_name = 'DiskGroup_Usage'
and m1.metric_column = 'free_mb'
and m1.target_type = 'osm_instance'
AND m1.type_meta_ver = metaVer_in
AND (m1.category_prop_1 = catProp1_in OR
m1.category_prop_1 = ' ')
AND (m1.category_prop_2 = catProp2_in OR
m1.category_prop_2 = ' ')
AND (m1.category_prop_3 = catProp3_in OR
m1.category_prop_3 = ' ')
AND (m1.category_prop_4 = catProp4_in OR
m1.category_prop_4 = ' ')
AND (m1.category_prop_5 = catProp5_in OR
m1.category_prop_5 = ' ')
) dg_free
where dg_total.key_value = dg_free.key_value;
OPEN dgusage_cur_out FOR
select DISTINCT KEY_PART1_VALUE , KEY_PART2_VALUE , TO_CHAR(round(NVL(value,0)/1024/1024))
from mgmt_current_metrics m1, mgmt_metrics m2, mgmt_metrics_composite_keys m3
where m1.metric_guid = m2.metric_guid
and m1.target_guid = m3.target_guid
and m1.key_value = m3.composite_key
and m3.target_guid = HEXTORAW(target_guid_in)
and m2.metric_name = 'Database_DiskGroup_Usage'
and m2.target_type = 'osm_instance'
and m2.type_meta_ver = metaVer_in
and (m2.category_prop_1 = catProp1_in OR
m2.category_prop_1 = ' ')
and (m2.category_prop_2 = catProp2_in OR
m2.category_prop_2 = ' ')
and (m2.category_prop_3 = catProp3_in OR
m2.category_prop_3 = ' ')
and (m2.category_prop_4 = catProp4_in OR
m2.category_prop_4 = ' ')
and (m2.category_prop_5 = catProp5_in OR
m2.category_prop_5 = ' ');
END getDatabasesInfo;
PROCEDURE getFailgroupInfo(target_guid_in IN VARCHAR2,
metaVer_in IN VARCHAR2,
catProp1_in IN VARCHAR2,
catProp2_in IN VARCHAR2,
catProp3_in IN VARCHAR2,
catProp4_in IN VARCHAR2,
catProp5_in IN VARCHAR2,
failgroup_cur_out OUT cursorType)
IS
BEGIN
OPEN failgroup_cur_out FOR
select w.client db_client,u.diskgroup,u.failgroup,v.mtr_value total_disk_count,
u.mtr_value available_disk_count from
(select m2.METRIC_COLUMN ,m1.VALUE mtr_value,
m3.KEY_PART1_VALUE DISKGROUP,m3.KEY_PART2_VALUE FAILGROUP
from mgmt_current_metrics m1, mgmt_metrics m2, mgmt_metrics_composite_keys m3
where m1.metric_guid=m2.metric_guid
and m1.target_guid = m3.target_guid
and m1.key_value=m3.composite_key
and m3.target_guid = HEXTORAW(target_guid_in)
and m2.TARGET_TYPE='osm_instance'
and m2.metric_name= 'unavailable_failure_group'
and m2.METRIC_COLUMN = 'fgAvailDiskCount'
and m2.type_meta_ver = metaVer_in
and (m2.category_prop_1 = catProp1_in OR
m2.category_prop_1 = ' ')
and (m2.category_prop_2 = catProp2_in OR
m2.category_prop_2 = ' ')
and (m2.category_prop_3 = catProp3_in OR
m2.category_prop_3 = ' ')
and (m2.category_prop_4 = catProp4_in OR
m2.category_prop_4 = ' ')
and (m2.category_prop_5 = catProp5_in OR
m2.category_prop_5 = ' ')) u,
(select m2.METRIC_COLUMN available ,m1.VALUE mtr_value,
m3.KEY_PART1_VALUE DISKGROUP,m3.KEY_PART2_VALUE FAILGROUP
from mgmt_current_metrics m1, mgmt_metrics m2, mgmt_metrics_composite_keys m3
where m1.metric_guid=m2.metric_guid
and m1.target_guid = m3.target_guid
and m1.key_value=m3.composite_key
and m3.target_guid = HEXTORAW(target_guid_in)
and m2.TARGET_TYPE='osm_instance'
and m2.metric_name= 'unavailable_failure_group'
and m2.METRIC_COLUMN = 'fgDiskCount'
and m2.type_meta_ver = metaVer_in
and (m2.category_prop_1 = catProp1_in OR
m2.category_prop_1 = ' ')
and (m2.category_prop_2 = catProp2_in OR
m2.category_prop_2 = ' ')
and (m2.category_prop_3 = catProp3_in OR
m2.category_prop_3 = ' ')
and (m2.category_prop_4 = catProp4_in OR
m2.category_prop_4 = ' ')
and (m2.category_prop_5 = catProp5_in OR
m2.category_prop_5 = ' ')) v,
(select DISTINCT KEY_PART1_VALUE DISKGROUP, KEY_PART2_VALUE CLIENT
from mgmt_current_metrics m1, mgmt_metrics m2, mgmt_metrics_composite_keys m3
where m1.metric_guid = m2.metric_guid
and m1.target_guid = m3.target_guid
and m1.key_value = m3.composite_key
and m3.target_guid = HEXTORAW(target_guid_in)
and m2.TARGET_TYPE='osm_instance'
and m2.metric_name = 'Database_DiskGroup_Usage'
and m2.type_meta_ver = metaVer_in
and (m2.category_prop_1 = catProp1_in OR
m2.category_prop_1 = ' ')
and (m2.category_prop_2 = catProp2_in OR
m2.category_prop_2 = ' ')
and (m2.category_prop_3 = catProp3_in OR
m2.category_prop_3 = ' ')
and (m2.category_prop_4 = catProp4_in OR
m2.category_prop_4 = ' ')
and (m2.category_prop_5 = catProp5_in OR
m2.category_prop_5 = ' ')) w,
(select key_value DISKGROUP, string_value REDUNDANCY from
mgmt_current_metrics m1,mgmt_metrics m2
where m1.metric_guid=m2.metric_guid
and m1.target_guid = HEXTORAW(target_guid_in)
and m2.target_type='osm_instance'
and m2.metric_name='DiskGroup_Usage'
and m2.metric_column='type'
and m2.type_meta_ver = metaVer_in
and (m2.category_prop_1 = catProp1_in OR
m2.category_prop_1 = ' ')
and (m2.category_prop_2 = catProp2_in OR
m2.category_prop_2 = ' ')
and (m2.category_prop_3 = catProp3_in OR
m2.category_prop_3 = ' ')
and (m2.category_prop_4 = catProp4_in OR
m2.category_prop_4 = ' ')
and (m2.category_prop_5 = catProp5_in OR
m2.category_prop_5 = ' ')) x
where u.failgroup=v.failgroup
and u.DISKGROUP=v.DISKGROUP
and u.DISKGROUP=w.DISKGROUP
and u.DISKGROUP=x.DISKGROUP
and x.REDUNDANCY !='EXTERN';
END getFailgroupInfo;
PROCEDURE getDiskGroupUsageInfo(target_guid_in IN VARCHAR2,
metaVer_in IN VARCHAR2,
catProp1_in IN VARCHAR2,
catProp2_in IN VARCHAR2,
catProp3_in IN VARCHAR2,
catProp4_in IN VARCHAR2,
catProp5_in IN VARCHAR2,
databases_cur_out OUT cursorType,
diskgroups_cur_out OUT cursorType,
dgusage_cur_out OUT cursorType,
ofsusage_cur_out OUT cursorType,
none_ofs_volumes_usage_cur_out OUT cursorType)
IS
BEGIN
-- get the database related diskgroup usage data
getDatabasesInfo(target_guid_in,
metaVer_in,
catProp1_in,
catProp2_in,
catProp3_in,
catProp4_in,
catProp5_in,
databases_cur_out,
diskgroups_cur_out,
dgusage_cur_out);
OPEN ofsusage_cur_out FOR
select key_value2, TO_CHAR(sum(NVL(value, 0)))
from mgmt$metric_current
where target_guid = HEXTORAW(target_guid_in)
and metric_name = 'Single_Instance_OFS_Summary'
and metric_column = 'dg_alloc_size_gb'
and key_value2 is not null
AND collection_timestamp =
(select MAX(collection_timestamp)
from mgmt$metric_current
where target_guid = HEXTORAW(target_guid_in)
and metric_name = 'Single_Instance_OFS_Summary'
and metric_column = 'dg_alloc_size_gb')
group by key_value2;
OPEN none_ofs_volumes_usage_cur_out FOR
select a.key_value2, TO_CHAR(sum(NVL(a.value, 0)))
from mgmt$metric_current a, mgmt$metric_current b
where a.target_guid = HEXTORAW(target_guid_in)
and a.metric_name = 'Volumes_Summary'
and a.metric_column = 'dg_alloc_size_gb'
and b.target_guid = a.target_guid
and b.key_value = a.key_value
and b.key_value2 =a.key_value2
and b.metric_name=a.metric_name
and b.metric_column='volume_usage'
and (b.value is null or b.value!='ACFS')
and a.collection_timestamp =
(select max(collection_timestamp)
from mgmt$metric_current
where target_guid = HEXTORAW(target_guid_in)
and metric_name = 'Volumes_Summary'
and metric_column = 'dg_alloc_size_gb')
group by a.key_value2;
END getDiskGroupUsageInfo;
PROCEDURE getDatabasesFGInfo(target_guid_in IN VARCHAR2,
metaVer_in IN VARCHAR2,
catProp1_in IN VARCHAR2,
catProp2_in IN VARCHAR2,
catProp3_in IN VARCHAR2,
catProp4_in IN VARCHAR2,
catProp5_in IN VARCHAR2,
databases_cur_out OUT cursorType,
diskgroups_cur_out OUT cursorType,
dgusage_cur_out OUT cursorType,
ofsusage_cur_out OUT cursorType,
none_ofs_volumes_usage_cur_out OUT cursorType,
failgroup_cur_out OUT cursorType)
IS
BEGIN
getDiskGroupUsageInfo(target_guid_in,
metaVer_in,
catProp1_in,
catProp2_in,
catProp3_in,
catProp4_in,
catProp5_in,
databases_cur_out,
diskgroups_cur_out,
dgusage_cur_out,
ofsusage_cur_out,
none_ofs_volumes_usage_cur_out);
getFailgroupInfo(target_guid_in,
metaVer_in,
catProp1_in,
catProp2_in,
catProp3_in,
catProp4_in,
catProp5_in,
failgroup_cur_out);
end getDatabasesFGInfo;
/*
* OFS related procedures
*/
/*
* getSingleInstanceOFSSummary
*/
PROCEDURE getSingleInstanceOFSSummary(targetName IN VARCHAR2,
targetType IN VARCHAR2,
servicedOFSInfoArray OUT SMP_EMD_ASM_SERVED_OFS_ARRAY)
IS
targetGuid MGMT_TARGETS.TARGET_GUID%TYPE;
mountPoint MGMT$METRIC_CURRENT.VALUE%TYPE;
availability MGMT$METRIC_CURRENT.VALUE%TYPE;
corrupt MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsState MGMT$METRIC_CURRENT.VALUE%TYPE;
volumeName MGMT$METRIC_CURRENT.VALUE%TYPE;
volumeDevice MGMT$METRIC_CURRENT.KEY_VALUE%TYPE;
diskGroup MGMT$METRIC_CURRENT.KEY_VALUE2%TYPE;
dgAllocGB MGMT$METRIC_CURRENT.VALUE%TYPE;
sizeGB MGMT$METRIC_CURRENT.VALUE%TYPE;
usedGB MGMT$METRIC_CURRENT.VALUE%TYPE;
usedPct MGMT$METRIC_CURRENT.VALUE%TYPE;
l_serviced_ofs_obj SMP_EMD_ASM_SERVED_OFS;
CURSOR aCursor (target_guid_in IN VARCHAR2,
key_value_in IN VARCHAR2,
metricColumn IN VARCHAR2,
metricName IN VARCHAR2) IS
SELECT NVL(value,' ')
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND metric_name = metricName
AND key_value=key_value_in
AND metric_column = metricColumn;
CURSOR keyCursor (target_guid_in IN VARCHAR2) IS
SELECT distinct key_value
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND ((key_value IS NOT NULL) AND (length(trim(key_value)) IS NOT NULL))
AND metric_name = 'Single_Instance_OFS_Summary'
AND collection_timestamp =
(SELECT MAX(collection_timestamp)
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND metric_name = 'Single_Instance_OFS_Summary');
-- ofs mountpoint
CURSOR mountPointCursor (target_guid_in IN VARCHAR2, key_in IN VARCHAR2) IS
SELECT distinct NVL(value,' ')
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND key_value=key_in
AND metric_name = 'OFS_State'
AND metric_column = 'ofs_mountpoint'
AND COLLECTION_TIMESTAMP =
(SELECT MAX(COLLECTION_TIMESTAMP)
FROM mgmt$metric_current
WHERE TARGET_GUID=HEXTORAW(target_guid_in)
AND key_value=key_in
AND metric_name = 'OFS_State');
-- get the diskgroup from Volumes_Summary metric
CURSOR dgCursor (target_guid_in IN VARCHAR2, key_in IN VARCHAR2) IS
SELECT distinct NVL(key_value2,' ')
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND key_value = key_in
AND metric_column='volume_usage'
AND NVL(value, ' ') = 'ACFS'
AND metric_name = 'Volumes_Summary';
BEGIN
-- target guid
BEGIN
SELECT target_guid
INTO targetGuid
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;
servicedOFSInfoArray := SMP_EMD_ASM_SERVED_OFS_ARRAY();
-- volume device --
OPEN keyCursor (targetGuid);
LOOP
FETCH keyCursor INTO volumeDevice;
EXIT WHEN keyCursor%NOTFOUND;
-- mount point --
OPEN mountPointCursor(targetGuid,volumeDevice);
LOOP
FETCH mountPointCursor INTO mountPoint;
EXIT WHEN mountPointCursor%NOTFOUND;
-- availability --
OPEN aCursor (targetGuid,volumeDevice,'ofs_availability', 'OFS_State');
LOOP
FETCH aCursor INTO availability;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- corrupt --
OPEN aCursor (targetGuid,volumeDevice,'ofs_corrupted_state', 'Single_Instance_OFS_Summary');
LOOP
FETCH aCursor INTO corrupt;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- state --
OPEN aCursor (targetGuid,volumeDevice,'ofs_mount_state', 'OFS_State');
LOOP
FETCH aCursor INTO ofsState;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- volume name --
OPEN aCursor (targetGuid,volumeDevice,'ofs_volume_name', 'Single_Instance_OFS_Summary');
LOOP
FETCH aCursor INTO volumeName;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- diskgroup --
OPEN dgCursor (targetGuid,volumeDevice);
LOOP
FETCH dgCursor INTO diskGroup;
EXIT WHEN dgCursor%NOTFOUND;
END LOOP;
CLOSE dgCursor;
-- diskgroup allocated size GB --
OPEN aCursor (targetGuid,volumeDevice,'dg_alloc_size_gb', 'Single_Instance_OFS_Summary');
LOOP
FETCH aCursor INTO dgAllocGB;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- size GB --
OPEN aCursor (targetGuid,volumeDevice,'ofs_size_gb', 'Single_Instance_OFS_Summary');
LOOP
FETCH aCursor INTO sizeGB;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
--used (GB)--
OPEN aCursor (targetGuid,volumeDevice,'ofs_used_gb', 'Single_Instance_OFS_Summary');
LOOP
FETCH aCursor INTO usedGB;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- used Pct --
OPEN aCursor (targetGuid,volumeDevice,'ofs_used_pct', 'Single_Instance_OFS_Summary');
LOOP
FETCH aCursor INTO usedPct;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
l_serviced_ofs_obj := SMP_EMD_ASM_SERVED_OFS(
mountPoint,
availability,
corrupt,
ofsState,
volumeName,
volumeDevice,
diskGroup,
dgAllocGB,
sizeGB,
usedGB,
usedPct);
servicedOFSInfoArray.EXTEND;
servicedOFSInfoArray(servicedOFSInfoArray.COUNT):= l_serviced_ofs_obj;
END LOOP;
CLOSE mountPointCursor;
END LOOP;
CLOSE keyCursor;
END;
/*
* getClusteredOFSInfo
*/
PROCEDURE getClusteredOFSInfo(clusterName IN VARCHAR2,
smpClusterOfsObjArray out SMP_EMD_CLUSTER_OFS_OBJ_ARRAY)
IS
hostName MGMT_TARGET_MEMBERSHIPS.MEMBER_TARGET_NAME%TYPE;
asmTarget MGMT$METRIC_CURRENT.TARGET_NAME%TYPE;
ofsName MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsUpNum NUMBER DEFAULT 0;
ofsDownNum NUMBER DEFAULT 0;
ofsMountNum NUMBER DEFAULT 0;
ofsDisMountNum NUMBER DEFAULT 0;
ofsVolumeName MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsVolumeDevice MGMT$METRIC_CURRENT.KEY_VALUE%TYPE;
ofsDiskGroup MGMT$METRIC_CURRENT.KEY_VALUE2%TYPE;
dgAllocSizeGB MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsSizeGB MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsUsedGB MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsUsedPct MGMT$METRIC_CURRENT.VALUE%TYPE;
wCount NUMBER;
cCount NUMBER;
totalWarningCount NUMBER DEFAULT 0;
totalCriticalCount NUMBER DEFAULT 0;
l_ofsName MGMT$METRIC_CURRENT.VALUE%TYPE;
l_string_value MGMT$METRIC_CURRENT.VALUE%TYPE;
l_number_value NUMBER;
hostNameStr MGMT_TARGET_MEMBERSHIPS.MEMBER_TARGET_NAME%TYPE;
ofsExistsInList NUMBER :=0;
smpClusterOfsObj SMP_EMD_CLUSTER_OFS_OBJ;
TYPE ValuesArray IS table of VARCHAR2(256);
l_hosts_array ValuesArray;
l_ofs_array ValuesArray;
metricColumn MGMT$METRIC_CURRENT.METRIC_COLUMN%TYPE;
metricValue MGMT$METRIC_CURRENT.VALUE%TYPE;
CURSOR hostsCursor(name VARCHAR2) IS
SELECT member_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE composite_target_name=name
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE;
CURSOR ofsCursor(host VARCHAR2) IS
SELECT distinct NVL(value, ' ')
FROM mgmt$metric_current
WHERE metric_name='OFS_State'
AND target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_column='ofs_mountpoint'
AND value != ' ' --skip the empty mountpoints, it's defined as dummy value in metric
AND key_value2=host;
CURSOR availCursor(ofsName VARCHAR2, hostNameStr VARCHAR2) IS
SELECT count(a.value)
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.metric_name='OFS_State'
AND a.metric_column='ofs_availability'
AND a.value='AVAILABLE'
AND a.key_value2 = hostNameStr
AND b.target_type=a.target_type
AND b.metric_name=a.metric_name
AND b.key_value=a.key_value
AND b.key_value2=a.key_value2
AND b.metric_column='ofs_mountpoint'
AND b.value=ofsName;
CURSOR unAvailCursor(ofsName VARCHAR2, hostNameStr VARCHAR2) IS
SELECT count(*)
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.metric_name='OFS_State'
AND a.metric_column='ofs_availability'
AND a.value IS NULL
AND a.key_value2 = hostNameStr
AND b.target_type=a.target_type
AND b.metric_name=a.metric_name
AND b.key_value=a.key_value
AND b.key_value2=a.key_value2
AND b.metric_column='ofs_mountpoint'
AND b.value=ofsName;
CURSOR keyCursor(ofsName VARCHAR2, hostNameStr VARCHAR2) IS
SELECT distinct a.key_value
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.metric_name='OFS_State'
AND a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.key_value2=hostNameStr
AND b.metric_name=a.metric_name
AND b.target_type=a.target_type
AND b.key_value=a.key_value
AND b.key_value2=a.key_value2
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ') = ofsName;
CURSOR dgCursor(ofsName VARCHAR2, hostNameStr VARCHAR2) IS
SELECT distinct NVL(a.key_value2, ' ')
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.metric_name='Single_Instance_OFS_Summary'
AND a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND b.metric_name='OFS_Sate'
AND b.target_type=a.target_type
AND b.key_value2=hostNameStr
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ')= ofsName;
CURSOR valueCursor(ofsName VARCHAR2, hostNameStr VARCHAR2, metricCol VARCHAR2) IS
SELECT distinct NVL(a.value, ' ')
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.metric_name='Single_Instance_OFS_Summary'
AND a.metric_column=metricCol
AND a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND b.metric_name='OFS_State'
AND b.target_type=a.target_type
AND b.key_value2=hostNameStr
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ')= ofsName;
CURSOR stateCursor(ofsName VARCHAR2, hostNameStr VARCHAR2, metricValue VARCHAR2) IS
SELECT count(a.value)
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.metric_name='OFS_State'
AND a.metric_column='ofs_mount_state'
AND NVL(a.value, ' ') = metricValue
AND a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.key_value2=hostNameStr
AND b.metric_name=a.metric_name
AND b.target_type=a.target_type
AND b.key_value=a.key_value
AND b.key_value2=a.key_value2
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ')= ofsName;
CURSOR asmTargetsCursor(ofsName VARCHAR2, hostNameStr VARCHAR2) IS
SELECT distinct a.target_name
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.metric_name='Single_Instance_OFS_Summary'
AND b.target_type=a.target_type
AND b.metric_name='OFS_State'
AND b.key_value2=hostNameStr
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ')= ofsName;
CURSOR alertCursor(asm_target_in VARCHAR2,ofs_name_in VARCHAR2) IS
SELECT nvl(SUM(DECODE(a.alert_state,'Warning',1,0)),0)AS warning_count,
nvl(SUM(DECODE(a.alert_state,'Critical',1,0)),0)AS critical_count
FROM mgmt$alert_current a, mgmt$metric_current b
WHERE a.target_name=asm_target_in
AND a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.metric_name in ('Single_Instance_OFS_Summary', 'OFS_State')
AND b.target_name=a.target_name
AND b.target_type=a.target_type
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ')= ofsName;
CURSOR spaceCursor(ofsName VARCHAR2, hostNameStr VARCHAR2) IS
SELECT a.metric_column, a.value
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.metric_name='Single_Instance_OFS_Summary'
AND a.metric_column IN ('ofs_used_pct', 'dg_alloc_size_gb', 'ofs_size_gb', 'ofs_used_gb')
AND a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND b.metric_name='OFS_State'
AND b.target_type=a.target_type
AND b.key_value=a.key_value
AND b.key_value2=hostNameStr
AND b.metric_column='ofs_mountpoint'
AND NVL(b.value, ' ')= ofsName;
BEGIN
smpClusterOfsObjArray := SMP_EMD_CLUSTER_OFS_OBJ_ARRAY();
l_hosts_array := ValuesArray();
l_ofs_array := ValuesArray();
-- hosts in the cluster
OPEN hostsCursor(clusterName);
LOOP
FETCH hostsCursor INTO hostName;
EXIT WHEN hostsCursor%NOTFOUND;
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= hostName;
END LOOP;
CLOSE hostsCursor;
FOR i in 1..l_hosts_array.COUNT LOOP
hostName := l_hosts_array(i);
OPEN ofsCursor(hostName);
LOOP
FETCH ofsCursor INTO l_ofsName;
EXIT WHEN ofsCursor%NOTFOUND;
ofsExistsInList := 0;
-- see if this ofs already exists in the list
FOR j in 1..l_ofs_array.COUNT LOOP
IF l_ofs_array(j)= l_ofsName THEN
ofsExistsInList := 1;
exit;
END IF;
END LOOP;
IF ofsExistsInList=0 THEN
l_ofs_array.EXTEND;
l_ofs_array(l_ofs_array.COUNT):= l_ofsName;
END IF;
END LOOP;
CLOSE ofsCursor;
END LOOP;
-- For each OFS in the l_ofs_array
FOR i in 1..l_ofs_array.COUNT LOOP
ofsUpNum := 0;
ofsDownNum := 0;
ofsMountNum := 0;
ofsDisMountNum := 0;
totalWarningCount := 0;
totalCriticalCount := 0;
ofsUsedGB := 0;
ofsUsedPct := 0;
FOR j in 1..l_hosts_array.COUNT LOOP
l_ofsName := l_ofs_array(i);
hostNameStr := l_hosts_array(j);
-- get the number of up ofs
OPEN availCursor(l_ofsName,hostNameStr);
LOOP
FETCH availCursor INTO l_number_value;
EXIT WHEN availCursor%NOTFOUND;
END LOOP;
CLOSE availCursor;
ofsUpNum := ofsUpNum + l_number_value;
-- get the number of down ofs
OPEN unAvailCursor(l_ofsName,hostNameStr);
LOOP
FETCH unAvailCursor INTO l_number_value;
EXIT WHEN unAvailCursor%NOTFOUND;
END LOOP;
CLOSE unAvailCursor;
ofsDownNum := ofsDownNum + l_number_value;
-- get the number of mounted ofs
OPEN stateCursor(l_ofsName,hostNameStr, 'MOUNTED');
LOOP
FETCH stateCursor INTO l_number_value;
EXIT WHEN stateCursor%NOTFOUND;
END LOOP;
CLOSE stateCursor;
ofsMountNum := ofsMountNum + l_number_value;
-- get the number of dismounted ofs
OPEN stateCursor(l_ofsName,hostNameStr, 'DISMOUNTED');
LOOP
FETCH stateCursor INTO l_number_value;
EXIT WHEN stateCursor%NOTFOUND;
END LOOP;
CLOSE stateCursor;
ofsDisMountNum := ofsDisMountNum + l_number_value;
-- get volume name
OPEN valueCursor(l_ofsName,hostNameStr,'ofs_volume_name');
LOOP
FETCH valueCursor INTO ofsVolumeName;
EXIT WHEN valueCursor%NOTFOUND;
END LOOP;
CLOSE valueCursor;
-- get volume device
OPEN keyCursor(l_ofsName,hostNameStr);
LOOP
FETCH keyCursor INTO ofsVolumeDevice;
EXIT WHEN keyCursor%NOTFOUND;
END LOOP;
CLOSE keyCursor;
-- get diskgroup
OPEN dgCursor(l_ofsName,hostNameStr);
LOOP
FETCH dgCursor INTO ofsDiskGroup;
EXIT WHEN dgCursor%NOTFOUND;
END LOOP;
CLOSE dgCursor;
-- get alerts
OPEN asmTargetsCursor(l_ofsName,hostNameStr);
LOOP
FETCH asmTargetsCursor INTO asmTarget;
EXIT WHEN asmTargetsCursor%NOTFOUND;
OPEN alertCursor(asmTarget,l_ofsName);
FETCH alertCursor INTO wCount,cCount;
totalWarningCount := totalWarningCount + wCount;
totalCriticalCount := totalCriticalCount + cCount;
CLOSE alertCursor;
END LOOP;
CLOSE asmTargetsCursor;
OPEN spaceCursor(l_ofsName,hostNameStr);
LOOP
FETCH spaceCursor INTO metricColumn, metricValue;
EXIT WHEN spaceCursor%NOTFOUND;
IF metricColumn='dg_alloc_size_gb' THEN
dgAllocSizeGB := metricValue;
ELSIF metricColumn='ofs_size_gb' THEN
ofsSizeGB := metricValue;
ELSIF metricColumn='ofs_used_gb' THEN
-- we only save the usedGB value which is bigger than 0
-- if it's 0, it means for this host, the OFS is dismounted.
-- For a clustered OFS, the space used should be the same across all hosts
-- so it's all right to override the previous value unless it's 0
IF (metricValue != 0 ) THEN
ofsUsedGB := metricValue;
END IF;
ELSIF metricColumn='ofs_used_pct' THEN
IF (metricValue != 0 ) THEN
ofsUsedPct := metricValue;
END IF;
END IF;
END LOOP;
CLOSE spaceCursor;
END LOOP;
-- If all the OFSs on all the hosts are dismounted or unavailable, and the OFS
-- usage value are 0's, this means this OFS is unavailable cluster-wise.
-- In this case, set the usage (size and pct) to -1 so the UI shows the corresponding
-- "n/a" strings.
IF ((l_hosts_array.COUNT = ofsDownNum) AND (ofsUsedGB = 0) AND (ofsUsedPct = 0)) THEN
ofsUsedGB := -1;
ofsUsedPct := -1;
END IF;
smpClusterOfsObj := SMP_EMD_CLUSTER_OFS_OBJ(
ofsVolumeDevice,
l_ofs_array(i),
ofsUpNum,
ofsDownNum,
ofsMountNum,
ofsDismountNum,
totalWarningCount,
totalCriticalCount,
dgAllocSizeGB,
ofsSizeGB,
ofsUsedGB,
ofsUsedPct);
smpClusterOfsObjArray.EXTEND;
smpClusterOfsObjArray(smpClusterOfsObjArray.COUNT):= smpClusterOfsObj;
END LOOP;
END;
/*
* getOFSSummary
*/
PROCEDURE getOFSSummary(volume_device_in IN VARCHAR2,
ofs_name_in IN VARCHAR2,
hostName_in IN VARCHAR2,
clusterName_in IN VARCHAR2,
lastUpdatedTime OUT VARCHAR2,
current_status_out OUT NUMBER,
avail_on_hosts_out OUT NUMBER,
none_avail_on_hosts_out OUT NUMBER,
corrupt_out OUT VARCHAR2,
volume_name_out OUT VARCHAR2,
servicedHostsArray OUT SMP_EMD_OFSHOSTS_INFO_ARRAY,
ofsusage_cur_out OUT cursorType)
IS
l_target_guid RAW(16);
l_hostName MGMT_TARGET_MEMBERSHIPS.MEMBER_TARGET_NAME%TYPE;
l_clusterName MGMT_TARGET_MEMBERSHIPS.COMPOSITE_TARGET_NAME%TYPE;
hostStatus NUMBER;
ofsStatus MGMT$METRIC_CURRENT.VALUE%TYPE;
ofsState MGMT$METRIC_CURRENT.VALUE%TYPE;
diskGroup MGMT$METRIC_CURRENT.KEY_VALUE2%TYPE;
upSince MGMT$METRIC_CURRENT.VALUE%TYPE;
asmTargetName MGMT$METRIC_CURRENT.TARGET_NAME%TYPE;
asmTarget MGMT$METRIC_CURRENT.TARGET_NAME%TYPE;
asmState NUMBER;
l_number_value NUMBER;
l_status VARCHAR2(20);
l_total_number NUMBER DEFAULT 0;
metric_column MGMT$METRIC_CURRENT.METRIC_COLUMN%TYPE;
metric_value MGMT$METRIC_CURRENT.VALUE%TYPE;
TYPE ValuesArray IS table of VARCHAR2(256);
TYPE NumbersArray IS table of NUMBER;
TYPE RawArray IS table of RAW(16);
l_hosts_array ValuesArray;
l_asm_tguid_array RawArray;
l_ofs_status_array NumbersArray;
l_ofs_hosts_array ValuesArray;
l_servicedHostsInfo SMP_EMD_OFSHOSTS_INFO;
CURSOR numKeyCursor(volDevice VARCHAR2, ofsName VARCHAR2, asm_target_guid VARCHAR2) IS
SELECT COUNT(distinct(a.key_value))
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.key_value=volDevice
AND a.metric_name='OFS_State'
AND a.target_guid=HEXTORAW(asm_target_guid)
AND b.key_value=a.key_value
AND b.metric_name=a.metric_name
AND b.target_guid=a.target_guid
AND b.metric_column='ofs_mountpoint'
AND b.value=ofsName
AND b.collection_timestamp=a.collection_timestamp;
CURSOR hostClusterCursor(host VARCHAR2) IS
SELECT composite_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE member_target_name=host
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE;
CURSOR clusterHostsCursor(clusterName VARCHAR2) IS
SELECT member_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE composite_target_name=clustername
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE;
CURSOR statusCursor(volDevice VARCHAR2,
ofsName VARCHAR2,
hostName VARCHAR2
) IS
SELECT distinct(nvl(trim(a.value), 'NA'))
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.metric_name='OFS_State'
AND a.metric_column='ofs_availability'
AND a.key_value=volDevice
AND a.key_value2=hostName
AND b.target_type = a.target_type
AND b.key_value = a.key_value
AND b.key_value2 = a.key_value2
AND b.metric_name = a.metric_name
AND b.metric_column = 'ofs_mountpoint'
AND b.value=ofsName;
-- Serviced Hosts Info --
CURSOR hostsCursor(volDevice VARCHAR2) IS
SELECT distinct key_value2
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='OFS_State'
AND key_value=volDevice;
CURSOR asmCursor(volDevice VARCHAR2, host VARCHAR2) IS
SELECT distinct target_name
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='OFS_State'
AND key_value=volDevice
AND key_value2=host;
CURSOR lastLoadTimeCursor(asmTarget VARCHAR2) IS
SELECT TO_CHAR(MAX(LAST_LOAD_TIME),'YYYY-MM-DD HH24:MI:SS')
FROM mgmt_targets
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND target_name IN (asmTarget);
CURSOR valueCursor(volDevice VARCHAR2, host VARCHAR2) IS
SELECT metric_column, value
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='OFS_State'
AND metric_column IN ('ofs_availability', 'ofs_uptime', 'ofs_mount_state')
AND key_value=volDevice
AND key_value2=host;
CURSOR valueCursor2(volDevice VARCHAR2) IS
SELECT metric_column, value
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='Single_Instance_OFS_Summary'
AND metric_column IN ('ofs_volume_name', 'ofs_corrupted_state')
AND key_value=volDevice;
CURSOR dgCursor(volDevice VARCHAR2) IS
SELECT key_value2
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='Single_Instance_OFS_Summary'
AND key_value=volDevice;
begin
servicedHostsArray := SMP_EMD_OFSHOSTS_INFO_ARRAY();
l_hosts_array := ValuesArray();
l_asm_tguid_array := RawArray();
l_ofs_hosts_array := ValuesArray();
l_ofs_status_array := NumbersArray();
-- hosts in the cluster
IF (clusterName_in IS NOT NULL) AND (length(trim(clusterName_in)) IS NOT NULL)
THEN
OPEN clusterHostsCursor(clusterName_in);
LOOP
FETCH clusterHostsCursor INTO l_hostName;
EXIT WHEN clusterHostsCursor%NOTFOUND;
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= l_hostName;
END LOOP;
CLOSE clusterHostsCursor;
END IF;
-- see if this host belongs to any cluster
IF (hostName_in IS NOT NULL) AND (length(trim(hostName_in)) IS NOT NULL)
THEN
OPEN hostClusterCursor(hostName_in);
FETCH hostClusterCursor INTO l_clusterName;
CLOSE hostClusterCursor;
END IF;
IF (l_clusterName IS NOT NULL) and (length(trim(l_clusterName)) IS NOT NULL)
THEN
OPEN clusterHostsCursor(l_clusterName);
LOOP
FETCH clusterHostsCursor INTO l_hostName;
EXIT WHEN clusterHostsCursor%NOTFOUND;
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= l_hostName;
END LOOP;
CLOSE clusterHostsCursor;
ELSE
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= hostName_in;
END IF;
-- this host list should be part of the l_hosts_array from above
OPEN hostsCursor(volume_device_in);
LOOP
FETCH hostsCursor INTO l_hostName;
EXIT WHEN hostsCursor%NOTFOUND;
FOR i IN 1..l_hosts_array.COUNT LOOP
IF l_hosts_array(i)= l_hostName THEN
l_ofs_hosts_array.EXTEND;
l_ofs_hosts_array(l_ofs_hosts_array.COUNT):= l_hostName;
END IF;
END LOOP;
END LOOP;
CLOSE hostsCursor;
current_status_out := 0;
avail_on_hosts_out := 0;
none_avail_on_hosts_out := 0;
FOR i in 1..l_ofs_hosts_array.COUNT LOOP
l_hostName := l_ofs_hosts_array(i);
-- General info
OPEN statusCursor(volume_device_in, ofs_name_in,l_hostName);
FETCH statusCursor INTO l_status;
CLOSE statusCursor;
IF l_status='AVAILABLE' THEN
avail_on_hosts_out := avail_on_hosts_out + 1;
l_ofs_status_array.EXTEND;
l_ofs_status_array(l_ofs_status_array.COUNT):= 1;
ELSE
none_avail_on_hosts_out := none_avail_on_hosts_out + 1;
l_ofs_status_array.EXTEND;
l_ofs_status_array(l_ofs_status_array.COUNT):= 0;
END IF;
-- OFS corrupt state
OPEN valueCursor2(volume_device_in);
LOOP
FETCH valueCursor2 INTO metric_column, metric_value;
EXIT WHEN valueCursor2%NOTFOUND;
IF metric_column='ofs_corrupted_state' THEN
corrupt_out:= metric_value;
ELSIF metric_column='ofs_volume_name' THEN
volume_name_out:= metric_value;
END IF;
END LOOP;
CLOSE valueCursor2;
-- target guid for the host name
BEGIN
SELECT target_guid
INTO l_target_guid
FROM mgmt_targets
WHERE target_name = l_hostName
AND target_type = MGMT_GLOBAL.G_HOST_TARGET_TYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE mgmt_global.target_does_not_exist;
END;
hostStatus := mgmt_target.get_avail_current_status(l_target_guid);
-- OFS Status, State
OPEN valueCursor(volume_device_in, l_hostName);
LOOP
FETCH valueCursor INTO metric_column, metric_value;
EXIT WHEN valueCursor%NOTFOUND;
IF metric_column='ofs_availability' THEN
ofsStatus := metric_value;
ELSIF metric_column='ofs_uptime' THEN
upSince := metric_value;
ELSIF metric_column='ofs_mount_state' THEN
ofsState := metric_value;
END IF;
END LOOP;
CLOSE valueCursor;
-- Disk Group
OPEN dgCursor(volume_device_in);
FETCH dgCursor INTO diskGroup;
EXIT WHEN dgCursor%NOTFOUND;
CLOSE dgCursor;
-- ASM Instance
OPEN asmCursor(volume_device_in,l_hostName);
FETCH asmCursor INTO asmTargetName;
EXIT WHEN asmCursor%NOTFOUND;
CLOSE asmCursor;
-- target guid for the asm target
BEGIN
SELECT target_guid
INTO l_target_guid
FROM mgmt_targets
WHERE target_name = asmTargetName
AND target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE;
l_asm_tguid_array.EXTEND;
l_asm_tguid_array(l_asm_tguid_array.COUNT):= l_target_guid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE mgmt_global.target_does_not_exist;
END;
asmState := mgmt_target.get_avail_current_status(l_target_guid);
l_servicedHostsInfo := SMP_EMD_OFSHOSTS_INFO(
l_hostName,
hostStatus,
ofsStatus,
upSince,
ofsState,
asmTargetName,
asmState,
diskGroup);
servicedHostsArray.EXTEND;
servicedHostsArray(servicedHostsArray.COUNT):= l_servicedHostsInfo;
END LOOP;
-- get the ofs status from all the hosts,if one of the status from
-- the status array is up,then the overall ofs status is up
-- get the maximum last upload time for all the asm targets
current_status_out := 0;
FOR n in 1..l_ofs_status_array.COUNT LOOP
IF l_ofs_status_array(n)= 1 THEN
current_status_out := 1;
EXIT;
END IF;
END LOOP;
-- See if this volume device is available in all the asm instances (RAC)
-- If not, then the OFS associated with this volume device doesn't exist anymore (deleted?)
FOR j in 1..l_asm_tguid_array.COUNT
LOOP
OPEN numKeyCursor(volume_device_in, ofs_name_in, l_asm_tguid_array(j));
FETCH numKeyCursor INTO l_number_value;
EXIT WHEN numKeyCursor%NOTFOUND;
l_total_number := l_total_number + l_number_value;
CLOSE numKeyCursor;
END LOOP;
IF l_total_number = 0 THEN
current_status_out := -1; -- OFS doesn't exist
--EXIT;
END IF;
IF clusterName_in IS NULL THEN
SELECT TO_CHAR(MAX(LAST_LOAD_TIME),'YYYY-MM-DD HH24:MI:SS')
INTO lastUpdatedTime
FROM mgmt_targets
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND target_name in (
SELECT distinct target_name
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='OFS_State'
AND key_value=volume_device_in
AND (key_value2=hostName_in OR
key_value2 in (
SELECT member_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE composite_target_name =
(SELECT composite_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE member_target_name=hostName_in
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE))));
END IF;
IF hostName_in IS NULL THEN
SELECT TO_CHAR(MAX(LAST_LOAD_TIME),'YYYY-MM-DD HH24:MI:SS')
INTO lastUpdatedTime
FROM mgmt_targets
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND target_name in (
SELECT distinct target_name
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='OFS_State'
AND key_value=volume_device_in
AND key_value2 in (
SELECT member_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE composite_target_name=clusterName_in
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE));
END IF;
-- OFS space usage history for last 7 days
OPEN ofsusage_cur_out FOR
SELECT TO_CHAR(total.rollup_timestamp,'MM/DD/YY') day,
total.average total,
used.average used
FROM
(SELECT distinct average,key_value,rollup_timestamp
FROM mgmt$metric_daily
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='Single_Instance_OFS_Summary'
AND metric_column='ofs_size_gb'
AND key_value=volume_device_in) total,
(SELECT distinct average,key_value,rollup_timestamp
FROM mgmt$metric_daily
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='Single_Instance_OFS_Summary'
AND metric_column='ofs_used_gb'
AND key_value=volume_device_in) used
WHERE total.key_value = used.key_value
AND total.rollup_timestamp = used.rollup_timestamp
AND total.rollup_timestamp >= to_date(to_char(sysdate-7, 'YYYYMMDD'), 'YYYYMMDD') order by day;
END;
/*
* getNoneOFSVolumesInfo
*/
PROCEDURE getNoneOFSVolumesInfo(targetName IN VARCHAR2,
targetType IN VARCHAR2,
nonOFSVolumesArray OUT SMP_EMD_NONEOFS_VOL_ARRAY)
IS
targetGuid MGMT_TARGETS.TARGET_GUID%TYPE;
volumeName MGMT$METRIC_CURRENT.VALUE%TYPE;
volumeDevice MGMT$METRIC_CURRENT.KEY_VALUE%TYPE;
volumeUsage MGMT$METRIC_CURRENT.VALUE%TYPE;
volumeState MGMT$METRIC_CURRENT.VALUE%TYPE;
diskGroup MGMT$METRIC_CURRENT.KEY_VALUE2%TYPE;
sizeGB MGMT$METRIC_CURRENT.VALUE%TYPE;
dgAllocSizeGB MGMT$METRIC_CURRENT.VALUE%TYPE;
redundancy MGMT$METRIC_CURRENT.VALUE%TYPE;
nonOFSVolumes SMP_EMD_NONEOFS_VOL;
metric_column MGMT$METRIC_CURRENT.KEY_VALUE%TYPE;
metric_value MGMT$METRIC_CURRENT.VALUE%TYPE;
CURSOR aCursor (target_guid_in IN VARCHAR2,
key_value_in IN VARCHAR2
) IS
SELECT metric_column, NVL(value,' ')
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND metric_name = 'Volumes_Summary'
AND metric_column IN ('volume_name', 'volume_status', 'volume_usage','size_gb', 'dg_alloc_size_gb', 'redundancy')
AND key_value=key_value_in;
-- all the volume devices that are not used for OFS
CURSOR keyCursor (target_guid_in IN VARCHAR2) IS
SELECT distinct key_value
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND ((key_value IS NOT NULL) AND (length(trim(key_value)) IS NOT NULL))
AND metric_name = 'Volumes_Summary'
AND metric_column = 'volume_usage'
AND NVL(value, ' ') != 'ACFS'
AND collection_timestamp =
(SELECT MAX(collection_timestamp)
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND metric_name = 'Volumes_Summary'
AND metric_column = 'volume_usage'
AND NVL(value, ' ') != 'ACFS');
-- diskgroups
CURSOR dgCursor (target_guid_in IN VARCHAR2, key_in IN VARCHAR2) IS
SELECT distinct key_value2
FROM mgmt$metric_current
WHERE target_guid = HEXTORAW(target_guid_in)
AND key_value = key_in
AND metric_name = 'Volumes_Summary'
AND NVL(value, ' ') != 'ACFS'
AND metric_column = 'volume_usage';
BEGIN
nonOFSVolumesArray := SMP_EMD_NONEOFS_VOL_ARRAY();
-- target guid
BEGIN
SELECT target_guid
INTO targetGuid
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;
-- volume device --
OPEN keyCursor (targetGuid);
LOOP
FETCH keyCursor INTO volumeDevice;
EXIT WHEN keyCursor%NOTFOUND;
OPEN aCursor (targetGuid,volumeDevice);
LOOP
FETCH aCursor INTO metric_column, metric_value;
EXIT WHEN aCursor%NOTFOUND;
IF metric_column='volume_name' THEN
volumeName := metric_value;
ELSIF metric_column='volume_usage' THEN
volumeUsage := metric_value;
ELSIF metric_column='volume_status' THEN
volumeState := metric_value;
ELSIF metric_column='size_gb' THEN
sizeGB := metric_value;
ELSIF metric_column='dg_alloc_size_gb' THEN
dgAllocSizeGB := metric_value;
ELSIF metric_column='redundancy' THEN
redundancy := metric_value;
END IF;
END LOOP;
CLOSE aCursor;
-- diskgroup --
OPEN dgCursor (targetGuid, volumeDevice);
LOOP
FETCH dgCursor INTO diskGroup;
EXIT WHEN dgCursor%NOTFOUND;
END LOOP;
CLOSE dgCursor;
nonOFSVolumes := SMP_EMD_NONEOFS_VOL(
volumeName,
volumeDevice,
volumeUsage,
volumeState,
diskGroup,
sizeGB,
dgAllocSizeGB,
redundancy);
nonOFSVolumesArray.EXTEND;
nonOFSVolumesArray(nonOFSVolumesArray.COUNT):= nonOFSVolumes;
END LOOP;
CLOSE keyCursor;
END;
/*
* getOFSSpaceInfo
*/
PROCEDURE getOFSSpaceInfo(host_in IN VARCHAR2,
cluster_in IN VARCHAR2,
volume_device_in IN VARCHAR2,
ofs_in IN VARCHAR2,
dg_alloc_size_GB_out OUT NUMBER,
size_GB_out OUT NUMBER,
used_GB_out OUT NUMBER,
free_GB_out OUT NUMBER,
used_pct_out OUT NUMBER,
snap_used_MB_out OUT NUMBER)
IS
l_target_guid MGMT_TARGETS.TARGET_GUID%TYPE;
l_asm_target MGMT$METRIC_CURRENT.TARGET_NAME%TYPE;
l_clusterName MGMT_TARGET_MEMBERSHIPS.COMPOSITE_TARGET_NAME%TYPE;
l_hostName MGMT_TARGET_MEMBERSHIPS.MEMBER_TARGET_NAME%TYPE;
TYPE ValuesArray IS table of VARCHAR2(256);
l_hosts_array ValuesArray;
l_ofs_hosts_array ValuesArray;
CURSOR hostClusterCursor(host VARCHAR2) IS
SELECT composite_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE member_target_name=host
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE;
CURSOR clusterHostsCursor(clusterName VARCHAR2) IS
SELECT member_target_name
FROM MGMT_TARGET_MEMBERSHIPS
WHERE composite_target_name=clusterName
AND composite_target_type=MGMT_GLOBAL.G_CLUSTER_TARGET_TYPE
AND member_target_type=MGMT_GLOBAL.G_HOST_TARGET_TYPE;
CURSOR hostsCursor(volDevice VARCHAR2, ofsName VARCHAR2) IS
SELECT distinct a.key_value2
FROM mgmt$metric_current a, mgmt$metric_current b
WHERE a.target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND a.metric_name='OFS_State'
AND a.key_value=volDevice
AND b.target_type=a.target_type
AND b.metric_name=a.metric_name
AND b.key_value=a.key_value
AND b.metric_column='ofs_mountpoint'
AND b.value=ofsName;
CURSOR asmCursor(volDevice VARCHAR2, host VARCHAR2) IS
SELECT distinct target_name
FROM mgmt$metric_current
WHERE target_type=MGMT_GLOBAL.G_ASM_TARGET_TYPE
AND metric_name='OFS_State'
AND key_value=volDevice
AND key_value2=host;
CURSOR aCursor (target_guid_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
volDevice IN VARCHAR2) IS
SELECT a.value
FROM mgmt$metric_current a
WHERE a.target_guid = HEXTORAW(target_guid_in)
AND a.metric_name = 'Single_Instance_OFS_Summary'
AND a.metric_column = metric_column_in
AND a.key_value = volDevice;
BEGIN
l_hosts_array := ValuesArray();
l_ofs_hosts_array := ValuesArray();
-- hosts in the cluster
IF (cluster_in IS NOT NULL) AND (length(trim(cluster_in)) IS NOT NULL)
THEN
OPEN clusterHostsCursor(cluster_in);
LOOP
FETCH clusterHostsCursor INTO l_hostName;
EXIT WHEN clusterHostsCursor%NOTFOUND;
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= l_hostName;
END LOOP;
CLOSE clusterHostsCursor;
END IF;
-- See if thIShost belongs to any cluster
IF (host_in IS NOT NULL) AND (length(trim(host_in)) IS NOT NULL)
THEN
OPEN hostClusterCursor(host_in);
FETCH hostClusterCursor INTO l_clusterName;
CLOSE hostClusterCursor;
END IF;
IF (l_clusterName IS NOT NULL) AND (length(trim(l_clusterName)) IS NOT NULL)
THEN
OPEN clusterHostsCursor(l_clusterName);
LOOP
FETCH clusterHostsCursor INTO l_hostName;
EXIT WHEN clusterHostsCursor%NOTFOUND;
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= l_hostName;
END LOOP;
CLOSE clusterHostsCursor;
ELSE
l_hosts_array.EXTEND;
l_hosts_array(l_hosts_array.COUNT):= host_in;
END IF;
-- this hot list should be part of the l_hosts_array from above
OPEN hostsCursor(volume_device_in, ofs_in);
LOOP
FETCH hostsCursor INTO l_hostName;
EXIT WHEN hostsCursor%NOTFOUND;
FOR i IN 1..l_hosts_array.COUNT LOOP
IF l_hosts_array(i)= l_hostName THEN
l_ofs_hosts_array.EXTEND;
l_ofs_hosts_array(l_ofs_hosts_array.COUNT):= l_hostName;
END IF;
END LOOP;
END LOOP;
CLOSE hostsCursor;
-- ASM Instance
FOR j IN 1..l_ofs_hosts_array.COUNT LOOP
l_hostName := l_ofs_hosts_array(j);
OPEN asmCursor(volume_device_in, l_hostName);
FETCH asmCursor INTO l_asm_target;
CLOSE asmCursor;
END LOOP;
-- Target guid
BEGIN
SELECT target_guid
INTO l_target_guid
FROM mgmt_targets
WHERE target_name = l_asm_target
AND target_type = MGMT_GLOBAL.G_ASM_TARGET_TYPE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE mgmt_global.target_does_not_exist;
END;
-- diskgroup allocated size GB --
OPEN aCursor (l_target_guid,'dg_alloc_size_gb', volume_device_in);
LOOP
FETCH aCursor INTO dg_alloc_size_GB_out;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- size GB --
OPEN aCursor (l_target_guid,'ofs_size_gb', volume_device_in);
LOOP
FETCH aCursor INTO size_GB_out;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- used GB --
OPEN aCursor (l_target_guid,'ofs_used_gb', volume_device_in);
LOOP
FETCH aCursor INTO used_GB_out;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- free GB --
OPEN aCursor (l_target_guid,'ofs_free_gb', volume_device_in);
LOOP
FETCH aCursor INTO free_GB_out;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- used Pct --
OPEN aCursor (l_target_guid,'ofs_used_pct', volume_device_in);
LOOP
FETCH aCursor INTO used_pct_out;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
-- snapshot space used MB --
OPEN aCursor (l_target_guid,'ofs_snap_used_mb', volume_device_in);
LOOP
FETCH aCursor INTO snap_used_MB_out;
EXIT WHEN aCursor%NOTFOUND;
END LOOP;
CLOSE aCursor;
end;
end emd_osm;
/
show errors;