Hello everyone,
I am trying to make an automatic job that is deleting Audit files from my DB. It is not functioning and I can not figure really why.
For testing purpose it is made to delete 1 day old files
SQL> SELECT * FROM dba_audit_mgmt_config_params;
PARAMETER_NAME PARAMETER_VAL AUDIT_TRAIL
------------------------------ ------------- --------------------
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 1 OS AUDIT TRAIL
AUDIT FILE MAX AGE 30 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 XML AUDIT TRAIL
SQL> SELECT * FROM dba_audit_mgmt_last_arch_ts;
AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------------------------------------
STANDARD AUDIT TRAIL 0 07-NOV-13 12.00.00.000000 AM +00:00
FGA AUDIT TRAIL 0 07-NOV-13 12.00.00.000000 AM +00:00
SQL> select JOB_NAME, LAST_START_DATE from DBA_SCHEDULER_JOBS;
JOB_NAME LAST_START_DATE
------------------------------ ----------------------------------------------------------- ------------
AUDIT_LAST_ARCHIVE_TIME 08-NOV-13 12.00.00.936211 AM +01:00
PURGE_ALL_AUDIT_TRAILS 07-NOV-13 02.18.46.020474 PM EUROPE/VIENNA
XMLDB_NFS_CLEANUP_JOB
SM$CLEAN_AUTO_SPLIT_MERGE 08-NOV-13 12.00.00.022480 AM EUROPE/VIENNA
RSE$CLEAN_RECOVERABLE_SCRIPT 08-NOV-13 12.00.00.736277 AM EUROPE/VIENNA
FGR$AUTOPURGE_JOB
BSLN_MAINTAIN_STATS_JOB 03-NOV-13 12.00.00.860415 AM +02:00
DRA_REEVALUATE_OPEN_FAILURES 07-NOV-13 10.00.02.211893 PM EUROPE/VIENNA
HM_CREATE_OFFLINE_DICTIONARY
ORA$AUTOTASK_CLEAN 08-NOV-13 03.00.00.593848 AM EUROPE/VIENNA
FILE_WATCHER
PURGE_LOG 08-NOV-13 03.00.00.729433 AM EUROPE/VIENNA
MGMT_STATS_CONFIG_JOB 01-NOV-13 01.01.01.221689 AM +02:00
MGMT_CONFIG_JOB 07-NOV-13 10.00.02.167125 PM EUROPE/VIENNA
RLM$SCHDNEGACTION 08-NOV-13 01.21.31.029495 PM +01:00
RLM$EVTCLEANUP 08-NOV-13 02.00.36.821241 PM +02:00
SQL> SELECT job_action FROM dba_scheduler_jobs WHERE job_name = 'PURGE_ALL_AUDIT_TRAILS';
JOB_ACTION
--------------------------------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE); END;
Today is 8. November. Job ran last time on 7. November and should have deleted all audit files older than 1 day. Still there is this audit file from 5. November.
$ ls -l BODEV3_ora_30075_1.aud
-rw-r----- 1 oracle dba 1798 Nov 5 14:25 BODEV3_ora_30075_1.aud
Could I provide any additional info? Any ideas why this did not work as expected? What did I do wrong?
Kind Regards, Vladimir