askmaclean论坛坛友提出的问题--[ORA-600/ORA-7445] ORA-00600 [6749]的问题

最新推荐文章于 2024-10-07 11:22:30 发布

clg10051

最新推荐文章于 2024-10-07 11:22:30 发布

阅读量234

点赞数

文章标签：数据库后端

问题

http://t.askmaclean.com/thread-4332-1-1.html

[ORA-600/ORA-7445] ORA-00600 [6749]的问题，附trc

1^#

发表于前天 22:44 | 查看: 23| 回复: 0

本帖最后由 xia 于 2014-9-4 22:50 编辑

RAC 环境

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

PL/SQL Release 11.2.0.3.0 - Production

CORE    11.2.0.3.0      Production

TNS for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

下午在告警日志中看到如下信息：

Thread 1 advanced to log sequence 16646 (LGWR switch)

  Current log# 102 seq# 16646 mem# 0: +ORADATA/portaldb/redo02.log

Thu Sep 04 16:48:52 2014

Errors in file /oracle/diag/rdbms/portaldb/portaldb1/trace/portaldb1_ora_40698472.trc  (incident=642694):

ORA-00600: internal error code, arguments: [6749], [3], [37923665], [163], [], [], [], [], [], [], [], []

Incident details in: /oracle/diag/rdbms/portaldb/portaldb1/incident/incdir_642694/portaldb1_ora_40698472_i642694.trc

Thu Sep 04 16:48:56 2014

Dumping diagnostic data in directory=[cdmp_20140904164856], requested by (instance=1, osid=40698472), summary=[incident=642694].

Use ADRCI or Support Workbench to package the incident.

See Note 411.1 at My Oracle Support for error and packaging details.

Thu Sep 04 16:48:58 2014

Sweep [inc][642694]: completed

Sweep [inc2][642694]: completed

Thu Sep 04 16:53:14 2014

Thread 1 advanced to log sequence 16647 (LGWR switch)

  Current log# 103 seq# 16647 mem# 0: +ORADATA/portaldb/redo103.log

找到了portaldb1_ora_40698472.trc （大约1.9G）和portaldb1_ora_40698472_i642694.trc（11M左右）两个文件；

oracle@ptdb1:/home/oracle/awrrpt_pack$du -sm portaldb1_ora_40698472.trc

1830.01 portaldb1_ora_40698472.trc

oracle@ptdb1:/home/oracle/awrrpt_pack$du -sm portaldb1_ora_40698472_i642694.trc

11.46   portaldb1_ora_40698472_i642694.trc

portaldb1_ora_40698472.trc这个文件是去不来了我把前面重要的几行复制下来；

UMP REDO

Opcodes 11.*

