Tuesday, February 4, 2020

How to change configuration for your audit trail


This post is applicable from Oracle version 11.2 until present (Oracle 19 as per writing).

Check the current settings:
SELECT * 
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
ORDER BY AUDIT_TRAIL;

"PARAMETER_NAME" "PARAMETER_VALUE" "AUDIT_TRAIL"
DB AUDIT TABLESPACE AUDDATA FGA AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 1 STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE AUDDATA STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE AUDDATA UNIFIED AUDIT TRAIL
AUDIT WRITE MODE IMMEDIATE WRITE MODE UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL


I will now change the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE for the XML AUDIT TRAIL from the default 5 days to 2 days.
This is the maximum age of an audit trail file before a new audit trail file gets created:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   audit_trail_property_value => 2
) ;
END;
/

Verify that it was set successfully:
SELECT * 
 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS 
 WHERE AUDIT_TRAIL='XML AUDIT TRAIL'
 AND PARAMETER_NAME='AUDIT FILE MAX AGE';

"PARAMETER_NAME" "PARAMETER_VALUE" "AUDIT_TRAIL"
AUDIT FILE MAX AGE 2 XML AUDIT TRAIL

For more examples, check the Oracle Documentation

No comments:

Post a Comment