# Oracle corrupt block（坏块） 详解

+关注继续查看

## 一. 坏块说明

### 1.1 相关链接

在看坏块之前，先看几个相关的链接，在后面的说明中，会用到链接中的一些内容。

ORA-600 各个参数含义说明

http://blog.csdn.net/tianlesoftware/article/details/6645809

Oracle 不同故障的恢复方案

http://blog.csdn.net/tianlesoftware/article/details/6106178

Current online Redo 和 Undo 损坏的处理方法

http://blog.csdn.net/tianlesoftware/article/details/6261475

Oracle DBV 工具 说明

http://blog.csdn.net/tianlesoftware/article/details/5015164

Oracle BBED 工具 说明

http://blog.csdn.net/tianlesoftware/article/details/5006580

Oracle bbed 五个 实用示例

http://blog.csdn.net/tianlesoftware/article/details/6684505

Oracle datafile block 格式 说明

http://blog.csdn.net/tianlesoftware/article/details/6654786

MOS 上的相关文档：

RMAN: Block-Level Media Recovery - Concept & Example [ID 144911.1]

FAQ:Physical Corruption [ID 403747.1]

MasterNote for Handling Oracle Database Corruption Issues [ID 1088018.1]

HandlingOracle Block Corruptions in Oracle7/8/8i/9i/10g/11g [ID 28814.1]

ExtractingData from a Corrupt Table using ROWID Range Scans in Oracle8 and higher [ID61685.1]

ValidatingDatabase Files and Backups

Performing Block Media Recovery

### 1.2  block corruption 分类

For purposes of the paper we will categorize corruption under three general areasand give best practices for prevention, detection and repair for each:

Memory corruption

Logical corruption(soft corruption)

Media corruption(Physicalcorruption)

Physicalor structural corruption can be defined as damage to internal data structureswhich do not allow Oracle software to find user data within the database.        Logical corruption involves Oracle beingable to find the data, but the data values are incorrect as far as the end useris concerned.

Physica lcorruption due to hardware or software can occur in two general places -- inmemory (including various IO buffers and the Oracle buffer cache) or on disk.Operator error such as overwriting a file can also be defined as a physicalcorruption.   Logical corruption on theother hand is usually due to end-user error or non-robust(?) applicationdesign. A small physical corruption such as a single bit flip may be mistakenfor a logical error.

### 1.3 查看blockcorruption

可以通过v$database_block_corruption 查看database 的corruption。 官网对该视图的定义如下： V$DATABASE_BLOCK_CORRUPTION displaysinformation about database blocks that were corrupted after the last backup.

SYS@dave2(db2)>desc v$database_block_corruption Name Null? Type ------------------------------------------------- ---------------------------- FILE# NUMBER BLOCK# NUMBER BLOCKS NUMBER CORRUPTION_CHANGE# NUMBER CORRUPTION_TYPE VARCHAR2(9) SYS@dave2(db2)> select * fromv$database_block_corruption;

no rows selected

这里没有坏块，为了演示这个效果，我用BBED 制造一个坏块，然后在用bbed 恢复过来。

SYS@dave2(db2)> select * from dvd;

JOB

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

DMM  is DBA!

dmme like Oracle!

SYS@dave2(db2)>select

2  rowid,

3  dbms_rowid.rowid_relative_fno(rowid)rel_fno,

4 dbms_rowid.rowid_block_number(rowid)blockno,

5 dbms_rowid.rowid_row_number(rowid) rowno

6  from dvd;

ROWID                 REL_FNO    BLOCKNO     ROWNO

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

AAAN9hAAGAAAAAcAAA          6         28          0

AAAN9hAAGAAAAAcAAB          6         28          1

