【BBED】BBED模拟并修复ORA-08102错误
1.1 BLOG文档结构图
1.2 前言部分
1.2.1 导读和注意事项
各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：
① 使用BBED修复ORA-08102错误（重点）
② BBED的使用
③ 数据块格式的dump文件解释
④ ORA-08102错误的trace文件解释
⑤ 从rdba获取ROWID信息
⑥ 其它实用技能
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客园(http://www.cnblogs.com/lhrbest)和微信公众号（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代码、相关软件、相关资料及本文的pdf版本都请前往小麦苗的云盘下载，小麦苗的云盘地址见：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若网页文章代码格式有错乱，请下载pdf格式的文档来阅读。
④ 在本篇BLOG中，代码输出部分一般放在一行一列的表格中。
⑤ 本文适合于Oracle初中级人员阅读，Oracle大师请略过本文。
⑥ 不喜勿喷。
本文若有错误或不完善的地方请大家多多指正，您的批评指正是我写作的最大动力。
1.3 本文简介
这几天一个朋友问我有关ORA-08102的错误，而且是关于OBJ$表上的I_OBJ4索引。这些系统对象的索引，不能采用重建或设置事件的方式来修复该错误。模模糊糊的记得很早以前看过使用BBED的方式来修复该错误，只是已经记不清了。正好，趁此机会把该错误再模拟的复现一下，也把bbed再熟悉一下吧。
朋友发给我的参考文章也是大师惜分飞的博客地址，大致看了一下过程，主要是找到索引块的相关地址，然后利用bbed把键值修改的和表中存储的一致即可。还是那句话，“纸上得来终觉浅，绝知此事要躬行。”，自己模拟实验，这个过程是必须的。
废话不多说，开始实验吧。
1.3.1 相关文章链接
阅读本篇文章，请先阅读以下内容：
1. Oracle 中 Object_iD 和 Data_Object_ID 的区别：http://blog.itpub.net/26736162/viewspace-2145230/
2. Oracle的dump函数：http://blog.itpub.net/26736162/viewspace-2145228/
3. BBED的几篇文章：
① 【BBED】编译及基本命令(1)：http://blog.itpub.net/26736162/viewspace-2075216/
② 【BBED】丢失归档文件情况下的恢复：http://blog.itpub.net/26736162/viewspace-2079337/
③ 【BBED】 sys.bootstrap$ 对象的恢复：http://blog.itpub.net/26736162/viewspace-2083621/
④ 【BBED】 SYSTEM文件头损坏的恢复(4)：http://blog.itpub.net/26736162/viewspace-2084329/
⑤ 【BBED】bbed常用命令：http://blog.itpub.net/26736162/viewspace-2123465/
1.4 注意事项
1、bbed毕竟是未公开的恢复方式，所以不熟悉的朋友要慎用。
2、startup force慎用
3、操作bbed之前最好先把数据库关闭
1.5 相关知识点扫盲
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常见于索引键值与表上存的值不一致。
[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 |
ora-08102这种错误说明索引或表出现了数据不一致，索引上记录的键值和表里的数据不一致，引起访问失败，一般重建下索引就可以解决。两边不一致改表和索引都能达到目的，只要一致即可，但有一个原则就是索引键值始终要保证按顺序递增。通常有三种情况：
1.如果损坏为索引，则删除索引并重建索引，但对于index的obj#小于56的情况，由于是核心的bootstrap$对象，index是在DB启动时由DB自动创建，此种情况下通过设置event 38003或startup migrate模式都不能解决，但obj#>56的则可以。
2.如果损坏为块级别，则采用坏块的处理方法
3.如果损坏的为表的记录级别的则采用bbed或其它工具
I_OBJ1、I_OBJ2、I_OBJ3、I_OBJ4、I_OBJ5这几个都是OBJ$基表的索引，如果损坏会非常麻烦，因为ORACLE 对这些对象的DDL做了严格限制，没有办法简单修复它们。
SYS@ora11g > drop index i_obj4; drop index i_obj5 * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered
|
---------------------------------------------------------------------
第二章 实验部分
2.1 实验环境介绍
项目 |
source db |
db 类型 |
单机 |
db version |
11.2.0.3.0 |
db 存储 |
FS |
OS版本及kernel版本 |
RHEL 6.5 |
2.2 实验目标
实验目标：使用BBED模拟并修复ORA-08102错误。
模拟错误过程：通过bbed修改OBJ$表中DATAOBJ#列最大的行所在的块，让DATAOBJ#的值增大，从而和索引中记录的值不一致。重启数据库并创建表让数据库报出ORA-08102错误。
修复错误过程：通过bbed把表中或索引中的不一致的数据修改成一致的，从而修复ORA-08102错误。
2.3 实验过程
2.3.1 做全备
[oracle@rhel6lhr ~]$ more rman_full.sh rman target / nocatalog <<eof</eof<> run{ backup database format '/home/oracle/bak/%d_%U.full'; sql 'alter system archive log current'; backup archivelog all format '/home/oracle/bak/%d_%U.arc'; backup current controlfile format '/home/oracle/bak/%d_%U.ctl'; } EOF
[oracle@rhel6lhr ~]$ sh rman_full.sh
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Sep 20 13:56:41 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11G (DBID=4270446895) using target database control file instead of recovery catalog
RMAN> 2> 3> 4> 5> 6> Starting backup at 2017-09-20 13:56:41 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00004 name=/u01/app/oracle/oradata/ora11g/users01.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ora11g/system01.dbf input datafile file number=00002 name=/u01/app/oracle/oradata/ora11g/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ora11g/example01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ora11g/undotbs01.dbf input datafile file number=00022 name=/u01/app/oracle/oradata/ora11g/ts_ogg01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ora11g/DWII_CNY_BK_F_01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ora11g/DWII_DPA_F_01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ora11g/DWII_DPA_I_01.dbf input datafile file number=00010 name=/u01/app/oracle/oradata/ora11g/DWII_DPA_S_01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ora11g/DWII_SOR_F_01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ora11g/DWII_SOR_I_01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/ora11g/DW_USER.dbf input datafile file number=00014 name=/u01/app/oracle/oradata/ora11g/SQCHECK.dbf input datafile file number=00015 name=/u01/app/oracle/oradata/ora11g/SD_CNY_D_01.dbf input datafile file number=00016 name=/u01/app/oracle/oradata/ora11g/SD_CNY_F_01.dbf input datafile file number=00017 name=/u01/app/oracle/oradata/ora11g/SD_DPA_D_01.dbf input datafile file number=00018 name=/u01/app/oracle/oradata/ora11g/SD_DPA_F_01.dbf input datafile file number=00019 name=/u01/app/oracle/oradata/ora11g/SD_SORT_T_01.dbf input datafile file number=00020 name=/u01/app/oracle/oradata/ora11g/DWII_FXDM_F_01.dbf input datafile file number=00021 name=/u01/app/oracle/oradata/ora11g/SD_SOR_T_01.dbf input datafile file number=00006 name=/u01/app/oracle/oradata/ora11g/aa.dbf input datafile file number=00023 name=/u01/app/oracle/oradata/ora11g/test01.dbf channel ORA_DISK_1: starting piece 1 at 2017-09-20 13:56:42 channel ORA_DISK_1: finished piece 1 at 2017-09-20 13:59:17 piece handle=/home/oracle/bak/ORA11G_27seuekq_1_1.full tag=TAG20170920T135642 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:02:35 Finished backup at 2017-09-20 13:59:17
Starting Control File and SPFILE Autobackup at 2017-09-20 13:59:17 piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/autobackup/2017_09_20/o1_mf_s_955202357_dw40xohn_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2017-09-20 13:59:18
sql statement: alter system archive log current
Starting backup at 2017-09-20 13:59:18 current log archived using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log backup set channel ORA_DISK_1: specifying archived log(s) in backup set input archived log thread=1 sequence=302 RECID=304 STAMP=955202163 input archived log thread=1 sequence=303 RECID=305 STAMP=955202358 input archived log thread=1 sequence=304 RECID=306 STAMP=955202358 channel ORA_DISK_1: starting piece 1 at 2017-09-20 13:59:18 channel ORA_DISK_1: finished piece 1 at 2017-09-20 13:59:19 piece handle=/home/oracle/bak/ORA11G_29seuepm_1_1.arc tag=TAG20170920T135918 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2017-09-20 13:59:19
Starting backup at 2017-09-20 13:59:20 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 2017-09-20 13:59:21 channel ORA_DISK_1: finished piece 1 at 2017-09-20 13:59:22 piece handle=/home/oracle/bak/ORA11G_2aseuepo_1_1.ctl tag=TAG20170920T135920 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 2017-09-20 13:59:22
Starting Control File and SPFILE Autobackup at 2017-09-20 13:59:22 piece handle=/u05/app/oracle/flash_recovery_area/ORA11G/autobackup/2017_09_20/o1_mf_s_955202362_dw40xtf0_.bkp comment=NONE Finished Control File and SPFILE Autobackup at 2017-09-20 13:59:23
RMAN>
Recovery Manager complete.
|
2.3.2 模拟一：BBED模拟ORA-08102错误
通过BBED修改OBJ$中DATAOBJ$重现I_OBJ4索引报ORA-08102错误。定位需要破坏的OBJ$上DATAOBJ$列最大的记录，使之和索引I_OBJ4中记录不一致，从而实现ORA-8102错误。
[oracle@rhel6lhr ~]$ ORACLE_SID=ora11g [oracle@rhel6lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Sep 21 09:24:08 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ora11g > 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(94098,16) from dual;
DUMP(94098,16) ----------------------- Typ=2 Len=4: c3,a,29,63 ===>>>>> Typ=2表示NUMBER，96表示CHAR。Len=4表示4位长度，所以，94098在数据库内部的存储格式为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
|
根据以上的SQL可以得到下表的内容：
项目 |
值 |
OBJ$上DATAOBJ#列的最大值 |
94098 |
OBJ$上DATAOBJ#列的最大值dump值 |
Typ=2 Len=4: c3,a,29,63 即：04c30a2963 |
该行所在数据块的地址 |
FILE# BLOCK# ROW# ---------- ---------- ---------- 1 241 27 |
该行的存储情况 |
COUNTS MAX_ROWNUM MIN_ROWNUM ---------- ---------- ---------- 105 104 0 |
即：OBJ$表上DATAOBJ#列的最大值为94098，该值在Oracle数据库中的存储格式为04c30a2963，该行数据所在的块为1号文件，241号块，第27行，该块上共有105行数据，最大值的行号为104，最小值的行号为0。
2.3.2.1 dump文件解析
先对1号文件，241号块做dump：
SYS@ora11g > conn / as sysdba Connected. SYS@ora11g > alter system dump datafile 1 block 241;
System altered.
SYS@ora11g > select value from v$diag_info where name='Default Trace File';
VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28221.trc |
关于块格式的详细介绍请参考：http://blog.itpub.net/26736162/viewspace-2141499/
Start dump data blocks tsn: 0 file#:1 minblk 241 maxblk 241 ====>>>>> SYSTEM是0号表空间1号文件，当前块是241号 Block dump from cache:====>>>>> 从内存中dump出来的 Dump of buffer cache at level 4 for tsn=0 rdba=4194545====>>>>> cache中的位置 BH (0x6cfe2658) file#: 1 rdba: 0x004000f1 (1/241) class: 1 ba: 0x6cd1a000====>>>>> 参考链接地址 set: 5 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,25 dbwrid: 0 obj: 18 objn: 18 tsn: 0 afn: 1 hint: f hash: [0x773fca58,0x773fca58] lru: [0x6dbedcf0,0x6bbf1e40] lru-flags: hot_buffer ckptq: [NULL] fileq: [NULL] objq: [0x6e7d8388,0x6d7e82d8] objaq: [0x6d3f0588,0x6d7e82e8] st: XCURRENT md: NULL fpin: 'kdswh05: kdsgrp' tch: 11 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] Block dump from disk: ====>>>>> 参考链接地址 buffer tsn: 0 rdba: 0x004000f1 (1/241) scn: 0x0000.038f7e74 seq: 0x01 flg: 0x06 tail: 0x7e740601 frmt: 0x02 chkval: 0xc3cc type: 0x06=trans data Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00007F1583F05A00 to 0x00007F1583F07A00 7F1583F05A00 0000A206 004000F1 038F7E74 06010000 [......@.t~......] 7F1583F05A10 0000C3CC 00000001 00000012 038F7E73 [............s~..] 7F1583F05A20 00000000 0002F801 00000000 00190006 [................] 7F1583F05A30 00000BF4 00C002D5 00030203 00002001 [............. ..] 7F1583F05A40 038F7E74 00690100 00E4FFFF 035F013B [t~....i.....;._.] ...........省略部分输出................ 7F1583F079C0 0215C102 800103C1 4F434905 C102244C [.........ICOL$..] 7F1583F079D0 C102FF02 6F780703 2F0A1109 6F78070E [......xo.../..xo] 7F1583F079E0 3B0A1109 6F780701 2F0A1109 02C1020E [...;..xo.../....] 7F1583F079F0 8001FFFF 028001FF 800102C1 7E740601 [..............t~] Block header dump: 0x004000f1 ====>>>>> 参考链接地址 Object id on Block? Y seg/obj: 0x12 csc: 0x00.38f7e73 itc: 1 flg: - typ: 1 - DATA fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0006.019.00000bf4 0x00c002d5.0203.03 --U- 1 fsc 0x0000.038f7e74 bdba: 0x004000f1 data_block_dump,data header at 0x7f1583f05a44 =============== tsiz: 0x1fb8 hsiz: 0xe4 pbl: 0x7f1583f05a44 76543210 flag=-------- ntab=1 nrow=105 ====>>>>> 共105行数据 frre=-1 fsbo=0xe4 fseo=0x13b avsp=0x35f tosp=0x35f 0xe:pti[0]nrow=105offs=0 0x12:pri[0]offs=0x1f79 0x14:pri[1]offs=0x1f36 ...........省略部分输出................ 0x44:pri[25]offs=0x18ff 0x46:pri[26]offs=0x18c1 0x48:pri[27]offs=0x13b====>>>>> 第27行数据的指针偏移位置是13b，转换为10进制是315，selet to_number('13b','xxx') from dual; ...........省略部分输出................ 0xde:pri[102]offs=0x515 0xe0:pri[103]offs=0x4ce 0xe2:pri[104]offs=0x48b block_row_dump: tab 0, row 0, @0x1f79 tl: 63 fb: --H-FL-- lb: 0x0 cc: 18 col 0: [ 2] c1 15 col 1: [ 2] c1 03 col 2: [ 1] 80 col 3: [ 5] 49 43 4f 4c 24 col 4: [ 2] c1 02 col 5: *NULL* col 6: [ 2] c1 03 col 7: [ 7] 78 6f 09 11 0a 2f 0e col 8: [ 7] 78 6f 09 11 0a 3b 01 col 9: [ 7] 78 6f 09 11 0a 2f 0e col 10: [ 2] c1 02 col 11: *NULL* col 12: *NULL* col 13: [ 1] 80 col 14: *NULL* col 15: [ 1] 80 col 16: [ 2] c1 02 col 17: [ 1] 80 ...........省略部分输出................ tab 0, row 27, @0x13b ====>>>>> 该块中第一个表第27行的指针位置，转换为10进制是315 tl: 72 fb: --H-FL-- lb: 0x1 cc: 18 col 0: [ 2] c1 02 col 1: [ 4] c3 0a 29 63 col 2: [ 1] 80 col 3: [12] 5f 4e 45 58 54 5f 4f 42 4a 45 43 54 col 4: [ 2] c1 02 col 5: *NULL* col 6: [ 1] 80 col 7: [ 7] 78 6f 09 11 0a 2f 0e col 8: [ 7] 78 75 09 14 13 1d 25 col 9: [ 7] 78 6f 09 11 0a 2f 0e col 10: [ 1] 80 col 11: *NULL* col 12: *NULL* col 13: [ 1] 80 col 14: *NULL* col 15: [ 1] 80 col 16: [ 4] c3 07 38 24 col 17: [ 1] 80 ...........省略部分输出................ tab 0, row 104, @0x48b tl: 67 fb: --H-FL-- lb: 0x0 cc: 18 col 0: [ 3] c2 02 06 col 1: [ 3] c2 02 06 col 2: [ 1] 80 col 3: [ 7] 41 43 43 45 53 53 24 col 4: [ 2] c1 02 col 5: *NULL* col 6: [ 2] c1 03 col 7: [ 7] 78 6f 09 11 0a 2f 10 col 8: [ 7] 78 6f 09 11 0a 2f 10 col 9: [ 7] 78 6f 09 11 0a 2f 10 col 10: [ 2] c1 02 col 11: *NULL* col 12: *NULL* col 13: [ 1] 80 col 14: *NULL* col 15: [ 1] 80 col 16: [ 2] c1 02 col 17: [ 1] 80 end_of_block_dump End dump data blocks tsn: 0 file#: 1 minblk 241 maxblk 241
|
由于SYS.OBJ$表共21列，但是最后3列都为空，所以，dump文件里就没有显示出来。将该行数据以16进制dump出来看看：
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; |
结果和dump文件中的内容一致。
列名 |
10进制值 |
16进制值 |
dump文件的存储 |
OBJ# |
1 |
Typ=2 Len=2: c1,2 |
col 0: [ 2] c1 02 |
DATAOBJ# |
94098 |
Typ=2 Len=4: c3,a,29,63 |
col 1: [ 4] c3 0a 29 63 |
OWNER# |
0 |
Typ=2 Len=1: 80 |
col 2: [ 1] 80 |
NAME |
_NEXT_OBJECT |
Typ=1 Len=12: 5f,4e,45,58,54,5f,4f,42,4a,45,43,54 |
col 3: [12] 5f 4e 45 58 54 5f 4f 42 4a 45 43 54 |
NAMESPACE |
1 |
Typ=2 Len=2: c1,2 |
col 4: [ 2] c1 02 |
SUBNAME |
|
NULL |
col 5: *NULL* |
TYPE# |
0 |
Typ=2 Len=1: 80 |
col 6: [ 1] 80 |
CTIME |
2011-09-17 09:46:13 |
Typ=12 Len=7: 78,6f,9,11,a,2f,e |
col 7: [ 7] 78 6f 09 11 0a 2f 0e |
MTIME |
2017-09-20 18:28:36 |
Typ=12 Len=7: 78,75,9,14,13,1d,25 |
col 8: [ 7] 78 75 09 14 13 1d 25 |
STIME |
2011-09-17 09:46:13 |
Typ=12 Len=7: 78,6f,9,11,a,2f,e |
col 9: [ 7] 78 6f 09 11 0a 2f 0e |
STATUS |
0 |
Typ=2 Len=1: 80 |
col 10: [ 1] 80 |
REMOTEOWNER |
|
NULL |
col 11: *NULL* |
LINKNAME |
|
NULL |
col 12: *NULL* |
FLAGS |
0 |
Typ=2 Len=1: 80 |
col 13: [ 1] 80 |
OID$ |
|
NULL |
col 14: *NULL* |
SPARE1 |
0 |
Typ=2 Len=1: 80 |
col 15: [ 1] 80 |
SPARE2 |
65535 |
Typ=2 Len=4: c3,7,38,24 |
col 16: [ 4] c3 07 38 24 |
SPARE3 |
0 |
Typ=2 Len=1: 80 |
col 17: [ 1] 80 |
所以，从dump文件中还可以得到第27行数据的指针偏移位置是13b，转换为10进制是315。
2.3.2.2 使用bbed破坏该块中的第27行数据
使用bbed破坏记录，修改dataobj#中的值，使得obj$.dataobj#和i_obj4中的dataobj#不匹配。
SYS@ora11g > select name from v$datafile where file#=1;
NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ora11g/system01.dbf
最好是干净的关闭数据库
|
[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 10:40:47 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> show all FILE# 0 BLOCK# 1 OFFSET 0 DBA 0x00000000 (0 0,1) FILENAME /u01/app/oracle/oradata/ora11g/system01.dbf BIFILE bifile.bbd LISTFILE BLOCKSIZE 8192 MODE Edit EDIT Unrecoverable IBASE Dec OBASE Dec WIDTH 80 COUNT 512 LOGFILE ./log.bbd SPOOL No
BBED> set block 241 BLOCK# 241
BBED> map File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 241 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster) ====>>>>> 表明是数据块
struct kcbh, 20 bytes @0
struct ktbbh, 48 bytes @20
struct kdbh, 14 bytes @68
struct kdbt[1], 4 bytes @82
sb2 kdbr[105] @86
ub1 freespace[87] @296
ub1 rowdata[7805] @383
ub4 tailchk @8188 ====>>>>> 块校验位
BBED> p kdbr===>>>>> 块中数据指针位置 sb2 kdbr[0] @86 8057 sb2 kdbr[1] @88 7990 sb2 kdbr[2] @90 7928 。。。。。。。。。。。省略。。。。。。。。。。。 sb2 kdbr[24] @134 6466 sb2 kdbr[25] @136 6399 sb2 kdbr[26] @138 6337 sb2 kdbr[27] @140 315===>>>>> 第27行数据指针位置为315，和dump出来的信息一致 sb2 kdbr[28] @142 6268 sb2 kdbr[29] @144 6201 。。。。。。。。。。。省略。。。。。。。。。。。 sb2 kdbr[103] @292 1230 sb2 kdbr[104] @294 1163===>>>>> 表明该块共有105行数据
BBED> p *kdbr[27] rowdata[0] ---------- ub1 rowdata[0] @383 0x2c===>>>>> 第27行偏移位置为383
BBED> show offset OFFSET 383
BBED> x /rnnncncntttnccncnnn===>>>>> 打印第27行的数据内容，n代表number，c代表char，t代表date rowdata[0] @383 ---------- flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@384: 0x01 cols@385: 18===>>>>> 共18列
col 0[2] @386: 1 col 1[4] @389: 94098 ==>>>>> 需要修改该列的值 col 2[1] @394: 0 col 3[12] @396: _NEXT_OBJECT col 4[2] @409: 1 col 5[0] @412: *NULL* col 6[1] @413: 0 col 7[7] @415: 2011-09-17 09:46:13 col 8[7] @423: 2017-09-20 18:28:36 col 9[7] @431: 2011-09-17 09:46:13 col 10[1] @439: 0 col 11[0] @441: *NULL* col 12[0] @442: *NULL* col 13[1] @443: 0 col 14[0] @445: *NULL* col 15[1] @446: 0 col 16[4] @448: 65535 col 17[1] @453: 0
BBED> set offset 389 OFFSET 389
BBED> d /v count 32 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 241 Offsets: 389 to 420 Dba:0x00000000 ------------------------------------------------------- 04c30a29 6301800c 5f4e4558 545f4f42 l ...)c..._NEXT_OB 4a454354 02c102ff 01800778 6f09110a l JECT.......xo...
<16 bytes="" per="" line="">
BBED>
|
当然，也可以使用find来直接查询：
BBED> f /x c30a29 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 241 Offsets: 390 to 421 Dba:0x00000000 ------------------------------------------------------------------------ c30a2963 01800c5f 4e455854 5f4f424a 45435402 c102ff01 8007786f 09110a2f
<32 bytes="" per="" line="">
BBED> f BBED-00212: search string not found
|
94098和94099对应的存储格式：
SYS@ora11g > select dump(94098,16),dump(94099,16) from dual;
DUMP(94098,16) DUMP(94099,16) ----------------------- ----------------------- Typ=2 Len=4: c3,a,29,63 Typ=2 Len=4: c3,a,29,64
|
使用bbed 修改04c30a2963为04c30a2964，即把94098修改为94099，如下所示：
BBED> set offset +2 OFFSET 392
BBED> d File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------------------------ 29630180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786f0911 0a2f0e07
<32 bytes="" per="" line="">
BBED> m /x 2964 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------------------------ 29640180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786f0911 0a2f0e07
<32 bytes="" per="" line="">
BBED> d /v File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------- 29640180 0c5f4e45 58545f4f 424a4543 l )d..._NEXT_OBJEC 5402c102 ff018007 786f0911 0a2f0e07 l T.......xo.../..
<16 bytes="" per="" line="">
BBED> sum Check value for File 0, Block 241: current = 0xc3cc, required = 0xc4cc
BBED> sum apply Check value for File 0, Block 241: current = 0xc4cc, required = 0xc4cc
BBED> v DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11g/system01.dbf BLOCK = 241
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> set offset 383 OFFSET 383
BBED> x /rnnncncntttnccncnnn rowdata[0] @383 ---------- flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@384: 0x01 cols@385: 18
col 0[2] @386: 1 col 1[4] @389: 94099 ==>>>>> 已修改 col 2[1] @394: 0 col 3[12] @396: _NEXT_OBJECT col 4[2] @409: 1 col 5[0] @412: *NULL* col 6[1] @413: 0 col 7[7] @415: 2011-09-17 09:46:13 col 8[7] @423: 2017-09-20 18:28:36 col 9[7] @431: 2011-09-17 09:46:13 col 10[1] @439: 0 col 11[0] @441: *NULL* col 12[0] @442: *NULL* col 13[1] @443: 0 col 14[0] @445: *NULL* col 15[1] @446: 0 col 16[4] @448: 65535 col 17[1] @453: 0 |
可以看到成功的将94098修改为94099。
2.3.2.3 重启数据库
重现在obj$的I_OBJ4 index上报ORA-8102错误,而且不能创建新对象。
SYS@ora11g > create table test_8102_lhr as select * from dba_users;
Table created.==>>>>> 需要重启数据库
SYS@ora11g > startup force ORACLE instance started.
Total System Global Area 409194496 bytes Fixed Size 2228864 bytes Variable Size 306187648 bytes Database Buffers 92274688 bytes Redo Buffers 8503296 bytes Database mounted. Database opened. SYS@ora11g > SYS@ora11g > create table test_8102_lhr_01 as select * from dba_users; create table test_8102_lhr_01 as select * from dba_users * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)
SYS@ora11g > col OBJECT_NAME for a30 SYS@ora11g > select object_name,object_type from dba_objects where object_id=39;
OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_OBJ4 INDEX
==>>>>>39号为I_OBJ4这个索引，在块94093上没有找到键值。
|
2.3.2.4 errorstack的trace文件分析
告警日志会生成errorstack：
Thu Sep 21 10:57:39 2017 Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_46780.trc: Thu Sep 21 10:57:41 2017 Dumping diagnostic data in directory=[cdmp_20170921105741], requested by (instance=1, osid=46780), summary=[abnormal process termination].
|
从日志文件中可以看到：
*** 2017-09-21 10:57:39.849 oer 8102.2 - obj# 39, rdba: 0x00416f83(afn 1, blk# 94083)<<<<<<===发生错误的对象为39号对象，1号文件，94083号块，rdba（relative data block address）表示相对数据块地址，计算方式参考：http://blog.itpub.net/26736162/viewspace-2141499/ kdk key 8102.2: ncol: 4, len: 16<<<<<<===共4列，长度为16字节 key: (16): 04 c3 0a 29 64 01 80 01 80 06 00 40 00 f1 00 1b <<<<<<===在索引中找不到键值04 c3 0a 29 64（转换为10进制即94099），其中04代表行的长度，后面则是列的内容。由于该索引共有3列（DATAOBJ#, TYPE#, OWNER#），所以，04 c3 0a 29 64代表DATAOBJ#，“01 80”代表TYPE#，接下来的“01 80”代表OWNER#，剩下的“06 00 40 00 f1 00 1b”代表该行的ROWID，06代表长度。索引中的ROWID=文件号（2进制前10位）+块号（2进制22位）+行号（2进制16位） 00 40 00 f1 00 1b（16进制）= 000000000100000000000000111100010000000000011011（2进制） 文件号=（0000000001）取前10位的二进制=第1号文件 块号=（0000000000000011110001）取前2进制22位==>第241号 行号=（0000000000011011）取2进制16位==>第27行 这个结果和之前查询出来的结果一致。 mask: (4096): 。。。。。。。。省略。。。。。。。。 *** 2017-09-21 10:57:39.850 dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ----- Current SQL Statement for this session (sql_id=4yyb4104skrwj) ----- update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14 where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
。。。。。。。。省略。。。。。。。。
Block header dump: 0x00416f83 Object id on Block? Y seg/obj: 0x27 csc: 0x00.38f9bc9 itc: 3 flg: O typ: 2 - INDEX<<<<<<===注意：块中可能有多个索引，所以需要使用“seg/obj: 0x27”来进行判断，这里0x27表示39号对象。 fsl: 0 fnx: 0x416f84 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc 0x01 0x0009.01f.00000f09 0x00c001f6.02d1.01 CB-- 0 scn 0x0000.038326d7 0x02 0x000a.009.0000098a 0x00c0030c.01ff.14 ---- 1 fsc 0x0000.00000000 0x03 0x0006.008.00000bf2 0x00c002df.0203.16 --U- 1 fsc 0x0000.038f9bdc Leaf block dump =============== header address 1857765492=0x6ebb4074 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 4 kdxcosdc 1 kdxconro 297 kdxcofbo 630=0x276 kdxcofeo 1722=0x6ba kdxcoavs 1398 kdxlespl 0 kdxlende 0 kdxlenxt 4213578=0x404b4a kdxleprv 4288386=0x416f82 kdxledsz 0 kdxlebksz 8008 row#0[7987] flag: ------, lock: 0, len=21 col 0; len 4; (4): c3 0a 20 26 col 1; len 2; (2): c1 23 col 2; len 3; (3): c2 02 26 col 3; len 6; (6): 00 41 6a b1 00 18 。。。。。。。。省略。。。。。。。。 row#284[1861] flag: ------, lock: 0, len=18 col 0; len 4; (4): c3 0a 29 63<<<<<<===索引中存储的是c3 0a 29 63 col 1; len 1; (1): 80 col 2; len 1; (1): 80 col 3; len 6; (6): 00 40 00 f1 00 1b<<<<<<===索引中的ROWID 。。。。。。。。省略。。。。。。。。 row#296[2302] flag: ------, lock: 0, len=15 col 0; NULL col 1; len 2; (2): c1 02 col 2; len 1; (1): 80 col 3; len 6; (6): 00 40 2e 9f 00 50 ----- end of leaf block dump ----- 。。。。。。。。省略。。。。。。。。 |
另外通过相关trace发现，在创建表操作中会调用update obj$的一个递归操作，而该操作会更新dataobj#，但是由于该值在表和index中不匹配，因此出现ORA-08102导致创建表不成功。也可以通过如下的SQL生成errorstack：
ALTER SESSION SET EVENTS '8102 trace name errorstack level 3'; create table t1 as select * from dual; select value from v$diag_info where name='Default Trace File'; |
在trace中，需要从索引块中读取c30a2964：
LHR@ora11g > select utl_raw.cast_to_number('c30a2964') from dual;
UTL_RAW.CAST_TO_NUMBER('C30A2964') ---------------------------------- 94099 |
2.3.3 模拟一：BBED修复ORA-08102错误
2.3.3.1 使用全表扫和全索引扫找到键值不一致的地方
SYS@ora11g > create table test_8102_lhr_01 as select * from dba_users; create table test_8102_lhr_01 as select * from dba_users * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)
SYS@ora11g > col OBJECT_NAME for a30 SYS@ora11g > select object_name,object_type from dba_objects where object_id=39;
OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_OBJ4 INDEX
SYS@ora11g > set autot on SYS@ora11g > set line 9999 SYS@ora11g > SYS@ora11g > select /*+ index(t i_obj4) */ DATAOBJ# from sys.obj$ t 2 minus 3 select /*+ full(t1) */ DATAOBJ# from sys.obj$ t1;
DATAOBJ# ---------- 94098
Execution Plan ---------------------------------------------------------- Plan hash value: 1107706645
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 85519 | 334K| | 1116 (47)| 00:00:14 | | 1 | MINUS | | | | | | | | 2 | SORT UNIQUE NOSORT| | 85519 | 167K| | 603 (1)| 00:00:08 | | 3 | INDEX FULL SCAN | I_OBJ4 | 85519 | 167K| | 386 (0)| 00:00:05 | | 4 | SORT UNIQUE | | 85519 | 167K| 680K| 512 (1)| 00:00:07 | | 5 | TABLE ACCESS FULL| OBJ$ | 85519 | 167K| | 295 (1)| 00:00:04 | --------------------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 1475 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
SYS@ora11g > select /*+ full(t1) */ DATAOBJ# from sys.obj$ t1 2 minus 3 select /*+ index(t i_obj4) */ DATAOBJ# from sys.obj$ t 4 ;
DATAOBJ# ---------- 94099
Execution Plan ---------------------------------------------------------- Plan hash value: 321946325
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 85519 | 334K| | 1116 (55)| 00:00:14 | | 1 | MINUS | | | | | | | | 2 | SORT UNIQUE | | 85519 | 167K| 680K| 512 (1)| 00:00:07 | | 3 | TABLE ACCESS FULL| OBJ$ | 85519 | 167K| | 295 (1)| 00:00:04 | | 4 | SORT UNIQUE NOSORT| | 85519 | 167K| | 603 (1)| 00:00:08 | | 5 | INDEX FULL SCAN | I_OBJ4 | 85519 | 167K| | 386 (0)| 00:00:05 | --------------------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1474 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed |
即表中记录的是94099，而索引中记录的是94098：
SYS@ora11g > set autot off SYS@ora11g > SYS@ora11g > select /*+ full(t) */ DATAOBJ#,type#,owner# from sys.obj$ t WHERE t.dataobj# IN (94098,94099);
DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 94099 0 0
SYS@ora11g > select /*+ full(t i_obj4) */ DATAOBJ#,type#,owner# from sys.obj$ t WHERE t.dataobj# IN (94098,94099);
DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 94098 0 0
|
2.3.3.2 使用bbed 修复ORA-8102
SYS@ora11g > select dump(94098,16),dump(94099,16) from dual;
DUMP(94098,16) DUMP(94099,16) ----------------------- ----------------------- Typ=2 Len=4: c3,a,29,63 Typ=2 Len=4: c3,a,29,64
|
[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 11:16:34 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 94083 BLOCK# 94083 BBED> map File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Index Leaf)<<<<===索引块
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdxle, 32 bytes @116
sb2 kd_off[297] @148
ub1 freespace[1092] @742
ub1 rowdata[6286] @1834
ub4 tailchk @8188
BBED> f /x 0a2963 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Offsets: 1981 to 2492 Dba:0x00000000 ------------------------------------------------------------------------ 0a296301 80018006 004000f1 001b0000 04c30a29 5b02c103 02c15b06 00416abb 00110000 04c30a29 5c02c102 02c15b06 00416abb 00100100 04c30a29 5c02c102 02c15b06 00416abb 000d0100 04c30a29 5b02c103 02c15b06 00416abb 000f0000 04c30a29 5a02c103 02c15b06 00416abb 000e0100 04c30a29 5a02c103 02c15b06 00416abb 000d0000 04c30a28 6202c103 02c15b06 00416abb 000c0000 04c30a28 6302c102 02c15b06 00416abb 000b0000 04c30a29 5902c103 01800600 416abb00 0a010004 c30a295e 01800180 06004000 f1001b00 0004c30a 283102c1 15018006 00416abb 00090000 04c30a29 5102c115 01800600 416abb00 08000004 c30a294f 02c11401 80060041 6abb0007 010004c3 0a295901 80018006 004000f1 001b0000 04c30a28 2d02c115 01800600 416abb00 06000004 c30a294d 02c11501 80060041 6abb0005 000004c3 0a294b02 c1140180 0600416a bb000400 0004c30a 282902c1 15018006 00416abb 00030000 04c30a29 4902c115 01800600 416abb00 02010004 c30a294e 01800180 06004000 f1001b00 0004c30a 294702c1 14018006 00416abb 00010000 04c30a28 2502c115 01800600 416abb00 000000ff 02c10201 80060040 2e9f0050 0000ff02 c1020180 0600402e 9f004800 00ff02c1 02018006 00402e9f 003e0000 ff02c102 01800600 402e9f00 380000ff 02c10201 80060040 2e9f0032
<32 bytes="" per="" line="">
BBED> f BBED-00212: search string not found <<<<===只找到1个，说明位置就是这里
BBED> set offset -2 OFFSET 1979 BBED> d /v count 32 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Offsets: 1979 to 2010 Dba:0x00000000 ------------------------------------------------------- 04c30a29 63018001 80060040 00f1001b l ...)c......@.... 000004c3 0a295b02 c10302c1 5b060041 l .....)[.....[..A
<16 bytes="" per="" line=""> BBED> set offset +4 OFFSET 1983
BBED> d /v File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Offsets: 1983 to 2014 Dba:0x00000000 ------------------------------------------------------- 63018001 80060040 00f1001b 000004c3 l c......@........ 0a295b02 c10302c1 5b060041 6abb0011 l .)[.....[..Aj...
<16 bytes="" per="" line="">
BBED> m /x 64 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Offsets: 1983 to 2014 Dba:0x00000000 ------------------------------------------------------------------------ 64018001 80060040 00f1001b 000004c3 0a295b02 c10302c1 5b060041 6abb0011
<32 bytes="" per="" line=""> BBED> sum Check value for File 0, Block 94083: current = 0xb4e3, required = 0xb3e3
BBED> sum apply Check value for File 0, Block 94083: current = 0xb3e3, required = 0xb3e3
BBED> set offset 0 OFFSET 0
BBED> f /x 0a2964 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Offsets: 1981 to 2012 Dba:0x00000000 ------------------------------------------------------------------------ 0a296401 80018006 004000f1 001b0000 04c30a29 5b02c103 02c15b06 00416abb
<<<<===已修改成和表中的数据一致 <32 bytes="" per="" line=""> BBED> f BBED-00212: search string not found BBED> v DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11g/system01.dbf BLOCK = 94083
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): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 ====>>>>> 若该块被标识为坏块，则需要修改offset为8188处为01 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED BBED> set offset 8188 OFFSET 8188
BBED> d File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 94083 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 0106aa4c
<32 bytes="" per="" line=""> |
2.3.3.3 验证
SYS@ora11g > create table t1 as select * from dual; create table t1 as select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-08102: index key not found, obj# 39, file 1, block 94083 (2)
SYS@ora11g > startup force;===>>>>> 慎用 ORACLE instance started.
Total System Global Area 409194496 bytes Fixed Size 2228864 bytes Variable Size 306187648 bytes Database Buffers 92274688 bytes Redo Buffers 8503296 bytes Database mounted. Database opened. SYS@ora11g > create table t1 as select * from dual;
Table created.
SYS@ora11g > insert into t1 select * from dual;
1 row created.
SYS@ora11g > commit;
Commit complete.
SYS@ora11g > select * from t1;
D - X X
|
OK，搞定。
2.3.4 模拟二：利用con$做模拟
看了郭一军大师的博客，也可以使用con$表来模拟ORA-08102错误，所以，小麦苗也尝试了一把。这个模拟实验就不详细解释了。
con$表是Oracle数据库中约束的基表，
SELECT * FROM Dba_Dependencies d WHERE d.name='DBA_CONSTRAINTS' AND d.owner='SYS' AND D.referenced_type='TABLE' ;
参考文档是郭一军老师的：http://blog.chinaunix.net/uid-28460966-id-4584119.html
DROP TABLE T_ORA8102_LHR; CREATE TABLE T_ORA8102_LHR AS SELECT * FROM USER_OBJECTS; CREATE UNIQUE INDEX IDX_ORA8102_LHR ON T_ORA8102_LHR(OBJECT_ID); ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID); LHR@ora11g > SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME='T_ORA8102_LHR';
CONSTRAINT_NAME ------------------------------ SYS_C0018168
LHR@ora11g > SELECT MAX(CON#) FROM SYS.CON$;
MAX(CON#) ---------- 18169
LHR@ora11g > SELECT D.NAME, 2 DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#, 3 DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#, 4 DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) ROW# 5 FROM SYS.CON$ D 6 WHERE D.CON# = 18169;
NAME FILE# BLOCK# ROW# ------------------------------ ---------- ---------- ---------- _NEXT_CONSTRAINT 1 289 12
LHR@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.CON$ D 5 WHERE DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) = 1 6 AND DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) = 289;
COUNTS MAX_ROWNUM MIN_ROWNUM ---------- ---------- ---------- 311 310 0
|
SELECT D.* FROM SYS.CON$ D WHERE D.CON# = 18169;
[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 13:31:03 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 289 BLOCK# 289
BBED> map File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 289 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Table/Cluster)
struct kcbh, 20 bytes @0
struct ktbbh, 72 bytes @20
struct kdbh, 14 bytes @92
struct kdbt[1], 4 bytes @106
sb2 kdbr[311] @110
ub1 freespace[675] @732
ub1 rowdata[6781] @1407
ub4 tailchk @8188
BBED> p kdbr sb2 kdbr[0] @110 8077 sb2 kdbr[1] @112 8057 sb2 kdbr[2] @114 8038 sb2 kdbr[3] @116 8016 sb2 kdbr[4] @118 7994 sb2 kdbr[5] @120 7972 sb2 kdbr[6] @122 7952 sb2 kdbr[7] @124 7932 sb2 kdbr[8] @126 7910 sb2 kdbr[9] @128 7888 sb2 kdbr[10] @130 7868 sb2 kdbr[11] @132 7848 sb2 kdbr[12] @134 1315 sb2 kdbr[13] @136 7826 。。。。。。。省略部分内容。。。。。。。。。 sb2 kdbr[303] @716 1640 sb2 kdbr[304] @718 1618 sb2 kdbr[305] @720 1596 sb2 kdbr[306] @722 1574 sb2 kdbr[307] @724 1552 sb2 kdbr[308] @726 1530 sb2 kdbr[309] @728 1508 sb2 kdbr[310] @730 1486
BBED> p *kdbr[12] rowdata[0] ---------- ub1 rowdata[0] @1407 0x2c
BBED> show offset OFFSET 1407
BBED> x /rncnn rowdata[0] @1407 ---------- flag@1407: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@1408: 0x00 cols@1409: 4
col 0[1] @1410: 0 col 1[16] @1412: _NEXT_CONSTRAINT col 2[4] @1429: 18169 col 3[1] @1434: 0
BBED> d /v offset 1429 count 16 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 289 Offsets: 1429 to 1444 Dba:0x00000000 ------------------------------------------------------- 04c30252 4601802c 00040180 105f4e45 l ...RE..,....._NE
<16 bytes="" per="" line="">
|
LHR@ora11g > select dump(18169,16),dump(18170,16) from dual;
DUMP(18169,16) DUMP(18170,16) ----------------------- ----------------------- Typ=2 Len=4: c3,2,52,46 Typ=2 Len=4: c3,2,52,47
|
BBED> set offset +4 OFFSET 1433
BBED> d File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 289 Offsets: 1433 to 1448 Dba:0x00000000 ------------------------------------------------------------------------ 4601802c 00040180 105f4e45 58545f43
<32 bytes="" per="" line="">
BBED> m /x 47 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 289 Offsets: 1433 to 1448 Dba:0x00000000 ------------------------------------------------------------------------ 4701802c 00040180 105f4e45 58545f43
<32 bytes="" per="" line="">
BBED> sum Check value for File 0, Block 289: current = 0xeeee, required = 0xecee
BBED> sum apply Check value for File 0, Block 289: current = 0xecee, required = 0xecee
BBED> v DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/ora11g/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> p *kdbr[12] rowdata[0] ---------- ub1 rowdata[0] @1407 0x2c
BBED> x /rccnn rowdata[0] @1407 ---------- flag@1407: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@1408: 0x00 cols@1409: 4
col 0[1] @1410: . col 1[16] @1412: _NEXT_CONSTRAINT col 2[4] @1429: 18170 col 3[1] @1434: 0
|
SYS@ora11g > startup force ORACLE instance started.
Total System Global Area 409194496 bytes Fixed Size 2228864 bytes Variable Size 314576256 bytes Database Buffers 83886080 bytes Redo Buffers 8503296 bytes Database mounted. Database opened. SYS@ora11g > conn lhr/lhr Connected. LHR@ora11g > ALTER TABLE T_ORA8102_LHR DROP PRIMARY KEY;
Table altered.
LHR@ora11g > ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID); ALTER TABLE T_ORA8102_LHR ADD 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 93040 (2)
LHR@ora11g > SELECT d.OBJECT_NAME,d.OBJECT_TYPE FROM Dba_Objects d WHERE d.OBJECT_ID=52;
OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_CON2 INDEX
LHR@ora11g > SELECT * FROM SYS.BOOTSTRAP$ D WHERE D.OBJ#=52;
LINE# OBJ# SQL_TEXT ---------- ---------- ----------------------------------------------- 52 52 CREATE UNIQUE INDEX I_CON2 ON CON$(CON#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 52 EXTENTS (FILE 1 BLOCK 464))
LHR@ora11g > set long 9999 LHR@ora11g > select dbms_metadata.get_ddl('INDEX','I_CON2','SYS') from dual;
DBMS_METADATA.GET_DDL('INDEX','I_CON2','SYS') --------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."I_CON2" ON "SYS"."CON$" ("CON#") PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM"
|
告警日志：
Thu Sep 21 14:18:32 2017 Errors in file /u01/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_31964.trc: Thu Sep 21 14:18:33 2017 Dumping diagnostic data in directory=[cdmp_20170921141833], requested by (instance=1, osid=31964), summary=[abnormal process termination].
|
找到：
oer 8102.2 - obj# 52, rdba: 0x00416b70(afn 1, blk# 93040) kdk key 8102.2: ncol: 1, len: 5 key: (5): 04 c3 02 52 47<<<<===在索引中找不到键值04 c3 02 52 47（转换为10进制即18170）
mask: (4096): 。。。。。。。。。省略。。。。。。。。
*** 2017-09-21 14:18:32.488 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 。。。。。。。。。省略。。。。。。。。 Block header dump: 0x00416b70<<<<===搜Block header dump Object id on Block? Y seg/obj: 0x34 csc: 0x00.39125d6 itc: 3 flg: O typ: 2 - INDEX<<<<===找到52号对象 fsl: 0 fnx: 0x416b71 ver: 0x01 。。。。。。。。。省略。。。。。。。。 row#447[966] flag: ------, lock: 0, len=13, data:(6): 00 41 69 a4 00 d9 col 0; len 4; (4): c3 02 52 44 row#448[940] flag: ---D--, lock: 3, len=13, data:(6): 00 41 69 a4 00 da col 0; len 4; (4): c3 02 52 45 row#449[953] flag: ------, lock: 0, len=13, data:(6): 00 40 01 21 00 0c====>>>>> ROWID col 0; len 4; (4): c3 02 52 46====>>>>> 索引中存储的最大键值是18169 ----- end of leaf block dump ----- ---------------------------------------- SO: 0x7624b240, type: 56, owner: 0x77896d88, flag: INIT/-/-/0x00 if: 0x3 c: 0x3 proc=0x77c98660, name=transaction, file=ktccts.h LINE:410, pg=0
|
注意索引中的ROWID（2进制共48位）=文件号（2进制前10位）+块号（2进制22位）+行号（2进制16位）
00 40 01 21 00 0c（16进制）= 000000000100000000000001001000010000000000001100（2进制）
文件号=（0000000001）取前10位的二进制=第1号文件
块号=（0000000000000100100001）取前2进制22位==>第289号
行号=（0000000000001100）取2进制16位==>第12行
LHR@ora11g > select dump(18169,16),dump(18170,16) from dual;
DUMP(18169,16) DUMP(18170,16) ----------------------- ----------------------- Typ=2 Len=4: c3,2,52,46 Typ=2 Len=4: c3,2,52,47
LHR@ora11g > select utl_raw.cast_to_number('c3025247'),utl_raw.cast_to_number('c3025246') from dual;
UTL_RAW.CAST_TO_NUMBER('C3025247') UTL_RAW.CAST_TO_NUMBER('C3025246') ---------------------------------- ---------------------------------- 18170 18169 |
ORA-8102常见于索引键值与表上存的值不一致。(Corruption related to Index 索引)
ORA- 8102即可能是ORACLE的bug，也可能是由于硬件I/O错误所引起。硬件或者I/O子系统由于丢失写 Lost Write造成块的逻辑上讹误，当一个Lost Io发生，包含对key的修改或者没有写入到ORACLE数据文件上，这即可能发生在表块上也可能发生在索引块上。
查看这个对象号为52的对象，发现是SYS.CON$表中I_CON2索引，这是一个BOOTSTRAP$对象，而且这是一个核心BOOTSTRP$对象，是不能通过startup migrate和event 38003重建的，所以最终只能通过BBED去修改这个块中有问题的地方。
[root@rhel6lhr ~]# oerr ora 38003 38003, 00000, "CBO Disable column stats for the dictionary objects in recursive SQL" // *Cause: // *Action: [root@rhel6lhr ~]# |
先通过下面这个SQL，查找表和索引之间到底相差什么
LHR@ora11g > set autot on LHR@ora11g > LHR@ora11g > select /*+ index(t I_CON2) */ T.CON# from sys.CON$ t 2 minus 3 select /*+ full(t1) */ T1.CON# from sys.CON$ t1;
CON# ---------- 18169
Execution Plan ---------------------------------------------------------- Plan hash value: 2332423480
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16963 | 165K| | 163 (48)| 00:00:02 | | 1 | MINUS | | | | | | | | 2 | SORT UNIQUE NOSORT| | 16963 | 84815 | | 88 (3)| 00:00:02 | | 3 | INDEX FULL SCAN | I_CON2 | 16963 | 84815 | | 32 (0)| 00:00:01 | | 4 | SORT UNIQUE | | 16963 | 84815 | 208K| 76 (3)| 00:00:01 | | 5 | TABLE ACCESS FULL| CON$ | 16963 | 84815 | | 20 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 102 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
LHR@ora11g > select /*+ full(t1) */ T1.CON# from sys.CON$ t1 2 minus 3 select /*+ index(t I_CON2) */ T.CON# from sys.CON$ t 4 ;
CON# ---------- 18170
Execution Plan ---------------------------------------------------------- Plan hash value: 2017867816
-------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 16963 | 165K| | 163 (55)| 00:00:02 | | 1 | MINUS | | | | | | | | 2 | SORT UNIQUE | | 16963 | 84815 | 208K| 76 (3)| 00:00:01 | | 3 | TABLE ACCESS FULL| CON$ | 16963 | 84815 | | 20 (0)| 00:00:01 | | 4 | SORT UNIQUE NOSORT| | 16963 | 84815 | | 88 (3)| 00:00:02 | | 5 | INDEX FULL SCAN | I_CON2 | 16963 | 84815 | | 32 (0)| 00:00:01 | --------------------------------------------------------------------------------------
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 102 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed
LHR@ora11g > select /*+ full(t) */ T.CON# from sys.CON$ t WHERE t.CON# IN (18169,18170);
CON# ---------- 18170
Execution Plan ---------------------------------------------------------- Plan hash value: 3767504726
-------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 20 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| CON$ | 2 | 10 | 20 (0)| 00:00:01 | --------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("T"."CON#"=18169 OR "T"."CON#"=18170)
Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 77 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 1 rows processed
LHR@ora11g > select /*+ full(t I_CON2) */ T.CON# from sys.CON$ t WHERE t.CON# IN (18169,18170);
CON# ---------- 18169
Execution Plan ---------------------------------------------------------- Plan hash value: 1461913314
----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 10 | 3 (0)| 00:00:01 | | 1 | INLIST ITERATOR | | | | | | |* 2 | INDEX UNIQUE SCAN| I_CON2 | 2 | 10 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access("T"."CON#"=18169 OR "T"."CON#"=18170)
Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 524 bytes sent via SQL*Net to client 520 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed |
利用这样的方法来查询表和索引之间的不一致，通过查询结果确实可以看到表和索引存在不一样的，表里是18170，而索引里存储的是18169，说明索引键值与表上存的值不一致
那么怎么办呢？如何处理这个问题？我们直接对索引进行rebuild，看行不行？
SYS@ora11g > alter index sys.I_CON2 rebuild; alter index sys.I_CON2 rebuild * ERROR at line 1: ORA-00701: object necessary for warmstarting database cannot be altered
LHR@ora11g > select dump(18169,16),dump(18170,16) from dual;
DUMP(18169,16) DUMP(18170,16) ----------------------- ----------------------- Typ=2 Len=4: c3,2,52,46 Typ=2 Len=4: c3,2,52,47
LHR@ora11g > select utl_raw.cast_to_number('c3025247'),utl_raw.cast_to_number('c3025246') from dual;
UTL_RAW.CAST_TO_NUMBER('C3025247') UTL_RAW.CAST_TO_NUMBER('C3025246') ---------------------------------- ---------------------------------- 18170 18169 |
[oracle@rhel6lhr ~]$ bbed password=blockedit blocksize=8192 mode=edit filename='/u01/app/oracle/oradata/ora11g/system01.dbf'
BBED: Release 2.0.0.0.0 - Limited Production on Thu Sep 21 16:08:05 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set block 93040 BLOCK# 93040
BBED> map File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 93040 Dba:0x00000000 ------------------------------------------------------------ KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
struct ktbbh, 96 bytes @20
struct kdxle, 32 bytes @116
sb2 kd_off[450] @148
ub1 freespace[4] @1048
ub1 rowdata[7068] @1052
ub4 tailchk @8188
BBED> f /x 025246 File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 93040 Offsets: 1079 to 1590 Dba:0x00000000 ------------------------------------------------------------------------ 02524600 00004169 a400d904 c3025244 01000040 0121000c 04c30252 45000000 4169a400 d804c302 52430100 00400121 000c04c3 02524400 00004169 a400d704 c3025242 01000040 0121000c 04c30252 43000000 4169a400 d604c302 52410100 00400121 000c04c3 02524200 00004169 a400d504 c3025240 01000040 0121000c 04c30252 41000000 4169a400 d404c302 523f0100 00400121 000c04c3 02524000 00004169 a400d304 c302523e 01000040 0121000c 04c30252 3f010000 4169a400 d204c302 523d0100 00400121 000c04c3 02523e01 00004169 a400d104 c302523c 01000040 0121000c 04c30252 3d010000 4169a400 d004c302 523b0100 00400121 000c04c3 02523c01 00004169 a400ce04 c302523a 01000040 0121000c 04c30252 3b010000 4169a400 cd04c302 52390100 00400121 000c04c3 02523a01 00004169 a400cc04 c3025238 01000040 0121000c 04c30252 39010000 4169a400 cf04c302 52370100 00400121 000c04c3 02523801 00004169 a400ce04 c3025236 01000040 0121000c 04c30252 37010000 4169a400 cd04c302 52350100 00400121 000c04c3 02523601 00004169 a400cc04 c3025234 01000040 0121000c 04c30252 35010000 4169a400 d004c302 52330100 00400121 000c04c3 02523401 00004169 a400cf04 c3025232 01000040 0121000c 04c30252 33010000 4169a400 ce04c302 52310100
<32 bytes="" per="" line="">
BBED> f BBED-00212: search string not found
BBED> d File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 93040 Offsets: 1079 to 1590 Dba:0x00000000 ------------------------------------------------------------------------ 02524600 00004169 a400d904 c3025244 01000040 0121000c 04c30252 45000000 4169a400 d804c302 52430100 00400121 000c04c3 02524400 00004169 a400d704 c3025242 01000040 0121000c 04c30252 43000000 4169a400 d604c302 52410100 00400121 000c04c3 02524200 00004169 a400d504 c3025240 01000040 0121000c 04c30252 41000000 4169a400 d404c302 523f0100 00400121 000c04c3 02524000 00004169 a400d304 c302523e 01000040 0121000c 04c30252 3f010000 4169a400 d204c302 523d0100 00400121 000c04c3 02523e01 00004169 a400d104 c302523c 01000040 0121000c 04c30252 3d010000 4169a400 d004c302 523b0100 00400121 000c04c3 02523c01 00004169 a400ce04 c302523a 01000040 0121000c 04c30252 3b010000 4169a400 cd04c302 52390100 00400121 000c04c3 02523a01 00004169 a400cc04 c3025238 01000040 0121000c 04c30252 39010000 4169a400 cf04c302 52370100 00400121 000c04c3 02523801 00004169 a400ce04 c3025236 01000040 0121000c 04c30252 37010000 4169a400 cd04c302 52350100 00400121 000c04c3 02523601 00004169 a400cc04 c3025234 01000040 0121000c 04c30252 35010000 4169a400 d004c302 52330100 00400121 000c04c3 02523401 00004169 a400cf04 c3025232 01000040 0121000c 04c30252 33010000 4169a400 ce04c302 52310100
<32 bytes="" per="" line="">
BBED> m /x 025247 Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y File: /u01/app/oracle/oradata/ora11g/system01.dbf (0) Block: 93040 Offsets: 1079 to 1590 Dba:0x00000000 ------------------------------------------------------------------------ 02524700 00004169 a400d904 c3025244 01000040 0121000c 04c30252 45000000 4169a400 d804c302 52430100 00400121 000c04c3 02524400 00004169 a400d704 c3025242 01000040 0121000c 04c30252 43000000 4169a400 d604c302 52410100 00400121 000c04c3 02524200 00004169 a400d504 c3025240 01000040 0121000c 04c30252 41000000 4169a400 d404c302 523f0100 00400121 000c04c3 02524000 00004169 a400d304 c302523e 01000040 0121000c 04c30252 3f010000 4169a400 d204c302 523d0100 00400121 000c04c3 02523e01 00004169 a400d104 c302523c 01000040 0121000c 04c30252 3d010000 4169a400 d004c302 523b0100 00400121 000c04c3 02523c01 00004169 a400ce04 c302523a 01000040 0121000c 04c30252 3b010000 4169a400 cd04c302 52390100 00400121 000c04c3 02523a01 00004169 a400cc04 c3025238 01000040 0121000c 04c30252 39010000 4169a400 cf04c302 52370100 00400121 000c04c3 02523801 00004169 a400ce04 c3025236 01000040 0121000c 04c30252 37010000 4169a400 cd04c302 52350100 00400121 000c04c3 02523601 00004169 a400cc04 c3025234 01000040 0121000c 04c30252 35010000 4169a400 d004c302 52330100 00400121 000c04c3 02523401 00004169 a400cf04 c3025232 01000040 0121000c 04c30252 33010000 4169a400 ce04c302 52310100
<32 bytes="" per="" line="">
BBED> sum Check value for File 0, Block 93040: current = 0xde85, required = 0xdf85
BBED> sum apply Check value for File 0, Block 93040: current = 0xdf85, required = 0xdf85
|
这个操作完毕后，可以重启数据库，然后测一下建主键。
SYS@ora11g > conn lhr/lhr Connected. LHR@ora11g > ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID); ALTER TABLE T_ORA8102_LHR ADD 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 93040 (2)
LHR@ora11g > conn / as sysdba Connected. SYS@ora11g > startup force ORACLE instance started.
Total System Global Area 409194496 bytes Fixed Size 2228864 bytes Variable Size 314576256 bytes Database Buffers 83886080 bytes Redo Buffers 8503296 bytes Database mounted. Database opened.
SYS@ora11g > conn lhr/lhr Connected. LHR@ora11g > ALTER TABLE T_ORA8102_LHR ADD PRIMARY KEY(OBJECT_ID);
Table altered.
|
2.4 实验总结
1、ORA-08102错误发生时会生成一份errorstack的trace文件，从该文件中可以获取不一致的详细信息。该trace文件指的细读几遍。
2、实验中的修复过程都是将索引中的键值修改为表中的值，其实，只要保证这2者一致即可。也可以根据报错信息找到相关的表的ROWID信息，然后使用bbed修改表中的值和索引中的值保持一致即可。--小麦苗已实验通过
2.4.1 模拟一的实验数据展示
使用BBED修改前获取到的数据：
项目 |
值 |
OBJ$上DATAOBJ#列的最大值 |
94098 |
OBJ$上DATAOBJ#列的最大值dump值 |
Typ=2 Len=4: c3,a,29,63 即：04c30a2963 |
该行所在数据块的地址 |
FILE# BLOCK# ROW# ---------- ---------- ---------- 1 241 27 |
该行的存储情况 |
COUNTS MAX_ROWNUM MIN_ROWNUM ---------- ---------- ---------- 105 104 0 |
列名 |
10进制值 |
16进制值 |
dump文件的存储 |
bbed中的存储 |
|
|
|
tab 0, row 27, @0x13b ====>>>>> 该块中第一个表第27行的指针位置，转换为10进制是315 tl: 72 fb: --H-FL-- lb: 0x1 cc: 18 |
rowdata[0] @383 ---------- flag@383: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@384: 0x01 cols@385: 18===>>>>> 共18列 |
OBJ# |
1 |
Typ=2 Len=2: c1,2 |
col 0: [ 2] c1 02 |
col 0[2] @386: 1 |
DATAOBJ# |
94098 |
Typ=2 Len=4: c3,a,29,63 |
col 1: [ 4] c3 0a 29 63 |
col 1[4] @389: 94098 ==>>>>> 需要修改该列的值 |
OWNER# |
0 |
Typ=2 Len=1: 80 |
col 2: [ 1] 80 |
col 2[1] @394: 0 |
NAME |
_NEXT_OBJECT |
Typ=1 Len=12: 5f,4e,45,58,54,5f,4f,42,4a,45,43,54 |
col 3: [12] 5f 4e 45 58 54 5f 4f 42 4a 45 43 54 |
col 3[12] @396: _NEXT_OBJECT |
NAMESPACE |
1 |
Typ=2 Len=2: c1,2 |
col 4: [ 2] c1 02 |
col 4[2] @409: 1 |
SUBNAME |
|
NULL |
col 5: *NULL* |
col 5[0] @412: *NULL* |
TYPE# |
0 |
Typ=2 Len=1: 80 |
col 6: [ 1] 80 |
col 6[1] @413: 0 |
CTIME |
2011/9/17 9:46 |
Typ=12 Len=7: 78,6f,9,11,a,2f,e |
col 7: [ 7] 78 6f 09 11 0a 2f 0e |
col 7[7] @415: 2011-09-17 09:46:13 |
MTIME |
2017/9/20 18:28 |
Typ=12 Len=7: 78,75,9,14,13,1d,25 |
col 8: [ 7] 78 75 09 14 13 1d 25 |
col 8[7] @423: 2017-09-20 18:28:36 |
STIME |
2011/9/17 9:46 |
Typ=12 Len=7: 78,6f,9,11,a,2f,e |
col 9: [ 7] 78 6f 09 11 0a 2f 0e |
col 9[7] @431: 2011-09-17 09:46:13 |
STATUS |
0 |
Typ=2 Len=1: 80 |
col 10: [ 1] 80 |
col 10[1] @439: 0 |
REMOTEOWNER |
|
NULL |
col 11: *NULL* |
col 11[0] @441: *NULL* |
LINKNAME |
|
NULL |
col 12: *NULL* |
col 12[0] @442: *NULL* |
FLAGS |
0 |
Typ=2 Len=1: 80 |
col 13: [ 1] 80 |
col 13[1] @443: 0 |
OID$ |
|
NULL |
col 14: *NULL* |
col 14[0] @445: *NULL* |
SPARE1 |
0 |
Typ=2 Len=1: 80 |
col 15: [ 1] 80 |
col 15[1] @446: 0 |
SPARE2 |
65535 |
Typ=2 Len=4: c3,7,38,24 |
col 16: [ 4] c3 07 38 24 |
col 16[4] @448: 65535 |
SPARE3 |
0 |
Typ=2 Len=1: 80 |
col 17: [ 1] 80 |
col 17[1] @453: 0 |
2.4.2 修改块的标志位
通过bbed修改i_obj4中的dataobj#值使之和obj$中对应值一致。
BED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf BLOCK = 97266
Block 97266 is corrupt Corrupt block relative dba: 0x00417bf2 (file 0, block 97266) Fractured block found during verification Data in bad block: type: 6 format: 2 rdba: 0x00417bf2 last change scn: 0x0000.00102ed8 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x2ed80602 check value in block header: 0x7955 computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined : 1 Total Blocks Processed (Data) : 0 Total Blocks Failing (Data) : 0 Total Blocks Processed (Index): 0 Total Blocks Failing (Index): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 1 Total Blocks Influx : 2 Message 531 not found; product=RDBMS; facility=BBED
BBED> set offset 8188 OFFSET 8188
BBED> d File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 0206d82e
<32 bytes="" per="" line="">
BBED> m /x 01 File: /u01/app/oracle/oradata/xifenfei/system01.dbf (0) Block: 97266 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 0106d82e
<32 bytes="" per="" line="">
BBED> sum Check value for File 0, Block 97266: current = 0x7955, required = 0x7956
BBED> sum apply Check value for File 0, Block 97266: current = 0x7956, required = 0x7956
BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/xifenfei/system01.dbf BLOCK = 97266
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): 0 Total Blocks Empty : 0 Total Blocks Marked Corrupt : 0 Total Blocks Influx : 0 Message 531 not found; product=RDBMS; facility=BBED
|
第三章 参考文章
3.1 博客
4. ORA-08102: index key not found：http://blog.csdn.net/gumengkai/article/details/53229144
5. 通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误：http://www.xifenfei.com/2015/03/%e9%80%9a%e8%bf%87bbed%e4%bf%ae%e6%94%b9obj%e4%b8%addataobj%e9%87%8d%e7%8e%b0i_obj4%e7%b4%a2%e5%bc%95%e6%8a%a5ora-08102%e9%94%99%e8%af%af.html
6. 使用bbed 修复I_OBJ4 index 报ORA-8102错误：http://www.xifenfei.com/2015/03/%e4%bd%bf%e7%94%a8bbed-%e4%bf%ae%e5%a4%8di_obj4-index-%e6%8a%a5ora-8102.html
7. 利用bbed来修复ora-08102错误 ：http://blog.chinaunix.net/uid-28460966-id-4584119.html
8. 跳过obj$坏块方法：http://www.xifenfei.com/2012/01/%E8%B7%B3%E8%BF%87obj%E5%9D%8F%E5%9D%97%E6%96%B9%E6%B3%95.html
9. bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决：http://www.xifenfei.com/2014/10/%E8%A7%A3%E5%86%B3ora-00701-object-necessary-for-warmstarting-database-cannot-be-altered%E9%94%99%E8%AF%AF.html
10. 分享I_OBJ4 ORA-8102故障恢复案例：http://www.xifenfei.com/2015/03/%E5%88%86%E4%BA%ABi_obj4-ora-8102%E6%95%85%E9%9A%9C%E6%81%A2%E5%A4%8D%E6%A1%88%E4%BE%8B.html
11. How to recreate Bootstrap Index(I_OBJ1,I_USER1,I_FILE#_BLOCK#) to fix ORA-00701 ?：
12. bootstrap$核心对象数据不一致导致ORA-08102：http://www.killdb.com/2011/07/30/bootstrap%e6%a0%b8%e5%bf%83%e5%af%b9%e8%b1%a1%e6%95%b0%e6%8d%ae%e4%b8%8d%e4%b8%80%e8%87%b4%e5%af%bc%e8%87%b4ora-08102.html
第四章 实验中用到的SQL总结
SELECT COUNT(*) COUNTS, 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,94099); select /*+ full(t i_obj4) */ DATAOBJ#,type#,owner# from sys.obj$ t WHERE t.dataobj# IN (94098,94099);
--16进制转换为10进制 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
.............................................................................................................................................
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2145368/
● 本文博客园地址：http://www.cnblogs.com/lhrbest/p/7576416.html
● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826
.............................................................................................................................................
● QQ群号：230161599（满）、618766405
● 微信群：可加我微信，我拉大家进群，非诚勿扰
● 联系我请加QQ好友（646634621），注明添加缘由
● 于 2017-09-20 09:00 ~ 2017-09-23 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
.............................................................................................................................................
● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail
● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/
.............................................................................................................................................
使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。
小麦苗的微信公众号 小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店
.............................................................................................................................................
