DBMS_AUDIT_MGMT - Cleanup Configuration Questions

Hello, we have an Oracle 11g system set up that I have turned on auditing for ALL activities at the session level on specific tables.  The audit flag is set to DB.  The system has been up for about 2.5 years now.  We have been instructed by our client that the audit logs only need to be kept for a year back.  I have never done an oracle audit cleanup... so I just want to make sure I'm on the right path.

We run in a pretty tight shop and I don't have the luxury to just run things when I want.  Everything has to go through a release and has to be executed by the production folks.  What I would like to do, is have an automated method to, once a year, clean out audit records that older than a year (as of the run date).

So, if I am running the cleanup on 10/31/2011, I want to keep all audit records from October 1, 2010 to October 31, 2011 and delete anything else before that.

So, based on that ... please help me think through how this would be implemented:

If I set the DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP to 10/01/2010 12:00:00 AM and then run the DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL it would then delete all records BEFORE 10/01/2010 correct?

I have researched this and I would use the following code?

   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   last_archive_time => to_timestamp('2010/OCT/01 0:00:00', 'YYYY/MON/DD HH24:MI:SS'),
   rac_instance_number => 1 /* single instance database */);

   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);

Would that do what I need?

Thanks in advance.
Who is Participating?
Praveen Kumar ChandrashekatrConnect With a Mentor Database Analysist Senior Commented:
Yes it will delete the old records before the LAST_ARCHIVE_TIMESTAMP. You can cross check the timestamp set in Database

SELECT * FROM dba_audit_mgmt_last_arch_ts;

Before you can purge the database audit trail you must perform a one-time initialization of the audit management infrastructure. This is done using the INIT_CLEANUP procedure.

if you want to automated it you can create the clean up job in oracle with audit_trail_purge_interval defined which is in hours.

the job can be viewed on DBA_SCHEDULER_JOBS view and it can be enable and disable using SET_PURGE_JOB_STATUS procedure.

check this doc for more detail info.

Roxanne25Author Commented:
Thank you, that answers my question.  Sorry for the delay, I was on vacation.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.