萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> oracle教程 >> oracle中OBJ4 ORA-8102故障恢復案例

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

  ----------

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

  lock@384: 0x00

  cols@385: 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

  ----------------------------------------

copyright © 萬盛學電腦網 all rights reserved