当前位置: 首页 > ORA > 正文

ORA-00600 6002故障处理

运行utldtree.sql脚本的时,创建sequence创建ORA-00600的错误

 

1,数据库版本

www.htz.pw > select * from v$version where rownum<3;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

26002错误

www.htz.pw > oradebug setmypid

Statement processed.

www.htz.pw > oradebug event 10046 trace name context forever,level 12;

Statement processed.

www.htz.pw > create sequence deptree_seq cache 200;

create sequence deptree_seq cache 200

*

ERROR at line 1:

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [6002], [6], [6], [1], [0], [], [],

[], [], [], [], []

Process ID: 1469

Session ID: 37 Serial number: 241

报了ORA-00600 6002错误

3,分析方法与故障解决

3.1 alert日志文件

Sat Jul 12 08:14:36 2014

Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_1469.trc  (incident=27820):

ORA-00600: internal error code, arguments: [6002], [6], [6], [1], [0], [], [], [], [], [], [], []

Incident details in: /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/incident/incdir_27820/orcl1124_ora_1469_i27820.trc

Use ADRCI or Support Workbench to package the incident.

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

Errors in file /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_1469.trc  (incident=27821):

ORA-00603: ORACLE server session terminated by fatal error

ORA-00600: internal error code, arguments: [6002], [6], [6], [1], [0], [], [], [], [], [], [], []

Incident details in: /oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/incident/incdir_27821/orcl1124_ora_1469_i27821.trc

Sat Jul 12 08:14:37 2014

Dumping diagnostic data in directory=[cdmp_20140712081437], requested by (instance=1, osid=1469), summary=[incident=27820].

opiodr aborting process unknown ospid (1469) as a result of ORA-603

Dumping diagnostic data in directory=[cdmp_20140712081438], requested by (instance=1, osid=1469), summary=[incident=27821].

查看orcl1124_ora_1469_i27820.trc日志文件内容

*** 2014-07-12 08:14:36.296

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

—– Current SQL Statement for this session (sql_id=acd938p9jb374) —–

insert into seq$(obj#,increment$,minvalue,maxvalue,cycle#,order$,cache,highwater,audit$,flags)values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)

这里看到了报错的SQL语句,由于在insert seq$表的时候出了问题

3.2 dbv数据文件

由于数据库原来通过强制打开,怀疑是可能有坏块等其它的原因

[oracle@www.htz.pw trace]$dbv file=/oracle/app/oracle/oradata/orcl1124/system01.dbf

 

DBVERIFY: Release 11.2.0.4.0 – Production on Sat Jul 12 08:19:25 2014

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

DBVERIFY – Verification starting : FILE = /oracle/app/oracle/oradata/orcl1124/system01.dbf

 

 

DBVERIFY – Verification complete

 

Total Pages Examined         : 96000

Total Pages Processed (Data) : 63740

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 13209

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 3471

Total Pages Processed (Seg)  : 1

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 15580

Total Pages Marked Corrupt   : 0

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 3497668 (0.3497668)

这里可以看到,数据文件无坏块

3.3 analyze表与索引

www.htz.pw > analyze table sys.seq$ validate structure online;

 

Table analyzed.

 

www.htz.pw > analyze table sys.seq$ validate structure cascade online;

 

Table analyzed.

这里需要注意的是cascade online的时候并没有报错,必须使用cascade方式,但是此方式需要在表上面有4TM锁,会影响业务

 

www.htz.pw > analyze table sys.seq$ validate structure cascade;

analyze table sys.seq$ validate structure cascade

*

ERROR at line 1:

ORA-01499: table/index cross reference failure – see trace file

 

查看trace文件可以发现下面的内容

trace文件

Table/Index row count mismatch

table 224 : index 0, 0

Index root = tsn: 0 rdba: 0x004002c0

由于表与索引的数据不一致导致的

3.4 分析表与索引数据

oracle@www.htz.pw sql]$sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jul 12 08:34:29 2014

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

www.htz.pw > @dba_to_fno_bno.sql

Enter value for dba: 004002c0

 

      FILE      BLOCK

———- ———-

         1        704       

        

www.htz.pw > @dump_block.sql

Enter value for datafile: 1

Enter value for block_id: 704

 

System altered.

 

Statement processed.

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_4180.trc

这里我使用的是直接dump块,也可以通过dba_extents的方式查询,不过生产库查询dba_extents速度太慢了。

 Extent Control Header

  —————————————————————–

  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 7

                  last map  0x00000000  #maps: 0      offset: 4128

      Highwater::  0x004002c2  ext#: 0      blk#: 1      ext size: 7

  #blocks in seg. hdr’s freelists: 0

  #blocks below: 1

  mapblk  0x00000000  offset: 0

                   Unlocked

     Map Header:: next  0x00000000  #extents: 1    obj#: 79     flag: 0x40000000

  Extent Map

  —————————————————————–

   0x004002c1  length: 7

 

  nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 0

  SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000

