How to Format Corrupted Block Not Part of Any Segment [ID 336133.1]

---

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 11.2.0.1.0 - Release: 8.1.7 to 11.2
Information in this document applies to any platform.

Symptoms

1. Rman backup fails with ORA-19566 error and the block reported corrupt does not belong to any object
2. Dbverify shows the block as corrupted
3. Corrupted block does not belong to any object

Cause

Corrupted block will still be reported by RMAN and DBV until it is reused and reformatted.

Solution

A possible way to fix the problem is provided below. Note that it is not guaranteed to work, but it has been known to resolve the problem in several cases.Also if there are many blocks reported corrupt in a
particular datafile  pass the highest block number reported corrupt for  that datafile ,when prompted for blocknumber in Step 5

 

Step 1 - Identify corrupt datafile

Check the ORA-19566 message to identify the corrupt datafile.

Example:

RMAN-03009: failure of backup command on nm4501 channel at 04/29/2005 09:44:41
ORA-19566: exceeded limit of 0 corrupt blocks for file E:\xxxx\test.ORA.

Corrupt block is present in file E:\xxxx\test.ORA.

Step 2 Run DBV on affected datafile and check for corrupt block

Run dbverify on the datafile which reports corrupt block.

Sample Output:

    DBVERIFY: Release 9.2.0.3.0 - Production on Thu Aug 25 11:15:54 2005
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    DBVERIFY - Verification starting : FILE = E:\xxxx\test.ORA
    Page 48740 is marked corrupt    ***
    Corrupt block relative dba: 0x01c0be64 (file 7, block 48740)
    Bad check value found during dbv:
     Data in bad block -
     type: 0 format: 2 rdba: 0x0000be64
     last change scn: 0x0000.00000000 seq: 0x1 flg: 0x05
     consistency value in tail: 0x00000001
     check value in block header: 0xb964, computed block checksum: 0x2a5a
     spare1: 0x0, spare2: 0x0, spare3: 0x0
    ***
    DBVERIFY - Verification complete
    Total Pages Examined         : 64000
    Total Pages Processed (Data) : 0
    Total Pages Failing   (Data) : 0
    Total Pages Processed (Index): 1751
    Total Pages Failing   (Index): 0
    Total Pages Processed (Other): 45
    Total Pages Processed (Seg)  : 0
    Total Pages Failing   (Seg)  : 0
    Total Pages Empty            : 62203
    Total Pages Marked Corrupt   : 1

Note that Block 48740 is reported as corrupt in datafile 7.

Step 3 - Check whether block is part of any object

Query dba_extents and  cross check the block doesnot belong to any object. 

SQL> select segment_name, segment_type, owner
       from dba_extents
      where file_id =
        and  between block_id
            and block_id + blocks -1;

If it doesn't belong to an object, double check if it does exists in dba_free_space
to check if the block belongs to file space usage bitmap.

SQL> Select * from dba_free_space where file_id=
     and between block_id and block_id + blocks -1;

Step 4 - Create a dummy table as user other than SYS and SYSTEM

SQL> connect scott/password

Create a dummy table in the tablespace containing datafile which has the corrupt block - and use nologging option to prevent redo records from being generated: 

SQL> create table s (
       n number,
       c varchar2(4000)
     ) nologging tablespace  ; 

Different storage parameters can be used to suit the specific environment.

Verify that the table is created in the correct tablespace by querying user_segments:
    

SQL> select segment_name,tablespace_name from user_segments
      where segment_name='S' ;

Step 5 - Create Trigger On dummy table which throws exception once the corrupted block is reused

Connect as sys and create the following trigger:

Please note when prompted for file number enter the relative file no(rfile# value from v$datafile)

CREATE OR REPLACE TRIGGER corrupt_trigger 
  AFTER INSERT ON scott.s 
  REFERENCING OLD AS p_old NEW AS new_p 
  FOR EACH ROW 
DECLARE 
  corrupt EXCEPTION; 
BEGIN 
  IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
 and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN 
     RAISE corrupt; 
  END IF; 
EXCEPTION 
  WHEN corrupt THEN 
     RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted'); 
END; 
/

When prompted for the block number, provide the block reported corrupt as input.
When prompted for the file number enter the relative fileno (rfile# value from v$datafile) for corrupt datafile.

Step 6- Allocate space to the table from the affected datafile .

First find the extent size by querying  dba_free_space 

SQL> Select BYTES from dba_free_space where file_id= and between block_id and block_id + blocks -1;

BYTES
---------------- ---------- ---------- ---------- ---------- ------------
 65536

If its 64 K use

For example to allocate space from E:\xxxx\test.ORA:

SQL> alter table scott.s
allocate extent (DATAFILE 'E:\xxxx\test.ORA' SIZE 64K);

If its 1M use

SQL> Select BYTES from dba_free_space where file_id= and between block_id and block_id + blocks -1;


BYTES
---------------- ---------- ---------- ---------- ---------- ------------
1048576

 

For example to allocate space from E:\xxxx\test.ORA: 

SQL> alter table scott.s
     allocate extent (DATAFILE 'E:\xxxx\test.ORA' SIZE 1M);

Keep allocating till the corrupted block is part of scott.s - check this with the following query:

SQL> select segment_name, segment_type, owner
       from dba_extents
      where file_id =
        and  between block_id
            and block_id + blocks -1 ;

Note: It is advisable to ensure that AUTOEXTEND is OFF for the datafile, to prevent it from growing

Step 7 -  Insert data into dummy table To format the block

Sample code (depending on the size of the tablespace it may vary):

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;

 Or

BEGIN
  FOR i IN 1..1000000000 LOOP
    INSERT INTO scott.s VALUES(i,'x');
  END LOOP;
END;
/

 Or use the below code which includes 2 loops:

Begin
  FOR i IN 1..1000000000 loop
    for j IN 1..1000 loop
      Insert into scott.s VALUES(i,'x');
    end loop;
    commit;
  END LOOP;
END; 

The trigger will be fired for every row inserted into the table and an exception with ORA-20000 will be produced as soon as it inserts the first row into the corrupt block.

Step 8 - Verify for any corruption in datafile by running DBV and Rman backup

 Run dbverify on the corrupt datafile.  It will not show the block as corrupted.

 RMAN backup will not report any error on this block.

Step 9 - Drop the dummy table created in step 4

 

SQL> DROP TABLE scott.s;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26634508/viewspace-721022/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26634508/viewspace-721022/