An audit investigation needs records that can still be found and interpreted after routine cleanup of the source trail. Preserve the records together with their source, collection scope, and verification evidence. A protected archive becomes useful when you can reconcile its coverage and retrieve the required events.
Oracle DBA Lesson 80 Part 1 — Preserve Audit Evidence Before Cleanup
An investigation extract and an archive that supports a cleanup boundary answer different questions.
| Scope | Question it answers | Required coverage |
|---|---|---|
| Investigation extract | What did a particular user do during an interval? | The selected user, events and interval, with the source identified |
| Archive supporting a cleanup boundary | Which earlier records can remain available after source cleanup? | Every record the proposed cleanup would include, for the exact trail, database and container scope |
A one-user investigation extract supplies only its stated subset. Establish continuous archive coverage for the entire prospective cleanup scope before recording a boundary. Retention requirements and legal holds then determine whether any source removal is permitted.
Define the source and interval
The manifest is the description that accompanies the evidence. Record:
- Source database identity, including the DBID where it applies, the instance identity, and the exact container name and identifier. Preserve container identity independently of a connection alias, which can change.
- Audit mode and the trail types and destinations in scope.
- The exact UTC start and end timestamps, their precision, and the convention start included, end excluded.
- The user and other investigation filters. Keep these separate from the broader archival scope.
- The extraction time, tool or procedure, collection identity, logs, known limitations, and unresolved errors.
Adjacent windows such as [09:00,10:00) and [10:00,11:00) fit together without duplicating the event at exactly 10:00. Use the same clock basis when extracting, reconciling, and retrieving events.
Query a bounded investigation
In the source container, bind the actual UTC timestamps. An audit viewer, normally through AUDIT_VIEWER, reads the trail for this analysis.
SELECT event_timestamp_utc, dbusername, action_name,
object_schema, object_name, return_code,
unified_audit_policies, fga_policy_name
FROM unified_audit_trail
WHERE dbusername = 'APP_USER'
AND event_timestamp_utc >= :start_utc
AND event_timestamp_utc < :end_utc
ORDER BY event_timestamp_utc;EVENT_TIMESTAMP_UTC gives the audit entry's UTC timestamp. EVENT_TIMESTAMP uses local time. Select and document the field deliberately. Client timestamp binds should use the same UTC basis as the selected column. Character values need an explicit conversion rather than dependence on session date formats.
For SQL*Plus, character binds need an explicit timestamp conversion:
VARIABLE start_utc VARCHAR2(26)
VARIABLE end_utc VARCHAR2(26)
BEGIN
:start_utc := '2026-10-01 09:00:00.000000';
:end_utc := '2026-10-01 10:00:00.000000';
END;
/
SELECT event_timestamp_utc, dbusername, action_name,
object_schema, object_name, return_code,
unified_audit_policies, fga_policy_name
FROM unified_audit_trail
WHERE dbusername = 'APP_USER'
AND event_timestamp_utc >=
TO_TIMESTAMP(:start_utc, 'YYYY-MM-DD HH24:MI:SS.FF6')
AND event_timestamp_utc <
TO_TIMESTAMP(:end_utc, 'YYYY-MM-DD HH24:MI:SS.FF6')
ORDER BY event_timestamp_utc;These binds are session-local. Confirm the source and container identity separately. This projection supports an investigation. It is not a complete archival schema. Preserve the additional identifiers and full field values the collection process requires. Avoid truncating SQL text, binds, or long policy lists during collection. SQL text and binds can themselves contain sensitive information.
Interpret the event
| Field | Meaning |
|---|---|
EVENT_TIMESTAMP_UTC | Time of creation of the audit entry in UTC |
DBUSERNAME | Database user associated with the action |
ACTION_NAME with AUDIT_TYPE | Operation and its audit context |
OBJECT_SCHEMA / OBJECT_NAME | Target object where applicable |
RETURN_CODE | Oracle error code; zero indicates that the audited action succeeded |
UNIFIED_AUDIT_POLICIES | Unified policies responsible for the record; mandatory records can have a null value |
FGA_POLICY_NAME | Fine-grained audit policy responsible for an FGA record |
RETURN_CODE = 0 indicates that the audited action succeeded. Establish what was returned from the query result and the application context, and keep that evidence with the investigation when it is required to explain the access. A return code is not a fetched-row count. Read the action and the audit type together, and preserve event identifiers when correlating records. See UNIFIED_AUDIT_TRAIL.
Inventory all destinations in the archive scope
Unified records can exist in the database and in operating system spillover .bin files. Mixed mode can also have traditional database, operating system, or XML trails. A successful query of one view establishes only that view's visible records under the current privileges and scope.
Document every relevant destination and any collection or loading still in progress. Oracle documents loading unified spillover files through DBMS_AUDIT_MGMT.LOAD_UNIFIED_AUDIT_FILES while the database is writable. Loading is an administrative operation with container-specific and release-specific conditions. Loading changes the source arrangement, and successfully loaded files are removed from their source directory. Preserve the required original-file evidence beforehand when the investigation requires it. Do not replace that procedure with manual deletion of spillover files.
Check both the root and the local container when the collection scope spans them. Some files associated with an active process need additional documented handling. Reconcile late-loaded events before claiming continuous archival coverage. See managing the unified audit trail.
Collect through a supported process
Use the organization's configured Oracle Audit Vault and Database Firewall collection process, or a documented Oracle export workflow appropriate to the installed release. Collection copies evidence. Source cleanup is a later operation.
Oracle's archiving guide also describes copying relevant audit records into an ordinary database table. A staging design needs an explicitly defined schema, the complete required fields, privileges, extraction consistency, and a protected export destination. The investigation projection above should not silently become the full archival payload. See archiving the audit trail.
For unified auditing, Oracle also documents a Data Pump full export restricted with INCLUDE=AUDIT_TRAILS, followed by the corresponding import procedure. It requires the documented full-export and import privileges and the correct container context. Include spillover records through the supported preparation before asserting a complete export. This path differs from attempting TABLES=UNIFIED_AUDIT_TRAIL as though the dictionary view were an ordinary owned table. The documented audit-trail export can cover more than the investigation window. Record its actual scope and contents in the manifest. Restoring audit policy metadata has separate full-export considerations. Verify the exact release-specific procedure before use. See unified audit export and import.
Keep the archive, the collection logs, and the manifest in access-controlled persistent storage. Use the encryption and storage protections the organization requires. A change of tablespace, or a movement of bytes within the same database, does not establish a retrievable independent evidence package.
Reconcile coverage and protect integrity
For the same closed source scope, compare record counts and covered intervals with the collected archive. Explain discrepancies, duplicates, missing destinations, collection errors, and late arrivals. If the source is changing, define how the collector closes an interval and reconciles later records. Two counts taken at different times are not a consistent snapshot by themselves.
The earliest and latest timestamps help locate coverage. Inspect the intervals between them. A package can contain both endpoints and still miss events in the middle. Counts supplement reconciliation. A matching total can hide substitutions, missing records, or duplicates.
Record integrity checksums for the final stored files, using the organization's approved algorithm, and protect the checksum manifest. Verify the checksums on retrieval. Checksums help detect content changes relative to the recorded files. Source reconciliation establishes which records those files should contain. Protect the manifest, because a file and an unprotected replacement hash can be changed together. These are operational verification practices. They are not automatic guarantees made by the Oracle trail view.
| Manifest item | Evidence to retain |
|---|---|
| Source and scope | Database/container, trail types, UTC intervals and filters |
| Completeness | Reconciled counts/intervals, destination inventory, late-arrival handling |
| Collection | Tool/procedure, extraction time, logs and errors |
| Integrity | Final stored-file checksums and protected manifest |
| Retrieval | Retrieved event, matching fields and test result |
Test retrieval from the stored archive
Choose a known non-sensitive test event recorded at the source. Retain its database and container, time, user, object, action, return code, policy, and the available event, session, and statement identifiers. Retrieve that event from the archived copy, using the collector's query interface or a documented restore or import into an isolated test destination.
Compare its key fields and the complete required payload with the retained source evidence. Verify the stored files against the manifest's checksums. Record the retrieval method, date, result, and any limitations. Test events near the relevant interval boundaries, and investigate gaps. One retrieved event demonstrates that retrieval route for that event. Combine it with coverage reconciliation and integrity verification for the whole claimed scope. Reading the archived copy does not purge source records or change cleanup settings, archive files, or existing schedules.
Define a verified archive boundary
The manifest may record a boundary only after the claimed coverage has been reconciled and the integrity and retrieval checks are complete. For a proposed 10:00 UTC boundary, establish complete continuous archival coverage for every earlier source record the later operation could include, in that exact trail, database, and container scope. A newly collected 09:00–10:00 window alone cannot establish coverage of older history. Retain the earlier verified archive manifests as part of the coverage chain.
If there is a gap, a failed collection, an unprocessed destination, or a late arrival, keep the earlier verified boundary until reconciliation is complete. Do not derive archive progress from the current date, or from the oldest or latest row in a filtered extract.
Oracle's last archive timestamp is supplied by an administrator and used by the cleanup infrastructure. Database audit types, including unified auditing, require UTC for this value. Recording that evidence in the manifest does not call SET_LAST_ARCHIVE_TIMESTAMP, run CLEAN_AUDIT_TRAIL, or create a purge job. See DBMS_AUDIT_MGMT.
Retention requirements and legal holds can require the source to be preserved even after archive verification. A timestamp-only cleanup has no automatic understanding of a selective legal hold. Keep the boundary conservative, retain the affected interval, or use a separately reviewed supported hold process before any removal. See archive timestamp and purge strategy and purging audit trail records.
Recap
Preserve the source identity, the interval, the destinations, and the verification evidence with the records. An investigation extract covers only its filters. A cleanup boundary needs continuous coverage of every record that operation could remove, including older history. Reconcile the counts and intervals, protect checksums of the final stored files, and retrieve a known event from the archive before recording the boundary. Retention requirements and legal holds still govern any later source removal.
No comments:
Post a Comment