Steps to recover data file block corruption using data file from standby

===================================================

Scenario :

========

Block corruption occurs in primary datafile , we dont have recent  archivelogs and  backups

Error :

=====

We receive the below error in alert log stating the file 52 block 192655 is corrupted

ORA-01578: ORACLE data block corrupted (file # 52, block # 192655)

Reread of blocknum=192655, file=xx /xbol_data5.dbf. found same corrupt data

Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data

Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data

Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data

Reread of blocknum=192655, file=xx/xbol_data5.dbf. found same corrupt data

Reading datafile 'xx/xbol_data5.dbf' for corruption at rdba: 0x0d02f08f (file 52, block 192655)

Reread (file 52, block 192655) found same corrupt data (no logical check)

Solution :

========

Please follow the below steps as per given order to recover the datafile.

1. PRIMARY SIDE

Step 1 :   switch logfile in primary node

alter system switch logfile;

Step 2:

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 3 :

RMAN > validate check logical database;

SQL>      select * from v$database_block_corruption;

Step 4 :

select file_id,file_name,tablespace_name from dba_data_files where file_id=57;

Step 5 :

select max(sequence#) from v$archived_log;

2. ON STANDBY DATABASE

Step 1 :  

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 2:

select max(sequence#) from v$archived_log where applied='YES';

3. ON PRIMARY DATABASE

Step 1 :  

shu immediate;

Step 2:

mv xx/a_media07.dbf xx/a_media07.dbf_06Octbkp

4. ON STANDBY DATABASE

Step 1 :  

backup as copy datafile 57 format 'xx/a_media07.dbf';

Step 2:

scp xx/ a_media07.dbf oraprod@144.11.7.15:xx/a_media07.dbf

5. ON PRIMARY DATABASE

Step 1 :  

startup mount

Step 2:

catalog start with 'xx/a_media07.dbf';

Step 3 :

restore datafile 57;

Step 4 :

recover datafile 57;

Step 5 :

alter database open;

Step 6 :

backup validate check logical database;

           OR

validate check logical datafile 57;

Step 7 :

select * from v$database_block_corruption;

Step 8 :

select max(sequence#) from v$archived_log;

6. ON PRIMARY DATABASE

Step 1 :

select status,instance_name,database_role,open_mode from v$database,v$instance;

Step 2 :

select max(sequence#) from v$archived_log where applied='YES';