运行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 |
2，6002错误
|
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方式，但是此方式需要在表上面有4的TM锁，会影响业务
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，说明问题已经得到解决
ORA-00600 6002故障处理：等您坐沙发呢！