DBAs (file#, block#):

(9, 174929) .

SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff

**NOTE: Only Dumping Redo less then 12 hours**

Times: 09/04/2014 04:41:29 thru eternity

Initial buffer sizes: read 1024K, overflow 832K, change 805K

Thread 1 low checkpoint scn: 0x0001.4c4c6be0

Thread 2 low checkpoint scn: 0x0001.4c4cb42f

SCN Start Scan Point: scn: 0x0001.4c4cb42f (5575062575)

Initial buffer sizes: read 1024K, overflow 832K, change 805K

Initial buffer sizes: read 1024K, overflow 832K, change 805K

INCARNATION:

  START: scn: 0x0000.00000001 (1) Timestamp:  06/01/2013 17:58:46

  END: scn: 0xffff.ffffffff

descrip:"Thread 0001, Seq# 0000016485, SCN 0x00014c4c6be0-0x00014c4f53f2"

descrip:"Thread 0002, Seq# 0000018304, SCN 0x00014c4cb42f-0x00014c4ea321"

*** 2014-09-04 16:41:30.442

*Error - Unable to open log for Thread 2 at SCN: scn: 0x0001.4c4ea321 (5575189281)

END OF DUMP REDO

Dumping current redo log in thread 1

Initial buffer sizes: read 1024K, overflow 832K, change 805K

DUMP OF REDO FROM FILE '+ORADATA/portaldb/redo02.log'

Opcodes 11.*

DBAs (file#, block#):

(9, 174929) .

RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff

SCNs: scn: 0x0001.4c4cb42f (5575062575) thru scn: 0xffff.ffffffff

Times: 09/04/2014 04:41:29 thru eternity

FILE HEADER:

        Compatibility Vsn = 186646528=0xb200000

        Db ID=663718102=0x278f88d6, Db Name='PORTALDB'

        Activation ID=663749334=0x279002d6

        Control Seq=1800459=0x1b790b, File size=1024000=0xfa000

        File Number=102, Blksiz=512, File Type=2 LOG

descrip:"Thread 0001, Seq# 0000016641, SCN 0x0001502f1901-0xffffffffffff"

thread: 1 nab: 0xffffffff seq: 0x00004101 hws: 0x1 eot: 1 dis: 0

resetlogs count: 0x30b21356 scn: 0x0000.00000001 (1)

prev resetlogs count: 0x0 scn: 0x0000.00000000

Low  scn: 0x0001.502f1901 (5640231169) 09/04/2014 16:39:02

Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

Enabled scn: 0x0000.00000001 (1) 06/01/2013 17:58:50

Thread closed scn: 0x0001.502f1901 (5640231169) 09/04/2014 16:39:02

20:05:59 sys@PORTALDB> column SEGMENT_NAME format a60

20:07:10 sys@PORTALDB> column owner format a10

20:07:38 sys@PORTALDB> column TABLESPACE_NAME format a10

20:07:49 sys@PORTALDB> SELECT tablespace_name,segment_type,owner,segment_name FROM dba_extents  wHERE file_id =9   and 174929 between block_id AND block_id + blocks - 1;

TABLESPACE SEGMENT_TYPE       OWNER      SEGMENT_NAME

---------- ------------------ ---------- ------------------------------------------------------------

NEANDS     TABLE              NEANDS3    M_PRODUCT_ALIAS           //该表为条码表

Elapsed: 00:00:04.09

20:08:07 sys@PORTALDB>  select table_name, iot_name from all_tables where table_name='M_PRODUCT_ALIAS';

TABLE_NAME           IOT_NAME

-------------------- ------------------------------

M_PRODUCT_ALIAS

21:59:18 sys@PORTALDB> select do.owner,do.object_name, do.object_type,sysind.flags

22:10:33   2       from dba_objects do, sys.ind$ sysind

22:10:33   3       where do.object_id = sysind.obj#

22:10:33   4       and bitand(sysind.flags,4096)=4096;

no rows selected

其中portaldb1_ora_40698472_i642694.trc文件中的这个语句是不是造成这个报错的原因呢？已经把trc文件和这个表给开发那边了，让开发那边也看看，

*** 2014-09-04 16:48:52.213

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=f40kjffsrvhvp) -----

UPDATE M_PRODUCT_ALIAS A SET A.IS_TOWMS = 'N' WHERE A.M_PRODUCT_ID = :B1

我还摸不着头绪，把故障现象发到这里，希望大家帮我看看，谢谢

分析

ora-600 trc文件
思路：

1, 根据MOS文章：ORA-600/ORA-7445/ORA-700 Error Look-up Tool (文档 ID 153788.1)
基本可以获取报错的一些情况，运气的话马上就会获取报错是否为BUG或其它原因
2，本案例的trc文件开头发现运行报ora-00600的sql

3，在trc文件查找plan table,找到报错sql的执行计划和绑定变量,尝试可否发现一些线索

4，在trc文件查找invocation context dump,获取报错sql出错一些线索

解决

1,根据MOS文章：ORA-600/ORA-7445/ORA-700 Error Look-up Tool (文档 ID 153788.1)
发现MOS文章：

ORA-600 [6749] "delete/update failed" (文档 ID 37955.1)

给出解决方案：

SUGGESTIONS:

  Run an ANALYZE TABLE  VALIDATE STRUCTURE CASCADE;
  for all tables involved.

  If the Known Issues section below does not help in terms of identifying
  a solution, please submit the trace files and alert.log to Oracle 
  Support Services for further analysis.

表可能存在坏块，分析表，然后尝试修复表即可

否则，请提交SR给ORACLE，以获取进一步的支持

2,打开trc文件，开头出现报错的sql,从最后2行可知，此SQL是存储过程中一部分，在调用运行时报错

========= Dump for incident 642694 (ORA 600 [6749]) ========

*** 2014-09-04 16:48:52.213

dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)

----- Current SQL Statement for this session (sql_id=f40kjffsrvhvp) -----

