🔐 Implementing Unified Auditing in Oracle 19c: A Step-by-Step Guide

🧠 What is Unified Auditing?

Unified Auditing is Oracle’s modern auditing framework that allows you to track and log database activities such as login events, DDL statements, SELECT/INSERT operations, and much more—all in one place.

Instead of having audit records scattered across different tables or files (like in traditional auditing), Unified Auditing stores everything in a single, optimized table called AUD$UNIFIED, which is owned by the internal schema AUDSYS.

This centralized model makes auditing easier to manage, more efficient in terms of performance, and more flexible when setting up policies.

🚀 Benefits of Unified Auditing

Here’s why organizations are adopting Unified Auditing in Oracle 19c:

• 🔐 Centralized log management
• ✅ Policy-based configuration
• 📊 Improved query performance
• 🔎 Supports audit of SYS and privileged users
• 🧾 Meets compliance standards (GDPR, SOX, HIPAA)

🚀 Step-by-Step: Enable Unified Auditing in Oracle 19c

Oracle 19c does not always enable Unified Auditing by default—especially in older databases upgraded from earlier versions. Let’s walk through how to enable and configure it properly.

---

🔎 Step 1: Check If Unified Auditing Is Enabled

Login to SQL*Plus as SYSDBA and run:

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

If the result is FALSE, it means Unified Auditing is not yet enabled.

Next, check if traditional auditing is still active:

SHOW PARAMETER AUDIT_TRAIL;

You’ll likely see:

audit_trail                          string      DB

This shows the old audit system is in use. Let’s turn that off first.

---

⚙️ Step 2: Disable Traditional Auditing

To enable Unified Auditing, we need to disable the traditional one first:

ALTER SYSTEM SET audit_trail='NONE' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;

We shut down the database because we are about to relink the Oracle binary files.

---

🔧 Step 3: Enable Unified Auditing at the Binary Level

Now log into your database server as the Oracle user and run the following:

cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle

This step relinks the Oracle binaries to turn on Unified Auditing.

Once it completes, start the database again:

STARTUP;

Now confirm if Unified Auditing is active:

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
-- Output should be: TRUE

🎉 You’ve successfully enabled Unified Auditing!

---

🗃️ Step 4: Move Audit Trail to a Dedicated Tablespace

By default, audit data is stored in the SYSTEM tablespace, which isn’t ideal. It’s best practice to create a separate tablespace just for audit data.

CREATE TABLESPACE audit_data 
DATAFILE '/usr/app/datafiles/CENTRALDB/audit_data.dbf' 
SIZE 11M;

Then, point Unified Auditing to that location:

BEGIN
  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_location_value => 'AUDIT_DATA');
END;
/

Verify the change:

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

This should show that the AUD$UNIFIED table is now using the AUDIT_DATA tablespace.

---

👤 Step 5: Create an Audit Viewer User

Let’s create a user who can only view audit logs, not change them:

CREATE USER c##auditor IDENTIFIED BY Welcome_123;

GRANT CREATE SESSION, 
      SELECT ANY DICTIONARY, 
      SELECT ANY TABLE 
TO c##auditor;

GRANT AUDIT_VIEWER TO c##auditor;

Now connect as that user:

CONNECT c##auditor/Welcome_123;

Check how many audit records are available:

SELECT COUNT(*) FROM audsys.unified_audit_trail;

---

🧹 Step 6: Clean Up Old Audit Data (Purging)

As audit logs grow over time, they need to be cleaned to free up space.

First, give the user the correct role:

GRANT AUDIT_ADMIN TO c##auditor;

Then purge old records:

BEGIN
  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;
/

Automate Purging with a Job

To automatically purge old data every 2 days:

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
    audit_trail_type            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
    audit_trail_purge_interval  => 2,
    audit_trail_purge_name      => 'Unified_Audit_Trail_Purge_Job',
    use_last_arch_timestamp     => TRUE,
    container                   => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/

---

🛡️ Step 7: Create a Custom Audit Policy

Let’s say we want to audit whenever C##AUDITOR creates a table.

First, create the audit policy:

CREATE AUDIT POLICY policy_for_auditor
  PRIVILEGES CREATE TABLE
  WHEN 'SYS_CONTEXT("USERENV", "SESSION_USER") = ''C##AUDITOR'''
  EVALUATE PER SESSION
  CONTAINER = CURRENT;

Then, enable it:

AUDIT POLICY policy_for_auditor;

---

🔎 Step 8: View and Analyze Audit Logs

To view recent audit records:

SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name
FROM unified_audit_trail
WHERE dbusername = 'C##AUDITOR';

Check active audit policies:

SELECT policy_name, 
       enabled_option, 
       entity_name, 
       success, 
       failure
FROM audit_unified_enabled_policies
WHERE policy_name = 'POLICY_FOR_AUDITOR';

You can also filter audit logs by time, action, or schema:

SELECT action_name, 
       event_timestamp, 
       object_name, 
       object_schema
FROM unified_audit_trail
WHERE event_timestamp >= SYSTIMESTAMP - INTERVAL '7' DAY
ORDER BY event_timestamp DESC;

---

🧾 Summary Table

Feature	Traditional Auditing	Unified Auditing
Centralized Audit Trail	❌	✅
Custom Policy-Based Auditing	❌	✅
Efficient Storage & Performance	❌	✅
Auditing SYS Operations	Partially	✅
Easy Data Purging	Manual	Automated
Compliance-Friendly	⚠️	✅

🧾 Final Notes

• You cannot truncate the AUDSYS.AUD$UNIFIED table manually.
• Unified auditing simplifies compliance audits and supports policy-based controls that were harder to enforce with traditional auditing.
• Make sure to monitor the size of AUDIT_DATA tablespace and purge old records regularly.

---

🧠 Conclusion

Unified Auditing in Oracle 19c is not just a security best practice—it’s a compliance imperative. By enabling and managing it effectively, you’re setting up your environment to be secure, auditable, and future-proof.