Changing OEM12c Repository retention settings

Today, I’ve been working on a script to pull some sizing/metric information out of the Oracle Enterprise Manager (OEM) repository database.  As I was working through (pulling my hair out…lol) the details; I had a discussion with an good friend.  He mentioned to me that I needed to change  the retention setting on the repository for usage with the script (I was working on) and long-term historical tracking.   Although, I already knew this needed to be done, it took some poking around to find the document number in MOS.

In case you want to check out the document or documentation, you can find this information at these points of interest:

http://docs.oracle.com/cd/E25178_01/doc.1111/e24473/repository.htm
MOS Doc ID: 1405036.1

Armed with the documentation and a few more grey hairs, I took at look at what default settings were configured after building the repository.  The script in Listing 1 will display what the current/default retention settings are.

Listing 1: Script for retention settings
select table_name, partitions_retained
from em_int_partitioned_tables
where table_name in (‘EM_METRIC_VALUES’,’EM_METRIC_VALUES_HOURLY’,’EM_METRIC_VALUES_DAILY’);

After running the script in Listing 1, your output should tell you if you are configured with the default settings.  The default settings for retention are 7, 32 and 12.  Figure 1 shows you the output from the environment I’m working in.  As you can see, the retention settings have not been changed.

Figure 1: Current retention settings

image

These numbers may look a bit funny when you first look at them.  Before changing theses settings it is good to understand what these values mean.  The values for EM_METRIC_VALUES and EM_METRIC_VALUES_HOURLY are displayed in DAYS  The value for EM_METRIC_VALUES_DAILY is displayed in MONTH.

For tracking and historical purposes, these settings are a bit low.  Most organizations will want to have data that is retained for longer period of times; mostly due to security and compliance reasons.  Now, this brings up the topic of how to change these values.

Oracle has provided a PL/SQL API to allow these changes to be done.  The SQL statements in Listing 2 shows how this API is used for each of the retention changes.

Listing 2: PL/SQL API to change retention settings

BEGIN
  gc_interval_partition_mgr.set_retention(‘SYSMAN’, ‘EM_METRIC_VALUES’, 10);
END;
/

BEGIN
  gc_interval_partition_mgr.set_retention(‘SYSMAN’, ‘EM_METRIC_VALUES_HOURLY’, 90);
END;
/

BEGIN
  gc_interval_partition_mgr.set_retention(‘SYSMAN’, ‘EM_METRIC_VALUES_DAILY’, 36);
END;
/

Changes that were made by the PL/SQL API, the retention setting for the OEM repository have been changed.  Figure 2, shows you the updated information that the repository will use.

Figure 2: Updated retention settings

image

With the updated settings, the OEM repository will now keep raw metric data for 10 days, hourly metric data for 90 days (3 months) and keep all the daily metric data for 36 months (3 years).  This increase in retention should help with reporting on growth of monitored targets over the long term.

Enjoy!

twitter: @dbasolved

blog: http://dbasolved.com

About these ads

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

HeliFromFinland

Heli's Oracle thoughts

Julian Dontcheff's Database Blog

The good DBA is one who learns from his mistakes, the best DBA is one who learns from other DBA's mistakes

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Follow

Get every new post delivered to your Inbox.

Join 1,599 other followers

%d bloggers like this: