SYSAUX tablespace growing rapidly in Oracle, 12c Unified Audit Trail AUDSYS

The Unified Auditing in Oracle 12c can Grow the SYSAUX table to a large size. In our case it grew to about 100GB and We had to take action to make sure it did not grow any larger. First we query to identify what is residing inside the SYSAUX tablespace. In our case it was the AUDSYS schema which was taking the most space. All arrows pointed to Unified Auditing’s way

--- Query to Check the SYSAUX Tablespace Occupants --
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2
/
-- Query to Check the SYSAUX Tablespace Largest Segments--
col owner for a6
col segment_name for a50
select * from
(select owner,segment_name||'~'||partition_name segment_name,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSAUX' ORDER BY BLOCKS desc) where rownum < 11;
-- Clean Up the Unified Audit Trail --

1. First set the Date, starting from where you need to have the Unified Auditing Records

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => TO_TIMESTAMP('15-SEP-2016 00:00:10.0','DD-MON-RRRR HH24:MI:SS.FF'),
rac_instance_number => 2);
END;
/

2. 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

BEGIN
DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;
END;
/

3. Execute the Purge Procedure

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

Useful Links:
https://docs.oracle.com/database/121/DBSEG/audit_admin.htm#DBSEG474
How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)

Category: DatabaseExadataLinuxUncategorized

Tags:

Leave a Reply

Article by: Shadab Mohammad