跳过Oracle数据库坏块方法

------------------------模拟坏块---------------------------------

查询某条数据的rowid

SQL> select object_id,object_name,object_type,rowid from my_user.tb_test where object_name='DBA_OBJECTS'

 OBJECT_ID OBJECT_NAME      OBJECT_TYPE     ROWID

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

      3310 DBA_OBJECTS      VIEW            AAAD9zAAJAAAACvAAS

      3311 DBA_OBJECTS      SYNONYM         AAAD9zAAJAAAACvAAT

查询某条数据的rowid，通过 rowid查看行数据的文件号，块号

SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno  from my_user.tb_test where object_id=3311;

ROWID             REL_FNO   BLOCKNO     

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

AAAD9zAAJAAAACvAAT      9        175   

查询对应数据文件

SQL> select file_name from dba_data_files where file_id=9;

FILE_NAME

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

/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf

bbed修改对应数据块

[oracle@rac-dg Desktop]$ bbed password=blockedit filename=/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf

BBED: Release 2.0.0.0.0 - Limited Production on Tue Sep 20 16:34:37 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

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

BBED> set block 175

         BLOCK#         175

BBED> show

         FILE#              0

         BLOCK#         175

         OFFSET            0

         DBA            0x00000000 (0 0,175)

         FILENAME       /u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf

         BIFILE              bifile.bbd

         LISTFILE            

         BLOCKSIZE         8192

         MODE                    Browse

         EDIT              Unrecoverable

         IBASE             Dec

         OBASE          Dec

         WIDTH                    80

         COUNT                   512

         LOGFILE           log.bbd

         SPOOL            No

BBED> dump

 File: /u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf (0)

 Block: 175              Offsets:    0 to  511           Dba:0x00000000

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

 06a20000 af004002 b2740a00 00000104 0bc70000 01000000 733f0000 a1740a00

 00000000 03003201 a0004002 ffff0000 00000000 00000000 00000000 00800000

 a1740a00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00015100

 ffffb400 e3032f03 2f030000 5100211f c81e691e 181ec11d 6c1d111d b61c561c

 fc1b9c1b 421be21a 901a381a e9199819 4119f018 99184018 e1178c17 3117dd16

 83162f16 d5157e15 2115cc14 71141d14 c3136f13 1513c612 73121a12 c7116e11

 1c11c410 71102210 c90f690f 050fad0e 5b0e030e b20d5b0d 0b0db30c 550c000c

 a50b550b ff0aa80a 4b0af709 9d094d09 f708a008 4308ef07 95074207 e9069706

 3f06ed05 95053f05 e3049304 3e04e303 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes="" per="" line="">

BBED> set mode edit

         MODE                    Edit

BBED> modify /x 1234

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

 File: /u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf (0)

 Block: 175              Offsets:    0 to  511           Dba:0x00000000

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

 12340000 af004002 b2740a00 00000104 0bc70000 01000000 733f0000 a1740a00

 00000000 03003201 a0004002 ffff0000 00000000 00000000 00000000 00800000

 a1740a00 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00015100

 ffffb400 e3032f03 2f030000 5100211f c81e691e 181ec11d 6c1d111d b61c561c

 fc1b9c1b 421be21a 901a381a e9199819 4119f018 99184018 e1178c17 3117dd16

 83162f16 d5157e15 2115cc14 71141d14 c3136f13 1513c612 73121a12 c7116e11

 1c11c410 71102210 c90f690f 050fad0e 5b0e030e b20d5b0d 0b0db30c 550c000c

 a50b550b ff0aa80a 4b0af709 9d094d09 f708a008 4308ef07 95074207 e9069706

 3f06ed05 95053f05 e3049304 3e04e303 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 <32 bytes="" per="" line="">

查询坏块报错

SQL> select count(*) from my_user.tb_test;

select count(*) from my_user.tb_test

                             *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 9, block # 175)

ORA-01110: data file 9:

'/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf'

