DBMS_AUDIT_MGMT - Cleanup Configuration Questions

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-05-12
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.
Question by:Roxanne25
LVL 12

Accepted Solution

Praveen Kumar Chandrashekatr earned 2000 total points
ID: 36922414
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.


Author Comment

ID: 36955799
Thank you, that answers my question.  Sorry for the delay, I was on vacation.

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question