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
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;
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:
Post a Comment