oracle not in一个变量,oracle问题排查.ORA-00600: internal error code, arguments: [4080], [1], [131], [], [], ...

今天一个同事反映说:过程执行的很快,但是在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.