Why your unified audit trail might not be purging

We have had the Unified Audit trail among us for a while now. Most people will be pretty settled by now. Still there are some challenges working with the Unified Audit trail. This post is about purging your unified audit trail in a Multitenant environment and why it sometimes doens’t purge.

So creating a CDB and adding a few PDB’s might not be a great deal of work. Creating and enabling Auditing Policies isn’t as well. Then purging isn’t hard and always works exactly as expected, right?

I found this wasn’t always the case when working with (pluggable) databases and purging them independently.

Basics of purging the unified audit trail

Let’s share the basics of purging the Unified Audit trail. Purging the whole audit trail goes like:

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE);
END;
/

Your whole Audit Trail will be empty. But wait, not all records go away. Well lets load the spill over (.bin) files (read this post). These files live on the OS , but do appear in the unified_audit_trail view. These files are located in:

$ORACLE_BASE/audit/$ORACLE_SID

To load these files execute this the database:

SQL> exec DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

By loading them they should be removed from the OS and now exist in the audit table. By default, they should be loaded (and removed) at instance startup.

The next challenge arises from the .aud files that are stored on the OS because of SYS actions in the database. The directory these files are stored in can be found by:

SQL> show parameter audit_file_dest
NAME            TYPE   VALUE                     
--------------- ------ --------------------------
audit_file_dest string /audit/oracle/TESTDB1

These files should also be removed to clean your audit trail. (ideas later)

And to be consistent, during startup your instance writes audit files to the following location:

$ORACLE_HOME/audit/rdbms/audit

Mind you, files from all databases active from this ORACLE_HOME appear in this directory. The database name is included in the file name.

A few ideas from the bash shell to clean all these OS files are:

find <audit_file_dest> -type f  -name '*.aud -execdir rm -- '{}' \;

find $ORACLE_BASE/audit/$ORACLE_SID -type f -name '*.bin' -execdir rm -- '{}' \;

find $ORACLE_HOME/rdbms/audit/ -type f -name '*'${ORACLE_SID}'*.aud' -execdir rm -- '{}' \;

Purge audit trail with a retention

In most cases you want to purge your audit trail based on a retention. For example keep audit records of the last 30 days. The way this works is that you first set a LAST_ARCHIVE_TIMESTAMP for your instance and next purge the audit trail based on this time stamp.

begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => systimestamp - 30,
    rac_instance_number => 1,
    database_id => 3038598791,
    CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
end;
/

This timestamp gets set and will appear in the following view:

SQL> SELECT audit_trail, rac_instance, LAST_ARCHIVE_TS, DATABASE_ID FROM dba_audit_mgmt_last_arch_ts;
           AUDIT_TRAIL    RAC_INSTANCE                        LAST_ARCHIVE_TS    DATABASE_ID
______________________ _______________ ______________________________________ ______________
UNIFIED AUDIT TRAIL                  0 25-MAY-23 11.29.59.000000000 AM GMT        3038598791

Next you can purge your unified audit trail based on this time time stamp

begin
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    database_id => 3038598791,
    use_last_arch_timestamp => TRUE);
end;
/

Of course you could put this all in an automated job, and do your thing, but in some rare cases this does not clean the whole audit trail. Make sure to load your spill over files and purge your OS files. Ideas for this are:

find <audit_file_dest> -type f -mtime +<days> -name '*.aud -execdir rm -- '{}' \;

find $ORACLE_BASE/audit/$ORACLE_SID -mtime +<days> -type f  -name '*.bin' -execdir rm -- '{}' \;

It could be that audit records are not purged fully with the first DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL procedure. I found that the last_arhive_timestamp gets saved in dba_audit_mgmt_last_arch_ts. I have seen cases where there are lots of timestamps saved and the cleaning of the audit trail does not use the most recent timestamp. Thats why its a good idea to clean the dba_audit_mgmt_last_arch_ts before you set the timestamp and purge.

delete from dba_audit_mgmt_last_arch_ts;
commit;

But then.. still some records might not be purged.

Purge of a cloned pluggable databases

The given example is recommended in many places on the internet. But a challenge arises. Even if you do not specify your database_id, the timestamp still gets set for your specific database ID.

When you have a cloned pluggable database, you also clone its unified audit trail. This means that when your start generating audit data, you have multiple database_id’s in your audit trail. When the pluggable database you cloned from is also cloned, you will have even more database_id’s. In my case, auditing did not purge because of this.

My pluggable database, for example, has been cloned from a pluggable database that had been cloned from the PDB$SEED. The seed database also had audit records in it from the time the CDB was created:

SQL> select distinct dbid from unified_audit_trail;
         DBID
_____________
   2913741198
   1531671507
   3016119345

This means that the steps of setting the LAST_ARCHIVE_TIMESTAMP and the CLEAN_AUDIT_TRAIL should be done for each dbid in the audit trail. I will not show this in examples since its just doing what I showed above, in this case with different ID’s. Of course purging without a timestamp at all will also lead to the cleaning of all these records. But if you have automated jobs in place that purge the audit trail based on a LAST_ARCHIVE_TIMESTAMP, purging based on ALL dbid’s in the audit tails is needed to get rid of all data.

Purge audit trail of RAC databases

Because the audit trail lives inside the database, this should matter. And it mostly doens’t. But audit records might not be purging. That is because by loading the spill over files for the OS, only the files for that specific instance are loaded. Records from other instances do show up in the unified_audit_trail view, straight from the OS, but they are not loaded by LOAD_UNIFIED_AUDIT_FILES.

So either you can load your files on each instance, or you can keep your unified files on the OS and purge them periodically from the OS. They will appear in the unified audit trail anyways. But it can be a reason why you find your files do not purge. So, check all instances for OS files that might remain. SYS audit files as well as spill over files.

Purging in case of Data Guard

Purging a data guard primary will be like purging any other database. So that’s nice. But purging a standby is a little different. A normal/passive data guard will be in mounted mode and will not be able to access data stored in the database nor make changes to this data. Actions will be audited and end up on the file system.

A database in an active data guard configuration will be in read-only mode. It can access data, but cant make changes to it. Actions in this case will also be audited and end up on the OS. The database is in read-only mode and because of that cannot write records to the audit trail. Fun fact, an active data guard standby database gives the “most accurate” representation of the audit data. It will combine the data in the database of the primary with its own data in OS files. It will miss data stored in OS files on the primary site. But the primary will miss all audit records from the standby since these are all stored on the OS.

You can think of several ways to load all this data in the database, but that’s not the focus of this post. In short, purging the standby site can be done by purging the OS files. All examples to purge OS files given earlier will work for the standby as well.

Conclusion

These are some situations that I encountered working on purging CDB’s in Oracle 19c. Maybe all challenges are fixed in future releases. But until then, I hope my struggles might help you towards your own solution and you will purge your unified audit trail successfully.

But first…. coffee.