This post is the third in a short series about Oracle 11g ADR. These posts are based on the presentations I gave at the NoCOUG Summer Conference in August 2011 and at the RMOUG Training Days in February 2012.

One of the benefits of the new ADR system is the automatic file maintenance (archiving and cleanup) that I mentioned earlier. Depending on the type of file it is, XML log and trace file maintenance is governed by either the long or short purge policy. The values of these policies can be observed from the ADRCI command "show control", e.g.:


adrci> show control

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           720                  8760                 2012-03-28 09:46:07.813608 -05:00        2012-09-18 22:19:08.939553 -05:00                                                 1                    2                    80                   1                    2012-03-28 09:46:07.813608 -05:00


I've highlighted the column names and values in red. It doesn't format very well in the terminal either.

Let's break it down.



Short Purge Policy - SHORTP_POLICY

The short purge policy governs files that usually need to be retained for only a short period of time. This includes trace files, core dumps and packaging information files (from IPS, which we'll cover later).

This policy is identified by the name "SHORTP_POLICY" and the value is represented in hours. The default SHORTP_POLICY is 720 hours, or 30 days. The maximum value is 35,791,394 hours, which is over 4,000 years. Hopefully you don't need any more time than this to retain your files. You can set the value to 0 to indicate that all files under this policy are immediately available for purging.

To set this value, you must use the "set control" command in ADRCI. For example, to set it to 168 hours (7 days), we would use this command:

adrci> set control (shortp_policy=168);

Long Purge Policy - LONGP_POLICY

Alert logs, incident info and incident dump files are all governed by the long purge policy, known by LONGP_POLICY in the ADR. The default retention time is 8,760 hours, or 365 days. It has the same maximum value as the short purge policy, and similar behavior when the value is set to zero. Similarly, to set the value for LONGP_POLICY to 1,440 hours (60 days):

adrci> set control (longp_policy=1440);

You can use show control again to view the new settings:

adrci> show control;

ADR Home = /home/oracle/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY         LAST_MOD_TIME                            LAST_AUTOPRG_TIME                        LAST_MANUPRG_TIME                        ADRDIR_VERSION       ADRSCHM_VERSION      ADRSCHMV_SUMMARY     ADRALERT_VERSION     CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1335663986           168                  1440                   2012-12-28 14:18:02.894677 -06:00        2012-09-18 22:19:08.939553 -05:00                                                 1                    2                    80                   1                    2012-03-28 09:46:07.813608 -05:00
1 rows fetched

Purge Job

Now just because a file is eligible to be purged doesn't mean it will be purged soon. It means they will be purged when the instance's purge job runs next, and that could take up to a week. The purge job is an MMON slave task, and only runs once every 7 days, according to MOS Doc ID 975448.1. So don't play around with the long or short policy values thinking they will trigger an instant cleanup (like you would expect if you changed the FRA size below the current used space).

There is however a known bug (9500575) that affects Oracle 11.1.0.7 & 11.2.0.1 where alert log XML files for listeners are not automatically purged. This has the potential then to fill up the disk where these logs are written to.

PURGE Command

You can manually purge files from ADRCI with the PURGE command. One common use is to purge files of a certain type older than a certain age. For example, to overcome the listener log purge bug that was just mentioned, you could schedule this to run weekly (ADRCI can be called from the command-line or cron with commands to run):

adrci> purge -age 10080 -type alert;

This purges all alert logs older than 10,080 minutes. Note that the -age parameter here is in minutes, where as the policy values are in hours.

Similarly you could choose to purge all files for a specific incident ID (assuming you are certain you don't need them and can't wait for them to automatically be purged out) with the -i option:

adrci> purge -i 13579;

For more options, type "help purge" at the ADRCI prompt. I can't stress enough how good the ADRCI help facility is.