mysql有坏块_oracle_数据库坏块类型以及坏块处理

本文将讲解数据库中块的类型，以及各种块出现损坏(模拟坏块)的处理流程。

数据库中基本对象就是段，段表示有真实大小，在oracle中段的类型为

dba_segments.segment_type

SEGMENT_TYPE VARCHAR2(18)  Type of

segment:

NESTED TABLE

---

嵌套表段

TABLE  ---  堆表段

TABLE PARTITION

---  表分区段

CLUSTER

---  簇表段

LOBINDEX

---  大对象索引段

INDEX  ---

索引段

INDEX PARTITION

---  索引分区段

LOBSEGMENT

---  大对象段

TABLE SUBPARTITION

---  表子分区段

INDEX SUBPARTITION ---

索引子分区段

LOB PARTITION

---

大对象分区段

LOB SUBPARTITION

---

大对象子分区段

ROLLBACK

---  回滚段

TYPE2 UNDO

---

重做段

DEFERRED ROLLBACK

---  延迟回滚段

TEMPORARY

---

临时段

CACHE  ---

缓存段

SPACE HEADER

---

空间头段

UNDEFINED

---

未定义

只要是上面列出的类型段，分配其中的块都有可能出现损坏。

引起损坏块的原因包括：

IO硬件/固件的损坏

操作系统问题

数据库问题

对非FORCELOGGING库的段NOLOGGING或UNRECOVERABLE操作进行恢复

当数据库报错坏块后，根源往往不容易发现，需要做的是尽快恢复生产，步骤如下

决定损坏受影响的区和判断损坏是持久还是临时的

替换或离开任何错误或怀疑的硬件环境

判断哪些数据库对象受影响

选择合适的数据库恢复或数据保留策略

由于NOLOGGING或UNRECOVERABLE的损坏

如果对象使用NOLOGGING或UNRECOVERABLE选择，对象所在数据文件进行恢复的话，

涉及对象会报坏块ORA-1578错误，在8i之后ORA-26040错误会产生。

Recovery操作无法恢复NOLOGGING的数据

对象块中没有数据会无法挽回

块损坏类型包括物理损坏和逻辑损坏

物理损坏，块损坏实例:

Bad header - the

beginning of the block (cache header) is corrupt with invalid

values

The block is

Fractured/Incomplete - header and footer of the block do not

match

The block checksum is

invalid

The block is

misplaced

Zeroed out blocks Note

1545366.1

逻辑损坏，块损坏实例：

This is when block contains a valid checksum and the structure

below the beginning of the block is corrupt (Block content is

corrupt). It may cause different ORA-600 errors.

The detailed corruption description for Logical Corruptions

are not normally printed in the alert.log. DBVerify will report

what is logically corrupted in the block.

Corruption Examples are:

row locked by

non-existent transaction - ORA-600 [4512], etc

the amount of space used

is not equal to block size

avsp bad

etc.

A corrupt block is a block that has been changed so that it

differs from what Oracle Database expects to find. This note covers

three data block corruption types:

In a physical block

corruption, which is also called a media corruption, the database

does not recognize the block at all: the checksum is invalid, the

block contains all zeros, the header and footer of the block do not

match or one of the key data block data structure is incorrect such

as the data block address (DBA).

In a logical block

corruption, the contents of the block are physically sound and pass

the physical block checks; however the block can be logically

inconsistent. Examples of logical corruption include corruption of

a row piece or index entry.

Block corruptions caused

by stray writes, lost writes or misdirected writes can also cause

havoc to your database availability. The data block may be

physically or logically correct but in this case the block’s

content is older or stale or in the wrong location.

Block corruptions can also be divided into interblock

corruption and intrablock corruption:

In intrablock

corruption, the corruption occurs in the block itself and can be

either a physical or a logical corruption.

In an interblock

corruption, the corruption occurs between blocks and can only be a

logical corruption.

图例显示分类

write lost介绍

《oracle_数据丢失写故障及处理 &

DB_LOST_WRITE_PROTECT》

table/index data

inconsistent

《oracle_数据库坏块之TABLE/INDEX不一致模拟》

坏块处理流程：首先，坏块后在无备份情况下就没有办法保障数据的完整性，dbv和rman检查坏块。

1.检查是否有硬件上的IO问题，是否是硬件导致

2.尽可能多的记录坏块信息，AFN,RFN,FILE,Block#,object,object_type

2.在有备份的情况下采用blockrecover,datafile,database级别的恢复，BR但是只针对普通表块，无法对系统块操作；

3.没有备份的情况,只能是补救数据，  (只针对普通段)

通过dbms_repair..skip_corrpt_blocks(owner,tab) 跳过表上坏块备份数据

通过10231事件设置跳过表上坏块备份数据

索引数据可以进行索引重建

4.rowid方式备份数据

SELECT dbms_rowid.rowid_created(1,OID,RFN,BL,0) low_rowid FROM

dual;

SELECT dbms_rowid.rowid_created(1,OID,RFN,BL 1,0) high_rowid

FROM dual;

INSERT INTO TABLE  SELECT * FROM table WHERE

rowid high_rowid

5.索引中找回丢失数据

如果是NOT NULL字段可以

SELECT /index_ffs()/ FROM table WHERE rowid high_rowid;

NULL字段

SELECT /index()/ FROM table WHERE rowid high_rowid;

6.logminer技术找回数据

7.DUL工具

上面是常规处理流程，也存在非常规的处理bbed.在后续的文章中会进行介绍。