How to purge audit trail records from audit table sys.aud$ in Oracle database 12c ?

Introduction:

If you are observing fast growth in SYSAUX tablespace and your database version is 12c, then you need to check for the sysaux occupants to find the root cause. In case if you find the top occupant name as AUDIT_TABLES , there could be some purging issue with audit table.

In this blog, will see how to check and purge database trail records from audit table in manual and automate methods.

Description :

Recently, after upgrading oracle database from 11.2.0.4 to 12.1.0.2, we have noticed a significant growth in SYSAUX tablespace utilization. On further troubleshooting , there is a solution to fix the issue. Here are the steps which we have followed.

Step 1 : Find the sysaux tablespace occupants by its usage

SET LINES 400 PAGES 800

COL SCHEMA_NAME FOR A20

COL OCCUPANT_NAME FOR A25

COL OCCUPANT_DESC FOR A55

 SELECT SCHEMA_NAME,OCCUPANT_NAME,OCCUPANT_DESC,

ROUND(SPACE_USAGE_KBYTES/1024) SPACE_USAGE_MB

FROM V$SYSAUX_OCCUPANTS

ORDER BY SPACE_USAGE_MB;

Step 2 : Find sysaux occupants by segment type

SELECT SEGMENT_TYPE,

       ROUND(SUM(BYTES)/1024/1024) MB

FROM

       DBA_SEGMENTS

WHERE

       TABLESPACE_NAME = ‘SYSAUX’

GROUP BY

       SEGMENT_TYPE

ORDER BY 2;

Step 3 : Find the audit table information

COL OWNER FOR A20

COL SEGMENT_NAME FOR A20

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’AUD$’;

Step 4 : Check audit configuration settings

Here the STANDARD AUDIT TRAIL parameter setting represent tablespace, batch size and clean up interval for database audit table.

 COLUMN parameter_name FORMAT A30

COLUMN parameter_value FORMAT A20

COLUMN audit_trail FORMAT A20

SET LINES 2000

SELECT * FROM dba_audit_mgmt_config_params;

Step 5 : Check last archive purge timestamp

If the archive CLEANUP_TIME is not recent then it’s likely to cause problem for the actual purge job.

SQL> SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS WHERE CLEANUP_TIME > SYSDATE -31;

SQL> COLUMN AUDIT_TRAIL FORMAT A20

COLUMN LAST_ARCHIVE_TS FORMAT A40

select AUDIT_TRAIL, RAC_INSTANCE,DATABASE_ID,CONTAINER_GUID, LAST_ARCHIVE_TS from DBA_AUDIT_MGMT_LAST_ARCH_TS;

Here DATABASE_ID = 0 is an invalid entry and it could have generated during the upgrade phase and caused the issue for the audit purge jobs.

Step 6 : Execute below to remove unknown DATABASE_ID records

exec DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,database_id=>0,container_guid=>’00000000000000000000000000000000′);

exec DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,database_id=>0,container_guid=>’00000000000000000000000000000000′);

exec DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,database_id=>0,container_guid=>’00000000000000000000000000000000′);

exec DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,database_id=>0,container_guid=>’00000000000000000000000000000000′);

Step 7 : One-time clean up procedure

Run the one time clean up procedure, which will clean up the records based on the archival timestamp in the purge configuration. Caution: Setting the use_last_arch_timestamp parameter to FALSE will cleanup all the standard audit trail records.

 BEGIN

DBMS_AUDIT_MGMT.clean_audit_trail(

audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,

use_last_arch_timestamp => TRUE);     

END;

/

Step 8 : Check the count/last archived timestamp of audit records on pre and post-clean up to validate.

select min(timestamp) from dba_audit_session;

 select dbid, count(*) from aud$ group by dbid;

Step 9 : Check the DBA_AUDIT_MGMT_CLEAN_EVENTS table to find the recent purged records.

SQL> select * from DBA_AUDIT_MGMT_CLEAN_EVENTS where CLEANUP_TIME > sysdate -31;

Step 10: Check the auto purge audit job details

SQL> SELECT distinct job_name from dba_scheduler_job_run_details where job_name like ‘%PURGE%’;

SQL> set long 999999999

col JOB_ACTION for a100

select job_action from dba_scheduler_jobs where job_name like ‘PURGE_STD_AUDIT_TRAIL’;

Bottom Line:

 There could be various reasons for sysaux tablespace growth, for more information related to  audit purging related issues, please refer Doc ID 2429746.1.