Oracle 11g频繁重建基表导致RAC集群宕机

项目场景：

物联网项目，采用两张相同结构的数据表存储数据，实时数据写入Oracle库A表，B表用于查询。定时（1分钟）调用存储过程，从A表复制全部数据到B表。

---

问题描述

某天应用方报告无法连接数据库。DBA检查发现Oracle集群（集群承载了几十个应用）宕机。 检查集群每个节点机空间时，发现根目录的可用空间耗尽。

df -h
du -h --max-depth=1

通过du命令，最终确定占用空间最大的目录是oracle 的 trace目录，通常如下：

/u02/app/oracle/diag/rdbms/服务名称/节点名称/trace/

当时情况紧急，先删为敬。在trace目录下执行

rm -rf *.*

故障临时排除，但是由于删除alert告警文件，没能发现引起trace目录跟踪文件暴增的原因。

后续DBA日常检查中，发现集群节点机可用空间仍在快速下降中，检查oracle告警文件，发现有大量的下列提示：

Tue Jul 12 08:19:33 2022
Global Enqueue Services Deadlock detected. More info in file
 /u02/app/oracle/diag/rdbms/gcgsdb/gcgsdb2/trace/gcgsdb2_ora_19716.trc.
Tue Jul 12 08:19:34 2022
Dumping diagnostic data in directory=[cdmp_20220712081934], requested by (instance=2, osid=19716), summary=[abnormal process termination].

---

原因分析：

表面上看，19716进程被异常终结，由于频繁导出类似的诊断信息，导致磁盘可用空间快速下降，最终导致集群宕机。查看trc文件，有如下内容，提示当前进程正在执行查询视图V_B（隐去真实名称，下同）时被异常终结。

user session for deadlock lock 0x26a386a9d0
  sid: 2055 ser: 28661 audsid: 113807272 user: 84/<none>
    flags: (0x8100045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x8009) -/DDLT2/INC
  pid: 134 O/S info: user: grid, term: UNKNOWN, ospid: 19716
    image: oracle@zjydb02
  client details:
    O/S info: user: root, term: , ospid: 3180467
    machine: localhost.localdomain program: php@localhost.localdomain (TNS V1-V3)
    application name: php@localhost.localdomain (TNS V1-V3), hash value=2676332840
  current SQL:
  select  井号 as jh ,设备名称 as sbmc,参数名称 as paramname,参数值 as avalue,采集时间

as xtime  from V_B

为什么查询v_b视图会导致死锁呢，检查该视图的引用的表

select name, type, referenced_type,referenced_name	  
from user_DEPENDENCIES
where name = 'V_B';

发现其中引用的名称referenced_name居然有bin$开头的表。 

 SELECT * FROM RECYCLEBIN;

查询其原始名称为B，查询B表的相关性

select name, type, referenced_type,referenced_name	  
from USER_DEPENDENCIES
where referenced_name = 'B';

发现对应存储过程PROC_B，查看存储过程内容。采用先删除B表，再从A表创建B表并写入数据的方式。由于删除B表没有指定 purge，B表放在回收站中等待恢复。个人认为由于B表没有完全释放，视图V_B仍然引用该表，但是其在回收站内不允许访问，于是出现系统级死锁，产生大量的诊断信息日志。

drop table B;
create table B as select * from A;

---

解决方案：

找到根本原因，解决就比较简单了。用截断表替换删除表，用insert into 替换创建表。

truncate table B;
insert into B(f1,f2,f3,f4) select f1,f2,f3,f4 from a;