SQL & PL/SQL

Hi,

I am trying to setup the DBMS_AUDIT_MGMT utility to periodically purge data older than a year in sys.aud$. I think I'm doing something wrong because even after I purge the data, I see rows that are older than a year.

Oracle 12.2

Here is what I am doing:

1) Make sure cleanup is initialized.

2) Change the last archive timestamp to be sysdate - 35.

3) Run clean audit trail.

4) check the min ntimestamp# in sys.aud$. It is about 3.5 years old. I was hoping it'll be sysdate - 365.

SQL> @setup_audit_purge.sql
SQL>
SQL> SET LINESIZE 1000
SQL>
SQL> SET PAGESIZE 100
SQL>
SQL> SET WRAP OFF
SQL>
SQL> --database version
SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
PL/SQL Release 12.2.0.1.0 - Production
CORE    12.2.0.1.0      Production
TNS for Linux: Version 12.2.0.1.0 - Production
NLSRTL Version 12.2.0.1.0 - Production
SQL>
SQL> -- check the initialization status
SQL>
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2    IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
  3      DBMS_OUTPUT.put_line('YES');
  4    ELSE
  5      DBMS_OUTPUT.put_line('NO');
  6    END IF;
  7  END;
  8  /
YES
PL/SQL procedure successfully completed.
SQL>
SQL> --set the archive timestamp to be sysdate - 365
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_aud_std, TRUNC(SYSTIMESTAMP)-365);
  3    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_fga_std, TRUNC(SYSTIMESTAMP)-365);
  4    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_os, TRUNC(SYSTIMESTAMP)-365);
  5    DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_xml, TRUNC(SYSTIMESTAMP)-365);
  6    -- 12cR1 Onwards
  7    --DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.audit_trail_unified, TRUNC(SYSTIMESTAMP)-365);
  8  END;
  9  /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --check the last archive timestamp. Anything older than this date should be purged.
SQL> SELECT audit_trail, last_archive_ts FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL          LAST_ARCHIVE_TS
-------------------- ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL 12-SEP-18 12.00.00.000000 AM +00:00
FGA AUDIT TRAIL      12-SEP-18 12.00.00.000000 AM +00:00
OS AUDIT TRAIL       12-SEP-18 12.00.00.000000 AM -04:00
XML AUDIT TRAIL      12-SEP-18 12.00.00.000000 AM -04:00
SQL>
SQL>
SQL> --manually run the purge job
SQL>
SQL> BEGIN
  2    DBMS_AUDIT_MGMT.clean_audit_trail(
  3     audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  4     use_last_arch_timestamp => TRUE);
  5  END;
  6  /
PL/SQL procedure successfully completed.
SQL>
SQL> --check the timestamp of my oldest record in sys.aud$
SQL>
SQL> select min(ntimestamp#) from sys.aud$;
MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
24-MAR-16 05.24.12.329344 PM