MERGE语句的BUG

一套11.2.0.4数据库,alert日志中没有任何信息,但是这套RAC库在用的节点经常性地生成trc文件。如下:

-rw-r----- 1 oracle oinstall      623 3月  28 15:50 xxxxxx1_ora_23923.trm
-rw-r----- 1 oracle oinstall   145146 3月  28 15:50 xxxxxx1_ora_23923.trc
-rw-r----- 1 oracle oinstall     2881 3月  28 15:50 xxxxxx1_ora_28115.trm
-rw-r----- 1 oracle oinstall   821149 3月  28 15:50 xxxxxx1_ora_28115.trc
-rw-r----- 1 oracle oinstall    13588 3月  28 15:50 xxxxxx1_ora_9846.trm
-rw-r----- 1 oracle oinstall  4035838 3月  28 15:50 xxxxxx1_ora_9846.trc
-rw-r----- 1 oracle oinstall      455 3月  28 15:50 xxxxxx1_ora_23919.trm
-rw-r----- 1 oracle oinstall   115024 3月  28 15:50 xxxxxx1_ora_23919.trc
-rw-r----- 1 oracle oinstall     1756 3月  28 15:50 xxxxxx1_ora_9588.trm
-rw-r----- 1 oracle oinstall   497493 3月  28 15:50 xxxxxx1_ora_9588.trc
-rw-r----- 1 oracle oinstall     6939 3月  28 15:50 xxxxxx1_ora_18809.trm
-rw-r----- 1 oracle oinstall  2081393 3月  28 15:50 xxxxxx1_ora_18809.trc
-rw-r----- 1 oracle oinstall     3039 3月  28 15:50 xxxxxx1_ora_9582.trm
-rw-r----- 1 oracle oinstall   825792 3月  28 15:50 xxxxxx1_ora_9582.trc
-rw-r----- 1 oracle oinstall     8252 3月  28 15:50 xxxxxx1_ora_18803.trm
-rw-r----- 1 oracle oinstall  2398151 3月  28 15:50 xxxxxx1_ora_18803.trc
-rw-r----- 1 oracle oinstall    11579 3月  28 15:50 xxxxxx1_ora_9093.trm
-rw-r----- 1 oracle oinstall  3369253 3月  28 15:50 xxxxxx1_ora_9093.trc
-rw-r----- 1 oracle oinstall     3478 3月  28 15:50 xxxxxx1_ora_14399.trm
-rw-r----- 1 oracle oinstall  1044624 3月  28 15:50 xxxxxx1_ora_14399.trc
-rw-r----- 1 oracle oinstall    20886 3月  28 15:50 xxxxxx1_ora_19316.trm
-rw-r----- 1 oracle oinstall  6168752 3月  28 15:50 xxxxxx1_ora_19316.trc
-rw-r----- 1 oracle oinstall     4821 3月  28 15:50 xxxxxx1_ora_14393.trm
-rw-r----- 1 oracle oinstall  1385676 3月  28 15:50 xxxxxx1_ora_14393.trc
-rw-r----- 1 oracle oinstall     5315 3月  28 15:50 xxxxxx1_ora_14389.trm
-rw-r----- 1 oracle oinstall  1465506 3月  28 15:50 xxxxxx1_ora_14389.trc
-rw-r----- 1 oracle oinstall     1050 3月  28 15:50 xxxxxx1_ora_23929.trm
-rw-r----- 1 oracle oinstall   294853 3月  28 15:50 xxxxxx1_ora_23929.trc
-rw-r----- 1 oracle oinstall     1839 3月  28 15:50 xxxxxx1_ora_9586.trm
-rw-r----- 1 oracle oinstall   490238 3月  28 15:50 xxxxxx1_ora_9586.trc
-rw-r----- 1 oracle oinstall    19366 3月  28 15:50 xxxxxx1_ora_14716.trm
-rw-r----- 1 oracle oinstall  5897425 3月  28 15:50 xxxxxx1_ora_14716.trc
-rw-r----- 1 oracle oinstall      452 3月  28 15:50 xxxxxx1_ora_23921.trm
-rw-r----- 1 oracle oinstall   118544 3月  28 15:50 xxxxxx1_ora_23921.trc
-rw-r----- 1 oracle oinstall      941 3月  28 15:52 xxxxxx1_ora_25867.trm
-rw-r----- 1 oracle oinstall   259764 3月  28 15:52 xxxxxx1_ora_25867.trc
-rw-r----- 1 oracle oinstall     1316 3月  28 15:52 xxxxxx1_ora_12162.trm
-rw-r----- 1 oracle oinstall   413013 3月  28 15:52 xxxxxx1_ora_12162.trc
-rw-r----- 1 oracle oinstall      424 3月  28 15:52 xxxxxx1_ora_12168.trm
-rw-r----- 1 oracle oinstall   117299 3月  28 15:52 xxxxxx1_ora_12168.trc
-rw-r----- 1 oracle oinstall     7098 3月  28 15:52 xxxxxx1_ora_7037.trm
-rw-r----- 1 oracle oinstall  2094182 3月  28 15:52 xxxxxx1_ora_7037.trc
-rw-r----- 1 oracle oinstall     3639 3月  28 15:52 xxxxxx1_ora_7041.trm
-rw-r----- 1 oracle oinstall  1055878 3月  28 15:52 xxxxxx1_ora_7041.trc
-rw-r----- 1 oracle oinstall     2575 3月  28 15:52 xxxxxx1_ora_21201.trm
-rw-r----- 1 oracle oinstall   740685 3月  28 15:52 xxxxxx1_ora_21201.trc
-rw-r----- 1 oracle oinstall     4030 3月  28 15:52 xxxxxx1_ora_7039.trm
-rw-r----- 1 oracle oinstall  1132193 3月  28 15:52 xxxxxx1_ora_7039.trc
-rw-r----- 1 oracle oinstall    10115 3月  28 15:52 xxxxxx1_ora_30105.trm
-rw-r----- 1 oracle oinstall  3085210 3月  28 15:52 xxxxxx1_ora_30105.trc
-rw-r----- 1 oracle oinstall     3719 3月  28 15:52 xxxxxx1_ora_21199.trm
-rw-r----- 1 oracle oinstall  1215167 3月  28 15:52 xxxxxx1_ora_21199.trc
-rw-r----- 1 oracle oinstall    14802 3月  28 15:52 xxxxxx1_ora_30118.trm
-rw-r----- 1 oracle oinstall  4412461 3月  28 15:52 xxxxxx1_ora_30118.trc
-rw-r----- 1 oracle oinstall     8542 3月  28 15:52 xxxxxx1_ora_12179.trm
-rw-r----- 1 oracle oinstall  2489036 3月  28 15:52 xxxxxx1_ora_12179.trc
-rw-r----- 1 oracle oinstall     6259 3月  28 15:52 xxxxxx1_ora_7033.trm
-rw-r----- 1 oracle oinstall  1885238 3月  28 15:52 xxxxxx1_ora_7033.trc
-rw-r----- 1 oracle oinstall     1355 3月  28 15:52 xxxxxx1_ora_25865.trm
-rw-r----- 1 oracle oinstall   383824 3月  28 15:52 xxxxxx1_ora_25865.trc
-rw-r----- 1 oracle oinstall     7480 3月  28 15:52 xxxxxx1_ora_12040.trm
-rw-r----- 1 oracle oinstall  2099306 3月  28 15:52 xxxxxx1_ora_12040.trc
-rw-r----- 1 oracle oinstall     1088 3月  28 15:52 xxxxxx1_ora_25871.trm
-rw-r----- 1 oracle oinstall   266132 3月  28 15:52 xxxxxx1_ora_25871.trc
-rw-r----- 1 oracle oinstall     3842 3月  28 15:52 xxxxxx1_ora_21209.trm
-rw-r----- 1 oracle oinstall  1098983 3月  28 15:52 xxxxxx1_ora_21209.trc
-rw-r----- 1 oracle oinstall     5023 3月  28 15:52 xxxxxx1_ora_21205.trm
-rw-r----- 1 oracle oinstall  1488649 3月  28 15:52 xxxxxx1_ora_21205.trc
-rw-r----- 1 oracle oinstall     2178 3月  28 15:52 xxxxxx1_ora_12170.trm
-rw-r----- 1 oracle oinstall   645836 3月  28 15:52 xxxxxx1_ora_12170.trc
-rw-r----- 1 oracle oinstall     1393 3月  28 15:52 xxxxxx1_ora_25873.trm
-rw-r----- 1 oracle oinstall   413702 3月  28 15:52 xxxxxx1_ora_25873.trc
-rw-r----- 1 oracle oinstall     6579 3月  28 15:52 xxxxxx1_ora_12042.trm
-rw-r----- 1 oracle oinstall  2004585 3月  28 15:52 xxxxxx1_ora_12042.trc
-rw-r----- 1 oracle oinstall    11159 3月  28 15:52 xxxxxx1_ora_12183.trm
-rw-r----- 1 oracle oinstall  3302825 3月  28 15:52 xxxxxx1_ora_12183.trc
-rw-r----- 1 oracle oinstall   345827 3月  28 15:54 xxxxxx1_mmon_15163.trm
-rw-r----- 1 oracle oinstall  3238053 3月  28 15:54 xxxxxx1_mmon_15163.trc
-rw-r----- 1 oracle oinstall      522 3月  28 15:54 xxxxxx1_ora_23927.trm
-rw-r----- 1 oracle oinstall   154834 3月  28 15:54 xxxxxx1_ora_23927.trc

