|
今天一个同事反映说:过程执行的很快,但是在commit的时候卡住了,
本来以为是有可能锁表了,查看了一下没有锁表的事.
看了一下log里面显示
Error stack returned to user:
ORA-00600: 内部错误代码, 参数: [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 服务器会话因致命错误而终止 ORA-00600: 内部错误代码, 参数: [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: 内部错误代码, 参数: [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: 内部错误代码, 参数: [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 服务器会话因致命错误而终止 ORA-00600: 内部错误代码, 参数: [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: 内部错误代码, 参数: [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: 内部错误代码, 参数: [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 服务器会话因致命错误而终止 ORA-00600: 内部错误代码, 参数: [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. 说明: 有分布式事务产生. 查询查询视图: SELECT * FROM Dba_2pc_Pending; 通过数据库可知 ZZSMS.MV_DXDR_USER_RANK3 这个物化视图是通过 T_DXDR_USER" "T_DXDR_USER 上table 建立的 on commit 物化视图.但是为什么物化视图刷新失败不知原因. 了解应用.发现应用日志错误: . ORA-00600: internal error code, arguments: [4080], [1], [131], [], [], [], [], []
查询metalink Doc ID: Note:1385495.8 竟然是个bug , 不允许进行分布式更新表,然后进行on commit 进行刷新. 4. 这就容易解决了. 使用ON DEMAND mv 或者 建立同义词,就不需要物化视图刷新了.(至于当时不用同义词,有其它的原因了.) 5. GES: Potential blocker (pid=770208) on resource TX-000D0024-00014DDC; GES: Potential blocker (pid=667728) on resource DR-00000000-00000000; 是由于rac 环境引起的.现在也没有好的解决办法. 附录: 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. |
|
|
