Manage Audit File Directory Growth with cron (Doc ID 1298957.1)

APPLIES TO:

Oracle Database Products > Exadata > Database Machine
Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.3 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 29-Apr-2013***

PURPOSE

The audit file destination directories for an ASM instance can grow to contain a very large number of files if they are not regularly maintained. Having a very large number of files can cause the file system to run out of free disk space or inodes, or can cause Oracle to run very slowly due to file system directory scaling limits, which can have the appearance that the ASM instance is hanging on startup.

This document explains how to use the Linux cron(8) utility to manage the number of files in the audit file destination directories.

SCOPE

These steps must be performed for the ASM instance on every database server.

This document explains how to manage the audit file destination directories for ASM instances only. For complete database auditing overview and instruction on managing and purging the database audit trail, refer to the Oracle Database Security Guide at http://download.oracle.com/docs/cd/E11882_01/network.112/e16543/auditing.htm#BCGIDBFI .

DETAILS

Step 1 - Identify the ASM audit directories

There are three directories that may contain audit files. All three must be managed to control excessive growth.

Two default locations are based on environment variable settings when the ASM instance is started. To determine the default locations for your system, login as the Grid Infrastructure software owner (typically either oracle or grid), set your environment so that you can connect to the ASM instance, then run the 'echo' commands provided below. In this example, the two default audit directories are /u01/app/11.2.0.2/grid/rdbms/audit and /u01/app/oracle/admin/+ASM1/adump.

   $ . /usr/local/bin/oraenv 
  
 ORACLE_SID = [+ASM1] ? +ASM1 
  
 The Oracle base for ORACLE_HOME=/u01/app/11.2.0.2/grid is /u01/app/oracle 
  
 $ echo $ORACLE_HOME/rdbms/audit 
  
 /u01/app/11.2.0.2/grid/rdbms/audit 
  
 $ echo $ORACLE_BASE/admin/$ORACLE_SID/adump 
  
 /u01/app/oracle/admin/+ASM1/adump

The third ASM audit directory can be found by logging into the ASM instance with SQL*Plus and running this statement:

   $ sqlplus '/ as sysasm' 
  
 SQL> select value from v$parameter where name = 'audit_file_dest'; 
  
 VALUE 
  
 -------------------------------------------------------------------------------- 
  
 /u01/app/11.2.0.2/grid/rdbms/audit

All three ASM audit directories will be managed with cron(8).

Step 2 - Give Grid Infrastructure software owner permission to use cron

Audit files are owned by the Grid Infrastructure software owner, which is typically either oracle or grid. Commands to move or remove audit files must be run as the Grid Infrastructure software owner. As root, add the Grid Infrastructure software owner to /etc/cron.allow file. The examples below use the user oracle.

   # echo oracle >> /etc/cron.allow 
 

Step 3 - Add command to crontab to manage audit files weekly

As the Grid Infrastructure software owner, add an entry to the crontab file. The following command will start a vi(P) command edit session to edit the existing crontab file or create a new crontab file if one does not already exist.

   $ crontab -e 
 

Add the following to this file as a single line:

   0 2 * * sun /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit /u01/app/11.2.0.2/grid/rdbms/audit /u01/app/oracle/admin/+ASM1/adump -maxdepth 1 -name '*.aud' -mtime +30 -delete 
 

This crontab entry executes the find(1) command at 2AM every Sunday. The find(1) command deletes all audit files in the three ASM audit directories that are older than 30 days.

If you wish to retain audit files for a longer period of time, instead of deleting the audit files with the find(1) command, you can archive audit files to a different directory or storage device using a crontab entry like the following:

   0 2 * * sun /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit /u01/app/11.2.0.2/grid/rdbms/audit /u01/app/oracle/admin/+ASM1/adump -maxdepth 1 -name '*.aud' -mtime +30 -execdir /bin/mv {} /archived_audit_dir \; 
 

This crontab entry executes the find(1) command at 2AM every Sunday. The find(1) command moves all audit files in the three ASM audit directories that are older than 30 days to /archived_audit_dir.

Save and exit the crontab file using vi commands (<ESC> :wq), then verify crontab contents.

   $ crontab -l 
  
 0 2 * * sun /usr/bin/find /u01/app/11.2.0/grid/rdbms/audit /u01/app/11.2.0.2/grid/rdbms/audit /u01/app/oracle/admin/+ASM1/adump -maxdepth 1 -name '*.aud' -mtime +30 -delete

Troubleshooting

If old audit files are not being removed, perform the following steps:

To monitor that cron(8) is executing the /usr/bin/find command on schedule and as the correct Grid Infrastructure software owner, review the /var/log/cron file as the root user for an entry like the following:

   Feb 20 02:00:01 dm01db01 crond[6936]: (oracle) CMD (/usr/bin/find /u01/app/11.2.0/grid/rdbms/audit /u01/app/11.2.0.2/grid/rdbms/audit /u01/app/oracle/admin/+ASM1/adump -maxdepth 1 -name '*.aud' -mtime +60 -delete) 
 

Log in as the user that owns the crontab entry (i.e. the Grid Infrastructure software owner). Run the find(1) command manually to verify the correctness of the command syntax and that there are no errors reported.