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