AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail

今天一上班，ORACLE以错误的形式来迎接我，alert错误如下：

Wed Aug 29 01:31:03 2012
Global Enqueue Services Deadlock detected. More info in file
 /u01/app/11.1.0/diag/rdbms/zzbrac2/zzbrac21/trace/zzbrac21_lmd0_5278.trc.
Global Enqueue Services Deadlock detected. More info in file
 /u01/app/11.1.0/diag/rdbms/zzbrac2/zzbrac21/trace/zzbrac21_lmd0_5278.trc.
Wed Aug 29 01:44:49 2012
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20120829010500.ZZBRAC2','zzbrac2' SCOPE=MEMORY SID='zzbrac21';
ALTER SYSTEM SET service_names='zzbrac2' SCOPE=MEMORY SID='zzbrac21';
Wed Aug 29 01:59:35 2012
Clearing Resource Manager plan via parameter
Wed Aug 29 02:01:53 2012
Trace dumping is performing id=[cdmp_20120829020218]
Wed Aug 29 02:06:16 2012
Thread 1 advanced to log sequence 15972 (LGWR switch)
  Current log# 2 seq# 15972 mem# 0: +DATADG/zzbrac2/onlinelog/group_2.266.733863261
  Current log# 2 seq# 15972 mem# 1: +DATADG/zzbrac2/onlinelog/group_2.267.733863261
Wed Aug 29 02:06:16 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:16 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:16 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:16 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:16 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:16 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Wed Aug 29 02:06:17 2012
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail

Thread 1 advanced to log sequence 15973 (LGWR switch)
  Current log# 7 seq# 15973 mem# 0: +DATADG/zzbrac2/onlinelog/group_7.660.791891943
  Current log# 7 seq# 15973 mem# 1: +DATADG/zzbrac2/onlinelog/group_7.1693.791891943

 

问题一：Global Enqueue Services Deadlock detected

这是由于应用导致，dump文件中写的很清楚，同时对一张表进行更新导致死索，已经联系开发来查找问题。

问题二：AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
这个问题是，数据库没有在5s(默认)内提交审计记录，系统将审计信息写在了adump文件中。这个问题可能是由系统性能引起的，表示当时数据库系统的压力比较大，可以看到信息在两次日志切换中间报出来的，说明当时日志切换比较快系统压力大，这可能和11g的系统自动清除任务相关。

解决办法：增加默认提交时间或关闭审计

 

MOS上的解决办法如下：

 

Aud: Audit Commit Delay Exceeded, Written A Copy To Os Audit Trail [ID 788530.1]

--------------------------------------------------------------------------------

修改时间:2012-6-12类型:PROBLEM状态:PUBLISHED优先级:3 注释 (0)    
 

In this Document

 Symptoms
 Changes
 Cause
 Solution
 References

--------------------------------------------------------------------------------

Applies to:
Oracle Server - Enterprise Edition - Version 10.2.0.3 to 11.2.0.1 [Release 10.2 to 11.2]
Information in this document applies to any platform.
Checked for relevance on 17-Sep-2011

Symptoms
You see the following messages appear in your alert.log:

AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Changes
You have applied the Audit Cleanup Patch or any superceding patch as referenced from note 731908.1.

Cause
This is a change that was introduced within the audit functionality to support Audit Vault, these messages can appear in your alert.log occasionally even if this database is not a source of Audit Vault, the reason is as follows:

The database will guarantee that the transaction writing the audit record will commit within a pre-defined maximum allowed interval which is called the Audit Commit Delay interval. If the transaction takes more than Audit Commit Delay to commit the audit record, the Database will write the same record to the OS audit trail. This is a fallback mechanism to make sure there's always written evidence of an audited action within the defined timeframe, a such it is a feature to enhance audit security. The commit delay is fixed at 5 seconds and cannot be tuned.

Solution
The problem is happening because the audit functionality was not able to commit an audit record within 5 seconds, this means at the time the message was written to the alert.log your database was under stress. The cause of the problem is not the auditing layer and the messages seen in the alert.log are only showing that the auditing is suffering because of the generic performance problems of the environment which might affect other components as well.

These messages are purely informational and no direct action can or should be taken to avoid them. This is most likely because of a resource problem on your database. If this is seen incidental you can ignore it but if these messages are seen regularly you will likely have a resource problem and also seeing other symptoms of that, you should analyze and solve the generic performance problem first and then these messages will also go away.

Update: the fix to unpublished bug 8642202 changes the behaviour as follows:

Audit Commit Delay increased to 15 seconds and enforced only when AUD$ is initialized for cleanup.

So if you have the fix to Bug 8642202, the delay will be increased to 15 seconds and if you still get these messages and you don't want them and you are not using package DBMS_AUDIT_MGMT for cleanup, you can now disable this security feature by calling:

 

set serveroutput on
begin
   if dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(
      audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) then
      dbms_audit_mgmt.DEINIT_CLEANUP(
      audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD);
      dbms_output.put_line('DEINIT_CLEANUP for AUDIT_TRAIL_AUD_STD');
  end if;
end;
/
This may alleviate the problem in some cases but there can still be an underlying performance problem.

Bug 8642202 is fixed in patchset 10.2.0.5, PSU 11.2.0.1.1 and patchset 11.2.0.2 and future releases.

Merge patches that include this fix:

11.1.0.7: Patch 9821987

On Windows this is fixed in 11.1.0.7 patch bundle 40 and higher, see Note 161549.1 for more info.

References
@ BUG:8642202 - LX64: TOO MANY AUDIT FILES GENERATED, 500,000 AUD FILES AFTER 2 DAYS
NOTE:161549.1 - Oracle Database, Networking and Grid Agent Patches for Microsoft Platforms
NOTE:731908.1 - New Feature DBMS_AUDIT_MGMT To Manage And Purge Audit Information
NOTE:8642202.8 - Bug 8642202 - Lots of audit files due to "Audit Commit Delay exceeded"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27471208/viewspace-742124/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27471208/viewspace-742124/