oracle 存储更换造成大量数据库逻辑坏快操作流程.

1、屏蔽坏快的事务-bbed
2、屏蔽smon进程的事务恢复操作  10511/10512/10513 event
   alter system set events '10511 trace name context forever, level 1';
   alter system set events '10512 trace name context forever, level 1';
   alter system set events '10513 trace name context forever, level 1';
   
3、recover database allow xx corruption --10g +


ora-00600  400~600 和undo有关系



--问题1：
ORA-01172: recovery of thread 2 stuck at block 102777 of file 1
ORA-01151: use media recovery to recover block, restore backup if needed
   用recover database allow xx corruption 解决
   
--问题2：
   alert很多数据库逻辑报错，不一会数据库abort，通过以下操作延长数据库宕机时间

--坏快太多，又有业务跑场景
--默认smon报错100次数据库就会crash
alter system set "_smon_internal_errlimit" =10000 scope=both;
--让全表扫描的时候跳过坏快
alter system set events '10231 trace name context forever,level 10';
 
 --让索引扫描的时候跳过坏快
alter system set events '10233 trace name context forever,level 10';
--为了业务运行，当数据文件 写入报错不crash数据库，影响没那么大：
alter system set "_datafile_write_errors_crash_instance" =false;

--问题3：处理坏快

--情况1：如果业务能停就用rman命令全库检查
--情况2：业务不能停，库大，可以用dbv 检查单个文件，根据dbv 提示rdba 去处理：
  ----1、定位file#,block#
  select dbms_utility.data_block_address_file(&dba) file#, dbms_utility.data_block_address_block(&rdba) block# from dual;
  ----2、根据file#,block# 去dump block
  oradebug setmypid
  alter system dump datafile 4 block 15783;
  alter system dump datafile 4 block 15785;
  ......
  oradebug tracefile_name
  ----3、去dump 的trc里面查找对象号：
  cat xxx.trc | grep "seg/obj"   --这个出来是16进制，需要转换成10进程
  ----4、根据对象号去查名字
  select owner,object_name,object_type,object_id from dba_objects where object_id in ();
  select a.owner,a.table_name,b.object_name as index_name ,b.object_type  from 	dba_indexes a,dba_objects b 
   where a.index_name=b.object_name and b.object_id in ();
   
  
  --批量check 检查dump block：
  
  select  'alter system dump datafile ' || 	file# || ' block ' || block# || ' ;'  from v$database_block_corruption;
  
  


---导出大概率会遇到ORA-8103:8103（是表，非索引） ，需要CTAS重建

--常规处理办法：
 1、生成trc定位block(表很大会很慢)--有条件的话直接odu导出最高效
 alter session set max_dump_file_size=unlimited;
 alter session set db_file_multiblock_read_count=1;
 alter session set events 'immediate trace name trace_buffer_on level 1048576';
 alter session set events '10200 trace name context forever, level 1';
 alter session set events '10236 trace name context forever, level 1';
 alter session set events '8103 trace name errorstack level 3';
 alter session set tracefile_identifier='ORA8103';
 ----->>>> run the sql statement that causes the ORA-08103
 select /*+ full(t1)*/  count(1)  from scott.t1 ;
 alter session set events 'immediate trace name trace_buffer_off';
 oradebug setmypid
 oradebug tracefile_name
 
 2、用bbed 把这个block设置成坏快，或者用空块覆盖
 ------或者-------
 或者用下面的mos plsql存储导出(前提是必须有主键或者普通索引)--（没odu快)：
 
 
 --SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE (Doc ID 1527738.1)
 
--REM Create a new table based on the table that is producing errors with no rows:

create table <new table name>
as
select *
from   <original table name>
where  1=2;

--REM Create the table to keep track of ROWIDs pointing to affected rows:

create table bad_rows (row_id rowid, oracle_error_code number);

set serveroutput on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

  CURSOR c1 IS select /*+ index_ffs(tab1 <index name>) parallel(tab1) */ rowid
  from <original table name> tab1
  where <indexed column> is NOT NULL
  order by rowid;

  r RowIDTab;
  rows NATURAL := 20000;
  bad_rows number := 0 ;
  errors number;
  error_code number;
  myrowid rowid;
BEGIN
  OPEN c1;
  LOOP
   FETCH c1 BULK COLLECT INTO r LIMIT rows;
   EXIT WHEN r.count=0;
   BEGIN
    FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
     insert into <new table name>
     select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
     from <original table name> A where rowid = r(i);
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
      error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
      if error_code in (1410, 8103, 1578) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into bad_rows values(myrowid, error_code);
      else
       raise;
      end if;
     END LOOP;
    END;
   END;
   commit;
  END LOOP;
  commit;
  CLOSE c1;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/