End dump data blocks tsn: 0 file#: 1 minblk 704 maxblk 704

dump文件中,我们可以得到,这是个位图块。

下面是获取索引的信息

www.htz.pw > @object_by_id.sql

 

+————————————————————————+

| display one object type,owner,time,status                              |

+————————————————————————+

 

Enter Search Object Id (i.e. 1235) : 79

 

                                                      Object          Create               Last_Ddl

OWNER                OBJECT_NAME   SUBOBJECT_NAME     Type            Time                 Time                 STATUS

——————– ——————————– ————— ——————– ——————– ———-

SYS                  I_SEQ1                           INDEX           2013-08-24 11:37:36  2013-08-24 11:37:36  VALID

 

www.htz.pw > @table_index.sql

www.htz.pw > set echo off

+—————————————————————————-+

| DISPLAY INDEX INFO ABOUT TABLE:TABLE_NAME                                 |

+—————————————————————————-+

Enter Search Table Owner (i.e. SCOTT|ALL(DEFAULT)) : sys

Enter Search Table Name (i.e. DEPT|DEFAULT(ALL)) : seq$

Enter Search Index Name (i.e. DEPT|DEFAULT(ALL)) :

 

OWNER

TABLE_NAME                              Dinsinct                      PAR

INDEX_NAME        UNIQUENES  PCT LOG  B     Keys LEAF_BLOCKS NUM_ROWS TI  POST NAME

—————– ——— —- — — ——– ———– ——– — —- —-

SEQ$:I_SEQ1       UNIQUE      10 YES  0      224           1      224 NO     1 OBJ#

下面是验证索引中的数据与表中的数据是否一致

www.htz.pw > select count(*) from sys.seq$;

 

  COUNT(*)

———-

       224

 

 

Execution Plan

———————————————————-

Plan hash value: 3316131119

 

——————————————————————-

| Id  | Operation        | Name   | Rows  | Cost (%CPU)| Time     |

——————————————————————-

|   0 | SELECT STATEMENT |        |     1 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE  |        |     1 |            |          |

|   2 |   INDEX FULL SCAN| I_SEQ1 |   224 |     1   (0)| 00:00:01 |

——————————————————————-

 

 

Statistics

———————————————————-

          0  recursive calls

          0  db block gets

          1  consistent gets

          0  physical reads

          0  redo size

        527  bytes sent via SQL*Net to client

        523  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          1  rows processed

 

www.htz.pw > select /*+ full(a) */count(*) from sys.seq$ a;

 

  COUNT(*)

———-

       224

 

 

Execution Plan

———————————————————-

Plan hash value: 2252164700

 

——————————————————————-

| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

——————————————————————-

|   0 | SELECT STATEMENT   |      |     1 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |            |          |

|   2 |   TABLE ACCESS FULL| SEQ$ |   224 |     3   (0)| 00:00:01 |

——————————————————————-

 

 

www.htz.pw > SELECT /*+ FULL(t1) */ obj#

  2     FROM sys.seq$ t1

  3     MINUS

  4     SELECT /*+ index(t1 i_seq1) */ obj#

  5     FROM sys.seq$ t1;

 

no rows selected

 

这里没有发现数据不一致,并且之前也查询出来,通过表与索引出来的行都是一致的

Execution Plan

———————————————————-

Plan hash value: 3590734068

 

——————————————————————————

| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

——————————————————————————

|   0 | SELECT STATEMENT    |        |   224 |  2240 |     6  (34)| 00:00:01 |

|   1 |  MINUS              |        |       |       |            |          |

|   2 |   SORT UNIQUE       |        |   224 |  1120 |     4  (25)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| SEQ$   |   224 |  1120 |     3   (0)| 00:00:01 |

|   4 |   SORT UNIQUE NOSORT|        |   224 |  1120 |     2  (50)| 00:00:01 |

|   5 |    INDEX FULL SCAN  | I_SEQ1 |   224 |  1120 |     1   (0)| 00:00:01 |

——————————————————————————

 

 

Statistics

———————————————————-

          2  recursive calls

          0  db block gets

          8  consistent gets

          0  physical reads

          0  redo size

        332  bytes sent via SQL*Net to client

        512  bytes received via SQL*Net from client

          1  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

          0  rows processed

这里报的是位图块,并且数据也一致,不知道是具体是什么原因了。

3.5 故障解决

www.htz.pw > alter index sys.I_SEQ1 rebuild online;

 

Index altered.

 

www.htz.pw > create sequence deptree_seq cache 200 ;

 

Sequence created.

能正常创建sequence,说明问题已经得到解决

本文固定链接: http://www.htz.pw/2014/07/15/ora-00600-6002%e6%95%85%e9%9a%9c%e5%a4%84%e7%90%86.html | 认真就输

该日志由 huangtingzhong 于2014年07月15日发表在 ORA 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: ORA-00600 6002故障处理 | 认真就输
关键字: ,