The DBMS_AUDIT_MGMT package provides subprograms to manage audit trail records. These subprograms enable audit administrators to manage the audit trail. In a mixed-mode environment, these audit trails comprise the database, operating system (OS), and XML audit trails. In a unified auditing environment, this comprises the unified audit trail.
-bash-4.1$ oerr ora 46269
46269, 00000, "Conflicting operation on audit files"
// *Cause: Audit files were being operated by more than one DBMS_AUDIT_MGMT
// API from different sessions. The simultaneous execution of the
// CLEAN_AUDIT_TRAIL API with itself can cause the conflict.
// *Action: Wait until the execution of one of the API's complete and try
// executing the API again.
-bash-4.1$From the database instance alert log:
Tue Oct 15 19:38:53 2019
Errors in file /u01/app/oracle/diag/rdbms/orcldb/orcldb1/trace/orcldb1_j002_247077.trc:
ORA-12012: error on auto execute of job "SYS"."STANDARD_OS_AUDIT_TRAIL_PURGE"
ORA-46269: Conflicting operation on audit files
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2447
ORA-06512: at line 1
Tue Oct 15 19:46:18 2019From the incident trace file:
Trace file /u01/app/oracle/diag/rdbms/orcldb/orcldb1/trace/orcldb1_j002_247077.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_2
System name: Linux
Node name: dbadm02.example.com
Release: 4.1.12-124.24.3.el6uek.x86_64
Version: #2 SMP Mon Jan 14 15:08:09 PST 2019
Machine: x86_64
Instance name: orcldb1
Redo thread mounted by this instance: 1
Oracle process number: 115
Unix process pid: 247077, image: oracle@dbadm02.example.com (J002)
*** 2019-10-15 19:38:53.665
*** SESSION ID:(1195.35311) 2019-10-15 19:38:53.665
*** CLIENT ID:() 2019-10-15 19:38:53.665
*** SERVICE NAME:(SYS$USERS) 2019-10-15 19:38:53.665
*** MODULE NAME:(DBMS_SCHEDULER) 2019-10-15 19:38:53.665
*** ACTION NAME:(STANDARD_OS_AUDIT_TRAIL_PURGE) 2019-10-15 19:38:53.665
Current audit management cleanup jobs list, I could see two audit clean-up jobs scheduled at same time and if first one that starts few seconds before the 2nd job, runs longer then 2nd job conflicts with the processing of first one and fails with the subjected error.
SQL> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS
JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY
---------------------------------------- -------- ---------------------------- -----------------------------------
STANDARD_OS_AUDIT_TRAIL_PURGE ENABLED OS AUDIT TRAIL FREQ=HOURLY;INTERVAL=1
CLEAN_OS_DB_AUDIT_RECORD ENABLED OS AUDIT TRAIL FREQ=HOURLY;INTERVAL=1
SQL>SQL> select OWNER,JOB_NAME,to_char(last_start_date,'DD-MM-YY HH24:MI:SS') Last_date_time, to_char(next_run_date,'DD-MM-YY HH24:MI:SS') Next_Date_Time, JOB_ACTION from DBA_SCHEDULER_JOBS
OWNER JOB_NAME LAST_DATE_TIME NEXT_DATE_TIME JOB_ACTION
------------------------------ ------------------------------ ----------------- ----------------- -------------------------------------------------------
SYS CLEAN_OS_DB_AUDIT_RECORD 16-10-19 03:38:13 16-10-19 04:38:13 BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE); END;
SYS STANDARD_OS_AUDIT_TRAIL_PURGE 16-10-19 03:38:53 16-10-19 04:38:53 BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE); END;
SQL>Scheduler details:
OWNER JOB_NAME START_DATE ENABL STATE FAILURE_COUNT LAST_START_DATE NEXT_RUN_DATE MAX_RUN_DURATIO SOURCE
--------------- ----------------------------------- ------------------------------ ----- --------------- ------------- -------------------------- ------------------------------ --------------- ---------------
SYS STANDARD_OS_AUDIT_TRAIL_PURGE 16-jan-2016 02:38:53 TRUE SCHEDULED 9731 16-oct-2019 01:38:53 16-oct-2019 02:38:53
SYS CLEAN_OS_DB_AUDIT_RECORD 19-jan-2017 15:38:13 TRUE SCHEDULED 619 16-oct-2019 01:38:13 16-oct-2019 02:38:13
SYS PURGE_LOG 10-oct-2014 03:00:00 TRUE SCHEDULED 0 15-oct-2019 03:00:00 16-oct-2019 03:00:00
SQL> select * from DBA_AUDIT_MGMT_CONFIG_PARAMS
PARAMETER_NAME PARAMETER_VALUE 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 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 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 1 OS AUDIT TRAIL
11 rows selected.
SQL>I don't want duplicate job schedules in place for the same purpose so I disabled and drop the one job schedule to fix the conflict and avoid the noise tickets.
SQL> begin
dbms_scheduler.disable( '"SYS"."CLEAN_OS_DB_AUDIT_RECORD"' );
END;
/
PL/SQL procedure successfully completed.
SQL>SQL> EXEC DBMS_AUDIT_MGMT.DROP_PURGE_JOB('CLEAN_OS_DB_AUDIT_RECORD');
PL/SQL procedure successfully completed.
SQL>Now, only one job is left in the schedule and it should work fine without any conflict in future.
SQL> select * from DBA_AUDIT_MGMT_CLEANUP_JOBS;
JOB_NAME JOB_STAT AUDIT_TRAIL JOB_FREQUENCY
---------------------------------------------------------------------------------------------------- -------- ---------------------------- ----------------------------------------------------------------------------------------------------
STANDARD_OS_AUDIT_TRAIL_PURGE ENABLED OS AUDIT TRAIL FREQ=HOURLY;INTERVAL=1
OWNER JOB_NAME LAST_DATE_TIME NEXT_DATE_TIME JOB_ACTION
------------------------------ ------------------------------ ----------------- ----------------- -------------------------------------------------------
SYS STANDARD_OS_AUDIT_TRAIL_PURGE 16-10-19 04:38:53 16-10-19 05:38:53 BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE); END;
Tested the manual job run once, and it worked fine.
SQL> BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(4, TRUE); END;
2 /
PL/SQL procedure successfully completed.
SQL>
Hope it helps, thanks for reading, please subscribe to this blog to stay updated with latest news on Oracle Cloud Infrastructure and Oracle Autonomous Database Cloud Services and new articles.
Twitter : https://twitter.com/rajsoft8899
Facebook : https://www.facebook.com/rkushawaha
No comments:
Post a Comment