A unified audit purge removes eligible source audit records using an administrator-supplied archive boundary. The boundary must come from verified archive coverage, and retention requirements and legal holds must permit removal. Setting the timestamp and deleting records are separate operations.
Oracle DBA Lesson 80 Part 2 — Purge with a Verified UTC Watermark
This lesson continues the evidence-preservation work in Part 1. It explains a local Oracle Database 19c operation: prepare an already-UTC value, store it, inspect the setting, purge with that setting, and verify the remaining source records and archive retrieval.
Records created before the timestamp
The boundary must represent continuous, reconciled archive coverage for every record the operation could include before that time: all relevant users, database identities, audit destinations, and earlier history. Retention requirements and legal holds must permit removal of that interval. See purging audit trail records.
With use_last_arch_timestamp => TRUE, cleanup uses a strict-before comparison. It removes records created before the configured timestamp. For a boundary of 2026-10-01 10:00:00 UTC, the record exactly at the boundary remains. The protected archive already contains the older evidence. The purge does not create an archive. See DBMS_AUDIT_MGMT.
| Database record time, UTC | Before cleanup | After cleanup |
|---|---|---|
| 09:15:00 | Present | Removed from source |
| 09:50:00 | Present | Removed from source |
| 10:00:00 | Present | Retained |
| 10:20:00 | Present | Retained |
This table is about database records. Operating system spillover files have documented file-management and loading conditions. Do not infer that each file is deleted according to individual rows in this table.
Establish the operation's scope
Call the package in the current writable pluggable database with AUDIT_ADMIN. Oracle also allows the same package calls through SYSDBA. Confirm the connection and record its database and container identity:
SHOW USER
SHOW CON_NAME
SELECT SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name,
SYS_CONTEXT('USERENV', 'CON_NAME') AS container_name,
SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user
FROM dual;CONTAINER_CURRENT keeps the package operation in the current container. It does not restrict cleanup to a particular application user. A user filter from an earlier investigation query does not carry over into CLEAN_AUDIT_TRAIL.
This operation supplies no database_id filter. Reconcile the full scope of records the selected operation can include, including relevant database IDs. Inspect the timestamp configuration's database identity as well as its trail and time.
AUDIT_TRAIL_UNIFIED can include unified database audit records and operating system spillover files. Reconcile their collection, archive, and documented loading requirements before cleanup. Loading unified audit files has its own source-file lifecycle and privileges. Use the procedure documented for the exact release. Do not restart the database or remove files in place of that procedure. In mixed mode, traditional database, operating system, or XML trails have separate type-specific procedures and timestamp rules. An Oracle Audit Vault managed environment must follow its documented collection and purge controls. See managing the unified audit trail.
Prepare an already-UTC input
Populate the bind from the verified archive manifest for the intended scope. Do not substitute the current date minus a retention interval as proof of archival.
VARIABLE archived_through_utc VARCHAR2(19)
EXEC :archived_through_utc := '2026-10-01 10:00:00';For unified auditing, the supplied timestamp must represent UTC. TO_TIMESTAMP with format YYYY-MM-DD HH24:MI:SS parses those characters. It does not convert a local wall-clock time into UTC. Convert and validate the source time correctly before supplying it.
A null guard catches an unset bind. It does not verify archive completeness, retention, legal holds, or the connected container:
BEGIN
IF :archived_through_utc IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'UTC boundary required');
END IF;
END;
/In SQL*Plus, stop on a failed guard or any subsequent error. A script that continues after an error can execute later commands despite an earlier failure.
Store the boundary
BEGIN
IF :archived_through_utc IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'Verified UTC boundary required');
END IF;
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TO_TIMESTAMP(
:archived_through_utc, 'YYYY-MM-DD HH24:MI:SS'),
container => DBMS_AUDIT_MGMT.CONTAINER_CURRENT);
END;
/SET_LAST_ARCHIVE_TIMESTAMP records the supplied boundary. It does not verify the archive, collect records, or perform source deletion. The manifest retains the evidence supporting that value.
Unified auditing does not need INIT_CLEANUP. Passing the unified trail type to that traditional preparation procedure raises ORA-46250. See DBMS_AUDIT_MGMT.
Inspect the setting before deletion
SELECT audit_trail, last_archive_ts, database_id, container_guid
FROM dba_audit_mgmt_last_arch_ts
WHERE audit_trail = 'UNIFIED AUDIT TRAIL';The video shows a compact projection of this query. A possible display for the setting is:
| AUDIT_TRAIL | LAST_ARCHIVE_TS |
|---|---|
| UNIFIED AUDIT TRAIL | 2026-10-01 10:00:00 +00:00 |
LAST_ARCHIVE_TS is a timestamp with time zone. Confirm its actual value and time-zone basis, plus the intended database and container identity. Display formatting depends on the session. A configured timestamp is evidence of the setting. Archive verification comes from the manifest and its supporting checks.
Oracle documents GET_LAST_ARCHIVE_TIMESTAMP for circumstances where this view is not populated, including a database opened read-only. A missing row does not mean the archive is complete. See DBA_AUDIT_MGMT_LAST_ARCH_TS.
Run the local cleanup
Only after confirming the setting and the earlier conditions:
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;
/AUDIT_TRAIL_UNIFIEDselects the unified audit scope, subject to its documented database and file behavior.TRUEuses the configured last archive timestamp.FALSEdisregards that timestamp and can remove records outside the intended archived interval.CONTAINER_CURRENTretains the local container scope. Do not substituteCONTAINER_ALLfor this local cleanup.
Keep the documented defaults for parameters not shown. drop_partition_only => TRUE can leave older rows in a partition that also contains newer records. The call above keeps the default FALSE. Do not manipulate AUDSYS tables directly.
A purge can generate redo and consume resources. Plan a suitable operating window and monitor the operation. Database space becoming reusable does not establish that an underlying physical file has shrunk. See manually purging the audit trail.
Verify the outcome
Use the same UTC basis and scope for before and after inspection. Compare a recorded baseline with remaining rows in a bounded interval around the boundary:
SELECT event_timestamp_utc, dbusername, action_name
FROM unified_audit_trail
WHERE event_timestamp_utc >= TIMESTAMP '2026-10-01 09:00:00'
AND event_timestamp_utc < TIMESTAMP '2026-10-01 11:00:00'
ORDER BY event_timestamp_utc;Confirm identifiable newer records remain. Check the boundary case when a suitable record exists. An empty query alone does not demonstrate correct preservation. Retrieve a known older event from the stored archive, and compare its time, identity, action, and required fields with the recorded source baseline. Investigate discrepancies and pending spillover collection. See UNIFIED_AUDIT_TRAIL.
Package calls are audited. Unified audit records can support checking the cleanup call and its return code. A successful call does not independently prove full archive coverage, retrieval, or physical space reclamation. DBA_AUDIT_MGMT_CLEAN_EVENTS concerns traditional audit trails. Do not use it as unified purge history. See audit trail management views.
Source records removed by purge require the archive or a prepared restore for recovery. ROLLBACK is not a reversal plan for this cleanup. Keep the archive protected and retained according to the applicable policy.
Scheduling and failed collection
Prove archive progress and failure handling before scheduling cleanup. If collection fails, keep the last verified boundary unchanged, investigate collection and capacity, and protect unarchived records. A full audit tablespace does not justify advancing the boundary beyond verified coverage.
Inspect a later purge job's container scope and USE_LAST_ARCHIVE_TIMESTAMP behavior through the documented audit management job views. Completing the manual cleanup does not create a recurring job. See scheduling an automatic purge job.
Recap
Prepare a verified UTC value, store it with SET_LAST_ARCHIVE_TIMESTAMP, and inspect the configured trail, time, and database identity before any deletion. CLEAN_AUDIT_TRAIL with use_last_arch_timestamp => TRUE removes source records created before that timestamp and leaves the boundary record in place. The purge does not create the archive, and the configured timestamp does not prove coverage. If collection fails, keep the last verified boundary. Recover removed source records from the archive or a prepared restore, not with ROLLBACK.
No comments:
Post a Comment