oracle数据库坏块修复

坏块修复

1．创建测试表插入数据

     create tablespace tbs_xb datafile '/opt/oracle/oradata/zlzcs/xb01.dbf' size 100m;

     create table tb_test(id integer,name varchar2(50)) tablespace tbs_xb;

     alter table tb_test add constraint pk_idprimary key (id);

 

      begin

        for i in 1..10000 loop

           insert into tb_test values(i,'striver'||i);

        end loop;

        commit;

       end;

      /

2．查看数据信息

    declare

      ridtyp number;

      objno number;

      rfno number;

      blno number;

      rowno number;

      rid rowid;

    begin

      select rowid into rid from tb_test where id=5000;

      dbms_rowid.rowid_info(rid,ridtyp,objno,rfno,blno,rowno,'SMALLFILE');

      dbms_output.put_line('rowidtype: '||ridtyp);

      dbms_output.put_line('objectno: '||objno);

      dbms_output.put_line('relativeno: '||rfno);

      dbms_output.put_line('blockno: '||blno);

      dbms_output.put_line('rowno: '||rowno);

   end;

3．创建BBED工具

cd  /opt/oracle/product/10.2.0/db_1/rdbms/lib

make -f ./ins_rdbms.mk  $ORACLE_HOME/rdbms/lib/bbed

4．使用BBED工具

       编辑bbed.par文件

       [oracle@localhost lib]$ more bbed.par

listfile=list

blocksize=8192

mode=edit

编辑LIST文件

执行如下SQL：

SQL> select file#||' '||name||' '||bytes from v$datafile;

 

FILE#||''||NAME||''||BYTES

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

1 /opt/oracle/oradata/zlzcs/system01.dbf 503316480

2 /opt/oracle/oradata/zlzcs/undotbs01.dbf 26214400

3 /opt/oracle/oradata/zlzcs/sysaux01.dbf 241172480

4 /opt/oracle/oradata/zlzcs/users01.dbf 5242880

5 /opt/oracle/oradata/zlzcs/xb01.dbf 104857600

 

LIST文件内容如下

[oracle@localhost lib]$ more list

1 /opt/oracle/oradata/zlzcs/system01.dbf 503316480

2 /opt/oracle/oradata/zlzcs/undotbs01.dbf 26214400

3 /opt/oracle/oradata/zlzcs/sysaux01.dbf 241172480

4 /opt/oracle/oradata/zlzcs/users01.dbf 5242880

5 /opt/oracle/oradata/zlzcs/xb01.dbf 104857600

运行BBED工具

[oracle@localhost lib]$ ./bbed parfile=bbed.par

Password:blockedit

默认密码：blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Fri May 24 21:42:002013

 

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

 

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

 

BBED> set dba 5,30

        DBA             0x0140001e (20971550 5,30)

BBED> find /c striver5000

 File:/opt/oracle/oradata/xesbi/xb01.dbf (5)

 Block: 30               Offsets: 6296 to 6807           Dba:0x0140001e

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

 73747269 76657235 3030302c010203c2 32640b73 74726976 65723439 39392c01

 0203c232 630b7374 7269766572343939 382c0102 03c23262 0b737472 69766572

 34393937 2c010203 c232610b73747269 76657234 3939362c 010203c2 32600b73

 74726976 65723439 39352c010203c232 5f0b7374 72697665 72343939 342c0102

 03c2325e 0b737472 6976657234393933 2c010203 c2325d0b 73747269 76657234

 3939322c 010203c2 325c0b7374726976 65723439 39312c01 0203c232 5b0b7374

 72697665 72343939 302c010203c2325a 0b737472 69766572 34393839 2c010203

 c232590b 73747269 766572343938382c 010203c2 32580b73 74726976 65723439

 38372c01 0203c232 570b737472697665 72343938 362c0102 03c23256 0b737472

 69766572 34393835 2c010203c232550b 73747269 76657234 3938342c 010203c2

 32540b73 74726976 6572343938332c01 0203c232 530b7374 72697665 72343938

 322c0102 03c23252 0b73747269766572 34393831 2c010203 c232510b 73747269

 76657234 3938302c 010203c232500b73 74726976 65723439 37392c01 0203c232

 4f0b7374 72697665 72343937382c0102 03c2324e 0b737472 69766572 34393737

 2c010203 c2324d0b 7374726976657234 3937362c 010203c2 324c0b73 74726976

 65723439 37352c01 0203c2324b0b7374 72697665 72343937 342c0102 03c2324a

 

 <32 bytes per line>

BBED> dump /v dba 5,30 offset 6296 count 11;

 File:/opt/oracle/oradata/zlzcs/xb01.dbf (5)

 Block: 30      Offsets: 6296 to 6306  Dba:0x0140001e

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

 73747269 76657235 303030            l striver5000

 

 <16 bytes per line>

BBED> modify 100 dba 5,30

 File:/opt/oracle/oradata/zlzcs/xb01.dbf (5)

 Block: 30               Offsets: 6296 to 6306           Dba:0x0140001e

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

 64747269 76657235 303030

 

 <32 bytes per line>

BBED> dump /v dba 5,30 offset 6296 count 11;

 File:/opt/oracle/oradata/zlzcs/xb01.dbf (5)

 Block: 30      Offsets: 6296 to 6306  Dba:0x0140001e

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

 64747269 76657235 303030            l dtriver5000

 

 <16 bytes per line>

5．创建repair_table，orphan_key_table

      SQL> begin

                                 dbms_repair.admin_tables(

                                 table_name=>'REPAIR_TABLE',

                                 table_type=>dbms_repair.repair_table,

                                 action=>dbms_repair.create_action,

                                   tablespace=>'USERS');

                    end;

                         /

     SQL> begin

                            dbms_repair.admin_tables(

                            table_name=>'ORPHAN_KEY_TABLE',

                            table_type=>dbms_repair.orphan_table,

                            action=>dbms_repair.create_action,

                            tablespace=>'USERS');

                  end;

          /

     检测坏块

SQL> declare

                          crpt_count int;

                          begin

                            crpt_count:=0;

                          dbms_repair.check_object(

                          schema_name=>'SYS',

                          object_name=>'TB_TEST',

                          repair_table_name=>'REPAIR_TABLE',

                          corrupt_count=>crpt_count);

                  end;

                   /

     定位坏块

     SQL>declare

                           fix_count int;

                 begin

                             fix_count:=0;

                           dbms_repair.fix_corrupt_blocks(

                           schema_name=>'SYS',

                           object_name=>'TB_TEST',

                           object_type=>dbms_repair.table_object,

                            repair_table_name=>'REPAIR_TABLE',

                             fix_count=>fix_count);

           end;

  /

查找指向坏块的索引块信息

SQL> l

    declare

    num_orphans int;

    begin

    num_orphans:=0;

   dbms_repair.dump_orphan_keys(

    schema_name=>'SYS',

    object_name=>'PK_ID',

   object_type=>dbms_repair.index_object,

   repair_table_name=>'REPAIR_TABLE',

   orphan_table_name=>'ORPHAN_KEY_TABLE',

   key_count=>num_orphans);

  end;

         跳过坏块

     SQL>begin        

                           dbms_repair.skip_corrupt_blocks(

                             schema_name=>'SYS',

                           object_name=>'TB_TEST',

                           object_type=>dbms_repair.table_object,

                           flags=>dbms_repair.skip_flag);

            end;

                  /