Home > Oracle Grid Control > Extracting data from OEM 12c repository for database Capacity Planning

Extracting data from OEM 12c repository for database Capacity Planning

Recently I was asked to give recommendation for next year business case on buying additional storage to accommodate database growth.

With several dozen of databases to support, there is simply no time to instrument each db with custom-built capacity planning scripts.

Since I had already invested considerable amount of time and energy in setting up EM 12c system to monitor my databases, I turned to OEM metrics to see if there is something useful being collected there.

I encountered two issues when looking at the EM.

First, to my surprise i could not find database size metrics anywhere in the web interface. There were tablespace-level used space metrics, but not full database size. This is such an obvious and important metrics that I hope that it is just me missing something.

Next issue was that metrics pages never display data beyond last 31 days. It turned out that OEM 12c has a bug in which when you go to a metric page it does not display historical data beyond last 31 days. This is confirmed as bug on support.oracle.com (need to find the bug #). The repository has the data, but the web page rendering simply does not display it. Needless to say that 31 days is not anywhere near the time frame needed for long-term capacity planning.

I decided to look directly into the repository and bypass the web interface.

Below are 3 simple repository queries.

In my case I found metrics ‘Used Space(GB)’ perfectly covering my needs, however the same approach can be used to retrieve any other metrics from the repository.

-- 1. find target name
select target_name from mgmt_targets 
where target_type='rac_database';

  Use target_type='oracle_database' if the target is non-rac database.

-- 2. find good metric
select * from mgmt$metric_daily 
where target_name = <target_name from="" query="" 1="">
and trunc(rollup_timestamp) = trunc(sysdate)-1;

-- 3. get metric history
select rollup_timestamp, average
from mgmt$metric_daily 
where target_name = <target_name from="" query="" 1="">
and column_label = 'Used Space(GB)'
order by rollup_timestamp;

Having retrieved the historical data it was easy to paste it in the Excel and draw a chart:

db growth from EM 12c repository

Advertisements
  1. Bobby
    November 12, 2014 at 5:44 am

    Can you please tell what need to provide inside this string

  2. Bobby
    November 12, 2014 at 6:10 am
  3. Nitin Nikhare
    November 3, 2015 at 3:55 pm

    You can put database target name inside this string.

    select rollup_timestamp, average
    from mgmt$metric_daily
    where target_name = orcl’
    and column_label = ‘Used Space(GB)’
    order by rollup_timestamp;

  4. Siva
    December 19, 2015 at 10:00 am

    Hi,

    With reference to your query i have created the allocated space, used space for all databases in EM, but i am not able to merge both in same query while JOIN both query there was no unique value in column, so it produces the multiples value, If possible give solution for this.
    Query to get both total space, used space in same query.

    select target_name, rollup_timestamp as On_date_of, average as Used_Space_Average from mgmt$metric_daily where column_label = ‘Used Space(GB)’ and rollup_timestamp like ’01-%’ order by rollup_timestamp;

    select target_name, rollup_timestamp as On_date_of, average as Allocated_Space_Average from mgmt$metric_daily where column_label = ‘Allocated Space(GB)’ and rollup_timestamp like ’01-%’ order by rollup_timestamp;

  1. January 9, 2013 at 3:52 pm

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

%d bloggers like this: