Purging Audit Data Oracle 12c/18c

Notes on auditing and settings.

 

let’s examine how to purge audit trails. The audit management package includes a procedure that automatically performs the purge for you. But before you can actually use it, you must call a one-time initialization procedure—INIT_CLEANUP—to set up the audit management infrastructure. Listing 2 shows how to perform the initialization.
Code Listing 2: Initializing cleanup of audit entries

begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.audit_trail_files,
default_cleanup_interval => 24 );
end;
The INIT_CLEANUP procedure takes two parameters, neither of which takes a default value:

audit_trail_type—designates the type of audit trail being initialized. For instance, audit_trail_aud_std indicates the standard database audit trail (the AUD$ table).

Table 1 lists the possible values for this parameter and the audit trail types they represent.

default_cleanup_interval—designates the default interval in hours between executions of automatic purge jobs (to be discussed later in this article).
Parameter Description
audit_trail_aud_std The standard AUD$ audit trail in the database
audit_trail_fga_std The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std Both standard and FGA audit trails
audit_trail_os The OS audit trail
audit_trail_xml The XML audit trail
audit_trail_files Both OS and XML audit trails
audit_trail_all All of the above

SQL> show parameter audit;

NAME TYPE VALUE
———————————— ———– ——————————
audit_file_dest string /u01/app/oracle/admin/azspdfn1
/adump
audit_sys_operations boolean TRUE
audit_syslog_level string LOCAL1.WARNING
audit_trail string OS
unified_audit_sga_queue_size integer 1048576

1st:

You can also set the Last Archive TS if it is not set
(in below it will set OS_AUDIT TS to sydate-45) :

SQL>

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-30);
END;
/

Lets see if the Last Archive TS is set for OS Audit files.
SQL> set pagesize 150
set linesize 150
col last_archive_ts format a40
select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;
AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS
——————– ———— —————————————-
STANDARD AUDIT TRAIL 0 23-DEC-14 03.34.45.000000 PM +00:00

If the audit files are in the OS. They can be cleaned up using:
SQL> BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);
END;
/

begin
dbms_audit_mgmt.create_purge_job (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_purge_interval => 24,
audit_trail_purge_name => ‘std_audit_trail_purge_job’,
use_last_arch_timestamp => TRUE
);
end;
/
Now Lets confirm the last Archive Time stamp in DB.

SQL> COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40

SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS
——————– ———— —————————————-
STANDARD AUDIT TRAIL 0 23-DEC-14 03.34.45.000000 PM +00:00
OS AUDIT TRAIL 1 26-DEC-14 10.43.05.000000 AM -06:00
————————————————————————-
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-30);
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.audit_trail_aud_std,
last_archive_time => SYSTIMESTAMP-30);
END;
/

begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.audit_trail_all,
default_cleanup_interval => 24 );
end;
/

begin
dbms_audit_mgmt.create_purge_job (
audit_trail_type => dbms_audit_mgmt.audit_trail_all,
audit_trail_purge_interval => 24,
audit_trail_purge_name => ‘std_audit_trail_purge_job’,
use_last_arch_timestamp => TRUE
);
end;
/

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

————————————————————————
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,TO_TIMESTAMP(’31-aug-2018 10:00:00′,’dd-mon-yyyy hh24:mi:ss’));

EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,TRUE);

EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,audit_trail_purge_interval => 1,audit_trail_purge_name => ‘AUDIT_CLEANUP’,use_last_arch_timestamp => TRUE);
EXEC DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,TO_TIMESTAMP(’31-aug-2018 10:00:00′,’dd-mon-yyyy hh24:mi:ss’));

EXEC DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,TRUE);

EXEC DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,audit_trail_purge_interval => 1,audit_trail_purge_name => ‘AUDIT_CLEANUP’,use_last_arch_timestamp => TRUE);
SELECT owner,job_name,job_action,repeat_interval FROM dba_scheduler_jobs WHERE job_name=’AUDIT’;

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