模拟坏块及rman恢复坏块测试（二）

损坏发生在只有归档，没有备份的数据库中该如何操作：
DBV只报告物理损坏，validate check logical database可以验证物理损坏和逻辑损坏，
执行VALIDATE命令后通过查询V$DATABASE_BLOCK_CORRUPTION视图可以获得坏块的详细信息，
块被修复后V$DATABASE_BLOCK_CORRUPTION中相应的记录会被删除。
该命令只能运行在归档模式下的数据库，如果要在非归档模式下执行该命令，必须使数据库在MOUNT模式。


执行下面的命令能验证归档日志的逻辑损坏和物理损坏：
RMAN > BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
执行下面的命令验证数据库物理损坏的块：
RMAN > BACKUP VALIDATE DATABASE|ARCHIVELOG ALL;
没有CHECK LOGICAL关键字的BACKUP命令只会验证物理损坏。 


VALIDATE DATAFILE 7 SECTION SIZE 1200M;
VALIDATE DATAFILE 1 BLOCK 10
VALIDATE DATABASE
VALIDATE BACKUPSET 22


backup validate check logical database;
select * from V$DATABASE_BLOCK_CORRUPTION;


RMAN> VALIDATE DATAFILE 7;


Starting validate at 21-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00007 name=+DATA/test1/datafile/erp2013.269.861528941
channel ORA_DISK_1: validation complete, elapsed time: 00:02:15
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
7    OK     0              67123        3927552         37722234  
  File Name: +DATA/test1/datafile/erp2013.269.861528941
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              2372378         
  Index      0              1466683         
  Other      0              21368           


Finished validate at 21-SEP-16


RMAN> 


RMAN> blockrecover datafile 7 block 3768525 clear; 


Starting recover at 21-SEP-16
using channel ORA_DISK_1
Finished recover at 21-SEP-16


SQL> select * from V$DATABASE_BLOCK_CORRUPTION;


     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         7    3768525          1           37722234 CORRUPT


SQL> 


SQL> insert into t_block values(4,'tcaaaa a');  
SQL> insert into t_block values(5,'ad阿@奥迪');  
SQL> insert into t_block values(6,'ABa$23省道');
SQL> commit;


SQL> select * from t_block;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 7, block # 3768525)
ORA-01110: data file 7: '+DATA/test1/datafile/erp2013.269.861528941'
no rows selected
SQL> 
SQL> select count(1) from t_block;


  COUNT(1)
----------
         6


根据文件号和块号查出损坏的是对象,7是文件号,3768525 是block号
SQL> select tablespace_name,segment_type,owner,segment_name 
  2  from dba_extents 
  3  where file_id=7 and 3768525 between block_id AND block_id + blocks -1;


TABLESPACE_NAME                SEGMENT_TYPE       OWNER
------------------------------ ------------------ ------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
ERP2013                        TABLE              SYSDEV
T_BLOCK



SQL> 
如果是索引就比较方便了重新创建即可
alter index indexname rebuild;


如果是表，使用10231事件忽略坏块，然后使用CTAS方式重建表最后rename table，别忘记rebuild index
alter session SET EVENTS '10231 trace name context forever,level 10';
create table T_BLOCK_new as select * from T_BLOCK;
rename T_BLOCK to T_BLOCK_bak;
rename T_BLOCK_new to T_BLOCK;
alter index indexname rebuild;
alter session SET EVENTS '10231 trace name context off';

或者

sqlplus / as sysdba

SQL> exec dbms_repair.skip_corrupt_blocks('xxxx','T_BLOCK'); 
PL/SQL procedure successfully completed.

sqlplus sysdev/xxxx

SQL> select * from T_BLOCK;


        ID NAME
---------- ----------
         4 tcaaaa a
         5 ad阿@奥迪
         6 ABa$23省道

SQL> select * from T_BLOCK_new;


        ID NAME
---------- ----------
         4 tcaaaa a
         5 ad阿@奥迪
         6 ABa$23省道


SQL> 
坏块里的数据丢失。


如果是LOB segment,先找出segment信息
select owner, segment_name, segment_type from dba_extents 
where file_id=7 and 3768525 between block_id AND block_id + blocks -1;
找到表名和LOB字段
select table_name, column_name from dba_lobs where segment_name = 'SYS_LOBxxxxxxxxx$$' and owner = 'xxx';
 
找到坏块的bad rowid,使用以下plsql脚本


create table bad_rows (row_id ROWID,oracle_error_code number);
set concat off
set serveroutput on


declare
  n number;
  error_code number;
  bad_rows number := 0;
  ora1578 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora1578, -1578);
begin
   for cursor_lob in (select rowid rid, &&lob_column from &&table_owner.&table_with_lob) loop
   begin
     n:=dbms_lob.instr(cursor_lob.&&lob_column,hextoraw('889911')) ;
   exception
    when ora1578 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,1578);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(cursor_lob.rid,error_code);
     commit;   
   end;
  end loop;
  dbms_output.put_line('Total Rows identified with errors in LOB column: '||bad_rows);
end;
/
Enter value for lob_column: WORD
Enter value for table_owner: HGHIS
Enter value for table_with_lob: EMR_CASE 


可以查询bad rowid信息
select * from bad_rows;


更新空LOB字段来避免ORA-1578,ORA-26040,如果是CLOB类型，将empty_blob()改为empty_clob()
set concat off
update &table_owner.&table_with_lob set &lob_column = empty_blob() where rowid in (select row_id from bad_rows);


将bad rowid lob块移到其他表空间,最后别忘记rebuild index
alter table &table_owner.&table_with_lob move LOB (&&lob_column) store as (tablespace &tablespace_name);