这套系统主要是用于监控管理,问题到不是很大。观察生成的trc文件内容,没有任何ORA-XXX、报错堆栈、SYSTEMSTATE DUMP或process dump等,只有一些数据块的转储。

$ more xxxxxx1_ora_30108.trc
Trace file /oracle/diag/rdbms/xxxxxx/xxxxxx1/trace/xxxxxx1_ora_30108.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/11.2.0/db_1
System name:    Linux
Node name:      xxxxxx01
Release:        2.6.32-279.el6.x86_64
Version:        #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: xxxxxx1
Redo thread mounted by this instance: 1
Oracle process number: 221
Unix process pid: 30108, image: oracle@xxxxxx01


*** 2017-03-28 12:27:02.369
*** SESSION ID:(7352.20037) 2017-03-28 12:27:02.369
*** CLIENT ID:() 2017-03-28 12:27:02.369
*** SERVICE NAME:(xxxxxx_xxxx) 2017-03-28 12:27:02.369
*** MODULE NAME:(JDBC Thin Client) 2017-03-28 12:27:02.369
*** ACTION NAME:() 2017-03-28 12:27:02.369
 
kduu @0x7fd89959a550
tsiz 0
hncr 6
hnkr 0
cont 0
chkd 0
colo 0
nchg 1
ichg 4
ksiz 0
tsto 0x00000000
ispa 0
size 60
nkey 0
itli 2
lcol -1
nckx 0
delt 0
wrsz 44
wrcc 6
flag CCHG FIRS HELD FIRC INIT DERR INPL
pscn 0x7fd89959a720
kcol 0x7fd89959a8e8
klen 0x7fd89959a918
dcol 0x7fd89959a8e8
dlen 0x7fd89959a918
kupdp 0x7fd89959a9d0
kupdl 0x7fd89959aa00
dupdp 0x7fd89959a9d0
dupdl 0x7fd89959aa00
kflag 0x7fd89959a928
dflag 0x7fd89959a928
kcmpp 0x7fd89959a940
kcmpl 0x7fd89959a970
kcmpf 0x7fd89959a9a0
dcmpp 0x7fd89959a940
dcmpl 0x7fd89959a970
dcmpf 0x7fd89959a9a0
kcpyp 0x7fd89959aa10
kcpyl 0x7fd89959aa30
dcpyp 0x7fd89959aa10
dcpyl 0x7fd89959aa30
tgre 0x0000
tncl 0
pnum 0
php 0x6af207368
tiddba 46223322
tidobjn 165969
tidobjd 165969
tidbsz 8168
maxf 4858
minf 807
init 2
maxt 255
rowcol 5
piececol 5
cmpf_ret_code 20
su <0x6af2077e0> {
  su->kduucmpf             : 2
  su->kduucmpx             : 512
  su->kduusdflag[rowcol] col_rowcol_flag 9
}
Compare value: 
  cmpp            : 0x7fd899579430
  cmpl            : 7
  cmpf            : 0
  Data: < 78 75 03 1c 0c 2b 0f >
