Techdata VINA 지식 공유

결과는 corruption 난 block의 data까지 예전 export file 에서 살려냈습니다.

방법은 간단...

 corrupt난 A table 의 index에는 pk값이 남아 있습니다. 즉 index까지는 access할수 있습니다. pk컬럼값을 아는 이상 예전 exp file에서 임시 table을 다른 유저에서 구축한 다음.. pk 값으로 임시 table에서 해당 row값을 꺼내면 됩니다

MOS 참고

Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle8 and higher (문서 ID 61685.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
	...