5 Replies Latest reply: Aug 25, 2012 5:52 AM by SanjayPatel RSS

    Purging Audit records

    jdfjsu99
      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
          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
            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
              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
                415289
                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
                  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;