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 11.2.0.3.0 - 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: 
    diag/tnslsnr/exampledb/listener
    diag/rdbms/exampledb/exampledb

    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 {
        compress
        monthly
        dateext
        maxage 365
        rotate 99
        size=+16M
        notifempty
        missingok
        copytruncate
    }

    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