Home > Oracle 11g, Oracle Grid Control > Using EM 12c repository to find ASM growth rate

Using EM 12c repository to find ASM growth rate

ASM disk group growth from EM 12c repository

Our company collocates multiple databases on Linux RAC grids to reduce hardware, OS and HA license expenses. All the databases on a grid are sharing few ASM disk groups.

Sharing disk groups helps to reduce storage overallocation commonly seen when each database is running on its dedicated storage, be it file system-based or ASM-based. Of course, the databases continue to grow, but the hope is that with shared storage the overallocation can be reduced due to extremes canceling out each other.

In this environment, when an ASM disk group usage does grow, the question becomes how to track which database is causing most of the growth.

Unfortunately EM 12c ASM disk group page shows only last 7 days of individual database sizes in a particular data group. For capacity planning purposes you need more than that.

One solution is to query EM 12c repository directly.

Recently I described an easy way to extract historical metrics data from EM 12c repository by running 3 simple sqls.

First, find the target name from mgmt_targets. In my case the target is clustered ASM and for weird reason in repository it has type of ‘osm_cluster’.

select target_name from mgmt_targets where target_type='osm_cluster'

Next, review available metrics for the target in mgmt$metric_daily and pick up the one that suits your needs. In my case ‘Total Bytes’ seems to be a good metrics which shows size of each database within a particular datagroup:

    select target_name, column_label, key_value, key_value2, average, rollup_timestamp  
    from mgmt$metric_daily
    where target_name = '+ASM_vtgfdclu' and key_value = 'VTG_DG_DATA02' 
    and column_label = 'Total Bytes' and trunc(rollup_timestamp) = trunc(sysdate)-1;

TARGET_NAME     COLUMN_LABEL   KEY_VALUE      KEY_VALUE2     AVERAGE  ROLLUP_TIMESTAMP
--------------- -------------- -------------- ----------- ----------  ----------------
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  WZCTE       6464471040  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  WZPRD       7977880780  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  NSAPRD      1165345423  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  NSLPRD      1319856810  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  VTGPRD      9841619763  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  WPOPRD      2524436234  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  WTACTE      1028548198  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  WTAPRD      7549327769  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  DB_UNKNOWN     1048576  1/8/2013
+ASM_vtgfdclu   Total Bytes    VTG_DG_DATA02  ASM           26214400  1/8/2013

As we see, there are 8 different databases using this data group plus DB_UNKNOWN  plus ASM.

Finally, pull the historical records:

      select key_value2, rollup_timestamp, average from mgmt$metric_daily
      where target_name = '+ASM_vtgfdclu' and key_value = 'VTG_DG_DATA02' and column_label = 'Total Bytes'

KEY_VALUE2    ROLLUP_TIMESTAMP    AVERAGE
------------- ---------------- ----------
WZCTE         4/29/2012        2457727711
WZCTE         4/30/2012        2403685717
WZCTE         5/1/2012         2322311850
WZCTE         5/2/2012         2350104227
...
WZPRD         4/29/2012        6840267235
WZPRD         4/30/2012        6840804966
WZPRD         5/1/2012         6840968806
WZPRD         5/2/2012         6842801722
...

and so on for all 8 individual databases plus DB_UNKNOWN  and ASM.

To make this long printout more manageable, it is helpful to pivot the report so that each database had its own column and all databases sizes for specific date  were on single line. A new 11g PIVOT sql operator lets us do this quite simple:

with pivot_data AS (
     select key_value2, rollup_timestamp, average
     from mgmt$metric_daily
     where target_name = '+ASM_vtgfdclu' and key_value = 'VTG_DG_DATA02' and column_label = 'Total Bytes'
     )
select * from pivot_data
pivot
     ( sum(average/1024/1024/1024)
       for key_value2
       in ( 'WZCTE','WZPRD','NSAPRD','NSLPRD','VTGPRD','WPOPRD','WTACTE','WTAPRD','DB_UNKNOWN','ASM' )
      )
order by rollup_timestamp; 

ROLLUP_TIMESTAMP    'WZCTE'    'WZPRD'   'NSAPRD'   'NSLPRD'   'VTGPRD'   'WPOPRD'   'WTACTE'   'WTAPRD' 'DB_UNKNOWN'      'ASM'
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------ ----------
4/29/2012        2.28893729 63.7049529 100.714843 14.9365234 106.435546 2.31933593 2.29770132 2.38754507
4/30/2012        2.23860677 63.7099609 100.714843 16.0060628 106.436055 2.31933593 2.30401611 2.39467366 0.0009765625
5/1/2012         2.16282145 63.7114868 100.714843 18.9360656 106.468200 2.33072916 2.23235066 2.40332031 0.0009765625
5/2/2012         2.18870511 63.7285571 100.714843  23.484375 106.513152 2.36639793 2.18984790 2.40332031 0.0009765625
5/3/2012         2.19140625 63.7294921 100.714843  23.484375 106.513671 2.36816406 2.19140625 2.40332031 0.0009765625
5/4/2012         2.19140625 63.6411236 100.714843  23.484375 106.519905 2.36816406 2.19140625 2.40332031 0.0009765625 0.02438616
5/5/2012         2.19140625 63.7294921 100.714843  23.484375 106.559346 2.36846923 2.19140625 29.4923706 0.0009765625 0.02441406
5/6/2012         2.19140625 63.7294921 100.714843  23.484375 106.582031 2.37792968 2.19140625 98.1362356 0.0009765625 0.02441406
...

This pivoted printout is much easier to analyze and build a chart on it in Excel.

As we can see, EM 12c can still be used for Capacity Planning purposes despite its web interface shortcomings.

In places where EM is already deployed and agents are collecting data from target databases, the EM repository can provide valuable historical data without setting up custom data collection scripts.

ASM disk group growth from EM 12c repository

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: