投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

记一次 ORA-600 [12700] 故障案例

• 数据库 • 阅读 64

这篇记录是以前做数据库巡检项目时遇到的，今天记录到博客上当作经验分享。

09年12月31日下午3：30左右，在对 myschema 用户做EXP导出时有ORA-00600报错，并会生成一跟踪报错文件，随后每次导出均有此错误产生，详细如下：

Exp 脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
exp [system/system@txczyy](mailto:system/system@txczyy) owner=myschema statistics=Y rows=N file='/arch/tf/1.dmp' log='/arch/tf/exp.log'
exp.log  
. . exporting table WF_PASS  
. . exporting table WF_STATE  
. . exporting table WF_STATE_VALUE  
. . exporting table WF_TASK_EXECUTOR  
. . exporting table WF_TASK_GRANTER  
. . exporting table WF_TASK_TERM  
. . exporting table WF_TEMPLATE  
. . exporting table WF_VARIABLE  
. . exporting table WF_VARIABLE_VALUE  
. exporting synonyms  
. exporting view  
EXP-00008: ORACLE error 600 encountered  
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26], [0], [24], [], []  
EXP-00056: ORACLE error 1403 encountered  
ORA-01403: no data found  
EXP-00000: Export terminated unsuccessfully
alert.log  
Wed Jan 6 22:02:01 2010  
Errors in file /oracle/app/admin/txczyy/udump/txczyy_ora_917632.trc:  
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26], [0], [24], [], []  
Wed Jan 6 22:02:02 2010  
Errors in file /oracle/app/admin/txczyy/udump/txczyy_ora_1249630.trc:  
ORA-00600: 内部错误代码，参数: [12700], [2], [4206885], [26], [0], [24], [], []  
Wed Jan 6 22:07:34 2010
trace file  
ksedmp: internal or fatal error  
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26], [0], [24], [], []  
Current SQL statement for this session:  
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID, COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM, FSPRECISION, LFPRECISION, CHARLEN, TFLAGS FROM SYS.EXU8COL WHERE TOBJID = :1 ORDER BY INTCOLID  
----- Call Stack Trace -----

1.开SR 3-1284405961 METALINK上分析trc发现在查询对象46907时出错,笔记如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
 Generic Note  
------------  
 Hi,
Trace file shows value of the bind variable as:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=00 oacfl2=0 size=24 offset=0  
bfp=1104bb330 bln=22 avl=04 flg=05  
value=46907  
End of cursor dump  
* dump of cursor xsc=1104dbdd8 
Action Plan  
=========  
Please check the value of the following query:
SELECT status, object_id, object_type, owner||'.'||object_name "OWNER.OBJECT"  
FROM dba_objects WHERE status != 'VALID' or object_id=46907 ORDER BY 4,2;
Regards  
Anudeep  
GCS

2 既然ORACLE分析绑定变量46907有问题,而出错的sql里也有个绑定变量,所以尝试将46907代到以下sql执行试试 ,经查ID为46907的对象，是myschema用户下的视图： myschema.v_cp_av_data,
执行以下sql(将TOBJID值改成46907 ,trace文件里出错的sql)

1
2
3
4
 
SELECT TOWNER, TNAME, NAME, LENGTH, PRECISION, SCALE, TYPE, ISNULL, CONNAME, COLID, INTCOLID, SEGCOLID,  
COMMENT$, DEFAULT$, DFLTLEN, ENABLED, DEFER, FLAGS, COLPROP, ADTNAME, ADTOWNER, CHARSETID, CHARSETFORM,  
FSPRECISION, LFPRECISION, CHARLEN, TFLAGS FROM SYS.EXU8COL  
WHERE TOBJID = 46907 ORDER BY INTCOLID

查询此视图,抛出以下错误：

1
 
ORA-00600: 内部错误代码，参数: [12700], [2], [4206885], [26], [0], [24], [], []

说明,问题就出现在这个视图上,当Oracle在查询SYS.EXU8COL时出错,发现SYS.exu8col是个视图

