Sunday, June 12, 2016

Recovering Standby Database from corrupt data file


Found the following errors in alert logfile of standby database
ORA-10567: Redo is inconsistent with data block (file# 11, block# 83962, file offset is 687816704 bytes) 
ORA-10564: tablespace SYSAUX 
ORA-01110: data file 11: '/p02/prod/proddata/sysaux01.dbf' 
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 13366 

We realized that file no 11 of sysaux tablespace was corrupted

So we checked the health status of the object on Primary for the same by querying and found that they were healthy

SELECT owner, segment_name, segment_type, tablespace_name 
FROM dba_extents 
WHERE file_id = 11 
and 83962 between block_id AND block_id + blocks - 1; 

SQL>select owner,object_name,object_type,subobject_name,status,temporary, object_id,data_object_id from dba_objects where object_id = 13366; 
Primary database:
alter tablespace sysaux begin backup;
Standby database:
Verify the archive gap on standby database with following query
SELECT SEQUENCE#, applied FROM V$ARCHIVED_LOG order BY sequence# desc;
Shut immediate 
  Primary database:
scp /p02/prod/proddata/sysaux01.dbf  prod@scdebsdbrpt01:/p02/prod/proddata/sysaux01.dbf 
alter tablespace sysaux end backup;

Standby database :

Startup nomount pfile='$ORACLE_HOME/dbs/initprod.ora';
Alter database mount standby database;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

SELECT database_role, open_mode FROM v$database;

Following Queries can be used to find the archive gap between Primary and standby database:

SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES'

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc

SELECT SEQUENCE#,applied FROM V$ARCHIVED_LOG order by SEQUENCE# desc



Check for the MRP process status which is responsible for applying the logs with below query :

select process "MRP PROCESS Status" ,status,sequence#
  from v$managed_standby where PROCESS like '%MRP%';

Check for Log seq# applied on PRIMARY vs Standby Database

select  almax "Last Seq Received from PROD", lhmax "Last Seq Applied on PRODRPT"
from (select thread# thrd, max(sequence#) almax
      from v$archived_log
      where resetlogs_change#=(select resetlogs_change# from v$database)
      group by thread#) al,
     (select thread# thrd, max(sequence#) lhmax
      from v$log_history
      where first_time=(select max(first_time) from v$log_history)
      group by thread#) lh
where al.thrd = lh.thrd;



No comments: