Monday, June 4, 2012

Oracle 11g Audit Trail

If you don't care about auditing the DML in your database then you will agree with me that Oracle should not have made auditing mandatory. Yes you can set this parameter to NONE, but you will still get some level of auditing. This is only a brief overview and I am not going into too much depth here.

The possible values for the audit_trail parameter are
 SQL> show parameters audit_trail

DB
DB_EXTENDED
OS
XML
NONE

If the value is DB you should set up a cron script to purge these files:
Script to purge audit trail files in Oracle 11g.

Where are the audit files

SQL> select value from v$parameter where name = 'audit_file_dest';

VALUE
--------------------------------------------
/app/oracle/admin/db251/adump


To update this parameter
SQL> alter system set audit_trail=db scope=spfile;
SQL> shutdown immediate;
SQL>startup;
SQL> show parameter audit_trail;
AUDIT_TRAIL TYPE   VALUE
-----------------   ------    -------
audit_trail           string   DB

The default is OS. If the setting is OS then you should also periodically delete rows from the AUD$ table.


SQL> select count(*) from aud$;
COUNT(*)
----------
344273

SQL> select count(*) from aud$ where ntimestamp# > sysdate - 21;
 COUNT(*)
----------
53550

SQL> delete from aud$ where ntimestamp# > sysdate - 21;

53550 rows deleted.

No comments:

Post a Comment

Oracle runInstaller fails on Linux, Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set.

How to get Oracle's runInstaller to run on Linux or Unix environment using from Windows using Cygwin as an xterm emulator. Download an...