3.即然这张view有问题,于是尝试重建视图 myschema.v_cp_av_data，看是否有效果

1
2
3
4
5
6
 
16:19:10 [SYS@txczyy](mailto:SYS@txczyy)> drop view myschema.v_cp_av_data;  
drop view myschema.v_cp_av_data  
*  
第 1 行出现错误:  
ORA-00604: 递归 SQL 层 1 出现错误  
ORA-08102: 未找到索引关键字，obj# 46，dba 4249996 (2)

4.查看obj# 46对象为col$表的i_col2索引

5.重建view行不通,于是,打算重建view引用相关表上的索引 即TABLE:myschema.CP_VOUCHER,依然行不通

6.在经过以上操作行不通后,于是怀疑系统表col$有问题,

6.1 select * from col$ 正常

1
2
3
4
5
6
7
8
9
10
11
 
6.2 31:30 [SYS@txczyy](mailto:SYS@txczyy)> select obj#,col# from col$ where obj#=46907;
 OBJ# COL#  
---------- ----------  
 46907 1  
 46907 2  
 46907 3  
 46907 4  
 46907 5  
 46907 6  
 46907 7
已选择7行。

6.3 select /*+ index(c i_col2) */ * from col$ c where obj#=46907;

1
2
3
 
ERROR:  
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26],  
[4249996], [25], [], []

说明,查询走索引 i_col2时,ORACLE正好报这个600错误,问题就是col$表 和 i_col2索引的交叉引用有问题，

1
2
 
ORA-600 [12700]是索引项中的ROWID指向的数据没有，  
ORA-8102 是表中的某条数据没有对应的索引项。

7 尝试重建系系统表col$上的索引i_col2

1
2
3
4
5
 
SQL> drop index i_col2;  
drop index i_col2  
*  
ERROR at line 1:  
ORA-00701: object necessary for warmstarting database cannot be altered

8 metalink回复

1
2
3
4
5
6
7
8
9
10
11
 
 Generic Note  
------------  
Hi,
I am Balaji and I Have reviewed the SR as per your request.
Looking into the SR I understand that , the index I_COL2 which is created on the table COL$ is corrupted.
Unfortunately , the object is a bootstrap segment , which cannot be modified using the sql scrpts.
The best option here is to perform the restore / recovery from the last good backup .
In case this is not possible then the only option way ahead is to perform an export of the entire database and import it.
You can go for schema level export.
Regards  
Balaji

由于故障根源为系统表col$的rowid 和其上的 i_col2索引标识的rowid不匹配，而引起“数据块的”损坏，要清出此“坏块”必须重建此索引，但由于此表为系统表，如对其做重建操作，很可能发生意想不到的事故，故推荐用以下方案来解决此问题。
方案1.重新建立一数据库，然后将当前故障库中有用数据导入新建库中，而将此故障库保留并试图对索引做重建操作来解决。
方案2.停机并将故障库数据完全拷贝至其它相应服务器恢复测试。

9 方案比较

方案1:
保守方案,风险小,速度快（目前数据量较小）; 历时短

方案2:
需要对Oracle系统表操作,首先在测试库上执行,风险评估可行方在生产库上执行,历时长;

10 最后采用方案一新建DB,并导入TXCZ10用户的数据,问题解决

附件一 view myschema.v_cp_av_data object_id=46907

1
2
3
 
create or replace view myschema.v_cp_av_data as  
select cp.org_code,cp.nd,cp.process_inst_id,cp.co_code, cp.org_money, cp.cur_money, cp.fund_code from cp_voucher cp  
where cp.is_sum_cp_voucher='0' and cp.control_type='1' and cp.cp_adjust_code='101' and cp.is_valid='1' and cp.a_status_code='2';

附件二 关于 ORA-600 [12700] [a] [b] [c]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
 
ORA-00600: internal error code, arguments: [12700], [2], [4206885], [26]
Note: For additional ORA-600 related information please read Note:146580.1
PURPOSE:  
 This article discusses the internal error "ORA-600 [12700]", what  
 it means and possible actions. The information here is only applicable  
 to the versions listed and is provided only for guidance.  
  
