In my shop, we completed installing OEM agents on all the nodes to be monitored a few months back. However, the MGMT_TABLESPACE continues to grow. I figured that could be reasonable since I didn’t know the details of data retention parameters that govern when data gets purged. If a purge parameter was set to 6 months, but we had only reached our high number of targets 3 months ago, then naturally the database would keep growing.

I found these two links that explain purge parameters nicely.
How to change retention parameters for metrics history
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=430830.1
How to change retention parameters for job history
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=604978.1

There are three tables that contain the bulk of the data in the OEM tablespace MGMT_TABLESPACE. They are MGMT_METRICS_RAW, MGMT_METRICS_1HOUR and MGMT_METRICS_1DAY. They contain partitions that are named after time intervals, and OEM takes care of adding and dropping them. The default retention period for the raw data is 7 days, for the 1hour aggregate is 31 days and for the 1day aggregate is 365 days.

If you want to change the 7 day default period for MGMT_METRICS_RAW to 3 days, you must insert a record into the MGMT_PARAMETERS table to add the parameter mgmt_raw_keep_window.
INSERT INTO MGMT_PARAMETERS (PARAMETER_NAME, PARAMETER_VALUE) VALUES (‘mgmt_raw_keep_window’,’3′);
The companion parameters for the other two tables are mgmt_hour_keep_window and mgmt_day_keep_window.

Advertisements