[oracle@db2 ~]$bbed parfile=/u01/bbed.par Password: BBED: Release 2.0.0.0.0 - LimitedProduction on Mon Aug 15 22:15:10 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. *************!!! For Oracle Internal Use only !!! *************** BBED> set dba 6,28 DBA 0x0180001c(25165852 6,28) BBED> map File: /u01/app/oracle/oradata/dave2/dave01.dbf(6) Block: 28 Dba:0x0180001c ------------------------------------------------------------ KTBData Block (Table/Cluster) struct kcbh, 20 bytes @0 struct ktbbh, 96 bytes @20 struct kdbh, 14 bytes @124 struct kdbt[1], 4 bytes @138 sb2kdbr[2] @142 ub1freespace[8005] @146 ub1rowdata[37] @8151 ub4tailchk @8188 BBED> d /voffset 0 count 128 File: /u01/app/oracle/oradata/dave2/dave01.dbf(6) Block: 28 Offsets: 0 to 127 Dba:0x0180001c ------------------------------------------------------- 06a20000 1c008001 f4a90780 00000104 l.¢......ô©...... f5b40000 01000000 61df0000 f4a90780 lõ´......aß..ô©.. 00000000 03003200 19008001 03001000 l......2......... 3b0a0000 00000000 00000000 00800000 l;............... dda90780 00000000 00000000 00000000 lÝ©.............. 00000000 00000000 00000000 00000000 l................ 0000000000000000 00000000 00000000 l ................ 00000000 00000000 00000000 00010200 l................ <16 bytes per line> BBED> modify /x 12345678 offset 0 File: /u01/app/oracle/oradata/dave2/dave01.dbf(6) Block: 28 Offsets: 0to 127 Dba:0x0180001c ------------------------------------------------------------------------ 12345678 1c008001 f4a90780 00000104 f5b4000001000000 61df0000 f4a90780 00000000 03003200 19008001 03001000 3b0a000000000000 00000000 00800000 dda90780 00000000 00000000 00000000 0000000000000000 00000000 00000000 00000000 00000000 00000000 00000000 0000000000000000 00000000 00010200 <32 bytes per line> BBED> sum apply Check value for File 6, Block 28: current = 0x5ab7, required = 0x5ab7 SYS@dave2(db2)>alter system flush buffer_cache; System altered. SYS@dave2(db2)> select * from dvd; select * from dvd * ERROR at line 1: ORA-01578: ORACLE data block corrupted(file # 6, block # 28) ORA-01110: data file 6:'/u01/app/oracle/oradata/dave2/dave01.dbf' 这里提示块有错误，我们查看下一下：v$database_block_corruption:

SYS@dave2(db2)> select * fromv$database_block_corruption; no rows selected 这里显示为空，但是我们之前看该视图的定义的时候，说是自上次备份以来的坏块。所以这里我们验证一下： RMAN> backup validate datafile 6; Starting backupat 15-AUG-11 using target database control file insteadof recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=141 devtype=DISK channel ORA_DISK_1: starting full datafilebackupset channel ORA_DISK_1: specifying datafile(s)in backupset input datafile fno=00006 name=/u01/app/oracle/oradata/dave2/dave01.dbf channel ORA_DISK_1: backup set complete,elapsed time: 00:00:02 Finished backup at 15-AUG-11 在次select 查询： SYS@dave2(db2)> select * from v$database_block_corruption;

FILE#    BLOCK#     BLOCKSCORRUPTION_CHANGE# CORRUPTIO

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

6         15          1                  0 CORRUPT

6        28          1                  0 CORRUPT

（1）ALL ZERO：Block header on disk contained only zeros. The block may be valid ifit was never filled and if it is in an Oracle7 file. The buffer will bereformatted to the Oracle8 standard for an empty block.

（2）FRACTURED： Block header looks reasonable, but the front and back of the blockare different versions.

（3）CHECKSUM： optional check value shows that the block is not self-consistent.It is impossible to determine exactly why the check value fails, but itprobably fails because sectors in the middle of the block are from differentversions.

（4）CORRUPT： Block is wrongly identified or is not a data block (for example,the data block address is missing)

（5）LOGICAL： Specifies the range is for logically corrupt blocks.CORRUPTION_CHANGE# will have a nonzero value.

BBED> revert

All changes made in this session will berolled back. Proceed? (Y/N) y

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

BBED> sum apply

Check value for File 6, Block 28:

current = 0xb4f5, required = 0xb4f5

SYS@dave2(db2)> alter system flushbuffer_cache;

System altered.

SYS@dave2(db2)> select * from dvd;

JOB

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

DMM  is DBA!

dmme like Oracle!

FILE#    BLOCK#     BLOCKSCORRUPTION_CHANGE# CORRUPTIO

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

6         15          1                  0 CORRUPT

6         28          1                  0 CORRUPT

corruption block的信息还存在里面。 之前经过该视图和备份有关，我们用rman validate 一下datafile，之后就ok了。

RMAN> backup validate datafile 6;

Starting backupat 15-AUG-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafilebackupset

channel ORA_DISK_1: specifying datafile(s)in backupset

channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01

Finished backup at 15-AUG-11

SYS@dave2(db2)>select * from v$database_block_corruption; FILE# BLOCK# BLOCKSCORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------------------------- --------- 6 15 1 0 CORRUPT --说明，我们修改的是block 28，它已经消失了。block 15 不是我们这本次测试修改的。 它是历史遗留问题。这里就不讨论了。 如果用rmanvalidate 之后还没有消失，可能是oracle的bug，参考： V$Database_Block_CorruptionDoes not clear after Block Recover Command [ID 422889.1]

### 1.4 使用RMAN 验证和recover corruption

之前有整理相关的文章，参考：

RMAN的默认validate 只验证物理坏块，不验证逻辑坏块。

RMAN>backup validate database archivelogall;

RMAN>backup validate database;

RMAN>backup validate check logicaldatabase archivelog all;

RMAN>backup validate check logicaldatabase;

注意： 如果加上了archivelog all，就必须要有归档文件存在。

RMAN>VALIDATE DATAFILE 1 BLOCK 10;

对于物理坏块，我们可以通过recover database 或者recover datafile 来解决，但是对于逻辑坏块这种方法就不行。 在后面单独讲到逻辑坏块时有说明。对于逻辑坏块可以尝试对对象进行重建，如重建索引，重建表在导入数据。

对于物理坏块，如果不使用recover，那么块上的data 基本是丢失的。 我们可以采用相关的event或者通过rowid来跳过block，导出数据。 要保证块上数据不丢失，就需要通过RMAN有效的备份来进行recover。

RMAN>blockrecover datafile 6 block 15;

RMAN> blockrecover corruption list;

datadictionary 是在创建数据库时通过$ORACLE_HOME/dbs/sql.bsq脚本创建的，并且这些核心的表，索引和clusters 不能被drop 和recreate。 他们在db 启动时使用。 2) Views createdon the fixed data structures (V$ views).

