Thursday, June 13, 2013

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

25 comments:

  1. Hi Javier
    thanks for sharing
    it works great for me but i needed to change 'osm_instance' by 'osm_cluster' in 12c

    Thanks

    ReplyDelete
  2. The offices are flawless and well lit which was significant for me. I would like to head off to some dirty place where I couldn't see who was around the corner. They are proficient and amicable and the best part - they offered newly prepared treats!! Now who doesn't cherish treats?
    self storage in Darlinghurs

    ReplyDelete
  3. Why do you use the MAX function in your DECODE statements?

    ReplyDelete
  4. Hi Javier,

    we've encountered the following error:
    ora-01722

    Can you explain us what we have to do?
    Thanks in advance
    Best Regards

    ReplyDelete
  5. When are you seeing this error and what are you executing when you get this error?

    ReplyDelete
  6. Hi Javier,

    we are seeing this error when executing the following select as SYSMAN:SELECT target_name,
    diskgroup,
    MAX (DECODE (seq, 7, VALUE)) REDUNDANCY,
    MAX (DECODE (seq, 4, ceil(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, to_number(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,
    CASE
    WHEN (VALUE > 1 AND diskgroup NOT LIKE '%VOTE%') THEN VALUE
    ELSE ''
    END)) 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 = 'osm_instance'
    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

    ReplyDelete
  7. I have seen this is well seems to be when a disk group size is small I have not had a chance to investigate yet.

    ReplyDelete
  8. I am working with another user that is seeing the same issue with error ora-01722 once I have a resolution I will post an update.

    ReplyDelete
  9. hi Javier
    I am interested in your DBA repository APEX application
    Can you share it to me?

    ReplyDelete
  10. Hi Javier, I tried it on my OEM, but get the following error: ORA-01722: invalid number. Any idea?

    ReplyDelete
  11. For those who have been getting error ORA-01722: invalid number please see my update in the blog post I found the issue and have second query that can be used.

    ReplyDelete
    Replies
    1. Hello Javier,

      I got the above error when I executed your query and I do not see updated query in the blog. Help me to get the asm storage report in OEM 12c.

      Delete
    2. The query in the blog post is correct I updated the query several weeks back. The issue you are seeing is because in your environment one of the columns has varchar data versus number. The fast way to find the column is to run the query and comment out each column at one time.

      Delete
    3. Thank you Javier. Let me try...

      Delete
  12. Thanks much Javier. It helped me.

    ReplyDelete
  13. can someone send the url to the blog post with the fix?

    ReplyDelete
    Replies
    1. Armando did you try the query in this post I updated the query a few back which should not get the error anymore

      Delete
    2. I was actually getting that error because of something I added in.
      At some point I did get the ORA- error and left the report alone, for a while but now I fixed my bugs and no longer getting errors.
      I would love to see any library of capabilities you might have or extent OEM 12C reporting, especially CPU and MEMORY Utilization.
      If you have the base sql's, I can build a apex app on top of that with graphs and charts to present to mgmt as they will not want to access OEM for reporting.

      I imagine being able to send daily charts upon threshold being met?
      Thoughts, is OEM better at that then custom reports in APEX?

      Delete
  14. Hi,
    i need the query with server level usage of asm, it is showing the diskgroup level usage, please send me the query with server level(addition of all diskgroup and its usage)

    Regards
    Siva

    ReplyDelete
    Replies
    1. Siva,
      Can you please explain what you mean by server level usage for ASM?

      Delete
    2. by your query i get the result like below
      +ASM_L1774.kpnnl.local DG_DATA1 EXTERN 68.382 1,192 377
      +ASM_L1774.kpnnl.local DG_FRA1 EXTERN 19.507 103 83

      but i need result like addition of the above things
      +ASM_L1774.kpnnl.local 1,295

      i need total ASM size for the server and server name..
      Please do the needfull..

      Delete
    3. Is your server name L1774? based on your output the DG_DATA1 has a total of 68G allocated to that diskgroup on ASM target L1774

      Delete
  15. thanks for the information, I have a query, Is there any way can I find mapping information between ASM disk to HOST Disk using Management views

    ReplyDelete
  16. Thanks for the information . It was really very helpful!!

    ReplyDelete

About Me

My photo

Senior DBA with over 16 years experience, specializing in "Database Performance Tuning" and High Availability (RAC, Data Guard & Oracle Golden Gate).