Management Repository Data Retention Policies

When the various components of Enterprise Manager are configured and running efficiently, the Oracle Management Service gathers large amounts of raw data from the Management Agents running on your managed hosts and loads that data into the Management Repository. This data is the raw information that is later aggregated, organized, and presented to you in the Enterprise Manager console.

After the Oracle Management Service loads information into the Management Repository, Enterprise Manager aggregates and purges the data over time.

The following sections describe:

The default aggregation and purging policies used to maintain data in the Management Repository.
How you can modify the length of time the data is retained before it is aggregated and then purged from the Management Repository.

Management Repository Default Aggregation and Purging Policies

Enterprise Manager aggregates collected metric data by hour and by day to enhance query performance and help minimize the size of the Management Repository. Before the data is aggregated, each data point is stored in a raw metric data table. Once a day, the previous day's raw metric data is rolled up, or aggregated, into a one-hour and a one-day table. These hourly and daily records will have hourly and daily metric data averages, minimums, maximums and standard deviations respectively.

After Enterprise Manager aggregates the data, the data is then considered eligible for purging. A certain period of time must pass for data to actually be purged. This period of time is called the retention time.

The raw data, with the highest insert volume, has the shortest default retention time, which is set to 7 days. As a result, 7 days after it is aggregated into a one-hour record, a raw data point is eligible for purging.

Note:

This data retention policy varies for JVMD and ADP data.

Hourly aggregate metric data records are purged after 32 days. The highest level of aggregation, one day, is kept for 24 months (roughly 730 days).

The default data retention policies are summarized in Table 2-1.

Table 2-1 Default Repository Purging Policies

Aggregate Level	Retention Time
Raw metric data	7 days
Hourly aggregated metric data	32 days
Daily aggregated metric data	24 months

If you have configured and enabled Application Performance Management, Enterprise Manager also gathers, saves, aggregates, and purges response time data. The response time data is purged using policies similar to those used for metric data. The Application Performance Management purging policies are shown in Table 2-2.

Table 2-2 Default Repository Purging Policies for Application Performance Management Data

Aggregate Level	Retention Time
Raw response time data	24 hours
One-hour aggregated response time data	7 days
One-hour distribution response time data	24 hours
One-day aggregated response time data	32 days
One-day distribution aggregated response time data	32 days

If you do not want to keep severity data for the default period (6 months), and want to reduce the retention period for the EVENTS purge policy, you can use the following command:

em_purge.modify_purge_policy_group('EVENTS',NULL,*l_new_purge_hours*);

This command will modify only the purge policy group which will affect all the purge policies associated with that group. Note that if a purge policy is associated with a purge group, the retention period is taken as the retention period of the group. When the retention of a purge policy (associated with a purge policy group) is changed, then the retention is determined from the purge policy and not from the purge policy group.

To modify an individual purge policy use the following command:

em_purge.modify_purge_policy(
  p_policy_name     IN VARCHAR2,
  p_retention_hours IN NUMBER
)

You can modify the purge policy and also the partition retention values by choosing Manage Enterprise Manager from the Setup menu, then selecting Repository. From that page, choose the Schema tab and then make any necessary changes in the Purge Policies section (click Modify) or Partition Retention section.

Events data is partitioned and maintains six months of historical data by default. You can change the default retention period using the procedure described above. The severity data is tied to the events data purge policy and will be adjusted accordingly.

The fixed set of tables affected by this data purge are listed below:

EM_EVENT_SEQUENCES
EM_EVENT_RAW
EM_EVENT_MSGS
EM_EVENT_CONTEXT
EM_EVENT_ANNOTATIONS
EM_EVENTS_INCIDENT
EM_ISSUES_INTERNAL
EM_ISSUES_MSG
EM_ISSUES_ANNOTATIONS
EM_INCIDENT_ISSUE
EM_PROBLEM_ISSUE
EM_INCIDENTS_PROBLEM

The following list is a dynamic set of tables that store data for different event types supported by Enterprise Manager. This list can vary over time as new event types or unsupported event types are added or removed:

EM_EV_CS_RULE_VIOLATION
EM_EV_CS_SCORE
EM_EV_JOB_STATUS_CHANGE
EM_EV_METRIC_ALERT
EM_EV_METRIC_ERROR
EM_EV_MEXT_UPDATE
EM_EV_MNTR_DISRUPTION
EM_EV_SELFUPDATE
EM_EV_SLA_ALERT
EM_EV_TARGET_AVAILABILITY
EM_EV_USER_REPORTED
EM_EV_ADP_ALERT
EM_EV_APM_KPI_ALERT
EM_EV_JVMDIAG_ALERT
EM_EV_HA_EVENT

