今天一個同事反映說:過程執(zhí)行的很快,但是在commit的時候卡住了,

 

本來以為是有可能鎖表了,查看了一下沒有鎖表的事.

 

看了一下log里面顯示

Error stack returned to user:
ORA-00600: 內(nèi)部錯誤代碼, 參數(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ù)器會話因致命錯誤而終止
ORA-00600: 內(nèi)部錯誤代碼, 參數(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)部錯誤代碼, 參數(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)部錯誤代碼, 參數(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ù)器會話因致命錯誤而終止
ORA-00600: 內(nèi)部錯誤代碼, 參數(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)部錯誤代碼, 參數(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)部錯誤代碼, 參數(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ù)器會話因致命錯誤而終止
ORA-00600: 內(nèi)部錯誤代碼, 參數(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)

錯誤分析:
1. Following on-commit snapshots not refreshed :
ZZSMS.MV_DXDR_USER_RANK3
  說明一個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
這個物化視圖是通過 T_DXDR_USER" "T_DXDR_USER 上table 建立的 on commit 物化視圖.但是為什么物化視圖刷新失敗不知原因.
了解應(yīng)用.發(fā)現(xiàn)應(yīng)用日志錯誤:

. ORA-00600: internal error code, arguments: [4080], [1], [131], [], [], [], [], []
  查詢metalink   Doc ID:  Note:1385495.8
  
  竟然是個bug , 不允許進行分布式更新表,然后進行on commit 進行刷新.
  
4. 這就容易解決了. 使用ON DEMAND mv 或者 建立同義詞,就不需要物化視圖刷新了.(至于當(dāng)時不用同義詞,有其它的原因了.)

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.