I have documented in below article how to enable auditing . However we need to also understand how to retain and purge audit data
Ideally audit data resides in sysaix tablespace . We need to move same to other tablespace
How to enable audit :
https://abdul-hafeez-kalsekar.blogspot.com/2020/05/oracle-database-auditing.html
Below steps are will be used to execute following s for unified audit data
1) Changing Unified Audit table location
2) Changing Unified Audit table partition range
3) Changing Unified audit write mode
4) Purging Unified audit data
5) Taking backup of Unified audit data
Audit Location :
Changing location of AUD$UNIFIED
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
audit_trail_location_value => 'NEW_TSPACE');
end;
/
begin
dbms_audit_mgmt.set_audit_trail_location(
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
audit_trail_location_value => 'NEW_TSPACE');
end;
/
SQL> col owner format a10
SQL> col table_name format a15
SQL> col tablespace_name format a12
SQL> col interval format a20
SQL> set lines 200
SQL> select owner,table_name,interval,partitioning_type,partition_count,def_tablespace_name from dba_part_Tables where owner=’AUDSYS’;
Change Audit table partition range :
By default aud$unified table are monthly partitioned which we will be converting to daily .
Please note this will take effect from next month as current data is already enabled with monthly partition .
begin
dbms_audit_mgmt.alter_partition_interval ( 1 , 'DAY') ;
END ;
/
Write Mode :
Default wrote mode for unified audit is queued mode where there is chance we will loose audit data as audit data is written to memory first and then to disk
we will be changing write mode to immediate
Confirm from the DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE function.
SET SERVEROUTPUT ON
DECLARE
value NUMBER;
BEGIN
value := DBMS_AUDIT_MGMT.GET_AUDIT_TRAIL_PROPERTY_VALUE(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_property => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE);
CASE value
WHEN DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE
THEN DBMS_OUTPUT.PUT_LINE(value||':QUEUED WRITE MODE');
WHEN DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE
THEN DBMS_OUTPUT.PUT_LINE(value||':IMMEDIATE WRITE MODE');
ELSE DBMS_OUTPUT.PUT_LINE('UNKNOWN MODE');
END CASE;
END;
/
Change the write mode:
Change to queued write mode.
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_property => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,
audit_trail_property_value => DBMS_AUDIT_MGMT.AUDIT_TRAIL_QUEUED_WRITE);
END;
/
Change to immediate write mode.
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_property => DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE,
audit_trail_property_value => DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
END;
/
SELECT PARAMETER_VALUE FROM DBA_AUDIT_MGMT_CONFIG_PARAMS WHERE PARAMETER_NAME='AUDIT WRITE MODE';
Purging Unified audit data :
Purging Unified audit trail can be achieved either using last_archive_timestamp or through scheduled job
Set Purge Archive timestamp and Manually Purge Archive based on Last_archive_timestamp
Begin
dbms_audit_mgmt.set_last_archive_timestamp (
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED ,
trunc (SYSTIMESTAMP) -10 ) ;
end ;
/
Begin
dbms_audit_mgmt.clean_audit_trail (
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED ,
use_last_archive_timestamp => true ) ;
end ;
/
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
USE_LAST_ARCH_TIMESTAMP => TRUE,
CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT );
END;
/
Or
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,last_archive_time => TO_TIMESTAMP('10-SEP-0714:10:10.0','DD-MON-RRHH24:MI:SS.FF'));
END;
/
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => TRUE);
END;
/
Purging audit data by Adding Purge schedule
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_purge_interval => 24 /* hours */,
audit_trail_purge_name => 'CLEANUP_AUDIT_TRAIL_UNIFIED',
use_last_arch_timestamp => TRUE);
END;
/
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, TRUNC(SYSTIMESTAMP)-10);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time.');
END;
/
OR
begin
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'UNIFIED_AUDIT_TRAIL_PURGE' ,
JOB_TYPE => 'PLSQL_BLOCK' ,
job_action => 'begin dbms_audit_mgmt.set_last_archive_timestamp ( DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED , trunc (SYSTIMESTAMP) -10 ) ; end ;
Begin dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED , use_last_archive_timestamp => true ) ;
end ; ',
enabled => true ,
end_date => NULL,
reepat_interval => 'FREQ=DAILY ; BYHOUR=1 ; BYTIME=0 ; BYSECOND=0 ; ' ,
START_DATE => SYSTIMESTAMP ,
comment => 'UNIFIED AUDIT PURGE' ) ;
END ;
/
Taking export backup of Unified audit data
from 19c onwards .
expdp system
full=y
directory=aud_dp_dir
logfile=audexp_log.log
dumpfile=audexp_dump.dmp
version=18.02.00.02.00
INCLUDE=AUDIT_TRAILS
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/administering-the-audit-trail.html#GUID-8140CCBF-77EE-4F86-A055-B9F9AB8B4573
References :
How To Prevent The Unified Audit Trail From Being Created in SYSAUX, And Change Its Default Partitioning Behavior (Doc ID 2548804.1)
12.1 upgrade to 19c: DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION does not move AUD$UNIFIED table to a different tablespace (Doc ID 2816457.1)
How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)
How To Purge The UNIFIED AUDIT TRAIL (Doc ID 1582627.1)
How to confirm/change write mode in unified auditing (Doc ID 2520310.1)
