EM12c ASM Storage Report

Below the SQL statement will get you a nice ASM report from EM12c data.

Follow me on Twitter

Below is a sample report I have in my apex application op top of my EM12c repository.

Columns:
target_name- ASM target
dsikgroup- diskgroup name
percent_used- percent of diskgroup space used
total_gb- total size of disk group in gb
usable_total_gb- total space used for diskgroup in gb
free_gb- free space for diskgroup in gb
usable_free_gb- usable free space for diskgroup
no_of_disk- number of disk in disk group
lun_size- lun size
rebal_pending- pending rebalance operations
imbalance- will show imbalance in diskgroup

-----------SQL Statement-----------------------

SELECT target_name,
         diskgroup,
         MAX (DECODE (seq, 7, VALUE)) REDUNDANCY,
         MAX (DECODE (seq, 4, VALUE)) PERCENT_USED,
         MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB,
         MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB,
         MAX (DECODE (seq, 3, ceil(VALUE/1024))) FREE_GB,
         MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB,
         MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK,
         ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, VALUE)))) LUN_SIZE,
         MAX (DECODE (seq, 5, decode(VALUE,'No','',value))) REBAL_PENDING,
         MAX (DECODE (seq, 1, VALUE)) IMBALANCE
    FROM (SELECT target_name,
                 key_value diskgroup,
                 VALUE,
                 metric_column,
                 ROW_NUMBER ()
                 OVER (PARTITION BY target_name, key_value
                       ORDER BY metric_column)
                    AS seq
            FROM MGMT$METRIC_CURRENT
          WHERE        target_type in ('osm_instance','osm_cluster')
                   AND metric_column IN
                          ('rebalInProgress',
                           'free_mb',
                           'usable_file_mb',
                           'type',
                           'computedImbalance',
                           'usable_total_mb',
                           'percent_used','diskCnt')
                OR (    metric_column = 'total_mb'
                    AND metric_name = 'DiskGroup_Usage'))
GROUP BY target_name, diskgroup
order by 1,2

Note: If you are going to use another account other than SYSMAN to select you will need to have the following privilege.

grant select on MGMT$METRIC_CURRENT to <username>;
grant exempt access policy to <username>;

 ORA-01722 Invalid Number
If you are seeing the error above this means that one of you columns in you environment has varchar data and not a number. You will need to troubleshoot to find the column with the issue by commenting out one column at a time like the below. I find that seq6 or seq5 sometime have varchar data and numbers.

SELECT target_name,
         diskgroup,
         MAX (DECODE (seq, 7, VALUE)) REDUNDANCY,
         MAX (DECODE (seq, 4, VALUE)) PERCENT_USED,
         --MAX (DECODE (seq, 6, ceil(VALUE/1024))) TOTAL_GB,
         MAX (DECODE (seq, 9, ceil(VALUE/1024))) USABLE_TOTAL_GB,
         MAX (DECODE (seq, 3, ceil(VALUE/1024))) FREE_GB,
         MAX (DECODE (seq, 8, ceil(VALUE/1024))) USABLE_FREE_GB,
         MAX (DECODE (seq, 2, VALUE)) NO_OF_DISK,
         ceil(( MAX (DECODE (seq, 6, ceil(VALUE/1024)))) /(MAX (DECODE (seq, 2, VALUE)))) LUN_SIZE,
         MAX (DECODE (seq, 5, decode(VALUE,'No','',value))) REBAL_PENDING,
         MAX (DECODE (seq, 1, VALUE)) IMBALANCE
    FROM (SELECT target_name,
                 key_value diskgroup,
                 VALUE,
                 metric_column,
                 ROW_NUMBER ()
                 OVER (PARTITION BY target_name, key_value
                       ORDER BY metric_column)
                    AS seq
            FROM MGMT$METRIC_CURRENT
          WHERE        target_type in ('osm_instance','osm_cluster')
                   AND metric_column IN
                          ('rebalInProgress',
                           'free_mb',
                           'usable_file_mb',
                           'type',
                           'computedImbalance',
                           'usable_total_mb',
                           'percent_used','diskCnt')
                OR (    metric_column = 'total_mb'
                    AND metric_name = 'DiskGroup_Usage'))
GROUP BY target_name, diskgroup
order by 1,2