DATAGUARD MONITORING

DATAGUARD MONITORING
====================

Check DB role(PRIMARY/STANDBY):
================================

SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;

Monitor standby background process:
===================================

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;

View dataguard message or errors:
=================================

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

Last log applied/Received in standby
====================================

select ‘Last Log applied : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied=’YES’)
union
select ‘Last Log received : ‘ Logs, to_char(next_time,’DD-MON-YY:HH24:MI:SS’) Time
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

 

Get standby redo log info:
==========================

set lines 100 pages 999
col member format a70
select st.group#
, st.sequence#
, ceil(st.bytes / 1048576) mb
, lf.member
from v$standby_log st
, v$logfile lf
where st.group# = lf.group#
/

 

 

Monitor recovery progress in standby db:
========================================

select to_char(START_TIME,’DD-MON-YYYY HH24:MI:SS’) “Recovery Start Time”,to_char(item)||’ = ‘||to_char(sofar)||’ ‘||to_char(units) “Progress”
from v$recovery_progress where start_time=(select max(start_time) from v$recovery_progress);

 

Stop/start MRP process in standby:
===================================

 

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;

alter database recover managed standby database using current logfile disconnect from session;