My Oracle Support Banner

Rapid growth of AUD$ because ALTER SESSIONS being audited for all users (Doc ID 2408610.1)

Last updated on JUNE 11, 2018

Applies to:

Oracle Fusion Application Toolkit - Version 11.1.9.2.0 and later
Information in this document applies to any platform.

Symptoms

When a Fusion Application OLTP database is upgraded from 11g to 12c it can be cause a large number of rows be inserted into AUD$ tables if the following items are all TRUE

1. DB is upgraded from 11g to 12c

2. DB Audit is set to any value other than NONE   

SQL> SELECT name, value FROM v$system_parameter WHERE name = 'audit_trail';

NAME                             VALUE
-------------------------------- ------------------------------
audit_trail                      DB, EXTENDED

3. Unified Auditing is disabled

SQL> SELECT parameter, value FROM v$option WHERE parameter = 'Unified Auditing';

PARAMETER                             VALUE
------------------------------------- ------------------------------
Unified Auditing                      FALSE

4. Database Vault is enabled

SQL> SELECT inst_id, parameter, value FROM gV$OPTION WHERE PARAMETER = 'Oracle Database Vault';

INST_ID                  PARAMETER                 VALUE
------------------------ ------------------------- -------
1                        Oracle Database Vault     TRUE
2                        Oracle Database Vault     TRUE

5. ALTER SESSION being audited for all Users

SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS WHERE audit_option = 'ALTER SESSION' AND USER_NAME IS NULL;

USER_NAME                   PROXY_NAME                 AUDIT_OPTION       SUCCESS       FAILURE      
--------------------------- -------------------------- ------------------ ------------- -------------
                                                       ALTER SESSION      BY ACCESS     BY ACCESS       

6. A lot of ALTER SESSION are being audited on daily basis (results will look similar to)

SQL> SELECT dbms_lob.substr(sqltext,100,1), COUNT(1) FROM sys.aud$ WHERE NTIMESTAMP# > sysdate - 1 GROUP BY dbms_lob.substr(sqltext,100,1) ORDER BY 2 DESC;

DBMS_LOB.SUBSTR(SQLTEXT,100,1)                                                      COUNT(1)
----------------------------------------------------------------------------------- ------------------------------
ALTER SESSION SET TIME_ZONE = 'UTC'                                                 942200
alter session set NLS_TIMESTAMP_TZ_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF TZR"          824593
alter session set NLS_NUMERIC_CHARACTERS = ".,"                                     824593
alter session set NLS_LENGTH_SEMANTICS = "CHAR"                                     824593
alter session set NLS_TIME_TZ_FORMAT = "HH24:MI:SS.FF TZR"                          824593
alter session set NLS_DATE_LANGUAGE = "NUMERIC DATE LANGUAGE"                       824593
alter session set NLS_TIMESTAMP_FORMAT = "YYYY-MM-DD HH24:MI:SS.FF"                 824593
alter session set NLS_TERRITORY = "AMERICA"                                         824593
alter session set NLS_TIME_FORMAT = "HH24:MI:SS.FF"                                 824593
alter session set NLS_COMP = "BINARY"                                               824593
alter session set NLS_DATE_FORMAT = "YYYY-MM-DD"                                    824593
alter session set NLS_SORT = BINARY                                                 824593
alter session set NLS_LANGUAGE = 'AMERICAN'                                         823782

 

If all the above are found to be TRUE log a SR with Oracle Support to get confirmation to run command to disable auditing ALTER SESSION for all users. When creating the SR please upload the results of the above six items.

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.