dbv检查坏块数量

[oracle@rac-dg Desktop]$ dbv file=/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf

DBVERIFY: Release 11.2.0.3.0 - Production on Tue Sep 20 16:44:30 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf

Page 175 is marked corrupt

Corrupt block relative dba: 0x024000af (file 9, block 175)

Bad header found during dbv:

Data in bad block:

 type: 18 format: 4 rdba: 0x024000af

 last change scn: 0x0000.000a74b2 seq: 0x1 flg: 0x04

 spare1: 0x0 spare2: 0x0 spare3: 0x2700

 consistency value in tail: 0x74b20601

 check value in block header: 0x760e

 computed block checksum: 0x0

DBVERIFY - Verification complete

Total Pages Examined         : 384

Total Pages Processed (Data) : 205

Total Pages Failing   (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing   (Index): 0

Total Pages Processed (Other): 139

Total Pages Processed (Seg)  : 0

Total Pages Failing   (Seg)  : 0

Total Pages Empty            : 39

Total Pages Marked Corrupt   : 1

Total Pages Influx           : 0

Total Pages Encrypted        : 0

Highest block SCN            : 685266 (0.685266)

------------------------------------------跳过坏块方法---------------------------

方法一，10231事件跳过坏块,只对全表扫有用，建议CTAS方式跳过坏块创建新表

SQL> alter session set events '10231 trace name context forever,level 10' ;

Session altered.

SQL> select count(*) from my_user.tb_test;

  COUNT(*)

----------

     15529

方法二，调用DBMS_REPAIR包跳过坏块

创建坏块记录表，记录表名必须以REPAIR_开头

SQL> BEGIN

  DBMS_REPAIR.ADMIN_TABLES (

  TABLE_NAME => 'REPAIR_TABLE',

  TABLE_TYPE => dbms_repair.repair_table,

  ACTION => dbms_repair.create_action,

  TABLESPACE => '&tablespace_name');

END;

/  2    3    4    5    6    7    8 

Enter value for tablespace_name: MY_TABLESPACE

old   6:   TABLESPACE => '&tablespace_name');

new   6:   TABLESPACE => 'MY_TABLESPACE');

PL/SQL procedure successfully completed.

检查指定对象坏块，并将坏块信息写入记录表REPAIR_TABLE

SQL> set serveroutput on

DECLARE num_corrupt INT;

BEGIN

  num_corrupt := 0;

  DBMS_REPAIR.CHECK_OBJECT (

  SCHEMA_NAME => '&schema_name',

  OBJECT_NAME => '&object_name',

  REPAIR_TABLE_NAME => 'REPAIR_TABLE',

  corrupt_count => num_corrupt);

  DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));

END;

/SQL>   2    3    4    5    6    7    8    9   10   11 

Enter value for schema_name: MY_USER

old   5:   SCHEMA_NAME => '&schema_name',

new   5:   SCHEMA_NAME => 'MY_USER',

Enter value for object_name: TB_TEST

old   6:   OBJECT_NAME => '&object_name',

new   6:   OBJECT_NAME => 'TB_TEST',

number corrupt: 1

PL/SQL procedure successfully completed.

查询记录表数据

SQL> select object_id,BLOCK_ID,CORRUPT_TYPE,MARKED_CORRUPT,CHECK_TIMESTAMP,FIX_TIMESTAMP from repair_table;

 OBJECT_ID   BLOCK_ID CORRUPT_TYPE MARKED_COR CHECK_TIMESTAMP     FIX_TIMESTAMP

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

     16243    175         6148 TRUE       2016-09-20 17:30:35

标记坏块

SQL> DECLARE num_fix INT;

BEGIN

  num_fix := 0;

  DBMS_REPAIR.FIX_CORRUPT_BLOCKS (

  SCHEMA_NAME => '&schema_name',

  OBJECT_NAME=> '&object_name',

  OBJECT_TYPE => dbms_repair.table_object,

  REPAIR_TABLE_NAME => 'REPAIR_TABLE',

  FIX_COUNT=> num_fix);

  DBMS_OUTPUT.PUT_LINE('num fix: ' || to_char(num_fix));

END;

/  2    3    4    5    6    7    8    9   10   11   12 

Enter value for schema_name: MY_USER

old   5:   SCHEMA_NAME => '&schema_name',

new   5:   SCHEMA_NAME => 'MY_USER',

Enter value for object_name: TB_TEST

old   6:   OBJECT_NAME=> '&object_name',

new   6:   OBJECT_NAME=> 'TB_TEST',

num fix: 0

PL/SQL procedure successfully completed.

设置DML语句跳过坏块

SQL> BEGIN

  DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (

  SCHEMA_NAME => '&schema_name',

  OBJECT_NAME => '&object_name',

  OBJECT_TYPE => dbms_repair.table_object,

  FLAGS => dbms_repair.SKIP_FLAG);

END;

/  2    3    4    5    6    7    8 

Enter value for schema_name: MY_USER

old   3:   SCHEMA_NAME => '&schema_name',

new   3:   SCHEMA_NAME => 'MY_USER',

Enter value for object_name: TB_TEST

old   4:   OBJECT_NAME => '&object_name',

new   4:   OBJECT_NAME => 'TB_TEST',

PL/SQL procedure successfully completed.

查询数据，已经跳过坏块

SQL> select count(*) from my_user.tb_test;

  COUNT(*)

----------

     15529

当有跳块操作时，系统会记录一条记录在trace文件中    

     *** 2016-09-20 17:44:23.376

table scan: segment: file# 9 block# 130

            skipping corrupt block file# 9 block# 175

table scan: segment: file# 9 block# 130

*** 2016-09-20 18:00:50.744

            skipping corrupt block file# 9 block# 175

table scan: segment: file# 9 block# 130

*** 2016-09-21 10:21:45.395

            skipping corrupt block file# 9 block# 175

*** 2016-09-21 10:23:17.978

table scan: segment: file# 9 block# 130

            skipping corrupt block file# 9 block# 175

*** 2016-09-21 10:32:46.715

table scan: segment: file# 9 block# 130

            skipping corrupt block file# 9 block# 175

*** 2016-09-21 10:33:06.150

table scan: segment: file# 9 block# 130

            skipping corrupt block file# 9 block# 175

查询坏块中的数据无结果，没有报错

SQL> select rowid, dbms_rowid.rowid_relative_fno(rowid) rel_fno, dbms_rowid.rowid_block_number(rowid) blockno  from my_user.tb_test where object_id=3311;

no rows selected

SQL> select owner,table_name,SKIP_CORRUPT,TABLESPACE_NAME from dba_tables where owner='MY_USER';

OWNER                  TABLE_NAME             SKIP_COR TABLESPACE_NAME

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

MY_USER                TB_TEST                ENABLED  MY_TABLESPACE

取消跳过坏块操作

SQL> select owner,table_name,SKIP_CORRUPT from dba_tables where table_name='TB_TEST';

OWNER                  TABLE_NAME             SKIP_COR

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

MY_USER                TB_TEST                ENABLED

SQL> execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(schema_name=>'MY_USER',object_name=>'TB_TEST',object_type=>dbms_repair.table_object,flags=>dbms_repair.noskip_flag);

PL/SQL procedure successfully completed.

SQL> select owner,table_name,SKIP_CORRUPT from dba_tables where table_name='TB_TEST';

OWNER                  TABLE_NAME             SKIP_COR

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

MY_USER                TB_TEST                DISABLED

SQL> select count(*) from my_user.tb_test;

select count(*) from my_user.tb_test

                             *

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 9, block # 175)

ORA-01110: data file 9:

'/u01/app/oracle/oradata/testdb/testdb/my_tablespace_01.dbf'

附：还可以使用数据泵导入导出的方法跳过坏块。