Process for deleting old audit files in Oracle 19c

In Oracle 19c, purging the audit trail involves removing old audit records or files to manage the size and performance of the database and audit repository. Below are the steps for purging audit trails in Oracle 19c:
1. Determine the Audit Trail Configuration
Oracle 19c supports two main types of auditing: For the following list of 2 elements, put the elements in a HTML ordered list .

• Unified Auditing (Unified Audit Trail): Introduced in Oracle 12c and recommended in 19c.
• Traditional Auditing (AUD$ and FGA_LOG$ Tables): Supported for backward compatibility.

Check the audit trail mode:

 
SELECT * FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

• YES: Unified Auditing is enabled.
• NO: Traditional Auditing is used.

2. Purging Unified Audit Trail (if Unified Auditing is enabled)
Unified audit records are stored in the `UNIFIED_AUDIT_TRAIL` view. You can use the `DBMS_AUDIT_MGMT` package to purge old audit records.
Enable the DBMS_AUDIT_MGMT Package:
Run the script to enable it if not already done:

 
@?/rdbms/admin/dbmsamgr.sql

Purge Old Audit Records:

1. Set the cleanup interval:

         BEGIN
           DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
             audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
             audit_trail_property => DBMS_AUDIT_MGMT.CLEANUP_INTERVAL,
             audit_trail_property_value => 12 /* Interval in hours */
           );
         END;
         /
       

2. Schedule a purge job:

         BEGIN
           DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
             audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
             audit_trail_purge_interval => 12 /* Interval in hours */,
             audit_trail_purge_name => 'UnifiedAuditPurgeJob'
           );
         END;
         /
       

3. Manually purge records older than a specific timestamp:

         BEGIN
           DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
             audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
             use_last_arch_timestamp => FALSE
           );
         END;
         /
       

3. Purging Traditional Audit Trail (if Unified Auditing is not enabled)
Traditional audit records are stored in `SYS.AUD$` and `SYS.FGA_LOG$`.
Backup Audit Data (Optional):
You may want to back up audit data before purging.

CREATE TABLE AUD_BACKUP AS SELECT * FROM SYS.AUD$ WHERE TIMESTAMP# < SYSDATE - 30;

Purge Records from `SYS.AUD$`:

1. Remove older records:

         DELETE FROM SYS.AUD$ WHERE TIMESTAMP# < SYSDATE - 30; -- Adjust the date range as needed
         COMMIT;
       

2. Truncate the table to remove all records:

         TRUNCATE TABLE SYS.AUD$;
       

Purge Records from `SYS.FGA_LOG$` (if Fine-Grained Auditing is used):

1. Remove old records:

         DELETE FROM SYS.FGA_LOG$ WHERE TIMESTAMP# < SYSDATE - 30; -- Adjust the date range
         COMMIT;
       

2. Truncate the table:

         TRUNCATE TABLE SYS.FGA_LOG$;
       

4. Purging Operating System Audit Files
If `AUDIT_TRAIL` is set to `OS` or `XML`, the audit files are written to the operating system directory defined by `AUDIT_FILE_DEST`.
Manually Remove Files:

1. Navigate to the directory:

         cd $ORACLE_BASE/admin//adump
       

2. Find and remove old files:

         find . -type f -mtime +30 -exec rm {} \; # Deletes files older than 30 days
       

Automate Purge Using a Cron Job:
Schedule a cron job to clean up old audit files regularly.
5. Monitor Audit Purge

• Unified Auditing: Query UNIFIED_AUDIT_TRAIL to ensure records are removed.
• Traditional Auditing: Check SYS.AUD$ or SYS.FGA_LOG$.

Example query:

 
SELECT COUNT(*) FROM SYS.AUD$;
SELECT COUNT(*) FROM SYS.FGA_LOG$;
SELECT COUNT(*) FROM UNIFIED_AUDIT_TRAIL WHERE EVENT_TIMESTAMP < SYSDATE - 30;

By implementing these steps, you can manage audit trail growth effectively in Oracle 19c.

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.