Oracle database housekeeping methods – ADR files purge

March 28, 2014 by 1 Comment 

Since Oracle Database 11g, all traces, incident dumps and packages, the alert log, Health Monitor reports, core dumps, and more files are stored in the ADR, a file-based Automatic Diagnostic Repository for simplified database diagnostic data management. In spite of that new 11g feature, the housekeeping or purging of those files got not considerably simplified. Unfortunately the new Oracle utility ADRCI can not purge all the files that Oracle database generates in ADR. Thus, you should use the other Unix OS methods and tools to accomplish Oracle 11g database housekeeping. In previous posts I described the Unix log rotation mechanism and SYS auditing files purge in adump directory. This time we will talk exactly about ADR purge functionality. But before, let’s look at some basics.

Location for Diagnostic Traces

Location of Diagnostic Traces of Oracle ADR

Location of Diagnostic Traces in Oracle 11g ADR

The table shown above in the slide describes the different classes of trace data and dumps that reside both in Oracle Database 10g and 11g.
With Oracle Database 11g, there is no distinction between foreground and background trace files. Both types of files go into the $ADR_HOME/trace directory.
All non-incident traces are stored inside the trace subdirectory. This is the main difference compared with previous releases where critical error information is dumped into the corresponding process trace files instead of incident dumps. Incident dumps are placed in files separated from the normal process trace files starting with Oracle Database 11g. We can also check V$DIAG_INFO view lists all important ADR locations including.

ADR retention policy – automatic purge

A retention policy allows you to specify how long to keep the diagnostic data. ADR incidents are controlled by two different policies:

LONGP_POLICY:
The incident metadata retention policy controls how long the metadata is kept around. This policy has a default setting of one year (default is 8760 = 365 days = 1 year). Here are the components that currently get purged by LONGP_POLICY:

ALERT — files in the ./alert directory
INCIDENT – files in the ./incident/incdir_ directory.
SWEEP — files in the ./sweep directory
STAGE — files in the ./stage directory
HM — files in the ./hm directory and metadata in the HM schema

SHORTP_POLICY
The incident files and dumps retention policy controls how long generated dump files are kept around. This policy has a default setting of one month (default is 720 which corresponds to 30 days – approximately 1 month). Here are the components that currently get purged by SHORTP_POLICY:

TRACE – files in the ./trace directory
CDUMP – files in the ./cdump directory
UTSCDMP – files in the ./trace/cdmp_ directories
IPS – files in the ./incpkg directory and metadata in the IPS schema

Based on these 2 policies Oracle database MMON process is responsible for purging automatically expired ADR data.

To view and modify these settings, you can either use the Incident Package Configuration link of Enterprise Manager 11g DB Control GUI or via the ADR Command Interpreter (ADRCI) started from an O/S prompt. See example below:

$ adrci
ADRCI: Release 11.2.0.3.0 - Production on Fri Mar 28 15:33:32 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
ADR base = "/opt/app/oracle"
adrci> show home
ADR Homes:
diag/rdbms/orcl/orcl

adrci> set home diag/rdbms/orcl/orcl
adrci> show control
ADR Home = /opt/app/oracle/diag/rdbms/orcl/orcl:
*************************************************************************
ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY A DRALERT_VERSION CREATE_TIME
-------------------- -------------------- -------------------- ---------------------------------------- --------------------- ------------------- ---------------------------------------- -------------------- -------------------- -------------------- - ------------------- ----------------------------------------
2185355490 720 8760 2013-04-16 08:52:55.634531 +02:00 2014-03-28 14:57:14.1 25915 +01:00 1 2 80 1 2013-04-16 08:52:55.634531 +02:00
1 rows fetched

To set the retention policy values :

adrci> set control (SHORTP_POLICY = 3600)

Note:
– Up to 11.1.0.7, CDUMP files were not purged due to unpublished Bug 6343743
– ADRCI still can not to purge *.log files which you can handle with Unix log rotation mechanism.
– ADRCI ‘purge -age “n” -type INCIDENT’ does not remove the *.lck files from the /lck directory in the incident schema

Manual purge of ADR files – Unix script with ADRCI

To purge ADR files we will use ADRCI that is a command-line tool that is part of the fault diagnosability infrastructure introduced in Oracle Database Release 11g. I prepared a script below with ADRCI commands that should be executed using ADRCI tool. There is no need to log in to ADRCI, because the data in ADR is not intended to be secure. ADR data is secured only by operating system permissions on the ADR directories. Take care about following guidelines:
– Script is for Unix/Linux environment
– Script should be run as oracle OS user that owns ADR file structure
– You can run the script manually comparing the number of files before and after with:
$ ll | wc –l

– You can schedule the script in Unix crontab utility by adding following line in crontab file:
$ crontab -e
00 05 * * 0 adr_purge.sh

# Purge ADR contents (adr_purge.sh)
echo "INFO: adrci purge started at `date`"
adrci exec="show homes"|grep -v : | while read file_line
do
echo "INFO: adrci purging diagnostic destination " $file_line
echo "INFO: purging ALERT older than 90 days"
adrci exec="set homepath $file_line;purge -age 129600 -type ALERT"
echo "INFO: purging INCIDENT older than 30 days"
adrci exec="set homepath $file_line;purge -age 43200 -type INCIDENT"
echo "INFO: purging TRACE older than 30 days"
adrci exec="set homepath $file_line;purge -age 43200 -type TRACE"
echo "INFO: purging CDUMP older than 30 days"
adrci exec="set homepath $file_line;purge -age 43200 -type CDUMP"
echo "INFO: purging HM older than 30 days"
adrci exec="set homepath $file_line;purge -age 43200 -type HM"
echo ""
echo ""
done
echo
echo "INFO: adrci purge finished at `date`"

So, that was a housekeeping method for Oracle files located in ADR using using ADRCI tool and Unix shell scripting.
Please share your methods of purging Oracle SYS audit files in comments below.

Enjoyed this article? Please share it with others using the social site of your choice:

Comments

One Response to “Oracle database housekeeping methods – ADR files purge”
  1. Mike Johnson says:

    Thanks for the script changed it up some and thought I would share.

    # Purge ADR contents (adr_purge.sh)
    purge_log () {
    purge_min=$(( 24 * 60 * $2 ))
    echo “INFO: purging $1 older than $2 days”
    adrci exec=”set homepath $file_line;purge -age $purge_min -type $1″
    }

    echo “INFO: adrci purge started at `date`”
    adrci exec=”show homes”|grep -v : | while read file_line
    do
    echo “INFO: adrci purging diagnostic destination ” $file_line
    echo “——————————————————————————-”
    purge_log ALERT 365
    purge_log INCIDENT 273
    purge_log TRACE 120
    purge_log CDUMP 75
    purge_log HM 75
    echo “——————————————————————————-”
    done
    echo
    echo “INFO: adrci purge finished at `date`”

Add a Comment

We welcome thoughtful and constructive comments from readers.
If you want your own picture to show with your comment?
Go get a Globally Recognized Avatar!

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email