Tag Archives: open office

Generate your own custom graphs and reports from Oracle Enterprise Manager Grid Control 11g’s Repository Data

I like Enterprise Manager. Its lets me see what’s going on and what’s gone on and that’s great. BUT, i know that’s in upper case, the graphs/charts it produces aren’t much use. The look okay but they’re far too small and are limited in a number of ways – the main way being that you tend to only get one metric per graph (unless I’m doing something really here  and someone wants to tell me where I’m going wrong). The other day My boss asked me for some reports on database storage (ie how much space our databases take up) and such like and could he have some nice graphs to put into his PowerPoint presentation.. Okay said I grudgingly.  I merely saved one or two of the bitmap graphs Enterprise Manager produces and sent him those. “These are nice graphs but I want some better graphs. Something that would look good in a presentation,” said he. “Gulp,” said I.

After a bit of mooching around the EM repository database I found that it wasn’t that difficult to get the data I wanted.

I logged into the my the repository data as SYSMAN, had a nose around and found that i needed 3 tables to extract the data I needed to produce a graph for statistics gathered on a daily basis: MGMT_TARGETS, MGMT_METRICS and MGMT_METRICS_1DAY. I chose MGMT_METRICS_1DAY because it contained the daily summary/figures for the data I wanted. I figured these tables would give me daily figures for the data I needed. You can figure out how the tables are joined by looking at the query which should make an appearance quite soon…

I decided that I needed information only or Oracle Databases,  the total size of all datafiles used by my database[s] over a specific time span. I needed to set the following parameters:

  • MGMT_TARGETS.TARGET_NAME = ‘DLIVE’ – this is the name of my Oracle Instance as it is displayed in Enterprise Manager.
  • MGMT_METRICS.TARGET_TYPE = ‘oracle_database’ – this ensures only data for Oracle Databases is returned
  • MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP > TO_DATE(‘2011-JAN-01’, ‘YYYY-MON-DD’) – I wanted my data from the 1st January 2011
  • MGMT_METRICS.METRIC_COLUMN = ‘ALLOCATED_GB’ – we’re only interested in storage allocated to the oracle database
  • MGMT_METRICS.METRIC_NAME = ‘DATABASE_SIZE’ – as i understood things we’re only interested in the size of the database (though the query works without this parameter!)

 

This SQL provided can provide the basis for all reports which make use of Enterprise Manager’s daily roll-up (ie summary) of a particular metric.

SELECT DISTINCT
                      MGMT_METRICS.TARGET_TYPE, MGMT_METRICS.TYPE_META_VER, MGMT_METRICS.METRIC_NAME, MGMT_METRICS.METRIC_COLUMN,
                      MGMT_METRICS.SHORT_NAME, MGMT_METRICS_1DAY.TARGET_GUID, MGMT_METRICS_1DAY.METRIC_GUID,
                      MGMT_METRICS_1DAY.KEY_VALUE, MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP, MGMT_METRICS_1DAY.SAMPLE_COUNT,
                      MGMT_METRICS_1DAY.VALUE_AVERAGE, MGMT_METRICS_1DAY.VALUE_MINIMUM, MGMT_METRICS_1DAY.VALUE_MAXIMUM,
                      MGMT_TARGETS.TARGET_NAME
FROM                  MGMT_METRICS, MGMT_METRICS_1DAY, MGMT_TARGETS, MGMT_TARGET_TYPES
WHERE                MGMT_METRICS.METRIC_GUID = MGMT_METRICS_1DAY.METRIC_GUID AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGETS.TARGET_TYPE AND
                      MGMT_METRICS_1DAY.TARGET_GUID = MGMT_TARGETS.TARGET_GUID AND
                      MGMT_METRICS.TYPE_META_VER = MGMT_TARGETS.TYPE_META_VER AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGET_TYPES.TARGET_TYPE

Here’s the SQL with my parameters added:

SELECT DISTINCT
                      MGMT_METRICS.TARGET_TYPE, MGMT_METRICS.TYPE_META_VER, MGMT_METRICS.METRIC_NAME, MGMT_METRICS.METRIC_COLUMN,
                      MGMT_METRICS.SHORT_NAME, MGMT_METRICS_1DAY.TARGET_GUID, MGMT_METRICS_1DAY.METRIC_GUID,
                      MGMT_METRICS_1DAY.KEY_VALUE, MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP, MGMT_METRICS_1DAY.SAMPLE_COUNT,
                      MGMT_METRICS_1DAY.VALUE_AVERAGE, MGMT_METRICS_1DAY.VALUE_MINIMUM, MGMT_METRICS_1DAY.VALUE_MAXIMUM,
                      MGMT_TARGETS.TARGET_NAME
FROM                  MGMT_METRICS, MGMT_METRICS_1DAY, MGMT_TARGETS, MGMT_TARGET_TYPES
WHERE                 MGMT_METRICS.METRIC_GUID = MGMT_METRICS_1DAY.METRIC_GUID AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGETS.TARGET_TYPE AND
                      MGMT_METRICS_1DAY.TARGET_GUID = MGMT_TARGETS.TARGET_GUID AND
                      MGMT_METRICS.TYPE_META_VER = MGMT_TARGETS.TYPE_META_VER AND
                      MGMT_METRICS.TARGET_TYPE = MGMT_TARGET_TYPES.TARGET_TYPE AND (MGMT_METRICS.TARGET_TYPE = 'oracle_database') AND
                      (MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP > TO_DATE('2011-JAN-01', 'YYYY-MON-DD')) AND
                      (MGMT_METRICS.METRIC_COLUMN = 'ALLOCATED_GB') AND (MGMT_METRICS.METRIC_NAME = 'DATABASE_SIZE')
ORDER BY              MGMT_METRICS_1DAY.ROLLUP_TIMESTAMP, MGMT_METRICS_1DAY.SAMPLE_COUNT

The next thing i did was to import this data into Excel, created a pivot table with chart and filtered the data to suit my needs – my boss was after information on only once of our instances but I thought as I was doing this and it was a useful exercise I’d bring back data for all of my databases and knock out a graph fit for a King. You can see the graph here. NB: I filtered the data so that only DLIVE – the database my boss was interested in is displayed. I should warn you: don’t get too excited.. though I did add a trend line for extra added excitement so beware… 🙂

You can read how to import the data into Excel and produce some fetching graphs and charts here.

Example Enterprise Manager Report in Excel
Enterprise Manager Data Imported into Excel and Presented as a Custom Graph

 

 

Now, I thought that was a useful exercise because there’s a whole load of data (and information) held in the walls of Enterprise Manager but displaying that data how you want the data to be displayed is a different matter all together. I find the graphs in Enterprise Manager to be too small and of very little use when writing my monthly summaries for my Boss. Anyway, he knows how to create his own graphs now so a little bit of work has succeeded in getting him off my back. This was my first custom graph and there are loads more to come. I’ll post details if anyone is interested. or even document the full process and post that if there’s a demand for it.

 

Seamus