数据文件被误删，解决ORA-376,ORA-1578,ORA-8103问题

如下为metalink 关于上述错误的解决方法：此错误主要是由于数据文件被误offline drop掉。出现ora-376

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.4
Information in this document applies to any platform.

Goal

This article describe a procedure that can be used to skip corrupted rows from a table when event 10231, dbms_repair or skipping the rows through an index cannot be used.

This method builds the rowid's for a table based on the information from dba_extents.

Example of errors where this procedure can be used:

ORA-8103
ORA-1578
ORA-376 (The procedure can be used to recover the rows from a table which contains rows in a OFFLINE datafile).

Solution

1. Determine the average rows per block by using any method. dba_tables.avg_row_len might be a starting point.

You may add an overhead of 100% or more to that average to ensure that no rows will be missed from the blocks. i.e. if the calculated value is 80 rows per block, then 160 may be appropriate. This value will be used in variable ROWSPERBLOCK.

2. Modify and run the next script

connect / as sysdba
set serveroutput onDECLAREnrows number;
rid rowid;
dobj number;
ROWSPERBLOCK number;BEGIN

ROWSPERBLOCK:=<VALUE CALCULATED IN STEP 1>;

nrows:=0;select data_object_id
into dobj
from dba_objects
where owner = 'PERF'
and object_name = 'PROFILEDATA';for i in (select relative_fno, block_id, block_id+blocks-1 totblocks
from dba_extents
where owner= '<TABLE OWNER>'
and segment_name = '<SOURCE TABLE NAME>'
-- and file_id != <OFFLINED DATAFILE> This condition is only used if a datafile needs to be skipped due to ORA-376
) loopfor 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 <OWNER.NEW_TABLE> (<columns here>)
select /*+ ROWID(A) */ <columns here>
from <OWNER>.<SOURCE TABLE NAME> 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;
/下面就上诉文档做出现ORA-376问题的解决示例，以此来说明此脚本的使用方法。假设select table test时，抱 ORA-376错误，发现file_id=4的文件丢失，被offline drop掉了。
另表test onwer是jeffer,且此表为普通表（非分区表）。1）估算每行块数量（ROWSPERBLOCK）
如表有统计分析过，则可以查询dba_tables表，得到dba_tables.num_rows/dba_tables.blocks.
最后取预估值rowsperblock = 2 * dba_tables.num_rows/dba_tables.blocks.当然只是估算值，此值
取值原则是尽可能大，来确保没有行丢失。后面会根据理解，来检测是否有数据丢失。2）创建抽取目标临时表T_TEST
CREATE TABLE JEFFER.T_TEST AS SELECT * FROM JEFFER.TEST WHERE 1=2;3) 替换如下值，运行脚本抽取数据。
<VALUE CALCULATED IN STEP 1> 替换为 （2 * dba_tables.num_rows/dba_tables.blocks）--假设为500
PERF 替换为 JEFFER
PROFILEDATA 替换为 TEST
<TABLE OWNER> 替换为 JEFFER
<SOURCE TABLE NAME> 替换为 TEST
<OFFLINED DATAFILE> 替换为 4
<OWNER.NEW_TABLE> (<columns here>) 替换为 JEFFER.T_TEST
<columns here> 替换为 *
<OWNER>.<SOURCE TABLE NAME> 替换为 JEFFER.TEST
替换后脚本如下：
set serveroutput on; DECLARE
nrows number;
rid   rowid;
dobj number;
ROWSPERBLOCK number;BEGIN
ROWSPERBLOCK:=500;
nrows:=0;select data_object_id into dobj
from dba_objects
where owner = 'JEFFER' and object_name = 'TEST';for i in (select relative_fno, block_id, block_id + blocks-1 totblocks          
                 from dba_extents
                 where owner = 'JEFFER' and segment_name = 'TEST' and file_id != 4 )
Loopfor 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 JEFFER.T_TEST
              select /*+ ROWID(A) */ * from JEFFER.TEST 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;
/ 4) 验证测试
select count(*) from JEFFER.T_TEST;
查看上面sql,如果TEST表存放在几个数据文件上（不仅仅存放在file_id=4上），上面结果肯定应该是>0的。
另验证上面是否ROWSPERBLOCK取值太小而有数据丢失，可如下进行
a,记下上面count(*)值为tot1，并truncate table T_test.b,将ROWSPERBLOCK取值放大一倍，重新运行上述脚本。
c,执行select count(*) tot2 from JEFFER.T_TEST;d,比较tot1,tot2值，如果相等，则ROWSPERBLOCK取值是没问题的
如果tot1<tot2，则ROWSPERBLOCK取值偏小。
此时可重复a-d步骤，直至tot1=tot2来保证未有数据丢失。当tot1=tot2时，T_TEST表里数据为所有我们需提取的数据。 注：上面是处理ora-376错误方法，如是其他错误，相应更改file_id != 条件换成其他排除条件就行了。
如果此表TEST是分区表，还可以使用exchange partition来处理如下：
ALTER TABLE TEST EXCHANGE PARTITION p2 WITH TABLE T_TEST WITHOUT VALIDATION; 其中TEST是出问题的表明,p2是出问题数据文件所在表空间对应的分区名,T_TEST是交换出去的目标表明,可以不是分区表.
注：上述处理的话,那出问题分区p2上所有数据都丢失了，而除了丢失的数据文件外，其他的数据文件上的数据是可以提取出来的。
这个要看你的实际情况来选择具体处理方法了！