Oracle Database 11.2.0.3 - Audit Trail Management
There seems to have been a glut of posts around managing an 11gR2 audit trail recently. I've had this one in the works for a little while, so thought I'd push on regardless even if it's just for my sake.
Auditing. You've gone and set up logging to your database, so now you know exactly which app is behaving badly, exactly which users forget their passwords every time they log in, and who is trying to select from system tables that they really shouldn't be.
The trouble is, if you end up with a badly behaving DBConsole agent like I did, then you find yourself with several million entries in the audit table that you really don't want there, and the SYS tablespace grows much bigger than you'd like.
There's 2 approaches to take here. One is the timed approach - archive/delete anything older than X days - and this is the one that I will be focusing on here. The other is the "damn it all, delete the whole lot", the easiest way to achieve this is doing a truncate on sys.aud$, which will delete everything.
The first thing you may think of doing, and this is certainly how I've seen it done before by an outsourced DBA company, is set up a cron or DBMS_SCHEDULER job to hack away at sys.aud$, deleting entries older than date 'x'. When I first saw this I was a bit concerned, because I had it drummed into me that you never change anything in SYS, but this is specifically referenced in the Oracle documents. From http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG473
There's 4 main steps to purging the audit trail. Some of which makes sense in my head, some of which seems a bit backwards. These steps are:
Auditing. You've gone and set up logging to your database, so now you know exactly which app is behaving badly, exactly which users forget their passwords every time they log in, and who is trying to select from system tables that they really shouldn't be.
The trouble is, if you end up with a badly behaving DBConsole agent like I did, then you find yourself with several million entries in the audit table that you really don't want there, and the SYS tablespace grows much bigger than you'd like.
There's 2 approaches to take here. One is the timed approach - archive/delete anything older than X days - and this is the one that I will be focusing on here. The other is the "damn it all, delete the whole lot", the easiest way to achieve this is doing a truncate on sys.aud$, which will delete everything.
The first thing you may think of doing, and this is certainly how I've seen it done before by an outsourced DBA company, is set up a cron or DBMS_SCHEDULER job to hack away at sys.aud$, deleting entries older than date 'x'. When I first saw this I was a bit concerned, because I had it drummed into me that you never change anything in SYS, but this is specifically referenced in the Oracle documents. From http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG473
"Note: SYS.AUD$ and SYS.FGA_LOG$ are the only SYS objects that can ever be directly modified"The details below are taken largely from the Oracle Docs with help from Tim at Oracle-Base.
There's 4 main steps to purging the audit trail. Some of which makes sense in my head, some of which seems a bit backwards. These steps are:
- Initialise the cleanup - move the aud$ table out of the SYSTEM tablespace and prepare it for cleanup. This is the one that I don't really understand, but you have to do it:
http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG468 - Configure an earliest timestamp to keep. It's possible to get DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL() to delete everything with no timestamp set, but I'm focusing on keeping the last 'x' days here:
http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG469 - Create a job to purge the data older than the earliest timestamp:
http://docs.oracle.com/cd/E11882_01/network.112/e36292/auditing.htm#DBSEG470
You'd think that's it. But no! The job that purges the data has no impact on the timestamp that it purges data up to. The job will run repeatedly (e.g. daily), but always does a "DELETE FROM sys.aud$ WHERE ntimestamp < 'constant' - but 'constant' never changes.
The answer, is to schedule another job (step #4) using DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(). Run this just before the purge job to set the earliest timestamp to the value required.
It is possible to combine both of these steps into one job, but for the simplicity of creating the cleanup job using the procedures in DBMS_AUDIT_MGMT, I found it easier to create them individually. It also means that you can then play with them both individually and set them up exactly as you need.
It is possible to combine both of these steps into one job, but for the simplicity of creating the cleanup job using the procedures in DBMS_AUDIT_MGMT, I found it easier to create them individually. It also means that you can then play with them both individually and set them up exactly as you need.
Comments
Post a Comment