sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-08102的产生原因和MOS官方解决办法

时间:2016-10-22 22:38   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle生产环境数据库alert日志报错ORA-08102,结合MOS官方分析产生原因为索引异常导致,重建异常索引解决。

1,数据库版本

SQL> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production

PL/SQL Release 11.2.0.4.0 – Production

CORE 11.2.0.4.0 Production

TNS for Linux: Version 11.2.0.4.0 – Production

NLSRTL Version 11.2.0.4.0 – Production

2,手动将表中索引列值修改

SQL> SELECT ROWID, con#

2 FROM con$

3 WHERE con# IN (SELECT MAX (con#) FROM con$);

ROWID CON#

—————— ———-

AAAAAcAABAAAAEhAAM 11

这里我们选择了con$中con#列最大的一个值来测试

SQL> @rowid_to_info.sql

Enter value for rowid: AAAAAcAABAAAAEhAAM

ROWID_TYPE: 1

OBJECT_NUMBER: 28

RELATIVE_FNO: 1

BLOCK_NUMBER: 289

ROW_NUMBER: 12

BBED> x /rncnnnn *kdbr[12]

rowdata[0] @1319

———-

flag@1319: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1320: 0x02

cols@1321: 4

col 0[1] @1322: 0

col 1[16] @1324: _NEXT_CONSTRAINT

col 2[4] @1341: 11088

col 3[1] @1346: 0

这里我们看到con#的值为11088,这里我们将值更改为11111

BBED> set count 10

COUNT 10

BBED> set offset 1341

OFFSET 1341

BBED> dump

File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)

Block: 289 Offsets: 1341 to 1350 Dba:0x00000000

————————————————————————

04c3020b 5901802c 0004

<32 bytes per line>

SQL> select dump(11111,’16’) from dual;

DUMP(11111,’16’)

———————

Typ=2 Len=4: c3,2,c,c

BBED> modify /x 0c0c offset 1344;

File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)

Block: 289 Offsets: 1344 to 1353 Dba:0x00000000

————————————————————————

0c0c0180 2c000401 8010

<32 bytes per line>

BBED> dump offset 1341

File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)

Block: 289 Offsets: 1341 to 1350 Dba:0x00000000

————————————————————————

04c3020c 0c01802c 0004

<32 bytes per line>

BBED> sum apply

Check value for File 0, Block 289:

current = 0x5f94, required = 0x5f94

BBED> verify

DBVERIFY – Verification starting

FILE = /oracle/app/oracle/oradata/orcl1124/system01.dbf

BLOCK = 289

DBVERIFY – Verification complete

Total Blocks Examined : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing (Index): 0

Total Blocks Empty : 0

Total Blocks Marked Corrupt : 0

Total Blocks Influx : 0

确认值是否修改成功

BBED> x /rncnnnn *kdbr[12]

rowdata[0] @1319

———-

flag@1319: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1320: 0x02

cols@1321: 4

col 0[1] @1322: 0

col 1[16] @1324: _NEXT_CONSTRAINT

col 2[4] @1341: 11111

col 3[1] @1346: 0

SQL> startup

ORACLE instance started.

Total System Global Area 379965440 bytes

Fixed Size 2253464 bytes

Variable Size 171969896 bytes

Database Buffers 201326592 bytes

Redo Buffers 4415488 bytes

Database mounted.

Database opened.

启动数据库

SQL> alter table scott.htz add constraint htz_object_id primary key(object_id);

alter table scott.htz add constraint htz_object_id primary key(object_id)

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

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


在报错信息中,我们可以看到obj#为52,所以我们查询52是什么对象,如果是非系统对象,并且是索引,我们可以直接drop掉索引,再重建索引就可以了。

这里我们看到了报错

这里发现对象是系统的并且,并且《59,不能使用重建的方法来实现,只能通过修改块的数据

SQL> @object_by_id.sql

+————————————————————————+

| display one object type,owner,time,status |

+————————————————————————+

Enter Search Object Id (i.e. 1235) : 52

Object Create Last_Ddl

OWNER OBJECT_NAME Type Time Time STATUS

——————– ————— ————— ——————– ——————– ———-

SYS I_CON2 INDEX 2013-08-24 11:37:35 2013-08-24 11:37:35 VALID

在trace文件中我们可以发现如下的内容