ERROR:  
 ORA-600 [12700] [a] [b] [c]  
  
VERSIONS:  
 versions 6.0 to 9.2  
  
DESCRIPTION:  
  
 Oracle is trying to access a row using its ROWID, which has been  
 obtained from an index.
 A mismatch was found between the index rowid and the data block it is  
 pointing to. The rowid points to a non-existent row in the data block.  
 The corruption can be in data and/or index blocks.  
  
 ORA-600 [12700] can also be reported due to a consistent read (CR)  
 problem.
 The information dumped to the trace file varies greatly between releases:
- in Oracle 7.3.x it is ORA-600 [12700][a][b] , where  
 Arg [a] dba (Data Block Address)  
 Arg [b] slot number (number of the row in the block pointed by the dba)
- in Oracle 8.x and 9.x, it is ORA-600 [12700][a][b][c] , where  
 Arg [a] dataobj# from sys.obj$  
 Arg [b] relative dba of the data block  
 Arg [c] slot number of the row in the data block
 The arguments of the ORA-600 [12700] contains information obtained  
 from the index we are using.
FUNCTIONALITY:  
 USER/ORACLE INTERFACE LAYER
IMPACT:  
 POSSIBLE CORRUPTION
SUGGESTIONS:
 Please refer to Note:155933.1 "Resolving an ORA-600 [12700] error"  
 for additional help with diagnosing this problem.
 If the above note does not help, then please log the issue with Oracle  
 Support Services.
 Known Issues:

查询块号

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
 
15:29:39 [SYS@txczyy](mailto:SYS@txczyy)> select obj#,name from sys.obj$ where dataobj#=2;
 OBJ# NAME  
---------- ------------------------------  
 4 TAB$  
 2 C_OBJ#  
 19 IND$  
 21 COL$  
 20 ICOL$  
 5 CLU$  
 156 LOB$  
 283 COLTYPE$  
 286 SUBCOLTYPE$  
 288 ATTRCOL$  
 290 VIEWTRCOL$
 OBJ# NAME  
---------- ------------------------------  
 295 TYPE_MISC$  
 352 NTAB$  
 359 LIBRARY$  
 362 REFCON$  
 365 OPQTYPE$  
 367 ICOLDEP$
15:31:00 [SYS@txczyy](mailto:SYS@txczyy)> select dbms_utility.data_block_address_file(4206885),dbms_utility.data_block_address_block(4206885) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(4206885) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(4206885)  
--------------------------------------------- ----------------------------------------------  
 1 12581
19:31:30 [SYS@txczyy](mailto:SYS@txczyy)> select obj#,col# from col$ where obj#=46907;
 OBJ# COL#  
---------- ----------  
 46907 1  
 46907 2  
 46907 3  
 46907 4  
 46907 5  
 46907 6  
 46907 7
已选择7行。
20:06:29 [SYS@txczyy](mailto:SYS@txczyy)> select rowid,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from col$ c where obj#=46907;
ROWID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)  
------------------ ------------------------------------ ------------------------------------  
AAAAACAABAAADElAAX 1 12581  
AAAAACAABAAADElAAY 1 12581  
AAAAACAABAAADElAAZ 1 12581  
AAAAACAABAAADElAAo 1 12581  
AAAAACAABAAADElAAp 1 12581  
AAAAACAABAAADElAAq 1 12581  
AAAAACAABAAADElAAr 1 12581

说明 block=12581发生坏块

查看数据行在哪个块

1
 
select depid,dept,dbms_rowid.rowid_block_number(rowid) blcokno from hw;

原创文章，作者：1402239773，如若转载，请注明出处：https://blog.ytso.com/tech/database/236364.html

赞 (0)
0 0
上一篇 2022年1月24日 21:27
下一篇 2022年1月24日 21:27

相关推荐

发表回复

登录后才能评论