某汽车集团数据库环境为linux5.8,oracle 11g rac 11.2.0.4。dblink所连接的数据库环境未知,业务系统在通过dblink进行数据更新时在出现如下错误:
ORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-02072: distributed database network protocol mismatch], [], [], [], [], [], [], [], [], [], [========= Dump for incident 264681 (ORA 600 [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-]) ========
检查相关的错误信息跟踪文件tsp1_ora_23612_i264681.trc有如下信息：
Dump continued from file: /u01/app/oracle/diag/rdbms/tsp/tsp1/trace/tsp1_ora_23612.trcORA-00600: internal error code, arguments: [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-02072: distributed database network protocol mismatch], [], [], [], [], [], [], [], [], [], [========= Dump for incident 264681 (ORA 600 [ORA-00600: internal error code, arguments: [kghstack_underflow_internal_1], [0x1103965E0], [], [], [], [], [], []ORA-]) ========
*** 2015-01-18 17:55:15.413dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)----- Current SQL Statement for this session (sql_id=43ws7jmatt2yf) -----
MERGE INTO xxx@DBLINK1 R USING (SELECT DISTINCT SUBSTR(A.VIN,LENGTH(VIN)-7,LENGTH(A.VIN)) AS VIN,A.OFFSET_LON,A.OFFSET_LAT,A.UTC,A.ADDRESS,A.MILEAGE, B.ORDERNO,C.STATUSNAME,C.STATUSFLAG FROM aaa@dblink2 A,b B,c C,d@dblink3 D WHERE LENGTH(VIN)>7 AND SUBSTR(A.VIN,LENGTH(VIN)-7,8)= B.CHASSISNUMBER AND B.CARSTATUS = C.STATUSFLAG AND A.VIN =D.LICENSEPLATE AND D.ISLOGOUT = 0 AND SUBSTR(B.ORDERNO,0,2) IN ('31','37','40') AND C.STATUSFLAG < 6 AND A.ADDRESS IS NOT NULL) D ON (R.ROW_ID='ssi'||D.VIN) WHEN MATCHED THEN UPDATE SET LONGITUDE=D.OFFSET_LON,DIMENSION=D.OFFSET_LAT,GPS_TIME = D.UTC,GPS_LOCATION=D.ADDRESS,GPS_LOCATION_SPLIT=D.ADDRESS,STATUS=D.STATUSNAME,GPS_MILEAGE=D.MILEAGE,GAUGE_MILEAGE='',CREATED_BY = 'ssi',LAST_UPD_BY='ssi',ORDER_NO=D.ORDERNO WHEN NOT MATCHED THEN INSERT (ROW_ID,CHASSIS_NO,LONGITUDE,DIMENSION,GPS_TIME,GPS_LOCATION,GPS_LOCATION_SPLIT,STATUS,GPS_MILEAGE,GAUGE_MILEAGE,CREATED_BY,LAST_UPD_BY,ORDER_NO) VALUES('ssi'||D.VIN,D.VIN,D.OFFSET_LON,D.OFFSET_LAT,D.UTC,D.ADDRESS,D.ADDRESS,D.STATUSNAME,D.MILEAGE,'','ssi','ssi',D.ORDERNO)----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
object line object
handle number name0x2d3112bc0 33 package body DFCP.DFVL_DBLINK10x2d92769c8 1 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hexlocation type point (? means dubious value)-------------------- -------- -------------------- ----------------------------
skdstdst()+41 call kgdsdst() 000000000 ? 000000000 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
ksedst1()+103 call skdstdst() 000000000 ? 000000000 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
ksedst()+39 call ksedst1() 000000000 ? 000000001 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
dbkedDefDump()+2746 call ksedst() 000000000 ? 000000001 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
ksedmp()+41 call dbkedDefDump() 000000003 ? 000000002 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
ksfdmp()+69 call ksedmp() 000000003 ? 000000002 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
dbgexPhaseII()+1764 call ksfdmp() 000000003 ? 000000002 ?
7FFFD889F8E0 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
dbgexProcessError() call dbgexPhaseII() 2B585AE35730 ? 2B585AE3E7F0 ?
+2680 7FFFD88A8F38 ? 7FFFD889F9B8 ?
7FFFD88A4460 ? 000000002 ?
dbgeExecuteForError call dbgexProcessError() 2B585AE35730 ? 2B585AE3E7F0 ?
()+88 000000001 ? 000000000 ?
7FFFD88A4460 ? 000000002 ?
dbgePostErrorKGE()+ call dbgeExecuteForError 2B585AE35730 ? 2B585AE3E7F0 ?
2136 () 000000001 ? 000000001 ?
000000000 ? 000000002 ?
dbkePostKGE_kgsf()+ call dbgePostErrorKGE() 00C0CC9E0 ? 2B585B260040 ?
71 000000258 ? 000000001 ?
000000000 ? 000000002 ?
kgesev()+280 call dbkePostKGE_kgsf() 00C0CC9E0 ? 2B585B260040 ?
000000258 ? 000000001 ?
000000000 ? 000000002 ?
ksesec1()+170 call kgesev() 00C0CC9E0 ? 2B585B260040 ?
000000258 ? 000000001 ?
7FFFD88A9AE0 ? 000000002 ?
npierr()+878 call ksesec1() 00C0CC9E0 ? 000000001 ?
0000000AC ? 2B585B49DDE0 ?
7FFFD88A9040 ? 00000000A ?
kpnerr()+240 call npierr() 2B585B49C570 ? 000000258 ?
000000000 ? 00000005E ?
7FFFD88A9040 ? 2B585B49EE90 ?
kpnpst()+276 call kpnerr() 2B585B49C570 ? 000000258 ?
000000000 ? 00000005E ?
7FFFD88ACA68 ? 2B585B49EE90 ?
upirtrc()+1919 call kpnpst() 7FFFD88ACBE0 ? 000000258 ?
000000023 ? 000000000 ?
7FFFD88ACA68 ? 2B585B49EE90 ?
kpurcsc()+98 call upirtrc() 7FFFD88ACBE0 ? 000000258 ?
000000023 ? 000000000 ?
7FFFD88ACA68 ? 2B585B4F4B10 ?
kpuexec()+10790 call kpurcsc() 7FFFD88ACBE0 ? 000000258 ?
000000023 ? 000000000 ?
7FFFD88ACA68 ? 2B585B4F4B10 ?
kpnexe()+1517 call kpuexec() 7FFFD88ACBE0 ? 000000258 ?
7FFFD88ACA60 ? 000000000 ?
7FFFD88ACA68 ? 7FFFD88B07B8 ?
opiexe()+32658 call kpnexe() 2B585B2AF000 ? 2B585B4F4B10 ?
2B585B507EB8 ? 2B585B19A950 ?
000000001 ? 1000000000 ?
opipls()+2164 call opiexe() 000000004 ? 000000005 ?
2B585B507EB8 ? 2B585B19A950 ?
000000001 ? 1000000000 ?
opiodr()+917 call opipls() 000000066 ? 000000005 ?
2B585B507EB8 ? 2B585B19A950 ?
000000001 ? 1000000000 ?
rpidrus()+211 call opiodr() 000000066 ? 000000007 ?
7FFFD88B3960 ? 2B585B19A950 ?
000000001 ? 1000000000 ?
skgmstack()+148 call rpidrus() 7FFFD88B3338 ? 000000007 ?
7FFFD88B3960 ? 2B585B19A950 ?
000000001 ? 1000000000 ?
rpiswu2()+690 call skgmstack() 7FFFD88B3310 ? 00C0CC600 ?
00000F618 ? 009698A9C ?
7FFFD88B3338 ? 1000000000 ?
rpidrv()+1327 call rpiswu2() 2C511BF60 ? 00000006D ?
2C511BFE4 ? 000000002 ?
7FFFD88B3338 ? 1000000000 ?
psddr0()+473 call rpidrv() 00000000B ? 000000066 ?
7FFFD88B3960 ? 000000038 ?
7FFFD88B3338 ? 1000000000 ?
psdnal()+457 call psddr0() 00000000B ? 000000066 ?
7FFFD88B3960 ? 000000030 ?
7FFFD88B3338 ? 1000000000 ?
pevm_EXECC()+314 call psdnal() 7FFFD88B50E0 ? 7FFFD88B52F0 ?
7FFFD88B3960 ? 2B585B2A2AE8 ?
22EC69508 ? 1000000000 ?
pfrinstr_EXECC()+80 call pevm_EXECC() 2B585B1D5CA0 ? 2B585B409640 ?
000000020 ? 2B585B2A2AE8 ?
22EC69508 ? 1000000000 ?
pfrrun_no_tool()+63 call pfrinstr_EXECC() 2B585B2A7258 ? 21D5E8DB4 ?
2B585B2A72C8 ? 2B585B2A2AE8 ?
22EC69508 ? 2B5800000020 ?
pfrrun()+627 call pfrrun_no_tool() 2B585B2A7258 ? 21D5E8DB4 ?
2B585B2A72C8 ? 2B585B2A2AE8 ?
22EC69508 ? 2B5800000020 ?
plsql_run()+649 call pfrrun() 2B585B2A7258 ? 21D5E8DB4 ?
2B585B2A72C8 ? 7FFFD88B50E0 ?
22EC69508 ? 2B5800000020 ?
peicnt()+302 call plsql_run() 2B585B2A7258 ? 21D5E8DB4 ?
2B585B2A72C8 ? 7FFFD88B50E0 ?
22EC69508 ? 2B5800000020 ?
kkxexe()+525 call peicnt() 7FFFD88B50E0 ? 2B585B2A7258 ?
2B585B2A72C8 ? 7FFFD88B50E0 ?
22EC69508 ? 000000000 ?
opiexe()+18001 call kkxexe() 2B585B2ABF80 ? 2B585B2A7258 ?
2B585B2A72C8 ? 7FFFD88B50E0 ?
22EC69508 ? 000000000 ?
kpoal8()+2118 call opiexe() 000000049 ? 000000003 ?
2B585B2A72C8 ? 7FFFD88B50E0 ?
22EC69508 ? 000000000 ?
opiodr()+917 call kpoal8() 00000005E ? 000000003 ?
2B585B2A72C8 ? 7FFFD88B50E0 ?
22EC69508 ? 000000000 ?
ttcpip()+2183 call opiodr() 00000005E ? 00000001C ?
7FFFD88B9910 ? 7FFFD88B50E0 ?
22EC69508 ? 000000000 ?
opitsk()+1710 call ttcpip() 00C0EA630 ? 00999BA90 ?
7FFFD88B9910 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
opiino()+969 call opitsk() 00C0EA638 ? 000000000 ?
7FFFD88B9910 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
opiodr()+917 call opiino() 00000003C ? 000000004 ?
7FFFD88BB108 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
opidrv()+570 call opiodr() 00000003C ? 000000004 ?
7FFFD88BB108 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
sou2o()+103 call opidrv() 00000003C ? 000000004 ?
7FFFD88BB108 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
opimai_real()+133 call sou2o() 7FFFD88BB0E0 ? 00000003C ?
000000004 ? 7FFFD88BB108 ?
7FFFD88B9368 ? 7FFFD88B990C ?
ssthrdmain()+265 call opimai_real() 000000002 ? 7FFFD88BB2D0 ?
000000004 ? 7FFFD88BB108 ?
7FFFD88B9368 ? 7FFFD88B990C ?
main()+201 call ssthrdmain() 000000002 ? 7FFFD88BB2D0 ?
000000001 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
__libc_start_main() call main() 000000002 ? 7FFFD88BB478 ?
+244 000000001 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
_start()+41 call __libc_start_main() 000A29108 ? 000000002 ?
7FFFD88BB468 ? 000000000 ?
7FFFD88B9368 ? 7FFFD88B990C ?
--------------------- Binary Stack Dump ---------------------
在执行如下语句时报错:
merge into xxx@dblink1 r
using (select distinct substr(a.vin, length(vin) - 7, length(a.vin)) as vin,
a.offset_lon,
a.offset_lat,
a.utc,
a.address,
a.mileage,
b.orderno,
c.statusname,
c.statusflag
from aaaa@dblink2 a,
b b,
c c,
dddd@dblink3 d
where length(vin) > 7
and substr(a.vin, length(vin) - 7, 8) = b.chassisnumber
and b.carstatus = c.statusflag
and a.vin = d.licenseplate
and d.islogout = 0
and substr(b.orderno, 0, 2) in ('31', '37', '40')
and c.statusflag < 6
and a.address is not null) d
on (r.row_id = 'ssi' || d.VIN)
when MATCHED then
update
set LONGITUDE = d.offset_lon,
DIMENSION = d.offset_lat,
gps_time = d.utc,
GPS_LOCATION = d.address,
GPS_LOCATION_SPLIT = d.address,
STATUS = d.statusname,
GPS_MILEAGE = d.mileage,
GAUGE_MILEAGE = '',
created_by = 'ssi',
last_upd_by = 'ssi',
order_no = d.orderno
when NOT MATCHED then
insert
(row_id,
chassis_no,
LONGITUDE,
DIMENSION,
GPS_TIME,
GPS_LOCATION,
GPS_LOCATION_SPLIT,
STATUS,
GPS_MILEAGE,
GAUGE_MILEAGE,
created_by,
last_upd_by,
order_no)
VALUES
('ssi' || d.vin,
d.vin,
d.offset_lon,
d.offset_lat,
d.utc,
d.address,
d.address,
d.statusname,
d.mileage,
'',
'ssi',
'ssi',
d.orderno)
后面经询问了解到通过dblink所连接的远端数据库版本要低，而生产数据库升级前的版本为11.2.0.1，通过dblink进行数据更新是没有问题。这种通过dblink，且数据库版本不同的情况下经常遇到这种问题。在这种情况下，一般的处理方法就是通过dblink执行最简单的select,insert,update,delete语句看是否报错，如果没有出现故障，我们就可以将使用merge语法的SQL修改成最简单的select,insert,update,delete语句来达到相同的目的。也就像有多条路到达一个目的地，其中一条路因为某些原因不能通行，就改走另一路。