If you’re managing an Oracle Database and utilizing Automatic Storage Management (ASM), monitoring the usage and health of your disk groups is crucial for ensuring optimal performance and reliability. In this blog post, we’ll explore an Oracle Enterprise Manager (OEM) query that can be used to build a comprehensive ASM Storage Disk Group dashboard.

Understanding the Query

The provided query leverages Oracle Enterprise Manager’s monitoring capabilities to gather key metrics related to ASM disk groups. Let’s break down the main components of the query:

1. Query Structure: The query is structured to retrieve various metrics related to ASM disk groups from the sysman.mgmt$metric_current table.
2. Metric Selection: Metrics such as total space, usable total space, free space, usable free space, and percent used are collected and aggregated for each disk group.
3. Calculation: The query calculates the percentage of free space (PCT_FREE) based on the PERCENT_USED metric.
4. Grouping and Ordering: Results are grouped by host name, target name, and disk group, and ordered by host name, target name, disk group, and percent used in ascending order.

Implementation

SELECT SUBSTR(t.host_name, 1, INSTR(t.host_name, ‘-‘) – 1) AS host_name, mc.target_name, mc.diskgroup, MAX(DECODE(seq,6,CEIL(VALUE / 1024))) AS TOTAL_GB, MAX(DECODE(seq,9,CEIL(VALUE / 1024))) AS USABLE_TOTAL_GB, MAX(DECODE(seq,3,CEIL(VALUE / 1024))) AS FREE_GB, MAX(DECODE(seq,8,CEIL(VALUE / 1024))) AS USABLE_FREE_GB, MAX(DECODE(seq,4,VALUE)) AS PERCENT_USED FROM ( SELECT target_name,key_value AS diskgroup,VALUE,metric_column,ROW_NUMBER() OVER (PARTITION BY target_name, key_value ORDER BY metric_column) AS seq FROM sysman.mgmt$metric_current WHERE target_type IN (‘osm_instance’, ‘osm_cluster’) AND metric_column IN (‘free_mb’,’usable_file_mb’,’type’,’usable_total_mb’,’percent_used’,’diskCnt’) OR (metric_column = ‘total_mb’ AND metric_name = ‘DiskGroup_Usage’)) mc JOIN sysman.mgmt$target t ON mc.target_name = t.target_name WHERE t.target_type IN (‘osm_instance’, ‘osm_cluster’) GROUP BY SUBSTR(t.host_name, 1, INSTR(t.host_name, ‘-‘) – 1), mc.target_name, mc.diskgroup ORDER BY 1,2,3,4 DESC :

1. Log in to Oracle Enterprise Manager.
2. Navigate to the SQL Monitoring or Custom Query section.
3. Copy and paste the provided query into the SQL editor.
4. Execute the query to retrieve ASM storage disk group metrics.
5. Optionally, you can save the query as a custom report or dashboard for easy access.

By regularly running this query and visualizing the results through Oracle Enterprise Manager’s reporting and dashboarding features, you can gain valuable insights into the health and performance of your ASM storage disk groups.

Conclusion

In this blog post, we’ve explored an Oracle Enterprise Manager query designed to build a comprehensive ASM Storage Disk Group dashboard. By leveraging this query and Oracle Enterprise Manager’s monitoring capabilities, database administrators can effectively monitor and manage ASM disk group usage and performance.

For more information on ASM storage management and Oracle Enterprise Manager, refer to the official Oracle documentation and support resources.