Auditing housekeeping

 Summary 

If you enable auditing then the auditing information, (stored in table SYS.AUD$) will start growing and because is relies on SYSTEM tablespace you might have performance problems in the future. Auditing housekeeping must be setup.

Here is a practical guide, from a 5TByte E-Business Suite, with 50MB auditing produced every day.

1. Create a SYSTEM.AUD$_BU table stored in a different tablespace(AUDIT_DATA) where you will move all you auditing produced.

CREATE TABLESPACE AUDIT_DATA DATAFILE 
  '/filesystem034/oradata/SID/audit_data_001.dbf' SIZE 10000M AUTOEXTEND OFF
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL;

CREATE TABLE SYSTEM.AUD$_BU TABLESPACE AUDIT_DATA 
AS SELECT * FROM SYS.AUD$ 
WHERE 1=2;

2. Create a procedure(Keep_Size_Aud_Log) that moves the rows from SYS.AUD$ to SYSTEM.AUD$_BU

CREATE OR REPLACE PROCEDURE Keep_Size_Aud_Log
IS
  rowCount NUMBER;
BEGIN
  SELECT COUNT(*) INTO rowCount FROM sys.aud$ ;
  IF rowCount > 0
  THEN
    COMMIT;
    INSERT /*+ APPEND */ INTO SYSTEM.aud$_bu (SELECT * FROM sys.aud$);
 COMMIT;
     EXECUTE IMMEDIATE 'truncate table sys.aud$';
    sys.Dbms_System.ksdwrt (3,'ORA-AUDIT TRAIL: rows moved from SYS.AUD$ to SYSTEM.AUD$_BU');
  END IF;
END Keep_Size_Aud_Log;
/

3. Execute the procedure every day at midnight with a job

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SYS.KEEP_SIZE_AUD_LOG;'
     ,next_date  => TO_DATE('27/02/2008 12:35:21','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL   => 'TRUNC(SYSDATE+1)'
     ,no_parse   => FALSE
    );
END;

Tip: To speed up searching on SYSTEM.AUD$_BU you can create 2 indexes (one on timestamp# and the other to userid)

CREATE INDEX SYSTEM.AUD$_BU_TIME_IDX ON SYSTEM.AUD$_BU (TIMESTAMP#) 
NOLOGGING TABLESPACE AUDIT_DATA;

CREATE INDEX SYSTEM.AUD$_BU_USERID_IDX ON SYSTEM.AUD$_BU (USERID) 
NOLOGGING TABLESPACE AUDIT_DATA;