Re: ERROR: SAVE Undo Block Corrupted. Error Code = 50
Date: Fri, 11 Jul 2014 08:44:43 -0700
Message-ID: <CAA2Dszyma1gncxv9hQtRse1qOwt5kn7BRjswhF4hiwyxg=ikFA_at_mail.gmail.com>
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_at_gmail.com> 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>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jul 11 2014 - 17:44:43 CEST