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.
Oracle Database

Avatar of undefined
Last Comment
Roxanne25

8/22/2022 - Mon