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,540Sweet. 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,802413So 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.2014In 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,247344So 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 -------------------- 7481Now 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.sqlResult:
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.
