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

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.

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

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

ORA-600 [12700]故障處理一則（線上重建損壞的索引）

相關文章