Wednesday, June 19, 2013

Manage SYS.AUD$ table

From 11g, Oracle by default enable auditing with 'DB' option.  That means audit information will store in database table. With this settings Oracle will audit following activities:

ALTER ANY PROCEDURE, ALTER ANY TABLE, ALTER DATABASE,ALTER PROFILE, ALTER SYSTEM, ALTER USER, AUDIT SYSTEM, CREATE ANY JOB, CREATE ANY LIBRARY, CREATE ANY PROCEDURE, CREATE ANY TABLE, CREATE EXTERNAL JOB, CREATE PUBLIC DATABASE LINK, CREATE SESSION, CREATE USER, DATABASE LINK, DROP ANY PROCEDURE, DROP ANY TABLE, DROP PROFILE
DROP USER, EXEMPT ACCESS POLICY, GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE, GRANT ANY ROLE, PROFILE, PUBLIC SYNONYM ROLE, SYSTEM AUDIT

Apart from lot of benefits,  auditing may raise performance issues and the reasons are:
  • Too much activity is being audited
  • AUD$ table still placed in the SYSTEM tablespace
  • Oracle bugs.
1. Too much is being audited

The more activity you audit the more audit records will generate.  You need to restrict unnecessary auditing because it cost your resources and hamper performance. To see which activities are being audited
  
SELECT  * FROM DBA_PRIV_AUDIT_OPTS UNION SELECT * FROM DBA_STMT_AUDIT_OPTS;


2. AUD$ table still placed in SYSTEM tablespace:
By default AUD$ table lies in SYSTEM tablespace.  You will face space managment issue if you not move AUD$ table in SYSAUX tablespace along with indexes.  Use Oracle recommended package DBMS_AUDIT  to purge old unnecessary entries. Periodically shrink / truncate AUD$ table.

 BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'SYSAUX');
END;
/

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


CREATE TABLE AUD_BACKUP AS SELECT * FROM AUD$;
TRUNCATE AUD$;
INSERT INTO AUD$ SELECT * FROM AUD_BACKUP;
DROP TABLE AUD_BACKUP PURGE;



3. Oracle bugs
Well there are couple of  known bugs, so apply latest patch.

Resources:

No comments: