Saturday, December 2, 2017

Oracle 12c : How To Purge The UNIFIED AUDIT TRAIL




Oracle 12c : How To Purge The UNIFIED AUDIT TRAIL 





APPLIES TO:

Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Information in this document applies to any platform.

GOAL

 How to purge the  UNIFIED_AUDIT_TRAIL table ?

SOLUTION


The unified audit trail can be purged using the DBMS_AUDIT_MGMT package: 

qlplus / as sysdba
SQL>  select  count(*) from unified_audit_trail;

  COUNT(*)
----------
      2619

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

PL/SQL procedure successfully completed.

SQL>  select  count(*) from unified_audit_trail;

  COUNT(*)
----------
         1


You can also use the last_arch_timestamp, if you don't want to purge all the audit record and kept the most recent record:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,last_archive_time => TO_TIMESTAMP('10-SEP-0714:10:10.0','DD-MON-RRHH24:MI:SS.FF'));
END;
/


BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
   use_last_arch_timestamp  =>  TRUE);
END;
/
Since Unified Auditing caches the audit trail in memory to implement a 'lazy write' feature that helps performance, some of the records eligible for deletion may still linger in the cache, to also first flush this cache add a call to: DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL; before the call to
dbms_audit_mgmt.clean_audit_trail, this will cause more consistent / expected results.
BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/
enable the Pure Unified Auditing

In order to enable to Pure Unified Auditing you will need to perform the following steps:
1.     Shutdown your Oracle Databases and listeners that are associated to the Oracle Home
2.     Relink the Oracle executable to support the Unified Auditing
3.     Start your Oracle instances and listener
In order to relink the Oracle executable you will need to execute the "make" command from the $ORACLE_HOME/rdbms/lib directory:

make -f ins_rdbms.mk uniaud_on ioracle

Now, after the relink has been completed, let's query V$OPTION again to verify that the Unified Auditing feature is enabled:
SQL> select value
     from V$OPTION
     where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
TRUE

References