Shulou(Shulou.com)06/01 Report--

[BBED] BBED simulates and fixes ORA-08102 errors

1.1 BLOG document structure map

1.2Preface 1.2.1 introduction and precautions for technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) obsession:

① uses BBED to fix ORA-08102 errors (focus)

The use of ② BBED

Dump file interpretation of ③ block format

Trace File interpretation of ④ ORA-08102 errors

⑤ gets ROWID information from rdba

Other practical skills of ⑥

An introduction to this article these days a friend asked me about ORA-08102 errors and about I_OBJ4 indexes on the OBJ$ table. The index of these system objects cannot be repaired by rebuilding or setting events. I vaguely remember seeing the use of BBED to fix the error a long time ago, but I can't remember it clearly. Just take this opportunity to reproduce the error and familiarize yourself with bbed.

My friend sent me the reference article is also the master cherish Fenfei blog address, a rough look at the process, mainly to find the relevant address of the index block, and then use bbed to modify the key value and the table storage can be consistent. The same saying, "the truth learned from paper is not profound, you must practice it yourself." Self-simulation experiment, this process is necessary.

Don't talk too much nonsense, let's start the experiment.

1.3.1 links to related articles read this article, please read the following first:

1. The difference between Object_iD and Data_Object_ID in Oracle: http://blog.itpub.net/26736162/viewspace-2145230/

2. Dump function of Oracle: http://blog.itpub.net/26736162/viewspace-2145228/

3. Several articles by BBED:

① [BBED] compilation and basic commands (1): http://blog.itpub.net/26736162/viewspace-2075216/

② [BBED] recovery in case of loss of archived files: http://blog.itpub.net/26736162/viewspace-2079337/

Recovery of ③ [BBED] sys.bootstrap$ objects: http://blog.itpub.net/26736162/viewspace-2083621/

Recovery of ④ [BBED] SYSTEM file header corruption (4): http://blog.itpub.net/26736162/viewspace-2084329/

⑤ [BBED] bbed Common Command: http://blog.itpub.net/26736162/viewspace-2123465/

1.4.Note 1. After all, bbed is an undisclosed recovery method, so friends who are not familiar with it should use it with caution.

2. Use startup force with caution

3. It's best to shut down the database before operating bbed.

1.5 related knowledge points literacy An ORA-08102 indicates that there is a mismatch between the key (s) stored in the index and the values stored in the table.What typically happens in the index is built and at some future time,some type of corruption occurs,either in the table or index,to cause the mismatch.

ORA-08102 is common when the index key value is inconsistent with the value stored on the table.

[oracle@rhel6lhr ~] $oerr ora 8102

08102, 00000, "index key not found, obj# s, file s, block s (s)"

/ / * Cause: Internal error: possible inconsistency in index

/ / * Action: Send trace file to your customer support representative, along

/ / with information on reproducing the error

An error like ora-08102 indicates that there is data inconsistency in the index or table, and the key values recorded in the index are inconsistent with the data in the table, resulting in access failure, which can be solved by rebuilding the index. Inconsistency between the two sides can achieve the goal of changing the table and the index, as long as it is consistent, but there is a principle that the key value of the index should always be increased sequentially. There are usually three situations:

1. If it is corrupted as an index, delete the index and rebuild the index, but for cases where the obj# of index is less than 56, because it is the core bootstrap$ object, index is automatically created by DB when DB starts. In this case, it cannot be solved by setting event 38003 or startup migrate mode, but obj# > 56 can.

two。 If the damage is at the block level, the treatment of bad blocks will be adopted.

3. If the damage is at the record level of the table, use bbed or other tools

I_OBJ1, I_OBJ2, I_OBJ3, I_OBJ4, and I_OBJ5 are all indexes of the OBJ$ base table, and it will be very troublesome if they are damaged, because ORACLE has strict restrictions on the DDL of these objects, and there is no way to simply repair them.

SYS@ora11g > drop index i_obj4

Drop index i_obj5

*

ERROR at line 1:

ORA-00701: object necessary for warmstarting database cannot be altered

-Chapter II Experimental Section 2.1 introduction to the experimental environment

