Re: ERROR: SAVE Undo Block Corrupted. Error Code = 50

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 11 Jul 2014 09:25:33 -0700
Message-ID: <CAA2Dszy1sXYtd6q=k4K7j=RPsC3JrJjjzks270hsanvCevPVRQ_at_mail.gmail.com>

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_at_gmail.com> 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_at_gmail.com> 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_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-l

Received on Fri Jul 11 2014 - 18:25:33 CEST