In this post I will show you how you can manage your audit trail using the package dbms_audit_mgmt.
It can be used for all kinds of audit trails, both OS, XML, unified and standard auditing.
You refer to them using the constants for audit_trail_types, found
here
In this post, I am using the DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
Note: If you simply want to purge the
entire audit trail, use the procedure below:
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);
END;
/
Be careful:
Setting the use_last_arch_timestamp to FALSE will effectlivly do a
TRUNCATE TABLE SYS.AUD$ behind the scenes and takes only a couple of minutes to execute, even with millions of rows to be purged. You may lose your audit data, which can have legal consequences.
If desirable, audit trails can be cleaned based on the "Last Archive Timestamp" value.
The Last Archive Timestamp represents the timestamp of the most recent audit record that was securely archived:
SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
no rows selected
No rows returned from this query means that there has been no previous cleanup performed.
In cases where you want to keep some of your audit data, you can manually set the "Last Archive Timestamp" to a value of your choice.
Here I set it to a month ago from today:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => SYSTIMESTAMP-30);
END;
/
If you try to use the SET_LAST_ARCHIVE_TIMESTAMP procedure
before cleanup has been initialized, you will receive error
ERROR at line 1:
ORA-46258: Cleanup not initialized for the audit trail
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2283
ORA-06512: at line 17
So make sure to initialize the cleanup first. Note that "initialization" will automatically relocate the AUD$ table to the SYSAUX tablespace.
Use the IS_CLEANUP_INITIALIZED procedure to verify the state of the cleanup before and after the the INIT_CLEANUP has been executed.
Put the following in a file called check_init_status.sql:
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.PUT_LINE('Cleanup is initialized.');
ELSE
DBMS_OUTPUT.PUT_LINE('Cleanup is not initialized.');
END IF;
END;
/
Check status:
sqlplus / as sysdba@check_init_status.sql
Cleanup is not initialized.
PL/SQL procedure executed.
To initialize cleanup of the
standard audit trail, put the following in a file called init_cleanup.sql:
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP (
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
default_cleanup_interval => 48);
END;
/
Initialize cleanup:
sqlplus / as sysdba@init_cleanup.sql
Check status again:
sqlplus / as sysdba@check_init_status.sql
Cleanup is initialized.
PL/SQL procedure executed.
Note that you need sufficient space in the SYSAUX tablespace to proceed. Autoextend on the datafiles will not help, there need to be sufficiently allocated space in advance:
ERROR at line 1:
ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1087
ORA-06512: at line 3
How many rows do we have in our standard audit trail?
select count(*) from sys.aud$;
How old is that data?
select min(ntimestamp#) "mindate", max(ntimestamp#) "maxdate"
from sys.aud$;
| "mindate" | "maxdate" |
| 21.11.2016 08:29:16,325777 | 04.02.2020 14:08:49,660074 |
So our data dates back to 2016. Time to clean up.
The "use_last_arch_timestamp" make sure I will preserve audit statements more recent than one month:
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
Let's check the status after the execution of the clean_audit_trail-procedure:
How many rows do we have left in our standard audit trail?
select count(*) from sys.aud$;
How old is that data?
select min(ntimestamp#) "mindate", max(ntimestamp#) "maxdate"
from sys.aud$;
| "mindate" | "maxdate" |
| 04.03.2020 08:09:01,325371 | 04.02.2020 14:08:49,660074 |