利用dd修改checksum值的过程

By cui hua on February 18, 2009 2:55 PM | 4 Comments

本文参考了如下文章：

1、metalink上 268302.1 ORA-8103 Diagnostics and Solution

2、熊哥的记一次ORA-8103错误的处理

 

看到上述两篇文章，很有感触，禁不住手痒，我这里详细记录一下我用dd修改一个指定block的checksum值从而人为制造出坏块的过程，当然，最后我会用DBMS_REPAIR.SKIP_CORRUPT_BLOCKS把上述坏块给标记出来，从而抢救出其他数据。

 

SQL> conn dras/astca@astcatest;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> create tablespace testtbs datafile '/dras20/astca/test01.dbf' size 1M;

 

Tablespace created

 

SQL> select tablespace_name,extent_management,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where tablespace_name='TESTTBS';

 

TABLESPACE_NAME                EXTENT_MANAGEMENT SEGMENT_SPACE_MANAGEMENT

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

TESTTBS                        LOCAL             MANUAL

 

SQL> create table t1 (id number, c1 char(2000), c2 char(2000), c3 char(2000)) tablespace testtbs;

 

Table created

 

SQL>

SQL> insert into t1 VALUES (1, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (2, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (3, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (4, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (5, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (6, 'A', 'A', 'A');

 

1 row inserted

 

SQL> insert into t1 VALUES (7, 'A', 'A', 'A');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select segment_name,extent_id,block_id from dba_extents where segment_name='T1';

 

SEGMENT_NAME   EXTENT_ID   BLOCK_ID

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

T1                       0          9

 

SQL> insert into t1 VALUES (8, 'A', 'A', 'A');

 

1 row inserted

 

SQL> commit;

 

Commit complete

 

SQL> select segment_name,extent_id,block_id from dba_extents where segment_name='T1';

 

SEGMENT_NAME   EXTENT_ID   BLOCK_ID

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

T1                     0          9

T1                     1         17

 

SQL> select id,rowid,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) from t1;

 

        ID ROWID              DBMS_ROWID.ROWID_RELATIVE_FNO(

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

         1 AAAVAgACFAAAAAKAAA 133_10

         2 AAAVAgACFAAAAALAAA 133_11

         3 AAAVAgACFAAAAAMAAA 133_12

         4 AAAVAgACFAAAAANAAA 133_13

         5 AAAVAgACFAAAAAOAAA 133_14

         6 AAAVAgACFAAAAAPAAA 133_15

         7 AAAVAgACFAAAAAQAAA 133_16

         8 AAAVAgACFAAAAARAAA 133_17

 

8 rows selected

 

现在我要故意把ID为2的那条记录的checksum值给改掉。

方法就是用dd，如下是完整的改动过程：

$ sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 18 13:48:56 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

 

SQL_astca>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL_astca>exit

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

 

$ dd if=/dras20/astca/test01.dbf of=/dras20/astca/test01_01.txt skip=11 bs=8192 count=1

1+0 records in.

1+0 records out.

$ dd if=/dras20/astca/test01.dbf of=/dras20/astca/test01_02.txt skip=12 bs=8192 count=117

117+0 records in.

117+0 records out.

$ ls -l

total 2463736

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassda0701_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drassdc0610_03.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassdc0701_02.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0610_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0705_01.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drasupd0706_02.dbf

-rw-r--r--   1 oracle   dba              17 Feb 18 13:48 login.sql

-rw-r-----   1 oracle   dba         1056768 Feb 18 13:56 test01.dbf

-rw-r--r--   1 oracle   dba            8192 Feb 18 13:59 test01_01.txt

-rw-r--r--   1 oracle   dba          958464 Feb 18 13:59 test01_02.txt

-rw-r-----   1 oracle   dba         1056768 Feb 18 13:54 test01backup.dbf

 

然后将test01_01.txt以binary方式ftp到本地，用ultraEdit打开，并将其第16个byte的值由C7改为B7，保存。然后将保存后的test01_01.txt再通过ftp以binary方式上传并覆盖原有同名文件。

 

覆盖完后再用dd把这个修改过checksum值的block给拷回去：

$ dd if=/dras20/astca/test01_01.txt of=/dras20/astca/test01.dbf seek=11 bs=8192

1+0 records in.

1+0 records out.

 

$ dd if=/dras20/astca/test01_02.txt of=/dras20/astca/test01.dbf seek=12 bs=8192

117+0 records in.

117+0 records out.

$ ls -l

total 2463720

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassda0701_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drassdc0610_03.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drassdc0701_02.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0610_03.dbf

-rw-r-----   1 oracle   dba       314580992 Feb 18 13:49 drasupd0705_01.dbf

-rw-r-----   1 oracle   dba       104865792 Feb 18 13:49 drasupd0706_02.dbf

-rw-r--r--   1 oracle   dba              17 Feb 18 13:48 login.sql

-rw-r-----   1 oracle   dba         1056768 Feb 18 14:06 test01.dbf

-rw-r--r--   1 oracle   dba            8192 Feb 18 14:04 test01_01.txt

-rw-r--r--   1 oracle   dba          958464 Feb 18 13:59 test01_02.txt

-rw-r-----   1 oracle   dba         1056768 Feb 18 13:54 test01backup.dbf

$ sqlplus '/ as sysdba';

 

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 18 14:06:17 2009

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to an idle instance.

 

 

SQL_astca>startup

ORACLE instance started.

 

Total System Global Area  824150304 bytes

Fixed Size                   743712 bytes

Variable Size             285212672 bytes

Database Buffers          536870912 bytes

Redo Buffers                1323008 bytes

Database mounted.

Database opened.

 

这时候查询表t1里的数据，果然，oracle这里报错有坏块，如下所示：

SQL> conn dras/astca@astcatest;

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0

Connected as dras

 

SQL> select id from t1;

 

select id from t1

 

ORA-01578: ORACLE data block corrupted (file # 133, block # 11)

ORA-01110: data file 133: '/dras20/astca/test01.dbf'

 

现在我们来skip上述坏快，把其他的数据给救出来：

SQL_astca>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 => 'TESTTBS');

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

SQL_astca>set serveroutput on

SQL_astca>DECLARE num_corrupt INT;

  2  BEGIN

  3  num_corrupt := 0;

  4  DBMS_REPAIR.CHECK_OBJECT (

  5  SCHEMA_NAME => 'DRAS',

  6  OBJECT_NAME => 'T1',

  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 procedure successfully completed.

 

SQL_astca>select BLOCK_ID, CORRUPT_TYPE, CORRUPT_DESCRIPTION

  2  from REPAIR_TABLE;

 

  BLOCK_ID CORRUPT_TYPE

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

CORRUPT_DESCRIPTION

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

        11         6148

 

 

SQL_astca>BEGIN

  2  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  3  SCHEMA_NAME => 'DRAS',

  4  OBJECT_NAME => 'T1',

  5  OBJECT_TYPE => dbms_repair.table_object,

  6  FLAGS => dbms_repair.SKIP_FLAG);

  7  END;

  8  /

 

PL/SQL procedure successfully completed.

 

做完后我们再次查询表t1里的数据，可以看到，除了id为2的那条记录外，其他的数据都已经被救出来了：

SQL> select id from t1;

 

        ID

----------

         1

         3

         4

         5

         6

         7

         8

 

7 rows selected

 

Categories:

4 Comments

qsxing | February 18, 2009 8:18 PM | Reply

赞一个，学习了。。

luolongjiu | October 24, 2009 12:37 AM | Reply

itpub--->laoxiong---->dbsnake。。。。。

netwares | February 4, 2010 4:05 PM | Reply

"ultraEdit打开，并将其第16个byte的值由C7改为B7"，请问这个16是如何来的？

cui hua replied to comment from netwares | February 4, 2010 4:44 PM | Reply

oracle里data block的16、17位存的是checksum value,我在 http://dbsnake.com/2009/08/fractured-block-fix.html 里贴过一个data block的粗略结构。

Leave a comment

Search

Recent Comments

  • cui hua: oracle里data block的16、17位存的是checksum value,我在 http://dbsnake.com/2009/08/fractured-block-fix.html 里贴过一个data block的粗略结构。 read more
  • netwares: "ultraEdit打开，并将其第16个byte的值由C7改为B7"，请问这个16是如何来的？ read more
  • luolongjiu: itpub--->laoxiong---->dbsnake。。。。。 read more
  • qsxing: 赞一个，学习了。。 read more

Recent Entries

Tag Cloud

About this Entry

This page contains a single entry by cui hua published on February 18, 2009 2:55 PM.

2008年我写的一些文章 was the previous entry in this blog.

关于我 is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.