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