Scenario 6 – Performing Audit Data Cleanup

In this section, we’ll guide you through the process of cleaning up audited data from AUDSYS tables stored in the SYSAUX tablespace.

  1. Manual Cleanup

You can perform the cleanup manually using the following steps:

  • – First, check the count of audited records:

SQL> SELECT COUNT(*) FROM unified_audit_trail;

 

– Use the following commands to cleanup audit data:

SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE);

SQL> EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE);

  1. Scheduled Cleanup

You can also schedule the cleanup for regular execution:

  • Set the last archive timestamp:

SQL> EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, LAST_ARCHIVE_TIME => SYSDATE);

– Create a purge job:

SQL> EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_PURGE_INTERVAL => 1, AUDIT_TRAIL_PURGE_NAME => ‘Audit_Trail_PJ’, USE_LAST_ARCH_TIMESTAMP => TRUE);

  1. Viewing Cleanup Job Executions

You can view the executions of the cleanup job:

SQL> COL JOB_NAME FORMAT A14
SQL> COL STATUS FORMAT A12
SQL> COL ACTUAL_START_DATE FORMAT A40

SQL> SELECT JOB_NAME, STATUS, ACTUAL_START_DATE FROM dba_scheduler_job_run_details WHERE JOB_NAME = ‘AUDIT_TRAIL_PJ’ ORDER BY ACTUAL_START_DATE;

  1. Verifying Cleanup

Finally, verify if the audit data has been purged:

SQL> SELECT COUNT(*) FROM unified_audit_trail;

By following these steps, you can effectively perform audit data cleanup in your Oracle database environment.