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

  • From: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
  • To: max scalf <oracle.blog3@xxxxxxxxx>
  • Date: Fri, 11 Jul 2014 09:25:33 -0700

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

Other related posts: