DBMS_AUDIT_MGMT - Cleanup Configuration Questions

Posted on 2011-10-05
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

    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

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now