Recover broken blocks in Oracle - LOB segment

At one fine moment in a very large database alert-log, the following messages 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 on hand.
Then follow the instructions to resolve this situation.


RMAN rested against this block and did not want to back up the database.
A detailed debriefing has begun, and the clarification to which this unit belongs:

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 

It was further clarified to which table the given LOB segment belonged:

 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.

In the vastness of the network, a solution was found - its essence was as follows:

• 1. Alternately sorting records from the table.
• 2. When getting into 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 

With the help of a simple query, PRIMARY KEY data records were obtained and records were successfully wiped.

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

I didn’t really want to create a table and fill it with 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 run the scan of the RMAN problem file:

 RMAN VALIDATE DATAFILE 28; 

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

UPDATE

This problem arose after the disks on the server began to crumble.
A copy of the section was removed by 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.
Weight table 70 GB.
Version of Oracle 11g R2 - I think this method is applicable for 10g.
ALTER TABLE ... SHRINK SPACE CASCADE has a number of limitations. I recommend that you first read the documentation before using it.