0

At the end of each financial year, audit team asks for audit records for samples dates in a year as per their methodology and ideally we are supposed to provide them. (Say, give them list of schemas dropped in a particular month or modifications made for a schema in a month)

Now, im in a situation I need to save the audit data for one complete year, is there anyway to purge the audit data on a monthly basis and at the same time provide the necessary info by the end of the year. The reason I need to purge is because of the audit file size which grows to a huge size.

What choices do I have? I do not have license to Oracle Audit Vault as well.

  • Have a look for this work around dba.stackexchange.com/questions/57752/… – sayantan chakraborty Apr 23 '16 at 17:33
  • you don't need audit vault. You just need to take a look at the features provided by the package dbms_audit_mgmt. If you don't have the disk space to keep a years worth of data in the databases audit table (sys.aud$) then where are you going to keep it outside of the database? – EdStevens Apr 24 '16 at 13:14
1

Archiving the Database Audit Trail

Scheduling an Automatic Purge Job for the Audit Trail

Manually Purging the Audit Trail

Basically you copy the audit trail to another table, e.g:

INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...; 

You can just export and drop these tables, then import them when you need to access audit records.

Then you set the last archive timestamp, e.g:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME    =>  '2009-05-28 06:30:00.00'   
   RAC_INSTANCE_NUMBER  =>  0 );
END;
/

Finally you initialize cleanup and schedule a purge or run it manually:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL    => 12 );
END;
/

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, 
   AUDIT_TRAIL_PURGE_INTERVAL  => 12,
   AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_PJ',
   USE_LAST_ARCH_TIMESTAMP     => TRUE );
END;
/

Manual:

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE );
END;
/
|improve this answer|||||
  • Is it mandatory to use dbms job to purge? A simple remove directly from the file system won't do the job? – tesla747 Apr 24 '16 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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