--检查数据文件是否正常dbv file=F:\oracle\product\10.2.0\oradata\movo\BLOCK.DBF blocksize=8192--rman验证validate datafile 1; --or validate database; 可以并行--查看坏块select * from v$database_block_corruption;--查看坏块对象select tablespace_name,segment_type,owner,segment_name from dba_extents where file_id=4 and 35 between block_id and block_id+blocks-1;--or 具体信息,检查哪个对象set pagesize 2000set linesize 280SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file# , greatest(e.block_id, c.block#) corr_start_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) - greatest(e.block_id, c.block#) + 1 blocks_corrupted , corruption_type description FROM dba_extents e, v$database_block_corruption c WHERE e.file_id = c.file# AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block#UNIONSELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block# , 1 blocks_corrupted , corruption_type||' Segment Header' description FROM dba_segments s, v$database_block_corruption c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1UNIONSELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest(f.block_id, c.block#) corr_start_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) - greatest(f.block_id, c.block#) + 1 blocks_corrupted , 'Free Block' description FROM dba_free_space f, v$database_block_corruption c WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >= c.block#order by file#, corr_start_block#;
坏块处理
--可通过rman 备份 修复坏块，或者填充为空块blockrecover datafile 5 block 19;--跳过坏块BEGIN DBMS_REPAIR.SKIP_CORRUPT_BLOCKS(SCHEMA_NAME => 'HR', OBJECT_NAME => 'EMP2', OBJECT_TYPE => dbms_repair.table_object, FLAGS => dbms_repair.skip_flag);END;/--取消跳过坏块execute dbms_repair.skip_corrupt_block(username,tablename,flags=>dbms_repair.noskip_flag);
rowid扫描方法
--定位坏块select dmbs_rowid.rowid_create(1,<data_object_id>,<RFN>,<BL>,0) low_rid from dual;select dmbs_rowid.rowid_create(1,<data_object_id>,<RFN>,<BL>+1,0) low_rid from dual;--ctscreate table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid < '<low_rid>';create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid >= '<high_rid>';--检查坏块是否处于表段头,如果extent_id 等于0，表示段头select file_id,block_id,blocks,extent_id from dba_extents where owner='' and segment_name='' and segment_type='TABLE' order by extent_id;--非空，从索引抢救数据 Fast Full Scan 访问方式select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid';--有空值，从索引抢救数据 Range Scan 访问方式select /*+ index_ffs (x <index_name>) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid' and index_col1 >= <min_col1_value>;;--对象所占用的块 select distinct dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from BOOTSTRAP$;
UNDO坏块
--跳过alter system set "_corrupted_rollback_segments"=(r1,r2,r3) scope=spfile;--设置offlinealter system set "_offline_rollback_segments"=() scope=spfile;
LOB坏块
create table corrupt_lobs (corrupt_rowid rowid, err_num number);--分析坏块declare error_1578 exception; error_1555 exception; error_22922 exception; pragma exception_init(error_1578,-1578); pragma exception_init(error_1555,-1555); pragma exception_init(error_22922,-22922); n number;begin for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop begin n:=dbms_lob.instr(cursor_lob.&&lob_column, hextoraw ('889911')); exception when error_1578 then insert into corrupt_lobs values (cursor_lob.r, 1578); commit; when error_1555 then insert into corrupt_lobs values (cursor_lob.r, 1555); commit; when error_22922 then insert into corrupt_lobs values (cursor_lob.r, 22922); commit; end; end loop;end;/--查看损坏的lob信息select * from corrupt_lobs;--清空损坏的lob行update EMPset EMP_XML = empty_blob()where rowid in (select corrupted_rowidfrom corrupt_lobs);commit;--导出expdp scott/tiger directory=data_pump_dir dumpfile=test.dmp logfile=test.log tables=EMP query=\"where rowid not in \(\'AAEWBsAAGAAACewAAC\', \'AAEWBsAAGAAACewAAF\', \'AAEWBsAAGAAACewAAG\'\)\"
