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_A RCHIVE_TIM ESTAMP to 10/01/2010 12:00:00 AM and then run the DBMS_AUDIT_MGMT.CLEAN_AUDI T_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_A RCHIVE_TIM ESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI L_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_AUDI T_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI L_OS,
use_last_arch_timestamp => TRUE);
END;
Would that do what I need?
Thanks in advance.
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_A
I have researched this and I would use the following code?
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_A
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
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_AUDI
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAI
use_last_arch_timestamp => TRUE);
END;
Would that do what I need?
Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER