本文将讲解数据库中块的类型,以及各种块出现损坏(模拟坏块)的处理流程。
数据库中基本对象就是段,段表示有真实大小,在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.在后续的文章中会进行介绍。