Source db

Db Typ

Single machine

Db version

11.2.0.3.0

Db storage

FS

OS version and kernel version

RHEL 6.5

2.2.2.ObjectiveObjectiveTo simulate and fix ORA-08102 errors using BBED.

Simulate the error process: modify the block of the largest row of the DATAOBJ# column in the OBJ$ table through bbed to increase the value of DATAOBJ#, which is inconsistent with the value recorded in the index. Restart the database and create a table to have the database report an ORA-08102 error.

Repair error process: fix ORA-08102 errors by modifying inconsistent data in a table or index to be consistent through bbed.

2.3 Experimental process 2.3.1 do complete [oracle@rhel6lhr ~] $more rman_full.sh

Rman target / nocatalog select object_id,object_type from dba_objects where object_name='I_OBJ4'

OBJECT_ID OBJECT_TYPE

--

39 INDEX

SYS@ora11g > select max (DATAOBJ#) from obj$

MAX (DATAOBJ#)

-

94098

SYS@ora11g > select dump (94098jue 16) from dual

DUMP (94098 and 16)

Typ=2 Len=4: c3mai 29pi 63 = = > Typ=2 means NUMBER,96 means CHAR. Len=4 represents a 4-bit length, so 94098 is stored inside the database as 04c30a2963

SYS@ora11g > SELECT DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) FILE#

2 DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) BLOCK#

3 DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) ROW#

4 FROM OBJ$

5 WHERE DATAOBJ# = 94098

FILE# BLOCK# ROW#

1 241 27

SYS@ora11g > SELECT COUNT (*) COUNTS

2 MAX (DBMS_ROWID.ROWID_ROW_NUMBER (ROWID)) MAX_ROWNUM

3 MIN (DBMS_ROWID.ROWID_ROW_NUMBER (ROWID)) MIN_ROWNUM

4 FROM SYS.OBJ$ D

5 WHERE DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) = 1

6 AND DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) = 241

COUNTS MAX_ROWNUM MIN_ROWNUM

105 104 0

According to the above SQL, you can get the contents of the following table:

Project

Value

Maximum value of DATAOBJ# column on OBJ$

94098

The maximum dump value of the DATAOBJ# column on OBJ$

Typ=2 Len=4: c3, a, 29, and 63, that is, 04c30a2963

The address of the data block in which the row is located

FILE# BLOCK# ROW#

1 241 27

The storage of the row

COUNTS MAX_ROWNUM MIN_ROWNUM

105 104 0

That is, the maximum value of the DATAOBJ# column on the OBJ$ table is 94098, and the value is stored in 04c30a2963 in the Oracle database. The block of the row data is file 1, block 241, and row 27. There are 105 rows of data on the block, and the row number of the maximum value is 104. the row number of the minimum value is 0.

For the full text, please refer to: http://blog.itpub.net/26736162/viewspace-2145368/

Chapter IV SQL Summary SELECT COUNT (*) COUNTS used in the experiment

MAX (DBMS_ROWID.ROWID_ROW_NUMBER (ROWID)) MAX_ROWNUM

MIN (DBMS_ROWID.ROWID_ROW_NUMBER (ROWID)) MIN_ROWNUM

FROM SYS.OBJ$ D

WHERE DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) = 1

AND DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) = 241

SELECT DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) FILE#

DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID) BLOCK#

DBMS_ROWID.ROWID_ROW_NUMBER (ROWID) ROW#

FROM SYS.OBJ$

WHERE DATAOBJ# = 94109

SELECT DUMP (94098, 16) FROM DUAL

