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:48:53 -0700
That's what happens If I don't test the code :(.
Anyway, you will have to fill the tablespace completely, that means that
you have to find the biggest extent from dba_free_space, fill that extent
etc, using allocate extent syntax, iterate until every extent is filled or
that all corrupt blocks are part of an extent. First query should return
zero rows when that happens. Also, make sure that auto extend is disabled
for file_id=1. Let me see if I can write a PL/SQL block to do this
allocation.
After filling it up, then you would fill up the table, which is easy part.
select count(*) from dba_free_space f2
where f2.blocks in (
select distinct 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:33 AM, max scalf <oracle.blog3@xxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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@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:
