rebuild the DB is the really really last option we want as this is a 50TB database. Also i tried tried filling up system TS completely with a table using the note i mentioned, but it seems like its not using all the space in that datafile. That datafile is about 250MB in size and has about 35MB free, i fill it up with insert statement(big loop) but at the end i do get not enough space on SYSTEM tablespace, but when i check how much is free, that datafile still shows it has about 8-10MB free. output from the query. SQL> select count(*) from dba_free_space f2 2 where f2.blocks in ( 3 select distinct f.file_id, f.block_id, f.bytes, f.blocks 4 from dba_free_space f join v$database_block_corruption c 5 on (c.block# between f.block_id and f.block_id + f.blocks -1 6 and f.file_id =c.file#) 7 where f.file_id=1 8 ) 9 and f2.file_id=1 10 / select distinct f.file_id, f.block_id, f.bytes, f.blocks * ERROR at line 3: ORA-00913: too many values SQL> On Fri, Jul 11, 2014 at 11:25 AM, Riyaj Shamsudeen < riyaj.shamsudeen@xxxxxxxxx> wrote: > 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> >>>> >>>> >>>> >>> >> >