今天一個(gè)同事反映說:過程執(zhí)行的很快,但是在commit的時(shí)候卡住了,
本來以為是有可能鎖表了,查看了一下沒有鎖表的事.
看了一下log里面顯示
Error stack returned to user:
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:36:44 CST 2012
Errors in file /opt/oracle/app/admin/orcl/udump/orcl_ora_4949.trc:
ORA-00603: ORACLE 服務(wù)器會(huì)話因致命錯(cuò)誤而終止
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:36:47 CST 2012
DISTRIB TRAN ORCL.28a603b4.3.47.39230
is local tran 3.47.39230 (hex=03.2f.993e)
insert pending collecting tran, scn=10741011903732 (hex=9c4.d632f4f4)
Wed Apr 18 10:36:47 CST 2012
DISTRIB TRAN ORCL.28a603b4.3.47.39230
is local tran 3.47.39230 (hex=03.2f.993e))
delete pending collecting tran, scn=10741011903732 (hex=9c4.d632f4f4)
Wed Apr 18 10:39:36 CST 2012
Errors in file /opt/oracle/app/admin/orcl/udump/orcl_ora_4998.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:39:42 CST 2012
Following on-commit snapshots not refreshed :
ZZSMS.MV_DXDR_USER_RANK3
Error 600 trapped in 2PC on transaction 8.10.43605. Cleaning up.
Error stack returned to user:
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:39:42 CST 2012
Errors in file /opt/oracle/app/admin/orcl/udump/orcl_ora_4998.trc:
ORA-00603: ORACLE 服務(wù)器會(huì)話因致命錯(cuò)誤而終止
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:39:45 CST 2012
DISTRIB TRAN ORCL.28a603b4.8.10.43605
is local tran 8.10.43605 (hex=08.0a.aa55)
insert pending collecting tran, scn=10741011905253 (hex=9c4.d632fae5)
Wed Apr 18 10:39:45 CST 2012
DISTRIB TRAN ORCL.28a603b4.8.10.43605
is local tran 8.10.43605 (hex=08.0a.aa55))
delete pending collecting tran, scn=10741011905253 (hex=9c4.d632fae5)
Wed Apr 18 10:39:57 CST 2012
Errors in file /opt/oracle/app/admin/orcl/udump/orcl_ora_5012.trc:
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:40:02 CST 2012
Following on-commit snapshots not refreshed :
ZZSMS.MV_DXDR_USER_RANK3
Error 600 trapped in 2PC on transaction 5.46.43964. Cleaning up.
Error stack returned to user:
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:40:02 CST 2012
Errors in file /opt/oracle/app/admin/orcl/udump/orcl_ora_5012.trc:
ORA-00603: ORACLE 服務(wù)器會(huì)話因致命錯(cuò)誤而終止
ORA-00600: 內(nèi)部錯(cuò)誤代碼, 參數(shù): [4080], [1], [131], [], [], [], [], []
Wed Apr 18 10:40:06 CST 2012
DISTRIB TRAN ORCL.28a603b4.5.46.43964
is local tran 5.46.43964 (hex=05.2e.abbc)
insert pending collecting tran, scn=10741011905424 (hex=9c4.d632fb90)
Wed Apr 18 10:40:06 CST 2012
DISTRIB TRAN ORCL.28a603b4.5.46.43964
is local tran 5.46.43964 (hex=05.2e.abbc))
delete pending collecting tran, scn=10741011905424 (hex=9c4.d632fb90)
錯(cuò)誤分析:
1. Following on-commit snapshots not refreshed :
ZZSMS.MV_DXDR_USER_RANK3
說明一個(gè)mv 刷新沒有完成,并且是 on commit 模式的. 物化視圖名稱:ZZSMS.MV_DXDR_USER_RANK3
2. Error 600 trapped in 2PC on transaction 30.14.44852. Cleaning up.
說明:
有分布式事務(wù)產(chǎn)生.
查詢查詢視圖: SELECT * FROM Dba_2pc_Pending;
通過數(shù)據(jù)庫可知 ZZSMS.MV_DXDR_USER_RANK3
這個(gè)物化視圖是通過 T_DXDR_USER" "T_DXDR_USER 上table 建立的 on commit 物化視圖.但是為什么物化視圖刷新失敗不知原因.
了解應(yīng)用.發(fā)現(xiàn)應(yīng)用日志錯(cuò)誤:
. ORA-00600: internal error code, arguments: [4080], [1], [131], [], [], [], [], []
查詢metalink Doc ID: Note:1385495.8
竟然是個(gè)bug , 不允許進(jìn)行分布式更新表,然后進(jìn)行on commit 進(jìn)行刷新.
4. 這就容易解決了. 使用ON DEMAND mv 或者 建立同義詞,就不需要物化視圖刷新了.(至于當(dāng)時(shí)不用同義詞,有其它的原因了.)
5. GES: Potential blocker (pid=770208) on resource TX-000D0024-00014DDC;
GES: Potential blocker (pid=667728) on resource DR-00000000-00000000;
是由于rac 環(huán)境引起的.現(xiàn)在也沒有好的解決辦法.
附錄: Doc ID: Note:1385495.8
Bug 1385495 OERI:4080 or STALE data in "ON COMMIT" snapshot if base table updated by DISTRIBUTED transaction
TIP: Click help for a detailed explanation of this page.
Bookmark Go to End
Subject: Support Description of Bug 1385495
Doc ID: Note:1385495.8 Type: PATCH
Last Revision Date: 14-AUG-2003 Status: PUBLISHED
Click here for details of sections in this note.
Bug 1385495 OERI:4080 or STALE data in "ON COMMIT" snapshot if base table updated by DISTRIBUTED transaction
This note gives a brief overview of bug 1385495.
Affects:
Product (Component) Oracle Server (RDBMS)
Range of versions believed to be affected Versions >= 8
Versions confirmed as being affected (None Specified)
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in Bug not yet fixed / confirmed as fixed
Symptoms:
Wrong Results
Internal Error may occur (ORA-600)
ORA-600 [4080]
Related To:
XA / Distributed Transactions
Replication
Description
Distributed transactions are not allowed to update
tables which have "ON COMMIT" snapshots / materialized
views on them. In Oracle9i and ORA-600 [4080] is
signalled at commit time. In Oracle8i the materialized
view is not updated and so becomes out of sync with
the master table.
Workaround:
Use ON DEMAND materialized views instead.
