sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oralce报错ORA-00600 6002产生原因和MOS官方解决办法

时间:2016-10-28 22:44   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库出现ORA-00600 6002报错,结合MOS分析原因为由于表与索引的数据不一致导致的。
运行utldtree.sql脚本的时,创建sequence创建ORA-00600的错误

1,数据库版本

oracleplus.net> 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

2,6002错误

oracleplus.net> oradebug setmypid
Statement processed.
oracleplus.net> oradebug event 10046 trace name context forever,level 12;
Statement processed.
oracleplus.net> 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表与索引
oracleplus.net> analyze table sys.seq$ validate structure online;

Table analyzed.

oracleplus.net> analyze table sys.seq$ validate structure cascade online;

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

oracleplus.net> 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

oracleplus.net> @dba_to_fno_bno.sql
Enter value for dba: 004002c0

FILE BLOCK
———- ———-
1 704

oracleplus.net> @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文件中,我们可以得到,这是个位图块。
下面是获取索引的信息
oracleplus.net> @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

oracleplus.net> @table_index.sql
oracleplus.net> 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#
下面是验证索引中的数据与表中的数据是否一致
oracleplus.net> 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

oracleplus.net> 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 | |Oracleoracleplus.net |
| 2 | TABLE ACCESS FULL| SEQ$ | 224 | 3 (0)| 00:00:01 |
——————————————————————-


oracleplus.net> 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 故障解决
oracleplus.net> alter index sys.I_SEQ1 rebuild online;

Index altered.

oracleplus.net> 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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oralce报错ORA-00600 6002产生原因和MOS官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1099.html

Oracle研究中心

关键词:

ORA-00600 6002解决思路

Oracle报错ORA-00600 6002

ORA-00600 6002