LLI Wiki — My Digital Knowledge Base

Oracle: clean up logs and traces with logrotate and adrci

Rotate alert and listener logs with logrotate, set ADR retention policies and purge with adrci, plus navigation tips.

Oracle writes alert logs, listener logs, trace files and audit files under the ADR (Automatic Diagnostic Repository). Left alone, they grow forever. This page shows two ways to keep them under control: logrotate for the plain-text logs, and adrci retention policies and purges for the ADR content. It also collects practical adrci navigation tricks.

Rotate text logs with logrotate

On a Linux server (tested on CentOS 7), configuration files live in /etc/logrotate.d. They must be owned by root and not writable by others:

chmod 644 /etc/logrotate.d/oracle_rdbms_log
chown root:root /etc/logrotate.d/oracle_rdbms_log

Database alert log, /etc/logrotate.d/oracle_rdbms_log:

/u01/app/oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log
{
    daily
    rotate 5
    dateext
    compress
    copytruncate
    delaycompress
    create 640 oracle oinstall
    notifempty
    su oracle oinstall
}

Listener log, /etc/logrotate.d/oracle_listener_log:

/u01/app/oracle/diag/tnslsnr/server01/listener/trace/listener.log
{
    daily
    rotate 5
    dateext
    compress
    copytruncate
    delaycompress
    create 640 oracle oinstall
    notifempty
    su oracle oinstall
}

copytruncate copies the log then truncates it in place, so Oracle keeps writing to the same open file.

Purge the ADR with adrci

Retention policy

The ADR has two retention policies: SHORTP_POLICY (short-lived data such as traces and core dumps) and LONGP_POLICY (long-lived data such as incidents and alerts). Values are in hours.

adrci> show homes
adrci> show homepath
adrci> set homepath diag/rdbms/mydb/mydb
adrci> show control

adrci> set control (SHORTP_POLICY=168)    -- 7 days
adrci> set control (LONGP_POLICY=2400)    -- about 100 days (3 months)

adrci> purge -age 5 -type ALERT

To apply a policy and purge immediately:

adrci> set control (SHORTP_POLICY=14);
adrci> set control (LONGP_POLICY=21);
adrci> purge;
adrci> show control;

The policies are stored in the ADR_CONTROL view:

SELECT * FROM ADR_CONTROL;

To find the ADR base from SQL*Plus: show parameter diagnostic_dest;

Purge every home

purge -age takes minutes (10080 = 7 days, 14400 = 10 days, 21600 = 15 days, 43200 = 30 days):

AGE15DAYS=21600
PURGETARGET=$AGE15DAYS
for f in $( adrci exec="show homes" | grep -v "ADR Homes:" );
do
  echo "Purging ${f}:";
  adrci exec="set home $f; purge -age $PURGETARGET ;" ;
done

Plain cleaning script (cron)

Where adrci is not used, a cron script can delete old files:

#!/bin/bash
TODAY=$(date +"%Y%m%d")
find /u01/app/oracle/diag/rdbms/mydb/mydb/trace/*.trc -mtime +30 -exec rm {} \;
find /u01/app/oracle/diag/rdbms/mydb/mydb/trace/*.trm -mtime +30 -exec rm {} \;
find /u01/app/oracle/admin/mydb/adump/*.aud -mtime +30 -exec rm {} \;
mv /u01/app/oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log /u01/app/oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log_$TODAY
find /u01/app/oracle/diag/rdbms/mydb/mydb/trace/alert_mydb.log_* -mtime +30 -exec rm {} \;
find /u01/app/oracle/diag/tnslsnr/server01/listener/alert/log_*.xml -mtime +30 -exec rm {} \;

Scheduled in the oracle user crontab, for example:

00 23 * * * /home/oracle/scripts/cleaning_log.sh >> /home/oracle/scripts/cleaning_log.log 2>&1

adrci navigation tricks

The examples assume this setup:

ADR base is "/u01/app/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/clone/CLONE
diag/rdbms/prod/PROD
diag/asm/+asm/+ASM
diag/tnslsnr/pc01/listener
Goal Command
Select one home by the last part of its path set home PROD
Select the only database home set home rdbms
Select several homes set homes PROD, listener
Select homes by the beginning of the path set homepath diag/rdbms
Go back to all homes set base "/u01/app/oracle" (or exit and restart adrci)

Follow the alert log like tail -f:

adrci> set home PROD
adrci> show alert -tail -f

Search the alert log from the shell, without opening an interactive session:

adrci exec="set home PROD; show alert -term;" | grep ORA-

Create a test incident (all on one line):

adrci> create incident problem_key="ORA-00600" error_message="[Memory corruption]" error_facility=ORA error_number=600

Notes

References

Mastodon LLIMastodon SocialFosstodon