AUDIT_TRAIL - Configure Oracle Database Auditing

AUDIT_TRAIL

Overview

AUDIT_TRAIL controls where Oracle writes traditional (non-unified) audit records generated by AUDIT statements. When set to DB, audit records are written to the SYS.AUD$ table in the database. When set to OS, records are written to the operating system audit facility (e.g., syslog on Linux). When set to XML, records are written as XML files to the audit directory. Setting it to NONE disables traditional auditing entirely. The _EXTENDED variants (DB_EXTENDED, XML_EXTENDED) additionally capture the SQL text and bind variable values associated with each audited statement.

In Oracle 12c, Unified Auditing was introduced as a replacement for traditional auditing. In Oracle 12c and later, the AUDIT_TRAIL parameter controls only traditional auditing; unified audit policies are managed separately via CREATE AUDIT POLICY. In Oracle 23ai, traditional auditing is desupported and AUDIT_TRAIL has no effect unless mixed-mode auditing is active. For Oracle 12c+ installations, migrating to unified auditing is strongly recommended.

Parameter Type: Static (requires instance restart to change) Default Value: DB (Oracle 12c+); NONE in pure unified auditing mode Valid Values: NONE, OS, DB, DB_EXTENDED, XML, XML_EXTENDED Available Since: Oracle 8i Modifiable: No — SCOPE=SPFILE only; takes effect on next startup PDB Modifiable: Yes (12c+, within CDB limits)

Configuration

Viewing Current Value

-- Check current AUDIT_TRAIL setting
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'audit_trail';

-- Check SPFILE value
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'audit_trail';

-- Check if unified auditing is active (12c+)
SELECT value
FROM v$option
WHERE parameter = 'Unified Auditing';

-- View the audit directory path for OS/XML audit files
SELECT name, value
FROM v$parameter
WHERE name IN ('audit_trail', 'audit_file_dest', 'audit_sys_operations')
ORDER BY name;

