This discussion is archived
5 Replies Latest reply: Aug 25, 2012 3:52 AM by SanjayPatel RSS

Purging Audit records

jdfjsu99 Newbie
Currently Being Moderated
Hopefully someone can answer this for me. I'm running AV 10.3 on Solaris 10. Both of my source databases are on OEL 5 and are 11.2.0.2. I am currently writing the audit trail to the DB.


Will the collector (or some other piece) update the LAST_ARCHIVE_TS attribute on the source database?

I want to set up the purge job to only purge archived audit logs, but this attribute is not set on my source databases. From reading the documents it looks like something on the AV side should be calling DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP, but I don't see that happening.


Thanks for the help.

Edited by: jdfjsu99 on May 21, 2012 1:51 PM
  • 1. Re: Purging Audit records
    damorgan Oracle ACE Director
    Currently Being Moderated
    Have your reviewed the documentation (http://tahiti.oracle.com) for the DBMS_AUDIT_MGMT built-in package.

    Demos: http://www.morganslibrary.org/reference/pkgs/dbms_audit_mgmt.html
  • 2. Re: Purging Audit records
    jdfjsu99 Newbie
    Currently Being Moderated
    I have. I understand how the package works. My question is related specifically to Audit Vault.

    According to section 4.10.2 of the Audit Vault 10.3 Admin Guide:
    "Oracle Audit Vault is integrated with the DBMS_AUDIT_MGMT package on a source database. This integration automates the purging of audit records from the AUD$ and FGA_LOG$ files, and from the operating system .aud and .xml files after they have been successfully inserted into the Audit Vault repository by the Audit Vault collector. After the purge is completed, the collectors automatically set a timestamp on audit data that has been collected. Therefore, you must set the USE_LAST_ARCH_TIMESTAMP property to true to ensure that the right set of audit records are purged."

    To me that implies that the collector is running something similar to the following in the source database:

    BEGIN
    DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP);
    END;
    /

    However when I run the following statement I get "no rows selected".

    SELECT * FROM dba_audit_mgmt_last_arch_ts;
  • 3. Re: Purging Audit records
    AG_DB Newbie
    Currently Being Moderated
    I will give another solution for purging your audit log.I think it simple we can schedule with help of DBscheduler .Please look it below.
    Timestamp Creating Schedule(Everyday it will create a time stamp for purging job)

    /* Timestamp creation for FGA Auditing */
    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'DAILY_AUDIT_ARC_FGA_TIMESTAMP',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,LAST_ARCHIVE_TIME => sysdate-7); END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=24',
    enabled => TRUE,
    comments => 'Create an archive timestamp'
    );
    END;
    /

    /* Timestamp creation for standard Auditing */

    BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
    job_name => 'DAILY_AUDIT_ARC_COMN_TIMESTAMP',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(AUDIT_TRAIL_TYPE =>
    DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,LAST_ARCHIVE_TIME => sysdate-7); END;',
    start_date => sysdate,
    repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
    enabled => TRUE,
    comments => 'Create an archive timestamp'
    );
    END;
    /


    /* Cleanup Initialization for FGA */

    BEGIN
         DBMS_AUDIT_MGMT.INIT_CLEANUP(
         AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
         DEFAULT_CLEANUP_INTERVAL => 24);-- DEFAULT TIME INTERVAL
    END;
    /


    /* Cleanup Initialization for standard audit */

    BEGIN
         DBMS_AUDIT_MGMT.INIT_CLEANUP(
         AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
         DEFAULT_CLEANUP_INTERVAL => 24);-- DEFAULT TIME INTERVAL
    END;
    /

    /* Purge job Creation for FGA audit*/

    BEGIN
    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24/* hours */,
    AUDIT_TRAIL_PURGE_NAME => 'Daily_FGA_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP => TRUE
    );
    END;
    /

    /* Purge job Creation for standard audit*/

    BEGIN
    DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
    AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    AUDIT_TRAIL_PURGE_INTERVAL => 24/* hours */,
    AUDIT_TRAIL_PURGE_NAME => 'Daily_COMN_Audit_Purge_Job',
    USE_LAST_ARCH_TIMESTAMP => TRUE
    );
    END;
    /

    commit;
  • 4. Re: Purging Audit records
    KuljeetPalSingh Guru
    Currently Being Moderated
    MOS documents on this
    How To Investigate Why DBMS_AUDIT_MGMT Is Not Purging Audit Files For An Audit Vault Source Database? [ID 1329116.1]
  • 5. Re: Purging Audit records
    SanjayPatel Newbie
    Currently Being Moderated
    Can you pl. verify that the Audit Trail Is Initialized for Cleanup or not. You can use use following PL/SQL block to check that.

    set serveroutput on
    begin
    if
    sys.dbms_audit_mgmt.is_cleanup_initialized(sys.dbms_audit_mgmt.audit_trail_aud_std)
    then
    sys.dbms_output.put_line('aud$ is initialized for cleanup');
    else
    sys.dbms_output.put_line('aud$ is not initialized for cleanup.');
    end if;
    end;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points