对于无备份情况下的ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题，可以通过如下PL/SQL 构造ROWID的方式挽救绝大多数非坏块的数据， 一般分成 2种情况 有索引可以用来获取ROWID， 或者 没有索引、索引不可用，必须通过dbms_rowid.ROWID_CREATE来构造ROWID的方式。
对于有索引的情况，可以直接使用MOS上提供的脚本：
REM Create a new table based on the table that is producing errors with no rows:create table
as
select *
from
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 onDECLARETYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;CURSOR c1 IS select /*+ index(tab1) */ rowidfrom tab1where is NOT NULL;r RowIDTab;rows NATURAL := 20000;bad_rows number := 0 ;errors number;error_code number;myrowid rowid;
BEGINOPEN c1;LOOPFETCH c1 BULK COLLECT INTO r LIMIT rows;EXIT WHEN r.count=0;BEGINFORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONSinsert into select /*+ ROWID(A) */ from A where rowid = r(i);EXCEPTIONwhen OTHERS thenBEGINerrors := SQL%BULK_EXCEPTIONS.COUNT;FOR err1 IN 1..errors LOOPerror_code := SQL%BULK_EXCEPTIONS(err1).ERROR_CODE;if error_code in (1410, 8103, 1578) thenmyrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);bad_rows := bad_rows + 1;insert into bad_rows values(myrowid, error_code);elseraise;end if;END LOOP;END;END;commit;END LOOP;commit;CLOSE c1;dbms_output.put_line('Total Bad Rows: '||bad_rows);
END;
/
对于没有索引或者索引损坏的情况可以使用如下方法：
创建示例数据create table maclean_tab1 (t1 int,t2 date default sysdate) tablespace users partition by range(t1) (partition p1 values less than (10000), partition p2 values less than (20000), partition p3 values less than (30000), partition p4 values less than (40000), partition p5 values less than (50000), partition p6 values less than (60000), partition p7 values less than (70000), partition p8 values less than (80000), partition p9 values less than (90000), partition p10 values less than (100000), partition p11 values less than (110000), partition p12 values less than (120000), partition p13 values less than (130000), partition p14 values less than (140000), partition p15 values less than (150000), partition p16 values less than (160000)) ;insert into maclean_tab1(t1) select rownum from dual connect by level<160000;commit;SQL> select count(*) from maclean_tab1;COUNT(*) ----------159999exec dbms_stats.gather_table_stats(USER,'MACLEAN_TAB1'); SQL> alter system flush buffer_cache;System altered.SQL> /System altered.随即采样一些块来做 坏块 约涉及到10个块的数据set linesize 200 pagesize 1400select dbms_rowid.rowid_block_number(rowid) blkid,dbms_rowid.rowid_relative_fno(rowid) rfilefrom maclean_tab1 sample(0.01)where rownum <= 200group by dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid)order by 1;BLKID RFILE ---------- ----------741833 4741850 4741994 4742030 4742085 4742141 4742159 4742172 4742173 4742179 4制造坏块[oracle@vrh8 udump]$ rman target /RMAN> blockrecover datafile 4 block 741833,741850,741994,742030,742085,742141,742159,742172,742173,742179 clear;Starting blockrecover at 21-APR-13 using channel ORA_DISK_1 Finished blockrecover at 21-APR-13SQL> select count(*) from maclean_tab1; select count(*) from maclean_tab1* ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 4, block # 741833) ORA-01110: data file 4: '/s01/oradata/G10R25/datafile/o1_mf_users_8nx5srgb_.dbf'通过blockrecover datafile block clear 构造了一系列坏块且没有备份 ，我们通过下列脚本挽回大部分可用数据一个实际操作过程drop table maclean_tab_backup;create table maclean_tab_backup tablespace users nologging compress pctfree 0 pctused 99 --可以注释掉的as select * from maclean_tab1 where 1=0;drop table bad_rows; create table bad_rows (row_id rowid,oracle_error_code varchar2(50)) tablespace users nologging compress pctfree 0 pctused 99 --可以注释掉的;set serveroutput on; set timing on;DECLARETYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;CURSOR Crowid_info ISselect Do.DATA_OBJECT_ID dataid,DE.FILE_ID fid,DE.BLOCK_ID blkid,DE.BLOCKS blkcntfrom dba_objects DO, dba_extents DEwhere DO.OBJECT_NAME = 'MACLEAN_TAB1' --and DE.PARTITION_NAME='&PARTITION_NAME' --若指定分区则取消注释and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')and DO.OBJECT_NAME = DE.SEGMENT_NAMEand DO.owner = 'SYS'order by 1, 2, 3 asc;bad_rows number := 0;errors varchar2(500);error_code varchar2(500);myrowid rowid; BEGIN/* Maclean Liu http://www.askmaclean.com * Copy Right 2013-4-20 */ execute immediate 'alter session set commit_write=''batch,nowait'' ';for i in Crowid_info loopfor j in 0 .. i.blkcnt - 1 loopfor z in 0 .. 2000 loopbeginmyrowid := dbms_rowid.ROWID_CREATE(1,i.dataid,i.fid,i.blkid + j,z);insert into maclean_tab_backupselect /*+ ROWID(A) */*from maclean_tab1 Awhere rowid = myrowid;EXCEPTIONwhen OTHERS thenBEGINerrors := SQLERRM;error_code := SQLCODE;if (error_code like '%1410%' or error_code like '%8103%' or error_code like '%1578%') thenbad_rows := bad_rows + 1;insert into bad_rows values (myrowid, error_code);commit;elseraise;end if;END;commit;end;end loop;end loop;end loop;dbms_output.put_line('Total Bad Rows: ' || bad_rows);commit; END; /Elapsed: 00:01:10.16SQL> select count(*) from maclean_tab_backup;COUNT(*) ----------155921===>损失了少量的10个块的数据
原始脚本如下：
步骤1 创建备份表create table <new table name>
as
select *
from <original table name>
where 1=2;步骤2 创建bad_rows表create table bad_rows (row_id rowid,oracle_error_code varchar2(50));步骤3 运行下列脚本DECLARETYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;CURSOR Crowid_info ISselect Do.DATA_OBJECT_ID dataid,DE.FILE_ID fid,DE.BLOCK_ID blkid,DE.BLOCKS blkcntfrom dba_objects DO, dba_extents DEwhere DO.OBJECT_NAME = '&TABNAME' --and DE.PARTITION_NAME='&PARTITION_NAME' --若指定分区则取消注释and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')and DO.OBJECT_NAME = DE.SEGMENT_NAMEand DO.owner = '&OWNER'order by 1, 2, 3 asc;bad_rows number := 0;errors varchar2(500);error_code varchar2(500);myrowid rowid;
BEGIN/* Maclean Liu http://www.askmaclean.com * Copy Right 2013-4-20 */ execute immediate 'alter session set commit_write=''batch,nowait'' ';for i in Crowid_info loopfor j in 0 .. i.blkcnt - 1 loopfor z in 0 .. 2000 loopbeginmyrowid := dbms_rowid.ROWID_CREATE(1,i.dataid,i.fid,i.blkid + j,z);insert into &backup_tableselect /*+ ROWID(A) */*from &source_table Awhere rowid = myrowid;EXCEPTIONwhen OTHERS thenBEGINerrors := SQLERRM;error_code := SQLCODE;if (error_code like '%1410%' or error_code like '%8103%' or error_code like '%1578%') thenbad_rows := bad_rows + 1;insert into bad_rows values (myrowid, error_code);commit;elseraise;end if;END;commit;end;end loop;end loop;end loop;dbms_output.put_line('Total Bad Rows: ' || bad_rows);commit;
END;
/