块管理

查看坏块

--检查数据文件是否正常
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 2000
set linesize 280
SELECT 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#
UNION
SELECT 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 - 1
UNION
SELECT 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,,,,0) low_rid from dual;
select dmbs_rowid.rowid_create(1,,,+1,0) low_rid from dual;
--cts
create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid < 
'';
create table tt_temp as select /*+ ROWID(A)*/ * from owner.tablename a where rowid >= '';
--检查坏块是否处于表段头,如果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_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid';
--有空值，从索引抢救数据 Range Scan 访问方式
select /*+ index_ffs (x ) */ index_col1,index_col2 from tablename x where rowid >= 'low_rid' and row <'hi_rid' and index_col1 >= ;;
--对象所占用的块
 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;
--设置offline
alter system set "_offline_rollback_segments"=() scope=spfile;

详细信息查询

--查看表空间datafile信息
select status, name, file# from v$datafile where ts# in (Select ts# from v$tablespace where name='UNDOTBS1')
--加入隐含参数
set long 9999
select '*._corrupted_rollback_segments=''' || replace(wm_concat(segment_name), ',', ''',''') || '''' from DBA_ROLLBACK_SEGS where tablespace_name = 'UNDOTBS1';
--查看undo 段信息
select segment_name,status from dba_rollback_segs where tablespace_name = 'UNDOTBS1' and status != 'OFFLINE';
--查看事务
SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
KTUXESTA Status,
KTUXECFL Flags
FROM x$ktuxe
WHERE ktuxesta!='INACTIVE';
col GLOBAL_TRAN_ID for a30
SELECT LOCAL_TRAN_ID,GLOBAL_TRAN_ID,STATE,MIXED,COMMIT# FROM DBA_2PC_PENDING;
SELECT LOCAL_TRAN_ID,IN_OUT,DATABASE,INTERFACE FROM dba_2pc_neighbors;
set serveroutput on
alter system disable distributed recovery;
begin
    for rec in (SELECT KTUXEUSN ||'.'|| KTUXESLT ||'.'|| KTUXESQN as local_trx_id /* Transaction ID */
                  FROM x$ktuxe
                 WHERE ktuxesta!='INACTIVE'
                )
    loop
        set transaction use rollback segment SYSTEM;
        delete from sys.pending_trans$ where local_tran_id = rec.local_trx_id;
        delete from sys.pending_sessions$ where local_tran_id = rec.local_trx_id;
        delete from sys.pending_sub_sessions$ where local_tran_id = rec.local_trx_id;
        --commit;
        insert into pending_trans$ (
        LOCAL_TRAN_ID, GLOBAL_TRAN_FMT, GLOBAL_ORACLE_ID, STATE, STATUS, SESSION_VECTOR,RECO_VECTOR, TYPE#, FAIL_TIME,RECO_TIME)
        values( rec.local_trx_id, 306206, /* */
        'XXXXXXX.12345.1.2.3', 'prepared','P', hextoraw( '00000001' ), hextoraw( '00000000' ),0, sysdate, sysdate );
        insert into pending_sessions$
        values( rec.local_trx_id, 1, hextoraw('05004F003A1500000104'), 'C', 0, 30258592, '', 146);
        commit;
        EXECUTE IMMEDIATE 'commit force '''|| rec.local_trx_id ||'''';
        DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY(rec.local_trx_id);
        commit;
    end loop;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
end;
/
select segment_name,status from dba_rollback_segs where tablespace_name = 'UNDOTB2' and status != 'OFFLINE';

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 EMP
set EMP_XML = empty_blob()
where rowid in (select corrupted_rowid
from 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\'\)\"

参考

• 坏块参考 mos:1526911.1