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

From: Riyaj Shamsudeen <riyaj.shamsudeen_at_gmail.com>
Date: Fri, 11 Jul 2014 13:23:58 -0700
Message-ID: <CAA2DszzwFLqpMy-akFOXt5Y=MUeVcxo+HcvjcAQH_ipKpTobsg_at_mail.gmail.com>

After back and forth email conversation with max and I, we were able to re-initialize the blocks, resolving the corruption issue. Here is a small writeup about this issue and scripts to resolve it, hopefully, this blog entry will be useful for someone in the future:

http://orainternals.wordpress.com/2014/07/11/how-to-reformat-corrupt-blocks-which-are-not-part-of-any-segment/

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 10:49 AM, Riyaj Shamsudeen < riyaj.shamsudeen_at_gmail.com> wrote:

> Database has many data files for system tablespace. I need to adjust the
> script a bit.
>
> Let's try the following script:
>
> drop table test1;
> create table test1 (n number, v varchar2(2048)) tablespace system;
>
> select * from dba_free_space where tablespace_name='SYSTEM' order by bytes;
>
> set serveroutput on size 100000
> declare
> begin
> for c1 in (
> select bytes from dba_free_space
> where tablespace='SYSTEM'
> order by bytes desc )
> loop
> dbms_output.put_line ('alter table test1 allocate extent ( size '||
> c1.bytes ||')' );
> execute immediate 'alter table test1 allocate extent ( size '||
> c1.bytes ||')';
> end loop;
> end;
> /
>
> 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 * from dba_free_space where tablespace_name='SYSTEM' order by bytes
> /
>
> 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>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 11 2014 - 22:23:58 CEST