In the realm of Oracle database management, maintaining an effective
audit trail is crucial for ensuring data integrity and security. Oracle's
approach to auditing has evolved, with unified audit records now taking
center stage. Here’s an in-depth look at how Oracle handles audit trails and
how you can manage them effectively.
Understanding Unified Audit Records
Oracle databases generate audit records during or after the execution of
audited SQL statements. These records are written to the internal relational
table within the AUDSYS schema. Unlike previous releases, where audit records were stored in
SecureFile LOBs, the current system utilizes a partitioned table. This
partitioned table uses the EVENT_TIMESTAMP as a partition key, with a default partition interval set to one day. If
the database version lacks partitioning support, the audit records are
stored in a regular, non-partitioned table.
Unified audit records require significantly more disk space—about 50%
more—compared to traditional audit records. It’s essential to plan for this
increased storage requirement to avoid potential issues.
Real-Time and Historical Audit Policies
Unified audit policies in Oracle take effect immediately when enabled,
applying to ongoing user sessions. Conversely, disabling these policies also
takes immediate effect. However, changes to existing unified audit policies
will only apply to new sessions, not retroactively to sessions that were
already in progress.
Handling Audit Trail Failures
In scenarios where the database is unable to write audit records to the
database (such as when it is read-only, when the tablespace is full or
offline, or if the audit tablespace is offline), Oracle writes audit records
to operating system spillover files in .bin
format. These spillover files continue to receive audit records until the
operating system’s disk space is exhausted. Once the disk is full, user
transactions that generate audit records will fail with an
ORA-02002 error.
SYSLOG Integration
Oracle provides the AUDIT_SYSLOG_LEVEL
parameter to facilitate writing SYS and standard OS audit records to the
system audit log using the SYSLOG utility. When AUDIT_SYSLOG_LEVEL
is set and SYS auditing is enabled (AUDIT_SYS_OPERATIONS = TRUE), SYS audit records are directed to the system audit log. If standard
audit records are being sent to the operating system (AUDIT_TRAIL=os), these records are also written to the system audit log. However, be
aware that most SYSLOG configurations have a limitation of 1024 bytes, which
means only a subset of key fields can be captured. For a complete record,
refer to the UNIFIED_AUDIT_TRAIL.
Purging Unified Audit Trail
Managing disk space and keeping the audit trail efficient involves regular
purging of old records. Follow these steps to purge the unified audit
trail:
1. Set Last Archive Timestamp:
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => <TimeStampValue> + INTERVAL '30' DAY );
Replace <TimeStampValue> with the timestamp of the last archived
record.
2. Check Last Archive Timestamp:
SELECT AUDIT_TRAIL, LAST_ARCHIVE_TS
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS
WHERE AUDIT_TRAIL = 'UNIFIED AUDIT TRAIL';
Use the LAST_ARCHIVE_TS value from this query for the new
<TimeStampValue> in the next step.
3. Clean the Audit Trail:
EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE );
By understanding and effectively managing these aspects of Oracle’s audit
trail, you can ensure that your database remains secure, compliant, and
performant. Regular monitoring and maintenance of the audit trail will help
prevent potential issues and ensure that your auditing processes run
smoothly.