本来今天晚上我打算进行Oracle数据字典深入研究的，但是在我马上要研究完的时候收到在北京的一个朋友的消息，说是 他们的生成库出问题了，让我看一下帮忙解决一下，我是非常高兴的，帮助别人是我的快乐，同样也提高了自己，特此在此时此刻记录一下，写完这篇日志在继续我的研究。
trace日志信息如下：
dbfs/oradata/admin/htdb/udump/htdb_ora_704518.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0.5
System name: AIX
Node name: ECMora01
Release: 3
Version: 5
Machine: 00C05BB64C00
Instance name: htdb
Redo thread mounted by this instance: 1
Oracle process number: 206
Unix process pid: 704518, image: oracle@ECMora01
*** SERVICE NAME:(SYS$USERS) 2013-06-17 08:43:14.002
*** SESSION ID:(1473.41629) 2013-06-17 08:43:14.002
*** 2013-06-17 08:43:14.001
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktrgcm_3], [], [], [], [], [], [], []
Current SQL statement for this session:
select count(distinct id) from ( select ve.workflowid||'' as id from v_executableworktask ve left join T_DATA_ITEM appt on appt.value=ve.BUSSINESSTYPE and appt.deleted=0 and appt.cataid = 1101 where 1 = 1 and ve.globalID in ('P{2266580}','O{411001700}','O{4110}','G{201111992}') and ve.BUSINESSID in ( SELECT C.id FROM T_CONTRACT_CONTENT C WHERE INSTR(C.GLOBALSN,:1) >0 UNION SELECT E.ID FROM T_CONTRACT_CONTENT C, CUECM.T_CONTRACT_DONE E WHERE E.AFFAIRID = C.AFFAIRID AND INSTR(C.GLOBALSN,:2) >0 UNION SELECT L.RELATIONID FROM T_CONTRACT_CONTENT C, T_APPROVE_LIST L WHERE L.DENYSIGN = 0 AND L.TARGETID = C.ID AND INSTR(C.GLOBALSN,:3) >0 UNION SELECT F.id FROM t_contract_file f,T_CONTRACT_CONTENT C WHERE f.contractId = C.ID AND INSTR(C.GLOBALSN,:4) >0 UNION SELECT d.id FROM t_contract_dissension d,T_CONTRACT_CONTENT C WHERE c.id=d.contractid AND INSTR(C.GLOBALSN,:5) >0 ) union select t.pendingcode as id from ( select tab1.pendingcode, tab1.type as businessTypeId,tab2.name businessTypeName, tab1.senddate as starttime ,tab1.seandername,tab1.title from t_approve_notify tab1 left join t_data_item tab2 on tab1.type = tab2.value and tab2.cataid = 1101 and tab2.deleted = 0 where tab1.flag=0 and tab1.recieverid = :6 and TAB1.BUSSINESSID in ( SELECT C.id FROM T_CONTRACT_CONTENT C WHERE INSTR(C.GLOBALSN,:7) >0 UNION SELECT E.ID FROM T_CONTRACT_CONTENT C, CUECM.T_CONTRACT_DONE E WHERE E.AFFAIRID = C.AFFAIRID AND INSTR(C.GLOBALSN,:8) >0 UNION SELECT L.RELATIONID FROM T_CONTRACT_CONTENT C, T_APPROVE_LIST L WHERE L.DENYSIGN = 0 AND L.TARGETID = C.ID AND INSTR(C.GLOBALSN,:9) >0 UNION SELECT F.id FROM t_contract_file f,T_CONTRACT_CONTENT C WHERE f.contractId = C.ID AND INSTR(C.GLOBALSN,:10) >0 UNION SELECT d.id FROM t_contract_dissension d,T_CONTRACT_CONTENT C WHERE c.id=d.contractid AND INSTR(C.GLOBALSN,:11) >0 ) )t where 1=1 )
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c bl ksedst1 0FFFF5A20 ?
28844220058552A4 ?
ksedmp+0290 bl ksedst 104C2B3D8 ?
ksfdmp+02d8 bl 03F4D8AC
kgerinv+00dc bl _ptrgl
kgeasnmierr+004c bl kgerinv FFFFFFFFFFF6330 ? 1100096D8 ?
356A9A350C2D0000 ?
356A9B920C2D3E80 ?
356A9B920C2D3E80 ?
ktrgcm+1c44 bl kgeasnmierr 11019C288 ? 1103F0040 ?
104F34564 ? 000000000 ?
000000C2D ?
356A9A350C2D96D8 ?
000000000 ? 1100096D8 ?
ktrget+05c0 bl ktrgcm 110481450 ?
kdirfrs+09fc bl ktrget 1058540E0 ? 0000001C2 ?
09E370001 ?
qerixFetchFastFullS bl kdirfrs FFFFFFFFFFF7E50 ?
can+0958
qergiFetch+02a8 bl 03F4D2BC
rwsfcd+0054 bl _ptrgl
qerhjFetch+00d0 bl 01FC340C
rwsfcd+0054 bl _ptrgl
qeruaFetch+013c bl 03F4D2BC
qersoFetch+0110 bl 01FC340C
qervwFetch+0088 bl 03F4D2BC
rwsfcd+0054 bl _ptrgl
qerhjFetch+0674 bl 01FC340C
rwsfcd+0054 bl _ptrgl
qerhjFetch+00d0 bl 01FC340C
rwsfcd+0054 bl _ptrgl
qeruaFetch+013c bl 03F4D2BC
qersoFetch+0110 bl 01FC340C
qervwFetch+0088 bl 03F4D2BC
qergsFetch+0324 bl 03F4D2BC
kpofrws+019c bl _ptrgl
opifch2+13a4 bl 01FC633C
opifch+003c bl opifch2 700000CB426AC6C ? 000000000 ?
FFFFFFFFFFF9E40 ?
opiodr+0b2c bl _ptrgl
ttcpip+1020 bl _ptrgl
opitsk+117c bl 01FC5F7C
opiino+09d0 bl opitsk 0FFFFD8F0 ? 000000000 ?
opiodr+0b2c bl _ptrgl
opidrv+04a4 bl opiodr 3C102B1A18 ? 404C7E2A8 ?
FFFFFFFFFFFF8B0 ? 0102B1A10 ?
sou2o+0090 bl opidrv 3C02A0E6BC ? 440663000 ?
FFFFFFFFFFFF8B0 ?
opimai_real+01bc bl 01FC1F54
main+0098 bl opimai_real 000000000 ? 000000000 ?
__start+0098 bl main 000000000 ? 000000000 ?
解决如下：
1）
Bug 14076510 ORA-600 [ktrgcm_3] in 10.2.0.5.3 - 10.2.0.5.7 This note gives a brief overview of bug 14076510.
The content was last updated on: 08-MAR-2013
Click here for details of each of the sections below.
Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions >= 10.2.0.5 but BELOW 11.1 Versions confirmed as being affected
Description This problem is introduced in Database PSU version 10.2.0.5.3and can affect 10.2.0.5.3 through 10.2.0.5.7 inclusive. The problem can also occur with interim patch 6157713 installedon top of 10.2.0.5.3 through 10.2.0.5.5 inclusive. ORA-600 [ktrgcm_3] can occur in the above releases. Workaround Disabling rowCR (which is an optimization to reduce consistent-read rollbacks during queries) by setting "_row_cr"=FALSE in the initialization files in one workaround. However, this could cause performance degradation of queries - the statistics "RowCR hits" / "RowCR attempts" can help show if this workaround may be detrimental to performance. Note: This issue was previously incorrectly listed as fixed in Windows 10.2.0.5 bundle 17 but the fix did not get included until bundle 19.
因此我们可以设置_row_cr这个隐含参数。如和设置该参数可以参考我写的《oracle之参数文件深入探究》见
*** SESSION ID:(1473.41629) 2013-06-17 08:43:14.002
*** 2013-06-17 08:43:14.001
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ktrgcm_3], [], [], [], [], [], [], []
Current SQL statement for this session:
select count(distinct id) from ( select ve.workflowid||'' as id from v_executableworktask ve left join T_DATA_ITEM appt on appt.value=ve.BUSSINESSTYPE and appt.deleted=0 and appt.cataid = 1101 where 1 = 1 and ve.globalID in ('P{2266580}','O{411001700}','O{4110}','G{201111992}') and ve.BUSINESSID in ( SELECT C.id FROM T_CONTRACT_CONTENT C WHERE INSTR(C.GLOBALSN,:1) >0 UNION SELECT E.ID FROM T_CONTRACT_CONTENT C, CUECM.T_CONTRACT_DONE E WHERE E.AFFAIRID = C.AFFAIRID AND INSTR(C.GLOBALSN,:2) >0 UNION SELECT L.RELATIONID FROM T_CONTRACT_CONTENT C, T_APPROVE_LIST L WHERE L.DENYSIGN = 0 AND L.TARGETID = C.ID AND INSTR(C.GLOBALSN,:3) >0 UNION SELECT F.id FROM t_contract_file f,T_CONTRACT_CONTENT C WHERE f.contractId = C.ID AND INSTR(C.GLOBALSN,:4) >0 UNION SELECT d.id FROM t_contract_dissension d,T_CONTRACT_CONTENT C WHERE c.id=d.contractid AND INSTR(C.GLOBALSN,:5) >0 ) union select t.pendingcode as id from ( select tab1.pendingcode, tab1.type as businessTypeId,tab2.name businessTypeName, tab1.senddate as starttime ,tab1.seandername,tab1.title from t_approve_notify tab1 left join t_data_item tab2 on tab1.type = tab2.value and tab2.cataid = 1101 and tab2.deleted = 0 where tab1.flag=0 and tab1.recieverid = :6 and TAB1.BUSSINESSID in ( SELECT C.id FROM T_CONTRACT_CONTENT C WHERE INSTR(C.GLOBALSN,:7) >0 UNION SELECT E.ID FROM T_CONTRACT_CONTENT C, CUECM.T_CONTRACT_DONE E WHERE E.AFFAIRID = C.AFFAIRID AND INSTR(C.GLOBALSN,:8) >0 UNION SELECT L.RELATIONID FROM T_CONTRACT_CONTENT C, T_APPROVE_LIST L WHERE L.DENYSIGN = 0 AND L.TARGETID = C.ID AND INSTR(C.GLOBALSN,:9) >0 UNION SELECT F.id FROM t_contract_file f,T_CONTRACT_CONTENT C WHERE f.contractId = C.ID AND INSTR(C.GLOBALSN,:10) >0 UNION SELECT d.id FROM t_contract_dissension d,T_CONTRACT_CONTENT C WHERE c.id=d.contractid AND INSTR(C.GLOBALSN,:11) >0 ) )t where 1=1 )
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c bl ksedst1 0FFFF5A20 ?
28844220058552A4 ?
ksedmp+0290 bl ksedst 104C2B3D8 ?
ksfdmp+02d8 bl 03F4D8AC
kgerinv+00dc bl _ptrgl
kgeasnmierr+004c bl kgerinv FFFFFFFFFFF6330 ? 1100096D8 ?
356A9A350C2D0000 ?
356A9B920C2D3E80 ?
356A9B920C2D3E80 ?
ktrgcm+1c44 bl kgeasnmierr 11019C288 ? 1103F0040 ?
104F34564 ? 000000000 ?
000000C2D ?
356A9A350C2D96D8 ?
000000000 ? 1100096D8 ?
ktrget+05c0 bl ktrgcm 110481450 ?
kdirfrs+09fc bl ktrget 1058540E0 ? 0000001C2 ?
09E370001 ?
qerixFetchFastFullS bl kdirfrs FFFFFFFFFFF7E50 ?
can+0958
qergiFetch+02a8 bl 03F4D2BC
rwsfcd+0054 bl _ptrgl
qerhjFetch+00d0 bl 01FC340C
rwsfcd+0054 bl _ptrgl
qeruaFetch+013c bl 03F4D2BC
qersoFetch+0110 bl 01FC340C
qervwFetch+0088 bl 03F4D2BC
rwsfcd+0054 bl _ptrgl
qerhjFetch+0674 bl 01FC340C
rwsfcd+0054 bl _ptrgl
qerhjFetch+00d0 bl 01FC340C
rwsfcd+0054 bl _ptrgl
qeruaFetch+013c bl 03F4D2BC
qersoFetch+0110 bl 01FC340C
qervwFetch+0088 bl 03F4D2BC
qergsFetch+0324 bl 03F4D2BC
kpofrws+019c bl _ptrgl
opifch2+13a4 bl 01FC633C
opifch+003c bl opifch2 700000CB426AC6C ? 000000000 ?
FFFFFFFFFFF9E40 ?
opiodr+0b2c bl _ptrgl
ttcpip+1020 bl _ptrgl
opitsk+117c bl 01FC5F7C
opiino+09d0 bl opitsk 0FFFFD8F0 ? 000000000 ?
opiodr+0b2c bl _ptrgl
opidrv+04a4 bl opiodr 3C102B1A18 ? 404C7E2A8 ?
FFFFFFFFFFFF8B0 ? 0102B1A10 ?
sou2o+0090 bl opidrv 3C02A0E6BC ? 440663000 ?
FFFFFFFFFFFF8B0 ?
opimai_real+01bc bl 01FC1F54
main+0098 bl opimai_real 000000000 ? 000000000 ?
__start+0098 bl main 000000000 ? 000000000 ?
解决如下：
1）
Bug 14076510 ORA-600 [ktrgcm_3] in 10.2.0.5.3 - 10.2.0.5.7 This note gives a brief overview of bug 14076510.
The content was last updated on: 08-MAR-2013
Click here for details of each of the sections below.
Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions >= 10.2.0.5 but BELOW 11.1 Versions confirmed as being affected
- 10.2.0.5.7 Database Patch Set Update
- 10.2.0.5.6 Database Patch Set Update
- 10.2.0.5.5 Database Patch Set Update
- 10.2.0.5.4 Database Patch Set Update
- 10.2.0.5.3 Database Patch Set Update
- 10.2.0.5
- 10.2.0.5 Patch 18 on Windows Platforms
- 10.2.0.5 Patch 17 on Windows Platforms
- 10.2.0.5 Patch 8 on Windows Platforms
Description This problem is introduced in Database PSU version 10.2.0.5.3and can affect 10.2.0.5.3 through 10.2.0.5.7 inclusive. The problem can also occur with interim patch 6157713 installedon top of 10.2.0.5.3 through 10.2.0.5.5 inclusive. ORA-600 [ktrgcm_3] can occur in the above releases. Workaround Disabling rowCR (which is an optimization to reduce consistent-read rollbacks during queries) by setting "_row_cr"=FALSE in the initialization files in one workaround. However, this could cause performance degradation of queries - the statistics "RowCR hits" / "RowCR attempts" can help show if this workaround may be detrimental to performance. Note: This issue was previously incorrectly listed as fixed in Windows 10.2.0.5 bundle 17 but the fix did not get included until bundle 19.
因此我们可以设置_row_cr这个隐含参数。如和设置该参数可以参考我写的《oracle之参数文件深入探究》见