Oracle数据库中的坏块问题处理

原创

东方之子7366 2014-02-02 08:44:39 博主文章分类：oracle、mysql管理 ©著作权

©著作权归作者所有：来自51CTO博客作者东方之子7366的原创作品，请联系作者获取转载授权，否则将追究法律责任

Oracle的数据块有固定的格式和结构，分三层： Cache layer、Transaction layer和Data layer.
对数据块进行读写操作时，做一致性检查：
–Block type
–DBA
–Scn
–Header and tail
发现不一致，标记为坏块。

坏块有两种：物理坏块和逻辑坏块。

坏块产生的影响：数据字典表、回滚段表、临时段和用户数据表和索引。
应用报错：
–Ora-1578
–Ora-600 and trace file in bdump directory
第一个参数[2000]-[8000]
Range block layer
-------------------------------------------
Cache layer 2000 – 4000
Transaction layer 4000 – 6000
Data layer 6000 - 8000

坏块产生的原因：
Oracle调用标准C的系统函数，对数据块进行读写操作：
- Bad I/O, H/W, Firmware.
- Operating System I/O or caching problems.
- Memory or paging problems.
- Disk repair utilities.
- Part of a datafile being overwritten.
- Third part software incorrectly attempting to access oracle used heap.
- Oracle or operating system bug.

表中坏块的处理方法：
(1).收集相关信息：
Ora-1578 file# (RFN) block#
Ora-1110 file# (AFN) block#
ora-600 file# (AFN) block#
select file_name,tablespace_name,file_id “AFN”, relative_fno “RFN” from dba_data_files;
select file_name,tablespace_name,file_id, relative_fno “RFN” from dba_temp_files;
9i tempfiles AFN=file_id+value of db_files
(2).确定受影响的对象：
SELECT tablespace_name, segment_type, owner, segment_name, partition_name FROM dba_extents WHERE file_id = <AFN> and <BL> between block_id AND block_id + blocks - 1;
If on tempfile, no data return;
(3).根据对象类型，确定处理方法：
Objects of sys
rollback
Temporary segment
Index and index partition
Cluster |
Partition | ===>表中坏块的处理
Table |
(4).选择合适的方法抢救表中的数据：
Recover datafile
Recover block only (9i)
通过ROWID RANGE SCAN 保存数据
使用DBMS_REPAIR
使用EVENT

表中坏块的处理方法一：恢复数据文件
数据库为归档方式，有完整的物理备份
OFFLINE the affected data file
ALTER DATABASE DATAFILE 'name_file' OFFLINE;
保存有坏块的文件，RESTORE 备份。
if different from the old location
ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';
Recover the datafile
RECOVER DATAFILE 'name_of_file';
Online the file/s
ALTER DATABASE DATAFILE 'name_of_file' ONLINE;

表中坏块的处理方法二：block recover
要求
(1).数据库9.2
(2).catalog 和rman
(3).数据库为归档方式，有完整的物理备份
(4).使用RMAN的BLOCKRECOVER命令
Rman>run{blockrecover
datafile 3 block 4,5;}
可以强制使用某个SCN号之前的备份，恢复数据块。
Rman>run{blockrecover
datafile 3 block 4,5 restore until sequence 7402;}

表中坏块的处理方法三：ROWID RANGE SCAN
使用DBMS_ROWID 确定坏块的ROWID RANGE
LOW_RID INSIDE the corrupt block:
SELECT dbms_rowid.rowid_create(1,<OBJ_ID>,<RFN>,<BL>,0) from DUAL;
HI_RID AFTER the corrupt block:
dbms_rowid.rowid_create(1,<OBJ_ID>,<RFN>,<BL>+1,0) from DUAL;
建一个临时表
CREATE TABLE salvage_table AS SELECT * FROM corrupt_tab Where 1=2;
保存未损坏的数据
INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid < '<low_rid>';
INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid >= '<hi_rid>';
重建table,index,foreign constrain table.

表中坏块的处理方法四：add 10231 event
在session 或database级设10231 event,做全表扫描时，可以跳过坏块.
Session level:
ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER,LEVEL 10';
CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp;
database level:
event="10231 trace name context forever, level 10"

表中坏块的处理方法五：dbms_repair
标记有坏块的表，做全表扫描时，可以跳过坏块.
Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('<schema>','<tablename>');
保存表中数据
EXPORT the table.
CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp;

表中坏块的处理方法六：检查索引
检查表上的索引和primary key foreign key约束
SELECT owner,index_name, index_type FROM dba_indexes WHERE table_owner=‘xxxx' AND table_name='xxxx';
SELECT owner,constraint_name,constraint_type,table_name FROM dba_constraints WHERE owner='xxx' AND table_name='xxx' AND
constraint_type='P';
SELECT owner,constraint_name,constraint_type,table_name FROM dba_constraints WHERE r_owner='xxxx' AND r_constraint_name='<CONSTRAINT-NAME>';

如何预先发现坏块：
(1).Export utility
exp system/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g
does not detect disk corruptions above the high water mark
does not detect corruptions in indexes
does not detect all corruptions in the data dictionary
ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE
performs the block checks ,but does NOT mark blocks as corrupt.
It also checks that table and index entries match.
Any problems found are reported into the user session trace file in USER_DUMP_DEST.
可以定期对一些重要的表作检查.
(2).DBV检查数据文件
show parameter db_block_size
select BYTES/2048 from v$datafile where FILE#=5;
dbv file=/dev/rdsk/r1.dbf blocksize=2048 END=5120
DBV expects a filename extension. If on raw dev
ln -s /dev/rdsk/mydevice /tmp/mydevice.dbf
Now use DBV against /tmp/mydevice.dbf