Process for deleting old audit files in Oracle 12c

As an Oracle Database Administrator (DBA) working with Oracle 12c, managing audit files is a critical task to ensure optimal performance and maintain compliance with storage policies. Over time, audit files can accumulate and consume significant disk space. To efficiently manage these files, a systematic approach for deletion of old audit files should be adopted.
The process for deleting old audit files in Oracle 12c is as follows:

1. Identify Audit File Location:
   • Determine the location of the audit files. This is typically specified in the `audit_file_dest` parameter of your Oracle instance. You can retrieve this value using the SQL command:
     

     SHOW PARAMETER audit_file_dest;
     

2. Assess File Age and Size:
   • Before deletion, assess the age and size of the audit files. This helps in determining which files are old and can be safely removed. It's important to comply with your organization's data retention policies.
3. Manual Deletion:
   • For manual deletion, use operating system commands. Navigate to the directory containing the audit files and use commands like `rm` in Linux/Unix to remove files older than a certain number of days. For example:
     

     find /u01/app/oracle/admin/orcl/adump -name '*.aud' -mtime +30 -exec rm {} \;
     

     
     This command finds and deletes all `.aud` files older than 30 days in the specified directory.
4. Automated Housekeeping:
   • Implement an automated housekeeping script to regularly clean up old audit files. This can be scheduled as a cron job in Unix/Linux systems or a scheduled task in Windows.
5. Using Oracle Tools:
   • Oracle 12c provides the `DBMS_AUDIT_MGMT` package to manage audit trails, including cleanup. To use this, first initialize the cleanup configuration:
     

     BEGIN
     DBMS_AUDIT_MGMT.init_cleanup(
     audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
     default_cleanup_interval   => 30); 
     END;
     

     
     This sets a default cleanup interval of 30 days.
   • Then, create a scheduled job to perform the cleanup:

     BEGIN
     DBMS_AUDIT_MGMT.create_purge_job (
     audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
     audit_trail_purge_interval => 30,
     audit_trail_purge_name     => 'Purge_old_audit_files',
     use_last_arch_timestamp    => FALSE);
     END;
     

     This creates a job named 'Purge_old_audit_files' that will run every 30 days to purge the audit files.
6. Monitoring and Verification:
   • Regularly monitor the audit file directory to ensure that the cleanup process is working as expected. Verify that files are being deleted according to the defined criteria.
7. Documentation and Compliance:
   • Document the procedure and schedule for audit file deletion. Ensure that these practices are in compliance with your organization’s IT policies and any relevant regulatory requirements.

By following these steps, you can effectively manage the size of your audit file directory in Oracle 12c, helping maintain the performance and compliance of your Oracle database environment.

As you can imagine, auditing can take up a lot of space in your database. There are times when you will use auditing to create a record of database activity, but you will also frequently use auditing simply to diagnose a problem. Once the problem is identified, you will no longer need the audit files. In addition, audit records are stored in the SYS.AUD$ table, which, by default, contains 99 extents of 10K each. If the space allowed for this table fills up, you can no long perform any auditing.
To clean out old audit records, simply delete rows from the SYS.AUD$ table, just as you would with any other table. The only people with permission to do this are the user SYS, anyone whom SYS has granted DELETE privilege on the AUD$ table to, or anyone with DELETE ANY TABLE privilege. If you want to delete a certain set of rows in the table, use a WHERE clause to limit the action. For instance, if you want to delete all the audit rows in a table that related to the COIN table, use the following SQL statement:

DELETE * FROM SYS.AUD$ WHERE OBJ$NAME = 'COIN';

If you want to save audit records before deleting them, use the standard SQL command to insert the rows from the SYS.AUD$ table into another table. For instance, if you want to save the audit rows for the COIN table into the table named COIN_AUDIT, simply use the statement:

INSERT INTO coin_audit SELECT * FROM SYS.AUD$
WHERE obj$name = 'COIN';

assuming that the column structure of the COIN_AUDIT table matches that of the SYS.AUD$ table.
The next lesson is the module conclusion.