Current value: 
  u->kduudcol[(piececol)]    : 0x3ae507ff5
  u->kduudlen[(piececol)]    : 7
  u->kduudflag[(piececol)]   : 0
  Data: < 78 75 03 1c 0d 17 05 >
  stid tiddef @6af207368
tidtsn 6
tidobjn 165969
tidobjd 165969
tiddba 02c14fda
tidbsz 8168
tidtbl 0
tidtyp 2
  itid tiddef @59c3ef210
tidtsn 0
tidobjn 0
tidobjd 0
tiddba 00000000
tidbsz 0
tidtbl 0
tidtyp 0
  prid 0x00000000.0000
  crid 0x01eea672.0000
  nrid 0x00000000.0000
  head fb: --H-FL-- lb: 0x2  cc: 6
 
Block header dump:  0x01eea672
 Object id on Block? Y
 seg/obj: 0x28851  csc: 0xd10.f7a34f13  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1eea601 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0019.015.0015b354  0x00d10afa.ff61.48  --U-   17  fsc 0x0000.f7a34f1a
0x02   0x003d.007.001fb31f  0x00d1cd97.4abf.3c  ----    5  fsc 0x0000.00000000
bdba: 0x01eea672
data_block_dump,data header at 0x3ae506064
===============
tsiz: 0x1f98
hsiz: 0xd6
pbl: 0x3ae506064
     76543210