*** 2014-04-19 15:42:38.136

oer 8102.2 – obj# 52, rdba: 0x00412fe5(afn 1, blk# 77797) OBJ#这里为索引的OBJECT_ID,这里可以看到rdba:这里就是报错的块的地址,在这里是一个索引的块,我们在最后,要在这个块中找到正常的值

kdk key 8102.2:

ncol: 1, len: 5

key: (5): 04 c3 02 0c 0c 这里为表中的值,正确的值可以通过dump rdba地址找到

mask: (4096):

61 00 00 00 00 48 61 5a 76 00 00 00 00 00 00 00 00 00 00 00 00 c0 d9 fa 0b

00 00 00 00 d0 7c 97 0a 00 00 00 00 e0 5e 48 76 00 00 00 00 b0 16 ff bf 7f

00 00 00 cf da 81 09 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00

00 00 00 00 00 00 00 00 00 68 2a 4c 76 00 00 00 00 00 00 00 00 00 00 00 78

00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 48 17 ff bf 7f 00 00 00 f0

19 ff bf 7f 00 00 00 01 00 00 00 08 00 00 00 ff ff ff ff ff ff ff ff 90 12

*** 2014-04-19 15:42:38.222

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)

—– Error Stack Dump —–

—– Current SQL Statement for this session (sql_id=bajr90ryjd2w8) —–

update con$ set con#=:3,spare1=:4 where owner#=:1 and name=:2

这里我们看到在更新con$的时候发生了错误。

SQL> SELECT /*+ FULL(t1) */ con#

2 FROM con$ t1

3 MINUS

4 SELECT /*+ index(t I_CON2) */ con#

5 FROM con$ t1

6 ;

CON#

———-

11111

这里我们发生是con#这列11111这列的值在索引中不存在

通过我们查询con#所有的rowid的信息,我们需要根据rowid的根本来计算存放在索引中的rowid的信息。用于寻找正确的索引值

SQL> select /*+ full(a) */rowid,con# from con$ a where con#=11111;

ROWID CON#

—————— ———-

AAAAAcAABAAAAEhAAM 11111

这里发现rowid,将rowid转成块的信息

SQL> @rowid_to_info.sql

Enter value for rowid: AAAAAcAABAAAAEhAAM

ROWID_TYPE: 1

OBJECT_NUMBER: 28

RELATIVE_FNO: 1

BLOCK_NUMBER: 289

ROW_NUMBER: 12

PL/SQL procedure successfully completed.

再转换成索引中的rowid的信息

SQL> @num_convert.sql

‘********************************************************’

‘function hex_to_dec (hexin IN VARCHAR2) return NUMBER; ‘

‘function dec_to_hex (decin IN NUMBER) return VARCHAR2; ‘

‘function oct_to_dec (octin IN NUMBER) return NUMBER; ‘

‘function dec_to_oct (decin IN NUMBER) return VARCHAR2; ‘

‘function bin_to_dec (binin IN NUMBER) return NUMBER; ‘

‘function dec_to_bin (decin IN NUMBER) return VARCHAR2; ‘

‘function hex_to_bin (hexin IN VARCHAR2) return NUMBER; ‘

‘function bin_to_hex (binin IN NUMBER) return VARCHAR2; ‘

‘function oct_to_bin (Oracle???????oracleplus.netoctin IN NUMBER) return NUMBER; ‘

‘function bin_to_oct (binin IN NUMBER) return NUMBER; ‘

‘function oct_to_hex (octin IN NUMBER) return VARCHAR2; ‘

‘function hex_to_oct (hexin IN VARCHAR2) return NUMBER; ‘

‘********************************************************’

Enter value for function: bin_to_hex

Enter value for number: 00000000 01000000 00000001 00100001 00001100

VALUE

——————

4001210C

所以这里索引的rowid的值为00 40 01 21 0c

dump报错的索引块的信息

SQL> alter system dump datafile 1 block 77797;

System altered.

SQL> oradebug setmypid

Statement processed.

SQL> oradebug tracefile_name;

/oracle/app/oracle/diag/rdbms/orcl1124/orcl1124/trace/orcl1124_ora_11517.trc

在trace文件中,以rowid来搜索

row#238[1772] flag: ——, lock: 0, len=13, data:(6): 00 40 01 21 00 0c

