Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

How to reformat corrupt blocks which are not part of any segment?

Posted by Riyaj Shamsudeen on July 11, 2014

There was a question in . Problem is that there were many corrupt blocks in the system tablespace not belonging to any segment. Both DBV and rman throws errors, backup is filling the v$database_block_corruption with numerous rows. OP asked to see if these blocks can be reinitialized. Also, note 336133.1 is relevant to this issue on hand.

$ 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)
…
REM Many such corruptions.
select * from v$database_block_corruption order by 1,2,3,4  ;

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

From the output above, blocks belonging to saved undo data blocks, which probably have undo records referring to other data files also. Since these blocks do not belong to any segment (note 336133.1 has a SQL statement to verify that), we should be able to force the allocation of these blocks to a table, load that table with numerous rows, which, in theory, should re-initialize the blocks. That should eliminate rman and dbv errors.

First, let me take a selfie

No selfie today. So, let us just create a table in the tablespace (SYSTEM in this blog entry) and allocate extents to the table, easy to do with a PL/SQL block. We query dba_free_space, order the rows by bytes, and allocate extent with size matching to bytes from dba_free_space, in each iteration of the loop. That will force the space management algorithm to allocate extents of exact free extent size, consuming all the free space from the tablespace.

REM Verify that you don't have table with a name TEST1. 
drop table test1; 
create table test1 (n number, v varchar2(2048))  tablespace system;

REM You may have to disable auto extend. Check all the files for that tablespace.
select file_id, autoextensible from dba_data_files where tablespace_name='SYSTEM';
REM If there any file with auto extend on, switch it off.
alter database datafile  auto extend off ;
REM
select count(*) from dba_Free_space where tablespace_name='SYSTEM';
set serveroutput on size 100000
REM following PL/SQL block allocate space in the tablespace.
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;
/
alter table test1 allocate extent ( size 99426304)
alter table test1 allocate extent ( size 71548928)
alter table test1 allocate extent ( size 38182912)
...

Now, let’s verify that all corrupt blocks are associated to an extent and that that table gobbled up all free space from the table pace. No free space should be left in the dba_free_space for that tablespace.

REM In our case, corruption in file_id=1

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
/
no rows selected
select * from dba_free_space where tablespace_name='SYSTEM' order by bytes
/
no rows selected.

Fill it up!

We can now fill the test1 table with rows and that should initialize the blocks.

alter table test1 nologging;

-- fill up the table until there are space errors.
declare
  begin
     while true
      loop
        insert /*+ append */ into test1
        select n, lpad(n, 1900, 'x') from  ( select level n from dual connect by level <=100000);
        commit;
      end loop;
end;
/

Finally, validate the data file.

rman target / 
rman> backup validate check logical datafile 1;
-- succeeded
drop table test1;

Further optimizations

It is also worth noting that you could allocate the extents in a specific datafile, as an optimized step if there are numerous data files in that tablespace. Also, If you have more then one datafile in that tablespace, then you have to alter the table such that it will not allocate more extents. So, you may have to do minor script changes so that syntax uses this format:

alter table test1 allocate extent ( size  datafile '');

Also alter the table to avoid further allocations.

select count(*) extcnt from dba_extents where segment_name='TEST11'
alter table test1 storage (maxextents  ) ;

In Summary, we used basic tools to resolve a corruption.

Update 1: Corrected the link for oracle-l. Also, added notes about allocating extent in a file.

One Response to “How to reformat corrupt blocks which are not part of any segment?”

  1. jkstill said

    Interesting post – I ran into nearly the same problem a few weeks ago.
    A database had been restored to another server, but nologging operations that occurred during the backup had rendered 122 blocks corrupt in a few tables.
    The names of the table suggested the tables could easily be recreated, so we verified with the developers and dropped the tables.
    Following that the datafile for the tablespace was shrunk as small as possible.
    That eliminated only a few corrupt blocks.
    The final step was to create a table (with pctfree 99) that consumed the rest of the blocks in the tablespace, and then drop the table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 219 other followers

%d bloggers like this: