Oracle索引修复 ,ORA-00600: internal error code, arguments: [6200],
问题背景:客户反馈DB每天产生的incident日志很多,需要排查原因
查看alert日志发现大量的ORA-07445、ORA-00600错误
1 Errors in file /data/oracle/diag/rdbms/bydata/bydata/trace/bydata_mmon_3667.trc (incident=111052):
2
3 ORA-00600: internal error code, arguments: [6200], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
4
5 Exception [type: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x2278EA1, kghstack_err()+85] [flags: 0x0, count: 1]
6
7 Errors in file /data/oracle/diag/rdbms/bydata/bydata/trace/bydata_mmon_3667.trc (incident=111053):
8
9 ORA-07445: exception encountered: core dump [kghstack_err()+85] [SIGSEGV] [ADDR:0x0] [PC:0x2278EA1] [SI_KERNEL(general_protection)] []
10
11 ORA-00600: internal error code, arguments: [6200], [18446744073709551615], [], [], [], [], [], [], [], [], [], []
12
13 Incident details in: /data/oracle/diag/rdbms/bydata/bydata/incident/incdir_111053/bydata_mmon_3667_i111053.trc
14
15 Mon Oct 21 09:50:30 2019
一般情况下,ORA-600被证明为oracle的内部错误,通常由数据文件的坏块或者oracle的bug引起
首先查看是否有数据文件坏块
1 [oracle@OA_oracle ~]$ dbv file =/data/oracle/oradata/bydata/SYSAUX01.DBF
2
3 DBVERIFY: Release 11.2.0.1.0 - Production on Mon Oct 21 15:12:29 2019
4
5 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
6
7
8
9 DBVERIFY - Verification starting : FILE = /data/oracle/oradata/bydata/SYSAUX01.DBF
10
11 Block Checking: DBA = 8522163, Block Type = KTB-managed data block
12
13 **** row 0: row length 35 past end of block
14
15 **** row 0: row skipped so other stats may be wrong
16
17 **** row 1: row length 7641 past end of block
18
19 **** row 1: row skipped so other stats may be wrong
20
21 **** row 2: row length 1002 past end of block
22
23 **** row 2: row skipped so other stats may be wrong
24
25 **** row 3: row length 1951 past end of block
26
27 **** row 3: row skipped so other stats may be wrong
28
29 **** row 4: row length 441 past end of block
30
31 **** actual free space = -277001 < kdxcoavs = 29
32
33 **** key (begin=0x594, len=1438) overlaps with another
34
35 begin = 0x5ae len = 1428
36
37 ---- end index block validation
38
39 Page 133555 failed with check code 6401
40
41
42
43
44
45 DBVERIFY - Verification complete
46
47
48
49 Total Pages Examined : 144640
50
51 Total Pages Processed (Data) : 47272
52
53 Total Pages Failing (Data) : 0
54
55 Total Pages Processed (Index): 39600
56
57 Total Pages Failing (Index): 1 ---需要留意
58
59 Total Pages Processed (Lob) : 9592
60
61 Total Pages Failing (Lob) : 0
62
63 Total Pages Processed (Other): 26419
64
65 Total Pages Processed (Seg) : 0
66
67 Total Pages Failing (Seg) : 0
68
69 Total Pages Empty : 21757
70
71 Total Pages Marked Corrupt : 0
72
73 Total Pages Influx : 0
74
75 Total Pages Encrypted : 0
76
77 Highest block SCN : 296083261 (0.296083261)
这次检查的有可能存在讹误索引的数据文件是sysaux,对于系统文件无法通过重建索引处理,建议客户重新恢复一个库
2> 如何在ORA-600 [6200] 报错中定位讹误的索引
描述:
在访问某张表时,你遇到ORA-600 [6200]报错,这个报错意味着相关索引被探测到存在讹误。
标准的解决方法是drop掉索引并为这张表重建所有相关索引。
不过,我们可以从trace文件当时生成的报错中定位哪个索引出的问题。
例如:
例子中显示的是从trace文件中看到的索引报错信息。
trace file报错信息:
1 ksedmp: internal or fatal error
2
3 ORA-00600: internal error code, arguments: [6200], [260], [262], [], [], [], [], []
4
5
6
7 Block header dump: dba: 0x7b404757
8
9 Object id on Block? Y
10
11 seg/obj: 0x6190 csc: 0x00.4e537b5 itc: 2 flg: -typ: 2 - INDEX
12
13 fsl: 0 fnx: 0x0
注意这里seg/obj指出的Hex值,我们可以将其转为十进制值,这个值就是对象id号。
0x6190 也就是24976 Hex = 00006190 Octal = 00000060620
这样我们就能在DBA_OBJECTS视图中找到索引对象了.
1 SVRMGR> SELECT OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS
2
3 WHERE DATA_OBJECT_ID = '24976';
4
5
6
7 DATA_OBJEC OBJECT_NAME
8
9 ---------- ------------------------------------------------------
10
11 24976 tab1_index5
这个索引就是我们应该去重建的那个。