Oracle – Making a Clean Sweep with ADRCI

In our consulting practice we are brought into a variety of client environments configured to varying standards, some new, and some a legacy of long organizational usage of Oracle products. We often find that while databases are upgraded many old practices are often carried forward rather than taking advantage of the ever evolving Oracle featureset. One area we see this in frequently is the non-usage of Oracle’s Automated Diagnostic Repository (ADR) which provides “fault diagnosability infrastructure” capabilities starting with the 11g release.

Most DBAs who have been working with Oracle versions prior to 11 typically had basic scripts to purge core/trace/dump files and various routine logs associated with running Oracle. These typically ran in the cron or whatever scheduler was used depending on the operating system of choice. Oracle produces a variety of trace/dump files for a multitude of aspects of the routine operation of the database as well as exception conditions that might arise. This is all fine and good, until you find the disk where you host Oracle (and possibly actual database related datafiles) filling up and your sysadmins complaining of the amount of space you’re using.

Enter the routine purge script that we see most places that is either hard coded for the current Oracle version or might be flexible and able to parse the /etc/oratab file and determine all the installed homes and then cycle through them and purge our old files based on last modified time or created time. Simple but key, you want to keep enough dump/trace files to be able to look back at some history should a problem arise BUT you don’t want to fill a disk or keep stuff for too long.

Oracle introduced the Automated Diagnostic Repository in Oracle 11 as a means to get control of the various dump/trace files as well as to expose them in a different way for integration with Enterprise Manager and other management tools. Here is the link to the docs for the most current Oracle release.

Older scripts can be adapted to purge files from the newly created “diag” directory and various sub directories, but why not adapt to the new release? Old habits die hard though, we know! There are 2 simple commands that you might wish to look at if you’re interested, just setup your Oracle environment, and enter the ADRCI (ADR Command Interface) to execute:

1. Immediately purge all diagnostic info or selective types of diagnostic info older than some amount of time.  Using the adrci command you can easily purge everything under the currently set Oracle environment to a point in time as measured in minutes - here’s a command to purge everything older than a month (30 days = 43200 minutes)
   purge -age 43200
2. Reset the default amount of time diagnostic data is retained. There are 2 distinctions of this data…one is SHORT (SHORTP_POLICY) and impacts TRACE, CDUMP, UTSCDMP, IPS files and one is LONG (LONGP_POLICY) and impacts ALERT Log, INCIDENTS, SWEEP, STAGE, HM files and info.
   To display the settings for these policies issue the SHOW CONTROL command and review the info.  To change settings use these commands.
   NOTE: These items are measured in terms of hours not minutes as the purge command noted above uses.

For 14 day retention on “short” time items…
NOTE: Default is 720 hours = 30 days
set control (SHORTP_POLICY=336); 

For 30 day retention on “long” time items…
NOTE: Default is 8760 hours = 1 year

set control (LONGP_POLICY=720);

Once set normal operation of the MMON background process will do cleanup to the specified retention once every 7 days. If you want to force this more frequently you could script running the purge -age <desired age in minutes> command.

Give it a try! Use those features your company is paying Oracle so much for!