Multiple DBIDs in AUD$

I wanted to test the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL package, with use_last_arch_timestamp to TRUE, to only purge records one month older than the minimum value found.

I found this value by using the function ADD_MONTHS to add 1 month on top of the minimum value found in AUD$

SET SERVEROUTPUT ON
 DECLARE
 currdate DATE;
 last_archtime DATE;

 BEGIN

 currdate := SYSTIMESTAMP;

 ---------------------------------------------------------
 -- Get the oldest timestamp from AUD$, then add one month.
 -- Use this timestamp as the last archive timestamp in
 -- procedure SET_LAST_ARCHIVE_TIMESTAMP
 ---------------------------------------------------------
 SELECT ADD_MONTHS(
   (
    SELECT MIN(TIMESTAMP) 
    FROM DBA_AUDIT_TRAIL
    ), 1)
 INTO last_archtime
 FROM DUAL;
 DBMS_OUTPUT.PUT_LINE('last_archtime: ' || last_archtime);

  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    LAST_ARCHIVE_TIME => last_archtime);

 END;
 /

Put all that in a file called set_last_timestamp_std.sql.
First, check the DBA_AUDIT_MGMT_LAST_ARCH_TS for the last archive timestamp:

SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;

No rows selected.

Execute the script created above:

sqlplus / as sysdba @set_last_timestamp_std.sql

 last_archtime: 07.02.2009

 PL/SQL-procedure executed.

Check the DBA_AUDIT_MGMT_LAST_ARCH_TS again:

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
 -------------------- ------------ ----------------------------------------
 STANDARD AUDIT TRAIL            0 07.02.2009 15.01.50,000000 +00:00

I was now ready to execute the manual cleanup. Before I did so, I wanted to get an idea about how many rows that should be purged:

SELECT COUNT(*)
FROM DBA_AUDIT_TRAIL
WHERE TIMESTAMP < (
SELECT ADD_MONTHS(
   (SELECT TIMESTAMP
    FROM (SELECT TIMESTAMP FROM DBA_AUDIT_TRAIL ORDER BY TIMESTAMP ASC)
    WHERE ROWNUM <=1), 1)
 FROM DUAL)
;

  COUNT(*)
----------
   126405

Compare with the total number of rows:

SQL> SELECT COUNT(*)FROM DBA_AUDIT_TRAIL;

  COUNT(*)
----------
  33 664,540

Sweet. 126405 records would be purged. I then executed:

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/

The purge succeeded. But when I checked the number of rows again, it still returned 126405 rows. What I found was that what Oracle was executing the following statement internally when using the DBMS_AUDIT_MGMT package:

DELETE FROM SYS.AUD$ WHERE DBID = 2367413790 AND NTIMESTAMP# < to_timestamp('2009-02-07 15:01:50', 'YYYY-MM-DD HH24:MI:SS.FF') AND ROWNUM <= 10000;

So I tested to select the rows using the same predicate that was used during the purge:

SQL> SELECT COUNT(*) FROM SYS.AUD$ WHERE DBID = 2367413790 AND NTIMESTAMP# < to_timestamp('2009-02-07 15:01:50', 'YYYY-MM-DD HH24:MI:SS.FF');

   COUNT(*)
 ----------
          0

checked again against the dba_audit_trail

SQL> SELECT COUNT(*) FROM DBA_AUDIT_TRAIL WHERE TIMESTAMP  < to_timestamp('2009-02-07 15:01:50', 'YYYY-MM-DD HH24:MI:SS.FF');

   COUNT(*)
 ----------
     126405

So there are indeed records that are older than '2009-02-07 15:01:50'. Why is it not caught when querying the AUD$ table, only the DBA_AUDIT_TRAIL? Of course! The AUD$ table also has a reference to the DBID. And since the database was recently cloned, it has cycled through another incarnation:

SQL> select DBID,MIN(NTIMESTAMP#)
2  FROM AUD$
3  GROUP BY DBID;

DBID MIN(NTIMESTAMP#)
---------- ----------------------------
2367413790 19.05.2014 07.07.13,675010
848951741 07.01.2009 13.01.50,802413

So the fact that minimum timestamp for DBID 2367413790 is 19.05.2014 is correct after all:

SQL> SELECT MIN(TIMESTAMP) FROM DBA_AUDIT_TRAIL WHERE DBID=2367413790;

MIN(TIMEST
----------
19.05.2014

In fact, the majority of the audit trail records are from a previous incarnation:

SQL> select count(*) from aud$ where dbid = 848951741;

COUNT(*)
----------
33 612,411

SQL> select DBID,MAX(NTIMESTAMP#)
2  FROM AUD$
3  GROUP BY DBID;

DBID MAX(NTIMESTAMP#)
---------- --------------------------------
2367413790 05.06.2014 08.42.59,749967
848951741 15.05.2014 21.41.52,247344

So the size of the AUD$ is 7481 MB:

SQL> SELECT SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'AUD$';

SUM(BYTES)/1024/1024
--------------------
7481

Now the question is: since the procedure DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL with the parameter use_last_arch_timestamp set to TRUE only attempts to purge the rows from AUD$ that has the same DBID as the current database incarnation, will a "purge all" directive, indicated by use_last_arch_timestamp set to FALSE be equally selective? Since this is a test system, I tried it out by putting the following statements into a script:

SET LINES 200
SET SERVEROUTPUT ON

SELECT DBID,COUNT(*)
FROM AUD$
GROUP BY DBID;

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);
END;
/

SELECT DBID,COUNT(*)
FROM AUD$
GROUP BY DBID;

Execute it:

sqlplus / as sysdba @clean_all_audit_trail.sql

Result:

DBID   COUNT(*)
---------- ----------
2367413790      52560
848951741   33612411

PL/SQL-procedure executed.

No rows selected.


SQL> SELECT SUM(BYTES)/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'AUD$';

SUM(BYTES)/1024/1024
--------------------
,0625

So a "purge all" directive will certainly wipe out all of your audit trail, regardless of the presence of multiple DBIDs.

Purging "up until a the last archive timestamp" will only select the audit entries for your current database incarnation's DBID.