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.
2 Comments. Leave new
Hi Suresh,
Great topic and some great points. A few additional items for consideration:
1) Primarily it’s only sys.aud$ that’s of concern but you can manage the FGAC audit trail (sys.fga_log$) in exactly the same way so I recommend following the same procedure for it as well just in case it’s currently in use or will be at some point in the future. To include fga_log$ when you initialize cleanup, instead of using the “DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD” value for the audit_trail_type argument use “DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD”. You can also make the same change to the step where you create the purge job. As well as DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION of course!
2) It’s also a good idea to cleanup external .aud files from the OS using DBMS_AUDIT_MGMT by creating a similar purge job using the audit_trail_type of “DBMS_AUDIT_MGMT.AUDIT_TRAIL_FILES”.
3) I like how you decided to advance the timestamp via an Oracle Scheduler job – I would recommend the same. However to make for less pieces I typically put all of the PLSQL logic to advanced the timestamp right into the Scheduler Job instead of having the job call a PLSQL procedure. And one job to advanced the timestamp for both the internal audit trail tables and the external audit files. That’s maybe just a personal preference but I consider less pieces to be a simpler solution and hence I like to move that PLSQL code right into the Scheduler Job.
4) Don’t forget about RAC compatibility. Sure there’s only one set of audit trail tables (aud$ and fga_log$) but if you also take my suggestion about using the same scheduler job to clean-up the external audit files then you will need to make the job RAC compatible by appending the instance number in the Scheduler Job’s name and by pinning a given job to the specified instance using the Scheduler Job’s class attribute.
5) Finally you might also want to manually purge data from the DBA_AUDIT_MGMT_CLEAN_EVENTS view. You could also add this step to your PLSQL logic whether that’s in a procedure or in the Scheduler job itself.
Overall, this is a good article on a good topic. It’s something every DBA should consider and take action on when deploying a new Oracle database.
Simon.
Thank you. I liked the idea of resetting the last archive timestamp.