Oracle Technical Track

Oracle Database: script to purge aud$ table using dbms_audit_mgmt package

2 min read

Sep 2, 2014 12:00:00 AM

With an increase in requirements on database security, database auditing is part of most production databases nowadays. The growth on Oracle database audit table"'aud$" always add spice on a DBA's life. Since this table growth directly impacts the database performance, this table got special place on every DBA's heart. Traditionally we follow many methods to purge the old data from this table, all these methods require application downtime for most of the time. Oracle introduced a new in-house package named "DBMS_AUDIT_MGMT", which gives more control for a DBA over management of auditing records. I tried to test this package on my 11gR2 test database. Although this can be done from oracle rdbms version 10.2.0.3.0. But we need to apply the required patches on 10.2.0.3 (Patch 6989148) and 10.2.0.4 (Patch 6996030) versions. This package is installed by default on versions 10.2.0.5 and 11.1.0.7. I configured audit_trail parameter value to db_extended and enabled database auditing. Later I moved this db audit table and associated LOB segments to dedicated tablespace named "AUDTBS". I confirmed the audit functionality is running fine after the tablespace change. Thanks to MOS notes 1362997.1 and 1508787.1, I successfully tested this purge procedure. I configured the database scheduler jobs in such a way that they should run once per 12 hours, purges data from aud$ table, which are older than 7 days. Here is the script(purge_job.sql) used myself to configure the required jobs. Script: purge_job.sql ==> Run this script as SYS database user account. prompt start of the script 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; / prompt End of the script To verify the purge status and configured jobs status execute the following queries. SQL> select min(NTIMESTAMP#) from aud$; SQL> select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name='ADVANCE_ARCHIVE_TIMESTAMP'; SQL> select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name='STANDARD_AUDIT_TRAIL_PJ'; We can definitely customize this script based on requirement, which is different for each database. But testing is required on the cloned database before configuring these purge jobs on the production database.

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Oracle Database: script to purge aud$ table using dbms_audit_mgmt package

Share this

Share this

More resources

Expand your Oracle Tuning Tools with dbms_utility.expand_sql_text

Options for Tracing Oracle dbms_stats

Oracle Cloud Infrastructure: Schemas in Oracle Autonomous Database

Ready to unlock value from your data?