【bdump】bdump目录下产生大量cdmp_2014xxx和死锁信息
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【bdump】bdump目录下产生大量cdmp_2014xxx和死锁信息，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9012字，纯文字阅读大概需要13分钟。
内容图文
一、环境： 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 <
内容总结
以上是互联网集市为您收集整理的【bdump】bdump目录下产生大量cdmp_2014xxx和死锁信息全部内容，希望文章能够帮你解决【bdump】bdump目录下产生大量cdmp_2014xxx和死锁信息所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】