Oracle8及更高版本中使用 ROWID Range Scans 从损坏的表中提取数据(Doc ID 61685.1)

Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle8 and higher (Doc ID 61685.1)

Salvaging data from a TABLE in Oracle8 onwards    从Oracle8以上的表中抢救数据
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  This article is an extension to Note:28814.1 which discusses the options available for handling block corruptions in Oracle.  本文是对Note：28814.1的扩展，其中讨论了可用于处理Oracle中的块损坏的选项。
  Here we describe how to retrieve data from a table which contains a corrupt block (or blocks) in Oracle8/8i releases.   在这里，我们描述了如何从Oracle8/8i发行版中包含损坏的一个或多个块的表中检索数据。
  *** This article does NOT cover Oracle7 - see Note:34371.1 instead ***   Oracle7参见Note：34371.1 

  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  绝对文件号-在本文中称为<AFN>
	- Relative File# 	- Referred to as <RFN> in this article  相对文件号-在本文中称为<RFN>
	- Block# 	 	- Referred to as <BL> in this article   Block#-在本文中称为<BL>
	- 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.
  如果没有，请参阅Note:28814.1，以获取有关如何获取此信息的详细信息。

  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.
                  使用特殊事件可以跳过损坏的块。
                  到目前为止，这是提取表数据的最简单方法，请参见注释：33405.1。 
                  请注意，只有在损坏的块报告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.
                  使用ROWID范围扫描在损坏的块周围进行选择。
                  本文将讨论此方法，以及有关如何从任何索引列中选择损坏块中数据的说明。
            - (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.
                  有各种抢救程序/ PLSQL脚本，可用于抢救表中的数据。 
                  与上述方法相比，这些方法的设置和使用可能需要更长的时间，但通常可以应对各种损坏。 
                  Note:28814.1中列出了这些内容，此处未涉及。

Using ROWID Range Scans in Oracle8:      在Oracle8中使用ROWID范围扫描：
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   The ROWID in Oracle8/8i is externalised as an 18 digit character string with the format 'OOOOOOFFFBBBBBBSSS' where:
   Oracle8/8i中的ROWID被外部化为18位字符串，其格式为'OOOOOOFFFBBBBBBBBSSS'，其中：
       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)
                是32位dataobj#的基本64位编码（在8.0中引入了数据对象号，以跟踪同一段的版本，
                因为某些操作可以更改版本。它用于发现过时的ROWID和过时的撤消记录）
          FFF = is a base 64 encoding of the relative file number
                是相对文件号的base 64编码
       BBBBBB = is a base 64 encoding of the block number
                是块号的base 64编码
          SSS = is a base 64 encoding of the slot (row) number
                是插槽（行）号的base 64编码

   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.
   请注意，ROWID包含相对文件号，该文件号不同于绝对文件号，对于Oracle8是新文件。 
   相对文件号是相对于表空间的（意味着一个表空间可以具有第一，第二，第三文件等），
   并且绝对文件号在整个系统中是绝对的。 两个不同的文件可能具有相同的相对编号。

   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:
   此格式称为扩展ROWID字符格式。 为了创建ROWID字符串，您必须具有所有组件。 
   然后，可以在DBMS_ROWID包中使用以下功能：

     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范围扫描构造ROWID，我们对ROWID_CREATE函数使用以下输入：
     ROWID_TYPE:
     ~~~~~~~~~~~
     	This is 1 because we are using the extended rowid format.
        这是1，因为我们正在使用扩展的rowid格式。
     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:
        当您阅读本文时，它应该已经可用。 还可以从DBA_EXTENTS视图中找到给定的绝对文件号和损坏块的块号：
		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.
        请记住，Oracle8在ORA-1578错误中报告<RFN>，在伴随的ORA-1110错误中报告<AFN>。

     OBJECT_NUMBER:
     ~~~~~~~~~~~~~
        For a non-partitioned table, select the DATA_OBJECT_ID from
	DBA_OBJECTS for the problem table:
        对于非分区表，请从DBA_OBJECTS中为问题表选择DATA_OBJECT_ID：
          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:
        请注意，分区表的每个分区都有一个对象号。因此，从DBA_OBJECTS中选择DATA_OBJECT_ID：
       	  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".
        在您进入本文之前，损坏的块的块号应该可用。(例如：报告为ORA-1578错误，或DBVerify报告为页码)。
        对于ROWID范围扫描，我们通常希望选择损坏块之前的所有行，然后选择损坏块之后的所有行。 
        块中的第一行是零行（0），因此我们希望所有rowid都小于"Block <BL> row 0"，
        然后大于或等于"Block <BL>+1 row 0"。

   You can now create the rowid strings to use in a predicate thus:
   现在，您可以创建用于谓词的rowid字符串，从而：
     The "LOW_RID" is the lowest rowid INSIDE the corrupt block:
     "LOW_RID"是损坏块中最低的rowid：
       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:
     "HI_RID"是损坏的块之后的第一个rowid：
       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 AS SELECT或INSERT ... SELECT来获取数据，
   而无需使用以下查询形式访问损坏的块：
	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")
   (请注意，"A"在HINT和FROM子句中用作表别名，并且我们希望不超过"lo_rid"，而希望大于或等于"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:
   对于表分区，则仅需要使用FROM子句中的 PARTITION(xxx) 选项从中选择问题分区：
	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.
   注意：请注意，此过程不能用于列定义为LONG的表。
        对于带有LONG的表，您将必须使用export/import。
        导出可以使用WHERE子句选项运行。
   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 
                                              EXTENT_ID为零是段头

  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:
    如果损坏的表上有任何索引，则可以从索引中获取有关损坏的块中有哪些数据的信息。 
    这需要从表中选择索引列以获取损坏块中的行标识。 
    从上面的 SELECT dbms_rowid.rowid_create ... 语句中，我们已经知道损坏的块所覆盖的ROWID范围。 
    要提取列数据，请使用以下形式的select语句之一：
    If the columns required at NOT NULLable you can use a fast full scan:
    如果在NOT NULLable处需要的列可以使用快速完整扫描：
	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.
    对表上的所有索引使用此技术可能能够检索某些数据。 
    请参见<View:DBA_IND_COLUMNS>，其中哪些列构成了每个索引。

Example of using ROWID Range Scans in Oracle8/8i or higher
在Oracle8/8i或更高版本中使用ROWID范围扫描的示例
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  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:
  本示例仅显示针对ORA-1578错误的主要步骤（缺少索引信息）。 
  使用10231事件可以更好地处理此示例，但是我们在此处显示ROWID范围扫描方法：
	SQL> select * from scott.partitionexample;

	ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
	ORA-01110: data file 7: '(datafile path and name)'

    >>  <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 
       首先使用错误中的block number
	SQL> select dbms_rowid.rowid_create(1, 88145,7,12698,0) from dual;

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

    >> Use the block number +1 next
       接下来使用block number +1
	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..
       现在我们可以在SELECT, CTAS, INSERT AS SELECT等中使用ROWID。
	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
       例如：假设我们在"COLUMN1"上有一个索引"PARTEXAM"
    >>     "PARTITIONEXAMPLE" table, and COLUMN1 is NOT NULL then we can:
           "PARTITIONEXAMPLE"表，并且COLUMN1不为NULL，那么我们可以：
	SQL> SELECT /*+ INDEX_FFS(A PARTEXAM) */  column1
	       FROM  scott.partitionexample A
	      WHERE  rowid >= 'AAAVhRAAHAAADGaAAA'
		AND  rowid <  'AAAVhRAAHAAADGbAAA' ;
	
	COLUMN1
	----------
	        25
	...


-------------------------------------------------------------------------

REFERENCES
NOTE:1088018.1 - Master Note for Handling Oracle Database Corruption Issues
NOTE:28814.1 - Handling Oracle Block Corruptions
NOTE:33405.1 - Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231
NOTE:34371.1 - Extracting Data from a Corrupt Table using ROWID or Index Scans in Oracle7