1.
Create
new tablespace to hold the audit trail.
SQL>create tablespace audit_data datafile 'E:\HISPL\oradata\HISPL\DATAFILE\audit_date.DBF' size 200M;
2.
Truncate
sys.aud$ to accelerate movement to the new tablespace (Optional).
Ensure that the size of aud$ can fit
the created tablespace otherwise try to delete some of it or just truncate it
and that would accelerate the movement.
SQL> truncate table sys.aud$;
3. Initializes
the audit management infrastructure for all audit trails with one week.
SQL> 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 =>
24*7);
else
dbms_output.put_line('Cleanup for
STD was already initialized');
end if;
end;
/
|
4. Move the standard audit to the new tablespace.
Begin
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
audit_trail_type =>
dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value =>
'AUDIT_DATA') ;
end;
/
|
5. Specify a timestamp of 7 days ago for the standard audit trail.
Begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => sysdate -
7);
end;
/
|
6. Purge all records older than 7 days.
BEGIN
DBMS_AUDIT_MGMT.DROP_PURGE_JOB
(AUDIT_TRAIL_PURGE_NAME =>
'Standard_Audit_Trail_PJ');
-- exception
-- when others then
-- null;
end;
/
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL =>
24,
AUDIT_TRAIL_PURGE_NAME =>
'Standard_Audit_Trail_PJ',
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
|
7. Call DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP regularly to
advance prompt the last archive TS, Audit Vault will do this for you
automatically.
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;
/
|
8. Schedule automatic advancement of the archive timestamp
(Optionally).
Begin
DBMS_SCHEDULER.disable('advance_archive_timestamp');
DBMS_SCHEDULER.drop_job('advance_archive_timestamp');
-- exception
-- when others then
-- null;
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=daily' ,
enabled => false,
auto_drop =>
FALSE);
dbms_scheduler.set_job_argument_value
(job_name =>'advance_archive_timestamp',
argument_position =>1,
-- one
week, you can customize this line:
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;
/
|
