Oracle Database Discussions

Hi everybody,

I'm trying to understand cleanup of Audit in an Oracle Database. Actually I've a 19c PDB which is auditing some privileges and all changes on tables of a specific schema. My policies look like this:

The privileges policy

create audit policy dba_actions_policy
privileges CREATE ANALYTIC VIEW, CREATE ASSEMBLY, CREATE ATTRIBUTE DIMENSION, CREATE CLUSTER, CREATE CREDENTIAL, CREATE CUBE, CREATE CUBE BUILD PROCESS, CREATE CUBE DIMENSION, 
CREATE DATABASE LINK, CREATE DIMENSION, CREATE EVALUATION CONTEXT, CREATE EXTERNAL JOB, CREATE HIERARCHY, CREATE INDEXTYPE, CREATE JOB, CREATE LIBRARY, CREATE LOCKDOWN PROFILE, 
CREATE MATERIALIZED VIEW, CREATE MEASURE FOLDER, CREATE MINING MODEL, CREATE OPERATOR, CREATE PLUGGABLE DATABASE, CREATE PROCEDURE, CREATE PROFILE, CREATE PUBLIC DATABASE LINK, 
CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE ROLLBACK SEGMENT, CREATE RULE, CREATE RULE SET, CREATE SEQUENCE, CREATE SESSION, CREATE SQL TRANSLATION PROFILE, CREATE SYNONYM, CREATE TABLE, 
CREATE TRIGGER, CREATE TYPE, CREATE USER, CREATE VIEW;

and my “object” policy created a bit more dynamically

create audit policy changes_on_hk actions insert, update, delete on hk.AEV_ANALYSEN_ALERT;

audit policy changes_on_hk;

DECLARE
  l_sql_stmt varchar2(1000);
BEGIN
  FOR t IN (SELECT owner, table_name FROM all_tables WHERE upper(owner) = 'HK')
  LOOP
    l_sql_stmt := 'alter audit policy changes_on_hk add actions insert, update, delete on ' || t.owner || '.' || t.table_name;
    EXECUTE IMMEDIATE l_sql_stmt;
  END LOOP;
END;
/

Now customer says we have to delete all audit records older than 90 days so I've designed these jobs to set the last archive timestamp each day to sysdate-90 and after run a cleanup.

-- first init
BEGIN
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_ALL,
default_cleanup_interval => 24);
END;
/


-- set last archive timestamp each day by job
BEGIN
dbms_scheduler.create_job (
job_name => 'Audit_Archive_Timestamp_TRAIL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
dbms_audit_mgmt.set_last_archive_timestamp(
AUDIT_TRAIL_TYPE => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => sysdate-90);
END;',
start_date => sysdate,
repeat_interval=>'FREQ=DAILY;BYHOUR=20',
enabled => TRUE,
comments => 'Create Trail Audit Archive timestamp');
END;
/

BEGIN
dbms_scheduler.create_job (
job_name => 'Audit_Archive_Timestamp_UNIFIED',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
dbms_audit_mgmt.set_last_archive_timestamp(
AUDIT_TRAIL_TYPE => dbms_audit_mgmt.AUDIT_TRAIL_UNIFIED,
LAST_ARCHIVE_TIME => sysdate-90);
END;',
start_date => sysdate,
repeat_interval=>'FREQ=DAILY;BYHOUR=21',
enabled => TRUE,
comments => 'Create Unified Audit Archive timestamp');
END;
/


-- purge jobs
BEGIN
dbms_audit_mgmt.create_purge_job(
AUDIT_TRAIL_TYPE => dbms_audit_mgmt.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Audit_Purge_Job_ALL',
USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/

BEGIN
dbms_audit_mgmt.create_purge_job(
AUDIT_TRAIL_TYPE => dbms_audit_mgmt.audit_trail_unified,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Audit_Purge_Job_UNI_AUD',
USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/


-- set time for purge jobs
BEGIN
dbms_scheduler.set_attribute (
name => 'SYS.Audit_Archive_Timestamp_TRAIL',
attribute => 'start_date',
value => TO_DATE('2024/03/11 20:00:00', 'YYYY/MM/DD HH24:MI:SS'));
end;
/

BEGIN
dbms_scheduler.set_attribute (
name => 'SYS.AUDIT_ARCHIVE_TIMESTAMP_UNIFIED',
attribute => 'start_date',
value => TO_DATE('2024/03/11 21:00:00', 'YYYY/MM/DD HH24:MI:SS'));
end;
/

BEGIN
dbms_scheduler.set_attribute (
name => 'AUDSYS.Audit_Purge_Job_ALL',
attribute => 'start_date',
value => TO_DATE('2024/03/11 22:00:00', 'YYYY/MM/DD HH24:MI:SS'));
end;
/

BEGIN
dbms_scheduler.set_attribute (
name => 'AUDSYS.Audit_Purge_Job_UNI_AUD',
attribute => 'start_date',
value => TO_DATE('2024/03/11 23:00:00', 'YYYY/MM/DD HH24:MI:SS'));
end;
/

These jobs now running since some month without any problems. I took this from dba_scheduler_job_run_details where I can't find any job with job_name like %AUDIT% with STATUS ≠ SUCCESSFUL.

Now I want to look into unified_audit_trail what is the oldest data set in there:

SELECT MIN(EVENT_TIMESTAMP) FROM UNIFIED_AUDIT_TRAIL;

MIN(EVENT_TIMESTAMP)       
---------------------------
17.01.24 03:34:02,710000000

And now I'm wondering because this is older than 90 days. So did I something wrong in this case?

Thanks a lot and regards,
David