Oracle: 11g automatically enables auditing

In one of my dbca-created databases the SYSTEM-Tablespace filled up until the DB halted with this error:

ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-02002: error while writing to audit trail

The named table SYS.AUD$ is the target for all auditing-data. But actually I didn’t intentionally enabled any auditing.

It appeared that since Oracle Database 11g the Database Configuration Assistent (dbca) and Database Upgrade Assistent (dbua) automatically sets the parameter audit_trail=’DB’ to enable a base-auditing by default which goes to the system-tablespace. In the dbca’s UI is no single hint of that fact since 11g R2. And now it bit me.

SQL> select name, value from v$parameter where name like 'audit_trail';

NAME                      VALUE
------------------------- ----------------------------------------
audit_trail               DB

SQL>

This setting exists in all of my dbca-databases. But until now I was neither aware of it nor was there ever a problem. So how come the 2,5 GB system-tablespace filled up until the brim within 12 month on a system with moderate traffic?!
Examining the auditdata in dba_audit_trail showed up permanent LOGONs/LOGOFFs with connection-durations of just some 30 seconds until a few (less than five) minutes.

The reason seems to be a “misconfigured” connection-pool on the application-server that disconnects to soon instead of holding the sessions for 30 minutes or so to reuse them. And so I ended up with more than 10-million lines of audit-data with the size of more than 2GB and a 100% used system-tablespace.

For a quick fix I simply truncated the table SYS.AUD$.

truncate table AUD$;

On next occasion I maybe stop the auditing – or set up a maintenance-job for the AUD$.

I tended to not use the DBMS_AUDIT_MGMT for cleaning up the audit-trail. As my auditing is for information-purposes only and not for compliance, I do not archive the log-data. Using DBMS_AUDIT_MGMT requires to set an archiving-date prior to delete the records from SYS.AUD$. This would impose unnecessary load on my database, as it would mean updating all audit-records with the archiving-date and then deleting all that records – each time generating redo-information. Also I wanted a rolling window of 180 days of audit-trail in my database. So the easiest way was to simply set up a scheduler-job:

begin
dbms_scheduler.create_job
 (job_name => 'PURGE_AUDIT_TRAIL',
  job_type => 'PLSQL_BLOCK',
  job_action=> 'delete from sys.aud$ where ntimestamp# < sysdate-180;commit;',
  start_date=> trunc(sysdate),
  repeat_interval=> 'FREQ=DAILY; BYHOUR=20;',
  enabled=>true,
  comments=>'Delete auditlog-records older than 180 days. (MT)');
end;

Some more info on auditing:
http://juliandontcheff.wordpress.com/2011/05/12/auditing-vs-performance-in-the-oracle-database/

Advertisements