3)SYSTEM rollback segment created after the databasecreation. If the corruption is in this segment, then the most recentbackup should be restored and a point in time recovery should be done on thedatabase up to the time when the corruption could have occurred.

system rollback segment 在数据库创建之后创建。 关于systemrollback segment在我的blog里有说明：

4)Compatibility segment (this is the only segment of type ‘CACHE’ in the SYSTEMtablespace). The Compatibility segment is a segment that keeps track of thefeatures being used in the database which will be used when the database isbeing downgraded to an earlier version. This segment is used to make sure thatthe features being used in the current version are disabled before beingdowngraded to the earlier version.  Ifthe compatibility segment has a corruption then, the database can be brought upby shutting down and starting it up.  Ifthe problem is still not fixed call Oracle support with appropriate trace filesand the alert.log.

The supported way of fixing data dictionary corruption is to restore from a backupand roll forward using the archived redo logs.

#### 4.3.4 Undo header and Undo blocks (ROLLBACK tablespace)

Rollback segments are undo segments that have information about the transaction that hasbeen executed so that it can be rolled back in case of failure of thetransaction or when the user asks for the transaction to be rolled backexplicitly.

They are made up of undo header blocks and undo blocks which are required to accessundo information to provide for consistent reads and transactionconsistency.  If the rollback segment iscorrupted, the transaction consistency of the data blocks (including datadictionary objects) can be jeopardized.

The corruption found when undoing atransaction could fall under three categories ：

（1）belongs to the object (table/index/cluster) that has the data onwhich the transaction was executed (data block)

（2）belongs to the undo block that is being used to undo the transaction(undo header)

（3）belongs to the undo segment header block of the segment where theundo block is found (undo block)

Thefirst case where the corruption is in the object to which the activetransaction belongs to, we have to identify the object first.  This can be done by setting an event in theINIT.ORA as follows:

event= “10015 trace name context forever, level 10”

