Oracle AUDIT Housekeeping
One-Time Initialisation
Before the audit trail can be cleaned up for the first time, a one-time initialisation process must be executed.
This process only needs to be performed once per database instance. Once executed, it does not need to be run again for future audit trail cleanup operations.Check
Check if audit trail cleanup is initialized:
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
Initialize
BEGIN
dbms_audit_mgmt.init_cleanup(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_DB_STD,
default_cleanup_interval => 24 );
END;
/
NOTE: To execute initialization process on a Logical Standby database, the SQL Apply process must be stopped.Purge
Each time you purge data from the audit trail you need to give Oracle a timestamp. All entries older than the timestamp will be purged in the next step.
BEGIN
dbms_audit_mgmt.set_last_archive_timestamp(audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time => TO_TIMESTAMP('2009-09-30 10:00:00','YYYY-MM-DD HH24:MI:SS'), rac_instance_number => NULL);
END;
/
Where 2009-09-30 10:00:00 is your specified timestampTo check the setting in effect use...
SELECT *
FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
To purge the audit entries older than the timestamp set in the previous step...
BEGIN
dbms_audit_mgmt.clean_audit_trail(audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
use_last_arch_timestamp => TRUE);
END;
/
The procedure uses the CURRENT DBID to select and remove audit entries. If your database has been cloned or your DBID has changed, the procedure will not clear all entries. (1)Batch Size
The procedure automatically groups the deletes into batches and executes each batch as a single transaction.
The Batch size is shown in the DBA_AUDIT_MGMT_CONFIG_PARAMS view.
The batch size defaults to 10000 rows and valid values can be anywhere between 100 and 1000000.
The most common reason to change this value is if the transactions are taking too long.
Update using...
EXEC dbms_audit_mgmt.set_audit_trail_property(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
DBMS_AUDIT_MGMT.DB_DELETE_BATCH_SIZE,10000);
Move AUDIT Trail
If the AUDIT trail is not in a suitable tablespace (e.g. if it's in SYSTEM), you can move it using these steps...
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_DATA') ;
END;
/