Recovery of beaten blocks in Oracle - LOB segment

At one point in the alert log of a very large database, messages of the following content began to appear:

Corrupt block relative dba: 0x0724c078 (file 28, block 2408568)
Fractured block found during backing up datafile
Reread of blocknum = 2408568, file = E: \ ORACLE \ ORADATA \ XXX \ XXX_BLOB16.DBF. found same corrupt data

The situation was complicated by the fact that there were no backups at hand.
Next, follow the instructions to get out of this situation.


RMAN rested on this block and didn’t want to backup the database at all.
A detailed debriefing has begun, and figuring out what this block relates to:

SELECT owner, segment_name, segment_type 
FROM dba_extents 
WHERE file_id = 28 
AND 2408568 BETWEEN block_id AND block_id + blocks - 1;
OWNER
----------------------------
SEGMENT_NAME
----------------------------
SEGMENT_TYPE
------------------
DOC_USER
SYS_LOB0000075021C00003$$
LOBSEGMENT

Then it was found out which table the given LOB segment belonged to:

SELECT table_name, column_name 
		  FROM dba_lobs 
		 WHERE owner='DOC_USER'
		   AND segment_name='SYS_LOB0000075021C00003$$';
TABLE_NAME
-------------------
COLUMN_NAME
-------------------
DOC_LARGE_PIC
BINARY_DATA

DBMS_REPAIR - could not clarify the situation, due to restrictions on working with LOB fields.

On the open spaces of the network, a solution was found - its essence was as follows:

• 1. Alternately sort through the entries from the table.
• 2. If you hit a record related to a broken block, pull out its ROWID.

set serverout on
		exec dbms_output.enable(100000);
		declare
		 error_1578 exception;
		 pragma exception_init(error_1578,-1578);
		 n number;
		 cnt number:=0;
		 badcnt number:=0;
		begin
		  for cursor_lob in
		        (select rowid r, BINARY_DATA L from DOC_USER.DOC_LARGE_PIC)
		  loop
		    begin
		      n:=dbms_lob.instr(cursor_lob.L,hextoraw('AA25889911'),1,999999) ;
		    exception
		     when error_1578 then
		       dbms_output.put_line('Got ORA-1578 reading LOB at '||cursor_lob.R);
		       badcnt:=badcnt+1;
		    end;
		    cnt:=cnt+1;
		  end loop;
		  dbms_output.put_line('Scanned '||cnt||' rows - saw '||badcnt||' errors');
		end;
		/

The script successfully returned two entries:

Got ORA-1578 reading LOB at AAASUNAAQAAPf7hAAY
Got ORA-1578 reading LOB at AAASUNAAQAAPf7hAAp

Using a simple query, PRIMARY KEY data records were received and the records were successfully wiped.

It would seem that here it is a solution to the problem - but RMAN stubbornly did not want to reserve the database resting on these blocks.
The query in V $ DATABASE_BLOCK_CORRUPTION and RMAN VALIDATE DATAFILE confirmed that the blocks remained in the same state.

I didn’t really want to create a table and hammer it to the eyeballs of the entire table space, so it was decided to use ALTER TABLE XXX SHRINK SPACE.

ALTER TABLE DOC_USER.DOC_LARGE_PIC ENABLE ROW MOVEMENT;
ALTER TABLE DOC_USER.DOC_LARGE_PIC SHRINK SPACE CASCADE;

Then we start checking the problem file with RMAN:

RMAN VALIDATE DATAFILE 28;

After this operation, the V $ DATABASE_BLOCK_CORRUPTION view turned out to be crystal clear.
Further, the database was successfully reserved by RMAN, and the missing records were pulled out of the replica.

UPDATE

This problem arose after the disks on the server began to crumble.
A copy of the section was removed with a third-party utility and deployed to a new freshly assembled array.
All operations were carried out on the basis of a weight of 1.5 TB.
The weight of the table is 70 GB.
Version of Oracle 11g R2 - I think this method is also applicable for 10g.
ALTER TABLE ... SHRINK SPACE CASCADE has a number of limitations, I recommend that you familiarize yourself with the documentation before using it.