How To Format Corrupted Block Not Part Of Any Segment

How To Format Corrupted Block Not Part Of Any Segment ORA-19566
Note: 336133.1

There is a more simple solution for the problem
1.dbv backup datafile validate
2.determine whether the block doesn't depend on any object
select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupted block number> between block_id
and block_id + blocks -1;
3.rman blockrecover if you have backup
4.SET MAXCORRUPT skip it
5.move segment to new tablespace and drop original one.

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2
This problem can occur on 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.

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:41ORA-19566:
exceeded limit of 0 corrupt blocks for file E:xxxx est.ORA.
Corrupt block is present in file E:xxxx est.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 est.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 = <Absolute file number>
and <corrupted block number> 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 <tablespace name having the corrupt block> ;

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:

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

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

For example to allocate space from E:xxxx est.ORA:

SQL> alter table scott.s
allocate extent (DATAFILE 'E:xxxx est.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 = <Absolute file number>
and <corrupt block number> 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 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;

Keywords
DBVERIFY; FORMAT;

Reference
http://www.itpub.net/viewthread.php?tid=1021812&extra=&page=3
http://space.itpub.net/7199859/viewspace-294664