UPDATE M_PRODUCT_ALIAS A SET A.IS_TOWMS = 'N' WHERE A.M_PRODUCT_ID = :B1

----- PL/SQL Stack -----

----- PL/SQL Call Stack -----

object line object

handle number name

700001622caa338 91 procedure NEANDS3.M_PRODUCT_AM2

700001667610908 1 anonymous block

3,在trc文件查找invocation context dump,发现如下信息
由下可知，报错SQL表m_product_alias出现坏块

----- Invocation Context Dump -----

Address: 0x1106da990

Phase: 3

flags: 0x18E0000

Incident ID: 642694

Error Descriptor: ORA-600 [6749] [3] [37923665] [163] [] [] [] [] [] [] [] []

Error class: 0

Problem Key # of args: 1

Number of actions: 11

----- Incident Context Dump -----

Address: 0xffffffffffe5e98

Incident ID: 642694

Problem Key: ORA 600 [6749]

E rror: ORA-600 [6749] [3] [37923665] [163] [] [] [] [] [] [] [] []

[00]: dbgexProcessError [diag_dde]

[01]: dbgeExecuteForError [diag_dde]

[02]: dbgePostErrorKGE [diag_dde]

[03]: dbkePostKGE_kgsf [rdbms_dde]

[04]: kgeade []

[05]: kgeriv_int []

[06]: kgeriv []

[07]: kgesiv []

[08]: ksesic3 [KSE]

[09]: IPRA.$kdddgb []

[10]: kdddgb []

[11]: kdu_array_flush_retry []

[12]: kdu_array_flush1 []

[13]: IPRA.$kdddgb []

[14]: kdddgb []

[15]: kdusru []

[16]: kauupd []

[17]: updrow [DML]

[18]: qerupFetch [SQL_Execution]

[19]: updaul [DML]

[20]: updThreePhaseExe [DML]

[21]: updexe [DML]

[22]: opiexe []

[23]: opipls []

[24]: opiodr []

[25]: rpidrus []

[26]: skgmstack []

[27]: rpidru []

[28]: rpiswu2 []

[29]: rpidrv []

[30]: psddr0 []

[31]: psdnal []

[32]: pevm_EXECC []

[33]: pfrinstr_EXECC []

[34]: pfrrun_no_tool []

[35]: pfrrun []

[36]: plsql_run []

[37]: peicnt []

[38]: kkxexe []

[39]: opiexe []

[40]: kpoal8 []

[41]: opiodr []

[42]: ttcpip []

[43]: opitsk []

[44]: opiino []

[45]: opiodr []

[46]: opidrv []

[47]: sou2o []

[48]: opimai_real []

[49]: ssthrdmain []

MD [00]: 'SID'=' 1898.62735' (0x2)

MD [01]: 'ProcId'='336.16' (0x2)

MD [02]: 'PQ'='(16787774, 1409820054)' (0x6)

MD [03]: 'Client ProcId'='oracle@ptdb1 ( TNS V1-V3).40698472_1' (0x0)

Impact 0: POSSIBLE DATA BLOCK CORRUPTION

Impact 1: PROCESS FAILURE

4,在trc文件查找plan table,获取报错sql的绑定变量及执行计划

5,综上而言

ORA-600 [6749] "delete/update failed" (文档 ID 37955.1)

6，运行如下语句,以找出表的物理及逻辑坏块，然后针对性修复

ANALYZE TABLE m_product_alias VALIDATE STRUCTURE CASCADE;

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。

服务过的客户：

中国电信

中国移动

中国联通

中国电通

国家电网

四川达州商业银行

湖南老百姓大药房

山西省公安厅

中国邮政

北京302医院

河北廊坊新奥集团公司

项目经验:

中国电信3G项目AAA系统数据库部署及优化

中国联通CRM数据库性能优化

中国移动10086电商平台数据库部署及优化

湖南老百姓大药房ERR数据库sql优化项目

四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化

四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化

北京高铁信号监控系统RAC数据库部署及优化

河南宇通客车数据库性能优化

中国电信电商平台核心采购模块表模型设计及优化

中国邮政储蓄系统数据库性能优化及sql优化

北京302医院数据库迁移实施

河北廊坊新奥data guard部署及优化

山西公安厅身份证审计数据库系统故障评估

联系方式：

手机：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub博客名称：wisdomone1 http://blog.itpub.net/9240380/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-1265450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1265450/

clg10051

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