ORA-600 [12700]故障处理一则（在线重建损坏的索引）

 

1、现象

今天（20101202）遭遇了ORA-600 [12700]报错故障；客户一数据库出现新的报错信息：

Thu Dec 2 00:00:10 2010

Errors in file ....../bdump/ora9i_j003_4087.trc:

ORA-00600: internal error code, arguments: [12700], [30396], [88425261], [37], [93028418], [25], [], []

2、官方解释

Oracle官方文档解释描述如下：

ORA-600 [12700] "Index entry Points to Missing ROWID" [ID 28229.1]

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.

3、处理步骤

1）找到跟踪文件、引起告警的SQL语句

ora9i_j003_4087.trc

打开ora9i_j003_4087.trc后，查找，发现时如下SQL语句引起告警：

Current SQL statement for this session:

SELECT max(a.entryid)

FROM p1_ct_orcleventtime a, p1_ct_dn b

WHERE a.entryid = b.entryid

AND a.attrvalue < :b2

AND b.parentdn like :b1

----- PL/SQL Call Stack ----

1） 对可能损坏了的数据块相关信息进行检查：

$ sqlplus /nolog

SQL*Plus: Release 9.2.0.4.0 - Production on Fri Dec 3 10:24:31 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba

Connected.

SQL> SELECT owner, object_name, object_type, object_id, data_object_id

2 FROM dba_objects

3 WHERE data_object_id = 30396;

OWNER OBJECT_NAME   OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID

-------------------------------- ---------- --------------

ODS   P1_CT_DN    TABLE 30396 30396

3）、对引发错误的表的数据块进行检查：

SQL> ANALYZE TABLE ods.p1_ct_dn VALIDATE STRUCTURE;

Table analyzed.

4）、对引发错误的表的索引进行检查：

SQL> ANALYZE TABLE ods.p1_ct_dn VALIDATE STRUCTURE CASCADE;

ANALYZE TABLE ods.p1_ct_dn VALIDATE STRUCTURE CASCADE

*

ERROR at line 1:

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

报错信息表明索引存在问题，需进一步分析检查；

5）将存储过程oerr12700和oerr12700_and_oerr12700diag.sql保存到sql脚本文件oerr12700_and_oerr12700diag.sql里并上传，设置权限可执行；

SQL> @/home/oracle/oerr12700_and_oerr12700diag.sql

Procedure created.

Procedure created.

SQL> set serveroutput on

SQL> execute oerr12700( 30396, 88425261, 37 )

ORA-600 [12700] [30396],[88425261],[37]

--------------------------------------------------

there is an index pointing to a row in ODS.P1_CT_DN

row is slot 37 in file 21 block 344877

one index entry is pointing to ROWID='AAAHa8AAVAABUMtAAl'

--------------------------------------------------

You may want to check the integrity of ODS.P1_CT_DN

executing :

dbv file=/db/ora_data/p1attrs1_ora9i.dbf

blocksize=4096 start=344877

end=344877

--------------------------------------------------

IF dbv does not show any corruption, you can try to

find the corrupted indexes using the queries proposed

by the procedure oerr12700diag(30396,88425261,37)

-------------------------------------------------------

PL/SQL procedure successfully completed.

$ dbv file=/db/ora_data/p1attrs1_ora9i.dbf blocksize=4096 start=344877 end=344877

DBVERIFY: Release 9.2.0.4.0 - Production on Fri Dec 3 11:19:43 2010

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = /db/ora_data/p1attrs1_ora9i.dbf

DBVERIFY - Verification complete

Total Pages Examined : 1

Total Pages Processed (Data) : 1

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 0

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 0

Total Pages Marked Corrupt : 0

Total Pages Influx : 0

上述操作的返回结果表明，没有数据块损坏；

6）继续执行检查判断是否有损坏的索引：

SQL> execute oerr12700diag( 30396, 88425261, 37 )

--------------------------------------------------

IF dbv did not show any corruption, you can try to

find the corrupted indexes using following queries:

-------------------------------------------------------

If a query returns "no rows selected" index is sane

If a query returns AAAHa8AAVAABUMtAAl index is corrupted

..................................................

.

To test ODS.P1_CT_DN indexes

To test INDEX EP_P1_DN you run :

select rowid "EP_P1_DN corrupted!"

from

(SELECT /*+ INDEX_FFS(P1_CT_DN,EP_P1_DN) */

ENTRYID,rowid from ODS.P1_CT_DN where ENTRYID=ENTRYID)

where rowid='AAAHa8AAVAABUMtAAl';

To test INDEX RP_P1_DN you run :

select rowid "RP_P1_DN corrupted!"

from

(SELECT /*+ INDEX_FFS(P1_CT_DN,RP_P1_DN) */

PARENTDN,rowid from ODS.P1_CT_DN where PARENTDN=PARENTDN)

where rowid='AAAHa8AAVAABUMtAAl';

.

To test INDEX PN_P1_DN you run :

select rowid "PN_P1_DN corrupted!"

from

(SELECT /*+ INDEX_FFS(P1_CT_DN,PN_P1_DN) */

PARENTDN,rowid from ODS.P1_CT_DN where PARENTDN=PARENTDN)

where rowid='AAAHa8AAVAABUMtAAl';

PL/SQL procedure successfully completed.

SQL> select rowid "EP_P1_DN corrupted!"

2 from

3 (SELECT /*+ INDEX_FFS(P1_CT_DN,EP_P1_DN) */

4 ENTRYID,rowid from ODS.P1_CT_DN where ENTRYID=ENTRYID)

5 where rowid='AAAHa8AAVAABUMtAAl';

no rows selected

SQL> select rowid "RP_P1_DN corrupted!"

2 from

3 (SELECT /*+ INDEX_FFS(P1_CT_DN,RP_P1_DN) */

4 PARENTDN,rowid from ODS.P1_CT_DN where PARENTDN=PARENTDN)

5 where rowid='AAAHa8AAVAABUMtAAl';

RP_P1_DN corrupted

------------------

AAAHa8AAVAABUMtAAl

SQL> select rowid "PN_P1_DN corrupted!"

2 from

3 (SELECT /*+ INDEX_FFS(P1_CT_DN,PN_P1_DN) */

4 PARENTDN,rowid from ODS.P1_CT_DN where PARENTDN=PARENTDN)

5 where rowid='AAAHa8AAVAABUMtAAl';

PN_P1_DN corrupted

------------------

AAAHa8AAVAABUMtAAl

以上表明表ODS.P1_CT_DN上索引RP_P1_DN和PN_P1_DN有损坏，需重建。

7)、在线重建损坏的索引

SQL> alter index ODS. RP_P1_DN rebuild online;

SQL> alter index ODS. PN_P1_DN rebuild online;

参考文档：ORA-600 [12700] "Index entry Points to Missing ROWID" [ID 28229.1]

Resolving an ORA-600 [12700] error in Oracle 8 and above

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21256317/viewspace-777450/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21256317/viewspace-777450/