Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am having a problem to archive and then delete portions of the fga_log$ table.

Here's what a script basically does

insert into fga_log_archive
select 
  ...
from
  sys.fga_log$;


begin
   sys.dbms_audit_mgmt.set_last_archive_timestamp(
     audit_trail_type     => sys.dbms_audit_mgmt.audit_trail_fga_std,
     last_archive_time    => systimestamp
  );

  sys.dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type        => sys.dbms_audit_mgmt.audit_trail_fga_std,
   use_last_arch_timestamp => true
  );
end;
/

The documentation for dbms_audit_mgmt.clean_audit_trail says:

This procedure deletes audit trail records. The CLEAN_AUDIT_TRAIL procedure is usually called after the SET_LAST_ARCHIVE_TIMESTAMP Procedure has been used to set the last archived timestamp for the audit records.

So, I have expected the records that were archived with the insert statement to be deleted from the fga_log$ table by the clean_audit_trail call. That's not the case however. Am I missing something?

Edit

The audit configuration parameters as selected with

select 
  substrb(parameter_name ,1,25) name, 
  substrb(parameter_value,1,20) value,
  substrb(audit_trail    ,1,20) trail
from
  dba_audit_mgmt_config_params
;

is

NAME                      VALUE                TRAIL
------------------------- -------------------- --------------------
DB AUDIT TABLESPACE       SYSAUX               STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE       AUDIT_LOG_TABLESPACE FGA AUDIT TRAIL
AUDIT FILE MAX SIZE       10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE       10000                XML AUDIT TRAIL
AUDIT FILE MAX AGE        5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE        5                    XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000                FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE  1000                 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 999                  FGA AUDIT TRAIL

Edit II

select * from v$version returns

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
share|improve this question
    
I suppose is_cleanup_initialized returns true for that audit type? (I have no idea if that's even required or not.) –  Mat Jan 21 '13 at 8:57
    
@mat: Yes, sys.dbms_audit_mgmt.is_cleanup_initialized(sys.dbms_audit_mgmt.audit_trail_fga_‌​std) returns true. –  René Nyffenegger Jan 21 '13 at 10:19
    
Can you edit the question with the output of SELECT * FROM dba_audit_mgmt_config_params;. Thanks –  FreshPhilOfSO Jan 21 '13 at 10:45
    
@Phil: I have edited my answer accordingly. –  René Nyffenegger Jan 21 '13 at 11:00
    
Rac? 11.2.0.3?? –  FreshPhilOfSO Jan 21 '13 at 11:12
show 1 more comment

1 Answer

up vote 1 down vote accepted

As Mat has pointed out, I hit Bug 9164488 (DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL does not delete sys.aud$ and sys.fga_log$ tables [ID 9164488.8]) which should be fixed in Release 11.2.0.2.

In the meantime, running on 11.2.0.1, I was able to circumvent the bug with a purge job in order to do the work. As soon as I ran

begin
  sys.dbms_audit_mgmt.create_purge_job (
     audit_trail_type           => sys.dbms_audit_mgmt.audit_trail_fga_std,
     audit_trail_purge_interval => 999,
     audit_trail_purge_name     =>'Purge_Test',
     use_last_arch_timestamp    => true
  );
end;
/

clean_audit_trail would do the work, as previously expected.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.