午夜国产狂喷潮在线观看|国产AⅤ精品一区二区久久|中文字幕AV中文字幕|国产看片高清在线

    oracle中OBJ4 ORA-8102故障恢復(fù)案例
    來源:易賢網(wǎng) 閱讀:1504 次 日期:2015-03-20 11:04:04
    溫馨提示:易賢網(wǎng)小編為您整理了“oracle中OBJ4 ORA-8102故障恢復(fù)案例”,方便廣大網(wǎng)友查閱!

    下面我們一起來看看關(guān)于oracle中OBJ4 ORA-8102故障恢復(fù)案例,希望本文章可以幫助到各位朋友。

    在測(cè)試環(huán)境中對(duì)于OBJ$中i_obj4中出現(xiàn)ORA-8102進(jìn)行了重新并恢復(fù)測(cè)試,認(rèn)為自己已經(jīng)比較清楚的掌握了I_OBJ4的ORA-8102問題處理,可是實(shí)際的一個(gè)案例,還是比較比實(shí)驗(yàn)中復(fù)雜,這里貼出來主要操作供大家參考,再次證明數(shù)據(jù)庫恢復(fù)的場(chǎng)景不可大意,客戶的故障只有你想不到的,沒有遇不到的

    通過bbed修改obj$中dataobj$重現(xiàn)I_OBJ4索引報(bào)ORA-08102錯(cuò)誤

    使用bbed 修復(fù)I_OBJ4 index 報(bào)ORA-8102

    數(shù)據(jù)庫創(chuàng)建表提示ORA-8102錯(cuò)誤

    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錯(cuò)誤

    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,因此兩者不一致,從而出現(xiàn)ORA-8102錯(cuò)誤

    嘗試bbed解決ORA-8102問題

    嘗試修改obj$和i_obj4中的dataobj#記錄一致,這里修改obj$中的對(duì)應(yīng)記錄

    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

    驗(yàn)證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$中對(duì)應(yīng)記錄已經(jīng)一致(條數(shù)和數(shù)據(jù)值),但是依然不能執(zhí)行創(chuàng)建表操作,依舊報(bào)ORA-8102錯(cuò)誤.

    進(jìn)一步分析錯(cuò)誤原因

    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錯(cuò)誤依舊在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查詢我們已經(jīng)的出來在obj$中有dataobj#為97109,而通過max(dataobj#)只有96815

    分析max(dataobj#)執(zhí)行計(jì)劃

    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)執(zhí)行計(jì)劃,從而的出來最大值為96815,

    --而我們從ORA-8102錯(cuò)誤中可以看到index中有dataobj#為97109,證明index中的鏈表可能出問題

    --為什么懷疑是鏈表有問題呢?因?yàn)樵搃ndex的ffs正常

    嘗試把ORA-8102報(bào)錯(cuò)標(biāo)記壞塊嘗試

    并且通過event和隱含參數(shù)屏蔽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'

    通過這一步測(cè)試證明,在該ora-8102的錯(cuò)誤中,通過壞塊是無法解決繞過去該錯(cuò)誤,只是把錯(cuò)誤從ORA-8102轉(zhuǎn)變?yōu)榱薕RA-1578

    修復(fù)好制造壞塊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$中相應(yīng)記錄無法解決該問題,因?yàn)檫€有鏈表異常

    3. 通過標(biāo)記為壞塊也無法繞過該問題

    由于后續(xù)如果繼續(xù)修復(fù)修復(fù)i_obj4可能工作量過大,而且可以也比較急,通過人工直接刪除I_OBJ4數(shù)據(jù)字典記錄,然后結(jié)合bootstrap$核心index(I_OBJ1,I_USER1,I_FILE#_BLOCK#,I_IND1,I_TS#,I_CDEF1等)異?;謴?fù)—ORA-00701錯(cuò)誤解決處理實(shí)現(xiàn)完美恢復(fù)

    更多信息請(qǐng)查看IT技術(shù)專欄

    更多信息請(qǐng)查看數(shù)據(jù)庫
    易賢網(wǎng)手機(jī)網(wǎng)站地址:oracle中OBJ4 ORA-8102故障恢復(fù)案例
    由于各方面情況的不斷調(diào)整與變化,易賢網(wǎng)提供的所有考試信息和咨詢回復(fù)僅供參考,敬請(qǐng)考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇?zhǔn)!

    2025國考·省考課程試聽報(bào)名

    • 報(bào)班類型
    • 姓名
    • 手機(jī)號(hào)
    • 驗(yàn)證碼
    關(guān)于我們 | 聯(lián)系我們 | 人才招聘 | 網(wǎng)站聲明 | 網(wǎng)站幫助 | 非正式的簡(jiǎn)要咨詢 | 簡(jiǎn)要咨詢須知 | 新媒體/短視頻平臺(tái) | 手機(jī)站點(diǎn) | 投訴建議
    工業(yè)和信息化部備案號(hào):滇ICP備2023014141號(hào)-1 云南省教育廳備案號(hào):云教ICP備0901021 滇公網(wǎng)安備53010202001879號(hào) 人力資源服務(wù)許可證:(云)人服證字(2023)第0102001523號(hào)
    云南網(wǎng)警備案專用圖標(biāo)
    聯(lián)系電話:0871-65099533/13759567129 獲取招聘考試信息及咨詢關(guān)注公眾號(hào):hfpxwx
    咨詢QQ:1093837350(9:00—18:00)版權(quán)所有:易賢網(wǎng)
    云南網(wǎng)警報(bào)警專用圖標(biāo)