Harden the audit log

By default, adm_audit_log is a normal table. A session that has DML on the schema can change or delete its rows. The hardening scripts in the delivery move the log into an Oracle immutable table. Nobody can update or delete its rows, not even the schema owner. The scripts also offer a blockchain table, which also detects changes made below the database.

Hardening is optional and it is not part of install.sql or upgrade.sql. It adds trust to the audit log for internal accountability. It is not a certified compliance feature.

What hardening changes and what it does not

	Plain table	Immutable table	Blockchain table
update, delete, truncate of rows	possible	refused	refused
A row older than its retention period	can be deleted	removed only by the purge procedure	removed only by the purge procedure
An edit of the stored data below the database	not detected	not detected	detected by dbms_blockchain_table.verify_rows
Cost of an insert	base	about the same	about 2.5 times the base cost

On a test database with 23ai, an insert into a plain table took about 25 µs, into an immutable table about 30 µs, and into a blockchain table about 70 µs. An audit row is one of many statements in an operation, so the difference is small.

Hardening does not protect against these actions:

• A forged insert. Anyone who can insert into adm_audit_log can add a row.
• A change to adm_audit_api or a dropped audit trigger that stops ADM from writing rows. Use the unified auditing of the database for DDL on the schema.
• With the immutable table only: an edit of the data files below the database. The blockchain table detects this. For full value, store the chain tips that STATUS prints outside the database. A chain that someone rebuilds from the first row is then detected.

Requirements

Item	Requirement
Immutable table	Oracle Database 19.11 or later, with COMPATIBLE set to 19.11 or higher
Blockchain table	Oracle Database 19.11 or later. On 19.10, patch 32431413 is also necessary
License	Oracle describes both table types as features of the database that need no extra licence
Edition	Not confirmed for Standard Edition 2. The check in the next section tests it on your database
Privileges	The schema owner of ADM, with a quota that fits a second copy of the audit log

ADM tested the scripts on Oracle 23ai. Run the check on every other database before you plan a maintenance window.

Make sure that the database supports hardening

Unpack the delivery archive and open the audit-hardening directory. Connect as the schema owner of ADM and run the script without arguments. The default action is CHECK, which changes nothing:

@harden_audit_log.sql

The check installs the package adm_audit_hardening in the schema. Then it checks the release, COMPATIBLE, the column types of adm_audit_log, the quota, and the objects that depend on the table. It creates a scratch table of the requested kind, writes one row, and drops it. If the probe fails, for example because the edition does not support it, the script stops with the reason. The scratch table has no drop protection, so the check leaves nothing behind.

Harden the audit log

Hardening swaps the table under the name adm_audit_log. Use a maintenance window.

1. Make the APEX application unavailable and stop the jobs of ADM.

2. Run the script with APPLY. The arguments are the kind of table, the row retention in days, and the idle days that protect the table from being dropped:

   @harden_audit_log.sql APPLY IMMUTABLE 365 16

   For a blockchain table, use BLOCKCHAIN instead of IMMUTABLE.

3. Read the output. The script prints the number of rows that it copied and the time during which adm_audit_log did not exist.

4. Make the application available again.

Argument	Default	Meaning
CHECK or APPLY	CHECK	CHECK runs the preflight only
IMMUTABLE or BLOCKCHAIN	IMMUTABLE	The kind of table
Row retention in days	365	Nobody can delete a row for this period after it was inserted. The minimum is 16 days. Oracle accepts an increase later, but not a decrease
Idle days	16	Nobody can drop the table until no row was inserted for this period

The script does these steps:

1. It creates the table adm_audit_log_h of the requested kind and copies the rows of adm_audit_log into it.
2. It renames adm_audit_log to adm_audit_log_plain. This rename waits up to 30 seconds for open transactions, so that no uncommitted row stays behind.
3. It creates a view named adm_audit_log on adm_audit_log_h. Every package, trigger, report and %type anchor keeps using the name adm_audit_log.
4. It copies the rows that were written during the copy, and makes sure that every row of the old table is in the new table with the same content.
5. It recompiles the objects that depend on the table, writes one HARDEN_AUDIT_LOG row, and makes the old table read only.

The HARDEN_AUDIT_LOG row records the number of legacy rows and a SHA-256 digest of their content. It states which history was intact when you hardened the log.

Hardening is hard to reverse

