无备份情况下,绕开ORA-01578错误的脚本

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 无备份情况下,绕开ORA-01578错误的脚本

无备份情况下,绕开ORA-01578错误的脚本

今天做测试数据导入的时候,执行imp过程中alert.log提示了错误,

Hex dump of (file 10, block 220258) in trace file /DATA/app/ora11g/diag/rdbms/wdep/WDEP/trace/WDEP_ora_23477.trc Corrupt block relative dba: 0x02835c62 (file 10, block 220258) Bad check value found during buffer read Data in bad block:  type: 6 format: 2 rdba: 0x02835c62  last change scn: 0x0000.000b1e8c seq: 0x5 flg: 0x04  spare1: 0x0 spare2: 0x0 spare3: 0x0  consistency value in tail: 0x1e8c0605  check value in block header: 0xab9c  computed block checksum: 0x4 Reading datafile '/DATA/oracle/oradata_WDEP/WDEP/acpt_dat01.dbf' for corruption at rdba: 0x02835c62 (file 10, block 220258) Reread (file 10, block 220258) found same corrupt data (no logical check) Thu Jan 18 14:17:22 2018 Corrupt Block Found          TSN = 9, TSNAME = ACPE          RFN = 10, BLK = 220258, RDBA = 42163298          OBJN = 15099, OBJD = 15099, OBJECT = TBL, SUBOBJECT = TBL_2014Q3          SEGMENT OWNER = XNDEP, SEGMENT TYPE = Table Partition Errors in file /DATA/app/ora11g/diag/rdbms/wdep/WDEP/trace/WDEP_ora_23477.trc  (incident=8239): ORA-01578: ORACLE data block corrupted (file # 10, block # 220258) ORA-01110: data file 10: '/DATA/oracle/oradata_WDEP/WDEP/acpt_dat01.dbf' Incident details in: /DATA/app/ora11g/diag/rdbms/wdep/WDEP/incident/incdir_8239/WDEP_ora_23477_i8239.trc Errors in file /DATA/app/ora11g/diag/rdbms/wdep/WDEP/trace/WDEP_ora_23477.trc  (incident=8240): ORA-01578: ORACLE data block corrupted (file # 10, block # 220258) ORA-01110: data file 10: '/DATA/oracle/oradata_WDEP/WDEP/acpe_dat01.dbf' Incident details in: /DATA/app/ora11g/diag/rdbms/wdep/WDEP/incident/incdir_8240/WDEP_ora_23477_i8240.trc Thu Jan 18 14:17:31 2018 Dumping diagnostic data in directory=[cdmp_20180118141731], requested by (instance=1, osid=23477), summary=[incident=8239]. Thu Jan 18 14:17:31 2018 Sweep [inc][8239]: completed

从错误信息看,ORA-01578提示10号文件的220258块corrupted,而且提示很明确,是ACPE表空间,object_name是TBL表,他是一张分区表,出现错误的块属于TBL_2014Q3子分区,

TSN = 9, TSNAME = ACPE          RFN = 10, BLK = 220258, RDBA = 42163298          OBJN = 15099, OBJD = 15099, OBJECT = TBL, SUBOBJECT = TBL_2014Q3          SEGMENT OWNER = XNDEP, SEGMENT TYPE = Table Partition

此时执行select count(*)等操作,会因为有corrupted的块,提示错误,无法直接得到检索结果,只有检索非TBL_2014Q3分区才能有返回。

测试库没有开启备份,TBL是一张分区表,数据量上亿了,重新执行一次导入操作,效率实在太低,作为测试数据,允许删除些数据,若可以将有corrupted的数据删除,就可以继续操作。

Maclean Liu这篇文章《利用构造ROWID实现无备份情况下绕过ORA-1578、ORA-8103、ORA-1410等逻辑/物理坏块问题》(http://www.askmaclean.com/archives/build-rowid-workaround-1578-1410-8103.html),提出了可以使用MOS的脚本《SCRIPT to skip ORA-8103 ORA-1410 ORA-1578 ORA-600 [kdsgrp1] when reading a TABLE (Doc ID 1527738.1)》,来做无备份情况下绕过ORA-01578,对于数据的恢复。

他的整体思路,就是逐行读取原表数据,碰见一些ORA错误,就将其记录于错误信息表,包括rowid和错误号,将正常数据存入备份表,这张备份表就忽略了,所有corrupted块的数据,进而可以提供正常的访问。

分为两种场景,有索引可以用来获取ROWID,没有索引、索引不可用,必须通过dbms_rowid.ROWID_CREATE来构造ROWID,由于上面示例中,索引尚未建立,所以选择第二种方法,如下是操作过程。

  1. 创建备份数据表,用于存储可用的原表数据,

SQL create table tbl_new as select * from tbl where 1=2; Table created.

  1. 创建错误信息表,用于存储错误的行rowid和错误号,

SQL create table bad_rows (row_id rowid, oracle_error_code varchar2(50)); Table created.

  1. 执行脚本,

SQL DECLARE   2    TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;   3    CURSOR Crowid_info IS   4      select Do.DATA_OBJECT_ID dataid,   5             DE.FILE_ID        fid,   6             DE.BLOCK_ID       blkid,   7             DE.BLOCKS         blkcnt   8        from dba_objects DO, dba_extents DE   9       where DO.OBJECT_NAME = '&TABNAME'  10         and DE.PARTITION_NAME='&PARTITION_NAME' --如果是非分区表,可以注释  11         and nvl(DO.SUBOBJECT_NAME,'-1') = nvl(DE.PARTITION_NAME,'-1')  12         and DO.OBJECT_NAME = DE.SEGMENT_NAME  13         and DO.owner = '&OWNER'  14       order by 1, 2, 3 asc;  15    bad_rows   number := 0;  16    errors     varchar2(500);  17    error_code varchar2(500);  18    myrowid    rowid;  19  BEGIN  20    /* Maclean Liu http://www.askmaclean.com * Copy Right 2013-4-20 */  21    execute immediate 'alter session set commit_write=''batch,nowait'' '; --设置commite_write,为了提升效率

 22    for i in Crowid_info loop  23      for j in 0 .. i.blkcnt - 1 loop --逐个数据块操作

 24        for z in 0 .. 2000 loop --2000行记录逐条遍历

 25          begin  26            myrowid := dbms_rowid.ROWID_CREATE(1,  27                                               i.dataid,  28                                               i.fid,  29                                               i.blkid + j,  30                                               z);  31            insert into &backup_table --向备份数据表,执行插入操作

 32              select /*+ ROWID(A) */  33               *  34                from &source_table A  35               where rowid = myrowid;  36          EXCEPTION  37            when OTHERS then --碰见ORA-01410/ORA-08103/ORA-01578错误,则立即记录错误信息表

 38              BEGIN  39                errors     := SQLERRM;  40                error_code := SQLCODE;  41                if (error_code like '%1410%' or error_code like '%8103%' or  error_code like '%1578%') then  42                  bad_rows := bad_rows + 1;  43                  insert into bad_rows values (myrowid, error_code);  44                  commit;  45                else  46                  raise;  47                end if;  48              END;  49              commit;  50          end;  51        end loop;  52      end loop;  53    end loop;  54    dbms_output.put_line('Total Bad Rows: ' || bad_rows);  55    commit;  56  END;  57  /

输入参数,分别为待恢复的表名、分区名称、表所属用户名、备份数据表名、原表名称,

Enter value for tabname: TBL old   9:      where DO.OBJECT_NAME = '&TABNAME' new   9:      where DO.OBJECT_NAME = 'TBL' Enter value for partition_name: TBL_2014Q3 old  10:        and DE.PARTITION_NAME='&PARTITION_NAME' new  10:        and DE.PARTITION_NAME='TBL_2014Q3' Enter value for owner: XNDEP old  13:        and DO.owner = '&OWNER' new  13:        and DO.owner = 'XNDEP' Enter value for backup_table: TBL_NEW old  31:           insert into &backup_table new  31:           insert into TBL_NEW Enter value for source_table: TBL old  34:               from &source_table A new  34:               from TBL A

接下来就是等待,可以检索错误信息表,

SQL select * from bad_rows where rownum 11; ROW_ID             ORACLE_ERROR_CODE ------------------ -------------------------------------------------- AAADr7AAKAAAwCAAFK -8103 AAADr7AAKAAAwCAAFL -8103 AAADr7AAKAAAwCAAFM -8103 AAADr7AAKAAAwCAAFN -8103 AAADr7AAKAAAwCAAFO -8103 AAADr7AAKAAAwCAAFP -8103 AAADr7AAKAAAwCAAFQ -8103 AAADr7AAKAAAwCAAFR -8103 AAADr7AAKAAAwCAAFS -8103 AAADr7AAKAAAwCAAFT -8103 10 rows selected.

记录了ORA-01410、ORA-08103和ORA-01578这三种错误,

SQL select distinct oracle_error_code from bad_rows; ORACLE_ERROR_CODE -------------------------------------------------- -1410 -8103 -1578

这三种错误解释,

SQL !oerr ora 1410 01410, 00000, "invalid ROWID" // *Cause: // *Action:

SQL !oerr ora 8103 08103, 00000, "object no longer exists" // *Cause:  The object has been deleted by another user since the operation //          began, or a prior incomplete recovery restored the database to //          a point in time during the deletion of the object. // *Action: Delete the object if this is the result of an incomplete //          recovery.

SQL !oerr ora 1578 01578, 00000, "ORACLE data block corrupted (file # %s, block # %s)" // *Cause:  The data block indicated was corrupted, mostly due to software //          errors. // *Action: Try to restore the segment containing the block indicated. This //          may involve dropping the segment and recreating it. If there //          is a trace file, report the errors in it to your ORACLE //          representative.

但说实话,由于上面采用的是INSERT,将正常数据导入的方式,对于这种上亿数据量的表,并不是很合适,一次次执行插入,非常耗时,对于一些数据量适中的表,可以说这是一种简便的方法,可以绕开ORA-01578的错误,保证正常的数据可以访问。

除此之外,MOS有以下两个脚本,在10231事件错误的情况下,可以绕开堆表ORA-00600,以及IOT索引组织表ORA-00600,朋友们可以参考,

SKIP ORA-600 in a Table

This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work.

SKIP ORA-600 in IOT

This is useful when the ORA-600 is produced by a non-existent chained row (invalid nrid) like ORA-600 [kdsgrp1] and when event 10231 does not work for an Index Organized Table (IOT).

如果您觉得本文有帮助,欢迎朋友们关注转发:bisal的个人杂货铺,

无备份情况下,绕开ORA-01578错误的脚本 无备份情况下,绕开ORA-01578错误的脚本
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 无备份情况下,绕开ORA-01578错误的脚本


 上一篇
Oracle CBO选错执行计划的一种场景 Oracle CBO选错执行计划的一种场景
测试人员做应用性能测试,反馈有一条SQL语句,之前执行非常快,现在执行时间,明显慢了。 SQL语句非常简单,根据日期字段,检索当日表中记录数, select count(*) from user.tbl where cki_d
下一篇 
Oracle软件的收费标准 Oracle软件的收费标准
昨天微信群中,有朋友问Oracle的收费价格,eygle发了一份16年版的《Oracle Technology Global Price List》,列出了Oracle几乎所有产品,对应的收费方式以及价格,不仅有数据库,还有Web