| 标题 | oracle中OBJ4 ORA-8102故障恢复案例 |
| 内容 | 下面我们一起来看看关于oracle中OBJ4 ORA-8102故障恢复案例，希望本文章可以帮助到各位朋友。 在测试环境中对于OBJ$中i_obj4中出现ORA-8102进行了重新并恢复测试,认为自己已经比较清楚的掌握了I_OBJ4的ORA-8102问题处理,可是实际的一个案例,还是比较比实验中复杂,这里贴出来主要操作供大家参考,再次证明数据库恢复的场景不可大意,客户的故障只有你想不到的,没有遇不到的 通过bbed修改obj$中dataobj$重现I_OBJ4索引报ORA-08102错误 使用bbed 修复I_OBJ4 index 报ORA-8102 数据库创建表提示ORA-8102错误 SQL> startup ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. SQL> 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 93842 (2) 分析ORA-08102错误 SQL> select object_name,object_type from dba_objects where object_id=39; OBJECT_NAME OBJECT_TYPE ------------------------------ ------------------- I_OBJ4 INDEX SQL> 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 93842 (2) SQL> select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t minus select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1; 2 3 DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 97109 0 0 SQL> select /*+ full(t1) */ DATAOBJ#,type#,owner# from obj$ t1 minus select /*+ index(t i_obj4) */ DATAOBJ#,type#,owner# from obj$ t ; 2 3 4 DATAOBJ# TYPE# OWNER# ---------- ---------- ---------- 97094 0 0 SQL> SET LINES 122 COL INDEX_OWNER FOR A20 COL INDEX_NAME FOR A30 COL TABLE_OWNER FOR A20 COL COLUMN_NAME FOR A25 SELECT TABLE_OWNER,INDEX_NAME,COLUMN_NAME,COLUMN_POSITION FROM Dba_Ind_Columns WHERE table_name = upper('&TABLE_NAME') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION and index_name='I_OBJ4'; SQL> SQL> SQL> SQL> SQL> 2 3 Enter value for table_name: OBJ$ old 3: WHERE table_name = upper('&TABLE_NAME') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION new 3: WHERE table_name = upper('OBJ$') order by TABLE_OWNER,INDEX_OWNER,INDEX_NAME,COLUMN_POSITION TABLE_OWNER INDEX_NAME COLUMN_NAME COLUMN_POSITION -------------------- ------------------------------ ------------------------- --------------- SYS I_OBJ4 DATAOBJ# 1 SYS I_OBJ4 TYPE# 2 SYS I_OBJ4 OWNER# 3 SQL> SELECT DATAOBJ# FROM OBJ$ WHERE OBJ#=97109; no rows selected SQL> SELECT DATAOBJ# FROM OBJ$ WHERE OBJ#=97094; DATAOBJ# ---------- 97094 SQL> select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t minus select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1; 2 3 ROWID DATAOBJ# TYPE# OWNER# ------------------ ---------- ---------- ---------- AAAAASAABAAAADxAAb 97109 0 0 SQL> select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1 minus select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t ; 2 3 4 ROWID DATAOBJ# TYPE# OWNER# ------------------ ---------- ---------- ---------- AAAAASAABAAAADxAAb 97094 0 0 SQL> select name,obj#,dataobj# from obj$ where rowid='AAAAASAABAAAADxAAb'; NAME OBJ# DATAOBJ# ------------------------------ ---------- ---------- _NEXT_OBJECT 1 97094 到此也比较清楚,rowid为AAAAASAABAAAADxAAb的dataobj#记录在obj$表中为97094而在I_OBJ4中记录为97109,因此两者不一致,从而出现ORA-8102错误 尝试bbed解决ORA-8102问题 尝试修改obj$和i_obj4中的dataobj#记录一致，这里修改obj$中的对应记录 SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block#, dbms_rowid.rowid_row_number(rowid) row# from obj$ where rowid='AAAAASAABAAAADxAAb' 2 3 4 / FILE# BLOCK# ROW# ---------- ---------- ---------- 1 241 27 SQL> select dump(97109,16) from dual; DUMP(97109,16) ---------------------- Typ=2 Len=4: c3,a,48,a SQL> select dump(97094,16) from dual; DUMP(97094,16) ----------------------- Typ=2 Len=4: c3,a,47,5f -bash-4.1$ bbed blocksize=8192 mode=edit filename=/u01/app/oracle/oradata/oa/system01.dbf Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 19:30:18 2015 Copyright (c) 1982, 2009, 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/oa/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/oa/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[27] rowdata[0] ---------- ub1 rowdata[0] @383 0x2c BBED> x /rnnncnnncc rowdata[0] @383 ---------- : 0x2c (KDRHFL, KDRHFF, KDRHFH) : 0x00 : 18 col 0[2] @386: 1 col 1[4] @389: 97094 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: xm....4 col 8[7] @423: xs....6 col 9[7] @431: xm....4 col 10[1] @439: . col 11[0] @441: *NULL* col 12[0] @442: *NULL* col 13[1] @443: . col 14[0] @445: *NULL* col 15[1] @446: . col 16[4] @448: ..8$ col 17[1] @453: . BBED> set count 32 COUNT 32 BBED> set offset 389 OFFSET 389 BBED> d File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Offsets: 389 to 420 Dba:0x00000000 ------------------------------------------------------------------------ 04c30a47 5f01800c 5f4e4558 545f4f42 4a454354 02c102ff 01800778 6d080f01 <32 bytes per line> BBED> set offset +3 OFFSET 392 BBED> d File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------------------------ 475f0180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786d080f 01113407 <32 bytes per line> BBED> m /x 480a File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 241 Offsets: 392 to 423 Dba:0x00000000 ------------------------------------------------------------------------ 480a0180 0c5f4e45 58545f4f 424a4543 5402c102 ff018007 786d080f 01113407 <32 bytes per line> BBED> p *kdbr[27] rowdata[0] ---------- ub1 rowdata[0] @383 0x2c BBED> x /rnnncnnncc rowdata[0] @383 ---------- : 0x2c (KDRHFL, KDRHFF, KDRHFH) : 0x00 : 18 col 0[2] @386: 1 col 1[4] @389: 97109 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: xm....4 col 8[7] @423: xs....6 col 9[7] @431: xm....4 col 10[1] @439: . col 11[0] @441: *NULL* col 12[0] @442: *NULL* col 13[1] @443: . col 14[0] @445: *NULL* col 15[1] @446: . col 16[4] @448: ..8$ col 17[1] @453: . BBED> sum apply Check value for File 0, Block 241: current = 0x913d, required = 0x913d 验证bbed修改后效果 SQL> startup ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. SQL> select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t minus select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1; 2 3 no rows selected SQL> select /*+ full(t1) */ rowid,DATAOBJ#,type#,owner# from obj$ t1 minus select /*+ index(t i_obj4) */ rowid,DATAOBJ#,type#,owner# from obj$ t ; 2 3 4 no rows selected SQL> ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE; ANALYZE TABLE sys.obj$ VALIDATE STRUCTURE CASCADE * ERROR at line 1: ORA-01499: table/index cross reference failure - see trace file SQL> create table t_xifenfei as select * from dual; create table t_xifenfei 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 93842 (2) 这里比较悲剧,我们看到i_obj4和obj$中对应记录已经一致(条数和数据值),但是依然不能执行创建表操作,依旧报ORA-8102错误. 进一步分析错误原因 SQL> ALTER SESSION SET EVENTS '802 trace name errorstack level 3'; Session altered. SQL> create table t as select * from dual; create table t 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 93842 (2) SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/oa/oa/trace/oa_ora_6163.trc oer 8102.2 - obj# 39, rdba: 0x00416e92(afn 1, blk# 93842) kdk key 8102.2: ncol: 4, len: 16 key: (16): 04 c3 0a 48 0a 01 80 01 80 06 00 40 00 f1 00 1b --这里可以看出来,提示ORA-8102错误依旧在I_OBJ4,97109记录上 SQL> select max(dataobj#) from obj$; MAX(DATAOBJ#) ------------- 96815 SQL> select name,obj#,dataobj# from obj$ where rowid='AAAAASAABAAAADxAAb'; NAME OBJ# DATAOBJ# ------------------------------ ---------- ---------- _NEXT_OBJECT 1 97109 --这里很奇怪，通过rowid查询我们已经的出来在obj$中有dataobj#为97109,而通过max(dataobj#)只有96815 分析max(dataobj#)执行计划 SQL> SET AUTOT TRACE SQL> select max(dataobj#) from obj$; Execution Plan ---------------------------------------------------------- Plan hash value: 721075849 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 2 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| I_OBJ4 | 1 | 2 | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 2 consistent gets 0 physical reads 0 redo size 533 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 --这里知晓,由于max(dataobj#)使用了INDEX FULL SCAN (MIN/MAX)执行计划,从而的出来最大值为96815, --而我们从ORA-8102错误中可以看到index中有dataobj#为97109,证明index中的链表可能出问题 --为什么怀疑是链表有问题呢?因为该index的ffs正常 尝试把ORA-8102报错标记坏块尝试 并且通过event和隐含参数屏蔽index坏块 -bash-4.1$ bbed blocksize=8192 mode=edit filename=/u01/app/oracle/oradata/oa/system01.dbf Password: BBED: Release 2.0.0.0.0 - Limited Production on Sat Mar 14 20:30:58 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. ************* !!! For Oracle Internal Use only !!! *************** BBED> set block 93842 BLOCK# 93842 BBED> set offset 8188 OFFSET 8188 BBED> d File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 010675ad <32 bytes per line> BBED> m /x 02 File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 020675ad <32 bytes per line> BBED> sum apply Check value for File 0, Block 93842: current = 0x9186, required = 0x9186 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/oa/system01.dbf BLOCK = 93842 Block 93842 is corrupt Corrupt block relative dba: 0x00416e92 (file 0, block 93842) Fractured block found during verification Data in bad block: type: 6 format: 2 rdba: 0x00416e92 last change scn: 0x0000.c007ad75 seq: 0x1 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xad750602 check value in block header: 0x9186 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 -bash-4.1$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 14 20:33:19 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. SQL> create table t as select * from dual; create table t as select * from dual * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01578: ORACLE data block corrupted (file # 1, block # 93842) ORA-01110: data file 1: '/u01/app/oracle/oradata/oa/system01.dbf' 通过这一步测试证明,在该ora-8102的错误中,通过坏块是无法解决绕过去该错误,只是把错误从ORA-8102转变为了ORA-1578 修复好制造坏块block BBED> m /x 01 File: /u01/app/oracle/oradata/oa/system01.dbf (0) Block: 93842 Offsets: 8188 to 8191 Dba:0x00000000 ------------------------------------------------------------------------ 010675ad <32 bytes per line> BBED> sum apply Check value for File 0, Block 93842: current = 0x9185, required = 0x9185 BBED> verify DBVERIFY - Verification starting FILE = /u01/app/oracle/oradata/oa/system01.dbf BLOCK = 93842 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 SQL> shutdown abort ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2.6991E+10 bytes Fixed Size 2213976 bytes Variable Size 1.9327E+10 bytes Database Buffers 7516192768 bytes Redo Buffers 145174528 bytes Database mounted. Database opened. SQL> 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 93842 (2) 至此我们大体出来信息： 1. ORA-8102的是I_OBJ4中的_NEXT_OBJECT记录异常和该index链表异常 2. 通过bbed修改I_OBJ4和obj$中相应记录无法解决该问题,因为还有链表异常 3. 通过标记为坏块也无法绕过该问题 由于后续如果继续修复修复i_obj4可能工作量过大,而且可以也比较急,通过人工直接删除I_OBJ4数据字典记录,然后结合bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)异常恢复—ORA-00701错误解决处理实现完美恢复 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。