ORA-08102异常重现及恢复

现象:

在表上面新建主键报ORA-08102的异常:
  1. SQL> alter table t add primary key(id);
  2. alter table t add primary key(id)
  3. *
  4. ERROR at line 1:
  5. ORA-00604: error occurred at recursive SQL level 1
  6. ORA-08102: index key not found, obj# 52, file 1, block 72661 (2)

重现异常:

1、查看基表CON$的_NEXT_CONSTRAINT信息和数据块:
  1. SQL> SELECT OWNER#,NAME,CON# FROM CON$ WHERE NAME='_NEXT_CONSTRAINT';
  2. OWNER# NAME CON#
  3. ---------- ------------------------------ ----------
  4. 0 _NEXT_CONSTRAINT 11222

  1. SQL> set lines 200
  2. SELECT OWNER#,NAME,CON#,
  3. dbms_rowid.ROWID_RELATIVE_FNO(rowid) fno,
  4. dbms_rowid.ROWID_BLOCK_NUMBER(rowid) bno,
  5. dbms_rowid.ROWID_ROW_NUMBER(rowid) rno FROM CON$ WHERE NAME='_NEXT_CONSTRAINT';
  6. OWNER# NAME CON# FNO BNO RNO
  7. ---------- ------------------------------ ---------- ---------- ---------- ----------
  8. 0 _NEXT_CONSTRAINT 11222 1 289 12

记录存储在1号文件、289号block、第12行。

2、使用BBED查看数据块

选择1号文件第289号block:
  1. [oracle@cqfrog bbed]$ bbed parfile=par.txt
  2. BBED> set file 1 block 289
  3. FILE# 1
  4. BLOCK# 289

打印第12行:
  1. BBED> p *kdbr[12]
  2. rowdata[0]
  3. ----------
  4. ub1 rowdata[0] @1207 0x2c

格式化显示:
  1. BBED> x /rccnn
  2. rowdata[0] @1207
  3. ----------
  4. flag@1207: 0x2c (KDRHFL, KDRHFF, KDRHFH)
  5. lock@1208: 0x02
  6. cols@1209: 4
  7. col 0[1] @1210: .
  8. col 1[16] @1212: _NEXT_CONSTRAINT
  9. col 2[4] @1229: 11222
  10. col 3[1] @1234: 0

将offset为1229的信息dump:
  1. BBED> dump /v offset 1229 count 16
  2. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  3. Block: 289 Offsets: 1229 to 1244 Dba:0x00400121
  4. -------------------------------------------------------
  5. 04c3020d 1701802c 00040180 105f4e45 l .......,....._NE
  6. <16 bytes per line>

查看11222的16进制:
  1. SQL> select dump(11222, 16) from dual;
  2. DUMP(11222,16)
  3. ----------------------
  4. Typ=2 Len=4: c3,2,d,17
这里的c3,2,d,17对应dump出来的信息:c3020d 17

3、关闭数据库
必须先关闭数据库, 否则修改不生效。
  1. SQL> shutdown immediate
  2. Database closed.
  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SQL> exit
  6. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  7. With the Partitioning, OLAP, Data Mining and Real Application Testing options

4、修改数据块的值
使用bbed修改数据块的值, 将11222 改大。

移动4位, 或者使用dump /v offset 1233count 16
  1. BBED> dump /v offset +4
  2. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  3. Block: 289 Offsets: 1233 to 1248 Dba:0x00400121
  4. -------------------------------------------------------
  5. 1701802c 00040180 105f4e45 58545f43 l ...,....._NEXT_C
  6. <16 bytes per line>

将17修改为19:
  1. BBED> modify /x 1901802c
  2. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  3. Block: 289 Offsets: 1233 to 1248 Dba:0x00400121
  4. ------------------------------------------------------------------------
  5. 1901802c 00040180 105f4e45 58545f43
  6. <32 bytes per line>
  7. BBED> sum apply
  8. Check value for File 1, Block 289:
  9. current = 0x7fca, required = 0x7fca
  10. BBED> dump /v offset 1233
  11. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  12. Block: 289 Offsets: 1233 to 1248 Dba:0x00400121
  13. -------------------------------------------------------
  14. 1901802c 00040180 105f4e45 58545f43 l ...,....._NEXT_C
  15. <16 bytes per line>

5、启动数据库
  1. SQL> startup
  2. ORACLE instance started.
  3. Total System Global Area 835104768 bytes
  4. Fixed Size 2217952 bytes
  5. Variable Size 549455904 bytes
  6. Database Buffers 276824064 bytes
  7. Redo Buffers 6606848 bytes
  8. Database mounted.
  9. Database opened.


6、查看con$表的信息

使用全表扫描方式:
  1. SQL> select /*+ FULL(t1) */ name,con# from con$ t1 where name='_NEXT_CONSTRAINT';
  2. NAME CON#
  3. ------------------------------ ----------
  4. _NEXT_CONSTRAINT 11224

使用索引方式:
  1. SQL> select /*+ index(t1 I_CON2) */ name,con# from con$ t1 where name='_NEXT_CONSTRAINT';
  2. NAME CON#
  3. ------------------------------ ----------
  4. _NEXT_CONSTRAINT 11222


使用全表扫描和使用索引方式的值相差2。

