在上一篇中说到:模拟极端ORA-08103并解决,不能通过修改成坏块来解决,这里演示了是一个普通的数据块出现异常,然后通过bbed修改为坏块通过dbms_repair来解决该故障,补充说明:在11.2.0.3.3的库中,使用该方法不能重现该错误,而是直接提示ORA-01578,证明ORACLE的新版本在这一方面进行了改进
创建测试表

SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> create table t_xifenfei as
  2   select * from dba_objects where rownum<3000;
Table created.
SQL> SELECT owner, segment_name, EXTENT_ID, FILE_ID, BLOCK_ID, BLOCKS
  2  FROM dba_extents
  3  WHERE segment_name='T_XIFENFEI' AND owner='CHF';
OWNER                          SEGMENT_NAME     EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
------------------------------ --------------- ---------- ---------- ---------- ----------
CHF                            T_XIFENFEI               0          4         57          8
CHF                            T_XIFENFEI               1          4         65          8
CHF                            T_XIFENFEI               2          4         73          8
CHF                            T_XIFENFEI               3          4         81          8
CHF                            T_XIFENFEI               4          4         89          8
CHF                            T_XIFENFEI               5          4         97          8
6 rows selected.
SQL> SELECT DISTINCT dbms_rowid.rowid_block_number(rowid) blk#,
  2  dbms_rowid.rowid_relative_fno(rowid) file#
  3   FROM t_xifenfei
  4   ORDER BY 2,1;
      BLK#      FILE#
---------- ----------
        60          4
        61          4
        62          4
        63          4
        64          4
        65          4
        66          4
        67          4
        68          4
        69          4
        70          4
      BLK#      FILE#
---------- ----------
        71          4
        72          4
        74          4
        75          4
        76          4
        77          4
        78          4
        79          4
        80          4
        81          4
        82          4
      BLK#      FILE#
---------- ----------
        83          4
        84          4
        85          4
        86          4
        87          4
        88          4
        90          4
        91          4
        92          4
        93          4
        94          4
      BLK#      FILE#
---------- ----------
        95          4
        96          4
        97          4
        98          4
37 rows selected.

模拟ORA-08103

SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
--破坏数据快(其实就是清空一个数据块block 95,注意dd和实际数据的block对应关系相差1)
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users01.dbf bs=8192 seek=95 count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000187113 seconds, 43.8 MB/s
SQL> STARTUP
ORACLE instance started.
Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             104860124 bytes
Database Buffers          205520896 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI;
SELECT COUNT(*) FROM CHF.T_XIFENFEI
                         *
