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.