这种情况的坏块是active transaction，当我们重启DB后，会进行相关的transaction recover（Rolling Back）。 当recover 完成，就可以正常访问对应的block了。 相关文章参考：

Thisevent traces the undo segment recovery when the database is started, the eventputs out a trace in the directory specified by the INIT.ORA parameter,USER_DUMP_DEST.  This trace file containsa transaction table for each of the rollback segments that are onlined in thedatabase.  The trace file has a messagethat says ‘error recovering tx(#, #) object #’. Tx(#, #) refers to thetransaction information and the object # is the object id of the object thathas a corruption.

设置event时候，会dump DB 启动是的undo segment recovery，在trace里会有‘error recovering tx(#, #) object#’信息，其中Tx(#, #)指的是事务信息，object #是corruption 的对象id。找到对象ID 后可以通过如下SQL查询：

The following query gives the name of theobject that is corrupted:

SQL>SELECTOWNER, OBJECT_NAME, OBJECT_TYPE, STATUS  FROM SYS.DBA_OBJECTS WHERE OBJECT_ID = <object #_from_tracefile>;

#### 4.3.5 Sort blocks (TEMP tablespace)

Sortsare usually done in the part of memory allocated from the SGA.  This is defined by the INIT.ORA parameterSORT_AREA_SIZE.  If the sort space neededfor a sort is so big that it cannot fit in the sort area defined in memory,then it is done on disk by creating segments called the Temporary segments.

当SORT_AREA_SIZE 指定的sort 空间不够时，会在磁盘上创建一个temporary segments来作为排序使用。 建议每个用户指定自己的temporary tablespace。

Itis advisable to create a separate tablespace called the TEMP tablespace.  After creating this tablespace, alter theusers to use this as their temporary tablespace by executing the followingcommand:

ALTERUSER user_name TEMPORARY TABLESPACE TEMP;

This way, the temporary segments created by any user will be in the TEMP tablespaceand it provides easy manageability.

Detection

· Usually thissegment is never corrupted since they are reformatted every time they get used

Repair

· Tempsegments are reused frequently

· If problempersists, either move or drop and recreate the temp tablespace

#### 4.3.6 Data/Index blocks (DATA/INDEX tablespace)

Whena data block is corrupted, when it belongs to a table segment, cluster segmentor an index segment, the detection mechanisms are the same:

datablock的corrupt可以出现在table segment，cluster segment和index segment。

（1）DBVERIFY can be used to detect the corrupted blocks in thedata file

（2）ANALYZE command run on the objects give errors (ANALYZE<table/cluster/index> <table/cluster/index_name> VALIDATESTRUCTURE;) When an ANALYZE (with CASCADE option) is run on a table or cluster,it cross verifies the index and data/cluster blocks along with the integritychecks done for the block.

（3）DB_BLOCK_CHECKSUM can be set to TRUE in the INIT.ORA file.When a block is changed and being written to disk from memory, DBWR calculatesa checksum for the block by summing up its contents and writes it in the blockalong with it on disk.  The next time when the block is being read by theforeground process, it calculates the checksum again for the block that isbeing read and compared with the checksum already written in the block ondisk.  If both are different then theblock has been corrupted on disk and so the block is not read into memory sothat it prevents cache corruption.  There is an overhead associated with this parameter since thechecksum is calculated each time it is read and written.

（4）Events 10210, 10211, 10212 can be set in the INIT.ORA file todetect software corrupt blocks.  When there is a corruption in a block, it is not detecteduntil the block is being updated. So any SELECTs on a corrupted block is executed until it is marked assoftware corrupt.  When the events areset in the INIT.ORA, the blocks are checked for integrity by comparing certaindata structures and once there is an inconsistency found, the seq or the sequence of the block is set to 0 in theblock header representing that the block is software corrupt.

The events can be set as follows:

event= “10210 trace name context forever, level 10”  (for data blocks)

event= “10211 trace name context forever, level 10”  (for index blocks)

event= “10212 trace name context forever, level 10”  (for cluster blocks)

（5）Users receiving ORA-1578 when trying to access an object.  The query from DBA_EXTENTS given in pg# 30shows that the error is on a table, cluster or index in the data tablespace,SQL语句如下：

SQL>selectsegment_type, segment_name from sys.dba_extents where file_id =<file_id_from_ora-1578> and <block_id_from_ora-1578> betweenblock_id and (block_id+blocks-1);

（6）ALERT.LOG shows ORA-1578 or ORA-600s with the first argument in therange of 2000 to 8000

#### 4.3.6.1 Tables

Whena data block is corrupted in a table, it should be understood that the data inthe corrupted block is lost.  The onlyway to not lose any data from the table is to restore from a valid backup andrecover until a point in time before the corruption occurred.

datablock 坏块通常意味着数据的丢失，如果要保证没有数据丢失，需要通过有效的备份进行恢复。这块参考1.4节。

#### 方法一：Event method

Event10231 can be set to skip corrupted blocks on full table scans in the INIT.ORAfile.  The object can be exported aftersetting this event.  This is notguaranteed to work for every kind of corruption.

This works only when the block is soft corrupted（逻辑坏块）, sequence is set to 0. The event can be set as follows:

event= “10231 trace name context forever, level 10”

对于table上的逻辑坏块，并且sequence 设置为0，可以设置10231 event，当全表扫描时，可以跳过corruption，从而读取数据。

#### 方法二：ROWID method

Extractthe data that does not belong to the corrupted block using ROWIDs. Every row inevery table in an Oracle database has a ROWID column which is usually not displayedunless SELECTed explicitly.

通过rowid，我们可以抽取出非corrupted block上的数据。关于ROWID的更多内容，参考我的Blog：

（1）通过函数构建ROWID

function ROWID_CREATE(rowid_type    IN number,

object_number INnumber,

relative_fno  IN number,

block_number  IN number,

row_number    IN number)

return ROWID;

-- rowid_type      - type(restricted=0/extended=1)

-- object_number   - data objectnumber

-- relative_fno    - relative filenumber

-- block_number    - block numberin this file

-- row_number      - row number inthis block

ROWID_TYPE:

Thisis 1 because we are using the extended rowid format.

RELATIVE_FNO:

Thisshould have been available when you came to this article. It can also be foundfrom the DBA_EXTENTS view given the absolute file number and block number ofthe corrupt block:

SQL>SELECTtablespace_name, relative_fno, segment_type, owner, segment_name, partition_nameFROM dba_extents WHERE file_id = <AFN>    AND  <BL> between block_id and block_id + blocks-1;

OBJECT_NUMBER:

For a non-partitioned table, select the DATA_OBJECT_ID from DBA_OBJECTS for theproblem table:

SQL>SELECT data_object_id FROM dba_objectsWHERE object_name = '<TABLE-NAME>' AND owner = '<TABLE-OWNER>' ;

Note that a partitionedtable has an object number for each partition.

--注意，对于分区表，每个分区有一个对象id：

Select the DATA_OBJECT_ID from DBA_OBJECTSthus:

SQL>SELECTdata_object_id FROM dba_objects WHERE object_name = '<TABLE-NAME>' ANDowner = '<TABLE-OWNER>' AND subobject_name = '<PARTITION-NAME>' ;

相对与普通表，普通表多了一个subojbect_name 条件。

BLOCK_NUMBER andROW_NUMBER:

Theblock number of the corrupt block should be available before you came to thisarticle. (Eg: It is reported in an ORA-1578 error, or as a Page Number byDBVerify).

Fora ROWID range scan we generally want to select all rows BEFORE the corruptblock, then all rows AFTER the corrupt block. The first row in a block is rowzero (0) and so we want all rowids LESS THAN "Block <BL> row 0"and then GREATER THAN OR EQUAL TO "Block <BL>+1 row 0".

（2）创建ROWID

You can now create the rowid strings to use in a predicate thus:

The "LOW_RID"is the lowest rowid INSIDE the corrupt block:

SQL>SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0)LOW_RID  from DUAL;

The "HI_RID" isthe first rowid AFTER the corrupt block:

SQL>SELECT  dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0)HI_RID       from DUAL;

（3）重建数据

Itis now possible to use CREATE TABLE AS SELECT or INSERT ... SELECT to get datawithout accessing the corrupt block using a query of the form:

根据刚才查询的rowid，跳过corrupt block来进行createtable 或者insert 操作：

SQL>CREATE TABLE salvage_table AS SELECT/*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid <'<low_rid>'   ;

SQL>INSERT INTO salvage_table SELECT /*+ROWID(A) */ * FROM <owner.tablename> A WHERE rowid >='<hi_rid>';

Fora table partition then only the problem partition need be selected from byusing the PARTITION(xxx) option in the FROM clause:

对于分区表，仅需要对问题分区进行处理：

SQL>CREATE TABLE salvage_table AS SELECT/*+ ROWID(A) */ * FROM <owner.tablename> PARTITION(<partition_name>) A WHERE rowid < '<lo_rid>';

SQL>INSERT INTO salvage_table SELECT /*+ROWID(A) */ * FROM <owner.tablename> PARTITION (<partition_name>) AWHERE rowid >= '<hi_rid>';

注意： 采用上面这种rowid的方法，不能处理含有LONG字段的表，对于LONG字段的表，只能使用带有where 条件的export/import.

If the corrupt block is the table segment header, this method won't work. You stillhave the option of using any indexes on the corrupt table to extract the data.

