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

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 =

 1<afn> and <bl> between block_id AND block_id + blocks - 1;   
 2If on tempfile, no data return;   
 3(3).根据对象类型，确定处理方法：   
 4Objects of sys   
 5rollback   
 6Temporary segment   
 7Index and index partition   
 8Cluster |   
 9Partition | ===&gt;表中坏块的处理   
10Table |   
11(4).选择合适的方法抢救表中的数据：   
12Recover datafile   
13Recover block only (9i)   
14通过ROWID RANGE SCAN 保存数据   
15使用DBMS_REPAIR   
16使用EVENT   
17  
18表中坏块的处理方法一：恢复数据文件   
19数据库为归档方式，有完整的物理备份   
20OFFLINE the affected data file   
21ALTER DATABASE DATAFILE 'name_file' OFFLINE;   
22保存有坏块的文件，RESTORE 备份。   
23if different from the old location   
24ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';   
25Recover the datafile   
26RECOVER DATAFILE 'name_of_file';   
27Online the file/s   
28ALTER DATABASE DATAFILE 'name_of_file' ONLINE;   
29  
30表中坏块的处理方法二：block recover   
31要求   
32(1).数据库9.2   
33(2).catalog 和rman   
34(3).数据库为归档方式，有完整的物理备份   
35(4).使用RMAN的BLOCKRECOVER命令   
36Rman&gt;run{blockrecover   
37datafile 3 block 4,5;}   
38可以强制使用某个SCN号之前的备份，恢复数据块。   
39Rman&gt;run{blockrecover   
40datafile 3 block 4,5 restore until sequence 7402;}   
41  
42表中坏块的处理方法三：ROWID RANGE SCAN   
43使用DBMS_ROWID 确定坏块的ROWID RANGE   
44LOW_RID INSIDE the corrupt block:   
45SELECT dbms_rowid.rowid_create(1,<obj_id>,<rfn>,<bl>,0) from DUAL;   
46HI_RID AFTER the corrupt block:   
47dbms_rowid.rowid_create(1,<obj_id>,<rfn>,<bl>+1,0) from DUAL;   
48建一个临时表   
49CREATE TABLE salvage_table AS SELECT * FROM corrupt_tab Where 1=2;   
50保存未损坏的数据   
51INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid &lt; '<low_rid>';   
52INSERT INTO salvage_table SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A WHERE rowid &gt;= '<hi_rid>';   
53重建table,index,foreign constrain table.   
54  
55表中坏块的处理方法四：add 10231 event   
56在session 或database级设10231 event,做全表扫描时，可以跳过坏块.   
57Session level:   
58ALTER SESSION SET EVENTS '10231 TRACE NAME CONTEXT FOREVER,LEVEL 10';   
59CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp;   
60database level:   
61event="10231 trace name context forever, level 10"   
62  
63表中坏块的处理方法五：dbms_repair   
64标记有坏块的表，做全表扫描时，可以跳过坏块.   
65Execute DBMS_REPAIR.SKIP_CORRUPT_BLOCKS('<schema>','<tablename>');   
66保存表中数据   
67EXPORT the table.   
68CREATE TABLE salvage_emp AS SELECT * FROM corrupt_emp;   
69  
70表中坏块的处理方法六：检查索引   
71检查表上的索引和primary key foreign key约束   
72SELECT owner,index_name, index_type FROM dba_indexes WHERE table_owner=‘xxxx' AND table_name='xxxx';   
73SELECT owner,constraint_name,constraint_type,table_name FROM dba_constraints WHERE owner='xxx' AND table_name='xxx' AND   
74constraint_type='P';   
75SELECT owner,constraint_name,constraint_type,table_name FROM dba_constraints WHERE r_owner='xxxx' AND r_constraint_name='<constraint-name>';   
76  
77如何预先发现坏块：   
78(1).Export utility   
79exp system/manager full=y log=exp_db_chk.log file=/dev/null volsize=100g   
80does not detect disk corruptions above the high water mark   
81does not detect corruptions in indexes   
82does not detect all corruptions in the data dictionary   
83ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE   
84performs the block checks ,but does NOT mark blocks as corrupt.   
85It also checks that table and index entries match.   
86Any problems found are reported into the user session trace file in USER_DUMP_DEST.   
87可以定期对一些重要的表作检查.   
88(2).DBV检查数据文件   
89show parameter db_block_size   
90select BYTES/2048 from v$datafile where FILE#=5;   
91dbv file=/dev/rdsk/r1.dbf blocksize=2048 END=5120   
92DBV expects a filename extension. If on raw dev   
93ln -s /dev/rdsk/mydevice /tmp/mydevice.dbf   
94Now use DBV against /tmp/mydevice.dbf</constraint-name></tablename></schema></hi_rid></owner.tablename></low_rid></owner.tablename></bl></rfn></obj_id></bl></rfn></obj_id></bl></afn>