flag=--------
ntab=1
nrow=98
frre=-1
fsbo=0xd6
fseo=0x18c
avsp=0x328
tosp=0x328
0xe:pti[0]      nrow=98 offs=0
0x12:pri[0]     offs=0x1f5c
0x14:pri[1]     offs=0x1f16
0x16:pri[2]     offs=0x1ecc
0x18:pri[3]     offs=0x1e82
0x1a:pri[4]     offs=0x1e42
0x1c:pri[5]     offs=0x1e0a
0x1e:pri[6]     offs=0x1dca
0x20:pri[7]     offs=0x1d8f
0x22:pri[8]     offs=0x1d4d
0x24:pri[9]     offs=0x1d11
0x26:pri[10]    offs=0x1ccb
0x28:pri[11]    offs=0x1c81
0x2a:pri[12]    offs=0x1c37
0x2c:pri[13]    offs=0x1bf7
0x2e:pri[14]    offs=0x1bbb
0x30:pri[15]    offs=0x1b75
0x32:pri[16]    offs=0x1b2b
0x34:pri[17]    offs=0x1ae1
0x36:pri[18]    offs=0x1aa1
0x38:pri[19]    offs=0x1a5c

查看相关的块转储对象,基本是属于一个对象

[oracle@xxxxxx01 trace]$ grep "seg/obj" xxxxxx1_ora_25867.trc
 seg/obj: 0x28851  csc: 0xd10.f8062cd0  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f806c41f  itc: 5  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f806c6f6  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f803cdb3  itc: 10  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f806cb9d  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f808a2c7  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f808aa5e  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f80b6022  itc: 28  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f80b72a9  itc: 6  flg: E  typ: 1 - DATA
......
[oracle@xxxxxx01 trace]$ grep "seg/obj" xxxxxx1_ora_30108.trc
 seg/obj: 0x28851  csc: 0xd10.f7a34f13  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7a5bd87  itc: 8  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7a99c08  itc: 4  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7a9f08b  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7a9f29e  itc: 7  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7aaffe7  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7ac5c8a  itc: 7  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7add0ee  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7adfc0b  itc: 6  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7ae12d1  itc: 5  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b03fae  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b036b1  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b0589f  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b0a396  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b03ac8  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b0ef51  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7b10df3  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7adf8e7  itc: 5  flg: E  typ: 1 - DATA
.....
[oracle@xxxxxx01 trace]$ grep "seg/obj" xxxxxx1_ora_21205.trc
 seg/obj: 0x28851  csc: 0xd10.f7f58301  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f25c19  itc: 5  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f5b7f2  itc: 7  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f5c249  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f555de  itc: 4  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f7f2eb  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f71324  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f82be3  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f746ce  itc: 3  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f8673a  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f89423  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f899de  itc: 2  flg: E  typ: 1 - DATA
 seg/obj: 0x28851  csc: 0xd10.f7f89a49  itc: 3  flg: E  typ: 1 - DATA
......

在MOS上搜索了下,可能和Bug 18396660 : ORA-600 [13013] DURING MERGE这个有关。到v$sqlarea里去搜索引了块转储对象涉及的SQL语句,确实基本都是merge语句。

应该可以确定是这个BUG,但是没有补丁,需要应用修改SQL语句。

关于紫砂壶

感悟技术人生
此条目发表在Oracle故障诊断分类目录,贴了标签。将固定链接加入收藏夹。