数据技术 – 第19页 – Ludatou's data life

从坏块中恢复未损坏的数据

前面介绍通过skip坏块（event或者dbms包）和构造rowid的方式，以及通过salvage脚本从存在坏块的表中恢复数据，同时也可以通过mdata恢复工具来unload出表中的数据，这里补充一个场景，就是坏块中未损坏的数据的恢复。

从已经损坏的块中尽可能的恢复数据的思路依旧参考的是通过rowid的方式来实现恢复，这里讨论oracle 8i以及8i以上版本的数据库.

rowid基本结构这里不做详细解释，前面有介绍过，这下面是官方的介绍。

       OOOOOO = is a base 64 encoding of the 32-bit dataobj# (Data object
                number was introduced in 8.0 to track versions of the same
                segment because certain operations can change the version.
                It is used to discover stale ROWIDs and stale undo records)
          FFF = is a base 64 encoding of the relative file number
       BBBBBB = is a base 64 encoding of the block number
          SSS = is a base 64 encoding of the slot (row) number

   Note that the ROWID contains the relative file number which is distinct from
   the absolute file number and new for Oracle8.  A relative file number is
   relative to the tablespace (meaning a tablespace can have a first, second,
   third file, etc.) and an absolute file number is absolute in the whole
   system.  Two different files may have the same relative number.

   例如 "SELECT ACCT_NO, ROWID from EXAMPLE;" 获得的如下结果：

   ACCT_NO    ROWID
   ---------- ------------------
        12345 AAAAh3AAGAAACJAAAA
        19283 AAAAh3AAGAAACJAAAB
        22345 AAAAh4AAFAAAAADAAA
        60372 AAAAh4AAFAAAAADAAB

     dbms_rowid构造rowid的函数：

              ROWID_CREATE(rowid_type    IN number,
                           object_number IN number,
                           relative_fno  IN number,
                           block_number  IN number,
                           row_number    IN number)
              return ROWID;

     -- rowid_type      - type (restricted=0/extended=1)
     -- object_number   - data object number
     -- relative_fno    - relative file number
     -- block_number    - block number in this file
     -- row_number      - row number in this block



    使用高低位的rowid来跳过坏块使用基于rowid的方式扫描出相关rows，这和上篇介绍的构造的rowid方式是一样的。

     The "LOW_RID" is the lowest rowid INSIDE the corrupt block:

       SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>,0) LOW_RID
	 from DUAL;

     The "HI_RID" is the first rowid AFTER the corrupt block:

       SELECT dbms_rowid.rowid_create(1,<DATA_OBJECT_ID>,<RFN>,<BL>+1,0) HI_RID
	 from DUAL;


	CREATE TABLE salvage_table AS
	 SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A
	  WHERE rowid < '<low_rid>'
	;

	INSERT INTO salvage_table 
	 SELECT /*+ ROWID(A) */ * FROM <owner.tablename> A
	  WHERE rowid >= '<hi_rid>'
	;

    如果是分区表，则通过针对存在坏块的分区的方式即可

	CREATE TABLE salvage_table AS
	 SELECT /*+ ROWID(A) */ * 
	   FROM <owner.tablename> PARTITION (<partition_name>) A
	  WHERE rowid < '<lo_rid>'
	;

	INSERT INTO salvage_table 
	 SELECT /*+ ROWID(A) */ * 
	   FROM <owner.tablename> PARTITION (<partition_name>) A
	  WHERE rowid >= '<hi_rid>'
	;

   如果表中带有long字段需要使用exp/imp的方式来进行相关数据的导入导出，同时要带有where条件来帅选出rowid的范围。
   剩下就是要讨论的从损坏的块中恢复数据，当然如果坏块是段头那么就没必要从段头中恢复rows，可以通过如下的sql判断损坏的块是否表段头。
 
   select file_id,block_id,blocks,extent_id 
   from dba_extents 
   where owner='<owner>' 
     and segment_name='<table_name>' 
     and segment_type='TABLE'
   order by extent_id;

     FILE_ID  BLOCK_ID    BLOCKS EXTENT_ID
   --------- --------- --------- ---------
           8     94854     20780         0 <- EXTENT_ID 为0则代表坏块为段头


  Finding out about data in the Corrupt Block
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  构造rowid的方式根据块号可以构造出坏块里面row地址，前面有说明，如果表中存在索引，则可以尝试通过索引来判断坏块中row的信息，可以结合上面提到的坏块上下块的高低位rowid来扫描出坏块里的rows信息，具体如下：
 
  如果索引所在字段要求为非空，则可以通过以下sql的索引快速全扫描来实现坏块rowid的获取： 
	SELECT /*+ INDEX_FFS(X <index_name>) */ 
		<index_column1>, <index_column2> ...
	  FROM <tablename> X
	 WHERE rowid >= '<low_rid>'
	   AND rowid <  '<hi_rid>'
	;

