Recently the Database Services team upgraded a client from Oracle 8i to 11g via the import/export method. A few months later, we found that the system tablespace was filling up, and that 70%+ of the space was used by the sys.aud$ table.
Digging in, we discovered that when you create a new Oracle 11g database using the Database Configuration Assistant (DBCA) tool, auditing is enabled for a lot of events—which is a problem. So, what’s the fix?
First, check the system-wide auditing setting. If it is a default database, then it is likely to be ‘DB’ indicating that auditing will go to the sys.aud$ table. Here is an example from our client’s Oracle 11g database.
SQL> SHOW PARAMETER AUDIT_TRAIL
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_trail string DB
Next, look at what is being audited.
SQL> set pagesize 66
SQL> set linesize 120
SQL> column user_name format a10
SQL> column proxy_name format a10
SQL> column privilege format a30
SQL> select * from DBA_PRIV_AUDIT_OPTS;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ------------------------------ ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
23 rows selected.
And there are a lot of DBA views related to auditing.
SQL> select view_name
2 from dba_views
3 where view_name like 'DBA%AUDIT%'
4 order by view_name;
VIEW_NAME
------------------------------
DBA_AUDIT_EXISTS
DBA_AUDIT_MGMT_CLEANUP_JOBS
DBA_AUDIT_MGMT_CLEAN_EVENTS
DBA_AUDIT_MGMT_CONFIG_PARAMS
DBA_AUDIT_MGMT_LAST_ARCH_TS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
18 rows selected.
Note that the DBA_AUDIT_TRAIL seems to be a view on the SYS.AUD$ table.
SQL> select count(*) from sys.aud$;
COUNT(*)
----------
5
SQL> select count(*) from dba_audit_trail;
COUNT(*)
----------
5
Several of the DBA_AUDIT% views select only particular audit actions. In particular, note the DBA_AUDIT_SESSION view above. We’ll use it next.
What we found for this particular client, and it’s probably typical for a lot of clients, is that auditing the “create session” privilege puts a LOT of data in sys.aud$ – see below for counts.
SQL> select count(*) from DBA_AUDIT_TRAIL;
COUNT(*)
----------
2,312,829
SQL> select count(*) from DBA_AUDIT_EXISTS
COUNT(*)
----------
12
SQL> select count(*) from DBA_AUDIT_OBJECT;
COUNT(*)
----------
323
SQL> select count(*) from DBA_AUDIT_POLICIES;
COUNT(*)
----------
0
SQL> select count(*) from DBA_AUDIT_SESSION;
COUNT(*)
----------
2,311,548
SQL> select count(*) from DBA_AUDIT_STATEMENT;
COUNT(*)
----------
946
As you can see, the overall count was 2.312 million rows, and over 99% of that was auditing to show that users had successfully logged into or out of Oracle! This was not a lot of useful information to our client.
There are several ways of handling this volume of data:
- Routinely truncate the sys.aud$ table (useful if the data is not really used or needed).
- Turn off auditing entirely (set audit_trail to none and bounce the DB).
- Implement some sort of automatic cleanup job (perhaps remove the unneeded rows or the rows older than a given date).
- Turn off auditing of create session (and optionally turn on auditing of create session whenever it fails).
For this particular client, we chose a combination of 1 and 4. We truncated the sys.aud$ table to get rid of the old data, turned off auditing of the create session, then turned on auditing of the create session whenever there was failure. This cleared out the overall audit table and dramatically reduced the total amount of data saved in sys.aud$.
SQL>
SQL> rem - turn off create session
SQL>
SQL> noaudit create session;
Noaudit succeeded.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ------------------------------ ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
22 rows selected.
SQL>
SQL> rem - note one less row now – the create session is missing!
SQL> rem – now turn it back on, but only on failure:
SQL>
SQL> audit create session whenever not successful;
Audit succeeded.
SQL> select * from DBA_PRIV_AUDIT_OPTS;
USER_NAME PROXY_NAME PRIVILEGE SUCCESS FAILURE
---------- ---------- ------------------------------ ---------- ----------
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
CREATE SESSION NOT SET BY ACCESS
AUDIT SYSTEM BY ACCESS BY ACCESS
ALTER SYSTEM BY ACCESS BY ACCESS
23 rows selected.
Hope this helps someone else with this problem!
For more information on our SmartSource managed services, click here.
NOTE: This blog was originally posted May 21, 2012.