Database Software

Oracle 11.2.0.4 2-node RAC cluster

RHEL 6.8

separate non-shared RAC Binary directories on each node (ie. each node has it's own $ORACLE_BASE / $ORACLE_HOME)

I want to confirm the standard operating procedure to prune audit trails.

main culprits I want to prune (particularly in non-prod environments where I care less):

- OS .aud files

- db audit trail (in general)

..stored to x days back

Am I right in doing this with THREE PHASES?

-- 1. Initialize Audit pruning on all audit trails

-- 2. Set the retention (LAST_ARCHIVE_TIMESTAMP)

-- 3. Setup a daily job to UPDATE LAST_ARCHIVE_TIMESTAMP , and PRUNE AUDIT trail?

Broken down as code, it seems really long winded. see below as a snippet of what i'm trying to do.

(also at https://gist.github.com/michaelsew/697c7214a6184a6243b1b676292b0ee7 )

It "seems" more difficult than it should be.  When I call something like :

exec dbms_audit_mgmt.set_last_archive_timestamp( dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, 10);
exec DBMS_AUDIT_MGMT.INIT_CLEANUP( DBMS_AUDIT_MGMT.AUDIT_TRAIL_all, 24 );

the call sets a purge 'marker' 10 days back, and does the cleanup/purge every 24 hours, but it does NOT UPDATE the purge marker (aka last_archive_timestamp).

I have to continually move the marker 'ahead' every time with a call to dbms_audit_mgmt.set_last_archive_timestamp( ) .. is this what everybody else is doing?

-- 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 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
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) ||
        '
= 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);