记一次Oracle坏块修复过程(2)

如果大于64K使用以下
BEGIN
 for i in 1..1000000 loop
 EXECUTE IMMEDIATE 'alter table users.s allocate extent (DATAFILE '||'''+DATA/dbrac/datafile/tbs_11.11.435678937'''||'SIZE 64K) ';
 end loop;
 end ;
 /

使用如下SQL查询坏块是否已属于某个对象：
select segment_name, segment_type, owner
        from dba_extents
      where file_id = <Absolute file number>
        and <corrupt block number> between block_id
            and block_id + blocks  -1 ;

例如：
select segment_name, segment_type, owner
        from dba_extents
      where file_id = 11
        and 184959440 between block_id
            and block_id + blocks  -1 ;
           
 3)插入数据初始化坏块
Begin
 FOR i IN 1..1000000000 loop
    for j IN 1..100000 loop
      Insert into users.s VALUES(i,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
    end loop;
    commit;
 END LOOP;
 END;

7、验证是否存在坏块
Rman> Backup validate check logical datafile <fileno> ;
或者validate datafile <fileno> block <blockno reported corrupt>, <blockno reported corrupt>  ;
 Select * from v$database_block_corruption ;
例如：
validate datafile 11  block 184959440, 184961480,184961481 ;
 Select * from v$database_block_corruption ;

8、当第7步确认坏块已消除，即可删除测试表
DROP TABLE users.s purge;

9、切换多次日志及checkpoint
 Alter system switch logfile ;
 Alter system checkpoint ;

10、删除触发器
DROP trigger CORRUPT_TRIGGER ;
 DROP trigger corrupt_trigger1 ;
 DROP trigger corrupt_trigger2 ;