Management Repository Default Aggregation and Purging Policies for Other Management Data

Besides the metric data and Application Performance Monitoring data, other types of Enterprise Manager data accumulates over time in the Management Repository.

For example, the last availability record for a target will also remain in the Management Repository indefinitely, so the last known state of a target is preserved.

Modifying the Default Aggregation and Purging Policies

The Enterprise Manager default aggregation and purging policies were designed to provide the most available data for analysis while still providing the best performance and least disk-space requirements for the Management Repository. As a result, you should not modify these policies to improve performance or increase your available disk space.

However, if you plan to extract or review the raw or aggregated data using data analysis tools other than Enterprise Manager, you may want to increase the amount of raw or aggregated data available in the Management Repository. You can accomplish this by increasing the retention times for the raw or aggregated data.

A PL/SQL API has been provided to modify the default retention time for the core metric data tables in the Enterprise Manager repository. Table 2-3 shows the default number of partitions retained for each of the three tables and the size of the partitions for each table. The API will allow you to change the number of partitions retained only.

Table 2-3 Core EM Metric Data Tables and Default Data Retention in the Management Repository

Table Name	Partitions Retained	Partition Size
EM_METRIC _VALUES_E	7	DAY
EM_METRIC_VALUES_HOURLY_E	32	DAY
EM_METRIC_VALUES_DAILY_E	24	MONTH

To modify the retention period for any of the above tables, execute the following command:

SQL> execute gc_interval_partition_mgr.set_retention('SYSMAN', <table name>, <number of partitions to retain>);

Replace the <table name> by name of table as listed above. The API will allow you to change the number of partitions retained only.

For example, to modify the default retention time for the table EM_METRIC_VALUES_E from 7 partitions to 14 partitions, follow these steps:

Use SQL*Plus to connect to the repository database as the SYSMAN user.

Check the current value of the retention periods:

SQL> select table_name, partitions_retained
from em_int_partitioned_tables
where table_name in ('EM_METRIC_VALUES_E','EM_METRIC_VALUES_HOURLY_E','EM_METRIC_VALUES_DAILY_E');
 
TABLE_NAME                 PARTITIONS_RETAINED
-------------------------  -------------------
EM_METRIC_VALUES_E                             7
EM_METRIC_VALUES_HOURLY_E                     32
EM_METRIC_VALUES_DAILY_E                      24

To modify the default retention time for the table EM_METRIC_VALUES_E from 7 partitions to 14, execute the following command:

SQL> execute gc_interval_partition_mgr.set_retention('SYSMAN', 'EM_METRIC_VALUES_E', 14);

Verify that the retention period has been modified:

SQL> select table_name, partitions_retained
from em_int_partitioned_tables
where table_name in ('EM_METRIC_VALUES_E','EM_METRIC_VALUES_HOURLY_E','EM_METRIC_VALUES_DAILY_E');
 
TABLE_NAME                PARTITIONS_RETAINED
------------------------- -------------------
EM_METRIC_VALUES_E                           14
EM_METRIC_VALUES_HOURLY_E                    32
EM_METRIC_VALUES_DAILY_E                     24

How to Modify the Retention Period of Job History

Enterprise Manager has a default purge policy which removes all finished job details which are older than 30 days. This section provides details for modifying this default purge policy.

The actual purging of completed job history is implemented via a DBMS_SCHEDULER job that runs once a day in the repository database. When the job runs, it looks for finished jobs that are 'n' number of days older than the current time (value of sysdate in the repository database) and deletes these jobs. The value of 'n' is, by default, set to 30 days.

The default purge policy cannot be modified via the Enterprise Manager console, but it can be changed using SQL*Plus.

To modify this purge policy, follow these steps:

Check the current values for the purge policies using the following command:

SQL> select * from mgmt_job_purge_policies;

POLICY_NAME                      TIME_FRAME
-------------------------------- ----------
SYSPURGE_POLICY                          30
REFRESHFROMMETALINKPURGEPOLICY            7
FIXINVENTORYPURGEPOLICY                   7
OPATCHPATCHUPDATE_PAPURGEPOLICY           7

The purge policy responsible for the job deletion is called SYSPURGE_POLICY. As seen above, the default value is set to 30 days.

To change the time period, you must drop and recreate the policy with a different time frame:

