Unified Audit Data Cleanup in Oracle 12c

Unified Audit Data Cleanup in Oracle 12c

We have two option to clean the unified audit trail
1. Complete empty the unified audit trail
2. Partitial delete data upto specified data

Following are the example of both methods:

1. Package used to complete clean the unified audit trail data
In this you only make parameter USE_LAST_ARCH_TIMESTAMP to false value.

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => FALSE,
CONTAINER => dbms_audit_mgmt.container_current);
END;
/

2. Partial Clean record up-to specific time as described in two example
In this case you have first to define the timestamp upto which you want to delete data from unified audit trail table. First procedure (set_last_archive_timestamp) is used to define the date upto which data need to be deleted by another procedure as show in example.
Step 1: Define the timestamp date with first Procedure:

--Example 1. You want to delete data of last 15 days in unified audit trail:
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_unified,
last_archive_time => SYSTIMESTAMP-15,
--rac_instance_number => 1,
container => DBMS_AUDIT_MGMT.container_current
);
END;
/

--Example 2. You specify the date and time upto which you keep data as follows:
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;
/

Note: Above both examples used the same procedure for defining the date with parameter last_archive_time given in different ways.

Step 2: Verify the date upto which it clean the data with following view:

COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40
SELECT audit_trail, last_archive_ts FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL LAST_ARCHIVE_TS
------------------- -----------------------------------
UNIFIED AUDIT TRAIL 18-JUL-18 02.26.17.000000 AM +00:00

Step 3: Start the clean job by using use_last_arch_timestamp defined as TRUE

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

Note: It will delete the data up-to specified time defined in step 1 procedure.

Flush from memory

exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

Example of cleaning unified audit data

sqlplus / as sysdba

SQL> select count(*) from unified_audit_trail;
COUNT(*)
----------
454543252

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.