程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle中OBJ4 ORA-8102故障恢復案例

oracle中OBJ4 ORA-8102故障恢復案例

編輯:Oracle教程

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

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

  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

  ----------

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

  lock@384: 0x00

  cols@385: 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錯誤解決處理實現完美恢復

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved