Skip to content

Oracle恢复

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

前面介绍通过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
	...

 

 


 

 

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

How to recreate idl_ub1$,idl_char$,idl_ub2$,idl_sb4$.

近期遭遇一个客户误truncate idl_ub1$表,导致数据库关闭后无法启动,在受到支援请求后在1小时内解决了此问题,遭遇了此类问题的处理方式主要如下,

以upgrade方式打开数据库,并截断相关表

startup upgrade
truncate table idl_ub1$;
truncate table idl_char$;
truncate table idl_ub2$;
truncate table idl_sb4$;

接着执行utlirp和rmjvm两个脚本,并关闭数据库

@c:\oracle\product\10.2.0\db_1\rdbms\admin\utlirp
shutdown immediate
startup upgrade
@c:\oracle\product\10.2.0\db_1\javavm\install\rmjvm
shutdown immediate
exit

 

再次启动数据库后,设置监听为临时不可用状态并依据数据库组件状态运行相关脚本

startup upgrade
alter system set "_system_trig_enabled"=false scope=memory;

@c:\oracle\product\10.2.0\db_1\javavm\install\initjvm.sql
-- 如果xml已经安装执行此脚本
@c:\oracle\product\10.2.0\db_1\xdk\admin\initxml.sql
@c:\oracle\product\10.2.0\db_1\xdk\admin\xmlja.sql
@c:\oracle\product\10.2.0\db_1\rdbms\admin\catjava.sql
--

shutdown immediate
exit

 

启动数据库并执行ultrp修复失效对象,运行编译修复后,修复完成。

startup upgrade
@c:\oracle\product\10.2.0\db_1\rdbms\admin\utlrp
exit

SQLPLUS / AS SYSDBA
shutdown immediate
startup
execute utl_recomp.recomp_serial();
exit