如何在windows下运用dd来下修复坏块？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 如何在windows下运用dd来下修复坏块？
前不久恢复了一个10201 for windows的库，由于坏块导致数据库异常。
我们知道，对于windows来说，相比unix或linux 操作起来更麻烦，其实windows也是可以使用dd的，
运用dd和UE基本上可以起到和使用bbed一样的效果。大家可以在这里下载dd for win版本dd-0.6beta3
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
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 -- 关闭数据库，使用万能的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将直接标记为物理坏块。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
C:\> 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来修复坏块，只是非常难）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- 使用dd 复制file 5 block 13 C:\> 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中，进行修复
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
[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> 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:\> dd if=5.13.dd of=G:\ORACLE\PRODUCT\10.2.0\ORADATA\ALEX\ROGER01.DBF seek=13 bs=8192 count=1 conv=notrunc 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
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> select object_id,RELATIVE_FILE_ID,block_id,CORRUPT_TYPE,object_name 2 from 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 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扫描的方式保存除坏块以外的正常数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
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。
9 Responses to “如何在windows下运用dd来下修复坏块？”
I’m sorry to annoy you like this but I just discovered your website and have to admit it’s outstanding. The one thing that I think would help you however is the SEOpressor wordpress plugin. It is frankly the greatest SEO plugin for wordpress blogs that exists today. In exactly Five days my site went from obscurity to the very first page of google after utilizing the SEOpressor plugin. If you’re serious about SEO you can get it here
thank you
Good blog with some exciting information. I will be back.
I appreciate your work , appreciate it for all the great articles .
Wow! Thank you! I continuously needed to write on my blog something like that. Can I implement a part of your post to my site?
You are a very intelligent person!
hi. i love your blog love wife & love life ——Roger » Blog Archive » 如何在windows下è¿ç”¨ddæ¥ä¸‹ä¿®å¤åå—？ and will certainly do a link to http://www.killdb.com/?p=169 on my site.
Very interesting subject , thanks for posting .
Some truly interesting information, well written and loosely user friendly .
Leave a Reply
You must be logged in to post a comment.