本來今天晚上我打算進行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 |
- 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
|
| Platforms affected |
Generic (all / most platforms affected) |
Fixed:
| This issue is fixed in |
- 10.2.0.5.8 Database Patch Set Update
- 10.2.0.5 Patch 19 on Windows Platforms
|
2）
DescriptionThis 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之參數檔案深入探究》見
