Jump to Navigation

299 - Oracle Audit Management

From 11g, Oracle by default enable auditing with 'DB' option. 
That means audit information will be stored in database table.
This information is useful, but apart from lot of benefits, the auditing raises performance issues and
disk space problem.

 * Too much activity especially unnecessary information will being audited.
 * AUD$ table still placed in the SYSTEM tablespace since the database creation.
 * SYSTEM tablespace is growing forever and occupied the disk space.

By default AUD$ table resides 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.

Please login as SYSDBA.

1. Check the amount of records

SQL> SELECT COUNT(*) FROM sys.aud$;

SQL> SELECT COUNT(*) FROM sys.FGA_LOG$;

SQL> COLUMN table_name FORMAT A20;

SQL> COLUMN tablespace_name FORMAT A20;

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

 

2. Check default clean up interval parameters

SQL> COLUMN parameter_name FORMAT A30;

SQL> COLUMN parameter_value FORMAT A20;

SQL> COLUMN audit_trail FORMAT A20;

SQL> SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
DB AUDIT TABLESPACE            AUDIT_AUX            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_AUX            FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT WRITE MODE               QUEUED WRITE MODE    UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE             5                    UNIFIED AUDIT TRAIL

 

 

3. Initialize audit management with a 24 hour clean up interval

 To purge audit data, you have to initianlize the audit parameters anyway.

 BEGIN
   SYS.DBMS_AUDIT_MGMT.init_cleanup(
     audit_trail_type         => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
     default_cleanup_interval => 24);
 END;
 /

 De initialize :

BEGIN
 SYS.DBMS_AUDIT_MGMT.DEINIT_CLEANUP(
  AUDIT_TRAIL_TYPE  => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD);
END;
/

* AUDIT_TRAIL_AUD_STD - Standard database audit records in the SYS.AUD$ table
* AUDIT_TRAIL_DB_STD - Both standard audit (SYS.AUD$) and FGA audit(SYS.FGA_LOG$) records
* AUDIT_TRAIL_FGA_STD - Standard database fine-grained auditing (FGA) records in the SYS.FGA_LOG$ table

4.  Check the parameters again

SQL> SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

----------------------------------------------------------------------------------------
SET SERVEROUTPUT ON
BEGIN
 IF
   SYS.DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
 THEN
   SYS.DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for clean-up');
 ELSE
   SYS.DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for clean-up.');
 END IF;
END;

 

5. Set Time Stamp

begin
   sys.dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type  => sys.dbms_audit_mgmt.audit_trail_aud_std,
     last_archive_time => to_timestamp('2014-10-30 00:00:00','YYYY-MM-DD HH24:MI:SS'),
     rac_instance_number  => null
   );
end;
/

begin
   sys.dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type  => sys.dbms_audit_mgmt.audit_trail_fga_std,
     last_archive_time => to_timestamp('2014-10-30 00:00:00','YYYY-MM-DD HH24:MI:SS'),
     rac_instance_number  => null
   );
end;
/

 

SQL> select * from DBA_AUDIT_MGMT_LAST_ARCH_TS;

 

 

6. Clean up audit data

BEGIN
SYS.DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
use_last_arch_timestamp => true);
END;
/

If you want to delete all audit, you set 'false' to the use_last_arch_timestamp parameter.

 

7. Check audit records again

SQL> SELECT COUNT(*) FROM sys.aud$;
SQL> SELECT COUNT(*) FROM SYS.FGA_LOG$;

 

8. Disable audit trail

SQL> ALTER SYSTEM SET AUDIT_TRAIL = NONE SCOPE=SPFILE

Restert Oracle Database to use a new parameter setting.

 



Main menu 2

Story | by Dr. Radut