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)

It looks a bit strange but it works. And now the traces are dropped after one week (SHORTP) or one month (LONGP). 

adrci can be called with additional parameters so it should be easy to write a Shell script to set the proper value for all your adr-homes with one single command like the following one:

#!/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

About Carajandb

I'm an Oracle professional for more than 20 years and founder of CarajanDB. As you can see because of the layout of my blog one of my hobbys is Kiting - and esp. Indoorkiting.
Gallery | This entry was posted in Oracle, Oracle(E). Bookmark the permalink.

6 Responses to Do you really need one year old Logs and Traces for your Oracle Database?

  1. Joao says:

    Congratulations, very good explanation and script.

  2. John says:

    I am trying to find the source of the script that creates the view that show control uses. Hve you any ideas?
    Searched ?/rdbms/admin but no luck. Any other areas thatit may be in?

    John

    • streetkiter says:

      Hi John
      I have no idea what you are talking about? Do you try to query the content from inside the database – guess that might not work?. It’s just the command “adrci” or Oracle Database Control you can use.

  3. John says:

    Any idea what are classified as SHORTP_POLICY(s) and what are classified as LONGP_POLICY(s)

    • Carajandb says:

      Hi John,
      I’ve moved my blog to my new homepage (blog.carajandb.com) so maybe we can follow up on that page. But in short:
      SHORTP_POLICY: Traces, dumps and packaging information
      LONGP_POLICY: Incidents, Alerts

  4. dino says:

    Thank’s a lot Carajandb for this share
    dino

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s