Friday, August 30, 2013

Database -- How to correct an unused corrupt block.

Normally, corrutions on unsued blocks can be ignored, as Oracle will create a new block image should the block need to be used.. On the other hand; if you use rman to backup the database; rman backups can fail with ORA-19566 error..
So, if you want to format this block anyways; here is the procedure..

The logic behind this procedure is simply --> identify the block, create a table, fill it with data till you reach the corrupted block..

I suppose that you already identified the corrupted block ; so I will not write about that part..


Create a table which will be used to reformat the corrupt unused block.

create table s (
n number,
c varchar2(4000)
) nologging tablespace <tablespace name having the corrupt block> ;


Create a trigger to report/warn when the corrupt block will be reformatted.

CREATE OR REPLACE TRIGGER corrupt_trigger 
AFTER INSERT ON scott.s
REFERENCING OLD AS p_old NEW AS new_p
FOR EACH ROW
DECLARE
corrupt EXCEPTION;
BEGIN
IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
RAISE corrupt;
END IF;
EXCEPTION
WHEN corrupt THEN
RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
END;
/

Use following loop to extent the table, we just created.. You should run this loop till the unused block will belong to the scott.s table..

BEGIN
for i in 1..1000000 loop
EXECUTE IMMEDIATE 'alter table scott.s allocate extent (DATAFILE '||'''the full path and dbf name which the corrupt block belongs to''' ||'SIZE 8K) ';
end loop;
end ;
/

You can check, whether the corrupt block belongs to our table with the following  query;

select segment_name, segment_type, owner
from dba_extents
where file_id = <Absolute file number>
and <corrupt block number> between block_id
and block_id + blocks -1 ;

After the block will belong to scott.sh table; insert data into it with the floowing loop.. 
This operation will actually reformat the block..

BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;

Once the block will be refomatted , check the block corruption again...

For Db version <=10gr2
Rman> Backup validate check logical datafile <fileno>,<fileno> ;
For Db version >= 11gr1
Rman> Backup validate check logical datafile <fileno> ;
Or
Rman> validate datafile <fileno> block <blockno reported corrupt>, <blockno reported corrupt> ;
-->
SQL>Select * from v$database_block_corruption ;

No comments :

Post a Comment