如果是块头出现corrupt，那么这个方法不使用。 当块头出现问题，整个块的data 都不可读取。 如果是非块头，我们可以使用这种方法挽回更多的数据。

Use the following query to determine if the affected block is thesegment header :

可以使用如下SQL判断是否是块头block：

SQL>selectfile_id,block_id,blocks,extent_id from dba_extents where owner='<owner>'and segment_name='<table_name>' and segment_type='TABLE'   order by extent_id;

FILE_ID BLOCK_ID    BLOCKS EXTENT_ID

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

8    94854     20780         0 <- EXTENT_IDZERO is segment header

#### 方法三： Index method

If there are any indexes on the corrupt table then it is possible to get someinformation about what data was in the corrupt block from the index. Thisrequires selecting indexed columns from the table for rowids in the corruptblock. We already know the ROWID range covered by the corrupt block from theSELECT dbms_rowid.rowid_create ... statements above.

To extract the column data use one of the following forms of select statement:

If the columns requiredat NOT NULLable you can use a fast full scan:

SQL>SELECT/*+ INDEX_FFS(X <index_name>) */ <index_column1>,<index_column2> ...  FROM<tablename> X WHERE rowid >= '<low_rid>'          AND rowid <  '<hi_rid>' ;

If the columns required are NULLable thenyou cannot use an index fast full scan and must use a range scan. This requiresyou to know a minimum possible value for the leading index column to ensure youenable the index scan:

SQL>SELECT /*+ INDEX(X<index_name>) */ <index_column1>, <index_column2> ...  FROM <tablename> X  WHERE rowid >= '<low_rid>'        ANDrowid <  '<hi_rid>'  AND <index_column1> >=<min_col1_value>;

Using this technique for all indexes on the table may be able to retrieve some of thedata. See <View:DBA_IND_COLUMNS> for which columns make up each index.

ExtractingData from a Corrupt Table using ROWID Range Scans in Oracle8 and higher [ID61685.1]

#### 4.3.6.2 Clusters

Clusters can be defined as a way to store more than one tables physically together sincethe tables have some common columns.  Bystoring the related rows from multiple tables together, the access time can bereduced.

For example, if a cluster is formed with the EMP and DEPT tables, the cluster blocklooks like:

A query that runs across EMP and DEPT will have to access just one block to getboth tables’ rows.  The IO is less andthe access time is less too.

The detection and repair for a cluster block corruption is similar to that of atable block corruption but the only difference is that when there is acorruption in a cluster block, all the objects that make up the cluster has tobe fixed.

#### 4.3.6.3 indexes

Detection

·（1） DBVERIFY gives the corrupted blockinformation

·（3）Users running queries against theindex get ORA-1578 on the index

·（4）ORA-600 with first argument in the rangebetween 2000 and 8000

Repair

·（1） Drop and recreate the indexsegment

·（2）Restore from a valid backup and recover

索引上的corrupt block 是比较好处理的，因为可以对索引进行重建，所以不会造成data lose。

## 五. dbms_repair 包与坏块

Oracle从8i 开始提供了DBMS_REPAIR包，该包可以用来发现、标识并修改数据文件中的坏块。dbms_repair包没有进行授权，只有sys用户可以执行。

