AUD$ Growing After Upgrade to 11g or ATC patch installed (Doc ID 852815.1)

Last updated on NOVEMBER 03, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 10.2.0.5 [Release 10.2]
Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.1.0.7 [Release 11.1]
Information in this document applies to any platform.
***Checked for relevance on 21-Feb-2013***

Symptoms

AUD$ table grows at a very high speed in the following situations:

Creating or upgrading the database to : 11.1.0.7.0
Creating or upgrading the database to : 10.2.0.4 + 6996030


If the audit option is enabled "By Session" then only one entry should be created for a (session + object) even if the object was accessed multiple times in that session. But in above mentioned version (combinations), session level audit will not work , i.e more than one audit entry will be created for every access on the object from the same session. This increases the audit records count numerously.

Example:

Oracle rdbms 10.2.0.3:

SQL> select * from DBA_STMT_AUDIT_OPTS where user_name='TEST';

USER_NAME    AUDIT_OPTION   SUCCESS     FAILURE
---------    -------------  ----------  ----------
TESTUSR      INSERT TABLE   BY SESSION  BY SESSION

SQL> show parameter audit
NAME TYPE VALUE
---------------------- ------- -------------------------------
audit_file_dest        string  /u01/app/oracle/db102/admin/testdb/adump
audit_sys_operations   boolean FALSE
audit_syslog_level     string
audit_trail            string  DB

SQL> create table test (no number);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> select OS_USERNAME,USERNAME,count(*), OBJ_NAME from DBA_AUDIT_TRAIL
where USERNAME='TESTUSR' and OBJ_NAME= 'TEST' group by OS_USERNAME,USERNAME,OBJ_NAME;

OS_USERNAME     USERNAME       COUNT(*)  OBJ_NAME
--------------- ------------ ---------- --------------------------
oracle          TESTUSR             1         TEST

SQL> insert into test values(2);
1 row created.

SQL> insert into test values(3);
1 row created.

SQL> select OS_USERNAME,USERNAME,count(*), OBJ_NAME from DBA_AUDIT_TRAIL
where USERNAME='TESTUSR' and OBJ_NAME= 'TEST' group by OS_USERNAME,USERNAME,OBJ_NAME;

OS_USERNAME     USERNAME       COUNT(*)  OBJ_NAME
--------------- ------------ ---------- --------------------------
oracle          TESTUSR             1         TEST


Oracle rdbms 11.1.0.7:

SQL> select * from DBA_STMT_AUDIT_OPTS where user_name='TEST';

USER_NAME    AUDIT_OPTION   SUCCESS     FAILURE
---------    -------------  ----------  ----------
TESTUSR      INSERT TABLE   BY SESSION  BY SESSION

SQL> show parameter audit
NAME TYPE VALUE
---------------------- ------- -------------------------------
audit_file_dest        string  /u01/app/oracle/db102/admin/testdb/adump
audit_sys_operations   boolean FALSE
audit_syslog_level     string
audit_trail            string  DB


SQL> create table test (no number);
Table created.

SQL> insert into test values(1);
1 row created.

SQL> select OS_USERNAME,USERNAME,count(*), OBJ_NAME from DBA_AUDIT_TRAIL
where USERNAME='TESTUSR' and OBJ_NAME= 'TEST' group by OS_USERNAME,USERNAME,OBJ_NAME;

OS_USERNAME     USERNAME       COUNT(*)  OBJ_NAME
--------------- ------------ ---------- --------------------------
oracle          TESTUSR             1         TEST


SQL> insert into test values(1);
1 row created.

SQL> select OS_USERNAME,USERNAME,count(*), OBJ_NAME from DBA_AUDIT_TRAIL
where USERNAME='TESTUSR' and OBJ_NAME= 'TEST' group by OS_USERNAME,USERNAME,OBJ_NAME;


OS_USERNAME     USERNAME       COUNT(*)  OBJ_NAME
--------------- ------------ ---------- --------------------------
oracle          TESTUSR            2          TEST

SQL> insert into test values(1);
1 row created.

SQL> select OS_USERNAME,USERNAME,count(*), OBJ_NAME from DBA_AUDIT_TRAIL
where USERNAME='TESTUSR' and OBJ_NAME= 'TEST' group by OS_USERNAME,USERNAME,OBJ_NAME;

OS_USERNAME     USERNAME       COUNT(*)  OBJ_NAME
--------------- ------------ ---------- --------------------------
oracle          TESTUSR             3         TEST

Changes

You have upgraded to or created your databases with a recent release that includes the fix to bug 6023472, this fix is part of the Audit Trail Cleanup patches for 10.2.0.4 and also present as of 10.2.0.5, 11.1.0.7 and 11.2.0.1.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms