Purge the Audit records with truncate or DBMS_AUDIT_MGMT package

Purge the Audit records with truncate or DBMS_AUDIT_MGMT package

Check AUDIT is enabled or disabled

Show parameter audit_trail

NAME          TYPE    VALUE
------------- ------- --------
audit_trail   string  DB

Check total no of rows in Audit table

select count(*) TOTAL from sys.aud$;

Check the size of AUD$ table

select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 "MB Size" from dba_segments where segment_name='AUD$';

 
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME MB Size
----- ------------ ------------ --------------- -------
SYS   AUD$         TABLE        SYSTEM          550

Purge the AUDIT records Manually

--For complete purging the AUDIT table.
TRUNCATE table sys.AUD$;

--Delete all data from AUD$ except keeping last 30 days
--disable the aud$ logging otherwise it generate lot of archive logs
alter table AUD$ nologging;
delete from aud$ where TIMESTAMP# <= sysdate-30;

Purge the Audit records with Package DBMS_AUDIT_MGMT

1. Check the configuration

COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SELECT * FROM dba_audit_mgmt_config_params;

 
PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE            TS_AUDIT             STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               UNIFIED AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL

2. Clear the Audit Record initially

--- Clean initially
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP( audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, default_cleanup_interval => 12);
end;
/
Note: If you haven’t moved the AUD$ table out of SYSTEM tablespace, then it the below script will move the AUD$ to SYSAUX tablespace by default.

--Check first step is completed YES
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/

--Set the last archive timestamp to remove the audit records lower than value of it. i keep 30 days data for aud$ table.
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-30);
END;
/

--Verify
SELECT * FROM dba_audit_mgmt_last_arch_ts;

For complete empty the AUD$ table from DBMS package

BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/

For Enable or Disable the Audit at Database Level

--For Enable
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
--For Disable
ALTER SYSTEM SET audit_trail=NONE SCOPE=SPFILE;

Scheduled the Job of Audit purging in DBMS SCHEDULER JOBS

-- Create the scheduler job which purge the AUD$ table daily
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JOB_PURGE_AUDIT_RECORDS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-30); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Update last_archive_timestamp');
END;
/

-- Select the scheduler job
select LAST_START_DATE,LAST_RUN_DURATION,NEXT_RUN_DATE from dba_scheduler_jobs where job_name= ’JOB_PURGE_AUDIT_RECORDS’;

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.