任何工具都不是万能的，使用这个包的同时会带来数据丢失、表和索引返回数据不一致，完整性约束破坏等其他问题。因此当出现错误时，应当首先从物理备份或逻辑备份恢复，使用dbms_repair只是在没有备份的情况下使用的一种手段，这种方式一般都会造成数据的丢失。

dbms_repair包的工作原理比较简单，是将检查到的坏块标注出来，使随后的dml操作跳过该块，同时，dbms_repair包还提供了用于保存索引中包含的标注为坏块中的键值，以及修复freelist和segment bitmap的过程。

DBMS_REPAIR Examples

Using the DBMS_REPAIR Package

yangtingkun的blog：

DBMS_REPAIR的使用

### 5.1 准备工作

create tablespace block datafile '/u01/block.dbf' size 5M;

create table DMM tablespace block as select * from all_tables;

commit;

CREATE INDEX indx_dmm on DMM(TABLE_NAME);

select count(*) from DMM;

COUNT(*)

----------

12896

### 5.2.创建管理表

PL/SQL procedure successfully completed

PL/SQL procedure successfully completed

### 5.3 检查坏块：dbms_repair.check_object

Set serveroutput on;

DECLARE

cc  NUMBER;

BEGIN

DBMS_REPAIR.check_object (schema_name => 'SYS', -- 注意此处是用户名

object_name     => 'DMM',

corrupt_count   => cc);

DBMS_OUTPUT.put_line ( TO_CHAR (cc));

END;

正常情况下输入为0。 如果有坏块，可以在创建的REPAIR_TABLE中查看块损坏信息：

/* Formatted on 2009-12-17 13:18:19 (QP5v5.115.810.9015) */

SELECT   object_name,

relative_file_id,

block_id,

marked_corrupt,

corrupt_description,

repair_description,

CHECK_TIMESTAMP

FROM   repair_table;

注意：在8i下，check_object只会检查坏块，MARKED_CORRUPT为false，故需要进行 定位坏块（fix_corrupt_blocks） ，修改MARKED_CORRUPT为true，同时更新CHECK_TIMESTAMP。

9i以后经过check_object，MARKED_CORRUPT的值已经标识为TRUE了。所以可以直接进行第四步了。

### 5.4 .定位坏块：dbms_repair.fix_corrupt_blocks

/* Formatted on 2009-12-17 13:29:01 (QP5v5.115.810.9015) */

DECLARE

cc   NUMBER;

BEGIN

DBMS_REPAIR.fix_corrupt_blocks (schema_name   => 'SYS',

object_name   => 'DMM',

fix_count     => cc);

DBMS_OUTPUT.put_line (a => TO_CHAR (cc));

END;

### 5.5 .跳过坏块

SQL> select count(*) from SYS.DMM;

ORA-01578: ORACLE 数据块损坏（文件号14，块号154）

ORA-01110: 数据文件 14: 'D: /BLOCK.DBF'

/* Formatted on 2009-12-17 13:30:17 (QP5v5.115.810.9015) */

exec dbms_repair.skip_corrupt_blocks(schema_name => 'SYS',object_name => 'DMM',flags => 1);

SQL> select count(*) from SYS.DMM;

COUNT(*)

----------

12850

### 5.6.处理index上的无效键值；dump_orphan_keys

/* Formatted on 2009-12-17 13:34:55(QP5 v5.115.810.9015) */

DECLARE

cc   NUMBER;

BEGIN

DBMS_REPAIR.dump_orphan_keys (schema_name         => 'SYS',

object_name         => 'INDX_DMM',

object_type         => 2,

repair_table_name   => 'REPAIR_TABLE',

orphan_table_name   => 'ORPHAN_TABLE',

key_count           => CC);

END;

SQL> SELECT * FROM ORPHAN_TABLE;

### 5.7重建freelist：rebuild_freelists

/* Formatted on 2009-12-17 13:37:57(QP5 v5.115.810.9015) */

exec dbms_repair.rebuild_freelists(schema_name => 'SYS',object_name => 'DMM');

6917 0

7760 0

9426 0

444 0

3228 0

16830 0
+关注
jimbuster

280

0

《SaaS模式云原生数据仓库应用场景实践》

《看见新力量：二》电子书