One thought on “通过ROWID找回坏块数据”

1. 取消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'
   

   回复

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

   回复