【bdump】bdump目录下产生大量cdmp_2014xxx和死锁信息

一、环境： AIX 6100-07+10.2.0.4.3 RAC 二、问题描述： bdump目录下产生大量cdmp_2014xxx目录，目录的数量到达上万个直至将bdump目录所在的根目录撑满，进而数据库异常。每个cdmp_2014xxx目录大概在4M左右。 alert.log日志记录如下： Fri Mar 21 17:38:25 2

一、环境： 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); pl_ind_column VARCHAR2(30); pl_ind_name VARCHAR2(30); pl_ind_owner VARCHAR2(30); pl_index VARCHAR2(2000); f_owner VARCHAR2(30); f_table_name VARCHAR2(30); /* Cursor c1 simply selects each Foreign Key constraint from the DBA View DBA_CONSTRAINTS. No need at this stage to limit the query to 'ENABLED' constraints, we'll simply report the status in the log file. For each constraint, we'll construct the column list, using cursor c2, which combine to form the foreign key constraint returned in cursor c1 */ CURSOR c1 IS SELECT constraint_name,owner,table_name,status,r_owner,r_constraint_name FROM dba_constraints WHERE constraint_type='R' AND owner between upper(from_schema) and upper(to_schema) ORDER BY owner; CURSOR c2(cons_name VARCHAR2,cons_owner VARCHAR2) IS SELECT column_name FROM dba_cons_columns WHERE constraint_name=cons_name AND owner=cons_owner ORDER BY dba_cons_columns.position; /* For each returned constraint, we need to fins a matching index, firstly we fetch each index name with c3, and then construct the index columns with cursor c4 in their correct order until we find a match with the foreign key constraint */ CURSOR c3(ind_table varchar2,tab_owner varchar2) IS SELECT index_name, owner FROM dba_indexes WHERE table_name=ind_table AND table_owner=tab_owner; CURSOR c4(ind_name varchar2,ind_owner varchar2) IS SELECT column_name FROM dba_ind_columns WHERE INDEX_NAME=ind_name AND INDEX_OWNER=ind_owner ORDER BY dba_ind_columns.column_position; CURSOR c5(for_owner varchar2,for_constraint varchar2) IS SELECT owner,table_name FROM dba_constraints WHERE OWNER=for_owner AND CONSTRAINT_NAME=for_constraint; BEGIN WRITE_TO_TABLE_Y_N:='&&WRITE_TO_TABLE_Y_N'; from_schema:= '&&SCHEMA'; IF from_schema = 'ALL' THEN begin from_schema := 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA'; to_schema := 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ'; end; ELSE to_schema := from_schema; END IF; DBMS_OUTPUT.PUT_LINE(' Missing Indexes for Foreign Keys'); DBMS_OUTPUT.PUT_LINE(' --------------------------------'); FOR c1_rec in c1 LOOP /* looping for each foreign key constraint */ pl_cons_column := NULL; pl_foreign_key := NULL; pl_ind_column := NULL; pl_ind_name := NULL; pl_ind_owner := NULL; pl_index := NULL; f_owner:=NULL; F_table_name:=NULL; OPEN c5(c1_rec.r_owner,c1_rec.r_constraint_name); FETCH c5 INTO f_owner,f_table_name; CLOSE c5; OPEN c2(c1_rec.constraint_name,c1_rec.owner); FETCH c2 INTO pl_cons_column; pl_foreign_key := pl_cons_column; -- the first col in the foreign key > LOOP /* constructing the foreign key columns, delimiting each column with a ',' */ FETCH c2 into pl_cons_column; EXIT WHEN c2%NOTFOUND; pl_foreign_key := pl_foreign_key||','||pl_cons_column; END LOOP constraint_names; /* we now have a table and foreign key definition for which we need an index */ CLOSE c2; OPEN c3(c1_rec.table_name,c1_rec.owner); > LOOP /* for each index found for this table */ FETCH c3 INTO pl_ind_name,pl_ind_owner; EXIT WHEN c3%NOTFOUND; OPEN c4(pl_ind_name,pl_ind_owner); FETCH c4 INTO pl_ind_column; pl_index := pl_ind_column; -- the first column in the index IF pl_index=pl_foreign_key THEN -- check this doesn't already match CLOSE c4; -- the foreign key EXIT index_name; END IF; IF pl_index = SUBSTR(pl_foreign_key,1,LENGTH(pl_index)) THEN /* we only need construct the whole index while it's leading edge still matches the constrained foreign key columns */ > LOOP /* construct the whole index in the same way as the foreign key */ FETCH c4 INTO pl_ind_column; EXIT WHEN c4%NOTFOUND; pl_index:= pl_index||','||pl_ind_column; /* we do not need to continue with the index name loop if we already have a match on the foreign key */ IF pl_index=pl_foreign_key THEN CLOSE c4; EXIT index_name; END IF; /* if the leading edge differs - go back around the loop to see if there is a subsequent index that matches */ IF pl_index != SUBSTR(pl_foreign_key,1,LENGTH(pl_index)) THEN EXIT index_columns; END IF; END LOOP index_columns; END IF; CLOSE c4; END LOOP index_name; CLOSE c3; IF pl_index != pl_foreign_key OR pl_index IS NULL THEN /* Alternative means of output having first set serveroutput using: SET SERVEROUTPUT ON SIZE n where n is between 2000 and 1000000 to set the output limit. DBMS_OUTPUT.PUT_LINE(c1_rec.owner||'.'||c1_rec.constraint_name); */ IF WRITE_TO_TABLE_Y_N ='Y' or WRITE_TO_TABLE_Y_N ='y' THEN EXECUTE IMMEDIATE 'INSERT INTO foreign_key_exceptions VALUES (c1_rec.owner,c1_rec.constraint_name,c1_rec.status, c1_rec.table_name,pl_foreign_key)'; END IF; dbms_output.put_line('Constraint '||c1_rec.constraint_name||'('||c1_rec.status||') : Changing data in table '||f_owner||'.'||f_table_name||' will lock table '||c1_rec.owner||'.'||c1_rec.table_name); dbms_output.put_line('Create index for table '||c1_rec.owner||'.'||c1_rec.table_name||' on columns '||pl_foreign_key); dbms_output.put_line('************************'); COMMIT; END IF; END LOOP; END; / undefine WRITE_TO_TABLE_Y_N undefine SCHEMA 在执行脚本时，需要交互地输入1）中新建的表名，以及你想查看哪个schema下的信息如果输入ALL表示查看所有用户信息。根据得到的信息,在相应的外键上创建索引,只需要在application's schema下的对象上创建索引，系统默认用户（sys,syste,sysman）下的对象不需要创建索引。