一、环境： AIX 6100-07+10.2.0.4.3 RAC
二、问题描述： bdump目录下产生大量cdmp_2014xxx目录，目录的数量到达上万个直至将bdump目录所在的根目录撑满，进而
数据库异常。每个cdmp_2014xxx目录大概在4M左右。
alert.log日志记录如下：
Fri Mar 21 17:38:25 2014
Thread 1 advanced to log sequence 17162 (LGWR switch)
Current log# 2 seq# 17162 mem# 0: /dev/rredo1_2a_256m
Current log# 2 seq# 17162 mem# 1: /dev/rredo1_2b_256m
Fri Mar 21 17:41:00 2014
Trace dumping is performing id=[cdmp_20140321173953]
Fri Mar 21 17:41:18 2014
Trace dumping is performing id=[cdmp_20140321174010]
Fri Mar 21 17:41:34 2014
Trace dumping is performing id=[cdmp_20140321174027]
Fri Mar 21 17:41:52 2014
Trace dumping is performing id=[cdmp_20140321174044]
Fri Mar 21 17:42:08 2014
Trace dumping is performing id=[cdmp_20140321174101]
Fri Mar 21 17:43:00 2014
Trace dumping is performing id=[cdmp_20140321174153]
Fri Mar 21 17:43:17 2014
Trace dumping is performing id=[cdmp_20140321174209]
Fri Mar 21 17:43:33 2014
Trace dumping is performing id=[cdmp_20140321174226]
Fri Mar 21 17:43:49 2014
Trace dumping is performing id=[cdmp_20140321174243]
Fri Mar 21 17:44:06 2014
Trace dumping is performing id=[cdmp_20140321174258]
Fri Mar 21 17:44:26 2014
Thread 1 advanced to log sequence 17163 (LGWR switch)
Current log# 3 seq# 17163 mem# 0: /dev/rredo1_3a_256m
Current log# 3 seq# 17163 mem# 1: /dev/rredo1_3b_256m
Fri Mar 21 17:45:00 2014
Trace dumping is performing id=[cdmp_20140321174353]
Fri Mar 21 17:45:17 2014
Trace dumping is performing id=[cdmp_20140321174410]
Fri Mar 21 17:45:35 2014
Trace dumping is performing id=[cdmp_20140321174427]
Fri Mar 21 17:45:52 2014
Trace dumping is performing id=[cdmp_20140321174444] 同时在alert.log中不定期的产生死锁的信息： Global Enqueue Service Deadlock detected. More info in file /oracle/admin/ballontt/udump/ballontt1_ora_6095180.trc
三、问题解决 1. 参数设置 1）在bdump目录下产生大量日志时，首先应考虑是否开启了event。可以查看参数event show parameter event 2）如果开启了event，可以利用如下脚本查询event level set serveroutput on
declare
event_level number;
begin
for i in 10000..10999 loop
dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
to_char(event_level));
end if;
end loop;
end;
/ 在我的环境中，并没有开启任何event。所以排除这个原因。
2. BUG造成 The issue matching to the following bug which is closed base bug 5470095. This is resolved in 10.2.0.4. Looks like your version is also 10.2.0.4.
++Bug 5388252 : TRACE DUMPING IS PERFORMING ID=[CDMP_ ... MESSAGES IN ALERT LOG 该BUG已经在10.2.0.4中被修复，我的数据库版本为10.2.0.4.3所以排除这个原因。
3. 外键上没有索引在二、中描述了alert.log中存在大量Global Enqueue Service Deadlock detected.，这也是可能产生cdmp的一个原因。而频繁的出现死锁，很可能的一个原因就是因为大量外键上没有创建索引，导致主表更新时外键更新的表需要被锁。可以通过如下脚本查询没有索引的外键信息。
外键上索引和锁的关系：http://blog.csdn.net/ballontt/article/details/22157759
1）创建存放相关信息的表 CREATE TABLE foreign_key_exceptions (owner VARCHAR2(30), constraint_name VARCHAR2(30), status VARCHAR2(8), table_name VARCHAR2(30), foreign_key VARCHAR2(2000));
2）执行如下脚本 set heading off select 'Write output to table FOREIGN_KEY_EXCEPTIONS created in this schema Y/N:' from dual; select upper(nvl('&&WRITE_TO_TABLE_Y_N','N')) from dual; select 'Schema Name:',upper('&&SCHEMA') from dual; set echo off SET SERVEROUTPUT ON FORMAT WRAPPED declare WRITE_TO_TABLE_Y_N VARCHAR2(100); from_schema VARCHAR2(30); to_schema VARCHAR2(30); pl_cons_column VARCHAR2(30); pl_foreign_key VARCHAR2(2000); p |