Oracle: Setup Audit Trail Regular Purging

gistfile1.txt

-- 1. INITIALIZE AUDIT PRUNING ON ALL AUDIT TRAILS
-- to be safe, DEINIT the purge jobs:
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_all );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_os );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_xml );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_aud_std );
exec dbms_audit_mgmt.deinit_cleanup( dbms_audit_mgmt.audit_trail_fga_std );
-- now with a clean slate, INIT cleanup capability on ALL audit trails every 24h
exec DBMS_AUDIT_MGMT.INIT_CLEANUP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_all, 24 );

-- 2. SET THE RETENTION (LAST_ARCHIVE_TIMESTAMP)
-- CURRENT PROD STANDARD = 60 DAYS
-- CURRENT NONPROD STANDARD = 10 DAYS
-- applicable to BOTH nodes:
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, to_timestamp(sysdate-10));
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, to_timestamp(sysdate-10));
-- 12c only
exec DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( dbms_audit_mgmt.audit_trail_unified, sysdate-10);

-- single instance OR 1st RAC Node
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_OS, to_timestamp(sysdate-10), 1);
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_XML, to_timestamp(sysdate-10), 1);
-- 2nd RAC node
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_OS, to_timestamp(sysdate-10), 2);
exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_XML, to_timestamp(sysdate-10), 2);


-- get various params
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000  pages 999
SELECT * FROM dba_audit_mgmt_config_params;

-- VIEW the current LAST_ARCHIVE_TIMESTAMP for each type of
-- AUDIT_TRAIL, if configured. If empty, then we need to SET IT.
col audit_trail format a20
col last_archive_ts format a35
col rac_instance format 99999 heading "RAC|Inst"
col days_back  for 9999  head "days|back"
select audit_trail, rac_instance, last_archive_ts
	, extract( day from (systimestamp-last_archive_ts))  days_back
from DBA_AUDIT_MGMT_LAST_ARCH_TS;

	-- 3. SETUP AUDIT PRUNE JOB

-- create a purge job for ALL audit trails (os,xml,std_aud,fga_aud) that deletes every 24 hours
-- SAME:
exec  dbms_audit_mgmt.create_purge_job( -
	audit_trail_type => dbms_audit_mgmt.audit_trail_all, -
	audit_trail_purge_interval => 24  /* hours */, -
	audit_trail_purge_name => 'DAILY_AUDIT_PURGE', -
	use_last_arch_timestamp => TRUE);


-- Update Audit Trail Retention to X number of days in the past, because
-- the DBMS_AUDIT_MGMT.LAST_ARCHIVE_TIMESTAMP has to be manually updated daily.
-- Parameter:   m_purge_retention   - days to keep. Example = 10 days
create or replace procedure AUDIT_UPDATE_RETENTION(
  m_purge_retention  IN  number  DEFAULT 10
) AS
BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TO_TIMESTAMP(SYSDATE-m_purge_retention));
  --DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, SYSDATE-m_purge_retention);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TO_TIMESTAMP(SYSDATE-m_purge_retention), 1);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TO_TIMESTAMP(SYSDATE-m_purge_retention), 1);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TO_TIMESTAMP(SYSDATE-m_purge_retention), 2);
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TO_TIMESTAMP(SYSDATE-m_purge_retention), 2);
END;
/
-- try running it as a test
exec audit_update_retention(10);

-- drop old DAILY_AUDIT_UPDATE_RETENTION job, if it exists.
set serveroutput on
declare
  ex_job_doesnt_exist EXCEPTION;
  PRAGMA EXCEPTION_INIT( ex_job_doesnt_exist, -27475 );
begin
  dbms_scheduler.drop_job(job_name => 'DAILY_AUDIT_UPDATE_RETENTION');
	dbms_output.put_line(chr(13) ||
		'pre-deleted old DAILY_AUDIT_UPDATE_RETENTION job definition..'||chr(13));
exception when ex_job_doesnt_exist then
   dbms_output.put_line(chr(13)||'DAILY_AUDIT_UPDATE_RETENTION doesn''t exist, no need to pre-delete.'||chr(13));
end;
/
-- Now that Job is confirmed deleted, re-create:
declare
  ex_must_be_declared EXCEPTION;
  PRAGMA EXCEPTION_INIT( ex_must_be_declared, -6550 );
  m_purge_retention     number(10);
BEGIN
  -- set number of days to Retain HERE
  -- Prod = 60 days, Non-Prod = 10 days
  m_purge_retention := 60;

	DBMS_SCHEDULER.create_job (
		job_name => 'DAILY_AUDIT_UPDATE_RETENTION',
		job_type => 'PLSQL_BLOCK',
		job_action => 'BEGIN
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'));
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'));
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'));
			--DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, SYSDATE-'||m_purge_retention||');
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'), 1);
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'), 1);
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'), 2);
			DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML, TO_TIMESTAMP(SYSDATE-'||m_purge_retention||'), 2);
		END;',
		start_date => SYSTIMESTAMP,
		repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
		end_date => NULL,
		enabled => TRUE,
    auto_drop => FALSE,
		comments => 'every day, update last_archive_timestamp (which DAILY_AUDIT_PURGE uses) to '||m_purge_retention||' days back.'
	);
exception
	when ex_must_be_declared then
  	dbms_output.put_line('DAILY_AUDIT_UPDATE_RETENTION: component missing?'||chr(13));
END;
/

-- manually RUN the update retention + purge jobs:
exec dbms_scheduler.run_job('DAILY_AUDIT_UPDATE_RETENTION',use_current_session=>false);
exec dbms_scheduler.run_job('DAILY_AUDIT_PURGE',use_current_session=>false);