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:
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

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


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

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

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

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

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


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.


twitter: @dbasolved


Leave a Reply

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

You are commenting using your 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

Mrs. Jaye's Class Blog

Coordinate Algebra & Advanced Algebra

Pardy DBA

ORA-00001: unique constraint (ORA.BLOG_TAGLINE_PK) violated

Martin Widlake's Yet Another Oracle Blog

Oracle performance, Oracle statistics and VLDBs


Heli's thoughts on Database Designing, Oracle SQL Developer Data Modeler, User Groups etc.

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

The 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...

Stuff that interests me, if not you!

%d bloggers like this: