DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, January 22, 2016


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';


No comments: