Log rotation for Oracle 11gR2 database

Up to Oracle 11gR2 the database logs (that is traces, dumps etc) were spawned all over the place and Oracle did not care about the pile-up.

Starting with 11g, Oracle is keeping the logs in check with a default deletion policy of 30 days for dumps and one year for the rest. The mmon process is deleting them. The management tool for logs is called “adrci” Automatic Diagnostic Repository Command Interpreter and is documented here.

However, there’s a problem: this tool is looking at the timestamp of files and not at the content. It erases files that are older than the deletion policy. Logs with frequent writes will grow indefinitely. For example: alert.log, listener.log, sqlnet.log. To resolve this, there’s a file you can add in /etc/logrotate.d so good old Linux will clean up after Oracle.

  1. First, you have to find out where the logs are. As user oracle, give this command
    echo -e "show base\nshow homes" | adrci

    The ouput will be like this:

    ADRCI: Release - Production on Wed Jun 5 14:41:05 2013
    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
    ADR base = "/opt/oracle"
    adrci> ADR base is "/opt/oracle"
    adrci> ADR Homes: 

    Note the base and the homes

  2. Then, as root, create the file /etc/logrotate.d/oracle_trace
    /opt/oracle/diag/tnslsnr/exampledb/listener/trace/*.log /opt/oracle/diag/rdbms/exampledb/exampledb/trace/*.log {
        maxage 365
        rotate 99

    Note that the base and homes from above were concatenated and also trace/*.log was added.

Some more useful info: if you want to shorten the default one year policy, there are two commands you need to know (as user oracle, type adrci to invoke the interpreter):

  1. show control
    This shows the default policy
  2. set control (LONGP_POLICY = 4380)
    This sets the parameter to half of year = 4380 hours

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s