SELECT DUMP (OBJ#, 16)

DUMP (DATAOBJ#, 16)

DUMP (OWNER#, 16)

DUMP (NAME, 16)

DUMP (NAMESPACE, 16)

DUMP (SUBNAME, 16)

DUMP (TYPE#, 16)

DUMP (CTIME, 16)

DUMP (MTIME, 16)

DUMP (STIME, 16)

DUMP (STATUS, 16)

DUMP (REMOTEOWNER, 16)

DUMP (LINKNAME, 16)

DUMP (FLAGS, 16)

DUMP (OID$, 16)

DUMP (SPARE1, 16)

DUMP (SPARE2, 16)

DUMP (SPARE3, 16)

DUMP (SPARE4, 16)

DUMP (SPARE5, 16)

DUMP (SPARE6, 16)

FROM SYS.OBJ$ D

WHERE DATAOBJ# = 94098

SELECT OBJ#

DATAOBJ#

OWNER#

NAME

NAMESPACE

SUBNAME

TYPE#

CTIME

MTIME

STIME

STATUS

REMOTEOWNER

LINKNAME

FLAGS

OID$

SPARE1

SPARE2

SPARE3

FROM SYS.OBJ$ D

WHERE DATAOBJ# = 94098

SELECT * FROM SYS.OBJ$ D WHERE D.NAME = 'OBJ$'

SELECT 'DUMP (' | | D.COLUMN_NAME | |', 16),'

FROM DBA_TAB_COLS D

WHERE D.TABLE_NAME = 'OBJ$'

ORDER BY D.COLUMN_ID

SELECT D.COLUMN_NAME | |','

FROM DBA_TAB_COLS D

WHERE D.TABLE_NAME = 'OBJ$'

ORDER BY D.COLUMN_ID

SELECT * FROM sys.Bootstrap$

ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)

Select / * + index (t i_obj4) * / DATAOBJ# from sys.obj$ t

Minus

Select / * + full (T1) * / DATAOBJ# from sys.obj$ T1

Select / * + full (T1) * / DATAOBJ# from sys.obj$ T1

Minus

Select / * + index (t i_obj4) * / DATAOBJ# from sys.obj$ t

Select / * + full (t) * / DATAOBJ#,type#,owner# from sys.obj$ t WHERE t.dataobj# IN (94098 and 94099)

Select / * + full (t i_obj4) * / DATAOBJ#,type#,owner# from sys.obj$ t WHERE t.dataobj# IN (94098 and 94099)

-- convert hexadecimal to decimal

Select utl_raw.cast_to_number ('c30a2964') from dual

Show all

Map

P kdbr

P * kdbr [27]

X / rnnncncntttnccncnnn

D / v count 32

F / x c30a29

Sum

Sum apply

V

About Me

. .

● author: wheat seedlings, only focus on the database technology, pay more attention to the application of technology

● article is updated synchronously on itpub (http://blog.itpub.net/26736162/abstract/1/), blog Park (http://www.cnblogs.com/lhrbest) and personal Wechat official account (xiaomaimiaolhr).

● article itpub address: http://blog.itpub.net/26736162/viewspace-2145368/

● article blog park address: http://www.cnblogs.com/lhrbest/p/7576416.html

● pdf version of this article, personal introduction and wheat seedling cloud disk address: http://blog.itpub.net/26736162/viewspace-1624453/

● database written examination interview questions database and answers: http://blog.itpub.net/26736162/viewspace-2134706/

● DBA Baodian Jinri Toutiao address: http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

. .

● QQ group number: 230161599 (full), 618766405

● WeChat group: you can add me Wechat, I will pull everyone into the group, if you are the one

● contact me, please add QQ friend (646634621), indicate the reason for adding

● completed at 09:00 on 2017-09-20 and 22:00 on 2017-09-23 in Mordor.

The content of the ● article comes from the study notes of wheat seedlings, and some of it is sorted out from the Internet. Please forgive me if there is any infringement or improper place.

Copyright ● all rights reserved, welcome to share this article, please reserve the source for reprint

. .

The micro store of ● wheat seedlings: https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

A series of database books published by ● wheat seedlings: http://blog.itpub.net/26736162/viewspace-2142121/

. .

Use Wechat client to scan the following QR code to follow the Wechat official account (xiaomaimiaolhr) and QQ group (DBA treasure book) of wheat seedlings and learn the most practical database technology.

Wheat seedling Wechat official account wheat seedling DBA treasure QQ group 1 wheat seedling DBA treasure QQ group 2 wheat seedling Weidian

. .

Cdn.qqmail.com/zh_CN/htmledition/p_w_picpaths/function/qm_open/ico_mailme_02.png ">

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.