ORA-01578,ORA-01110 :Recovery of corrupted datafile from RMAN backup.

Issue :

Oracle database datafile got corrupt with following error from alert log.

Fri Jan 15 07:45:36 2016

Corrupt Block Found

         TSN = 9, TSNAME = TABLES_LG

         RFN = 34, BLK = 993463, RDBA = 143599799

         OBJN = 35806, OBJD = 38520, OBJECT = FASTFACT_ATTR, SUBOBJECT =

         SEGMENT OWNER = SAM, SEGMENT TYPE = Table Segment

Replication error:

Due to the same there is a post queue has stopped on shareplex , below is the error reported in  Shareplex alert:-

"01/15/16 00:27 Notice: Table "NORM"."EVENTLOG" is being disabled for INSERT [sp_opst (for o.WE7P-o.WE7C queue db6)/6593]

01/15/16 00:27 Process exited sp_opst  (for o.WE7P-o.WE7C queue sam_7p_7c) [pid = 19873] - exit(1)

01/15/16 00:27 Error: Unexpected Oracle error: ORA-01578: ORACLE data block corrupted (file # 34, block # 993463).ORA-01110: data file 34: '/u08/oradata/WE7C/TABLE128M/tables_lg_05.dbf'. (object name: "SAM"."FASTFACT_ATTR") [sp_opst (for o.WE7P-o.WE7C queue sam_7p_7c)/19873]

01/15/16 00:27 Notice: ORA-01578: ORACLE data block corrupted (file # 34, block # 993463).ORA-01110: data file 34: '/u08/oradata/WE7C/TABLE128M/tables_lg_05.dbf'. [sp_opst(osp)/19873]"

Fix :

·        Find out corrupted datafile in database by using below query.

Select * from v$database_block_corruption;  ----it will give corrupted datafile#.

Find out datafile name from file# in dba_data_files data dictionary view.

·       Put corrupted datafile in offline mode.

alter database datafile <datafile-name> offline;

·       Rename corrupted datafile.

·        Restore & recover corrupted datafile from RMAN using below command.

RUN

{

RESTORE datafile 34; ----  34 is datafile#.

RECOVER datafile 34;

}

·      While recovering & restoring it would restore lot of archives(if needed) to apply. Make sure enough space available in archive log location.

·      Once datafile successfully restored & recovered from backup, bring datafile into online.

alter database datafile <datafile-name> online;

Below are the sample screen shots:

Before :

After: