Re: ERROR: SAVE Undo Block Corrupted. Error Code = 50
Date: Fri, 11 Jul 2014 11:33:02 -0500
Message-ID: <CAKoJ+qCrn9EdFjTRnTC_FR9bS640jA42=kAdi65G=zMNFoRnvQ_at_mail.gmail.com>
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_at_gmail.com> 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_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-lReceived on Fri Jul 11 2014 - 18:33:02 CEST