通过ROWID找回坏块数据

联系:手机(+86 13429648788) QQ(107644445)QQ咨询惜分飞

标题:通过ROWID找回坏块数据

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一.准备环境

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 12月 23 10:49:52 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create tablespace t_xff datafile 'E:\ORACLE\ORADATA\XFF\t_xff01.dbf'
  2  size 10m autoextend on next 10m maxsize 1g;

表空间已创建。

SQL> create table t_xifenfei tablespace t_xff
  2  as
  3  select * from dba_objects;

表已创建。

SQL> select count(*) from t_xifenfei;

  COUNT(*)
----------
     73286

二.发现坏块
使用ULtraEdit破坏数据(关闭数据库执行)

SQL> select count(*) from t_xifenfei;
select count(*) from t_xifenfei
                     *
第 1 行出现错误:
ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373)
ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'

三.查询坏块相关信息

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;

SQL> col tablespace_name for a30
SQL> col segment_type for a5
SQL> col owner for a10
SQL> col segment_name for a20
SQL> SELECT tablespace_name, segment_type, owner, segment_name
  2                 FROM dba_extents
  3                WHERE file_id =13
  4  AND 373 between block_id AND block_id + blocks - 1 ;

TABLESPACE_NAME                SEGME OWNER      SEGMENT_NAME
------------------------------ ----- ---------- --------------------
T_XFF                          TABLE CHF        T_XIFENFEI

SQL> SELECT data_object_id
  2            FROM dba_objects
  3   WHERE object_name = 'T_XIFENFEI'  and owner='CHF';

DATA_OBJECT_ID
--------------
         77759
