If your not familiar with Oracle’s auditing features then a good place to start is with Oracle’s Documentation. I personally think Oracle’s documentation, especially for the database products is great.
http://docs.oracle.com/cd/B28359_01/network.111/b28531/auditing.htm#autoId1
The above documentation linked to above describes what auditing is, why you would need it, etc. There’s no need for me to repeat it here.
Our environment is pretty small, with a limited number of people who have access to the databases, etc. So normally I have the audit trail disabled, however for this particular database we did need to increase logging to investigate some issues. After we were finished, we used NOAUDIT to disable the extra logging we enabled. However we left AUDIT_TRAIL set to DB.
Move to a few months later and on a routine scan of database performance I noticed a query consuming a fair amount of resources.
SQL Details: gh9pd08vhptgr
SELECT TO_CHAR(current_timestamp AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') AS curr_timestamp, COUNT(username) AS failed_count
FROM sys.dba_audit_session
WHERE returncode != 0 AND timestamp >= current_timestamp - TO_DSINTERVAL('0 0:30:00')
By looking at the screenshot above it seems pretty nasty but in reality it was just a small spike on the Grid Control Top Activity chart. As I mentioned, our environment is small and our servers have more than enough horsepower, so it didn’t have a significant impact on performance.
From Metalink note: Slow Performance Of DBA_AUDIT_SESSION Query From EM [ID 829103.1] “There is a known performance issue with DBA_AUDIT_SESSION table per non-published Bug 7633167”
However with AUDIT_TRAIL=DB, LOGON and LOGOFF’s were still be recorded. Since this database hosts our Portal schema it mean upwards of 200k of entries going into the sys.aud$ daily, for a grand total of 32 million rows!
The note provides two options for the query above, the first is to disable the Failed Login Count Metric, the second is to purge the sys.aud$ table. Note 73408.1 How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$
Since at the moment we don’t require auditing to be enabled, then I am simply going to truncate the sys.aud$ table and disable auditing by setting the database initialization parameter AUDIT_TRAIL=none and restarting the database at the next maintenance window.
If you do require auditing then you should setup a purging strategy. If you need to do this, some good blog articles to read are:
http://www.pythian.com/news/1106/oracle-11g-audit-enabled-by-default-but-what-about-purging/
http://damir-vadas.blogspot.com/2010/06/auditing-database.html
