狸猫换太子－online修复ora-08103错误的另外一种方法

Posted: December 12, 2011 | Author: Cui Hua | Filed under: Oracle | Tags: ORA-08103 | 7 Comments »

我在“如何online修复ora-08103错误”这篇文章里介绍了一种常规的、online修复ora-08103错误的方法。现在我再来介绍一种更彻底的方法——即所谓的狸猫换太子。

 

现在表t1里是有数据的，但是因为其所在的块坏了（9_3739，即file 9，block 3739，如何定位这个坏块，我在“如何online修复ora-08103错误”这篇文章里有介绍），导致我现在既查不了，也建不了索引：

SQL_testdb>select count(*) from scott.t1;

select count(*) from scott.t1

                           *

ERROR at line 1:

ORA-08103: object no longer exists

 

SQL_testdb>create unique index scott.idx_t1 on scott.t1(object_id);

create unique index scott.idx_t1 on scott.t1(object_id)

                                          *

ERROR at line 1:

ORA-08103: object no longer exists

 

现在我们来构造一个好块，把上述坏块替换掉，以彻底的骗过oracle，注意，我这里所有的操作全部是在上述库open的状态下做的：

SQL_testdb>create table t2 tablespace users as select * from t1 where 1=2;

 

Table created.

 

SQL_testdb>insert into t2 select * from t1 where rownum<2;

 

1 row created.

 

SQL_testdb>commit;

 

Commit complete.

 

SQL_testdb>select dbms_rowid.rowid_relative_fno(rowid)||’_'||dbms_rowid.rowid_block_number(rowid) location,count(*) from t2 group by dbms_rowid.rowid_relative_fno(rowid)||’_'||dbms_rowid.rowid_block_number(rowid);

 

LOCATION                                            COUNT(*)

——————————————————————————— ———-

9_3752                                                   1

 

SQL_testdb>delete from t2;

 

1 row deleted.

 

SQL_testdb>commit;

 

Commit complete.

 

SQL_testdb>select count(*) from t2;

 

  COUNT(*)

———-

         0

 

SQL_testdb>select to_char(data_object_id,’XXXXXXXX’) from dba_objects where owner=’SCOTT’ and object_name=’T1′;

 

TO_CHAR(D

———

     7703

 

SQL_testdb>select to_char(data_object_id,’XXXXXXXX’) from dba_objects where owner=’SCOTT’ and object_name=’T2′;

 

TO_CHAR(D

———

     770F

 

我这里truncate t2，强制让oracle把t2的所有dirty block写回datafile：

SQL_testdb>truncate table t2;

 

Table truncated.

 

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/bin]#dd if=/dras20/testdb/users01.dbf of=9_3752.dmp bs=8192 skip=3752 count=1

1+0 records in.

1+0 records out.

 

[P550_04_LA:oracle@:/cadrasu01/app/oracle/product/9.2.0/bin]#dd if=9_3752.dmp of=/dras20/testdb/users01.dbf bs=8192 seek=3739 count=1 conv=notrunc

1+0 records in.

1+0 records out.

 

接着，我只需要用BBED online改一下RDBA和data object id就OK了：

BBED> set file 9 block 3739

        FILE#           9

        BLOCK#          3739

 

BBED> set offset 4

        OFFSET          4

 

BBED> dump

 File: /dras20/testdb/users01.dbf (9)

 Block: 3739             Offsets:    4 to  515           Dba:0x02400e9b

————————————————————————

 02400ea8 800007e3 00000106 b1330000 01000000 0000770f 800007e1 00007368

 00023200 02400ea1 00100021 00000111 02c000c3 002b3b00 80000000 80000752

 ……省略显示部分内容

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> modify /x 02400e9b

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

 File: /dras20/testdb/users01.dbf (9)

 Block: 3739             Offsets:    4 to  515           Dba:0x02400e9b

————————————————————————

 02400e9b 800007e3 00000106 b1330000 01000000 0000770f 800007e1 00007368

 00023200 02400ea1 00100021 00000111 02c000c3 002b3b00 80000000 80000752

  ……省略显示部分内容

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> set offset 24

        OFFSET          24

 

BBED> dump

 File: /dras20/testdb/users01.dbf (9)

 Block: 3739             Offsets:   24 to  535           Dba:0x02400e9b

————————————————————————

 0000770f 800007e1 00007368 00023200 02400ea1 00100021 00000111 02c000c3

 002b3b00 80000000 80000752 00140010 00000120 02c0008d 00352100 2001005e

 ……省略显示部分内容

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> modify /x 00007703

 File: /dras20/testdb/users01.dbf (9)

 Block: 3739             Offsets:   24 to  535           Dba:0x02400e9b

————————————————————————

 00007703 800007e1 00007368 00023200 02400ea1 00100021 00000111 02c000c3

 002b3b00 80000000 80000752 00140010 00000120 02c0008d 00352100 2001005e

 ……省略显示部分内容

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 

 <32 bytes per line>

 

BBED> sum apply

Check value for File 9, Block 3739:

current = 0xb10c, required = 0xb10c

 

现在偷梁换柱的工作已经做完了，我们再去看一下效果：

SQL_testdb>select count(*) from scott.t1;

 

  COUNT(*)

———-

       971

 

SQL_testdb>create unique index scott.idx_t1 on scott.t1(object_id);

 

Index created.

 

可以看到，oracle已经被我们彻底的骗过了。

 

---