SYSAUX tablespace full with AUDSYS objects in Oracle database

SYSAUX tablespace full with AUDSYS objects in Oracle database

SYSAUX tablespace in Oracle database is going to full, on checking we found that AUDSYS schema is consuming lot of space in it.
On checking we found unified auditing is used the AUDSYS schema. So, we tried to fixed it by disable the default audit policy. Default unified audit policy make data despite the value of audit_trail parameter.

Note: We are thinking that AWR or stats are consuming more space on SYSAUX tablespace. So we can check it with AWRINFO.sql script
You can check it with awrinfo.sql utility present in $ORACLE_HOME\rdbms\admin\awrinfo.sql.
Please find the example report:AWRINFO.DOC

Check the sysaux tablespace top most consuming table

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 < 6;

OWNER  SEGMENT_NAME                       SIZE_M                                                                                                                            
------ ---------------------------------- ----------                                                                                                                            
AUDSYS SYS_LOB0000091751C00014$$~         17808.125                                                                                                                            
AUDSYS CLI_SWP$8e0bfd86$1$1~              14296                                                                                                                            
AUDSYS CLI_TIME$8e0bfd86$1$1~               232                                                                                                                            
AUDSYS CLI_SCN$8e0bfd86$1$1~                224                                                                                                                            
AUDSYS CLI_LOB$8e0bfd86$1$1~                209                                                                                                                            
5 rows selected.     

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. Complete clean the unified audit trail

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

--Example 1. You can specify the data keep of last 15 days data as follows:
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;
/

--Check 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

--Example 2. You can specify the data 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;
/

-- Start the clean job by using last time stamp defined as TRUE value in upper both example.
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/

Flush from memory

exec DBMS_AUDIT_MGMT.FLUSH_UNIFIED_AUDIT_TRAIL;

Disable the Unified Audit policies that are enabled by default

NOAUDIT POLICY ORA_SECURECONFIG;
noaudit policy ORA_LOGON_FAILURES;

Re-enable these audit policies if needed

AUDIT POLICY ORA_SECURECONFIG;
audit policy ORA_LOGON_FAILURES;

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.