如果索引所在的列允许null，则无法使用索引快速全扫描并且必须使用范围扫描。这需要知道确保领先索引列的最小可能值，具体sql如下：

	SELECT /*+ INDEX(X <index_name>) */ 
		<index_column1>, <index_column2> ...
	  FROM <tablename> X
	 WHERE rowid >= '<low_rid>'
	   AND rowid <  '<hi_rid>'
	   AND <index_column1> >= <min_col1_value>
	;

8i以上版本的恢复案例：

SQL> select * from scott.partitionexample;

	ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
	ORA-01110: data file 7: '/oracle1/oradata/V816/oradata/V816/users01.dbf'

    >>  <RFN> = 7 , <BL> = 12698 , <AFN> = 7

	SQL> SELECT tablespace_name, segment_type, owner, segment_name
               FROM dba_extents
              WHERE file_id =7
		AND 12698 between block_id AND block_id + blocks - 1 ;

        TABLESPACE_NAME  SEGMENT_TYPE       OWNER   SEGMENT_NAME
        ---------------  ------------       -----   ------------
        USERS            TABLE PARTITION    SCOTT   PARTITIONEXAMPLE

        SQL> SELECT partition_name FROM dba_extents
              WHERE file_id =7
	        AND 12698 between block_id AND block _id + blocks - 1;

	PARTITION_NAME
	------------------------------
	PARTEX2

        SQL> SELECT data_object_id
	       FROM dba_objects
	      WHERE object_name = 'PARTITIONEXAMPLE'  and owner='SCOTT'
	        AND subobject_name= 'PARTEX2';

	DATA_OBJECT_ID
 	---------------
     	88145
       
        找出高低位的rowid并扫描相关数据

	SQL> select dbms_rowid.rowid_create(1, 88145,7,12698,0) from dual;

	DBMS_ROWID.ROWID_C
	------------------
	AAAVhRAAHAAADGaAAA

	SQL>  select dbms_rowid.rowid_create(1, 88145,7,12699,0) from dual;

	DBMS_ROWID.ROWID_C
	------------------
	AAAVhRAAHAAADGbAAA

	SQL> SELECT /*+ ROWID(A) */ * 
	       FROM scott.partitionexample A
     	      WHERE rowid < 'AAAVhRAAHAAADGaAAA';

	COLUMN1    COLUMN2
	---------- ----------
	        15 a
	       ...

	SQL> SELECT /*+ INDEX_FFS(A PARTEXAM) */  column1
	       FROM  scott.partitionexample A
	      WHERE  rowid >= 'AAAVhRAAHAAADGaAAA'
		AND  rowid <  'AAAVhRAAHAAADGbAAA' ;
	
	COLUMN1
	----------
	        25
	...

Database Oracle Oracle恢复异常恢复 | Guang Cai Li | 2018-12-16

REM NAME: salvage.sql

REM NOTE: This program will not handle long, long raw colums or chained rows.

SET ECHO OFF;  
SET SERVEROUTPUT ON;  
  
CREATE OR REPLACE FUNCTION dectohex(a IN NUMBER) RETURN VARCHAR2 IS  
      x VARCHAR2(8) := '';  
        y VARCHAR2(1);  
        z NUMBER;  
        w NUMBER;  
    BEGIN  
        IF a > POWER(2,32) OR a < 0  THEN  
            RAISE invalid_number;  
        END IF;  
        w := a;  
        WHILE w > 0 LOOP  
 z := w MOD 16;  
        IF z = 10 THEN y := 'A';  
        ELSIF z = 11 THEN y := 'B';  
        ELSIF z = 12 THEN y := 'C';  
        ELSIF z = 13 THEN y := 'D';  
     ELSIF z = 14 THEN y := 'E';  
        ELSIF z = 15 THEN y := 'F';  
        ELSE y := TO_CHAR(z);  
        END IF;  
            w := TRUNC(w / 16);  
            x := CONCAT(y,x);   -- build x string backwards  
        END LOOP;  
        RETURN x;  
    END;  
/  
  
  
DECLARE  
  
        table_name         VARCHAR2(30):= 'dept';  
   user_name          VARCHAR2(30):= 'scott';      
  
        file_id            NUMBER;  
        block_id           NUMBER;  
        extentid           number;  
    blocks             NUMBER;  
        file_id_hex        VARCHAR2(4);  
        block_id_hex       VARCHAR2(8);  
        row_count_hex      VARCHAR2(4);  
        row_count          NUMBER;  
        block_counter      NUMBER;  
        for_loop_counter   NUMBER;  
        corrupt_block_flag NUMBER:=0;  
        check_file NUMBER;  
        check_block        NUMBER;  
        row_id_hex         CHAR(18);  
        invalid_rowid      EXCEPTION;  
        rows_per_block     NUMBER:=100;  
  
    CURSOR c1 IS SELECT file_id, block_id, blocks,extent_id  FROM dba_extents  
       WHERE SEGMENT_NAME = UPPER(table_name) AND OWNER = UPPER(user_name)  
    ORDER BY EXTENT_ID;  
            
    CURSOR c2 IS SELECT file_id,block_id FROM corrupt_block;  
      
    PROCEDURE ins (v_rowid VARCHAR2) is  
      bad_rowid  EXCEPTION;  
      PRAGMA EXCEPTION_INIT (bad_rowid, -01410);  
      BEGIN  
   INSERT INTO salvaged_rows_table  
                SELECT *  
                FROM SCOTT.DEPT  
                WHERE rowid = v_rowid;  
  
      EXCEPTION  
         WHEN bad_rowid THEN  
         NULL;  
    END ins;  
  
