- 11 Oct, 2023
- 0 Comments
- 4 Mins Read
Purge AUD$ Table Using DBMS_AUDIT_MGMT
Oracle 11gR2 introduced DBMS_AUDIT_MGMT for managing audit trails.
The growth of AUD$ can impact the performance of the database. So purging it regularly is the best practice followed by DBA’s and DBMS_AUDIT_MGMT makes it easier.
Follow below steps for puring aud$ table. :
1. Make sure AUD$ table is not in SYSTEM table tablespace. :
If AUD$ table is present in SYSTEM tablespace , then move it to a a new dedicated tablespace using below script.select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 ------------ ------------ ------------------ ------------------------------ --------------- SYS AUD$ TABLE SYSTEM 176 SQL> BEGIN DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'TS_AUDIT'); END; / SQL> 2 3 4 5 PL/SQL procedure successfully completed. SQL> select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME BYTES/1024/1024 ------------ ------------ ------------------ ------------------------------ --------------- SYS AUD$ TABLE TS_AUDIT 176
2. Check the audit mgmt configuration parameters:
COLUMN parameter_name FORMAT A30 COLUMN parameter_value FORMAT A20 COLUMN audit_trail FORMAT A20 SET LINES 2000 SQL> SELECT * FROM dba_audit_mgmt_config_params; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- -------------------- AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL AUDIT FILE MAX AGE 5 OS AUDIT TRAIL AUDIT FILE MAX AGE 5 XML AUDIT TRAIL AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL DB AUDIT TABLESPACE TS_AUDIT STANDARD AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL AUDIT WRITE MODE QUEUED WRITE MODE UNIFIED AUDIT TRAIL 14 rows selected.
3. Initialize the clean job:
Please Note – If you haven’t moved the AUD$ table out of SYSTEM tablespace, then it the below script will move the AUD$ to SYSAUX tablespace by default.SQL> BEGIN DBMS_AUDIT_MGMT.init_cleanup( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, default_cleanup_interval => 12 /* hours */); END; / PL/SQL procedure successfully completed.
4. Validate whether initialization is success or not:
It should return YES
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;
/
YES
PL/SQL procedure successfully completed.
5. Set LAST_ARCHIVE_TIMESTAMP:
This value allows the audit record to be purged before a particular date. Suppose LAST_ARCHIVE_TIME is set to SYSTIMESTAMP -30, then it will keep only recent 30 days record and purge the all audit records dated before 30 days.SQL> BEGIN DBMS_AUDIT_MGMT.set_last_archive_timestamp( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, last_archive_time => SYSTIMESTAMP-30); END; / 2 3 4 5 6 PL/SQL procedure successfully completed. SQL> COLUMN audit_trail FORMAT A20 SQL> COLUMN last_archive_ts FORMAT A40 SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts; AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS DATABASE_ID CONTAINER_GUID -------------------- ------------ ---------------------------------------- ----------- --------------------------------- STANDARD AUDIT TRAIL 0 11-OCT-23 11.29.39.000000 AM +00:00 552547857 183E654F83D9063AE0540010E0202A24
6. If you want to run the purge instantly( one time):
BEGIN DBMS_AUDIT_MGMT.clean_audit_trail( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, use_last_arch_timestamp => TRUE); END; /
7. Schedule a job to purge AUD$ regularly.
Below job will run daily and delete all the aud$ records before systimestamp- 30 .SQL[SYS@TCRMDB03]SQL>>]BEGIN DBMS_AUDIT_MGMT.create_purge_job( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL, audit_trail_purge_interval => 24 /* hours */, audit_trail_purge_name => 'PURGE_DB_AUDIT_TRAILS', use_last_arch_timestamp => TRUE); END; / PL/SQL procedure successfully completed. BEGIN DBMS_SCHEDULER.create_job ( job_name => 'set_last_archive_aud_timestamp', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30); END;', start_date => SYSTIMESTAMP, repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;', end_date => NULL, enabled => TRUE, comments => 'Update last_archive_timestamp'); END; /
8. Updating audit properties.
Note:- If the AUD$ table, lot of rows ( around 2 to 3 crores), then setting the DB AUDIT CLEAN BATCH SIZE to a bit higher values (around 2 to 3 lakh) will increase the performance. Keeping a low value will increase the time taken for purge and a very high value will led to higher undo generation.SQL> SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE'; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- -------------------- DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL SQL> SQL> SQL> begin dbms_audit_mgmt.set_audit_trail_property ( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std, audit_trail_property => dbms_audit_mgmt.db_delete_batch_size, audit_trail_property_value => 300000); end; / 2 3 4 5 6 7 PL/SQL procedure successfully completed. SQL> SELECT * FROM dba_audit_mgmt_config_params where PARAMETER_NAME='DB AUDIT CLEAN BATCH SIZE'; PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL ------------------------------ -------------------- -------------------- DB AUDIT CLEAN BATCH SIZE 300000 STANDARD AUDIT TRAIL DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL