如何在windows下运用dd来下修复坏块？

 原文链接 个人博客 http://www.killdb.com/?p=169

前不久恢复了一个10201 for windows的库，由于坏块导致数据库异常。
我们知道，对于windows来说，相比unix或linux 操作起来更麻烦，其实windows也是可以使用dd的，
运用dd和UE基本上可以起到和使用bbed一样的效果。 大家可以在这里下载dd for win版本dd-0.6beta3
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 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> create table test1 as select owner,object_id,object_name from dba_objects

 2> where rownum <500;

表已创建。
SQL> select header_file,header_block from dba_segments where segment_name='TEST1';

HEADER_FILE HEADER_BLOCK
----------- ------------
          5           11

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) file# from test1;

     FILE#
----------
         5

SQL> select distinct dbms_rowid.rowid_block_number(rowid) blk# from test1;

      BLK#
----------
        13
        12

SQL> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192
SQL>

---关闭数据库，使用万能的UE修改file 5 block 13

SQL> conn /as sysdba
已连接。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  314572800 bytes
Fixed Size                  1296452 bytes
Variable Size              96470972 bytes
Database Buffers          209715200 bytes
Redo Buffers                7090176 bytes
数据库装载完毕。
数据库已经打开。
SQL> conn roger/roger
已连接。
SQL> select count(*) from test1;
select count(*) from test1
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 5, 块号 13)
ORA-01110: 数据文件 5: 'G:\ORACLE\PRODUCT\10.2.0\ORADATA\ALEX\ROGER01.DBF'

说明一下的是，我这里直接修改了块头中的flg，将其修改为0xff，oracle将直接标记为物理坏块。

C:\Documents and Settings\Administrator>dbv file=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ALEX

\ROGER01.DBF blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on 星期六 7月 9 15:48:35 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - 开始验证: FILE = G:\ORACLE\PRODUCT\10.2.0\ORADATA\ALEX\ROGER01.DBF
页 13 标记为损坏
Corrupt block relative dba: 0x0140000d (file 5, block 13)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0140000d
 last change scn: 0x0000.000fbf2f seq: 0x2 flg: 0xff
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xbf2f0602
 check value in block header: 0xd7f
 computed block checksum: 0xfb00

DBVERIFY - 验证完成

检查的页总数: 2560
处理的页总数 (数据): 1
失败的页总数 (数据): 0
处理的页总数 (索引): 0
失败的页总数 (索引): 0
处理的页总数 (其它): 11
处理的总页数 (段)  : 0
失败的总页数 (段)  : 0
空的页总数: 2547
标记为损坏的总页数: 1
流入的页总数: 0
最高块 SCN            : 1031985 (0.1031985)

修复坏块的方式大家都知道有很多，我就不多说了，我这里需要说的是，windows下我用dd将该block取出来。
然后使用bbed进行修改（当然，既然我们能用UE制造坏块，那么也就可以完全用UE来修复坏块，只是非常难）

---使用dd 复制file 5 block 13
C:\Documents and Settings\Administrator>dd if=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ALEX\ROGER01.DBF of=5.13.dd skip=13 bs=8192 count=1
rawwrite dd for windows version 0.6beta3.
Written by John Newbigin <jn@it.swin.edu.au>
This program is covered by terms of the GPL Version 2.

skip to 106496
1+0 records in
1+0 records out
C:\Documents and Settings\Administrator>dir *dd
 驱动器 C 中的卷是 system
 卷的序列号是 48A6-367D

 C:\Documents and Settings\Administrator 的目录

2011-07-09  15:32             8,192 5.13.dd
               1 个文件          8,192 字节
               0 个目录  9,338,363,904 可用字节

由于我这里是10204的，所有就不用9iR2的bbed for win版本了，我将dd出来的file   5.13.dd文件传到linux中，进行修复

[oracle@roger ~]$ ls -ltr 5.13*
-rw-r--r--  1 oracle dba 8192 Jul  9  2011 5.13.dd
[oracle@roger ~]$ bbed parfile=par.bbd
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Sat Jul 9 15:34:38 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /oracle/product/oradata/roger/system01.dbf                       61440
     2  /oracle/product/oradata/roger/undotbs01.dbf                       3200
     3  /oracle/product/oradata/roger/sysaux01.dbf                       30720
     4  /oracle/product/oradata/roger/users01.dbf                          640
     5  /oracle/product/oradata/roger/roger01.dbf                            0
     6  /home/oracle/5.13.dd                                                 1

BBED> set file 6
        FILE#           6

BBED> p kcbh  
struct kcbh, 20 bytes                       @0      
   ub1 type_kcbh                            @0        0x06
   ub1 frmt_kcbh                            @1        0xa2
   ub1 spare1_kcbh                          @2        0x00
   ub1 spare2_kcbh                          @3        0x00
   ub4 rdba_kcbh                            @4        0x0140000d
   ub4 bas_kcbh                             @8        0x000fbf2f
   ub2 wrp_kcbh                             @12       0x0000
   ub1 seq_kcbh                             @14       0x02
   ub1 flg_kcbh                             @15       0xff (KCBHFNEW, KCBHFDLC, KCBHFCKV)
   ub2 chkval_kcbh                          @16       0x0d7f
   ub2 spare3_kcbh                          @18       0x0000

BBED>
BBED> modify /x 4 offset 15
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /home/oracle/5.13.dd (6)
 Block: 1                Offsets:   15 to  511           Dba:0x01800001
------------------------------------------------------------------------
 047f0d00 00010000 008ccb00 002dbf0f 00000000 00030032 00090040 01ffff00
 00000000 00000000 00000000 00008000 002dbf0f 00000000 00000000 00000000
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
 00000000 00000000 00000000 000001c7 00ffffa0 01dc0c3c 0b3c0b00 00c7005d
 1f3e1f1d 1ffb1eda 1eb71e93 1e7b1e5f 1e4c1e3a 1e221e0a 1ef01dd4 1dbd1da4
 1d891d6c 1d591d44 1d331d21 1d0b1de6 1cd51cc3 1cad1c88 1c771c65 1c4f1c2a
 1c191c07 1cf11bcc 1bba1ba7 1b911b6c 1b5a1b47 1b311b0c 1bfb1ae8 1ad51ac0
 1aab1a97 1a7e1a6a 1a561a3c 1a271a16 1a001aed 19d519c3 19b1199f 198a1978
 195f194c 19341922 191019fd 18ea18d6 18c018b0 188b1866 1852183c 18231809
 18f317d9 17bf17a5 1792177b 17681751 173d1725 171217fb 16e416d0 16b816a6
 1690167a 16641654 1640162b 161216fa 15de15c2 15ab1590 15751561 15491531
 1520150b 15f514db 14cb14ba 14a81495 1481146d 14581444 142f141b 140614f2
 13d813bd 13ac1396 136f1359 1344132a 131213f5 12e512ca 12b61291 127b1267
 12511237 121f1205 12ed11d3 11bd11a8 1192117a 1164114d 11361120 110c11f7
 10dc10c3 10a4108f 10781061 10481034 101b1000 10e60fc1 0faa0f90 0f760f5a
 0f3a0f1a 0f000fe3 0ec60eae 0e940e7b 0e

 <32 bytes per line>

BBED> sum apply
Check value for File 6, Block 1:
current = 0x46c3, required = 0x46c3

C:\Documents and Settings\Administrator>dd if=5.13.dd of=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ALEX\ROGER01.DBF seek=13 bs=8192 count=1 conv=notr
unc
rawwrite dd for windows version 0.6beta3.
Written by John Newbigin <jn@it.swin.edu.au>
This program is covered by terms of the GPL Version 2.

notrunc
1+0 records in
1+0 records out

如上是通过bbed来修改的，当然，我们还可以用dbms_repair包来操作，它实质是对坏块进行标记，
然后oracle在做扫描block的时候，会跳过该坏块，类似event 10231.
SQL> BEGIN
  2    DBMS_REPAIR.ADMIN_TABLES (
  3    TABLE_NAME => 'REPAIR_TABLE',
  4    TABLE_TYPE => dbms_repair.repair_table,
  5    ACTION => dbms_repair.create_action,
  6    TABLESPACE => 'ROGER');
  7    END;
  8    /

PL/SQL 过程已成功完成。

SQL> set serveroutput on
SQL>  DECLARE num_corrupt INT;
  2     BEGIN
  3     num_corrupt := 0;
  4     DBMS_REPAIR.CHECK_OBJECT (
  5     SCHEMA_NAME => 'ROGER',
  6     OBJECT_NAME => 'TEST1',
  7     REPAIR_TABLE_NAME => 'REPAIR_TABLE',
  8     corrupt_count => num_corrupt);
  9     DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
 10     END;
 11     /
number corrupt: 1

PL/SQL 过程已成功完成。

SQL>
SQL> select object_id,RELATIVE_FILE_ID,block_id,CORRUPT_TYPE,object_name from
  2  repair_table;

 OBJECT_ID RELATIVE_FILE_ID   BLOCK_ID CORRUPT_TYPE OBJECT_NAME
---------- ---------------- ---------- ------------ ---------------
     52108                5         13         6148 TEST1

SQL> declare
  2      fix_count int;
  3      begin
  4        fix_count := 0;
  5        dbms_repair.fix_corrupt_blocks (
  6        schema_name => 'ROGER',
  7        object_name => 'TEST1',
  8        object_type => dbms_repair.table_object,
  9        repair_table_name => 'REPAIR_TABLE',
 10        fix_count => fix_count);
 11      dbms_output.put_line('fix count: ' || to_char(fix_count));
 12    end;
 13    /
fix count: 0

PL/SQL 过程已成功完成。

SQL> begin
  2      dbms_repair.skip_corrupt_blocks (
  3        schema_name => 'ROGER',
  4        object_name => 'TEST1',
  5        object_type => dbms_repair.table_object,
  6        flags => dbms_repair.skip_flag);
  7    end;
  8    /

PL/SQL 过程已成功完成。

SQL> conn roger/roger
已连接。
SQL> select count(*) from test1;

  COUNT(*)
----------
       300

SQL>
BBED> p kdbt
struct kdbt[0], 4 bytes                     @138    
   b2 kdbtoffs                              @138      0
   b2 kdbtnrow                              @140      199

可以发现，完全跳过了坏块file 5 block 13，现在正常数据是300条（其中坏块13中包含数据199条）.

补充一点的是，我们还可以使用基于rowid扫描，将test1表中的其他数据给提取出来。

如下是利用基于rowid扫描的方式保存除坏块以外的正常数据：
SQL> select dbms_rowid.rowid_create(1,52108,5,13,0) from dual;

DBMS_ROWID.ROWID_C                                           
------------------                                           
AAAMuMAAFAAAAANAAA                                           

SQL> select dbms_rowid.rowid_create(1,52108,5,14,0) from dual;

DBMS_ROWID.ROWID_C                                           
------------------                                           
AAAMuMAAFAAAAAOAAA                                           
SQL> create table test1_bak as
  2    select /* rowid(test1) */ * from test1
  3    where rowid < CHARTOROWID('AAAMuMAAFAAAAANAAA')
  4    or rowid >= CHARTOROWID('AAAMuMAAFAAAAAOAAA');

表已创建。

SQL> select count(*) from test1_bak;

  COUNT(*)
----------
       300

最后再补充一点的是，如果表有有索引，那么记得操作完以后记得rebuild index。