Link to home
Start Free TrialLog in
Avatar of Roxanne25
Roxanne25

asked on

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?

BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   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 */);
END;

BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
   use_last_arch_timestamp => TRUE);
END;

Would that do what I need?

Thanks in advance.
ASKER CERTIFIED SOLUTION
Avatar of Praveen Kumar Chandrashekatr
Praveen Kumar Chandrashekatr
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Roxanne25
Roxanne25

ASKER

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