SQL> execute MGMT_JOBS.drop_purge_policy('SYSPURGE_POLICY');
```
PL/SQL procedure successfully completed.
```
SQL> execute MGMT_JOBS.register_purge_policy('SYSPURGE_POLICY', 60, null);
```
PL/SQL procedure successfully completed.
```
SQL> COMMIT;
```
Commit complete.
```
SQL> select * from mgmt_job_purge_policies;
```
POLICY_NAME                      TIME_FRAME
-------------------------------- ---------- 
SYSPURGE_POLICY                          60
....
```

The above commands increase the retention period to 60 days. The time frame can also be reduced below 30 days, depending on the requirement.

You can check when the purge job will be executed next. The actual time that the purge runs is set to 5 AM repository time and can be verified using these steps:

Login to the Repository database using the SYSMAN account.
Execute the following command:
```
SQL> select job_name,
            to_char(last_start_date, 'DD-MON-YY HH24:MI:SS') last_run,
            to_char(next_run_date,   'DD-MON-YY HH24:MI:SS') next_run
from all_scheduler_jobs
where job_name ='EM_JOB_PURGE_POLICIES';
 
JOB_NAME              LAST_RUN           NEXT_RUN
--------------------- ------------------ ------------------
EM_JOB_PURGE_POLICIES                    07-SEP-11 05:00:00
```
The schedule can also be verified from the Enterprise Manager console by following these steps:
1. From the Setup menu, select Management Service, then select Repository.
2. Click the Repository Operations tab.
3. Find the Next Scheduled Run and Last Scheduled Run information for Job Purge in the list.
Please note that the time of the next scheduled execution of the Job Purge does not represent the cutoff time for the retention period; the cutoff time is determined by the purge policy at the time the Job Purge runs.

DBMS_SCHEDULER Troubleshooting

Enterprise Manager uses the database scheduler (dbms_scheduler) to run various processes in the repository. When the dbms_scheduler is stopped or has insufficient resources to operate, the Enterprise Manager processes do not run or are delayed. The following is a list of common causes that may prohibit the dbms_scheduler from running normally.

Job Queue Processes

The dbms_scheduler uses a separate job-queue process for each job it runs. The maximum number of these processes is controlled by the database parameter, job_queue_processes. If all processes are in use, no new jobs will be started.

The following query returns the number of currently running jobs.

SQL> SELECT count(*)
FROM dba_scheduler_running_jobs;

If the count is close to the setting of job_queue_processes, it could mean that Enterprise Manager dbms_scheduler jobs cannot be started (on time). Determine if any of the running dbms_scheduler jobs are stuck and consider increasing the setting for job_queue_processes.

Job Slave Processes

The dbms_scheduler also depends on the setting of the dbms_scheduler property MAX_JOB_SLAVE_PROCESSES. If the number of running dbms_scheduler jobs exceeds this setting, no new jobs will be started. This attribute can be checked using this query.

SQL> SELECT value
FROM dba_scheduler_global_attribute
WHERE attribute_name='MAX_JOB_SLAVE_PROCESSES';

If the count equals the number of running dbms_scheduler jobs, then determine if any of the running dbms_scheduler jobs are stuck and consult the dbms_scheduler documentation about how to adjust this attribute.

DBMS_SCHEDULER Program Disabled

The dbms_scheduler has an attribute that can be set to disable this feature in the database. When set, the Enterprise Manager dbms_scheduler jobs will not run. To check if this attribute has been set (inadvertently), run this query.

SQL> SELECT *
FROM dba_scheduler_global_attribute
WHERE attribute_name = 'SCHEDULER_DISABLED';

When a row is returned, the dbms_scheduler is disabled. Execute dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');

Consult the dbms_scheduler documentation about how to remove this attribute.

Too Many Database Sessions

Each dbms_scheduler job requires two database sessions. When no more sessions are available, Enterprise Manager dbms_scheduler jobs will not run. The following two queries give the maximum number of allowed sessions and the current number of active sessions:

SQL> SELECT value
FROM v$parameter
WHERE name='sessions';

SQL> SELECT count(*)FROM v$session;

When the current number of sessions approaches the maximum, then you should determine if any of the sessions are stuck and consult the Oracle Database documentation about how to increase the maximum number of sessions.

Also the high water mark of the number of sessions may indicate that this issue has played a role in the past:

SQL> select * 
from v$resource_limit 
where resource_name = 'sessions' ;

If the MAX_UTILIZATION column indicates a value that is close the maximum number of sessions, it could explain why some of the Enterprise Manager dbms_scheduler jobs may not have run (on time) in the past.

Insufficient Memory

The database may not be able to spawn a new job queue process when there is insufficient memory available. The following message in the database alert file, Unable to spawn jobq slave processes, in combination with, (free memory = 0.00M), would be indicative of this problem. Please consult the Oracle Database documentation about how to diagnose this memory problem further.