ORA-08102 index key not found, obj# 52, file 1, block 77797 (2)
2014年4月21日
13:51
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 (octin 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. |
ORA-08102 index key not found, obj# 52, file 1, block 77797 (2)：等您坐沙发呢！