Unified Auditing in Oracle: Setup, Management, and Purging

In this series of blogs, we will walk through the setup of an end-to-end Unified Auditing in the Oracle framework. We will begin with enabling and managing Unified Auditing, then look at extracting records from the audit trail and forwarding them to a central logging platform such as Google Cloud Ops Logging or Splunk. Here, the audit data can be analyzed, correlated, and subjected to a forensic review by a Database Auditing Management tool.

This post walks through the steps I used to enable Unified Auditing in Oracle 19c on a standalone database environment. We also need to think about existing audit settings, audit trail storage, partitioning, retention, and purge jobs. This is important to manage the audit data.

Set up Unified Audit Trail

Unified Auditing gives Oracle a single audit trail instead of spreading audit records across multiple older audit destinations.

1. Pre-checks

First, check whether Unified Auditing is already enabled.

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

If the value is TRUE, Unified Auditing is already enabled. If it is FALSE, then the database still needs to be migrated to pure Unified Auditing.

I would recommend saving the existing audit configuration before making changes.

show parameter audit

SELECT *
FROM dba_audit_mgmt_config_params;

In my case, I also reviewed the unified_audit_systemlog settings because different databases needed separate SYSLOG handling.

2. Backup and relink the software

The Database should be stopped before attempting a relink. For RAC environments with non-shared Oracle homes, the relink step must be repeated on each cluster node.

srvctl status database -d orcl
srvctl stop database -d orcl

cd /u01/app/oracle/product/19.3.0/
tar -czvf /ubb1/patching_backups/dbhome_19c_bkp.tar.gz db_1

3. Relink the Oracle Binary

Now go to the RDBMS library directory and relink the Oracle binary with uniaud_on. This enables Unified Auditing for the software.

cd $ORACLE_HOME/rdbms/lib


make -f ins_rdbms.mk uniaud_on ioracle

srvctl start database -d orcl

4. Validations

After relinking, start the databases again. Then, validate the Unified Auditing status again. Should be TRUE

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

Managing Unified Audit Trail

Once Unified Auditing is enabled, I’d recommend separating the audit data and keeping it in its dedicated tablespace.

CREATE TABLESPACE DB_AUD_HIST_TS;

To move the Unified Audit Trail to its dedicated tablespace use DBMS_AUDIT_MGMT. Notice the audit trail type here is “Audit trail unified”. Since we are not using any

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'DB_AUD_HIST_TS');
END;
/

UNIFIED_AUDIT_TRAIL It is built on an internal interval-partitioned table, and existing partitions remain in the old tablespace. Newly created partitions will use the new tablespace after the location change. So you can manage the partition interval

The default partition interval is monthly; you can check the current partitioning details.

set linesize 150
column owner format a10
column table_name format a20
column interval format a25

SELECT owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
FROM dba_part_tables
WHERE owner = 'AUDSYS';

If the audit volume is large, I would consider changing the unified audit trail partition interval from monthly to daily. The default monthly interval may be fine for smaller environments, but it leaves you with a much wider retention boundary. With daily partitions, you get more granular control over how audit data is retained, reviewed, and purged.

BEGIN
  DBMS_AUDIT_MGMT.alter_partition_interval(
    interval_number    => 1,
    interval_frequency => 'DAY');
END;
/

Validate again:

SELECT owner,
       table_name,
       interval,
       partitioning_type,
       partition_count,
       def_tablespace_name
FROM dba_part_tables
WHERE owner = 'AUDSYS';

Purging Audit Data

We next need to plan to periodically archive and purge audit trail records so that the audit trail does not grow too large. The purge operation is controlled via a combination of SET_LAST_ARCHIVE_TIMESTAMP & CLEAN_AUDIT_TRAIL packages.

SET_LAST_ARCHIVE_TIMESTAMP defines the archive cutoff point for the audit trail. Any audit records older than this timestamp become eligible for cleanup, i.e., deletion.

CLEAN_AUDIT_TRAIL with use_last_arch_timestamp => TRUE, Oracle checks this timestamp and removes audit records older than that cutoff.

The purging schedule and partitioning strategy should be defined based on the audit retention requirements. Purging audit data is more efficient when Oracle can drop older partitions, instead of deleting individual rows. If an entire older partition falls before the archive timestamp, Oracle can drop that partition.

So, if you use the default monthly partitioning, it is better to align the archive timestamp with the beginning of a month. That allows older monthly partitions to be dropped. For e.g. you could schedule the purge job to run monthly and retain the required X number of months based on your audit retention policy.

However, in our environment, the audit data generation was massive, and we wanted to only retain 45 days’ worth of audit records within the database. Hence, we modified our partition interval to daily, and set the last_archive_time to sysdate – 45.

SELECT audit_trail,
       last_archive_ts
FROM dba_audit_mgmt_last_arch_ts;

BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    last_archive_time => SYSTIMESTAMP - 45,
    container         => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

Then validate: the value of last_archive_ts should be 45 days earlier. The purge job will use this timestamp as the purge boundary and delete all audit data before this timestamp.

Please note if the db is a standby database, then the spill over files needs to be loaded into the audit table before running the cleanup operation. Refer MOS KB131142

exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_all );
 EXEC DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES;

Daily Purge Job

You can create a scheduler job that advances the archive timestamp and purges records older than 45 days.

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name => 'PURGE_UNIFIED_AUDIT_JOB',
    job_type => 'PLSQL_BLOCK',
    job_action => '
      BEGIN
        DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
          audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
          last_archive_time => SYSTIMESTAMP - 45,
          container         => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);

        DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
          audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
          use_last_arch_timestamp => TRUE,
          container               => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
      END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=2; byminute=10; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Purge unified audit trail older than 45 days.');
END;
/

Then confirm the job status.

ALTER SESSION SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

COLUMN job_name FORMAT A25
COLUMN start_date FORMAT A30
COLUMN end_date FORMAT A30
COLUMN last_start_date FORMAT A30
COLUMN next_run_date FORMAT A40

SELECT job_name,
       enabled,
       start_date,
       end_date,
       last_start_date,
       next_run_date
FROM dba_scheduler_jobs
WHERE job_name = 'PURGE_UNIFIED_AUDIT_JOB';

Check the date range of the audit records. If the minimum timestamp advances after the purge job runs, the cleanup is not working as you expect. So do verify.

SELECT MIN(event_timestamp),
       MAX(event_timestamp)
FROM unified_audit_trail;

Our audit management setup is now complete to drop partitions daily and only maintain 45 days’ worth of unified audit trail data within the database.

Do note – enabling Unified Auditing is just the starting point. The audit trail needs to be managed properly, preferably with a dedicated tablespace. Proper partitioning and retention rules should be defined based on your requirements, and finally a purge job should be set up to manage them.

In the next blog, let’s look at different options for extracting Unified Audit data from the database and preparing it for centralized logging and forensic analysis.