Introduction:-
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 purging 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 newpuring 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:
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
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 28-MAY-16 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