ERROR at line 1:
ORA-08103: object no longer exists
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei file=/tmp/t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Fri Jan 13 22:09:43 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI
EXP-00056: ORACLE error 8103 encountered
ORA-08103: object no longer exists
Export terminated successfully with warnings.
[oracle@xifenfei ~]$ expdp chf/xifenfei tables=t_xifenfei dumpfile=t_xifenfei.dmp
Export: Release 10.2.0.4.0 - Production on Friday, 13 January, 2012 22:10:26
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "CHF"."SYS_EXPORT_TABLE_01":  chf/******** tables=t_xifenfei dumpfile=t_xifenfei.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 384 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31693: Table data object "CHF"."T_XIFENFEI" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-08103: object no longer exists
Master table "CHF"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for CHF.SYS_EXPORT_TABLE_01 is:
  /u01/oracle/oracle/product/10.2.0/db_1/rdbms/log/t_xifenfei.dmp
Job "CHF"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:10:50

测试证明当出现ORA-08103的时候,全表扫描,exp,expdp均不能正常工作

找到出现ORA-08103数据块

SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL>  alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL>  alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
……………………
ERROR:
ORA-08103: object no longer exists
2700 rows selected.
--在trace文件结尾发现如下记录,表示读到这个数据块时发生错误
KTRVAC: path typ=0, rdba=100005f
SQL> select to_number('100005f','xxxxxxxxxxxxx') from dual;
TO_NUMBER('100005F','XXXXXXXXXXXXX')
------------------------------------
                            16777311
SQL> select
  2  dbms_utility.data_block_address_file(16777311) FILE_NO,
  3  dbms_utility.data_block_address_block(16777311) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4         95

bbed继续破坏异常块

BBED> set filename '/u01/oracle/oradata/XFF/users01.dbf'
        FILENAME        /u01/oracle/oradata/XFF/users01.dbf
BBED> set block 95
        BLOCK#          95
BBED> map
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95                                    Dba:0x00000000
------------------------------------------------------------
BBED-00400: invalid blocktype (00)
BBED> set count 32
        COUNT           32
BBED> d
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets:    0 to   31           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 <32 bytes per line>
--这个就是和11gr2的区别,在11g中使用该方法来模拟ORA-08103,直接提示坏块,从而不会出现ORA-08103
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/XFF/users01.dbf
BLOCK = 95
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            : 1
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
BBED> d offset 8180
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8180 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 00000000 00000000 00000000
 <32 bytes per line>
BBED> m /x 01010101 offset 8188
BBED-00215: editing not allowed in BROWSE mode
BBED> set mode edit
        MODE            Edit
--修改sumcheck
BBED> m /x 01010101 offset 8188
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/XFF/users01.dbf (0)
 Block: 95               Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 01010101
 <32 bytes per line>
BBED> sum
Check value for File 0, Block 95:
current = 0x0000, required = 0x0000

测试修改为坏块效果

SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 95)
ORA-01110: data file 4: '/u01/oracle/oradata/XFF/users01.dbf'
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
      2918

通过让ORA-08103对应的块变为真正的坏块,然后使用dbms_repair或者event来跳过坏块,达到拯救数据的目的

ORA-08103错误在数据库日程运维和异常恢复中都可能遇到,出现该错误的原因很多,有很多情况下(模拟普通ORA-08103并解决),直接通过修改块使其在数据库查询的时候表标志为坏块,然后使用event或者dbms_repair包来标志该块,然后跳过就可以解决该问题,但是有些时候,遇到极端情况,该方法会失效,需要借助极端工具来处理该极端问题.
分析表相关EXTENT

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table t_xifenfei  as
  2   select * from dba_objects;
Table created.
SQL> select count(*) from t_xifenfei;
  COUNT(*)
----------
     74504
SQL> select data_object_id,object_id from dba_objects where object_name='T_XIFENFEI';
DATA_OBJECT_ID  OBJECT_ID
-------------- ----------
         75592      75592
SQL> select file_id,block_id,block_id+blocks-1
  2      from dba_extents
  3   where segment_name ='T_XIFENFEI' AND owner='CHF';
   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         4        680               687
         4        688               695
         4        696               703
         4        704               711  <---注意
         4        712               719
         4        720               727
         4        728               735
         …………
24 rows selected.
SQL> Select segment_name,header_file,header_blocK
  2  from dba_segments where
  3  segment_name in ('T_XIFENFEI') and owner='CHF'
  4  ;
SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
T_XIFENFEI                               4          682
SQL> alter system checkpoint;
System altered.
SQL> alter system dump datafile 4 block 682;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28933.trc
--trace文件
Dump of memory from 0xB6CFD600 to 0xB6CFF600
B6CFD600 0000A223 010002AA 000DB4EA 04010000  [#...............]
B6CFD610 00008ECF 00000000 00000000 00000000  [................]
B6CFD620 00000000 00000018 00000480 00000A9C  [................]
B6CFD630 00000017 0000003F 00000080 010007BF  [....?...........]
B6CFD640 00000000 00000017 00000000 0000043F  [............?...]
B6CFD650 00000000 00000000 00000000 00000017  [................]
B6CFD660 0000003F 00000080 010007BF 00000000  [?...............]
B6CFD670 00000017 00000000 0000043F 01000780  [........?.......]
B6CFD680 01000780 00000000 00000000 00000000  [................]
B6CFD690 00000000 00000000 00000000 00000000  [................]
        Repeat 3 times
B6CFD6D0 00000001 00002000 00000000 00001434  [..... ......4...]
B6CFD6E0 00000000 010002A9 00000001 01000781  [................]
B6CFD6F0 010002A9 00000000 00000000 00000000  [................]
B6CFD700 00000000 00000000 00000018 00000000  [................]
B6CFD710 00012748 10000000 010002A8 00000008  [H'..............]
B6CFD720 010002B0 00000008 010002B8 00000008  [................]
B6CFD730 010002C0 00000008 010002C8 00000008  [................]   <----dump中找到下面值
……
  Extent Map
  -----------------------------------------------------------------
   0x010002a8  length: 8
   0x010002b0  length: 8
   0x010002b8  length: 8
   0x010002c0  length: 8     <-----选择第四个exent
   0x010002c8  length: 8
   …………
  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x010002a8 Data dba:  0x010002ab
   Extent 1     :  L1 dba:  0x010002a8 Data dba:  0x010002b0
   Extent 2     :  L1 dba:  0x010002b8 Data dba:  0x010002b9
   Extent 3     :  L1 dba:  0x010002b8 Data dba:  0x010002c0  <---同上
   Extent 4     :  L1 dba:  0x010002c8 Data dba:  0x010002c9
   Extent 5     :  L1 dba:  0x010002c8 Data dba:  0x010002d0
   …………
  --------------------------------------------------------
--确定Extent 3的记录在file 4 block 682 的偏移量为304
------省略了相同部分B6CFD
SQL> SELECT TO_NUMBER('730','XXXXX') FROM DUAL;
TO_NUMBER('730','XXXXX')
------------------------
                    1840
SQL> SELECT TO_NUMBER('600','XXX') FROM DUAL;
TO_NUMBER('600','XXX')
----------------------
                  1536
SQL> SELECT 1840-1536 FROM DUAL;
 1840-1536
----------
       304
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

这里我们假设修改SEGMENT HEADER中关于EXTENT 3(从0开始计数)的映射地址,从而来使得该表在查询的时候出现ORA-08103错误

bbed修改相关值

[oracle@xifenfei ~]$ bbed parfile=bbed.par
BBED> SET MODE EDIT
        MODE            Edit
BBED> INFO
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     4  /u01/oracle/oradata/ora11g/users01.dbf                               0
BBED> SET FILE 4 BLOCK 682
        FILE#           4
        BLOCK#          682
BBED> D
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 23a20000 aa020001 eab40d00 00000104 cf8e0000 …………
 <32 bytes per line>
BBED> m /x 1100
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 11000000 aa020001 eab40d00 00000104 cf8e0000 …………
 <32 bytes per line>
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (4)
 Block: 682              Offsets:    0 to  511           Dba:0x010002aa
------------------------------------------------------------------------
 11000000 aa020001 eab40d00 00000104 cf8e0000 …………
 <32 bytes per line>
BBED> sum apply
Check value for File 4, Block 682:
current = 0x2cfd, required = 0x2cfd

重现ORA-08103

SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             239078260 bytes
Database Buffers           67108864 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
SQL> conn chf/xifenfei
Connected.
SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
ERROR at line 1:
ORA-08103: object no longer exists

定位坏块位置

SQL> alter session set max_dump_file_size=unlimited;
Session altered.
SQL> alter session set db_file_multiblock_read_count=1;
Session altered.
SQL> alter session set events 'immediate trace name trace_buffer_on level 1048576';
Session altered.
SQL> alter session set events '10200 trace name context forever, level 1';
Session altered.
SQL> alter session set events '8103 trace name errorstack level 3';
Session altered.
SQL> alter session set events '10236 trace name context forever, level 1';
Session altered.
SQL> alter session set tracefile_identifier='ORA8103';
Session altered.
SQL> select * from chf.t_xifenfei;
select * from chf.t_xifenfei
                  *
ERROR at line 1:
ORA-08103: object no longer exists
--trace文件关键内容
block_row_dump:
tab 0, row 0, @0x1f70
tl: 48 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 9]  44 49 43 54 2e 42 41 53 45
col  1: [ 1]  32
col  2: [32]
…………
end_of_block_dump
The buffer with tsn: 0 rdba: 0x00400321 has already been dumped
The buffer with tsn: 4 rdba: 0x010002aa was pinned,
but could not be dumped
SQL> Select to_number('010002aa','xxxxxxxxxxxxxxxxxx') from dual;
TO_NUMBER('010002AA','XXXXXXXXXXXXXXXXXX')
------------------------------------------
                                  16777898
SQL> select
  2  dbms_utility.data_block_address_file(16777898) FILE_NO,
  3  dbms_utility.data_block_address_block(16777898) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4        682

检查坏块

[oracle@xifenfei ~]$ dbv file=/u01/oracle/oradata/ora11g/users01.dbf
DBVERIFY: Release 11.2.0.3.0 - Production on Fri Jan 13 18:03:13 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/oracle/oradata/ora11g/users01.dbf
Block Checking: DBA = 16777898, Block Type = Unlimited data segment header with flg blks
Incorrect total map count: 24
Page 682 failed with check code 17006
DBVERIFY - Verification complete
Total Pages Examined         : 2240
Total Pages Processed (Data) : 1421
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 199
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 229
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 391
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 898278 (0.898278)
--对应块
SQL> select
  2  dbms_utility.data_block_address_file(16777898) FILE_NO,
  3  dbms_utility.data_block_address_block(16777898) BLOCK_NO
  4  from dual;
   FILE_NO   BLOCK_NO
---------- ----------
         4        682

通过这里dbv检查发现,该数据库已经是坏块了,也就是说,网上流传的人工标志为坏块的方法在此处行不通,这里我们考虑使用dul等类此工具挖取数据,来拯救数据

dul和odu常规操作

DUL> unload table chf.t_xifenfei;
. unloading table                T_XIFENFEI
DUL: Warning: Using data objno from segment header( 16779199) instead of expected id (75592) from dictionary
DUL: Error: No entry in control file for block: ts# = 4 rfile# = 0 block# = 1087
DUL: Error: While processing unknown file block# 1087
DUL: Error: Could not read/parse data block
ODU> unload table chf.t_xifenfei
Unloading table: T_XIFENFEI,object ID: 75592
Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0)
corrupted block
0 rows unloaded

通过上面的测试证明,在该中情况下(SEGMENT HEADER)出现异常时,dul/odu均不能使用最常规的方法挖的数据

扫描数据文件方式挖
因为odu在这个方面的操作人性化于dul,所以只用odu进行相关测试,dul肯定能过实现相同功能

ODU> SCAN EXTENT TABLESPACE 4 PARALLEL 2
scan extent start: 2012-9-23 2:47:51
scanning extent...
scanning extent finished.
scan extent completed: 2012-9-23 2:47:51
ODU> unload table chf.t_xifenfei object  75592
Unloading table: T_XIFENFEI,object ID: 75592
Unloading segment,storage(Obj#=75592 DataObj#=75592 TS#=4 File#=4 Block#=682 Cluster=0)
74504 rows unloaded

试验证明通过odu扫描表空间/数据文件找回来所有的数据,然后truncate掉问题表,导入该数据库,问题可以得意顺利解决