oracle 通过plsql 存储过程抢救数据

--1、有非空列index情况
--创建测试表
create table scott.t1  as select * from dba_objects;

--修改某个项为非空值
alter table scott.t1  modify object_id not null;

--创建一个唯一index
create unique index idx_t1  on scott.t1(object_id);
 
--表总记录
select count(*) from scott.t1;
  COUNT(*)
----------
     87337
	 
--extent的分布情况
set pages 100
select file_id,block_id,block_id+blocks-1 from dba_extents where segment_name ='T1' AND owner='SCOTT';


   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         4       5784              5791
        .....................................
         4      32512             32639

25 rows selected.

-- 5791 数据块 包含记录
 select   count(*)   from SCOTT.t1 where dbms_rowid.rowid_block_number(rowid)=5791;
   COUNT(*)
----------
        78
		
--关闭数据库
shutdown immediate

--破坏数据库
dd if=/dev/zero of=/oradata/orcl/users01.dbf  bs=8192  count=1 seek=5791 conv=notrunc

--启动数据库
startup

--查询结果
select /*+ full(t) */ count(*) from scott.t1 t;
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 5791)
ORA-01110: data file 4: '/oradata/orcl/users01.dbf'


--创建备份表
create table  t1_new  as select *from t1 where 1=2;

--创建坏块相关rowid记录表
create table scott.bad_rows (row_id rowid, oracle_error_code number);

--执行plsql找回数据
SET SERVEROUTPUT ON 
DECLARE
 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
 CURSOR c1 IS  select /*+index(t idx_t1)*/ rowid from scott.t1 t where object_id is not null; --修改地方1
 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 scott.t1_new select /*+ ROWID(A) */ * from scott.t1 A where rowid = r(i); --修改地方2
   EXCEPTION
   when OTHERS then
    BEGIN
     errors := SQL%BULK_EXCEPTIONS.COUNT;
     FOR err1 IN 1..errors LOOP
         error_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;
         myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
         bad_rows := bad_rows + 1;
         insert into scott.bad_rows values(myrowid, error_code);  --修改地方3
     END LOOP;
     END;
   END;
  commit;
 END LOOP;
 commit;
 CLOSE c1;
 dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/



Total Bad Rows: 78

PL/SQL procedure successfully completed.


select count(*) from scott.bad_rows;
  COUNT(*)
----------
        78



--查询原表记录
select count(*) from scott.t1;
 
  COUNT(*)
----------
     87337
--查看新表记录
select count(*) from scott.t1_new;

  COUNT(*)
----------
     87259 

select  87337-87259 from dual;
87337-87259
-----------
         78    --和被破坏块中记录一致,证明所有好块中记录全部被找回来

		

--2、无非空列index情况


--创建表
create table scott.t2  as  select * from dba_objects;
select count(1) from  scott.t2;
   COUNT(1)
  ---------
     87339
--extent的分布情况
set pages 100
select file_id,block_id,block_id+blocks-1 from dba_extents where segment_name ='T2' AND owner='SCOTT';
   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         4       6656              6663
    *****************************************
         4      34944             35071

25 rows selected.


--6663 数据块中记录数
select   count(*) from scott.t2 where dbms_rowid.rowid_block_number(rowid)=6663;
  COUNT(*)
----------
        78
		

--关闭数据库
shutdown immediate

--破坏数据库
dd if=/dev/zero of=/oradata/orcl/users01.dbf  bs=8192  count=1 seek=6663 conv=notrunc

--启动数据库
startup

--查询结果
select /*+ full(t) */ count(*) from scott.t2 t;
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 6663)
ORA-01110: data file 4: '/oradata/orcl/users01.dbf'


--创建备份表
CREATE TABLE scott.t2_new  AS SELECT * FROM scott.t2 WHERE 1=0;


--找回记录
----这种方法比通过索引找rows要慢很多
set serveroutput on
set concat off        
DECLARE 
 nrows number; 
 rid rowid; 
 dobj number; 
 ROWSPERBLOCK number; 
BEGIN
 ROWSPERBLOCK:=1000;  --估算最大的一个块中记录条数
 nrows:=0; 
 select data_object_id  into dobj  from dba_objects  where owner = 'SCOTT'  and object_name = 'T2' --修改1
-- and subobject_name = '<table partition>'  Add this condition if table is partitioned  
 ;
 for i in (select relative_fno, block_id, block_id+blocks-1 totblocks            
           from dba_extents    where owner = 'SCOTT'   and segment_name = 'T2'    --修改2
-- and partition_name = '<table partition>' Add this condition if table is partitioned 
-- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376 (A) 
   order by extent_id)  
 loop   
   for br in i.block_id..i.totblocks loop  
    for j in 1..ROWSPERBLOCK loop 
    begin
      rid := dbms_rowid.ROWID_CREATE(1,dobj,i.relative_fno, br , j-1); 
      insert into scott.t2_new  select /*+ ROWID(A) */ *    from scott.t2 A   where rowid = rid;  --修改3       
      if sql%rowcount = 1 then nrows:=nrows+1; end if; 
      if (mod(nrows,10000)=0) then commit; end if; 
    exception when others then null; 
    end; 
    end loop; 
  end loop; 
 end loop; 
 COMMIT;
 dbms_output.put_line('Total rows: '||to_char(nrows)); 
END; 
/ 
 
--找回记录数
SELECT COUNT(*) FROM scott.t2_new;
  COUNT(*)
----------
     87261

----恢复完成。。。。。