Tags

,

ORA-10452 error occurs if you try to do block media recovery.

When  database block corruption  detected it throws ORA-1578,  if the database version 11.2 > there is feature that makes server to do automatic recover from physical standby(with real time apply). Otherwise we can do manual recover using RMAN Recover …. Block command.

**** NOTE ***
Automatic block repair is attempted if the following database initialization parameters are configured on the standby database as described:

— The LOG_ARCHIVE_CONFIG parameter is configured with a DG_CONFIG list and a LOG_ARCHIVE_DEST_n parameter is configured for the primary database with the DB_UNIQUE_NAME attribute.
— The FAL_SERVER parameter is configured and its value contains an Oracle Net service name for the primary database.

During manual block recovery,  RMAN failed with ORA-10452. I found later there is another recovery going on buts it’s at datafile level.

 

screen_1

 

Select query on the object fails with ORA-01578:

Linux2

 

So we now know there is a corrupted bock, we use db tools like dbv, rman validate, dbms_repair to identify the corruption:

Linux3

 

We can see the corruption report using dbv utility, now it’s time to repair the corruption. Since it’s a physical corruption, we have to restore and repair. In case of indexes, we can drop and recreate but we can’t do this in PROD unless no options left.

Repair the corruption, this is where RMAN failed with  ORA-10452:

screen_2

 

Complete the recovery in other sessions or flashback operations and, do the block recovery again:

Linux5

 

 

We can query v$database_block_corruption to check the list of blocks corrupted and use RMAN to recover. Initially it checks for the latest blocks in flashback logs if it doesn’t find one then it uses backup sets.