Normally, corruptions 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 simple --> identify the block, create a table, fill it with data till you reach the corrupted block..
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.
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;
/
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 ;
BEGIN
FOR i IN 1..1000000000 LOOP
INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;
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 ;
So, if you want to format this block anyways; here is the procedure..
The logic behind this procedure is simple --> 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 ;
/
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;
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..
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...
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
If you will ask a question, please don't comment here..
For your questions, please create an issue into my forum.
Forum Link: http://ermanarslan.blogspot.com.tr/p/forum.html
Register and create an issue in the related category.
I will support you from there.