Script to purge aud$ table using dbms_audit_mgmt package

With an increase in requirements on database security, database auditing is part of most production databases nowadays. The growth of the Oracle database audit table "aud$" always adds spice to a DBA's life. Since this table's growth directly impacts database performance, it holds a special place in every DBA's heart.

Traditionally, we follow many methods to purge the old data from this table; however, these methods usually require application downtime. Oracle introduced a new in-house package named DBMS_AUDIT_MGMT, which gives a DBA more control over the management of auditing records.

Leveraging DBMS_AUDIT_MGMT for Management

I tested this package on my 11gR2 test database. Although this can be done starting from Oracle RDBMS version 10.2.0.3.0, you need to apply specific patches for older versions:

• 10.2.0.3: Patch 6989148
• 10.2.0.4: Patch 6996030

The package is installed by default on versions 10.2.0.5 and 11.1.0.7.

Pre-requisites and Initial Setup

Before running the purge, I configured the audit_trail parameter value to db_extended and enabled database auditing. Later, I moved the database audit table and associated LOB segments to a dedicated tablespace named "AUDTBS".

I confirmed the audit functionality was running fine after the tablespace change. Thanks to MOS notes 1362997.1 and 1508787.1, I successfully tested the purge procedure.

The Purge Automation Script

I configured the database scheduler jobs to run once every 12 hours, purging data from the aud$ table that is older than 7 days. Below is the script (purge_job.sql) used to configure these requirements.

Note: Run this script as the SYS database user account.

set serveroutput on prompt Change based on our customization done update dam_config_param$ set string_value='AUDTBS' where audit_trail_type#=1 and param_id=22; commit;  prompt First Step: init cleanup (if not already) BEGIN     IF NOT DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED (DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN         dbms_output.put_line('Calling DBMS_AUDIT_MGMT.INIT_CLEANUP');         DBMS_AUDIT_MGMT.INIT_CLEANUP(             audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,             default_cleanup_interval => 12);     else         dbms_output.put_line('Cleanup for STD was already initialized');     end if; end; /  prompt revert back to default values again update dam_config_param$ set string_value='SYSAUX' where audit_trail_type#=1 and param_id=22; commit;  prompt set last archive timestamp to older than 7 days begin     DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(         audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,         last_archive_time => sysdate - 7); end; /  prompt setup a purge job BEGIN     DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (         AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,         AUDIT_TRAIL_PURGE_INTERVAL => 12,         AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_PJ',         USE_LAST_ARCH_TIMESTAMP => TRUE     ); END; /  prompt call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP regularly to advance the last archive timestamp create or replace procedure set_archive_retention (retention in number default 7) as begin     DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(         audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,         last_archive_time => sysdate - retention); end; /  BEGIN     DBMS_SCHEDULER.create_job (         job_name => 'advance_archive_timestamp',         job_type => 'STORED_PROCEDURE',         job_action => 'SET_ARCHIVE_RETENTION',         number_of_arguments => 1,         start_date => SYSDATE,         repeat_interval => 'freq=hourly;interval=12' ,         enabled => false,         auto_drop => FALSE);      dbms_scheduler.set_job_argument_value (         job_name =>'advance_archive_timestamp',          argument_position =>1,          argument_value => 7);      DBMS_SCHEDULER.ENABLE('advance_archive_timestamp'); End; /  BEGIN     DBMS_SCHEDULER.run_job (         job_name => 'advance_archive_timestamp',          use_current_session => FALSE); END; / 

Verification and Best Practices

To verify the purge status and the status of the configured jobs, execute the following queries:

• Check the oldest audit record: select min(NTIMESTAMP#) from aud$;
• Check the timestamp advancement job: select LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE from dba_scheduler_jobs where job_name='ADVANCE_ARCHIVE_TIMESTAMP';
• Check the standard purge job: select LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE from dba_scheduler_jobs where job_name='STANDARD_AUDIT_TRAIL_PJ';

Final Recommendation

We can definitely customize this script based on requirements, which differ for each database. However, testing is required on a cloned database before configuring these purge jobs on a production system.

Oracle Database Consulting Services

Ready to optimize your Oracle Database for the future?