oracle 坏块处理脚本

原创 2013年12月06日 09:26:51

oracle 坏块处理脚本


Applies to:
Oracle Database - Enterprise Edition - Version 8.1.7.0 to 12.1.0.1.0 [Release 8.1.7 to 12.1]
Information in this document applies to any platform.


Goal
The purpose of this plsql script is to create a new table based on a table that is producing errors such as ORA-8103 or ORA-1410 or ORA-1578. The script skips the blocks/rows producing those errors.
This is done when there is not option to restore the table from a backup like applying media recovery or recovering the table from an export or other source.
The first option to skip an ORA-1578 error is to use the DBMS_REPAIR script and decide to create a new table using create table as select (CTAS); however if for any reason that does not work use the plsql in this document instead.

Caution
This sample code is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.
Proofread this sample code before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this sample code may not be in an executable state when you first receive it. Check over the sample code to ensure that errors of this type are corrected.

Fix

Run sqlplus with SYS or TABLE owner user


Example:


sqlplus '/ as sysdba'
or
sqlplus <table owner> / password

SKIP ORA-1578 ORA-8103 ORA-1410

REM Create a new table based on the table that is producing errors with no rows:

create table <new table name>
as
select *
from   <original table name>
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 on

DECLARE
  TYPE RowIDTab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
  CURSOR c1 IS select /*+ index(tab1) */ rowid
  from <original table name> tab1
  where <indexed column> 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 <new table name>
     select /*+ ROWID(A) */ <list of columns from table (ie col1, col2,..)>
     from <original table name> 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, 1578) then
       myrowid := r(SQL%BULK_EXCEPTIONS(err1).ERROR_INDEX);
       bad_rows := bad_rows + 1;
       insert into 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;
/



Notes:

Replace the next values in the plsql script by the values of the affected table: <original table name>, <indexed column>, <list of columns from table (ie col1, col2,..)>
The idea is to get the rowid's from an existent index, then get all the columns from the table for each rowid and insert these rows into the new table. Using the "index" hint, allows the optimizer to choose the most appropriated index to scan the table based on the indexed column.
Make sure that the select in the plsql is using an index. One way to verify if the index is used is to get an execution plan from sqlplus:

set autotrace trace explain
select /*+ index(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL;


Note that the plsql executes an INSERT for 20000 rows and COMMIT. If it is required to change this, adjust the value of rows. e.g.:

rows NATURAL := 50000; -> to insert 50000 rows in one execution of INSERT and commit every 50000 records.
If 'Total Bad Rows:' displays 0 and it is known for certain that there is a block incorrect on disk that is causing the ORA-8103, , then it means that the block is empty (no rows) and there is not data loss.



SKIP ORA-600

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.
If the problem is caused in an Index Organized Table (IOT) change ROWID by UROWID for the row_id column in table bad_rows.



drop table bad_rows;
create table bad_rows (row_id ROWID
                      ,oracle_error_code number);

rem Create the new empty table:

create table &&new_table
as select *
from &&affected_table
where 1=2;


set serveroutput on
declare
  n number:=0;
  bad_rows number := 0;
  error_code number;
  ora600 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora600, -600);
begin
  for i in (select rowid rid from  &&affected_table)  loop
  begin
    insert into  &&new_table
     select *
     from &&affected_table
     where rowid=i.rid;
     n:=n+1;
  exception
    when ora600 then
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,600);
     commit;
    when others then
     error_code:=SQLCODE;
     bad_rows := bad_rows + 1;
     insert into bad_rows values(i.rid,error_code);
     commit;
  end;
  end loop;
  dbms_output.put_line('Total Bad Rows: '||bad_rows);
  dbms_output.put_line('Total Good rows: '||n);
end;
/

相关文章推荐

oracle坏块处理

  • 2012年08月06日 22:09
  • 27KB
  • 下载

oracle 高级坏块处理

  • 2014年12月24日 16:41
  • 247KB
  • 下载

Oracle坏块修复处理实验

坏块分为物理坏块和逻辑坏块,前者是硬件问题产生,后者是oracle内部数据有问题,本次实验针对后者。 给数据库开启归档模式 [oracle@cancer ~]$ sqlplus / as sysdba...

Oracle 数据坏块处理

  • 2011年10月27日 17:02
  • 289KB
  • 下载

oracle无备份坏块处理

无备份坏块处理 数据库版本:9.2.0.5.0 平台:windows2003 下午接到通知以数据库出现坏块现象,无备份,应用无法正常使用,要予以解决 由于没有备份,无法恢复,只能采用跳过坏...

Oracle 坏块处理。

资料引用:http://www.knowsky.com/388986.html 亲身操作成功!(当然那个坏块的数据会丢失,如果要完整数据,只能用全备恢复,或用归档恢复了) Oracle的坏块即...
  • akuoma
  • akuoma
  • 2011年12月14日 09:16
  • 1663

Oracle 11g 基于RMAN实现坏块介质恢复(blockrecover)

基于RMAN实现坏块介质恢复(blockrecover) blockrecover datafile 介质恢复 dbv

ORACLE-数据库坏块

一. 什么是数据库的坏块首先我们来大概看一下数据库块的格式和结构:数据库的数据块有固定的格式和结构,分三层:Cache layerTransaction layerData layer在我们对数据块进...

Oracle数据库中的坏块

一 什么是数据库的坏块   首先我们来大概看一下数据库块的格式和结构——数据库的数据块有固定的格式和结构,分三层  cache layer,transaction layer,da...
  • xyz846
  • xyz846
  • 2012年02月12日 18:33
  • 297

ORACLE使用dbv工具检验数据文件是否有坏块

使用dbv工具检验数据文件是否有坏块  dbv工具可以用来验证数据文件的有效性,在数据库恢复之前可以使用该命令对备份文件进行有效性检查, 防止因备份文件本身的问题导致数据库无法恢复。 当然,db...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle 坏块处理脚本
举报原因:
原因补充:

(最多只允许输入30个字)