Re: ERROR: SAVE Undo Block Corrupted. Error Code = 50
- From: max scalf <oracle.blog3@xxxxxxxxx>
- To: Riyaj Shamsudeen <riyaj.shamsudeen@xxxxxxxxx>
- Date: Sat, 12 Jul 2014 10:07:08 -0500
Riyaj,
Thank you for your help again. I had a question with regards to the
procedure we used to re-initialize those empty blocks. What we did was
filled up the entire tablespace(SYSTEM in this case), but i was wondering
if we could have gotten away with just filling up the affect datafile by
allocating extents out of that file only by using alter table test1
allocate extent (datafile <datafile_name> size XX)?
On Fri, Jul 11, 2014 at 3:23 PM, Riyaj Shamsudeen <
riyaj.shamsudeen@xxxxxxxxx> wrote:
> 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@xxxxxxxxx> 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>
>>
>>
>>
>
Other related posts:
