分类目录归档：数据库

“在oracle出现ORA-8103/ORA-1578/ORA-376″等情况下抢救数据的争论没有停止过,很多人想到的是使用bbed,dul等工具来抢救,其实在很多时候我们使用pl/sql也可以完美的抢救数据.在这里我们通过模拟ORA-8103错误，然后使用plsql来找回数据.这中处理方法相对于bbed风险小,但是缺点是如果数据量大处理时间可能比较长,可能比dul有的一比,但是dul的工具不是任何人都有的.所以整体来说,在大部分情况下,这种方法处理某个数据块错误,抢救某个对象数据,还是很好的方法.
1.有非空列index情况

--创建测试表
SQL> create table xifenfei 
  2  as
  3  select * from dba_objects;

Table created.

--修改某个项为非空值
SQL> alter table xifenfei modify object_id not null;

Table altered.

--创建一个唯一index
SQL> create unique index ind_xifenfei  on xifenfei(object_id);

Index created.

--表总记录
SQL> select count(*) from xifenfei;

  COUNT(*)
----------
     50088

--extent的分布情况
SQL> set pages 100
SQL>  select file_id,block_id,block_id+blocks-1
  2    from dba_extents
  3   where segment_name ='XIFENFEI' AND owner='CHF';

   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         9       1545              1552
         9       1553              1560
         9       1561              1568
         9       1569              1576
         9       1577              1584
         9       1585              1592
         9       1593              1600
         9       1601              1608
         9       1609              1616
         9       1617              1624
         9       1625              1632
         9       1633              1640
         9       1641              1648
         9       1649              1656
         9       1657              1664
         9       1665              1672
         9       1673              1800
         9       1801              1928
         9       1929              2056
         9       2057              2184
         9       2185              2312

21 rows selected.

--2200数据块包含记录
SQL> select   count(*)
  2  from chf.xifenfei where dbms_rowid.rowid_block_number(rowid)=2200;

  COUNT(*)
----------
        69

--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--破坏数据块
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users03.dbf  bs=8192  count=1 seek=2200 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000151554 seconds, 54.1 MB/s

--启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             104860124 bytes
Database Buffers          205520896 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

--查询结果
SQL>  select /*+ full(xifenfei) */ count(*) from chf.xifenfei;
 select /*+ full(xifenfei) */ count(*) from chf.xifenfei
                                                *
ERROR at line 1:
ORA-08103: object no longer exists

SQL> create table chf.xifenfei_new
  2  as
  3  select * from chf.xifenfei;
select * from chf.xifenfei
                  *
ERROR at line 3:
ORA-08103: object no longer exists

--创建备份表

SQL> create table chf.xifenfei_new
  2  as
  3  select * from chf.xifenfei where 1=0;

Table created.

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

Table created.

--执行plsql脚本
DECLARE
 TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;

 CURSOR c1 IS  select /*+ index(xifenfei ind_xifenfei) */ rowid
 from chf.xifenfei
 where object_id is NOT NULL;

 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 chf.xifenfei_new      
     select /*+ ROWID(A) */ *
     from chf.xifenfei 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) then
         myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
         bad_rows := bad_rows + 1;
         insert into chf.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;
/


--查询错误记录
SQL> select count(*) from chf.bad_rows ;                   

  COUNT(*)
----------
        69

SQL> select * from chf.bad_rows where rownum<10;

ROW_ID             ORACLE_ERROR_CODE
------------------ -----------------
AAAMugAAJAAAAiYAAA              8103
AAAMugAAJAAAAiYAAB              8103
AAAMugAAJAAAAiYAAC              8103
AAAMugAAJAAAAiYAAD              8103
AAAMugAAJAAAAiYAAE              8103
AAAMugAAJAAAAiYAAF              8103
AAAMugAAJAAAAiYAAG              8103
AAAMugAAJAAAAiYAAH              8103
AAAMugAAJAAAAiYAAI              8103

9 rows selected.

--查询备份表记录
SQL> select count(*) from chf.xifenfei_new;

  COUNT(*)
----------
     50019

50088-50019=69和被破坏块中记录一致,证明所有好块中记录全部被找回来

2.无非空列index情况

--创建表
SQL> CONN CHF/XIFENFEI
Connected.
SQL> create table t_xifenfei
  2  as
  3  select * from dba_objects;

Table created.

--表中记录总数
SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     50086

--extent分布
SQL> SET PAGES 100
SQL>  select file_id,block_id,block_id+blocks-1
  2    from dba_extents
  3   where segment_name ='T_XIFENFEI' AND owner='CHF';

   FILE_ID   BLOCK_ID BLOCK_ID+BLOCKS-1
---------- ---------- -----------------
         9          9                16
         9         17                24
         9         25                32
         9         33                40
         9         41                48
         9         49                56
         9         57                64
         9         65                72
         9         73                80
         9         81                88
         9         89                96
         9         97               104
         9        105               112
         9        113               120
         9        121               128
         9        129               136
         9        137               264
         9        265               392
         9        393               520
         9        521               648
         9        649               776

21 rows selected.

--700数据块中记录数
SQL> select   count(*)
  2  from chf.t_xifenfei where dbms_rowid.rowid_block_number(rowid)=700;

  COUNT(*)
----------
        73

--关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

--破坏block 700的数据块
[oracle@xifenfei ~]$ dd if=/dev/zero of=/u01/oracle/oradata/XFF/users03.dbf  bs=8192  count=1 seek=700 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000156576 seconds, 52.3 MB/s

--启动数据库
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size             104860124 bytes
Database Buffers          205520896 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

--查询报错
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-08103: object no longer exists

--创建备份表
SQL> CREATE TABLE T_XIFENFEI_NEW
  2  AS
  3  SELECT * FROM T_XIFENFEI WHERE 1=0;

--找回记录
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 = 'CHF'  
 and object_name = 'T_XIFENFEI' 
-- 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 = 'CHF'              
             and segment_name = 'T_XIFENFEI'  
-- 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 CHF.T_XIFENFEI_NEW      
      select /*+ ROWID(A) */ *        
      from CHF.T_XIFENFEI A  
      where rowid = rid;          
      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; 
/ 

--找回记录数
SQL> SELECT COUNT(*) FROM CHF.T_XIFENFEI_NEW;

  COUNT(*)
----------
     50013

50086-50013=73  证明非坏块中的数据都被完全寻找回来

参考：
ORA-8103 Troubleshooting, Diagnostic and Solution [ID 268302.1]
Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS [ID 422547.1]