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