BEGIN  
  OPEN c1;  
  LOOP   
    FETCH c1 INTO file_id, block_id, blocks,extentid;  
    EXIT WHEN c1%NOTFOUND;  
    dbms_output.put_line('extent: file:'||to_char(file_id)||'  
block_id:'||to_char(block_id)||' blocks:'||to_char(blocks));  
    file_id_hex := DECTOHEX(file_id);  
    block_counter:= block_id;  
    for_loop_counter:= block_id + blocks-1;  
    FOR i IN block_id..for_loop_counter LOOP  
      corrupt_block_flag:= 0;  
      OPEN c2;  
        LOOP  
          FETCH c2 INTO check_file, check_block;  
          EXIT WHEN c2%NOTFOUND;  
          IF (check_block = block_counter AND check_file = file_id) THEN  
             corrupt_block_flag :=1;  
               dbms_output.put_line('....Skipping corrupt  
block:'||to_char(block_counter)|| ' file id:'||to_char(file_id));  
          END IF;  
        END LOOP;    
      CLOSE c2;  
      block_id_hex := dectohex(block_counter);  
      IF corrupt_block_flag = 0 THEN  
        FOR row_count IN 0..rows_per_block LOOP  
            IF row_count = 0 THEN  
               row_count_hex := '0';  
            ELSE  
               row_count_hex:=dectohex(row_count);    
      END IF;  
            row_id_hex:=LPAD(block_id_hex,8,0) ||'.'||  
    LPAD(row_count_hex,4,0) ||'.'|| LPAD(file_id_hex,4,0);  
            ins(row_id_hex);  
        END LOOP;  
      END IF;  
      block_counter := block_counter+1;  
      COMMIT;  
    END LOOP;       
  END LOOP;  
CLOSE c1;  
COMMIT;  
END;  
/

SALVAGE.SQL – PL/SQL Code to Extract Data from a Corrupt Table

利用构造ROWID脚本实现无备份情况下绕过ORA-1578、8103、1410、00600 kdsgrp1 等坏块场景

利用构造rowid的方式来实现绕过坏块恢复正常的表数据库，也可以通过该脚本尝试读取坏块内部未损坏的数据，只是脚本需要修改。

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_ffs(tab1 <index name>) parallel(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL
order by rowid;

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: <index name>, <original table name>, <indexed column>, <list of columns from table (ie col1, col2,..)>

<index name> should be replaced by preferrable a Primary Key index.

If a Primary Key index is used, remove the where condition: “where <indexed column> is NOT NULL”

The “order by rowid” clause is to get the rows ordered by blocks so a batch of Inserts finds the block in the buffer cache. Otherwise, one rowid can bring one block from the table and insert just one row, the next rowid brings a different row and insert one row as opposed of finding the same previous block.

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:


explain plan for
select /*+ index_ffs(tab1 <index name>) parallel(tab1) */ rowid
from <original table name> tab1
where <indexed column> is NOT NULL
order by rowid;

set lines 200
@?/rdbms/admin/utlxplp</div>

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 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.


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;
/

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).


drop table bad_rows;
create table bad_rows (row_id UROWID
,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 /*+ INDEX_FFS(IOT_1) */ rowid rid from  &&affected_table IOT_1)  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 数据技术 | Guang Cai Li | 2018-12-12

Oracle 恢复工具 Mdata 5.0.1 版本发布

经过2016到2018，修复了一些bug.

现在从新开放下载.企业版新增table drop后的lob恢复.

使用须知：

1.新版本下载后，建议使用jdk 1.4或者jdk 1.5运行.

2.新版不限制数据恢复量，不限制恢复行数，依然免费使用

3.新版程序绑定机器，需发邮件给我们提供运行时候的feature code，我们将根据feature code 生成注册码。

4.使用前请阅读下载文件中的使用说明书

联系邮箱：

feigigi@qq.com;

564439763@qq.com

下载地址：

Mdata person 5.0.1 Download

MDATA | Guang Cai Li | 2018-12-01 | MDATA

Oracle 12.2.0.1 and 18c 的原厂支持周期

一般我们确认oracle 数据库某个版本的支持周期都是通过mos 742060.1的文档来获悉，

MOS Note: 742060.1.: Release Schedule of Current Database Releases

但目前在该文档中目前并未提及oracle 18c的支持周期。如下图：

2018年7月23日，Oracle在内部发布了Oracle Database 18c 18.3.0。从此日期开始，已确定上一版本的 Oracle数据库12.2.0.1支持（如下图），Oracle 12.2.0.1的修补结束日期定在2020年7月23日，据mike dietrich 描述Oracle 18c也会发生同样的事情，一旦Oracle 19c在内部可用，将确定并公布Oracle 18c的修补结束日期。从特定日期算起至少两年 – 而不是从Oracle 18c发布之日起。

在这里需要提到的是可以通过Oracle lifetime support的手册来获取更多关于支持周期的信息

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf

12c/18c升级指南 Database Oracle 数据技术 | Guang Cai Li | 2018-09-01 | 12c 18c

数据技术 - 19. page

从坏块中恢复未损坏的数据

利用构造ROWID脚本实现无备份情况下绕过ORA-1578、8103、1410、00600 kdsgrp1 等坏块场景

利用构造rowid的方式来实现绕过坏块恢复正常的表数据库，也可以通过该脚本尝试读取坏块内部未损坏的数据，只是脚本需要修改。

SKIP ORA-1578 ORA-8103 ORA-1410

SKIP ORA-600 in a Table

SKIP ORA-600 in IOT

Oracle 恢复工具 Mdata 5.0.1 版本发布

使用须知：

联系邮箱：

下载地址：

Oracle 12.2.0.1 and 18c 的原厂支持周期

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!

近期活动

利用构造rowid的方式来实现绕过坏块恢复正常的表数据库，也可以通过该脚本尝试读取坏块内部未损坏的数据，只是脚本需要修改。

SKIP ORA-1578 ORA-8103 ORA-1410

SKIP ORA-600 in a Table

SKIP ORA-600 in IOT

使用须知：

联系邮箱：

下载地址：

Oracle 恢复工具 Mdata 5.0.1 版本发布

近期文章

分类目录

扫码关注微信公众号:Oracle运维那些事 获取定期发布的数据库运维的有趣事情!

近期活动

扫码关注微信公众号:Oracle运维那些事获取定期发布的数据库运维的有趣事情!