Blog 
Do you really need one year old Logs and Traces for your Oracle Database?

With Release 11g Oracle introduced the concept of the Automatic Diagnostic Repository (ADR) which (in theory) is an independant directory structure for all kind of logs and traces for the database, listener and other Oracle products. The content is mostly stored twice (as text or traditional trace file and in xml format). So the size of each individual directory can easily become several GB. Nothing to worry for todays disk spaces but annoying if you really need to find some relevant information.

The good news is that there is a purge mechanism associated with it so that old logs and traces are automatically removed. Unfortunately the default retention for most of those logs is one year! There are two parameters: SHORTP_POLICY (default 720 hours) and LONGP_POLICY (default 8720 hours). so SHORTP_POLICY is 30 days while LONGP_POLICY is 365 days.

But you can easily change the retention by setting the variables to a more suitable value:

% adrci:
adrci> show homes
diag/rdbms/rac112/RAC1122
diag/rdbms/rac112/TEST
diag/rdbms/rac112/RAC1121
diag/rdbms/ron112/RON112_2
diag/rdbms/ron112/RON1121
diag/rdbms/ron112/RON112_1
adrci> set home diag/rdbms/rac112/RAC1122
adrci> show control
ADR Home = /u01/app/oracle/diag/rdbms/rac112/RAC1122:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ----------------------------------------
1122254562 720 8760 2011-01-11 13:08:51.956565 +01:00 2011-03-23 23:08:36.314710 +01:00 1 2 76 1 2011-01-11 13:08:51.956565 +01:00
1 rows fetched
adrci> set control (SHORTP_POLICY = 168)
adrci> set control (LONGP_POLICY = 720)

Like in this example you probably have more than one database or multiple Oracle Homes installed which ends um in various ADR-Home directories. Changing the parameter SHORTP_POLICY and LONGP_POLICY requires to first select the correct ADR-Home erst dann die Parameter anpassen kann.

But adrci can be called with additional parameters which helps to create a simple Shell-script for the necessary changes. The following example will set SHORTP_POLICY to one week and LONGP_POLICY to one month:

#!/bin/sh
for ADRHOME in `adrci exec="show home"`
do
    if [ $ADRHOME = "ADR" -o $ADRHOME = "Homes:" ]
    then
        continue;
    fi
    echo $ADRHOME
    adrci << EOF
         set home $ADRHOME
         set control (SHORTP_POLICY = 168)
         set control (LONGP_POLICY = 720)
         purge
    exit
EOF
done

This can save a huge amount of space and the important files can be found more easily.

No comments on “Do you really need one year old Logs and Traces for your Oracle Database?

Leave a Reply

Your email address will not be published.

What can CarajanDB do for you?