Oracle audit table AUD$ migrate tablespace and create cleanup job
Versions of Oracle above 10.2.0.5 can use DBMS_AUDIT_MGMT to clean up audit
1. Check whether audit has been opened.
SQL > show parameter audit_trail
NAME TYPE VALUE
Audit_trail string DB, EXTENDED
2. Create audit tablespace
Create tablespace ts_audit datafile'/ oradata/ora10g/audit01.dbf' size 4000m autoextend off
3. Set the cleaning frequency
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP (
Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
Default_cleanup_interval = > 12 / hours /)
END
/
4. Migrate AUD$ from SYSTEM tablespace to new tablespace
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
/
5. Set last archive timestamp and keep it for 30 days.
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
Last_archive_time = > SYSDATE-30)
END
/
6. Set last archive timestamp job and keep it for 30 days
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
Job_name = > SET_AUDIT_ARCHIVE_TIMESTAMP'
Job_type = > 'PLSQL_BLOCK'
Job_action = > 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (AUDIT_TRAIL_TYPE = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME = > SYSDATE-30); END;'
Start_date = > sysdate
Repeat_interval = > 'FREQ=HOURLY;INTERVAL=24'
Enabled = > TRUE
Comments = > 'set last archive timestamp'
);
END
/
7. Define automatic cleaning job
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
Audit_trail_type = > DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
Audit_trail_purge_interval = > 24 / hours /
Audit_trail_purge_name = > 'AUDIT_CLEANUP_JOB'
Use_last_arch_timestamp = > TRUE)
END
/
8. Check the execution of cleaning job. Delete_count should not be 0.
SQL > select * from dba_audit_mgmt_clean_events
AUDIT_TRAIL RAC_INSTANCE CLEANUP_TIME DELETE_COUNT WAS
STANDARD AUDIT TRAIL 0 16-MAY-19 07.18.59.937786 AM + 00:00 460 NO