-- Check how many audit records are in SYS.AUD$ (when AUDIT_TRAIL=DB)
SELECT COUNT(*) AS total_audit_records,
       MIN(timestamp#) AS oldest_record,
       MAX(timestamp#) AS newest_record
FROM sys.aud$;

Setting the Parameter

-- Enable database auditing (records go to SYS.AUD$)
ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;

-- Enable extended database auditing (includes SQL text and bind variables)
ALTER SYSTEM SET audit_trail = DB_EXTENDED SCOPE=SPFILE;

-- Write audit records to OS (syslog on Linux, Event Log on Windows)
ALTER SYSTEM SET audit_trail = OS SCOPE=SPFILE;

-- Write audit records as XML files to AUDIT_FILE_DEST
ALTER SYSTEM SET audit_trail = XML SCOPE=SPFILE;

-- Disable traditional auditing entirely
ALTER SYSTEM SET audit_trail = NONE SCOPE=SPFILE;

-- After changing, restart the instance
-- SHUTDOWN IMMEDIATE;
-- STARTUP;

-- Verify after restart
SELECT name, value
FROM v$parameter
WHERE name = 'audit_trail';

Tuning Guidance

Recommended Values

Environment	Recommended Value
Oracle 11g and earlier, compliance required	DB_EXTENDED
Oracle 11g and earlier, minimal auditing	DB
Oracle 12c+ (mixed mode auditing)	DB or XML while migrating to unified
Oracle 12c+ (pure unified auditing)	NONE (unified auditing is separate)
Oracle 19c+ (new installations)	NONE + use unified audit policies
Oracle 23ai	NONE (traditional auditing desupported)
High-security environments (external SIEM)	XML_EXTENDED (parse XML files into SIEM)
Environments where SYS.AUD$ growth is a concern	OS or XML

For Oracle 12c and later, the recommended path is to migrate to unified auditing and set AUDIT_TRAIL=NONE. Traditional auditing is a legacy feature and unified auditing provides better performance, richer policy control, and guaranteed audit record delivery.

How to Size

The main operational concern with AUDIT_TRAIL=DB is the growth of SYS.AUD$. For AUDIT_TRAIL=OS or XML, monitor the audit file destination disk usage.

-- Check SYS.AUD$ table size
SELECT s.segment_name,
       s.bytes / 1024 / 1024 AS size_mb,
       s.extents
FROM dba_segments s
WHERE s.owner = 'SYS'
  AND s.segment_name = 'AUD$';

-- Count audit records by action type
SELECT action#, action_name, COUNT(*) AS record_count
FROM sys.aud$ a
JOIN audit_actions ac ON a.action# = ac.action
GROUP BY action#, action_name
ORDER BY record_count DESC
FETCH FIRST 20 ROWS ONLY;

-- Check audit file destination space (for OS/XML modes)
SELECT value AS audit_file_dest
FROM v$parameter
WHERE name = 'audit_file_dest';
-- Then check OS-level disk usage: du -sh $ORACLE_BASE/admin/*/adump/

Monitoring

-- Monitor SYS.AUD$ growth rate
SELECT TRUNC(timestamp#, 'DD') AS audit_date,
       COUNT(*) AS records_per_day
FROM sys.aud$
WHERE timestamp# > SYSDATE - 30
GROUP BY TRUNC(timestamp#, 'DD')
ORDER BY audit_date;

-- Find top audited users
SELECT userid, COUNT(*) AS audit_events
FROM sys.aud$
WHERE timestamp# > SYSDATE - 7
GROUP BY userid
ORDER BY audit_events DESC
FETCH FIRST 10 ROWS ONLY;

-- Check unified audit trail if running 12c+ in mixed mode
SELECT event_timestamp, db_username, action_name,
       object_schema, object_name, unified_audit_policies
FROM unified_audit_trail
WHERE event_timestamp > SYSTIMESTAMP - INTERVAL '1' HOUR
ORDER BY event_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Common Issues

Issue 1: SYS.AUD$ Table Growing Uncontrolled

When AUDIT_TRAIL=DB and many objects or users are audited, SYS.AUD$ can grow to hundreds of gigabytes, consuming space in the SYSTEM tablespace and slowing audit queries.

Resolution: Regularly archive and purge old audit records using DBMS_AUDIT_MGMT. Consider switching to XML or OS mode to write audit records outside the database, or migrate to unified auditing which supports automatic purge policies.

-- Initialize audit management and set a purge interval
EXEC DBMS_AUDIT_MGMT.INIT_CLEANUP(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  default_cleanup_interval => 24);

-- Set an archive timestamp (purge records older than 30 days)
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  last_archive_time => SYSDATE - 30);

-- Run purge manually
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
  audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  use_last_arch_timestamp => TRUE);

Issue 2: AUDIT_TRAIL=NONE Disables Mandatory Auditing

Setting AUDIT_TRAIL=NONE disables traditional statement auditing, but Oracle always audits certain privileged operations (SYS operations) regardless of this setting when AUDIT_SYS_OPERATIONS=TRUE.

Resolution: Understand that SYS-level auditing is controlled separately. Verify compliance requirements before disabling traditional auditing.

-- Check SYS-level auditing setting
SELECT name, value
FROM v$parameter
WHERE name = 'audit_sys_operations';

-- SYS audit records go to OS audit file (not SYS.AUD$) regardless of AUDIT_TRAIL
SELECT value AS sys_audit_dest
FROM v$parameter
WHERE name = 'audit_file_dest';

Issue 3: Mixed Mode Auditing in 12c+ Creates Duplicate Records

In Oracle 12c with mixed mode auditing (both traditional and unified auditing active), some audit events may be recorded twice — once in SYS.AUD$ and once in the unified audit trail. This inflates storage consumption.

Resolution: Migrate fully to unified auditing by disabling traditional auditing (AUDIT_TRAIL=NONE) after converting all AUDIT statements to CREATE AUDIT POLICY definitions.

-- Check current auditing mode
SELECT value
FROM v$option
WHERE parameter = 'Unified Auditing';

-- List traditional audit settings that need to be converted
SELECT audit_option, success, failure
FROM dba_stmt_audit_opts
ORDER BY audit_option;

SELECT owner, object_name, object_type, alt, aud, com, del, exe,
       gra, ind, ins, loc, ren, sel, upd, ref, exe
FROM dba_obj_audit_opts
WHERE alt != '-/-' OR ins != '-/-' OR sel != '-/-'
ORDER BY owner, object_name;

Related Parameters

• AUDIT_FILE_DEST — Directory for OS and XML audit files when AUDIT_TRAIL=OS or XML
• AUDIT_SYS_OPERATIONS — Controls whether SYS-privileged operations are audited; independent of AUDIT_TRAIL
• SEC_CASE_SENSITIVE_LOGON — Password security parameter often reviewed alongside auditing configuration
• REMOTE_LOGIN_PASSWORDFILE — Controls SYSDBA authentication; SYS operations are always audited to OS

Related Errors

• ORA-00604: Error Occurred at Recursive SQL Level — Can occur when SYS.AUD$ is full or SYSTEM tablespace runs out of space due to audit record overflow
• ORA-01653: Unable to Extend Table — SYS.AUD$ table or its tablespace has run out of space
• ORA-01031: Insufficient Privileges — Attempts to query SYS.AUD$ without DBA or AUDIT_ADMIN role

Version Notes

Version	Notes
Oracle 8i	AUDIT_TRAIL introduced; DB and OS modes available
Oracle 10g	XML and XML_EXTENDED modes added
Oracle 11g	DB_EXTENDED mode added; fine-grained auditing (FGA) enhanced
Oracle 12c	Unified Auditing introduced; AUDIT_TRAIL controls traditional auditing only; mixed mode is default
Oracle 18c/19c	Pure unified auditing mode available; DBMS_AUDIT_MGMT enhanced for automated purge
Oracle 21c	Traditional auditing deprecated; migration to unified auditing strongly recommended
Oracle 23ai	Traditional auditing desupported; AUDIT_TRAIL parameter is ignored; use unified audit policies exclusively