col 0; len 4; (4): c3 02 0b 5a

所以这里我们可以看到原来的值是c3 02 0b 5a

这里我们不能直接修改索引值,因为索引的值是有顺序的,改起来麻烦

如下:

所以这里我们知道,索引的记录在row#238这里

BBED> set block 77797

BLOCK# 77797

BBED> x /rn *kd_off[238]

rowdata[56] @1927

———–

flag@1927: 0x00 (NONE)

lock@1928: 0x00

keydata[6]: 0x00 0x41 0x54 0xc4 0x00 0xb1

data key:

col 0[4] @1936: 11086

BBED> set mode edit

MODE Edit

BBED> modify /x 0c0c offset 1938

Warning: contents of previous BIFILE will be lost. Proceed (Y/N) y

File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)

Block: 77797 Offsets: 1938 to 1947 Dba:0x00000000

————————————————————————

0c0c0100 00400121 000c

<32 bytes per line>

BBED> sum apply

Check value for File 0, Block 77797:

current = 0x5c0e, required = 0x5c0e

BBED> verify

DBVERIFY – Verification starting

FILE = /oracle/app/oracle/oradata/orcl1124/system01.dbf

BLOCK = 77797

Block Checking: DBA = 4272101, Block Type = KTB-managed data block

**** row 237: key out of order

—- end index block validation

Block 77797 failed with check code 6401

DBVERIFY – Verification complete

Total Blocks Examined : 1

Total Blocks Processed (Data) : 0

Total Blocks Failing (Data) : 0

Total Blocks Processed (Index): 1

Total Blocks Failing (Index): 1

Total Blocks Empty : 0

Total Blocks Marked Corrupt : 0

Total Blocks Influx : 0

Message 531 not found; product=RDBMS; facility=BBED

这里报错了,下面我们只能修改块中的内容值,修改11111修改成索引中存放的值

BBED> set block 289

BLOCK# 289

BBED> x /rncnnnn *kdbr[12]

rowdata[0] @1319

———-

flag@1319: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1320: 0x00

cols@1321: 4

col 0[1] @1322: 0

col 1[16] @1324: _NEXT_CONSTRAINT

col 2[4] @1341: 11111

col 3[1] @1346: 0

BBED> set mode edit

MODE Edit

BBED> modify /x 0b5a offset 1344

File: /oracle/app/oracle/oradata/orcl1124/system01.dbf (0)

Block: 289 Offsets: 1344 to 1353 Dba:0x00000000

————————————————————————

0b5a0180 2c000401 8010

<32 bytes per line>

BBED> sum apply

Check value for File 0, Block 289:

current = 0xd3bb, required = 0xd3bb

BBED> verify

DBVERIFY – Verification starting

FILE = /oracle/app/oracle/oradata/orcl1124/system01.dbf

BLOCK = 289

DBVERIFY – Verification complete

Total Blocks Examined : 1

Total Blocks Processed (Data) : 1

Total Blocks Failing (Data) : 0

Total Blocks Processed (Index): 0

Total Blocks Failing (Index): 0

Total Blocks Empty : 0

Total Blocks Marked Corrupt : 0

Total Blocks Influx : 0

Message 531 not found; product=RDBMS; facility=BBED

BBED> x /rncnnnn *kdbr[12]

rowdata[0] @1319

———-

flag@1319: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@1320: 0x00

cols@1321: 4

col 0[1] @1322: 0

col 1[16] @1324: _NEXT_CONSTRAINT

col 2[4] @1341: 11089

col 3[1] @1346: 0

下面成功了

SQL> startup

ORACLE instance started.

Total System Global Area 379965440 bytes

Fixed Size 2253464 bytes

Variable Size 171969896 bytes

Database Buffers 201326592 bytes

Redo Buffers 4415488 bytes

Database mounted.

Database opened.

SQL> alter table scott.htz add constraint htz_object_id primary key(object_id);

Table altered.

本文固定链接: http://www.htz.pw/2014/04/22/ora-08102-index-key-not-found-obj-52-file-1-block-77797-2.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-08102的产生原因和MOS官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1034.html

Oracle研究中心

关键词:

Oracle报错ORA-08102的解决办法

ORA-00604 ORA-08102

Oracle索引异常并报错ora-08102