7、新建测试表并增加主键

  1. SQL> create table t as select * from test where rownum<10;
  2. Table created.
  3. SQL> desc t;
  4. Name Null? Type
  5. ----------------------------------------- -------- ----------------------------
  6. ID NUMBER
  7. NAME VARCHAR2(128


  1. SQL> alter table t add primary key(id);
  2. alter table t add primary key(id)
  3. *
  4. ERROR at line 1:
  5. ORA-00604: error occurred at recursive SQL level 1
  6. ORA-08102: index key not found, obj# 52, file 1, block 72661 (2)

观察发现, 有ORA-8102异常。


修复ORA-08102异常:

1、根据报错信息定位数据块
  1. SQL> alter table t add primary key(id);
  2. alter table t add primary key(id)
  3. *
  4. ERROR at line 1:
  5. ORA-00604: error occurred at recursive SQL level 1
  6. ORA-08102: index key not found, obj# 52, file 1, block 72661 (2)

位置:file 1, block 72661

2、查看索引和全表扫描的信息
  1. SQL> select /*+ FULL(t1) */ name,con# from con$ t1 where name='_NEXT_CONSTRAINT';
  2. NAME CON#
  3. ------------------------------ ----------
  4. _NEXT_CONSTRAINT 11224
  5. SQL> select /*+ index(t1 I_CON2) */ name,con# from con$ t1 where name='_NEXT_CONSTRAINT';
  6. NAME CON#
  7. ------------------------------ ----------
  8. _NEXT_CONSTRAINT 11222


3、dump报错的数据块

根据alert日志或者dump数据块:

  1. SQL> alter system dump datafile 1 block 72661;
  2. System altered.
  3. SQL> select * from v$diag_info; 1 Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6051.trc

在dump文件搜索8102错误:
 可以看到数据块期望的值是:c3 02 0d 19 。11224。
  1. SQL> select UTL_RAW.CAST_TO_NUMBER(replace('c3 02 0d 19',' ','')) from dual;
  2. UTL_RAW.CAST_TO_NUMBER(REPLACE('C3020D19','',''))
  3. -------------------------------------------------
  4. 11224


查看dump的文件:
  1. row#198[3566] flag: ---D--, lock: 2, len=13, data:(6): 00 41 38 6c 00 ad
  2. col 0; len 4; (4): c3 02 0d 15
  3. row#199[3540] flag: ---D--, lock: 2, len=13, data:(6): 00 41 38 6c 00 af
  4. col 0; len 4; (4): c3 02 0d 16
  5. row#200[3553] flag: ------, lock: 0, len=13, data:(6): 00 40 01 21 00 0c
  6. col 0; len 4; (4): c3 02 0d 17
  7. ----- end of leaf block dump -----
  8. End dump data blocks tsn: 0 file#: 1 minblk 72661 maxblk 72661

找到最后, 图上的红色部分:
00 40 01 21 00 0c 是rowid信息:
文件号:00 40  (取前十位,1)
块号:    01 21  (289)
行号:    00 0c   (12)

注意:这里dump的索引的块, 可以看到对应的数据块的rowid。
我们使用bbed可以看到文件1、数据块289、12行的数据是多少, 这里省略了。
我们看到索引存储的值为: c3 02 0d 17转换为10进制为11222。
  1. SQL> select UTL_RAW.CAST_TO_NUMBER(replace('c3 02 0d 17',' ','')) from dual;
  2. UTL_RAW.CAST_TO_NUMBER(REPLACE('C3020D17','',''))
  3. -------------------------------------------------
  4. 11222
从前面的全表扫描的结果, 期望值应该为11224, 因此只需要将索引块值修改为c3 02 0d 19。 即可与数据块保持一致。



4、使用BBED修复

查找offset的值:
查看dump的信息, 索引块的ITL槽位有3个, 可以计算offset如下:
offset = 3553+44+8+24*ITL_CNT
= 3553+44+8+24*3= 3677

指定文件和块:
  1. BBED> set file 1 block 72661;
  2. FILE# 1
  3. BLOCK# 72661

定位到行:
  1. BBED> dump offset 3677 count 16
  2. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  3. Block: 72661 Offsets: 3677 to 3692 Dba:0x00411bd5
  4. ------------------------------------------------------------------------
  5. 04c3020d 17010200 41386c00 ad04c302

修改:
  1. BBED> modify /x 19 offset 3681
  2. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  3. Block: 72661 Offsets: 3681 to 3696 Dba:0x00411bd5
  4. ------------------------------------------------------------------------
  5. 19010200 41386c00 ad04c302 0d150100
  6. <32 bytes per line>
  7. BBED> sum apply
  8. Check value for File 1, Block 72661:
  9. current = 0x1fa3, required = 0x1fa3
  10. BBED> dump offset 3677 count 16
  11. File: /u01/app/oracle/oradata/orcl/system01.dbf (1)
  12. Block: 72661 Offsets: 3677 to 3692 Dba:0x00411bd5
  13. ------------------------------------------------------------------------
  14. 04c3020d 19010200 41386c00 ad04c302
  15. <32 bytes per line>

5、打开数据库验证

查看全表扫描和索引扫描的值是否一致:
  1. SQL> select /*+ FULL(t1) */ name,con# from con$ t1 where name='_NEXT_CONSTRAINT';
  2. NAME CON#
  3. ------------------------------ ----------
  4. _NEXT_CONSTRAINT 11224
  5. SQL> select /*+ index(t1 I_CON2) */ name,con# from con$ t1 where name='_NEXT_CONSTRAINT';
  6. NAME CON#
  7. ------------------------------ ----------
  8. _NEXT_CONSTRAINT 11224

验证增加主键能成功:
  1. SQL> alter table t add primary key(id);
  2. Table altered.

















posted @ 2017-03-12 01:54  小海蟹  阅读(2334)  评论(0编辑  收藏  举报