--坏块的最小rowid
SQL> select dbms_rowid.rowid_create(1, 77759,13,373,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF1AAA

坏块的最大rowid(block+1得到)
SQL> select dbms_rowid.rowid_create(1, 77759,13,374,0) from dual;

DBMS_ROWID.ROWID_C
------------------
AAAS+/AANAAAAF2AAA

四.根据rowid找回数据

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE ROWID>='AAAS+/AANAAAAF2AAA';

  COUNT(*)
----------
     55858

SQL> SELECT /*+ ROWID(A) */ COUNT(*) FROM T_XIFENFEI A
  2  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';

  COUNT(*)
----------
     17358

SQL> SELECT 77759-55858-17358 from dual;

77759-55858-17358
-----------------
             4543

SQL> CREATE TABLE T_XIFENFEI_BAK  TABLESPACE T_XFF
  2  AS
  3  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  4  WHERE ROWID>='AAAS+/AANAAAAF2AAA';

表已创建。

SQL> INSERT INTO  T_XIFENFEI_BAK
  2  SELECT /*+ ROWID(A) */ * FROM T_XIFENFEI A
  3  WHERE  ROWID<'AAAS+/AANAAAAF1AAA';

已创建17358行。

SQL> COMMIT;

提交完成。

SQL> SELECT COUNT(*) FROM T_XIFENFEI_BAK;

  COUNT(*)
----------
     73216

五.和dbms_repair解决坏块对比

SQL> CONN / AS SYSDBA
已连接。
SQL> exec dbms_repair.skip_corrupt_blocks('CHF','T_XIFENFEI');

PL/SQL 过程已成功完成。

SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI';

SKIP_COR
--------
ENABLED

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     73216

通过跳过坏块和rowid功能对比可以看出,两者丢失的数据是相同的,如果有index,同样利用rowid结合index,可能会找回部分数据。当然dbms_repair也提供了类此的功能。两种方法的使用看个人的爱好与习惯。

此条目发表在 Oracle备份恢复 分类目录,贴了 标签。将固定链接加入收藏夹。

通过ROWID找回坏块数据》有 2 条评论

  1. 惜 分飞 说:
    Salvaging data from a TABLE in Oracle8 onwards 
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      This article is an extension to Note:28814.1 which discusses the
      options available for handling block corruptions in Oracle.
      Here we describe how to retrieve data from a table which contains a
      corrupt block (or blocks) in Oracle8/8i releases.
      *** This article does NOT cover Oracle7 - see Note:34371.1 instead ***
    
      In order to use the steps here you need to have a list of all corrupt
      File/Blocks in the table. Ideally you should have the following
      information to hand:
    
    	- Original Error
    	- Absolute File# 	- Referred to as <AFN> in this article
    	- Relative File# 	- Referred to as <RFN> in this article
    	- Block# 	 	- Referred to as <BL> in this article
    	- Object Type	 	- eg: TABLE , TABLE PARTITION or CLUSTER
    	- Object Owner.Name 	
    	- Related Objects	- eg: Indexes, Foreign key constraints ,
    				      Partition Name etc..
    
      If not then see Note:28814.1 for details of how to get this information.
    
    
      There are several ways to extract data from a corrupt table:
    
                - (1) Use a special event which can SKIP over corrupt blocks.
    		  This is by far the simplest option to extract table data
    		  and is discussed in Note:33405.1. Note that this event
    		  can only be used if the corrupt block reports ORA-1578.
    
                - (2) Use a ROWID range scan to select around a corrupt block.
                      This method is discussed in this article, along with 
    		  notes on how to select data that was in the corrupt block
    		  from any indexed columns.
    
                - (3) There are various salvage programs / PLSQL scripts which
                      can be used to salvage data from a table. These can take
                      longer to set up and use than the above methods but can
                      often cope with various kinds of corruption.
                      These are listed in Note:28814.1 and are not covered here.
    
    
    
    Using ROWID Range Scans in Oracle8:
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
       The ROWID in Oracle8/8i is externalised as an 18 digit character string
       with the format 'OOOOOOFFFBBBBBBSSS' where:
    
           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.
    
       Eg: If we issued "SELECT ACCT_NO, ROWID from EXAMPLE;" we would get 
           something like:
    
       ACCT_NO    ROWID
       ---------- ------------------
            12345 AAAAh3AAGAAACJAAAA
            19283 AAAAh3AAGAAACJAAAB
            22345 AAAAh4AAFAAAAADAAA
            60372 AAAAh4AAFAAAAADAAB
    
       This format is called the extended ROWID character format.
       In order to create a ROWID string you must have all of the components.
       Then you can use the following function in the DBMS_ROWID package:
    
         function 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
    
    
       To construct a ROWID for a ROWID range scan we use the following input
       to the ROWID_CREATE function:
    
         ROWID_TYPE:
         ~~~~~~~~~~~
         	This is 1 because we are using the extended rowid format.
    
         RELATIVE_FNO:
         ~~~~~~~~~~~~~
    	This should have been available when you came to this article.
            It can also be found from the DBA_EXTENTS view given the absolute file
    	number and block number of the corrupt block:
    
    		SELECT tablespace_name, relative_fno,
    			segment_type, owner, segment_name, partition_name
    		  FROM dba_extents
    		 WHERE file_id = <AFN>
    		   AND <BL> between block_id and block_id + blocks -1
    		;
    
    	Remember that Oracle8 reports <RFN> in an ORA-1578 error, and <AFN>
    	in the accompanying ORA-1110 error.
    
         OBJECT_NUMBER:
         ~~~~~~~~~~~~~
            For a non-partitioned table, select the DATA_OBJECT_ID from
    	DBA_OBJECTS for the problem table:
    
              SELECT data_object_id
    	    FROM dba_objects
    	   WHERE object_name = '<TABLE-NAME>'
    	     AND owner = '<TABLE-OWNER>'
    	  ;
    
            Note that a partitioned table has an object number for each partition.
            Select the DATA_OBJECT_ID from DBA_OBJECTS thus:
    
           	  SELECT data_object_id
    	    FROM dba_objects
    	   WHERE object_name = '<TABLE-NAME>'
    	     AND owner = '<TABLE-OWNER>'
     	     AND subobject_name = '<PARTITION-NAME>'
    	  ;
    
    
          BLOCK_NUMBER and ROW_NUMBER:
          ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    	The block number of the corrupt block should be available before
    	you came to this article. (Eg: It is reported in an ORA-1578 error,
    	or as a Page Number by DBVerify).
    	For a ROWID range scan we generally want to select all rows BEFORE
    	the corrupt block, then all rows AFTER the corrupt block. The first
    	row in a block is row zero (0) and so we want all rowids LESS THAN
    	"Block <BL> row 0" and then GREATER THAN OR EQUAL TO "Block <BL>+1
    	row 0".
    
    
       You can now create the rowid strings to use in a predicate thus:
    
         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;
    
       It is now possible to use CREATE TABLE AS SELECT or INSERT ... SELECT
       to get data without accessing the corrupt block using a query of the
       form:
    
    	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>'
    	;
    
       (Note that "A" is being used as a table alias in the HINT and in 
        the FROM clause, and that we want LESS THAN the "lo_rid" and 
        GREATER THAN OR EQUAL TO the "hi_rid")
    
       For a table partition then only the problem partition need be selected
       from by using the PARTITION(xxx) option in the FROM clause:
    
    	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>'
    	;
    
       NOTE: Please note this procedure can't be used for tables that have columns defined as LONG.
             For tables with LONG you will have to use export/import.
             Export ca be run using WHERE clause option.
    
       Once the table data has been salvaged then the tables can be renamed, or
       partition exchanged with the table, to put the salvaged data into place.
       The next steps are then normally to:
    
    	- See if any data can be extracted from the corrupt block itself, 
    	  or from indexes pointing at the corrupt block.
    	  This is discussed briefly below.
    
    	- Sort out indexes, constraints etc.. on the new table / partition
    	  This is not discussed here.
    
       If the corrupt block is the table segment header, this method won't work. You still 
       have the option of using any indexes on the corrupt table to extract the data.
       Use the following query to determine if the affected block is the segment header :
    
       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 ZERO is segment header
    
    
      Finding out about data in the Corrupt Block
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        If there are any indexes on the corrupt table then it is possible to
        get some information about what data was in the corrupt block from the
        index. This requires selecting indexed columns from the table for
        rowids in the corrupt block. We already know the ROWID range covered
        by the corrupt block from the SELECT dbms_rowid.rowid_create ... 
        statements above. To extract the column data use one of the 
        following forms of select statement:
    
        If the columns required at NOT NULLable you can use a fast full scan:
    
    	SELECT /*+ INDEX_FFS(X <index_name>) */ 
    		<index_column1>, <index_column2> ...
    	  FROM <tablename> X
    	 WHERE rowid >= '<low_rid>'
    	   AND rowid <  '<hi_rid>'
    	;
    
        If the columns required are NULLable then you cannot use an index
        fast full scan and must use a range scan. This requires you to know
        a minimum possible value for the leading index column to ensure you
        enable the index scan:
    
    	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>
    	;
        
        Using this technique for all indexes on the table may be able to retrieve 
        some of the data. See <View:DBA_IND_COLUMNS> for which columns make 
        up each index.
    
    
    Example of using ROWID Range Scans in Oracle8/8i or higher
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      This example shows the main steps only (missing out index information) for an
      ORA-1578 error. This example would be better handled using the 10231
      event but we show the ROWID range scan method here:
    
    	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
            In this example the absolute and relative file numbers happen 
    	to be the same.
    
    	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
    
        >> Use the block number in the error first
    
    	SQL> select dbms_rowid.rowid_create(1, 88145,7,12698,0) from dual;
    
    	DBMS_ROWID.ROWID_C
    	------------------
    	AAAVhRAAHAAADGaAAA
    
        >> Use the block number +1 next
    
    	SQL>  select dbms_rowid.rowid_create(1, 88145,7,12699,0) from dual;
    
    	DBMS_ROWID.ROWID_C
    	------------------
    	AAAVhRAAHAAADGbAAA
    
        >> Now we can use the ROWID in SELECT, CTAS, INSERT AS SELECT etc..
    
    	SQL> SELECT /*+ ROWID(A) */ * 
    	       FROM scott.partitionexample A
         	      WHERE rowid < 'AAAVhRAAHAAADGaAAA';
    
    	COLUMN1    COLUMN2
    	---------- ----------
    	        15 a
    	       ...
    	No error as we do not access the corrupt block.
    
    
        >> We can also attempt to see what data WAS in the corrupt block.
        >> Eg: Assume we have an index "PARTEXAM" on "COLUMN1" of our
        >>     "PARTITIONEXAMPLE" table, and COLUMN1 is NOT NULL then we can:
    
    	SQL> SELECT /*+ INDEX_FFS(A PARTEXAM) */  column1
    	       FROM  scott.partitionexample A
    	      WHERE  rowid >= 'AAAVhRAAHAAADGaAAA'
    		AND  rowid <  'AAAVhRAAHAAADGbAAA' ;
    	
    	COLUMN1
    	----------
    	        25
    	...
    
    
    -------------------------------------------------------------------------
    

      [引用]  [回复]

  2. 惜 分飞 说:

    取消dbms_repair跳过坏块功能

    SQL> EXEC dbms_repair.skip_corrupt_blocks(schema_name => 'CHF',
     2   object_name => 'T_XIFENFEI',object_type => DBMS_REPAIR.TABLE_OBJECT,
     3   flags => DBMS_REPAIR.NOSKIP_FLAG);
    
    PL/SQL 过程已成功完成。
    
    SQL>  select skip_corrupt from dba_tables where table_name='T_XIFENFEI';
    
    SKIP_COR
    --------
    DISABLED
    
    SQL> select count(*) from chf.t_xifenfei;
    select count(*) from chf.t_xifenfei
                             *
    第 1 行出现错误:
    ORA-01578: ORACLE 数据块损坏 (文件号 13, 块号 373)
    ORA-01110: 数据文件 13: 'E:\ORACLE\ORADATA\XFF\T_XFF01.DBF'
    

      [引用]  [回复]

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>