Please try the following statement. However, If there are numerous extents matching with corrupt extent size, then your options are (1) to fill up system tablespace completely with a table and drop it later. Surgical approach might not work. (2) or rebuild the database. select count(*) from dba_free_space f2 where f2.blocks in ( select distinct f.file_id, f.block_id, f.bytes, f.blocks from dba_free_space f join v$database_block_corruption c on (c.block# between f.block_id and f.block_id + f.blocks -1 and f.file_id =c.file#) where f.file_id=1 ) and f2.file_id=1 / Cheers Riyaj Shamsudeen Principal DBA, Ora!nternals - http://www.orainternals.com - Specialists in Performance, RAC and EBS Blog: http://orainternals.wordpress.com/ Oracle ACE Director and OakTable member <http://www.oaktable.com/> Co-author of the books: Expert Oracle Practices <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL practices <http://tinyurl.com/book-expert-plsql-practices> <http://tinyurl.com/book-expert-plsql-practices> On Fri, Jul 11, 2014 at 9:15 AM, max scalf <oracle.blog3@xxxxxxxxx> wrote: > Hi Riyaj, > > Below is the out, seems like query#2 has some error > > SQL> show parameter db_block_size; > > NAME TYPE VALUE > ------------------------------------ ----------- > ------------------------------ > db_block_size integer 8192 > SQL> SELECT DISTINCT f.file_id, > 2 f.block_id, > 3 f.bytes, > 4 f.blocks > 5 FROM dba_free_space f > 6 JOIN v$database_block_corruption c ON (c.block# BETWEEN f.block_id > AND f.block_id + f.blocks -1 > AND f.file_id =c.file#) > 7 8 WHERE f.file_id=1; > > FILE_ID BLOCK_ID BYTES BLOCKS > ---------- ---------- ---------- ---------- > 1 12556 65536 8 > 1 12568 262144 32 > 1 12866 1196032 146 > 1 12714 1015808 124 > 1 14412 65536 8 > 1 12612 458752 56 > > 6 rows selected. > > SQL> SELECT count(*) > 2 FROM dba_free_space f2 > 3 WHERE f2.blocks IN > 4 (SELECT DISTINCT f.file_id, > 5 f.block_id, > 6 f.bytes, > 7 f.blocks > 8 FROM dba_free_space f > 9 JOIN v$database_block_corruption c ON (c.block# BETWEEN > f.block_id AND f.block_id + f.blocks -1 > 10 AND f.file_id =c.file#) > WHERE f.file_id=1) where f2.file_id=1; 11 > WHERE f.file_id=1) where f2.file_id=1 > * > ERROR at line 11: > ORA-00933: SQL command not properly ended > > > SQL> > > > > On Fri, Jul 11, 2014 at 10:44 AM, Riyaj Shamsudeen < > riyaj.shamsudeen@xxxxxxxxx> wrote: > >> Hi >> Can you send me the output of the following queries? I am trying to >> figure out, how many free extents these blocks are spread around? and then, >> how many free extents have the same size? If we know this, may be, we can >> allocate extents of that size exactly and hopefully reuse those blocks. If >> there are many extents, then, you may be better off, rebuild database with >> transportable tablespace option or (expdp/impdp if the database size is >> small). >> >> BTW, this method is not too different from that note you have posted, >> however, there are subtle differences. >> >> (typo possible in the queries, as I have not tested them). >> >> select distinct f.file_id, f.block_id, f.bytes, f.blocks >> from dba_free_space f join v$database_block_corruption c >> on (c.block# between f.block_id and f.block_id + f.blocks -1 >> and f.file_id =c.file#) >> where f.file_id=1 >> / >> >> select count(*) from dba_free_space f2 >> where f2.blocks in ( >> select distinct f.file_id, f.block_id, f.bytes, f.blocks >> from dba_free_space f join v$database_block_corruption c >> on (c.block# between f.block_id and f.block_id + f.blocks -1 >> and f.file_id =c.file#) >> where f.file_id=1 >> ) >> where f2.file_id=1 >> / >> show parameter db_block_size >> >> >> >> >> Cheers >> >> Riyaj Shamsudeen >> Principal DBA, >> Ora!nternals - http://www.orainternals.com - Specialists in >> Performance, RAC and EBS >> Blog: http://orainternals.wordpress.com/ >> Oracle ACE Director and OakTable member <http://www.oaktable.com/> >> >> Co-author of the books: Expert Oracle Practices >> <http://tinyurl.com/book-expert-oracle-practices/>, Pro Oracle SQL, >> <http://tinyurl.com/ahpvms8> <http://tinyurl.com/ahpvms8>Expert RAC >> Practices 12c. <http://tinyurl.com/expert-rac-12c> Expert PL/SQL >> practices <http://tinyurl.com/book-expert-plsql-practices> >> >> <http://tinyurl.com/book-expert-plsql-practices> >> >> >> >> On Fri, Jul 11, 2014 at 6:46 AM, max scalf <oracle.blog3@xxxxxxxxx> >> wrote: >> >>> Hello list, >>> >>> I might have a interesting error at hand, Please note i already have >>> opened a ticket with oracle, but not getting good enough response. We >>> started doing RMAN backup of one of our system(backup check logical >>> database...) and found that there is some corruption on the system >>> datafile. When i ran DBV i get the below errors and DBV also says "Total >>> Pages Marked Corrupt : 0". We do not know how long this corruption has >>> been in our system as we recently started doing RMAN backup on it and that >>> failed on FIRST day of our backup. >>> >>> Selecting out of v$database_block_corruption show we have about 43 >>> blocks thats are corrupted and CORRUPTION_TYPE is UNKNOWN and when i try to >>> find out which segments are affected. They are all empty blocks. >>> >>> Oracle answer was "We have no other options or workarounds other than >>> rebuilding this database, or setting the maxcorrupt clause for that >>> datafile" >>> >>> i supposed i can live with setting up maxcorrupt caluse i our backup >>> script, but what worries me the most is that this is datafile#1(system >>> datafile). >>> >>> i tried following this note "How to Format Corrupted Block Not Part of >>> Any Segment (Doc ID 336133.1)" but that dose not seem to help, and there is >>> a big DISCLAIMER that says :-The steps given in this note are not always >>> guaranteed to work. >>> >>> Also the below error on DBV are something i could not find anything on >>> oracle support or google. Oracle version is 10.2.0.5 and do note this is a >>> dictionary manage tablespace. One more wierd part about this error. When >>> i run "backup validate check logical datafile 1", in the alert log we get >>> error pointing to datafile# 16. DBV on that datafile dose not produce any >>> error(also ran backup validate check logical datafile 16 followed by select >>> on v$database_block_corruption and no errors there), this datafile 16 is >>> part of a tablespace that is also dictionary managed tablespace. This DB >>> has multiple tablespace, half of them locally managed and half dictionary >>> managed(back from 8i incarnation of this database). >>> >>> Error backing up file 16, block 12557: logical corruption >>> Error backing up file 16, block 12561: logical corruption >>> Error backing up file 16, block 12589: logical corruption >>> Error backing up file 16, block 12593: logical corruption >>> >>> >>> So my question is how do i get rid of this? Is there a way to insert >>> rows into a specific block, so that it can be reformatted ? I have also >>> attached output.txt file of DBV and select on v$database_block_corruption >>> for better readability(in case needed by someone). >>> >>> >>> $ dbv file=/oracle/SID/system_1/system.data1 >>> >>> DBVERIFY: Release 10.2.0.5.0 - Production on Fri Jul 11 08:04:18 2014 >>> >>> Copyright (c) 1982, 2007, Oracle. All rights reserved. >>> >>> DBVERIFY - Verification starting : FILE = >>> /oracle/SID/system_1/system.data1 >>> Block Checking: DBA = 67121421, Block Type = Save undo data block >>> ERROR: SAVE Undo Block Corrupted. Error Code = 50 >>> kts4subck: record (3) seq# (0), split flag (0) >>> and total pieces(0) >>> Block Checking: DBA = 67121425, Block Type = Save undo data block >>> ERROR: SAVE Undo Block Corrupted. Error Code = 50 >>> kts4subck: record (3) seq# (0), split flag (0) >>> and total pieces(0) >>> Block Checking: DBA = 67121453, Block Type = Save undo data block >>> ERROR: SAVE Undo Block Corrupted. Error Code = 50 >>> kts4subck: record (3) seq# (0), split flag (0) >>> and total pieces(0) >>> ........ >>> ........ >>> ........ ====> Same as above errors >>> ........ >>> >>> DBVERIFY - Verification complete >>> >>> Total Pages Examined : 32000 >>> Total Pages Processed (Data) : 17136 >>> Total Pages Failing (Data) : 0 >>> Total Pages Processed (Index): 13619 >>> Total Pages Failing (Index): 0 >>> Total Pages Processed (Other): 1215 >>> Total Pages Processed (Seg) : 1 >>> Total Pages Failing (Seg) : 0 >>> Total Pages Empty : 30 >>> Total Pages Marked Corrupt : 0 >>> Total Pages Influx : 0 >>> Highest block SCN : 2826666056 (458.2826666056) >>> $ >>> >>> SQL> select * from v$database_block_corruption order by 1,2,3,4 >>> 2 ; >>> >>> FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO >>> ----- ------ ------ ------------------ --------- >>> 1 12557 1 1 UNKNOWN >>> 1 12561 1 1 UNKNOWN >>> 1 12589 1 1 UNKNOWN >>> 1 12593 1 1 UNKNOWN >>> 1 12597 1 1 UNKNOWN >>> 1 12665 1 1 UNKNOWN >>> 1 12667 1 1 UNKNOWN >>> 1 12715 1 1 UNKNOWN >>> 1 12719 1 1 UNKNOWN >>> 1 12723 1 1 UNKNOWN >>> 1 12727 1 1 UNKNOWN >>> 1 12731 1 1 UNKNOWN >>> 1 12735 1 1 UNKNOWN >>> 1 12739 1 1 UNKNOWN >>> 1 12743 1 1 UNKNOWN >>> 1 12747 1 1 UNKNOWN >>> 1 12751 1 1 UNKNOWN >>> 1 12755 1 1 UNKNOWN >>> 1 12759 1 1 UNKNOWN >>> 1 12763 1 1 UNKNOWN >>> 1 12767 1 1 UNKNOWN >>> 1 12771 1 1 UNKNOWN >>> 1 12775 1 1 UNKNOWN >>> 1 12779 1 1 UNKNOWN >>> 1 12783 1 1 UNKNOWN >>> 1 12787 1 1 UNKNOWN >>> 1 12791 1 1 UNKNOWN >>> 1 12795 1 1 UNKNOWN >>> 1 12799 1 1 UNKNOWN >>> 1 12803 1 1 UNKNOWN >>> 1 12807 1 1 UNKNOWN >>> 1 12811 1 1 UNKNOWN >>> 1 12815 1 1 UNKNOWN >>> 1 12819 1 1 UNKNOWN >>> 1 12823 1 1 UNKNOWN >>> 1 12827 1 1 UNKNOWN >>> 1 12831 1 1 UNKNOWN >>> 1 12835 1 1 UNKNOWN >>> 1 12999 1 1 UNKNOWN >>> 1 13003 1 1 UNKNOWN >>> 1 13007 1 1 UNKNOWN >>> 1 13011 1 1 UNKNOWN >>> 1 14413 4 1 UNKNOWN >>> >>> 43 rows selected. >>> >>> SQL> >>> >>> SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, >>> c.file# >>> 2 , greatest(e.block_id, c.block#) s_blk# >>> 3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) e_dblk# >>> 4 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) >>> 5 - greatest(e.block_id, c.block#) + 1 blk_corrupt >>> 6 , null description >>> 7 FROM dba_extents e, v$database_block_corruption c >>> 8 WHERE e.file_id = c.file# >>> 9 AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks >>> - 1 >= c.block# >>> 10 UNION >>> 11 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, >>> c.file# >>> 12 , header_block s_blk# >>> 13 , header_block e_blk# >>> , 1 blk_corrupt >>> 14 15 , 'Segment Header' description >>> 16 FROM dba_segments s, v$database_block_corruption c >>> WHERE s.header_file = c.file# >>> AND s.header_block between c.block# and c.block# + c.blocks - 1 >>> 17 18 19 UNION >>> 20 SELECT null owner, null segment_type, null segment_name, null >>> partition_name, c.file# >>> 21 , greatest(f.block_id, c.block#) s_blk# >>> 22 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) e_blk# >>> , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) >>> 23 24 - greatest(f.block_id, c.block#) + 1 blk_corrupt >>> , 'Free Block' description >>> 25 26 FROM dba_free_space f, v$database_block_corruption c >>> 27 WHERE f.file_id = c.file# >>> AND f.block_id <= c.block# + c.blocks - 1 AND f.block_id + f.blocks - 1 >>> >= c.block# >>> 28 29 order by file#, s_blk# >>> 30 ; >>> >>> OWNER SEGMENT_TYPE SEGMENT_NAME >>> PARTITION_NAME FILE# S_BLK# E_DBLK# BLK_CORRUPT DESCRIPTION >>> -------------------- ------------------ ------------------------- >>> ------------------------- ----- ------ ---------- ----------- -------------- >>> >>> 1 12557 12557 1 Free Block >>> >>> 1 12561 12561 1 Free Block >>> >>> 1 12589 12589 1 Free Block >>> >>> 1 12593 12593 1 Free Block >>> >>> 1 12597 12597 1 Free Block >>> >>> 1 12665 12665 1 Free Block >>> >>> 1 12667 12667 1 Free Block >>> >>> 1 12715 12715 1 Free Block >>> >>> 1 12719 12719 1 Free Block >>> >>> 1 12723 12723 1 Free Block >>> >>> 1 12727 12727 1 Free Block >>> >>> 1 12731 12731 1 Free Block >>> >>> 1 12735 12735 1 Free Block >>> >>> 1 12739 12739 1 Free Block >>> >>> 1 12743 12743 1 Free Block >>> >>> 1 12747 12747 1 Free Block >>> >>> 1 12751 12751 1 Free Block >>> >>> 1 12755 12755 1 Free Block >>> >>> 1 12759 12759 1 Free Block >>> >>> 1 12763 12763 1 Free Block >>> >>> 1 12767 12767 1 Free Block >>> >>> 1 12771 12771 1 Free Block >>> >>> 1 12775 12775 1 Free Block >>> >>> 1 12779 12779 1 Free Block >>> >>> 1 12783 12783 1 Free Block >>> >>> 1 12787 12787 1 Free Block >>> >>> 1 12791 12791 1 Free Block >>> >>> 1 12795 12795 1 Free Block >>> >>> 1 12799 12799 1 Free Block >>> >>> 1 12803 12803 1 Free Block >>> >>> 1 12807 12807 1 Free Block >>> >>> 1 12811 12811 1 Free Block >>> >>> 1 12815 12815 1 Free Block >>> >>> 1 12819 12819 1 Free Block >>> >>> 1 12823 12823 1 Free Block >>> >>> 1 12827 12827 1 Free Block >>> >>> 1 12831 12831 1 Free Block >>> >>> 1 12835 12835 1 Free Block >>> >>> 1 12999 12999 1 Free Block >>> >>> 1 13003 13003 1 Free Block >>> >>> 1 13007 13007 1 Free Block >>> >>> 1 13011 13011 1 Free Block >>> >>> 1 14413 14416 4 Free Block >>> >>> 43 rows selected. >>> >>> SQL> >>> >>> >>> >> >