The retention of an immutable table cannot decrease, and Oracle does not let anyone drop the table until it has been idle for the idle days. The idle days start at the last insert, so an active log is not idle. Do not harden a test or development schema that you must drop or refresh soon. Choose the row retention that your policy needs: a row cannot be removed before it expires.

What users see during the swap

For a few milliseconds adm_audit_log does not exist. An audited change that runs at that time fails, because its audit row cannot be written. Reads, denials and session events are not audited, because their writers do not raise errors. The recompilation takes seconds to a minute, and each session that had adm_audit_api loaded gets the error ORA-04068 once. A maintenance window avoids all of this.

The idle days above 16

Oracle accepts at most 16 idle days when it creates the table. The script creates the table with 16 and then increases the value. Where BLOCKCHAIN_TABLE_RETENTION_THRESHOLD has its default of 16, the increase needs the TABLE RETENTION privilege. Without it, the table keeps 16 idle days and the script says so.

The time of a row

Oracle does not allow a time zone type in an immutable or blockchain table. The hardened table stores action_date as a timestamp in UTC. The view adm_audit_log returns it as a timestamp with local time zone, so a query such as action_date > systimestamp - 7 has the same meaning as before. An insert that names action_date fails with ORA-01733: the database sets the time of every row.

Review the state of the audit log

Connect as the schema owner and run:

@audit_log_hardening.sql STATUS

STATUS prints these values:

• whether the log is plain or hardened, the kind of table, and the retention;
• the number of rows;
• whether the legacy rows still have the digest of the HARDEN_AUDIT_LOG row. The result is MATCH, MISMATCH, or a difference in the number of rows;
• for a blockchain table, the result of dbms_blockchain_table.verify_rows and the chain tips (instance, chain, sequence number, and hash).

Store the chain tips in a place that the database administrators cannot change. With the tips, a rebuilt chain is also detected.

Purge expired rows

On a hardened log, a plain delete fails with ORA-05715. Oracle removes a row only after its row retention has expired, and only through the purge procedure. A row expires when its retention counts from the time of its insert into adm_audit_log_h. A legacy row expires one retention period after the hardening, whatever its action_date.

Count the rows that can be purged:

@audit_log_hardening.sql PURGE CHECK

Purge them. The date limits the purge to rows that were inserted before that day:

@audit_log_hardening.sql PURGE APPLY 2026-01-01

The purge copies each expired row to the plain table adm_audit_log_hist, commits, and then calls delete_expired_rows with the same limit. A row that is already in the history table is not copied again.

The purged rows are not protected. They are also not in adm_audit_log, in adm_report_audit_log_v, or on the audit page. ADM does not archive them outside the database. Export adm_audit_log_hist if your policy needs that.

Return to the plain table

To return, run the unharden script. The default action is CHECK:

@unharden_audit_log.sql APPLY

Use a maintenance window, for the reasons in Harden the audit log. The script copies every row that was written during the hardening back into adm_audit_log_plain, drops the view, and renames the plain table back to adm_audit_log. It writes one UNHARDEN_AUDIT_LOG row.

The hardened table adm_audit_log_h stays. It keeps all its rows, and nothing writes to it. Oracle refuses to drop it until its idle days have passed. To drop it later, run:

@audit_log_hardening.sql DROP_RETIRED

If the table cannot be dropped yet, the script prints the date from which Oracle allows it. If you harden again before that date, the script reuses the table when the kind and the columns are the same.

Upgrade a hardened schema

upgrade.sql does not run on a hardened schema. It looks for the table adm_audit_log, finds a view, and fails with ORA-00955. A release that adds a column to the audit log also cannot change an immutable table. To upgrade:

1. Run unharden_audit_log.sql APPLY.
2. Run upgrade.sql, as described in Upgrading.
3. Run harden_audit_log.sql APPLY again.

If the upgrade changed the columns of the audit log, the script cannot reuse the retired table. It stops until Oracle allows you to drop that table, which is 16 idle days after the unharden.

Limits

• ADM has not tested the scripts on Oracle 19c, on Standard Edition 2, or with Data Pump export and import of the hardened table. Test an export and an import in a copy of your schema before you depend on them.
• ADM has not tested the swap under concurrent load from many sessions.
• Hardening applies to adm_audit_log only. The audit trail does not cover the other limits listed in What is not recorded.