Category: Audit

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS): ORA-46264 ORA-06512

Subscribe to continue reading

Become a paid subscriber to get access to the rest of this post and other exclusive content.

Unable To Create Or Drop Purge Job Using DBMS_AUDIT_MGMT

Symptoms

When attempting to create a new purge job,
the following error occurs.

ERROR
———————–
ORA-46252: Cannot complete the operation, overlapping job already exists
ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 61
ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 2595
ORA-06512: at line 2


STEPS
———————–
The issue can be reproduced at will with the following steps:
1. sqlplus / as sysdba

2. BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  AUDIT_TRAIL_PURGE_INTERVAL => 24,
  AUDIT_TRAIL_PURGE_NAME => ‘PURGE_ALL_AUDIT_TRAILS’,
  USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Cause

DBA_SCHEDULER_JOB does not have the information about the audit_mgmt job.
 
This scenario happens when DBMS_SCHEDULER and DBMS_AUDIT_MGMT are not Synchronous .
 

Solution

SQL> DELETE FROM SYS.DAM_CLEANUP_JOBS$ where JOB_NAME = ‘STANDARD_OS_AUDIT_TRAIL_PURGE’;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => ‘PURGE_ALL_AUDIT_TRAILS’,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

PL/SQL procedure successfully completed.
 

References

NOTE:1252235.1 – Unable To Create Or Drop Purge Job Using DBMS_AUDIT_MGMT

Standby Database:  *.bin files are observed in $ORACLE_BASE/audit/$ORACLE_SID/

Symptoms

There are many *.bin files are observed in $ORACLE_BASE/audit/$ORACLE_SID.

For example:

$ ls -ltr
total 666644
-rw-------. 1 oracle oinstall 10240512 Sep 19 15:09 ora_audit_1212246579_3985105844.bin
-rw-------. 1 oracle oinstall 10241536 Sep 20 06:52 ora_audit_1212303144_3688912851.bin
-rw-------. 1 oracle oinstall    20480 Sep 20 12:02 ora_audit_1212321763_3431476539.bin
-rw-------. 1 oracle oinstall    12288 Sep 20 12:35 ora_audit_1212323740_3065638769.bin
-rw-------. 1 oracle oinstall 10241024 Sep 20 22:35 ora_audit_1212359756_874733345.bin
-rw-------. 1 oracle oinstall  1126400 Sep 21 03:40 ora_audit_1212378045_893687335.bin
-rw-------. 1 oracle oinstall 10241024 Sep 21 06:30 ora_audit_1212388214_2015242572.bin
-rw-------. 1 oracle oinstall 10240512 Sep 21 14:19 ora_audit_1212416379_363470844.bin
-rw-------. 1 oracle oinstall 10241536 Sep 22 06:02 ora_audit_1212472944_1767466054.bin
-rw-------. 1 oracle oinstall 10241536 Sep 22 21:45 ora_audit_1212529539_1413139791.bin
-rw-------. 1 oracle oinstall 10241024 Sep 23 13:28 ora_audit_1212586104_1758745070.bin
...
..
.

CAUSE

The *.bin files are Unified Auditing OS “spillover” files.  These are generated when the audit trail records cannot be written to the database.  For example, when the database is not writable (such as during database mounts), if the database is closed, or if it is read-only, then Oracle Database writes the audit records to external files in the $ORACLE_BASE/audit/$ORACLE_SID directory.
 
This is expected behavior on 12c onwards when the DB is configured for Unified Auditing.

SOLUTION

If Unified Auditing is not necessary, disabling the default Unified Auditing policies will cause the *.bin files to stop being generated.  
 
Refer to:  The UNIFIED_AUDIT_TRAIL is Getting Populated even if Unified Auditing was not explicitly enabled in 12c (Doc ID 1624051.1)

If these policies are re-enabled, one will need to periodically maintain $ORACLE_BASE/audit/$ORACLE_SID.

If one does not require the audit data in the *.bin files, these files can be safely deleted.