Cleaning AUD$

Notes:
SCRIPT: Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt (Doc ID 1362997.1)
DBMS_AUDIT_MGMT.INIT_CLEANUP Fails With ORA-46267 (Doc ID 1508787.1)

This is a procedure to clean the AUD$ table to avoid locks during 
the truncate process. Even being online process is recommended to be 
executed in a maintenance window. For small environments or non-prod you
can try execute online, without stop the application.

Script: purge_job.sql ==> Run this script as SYS database user account.

=> Please before start take the current size of AUD$ on DBA_SEGMENTS.
select sum(bytes)/(1024*1024) mb 
from dba_segments where segment_name='AUD$';

=> Create a new tablespace based on the value returned on the previous 
select.
create tablespace AUDTBS datafile '/oradata/db/audtbs1.dbf' 
size <VALUE>g autoextend on next 128m maxsize 20g;

=> Take the current number of rows, just to check after <OPTIONAL>
select count(*) from aud$;

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.

select min(NTIMESTAMP#) from aud$;
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE 
from dba_scheduler_jobs where job_name='ADVANCE_ARCHIVE_TIMESTAMP';
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE 
from dba_scheduler_jobs where job_name='STANDARD_AUDIT_TRAIL_PJ';

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s