DATA GUARD Handy:

************************************************************************************************************************************************************
primay :   SELECT MESSAGE FROM V$DATAGUARD_STATUS;


SELECT to_char(CURRENT_SCN) FROM V$DATABASE;

SQL> select name, open_mode, database_role from v$database;

select max(sequence#) from v$log_history;

Check redo received and applied on standby:
==========================================

SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG;
SELECT SEQUENCE#, APPLIED ,COMPLETION_TIME ,STAMP FROM V$ARCHIVED_LOG where COMPLETION_TIME like '%06-JUL-18%';

SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG  WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG
GROUP BY THREAD#) ORDER BY 1;

select process,status,sequence# from v$managed_standby;

Identify the missing archive log file.

 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;


 SQL> select thread#, max (sequence#) from v$archived_log where APPLIED='YES' group by thread#;
 THREAD# MAX(SEQUENCE#)

———- ————–

         1             22

         2             14

         3             12
 ************************************************************************************************************************************************************

 Crosscheck this :
   
 
  Check redo received and applied on standby.
 
  SELECT ARCH.THREAD#  , ARCH.SEQUENCE#  , APPL.SEQUENCE#  , (ARCH.SEQUENCE# – APPL.SEQUENCE#)  FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;



Identify the missing archive log file.

 SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' order by NAME;

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='NO';

col NAME for a70
select SEQUENCE#,THREAD#,STATUS,APPLIED,NAME from v$archived_log where APPLIED='YES' AND SEQUENCE# BETWEEN 80886 AND 81999;
use first query

 =========================
  set head off
set echo off
set feedback off
SELECT (ARCH.SEQUENCE# - APPL.SEQUENCE#) difference
FROM
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH
,
(SELECT DISTINCT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

===================  ARCH Difference query================================

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;
==========================================
errors

select status , error from v$archive_dest where des_id=2;

select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;


SQL> select process,client_process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
MRP0      N/A      WAIT_FOR_LOG          1     169866          0          0
RFS       LGWR     RECEIVING             1     169990     242070          8
RFS       ARCH     IDLE                  0          0          0          0


SELECT SEQUENCE#, APPLIED FROM V$ARCHIVED_LOG where APPLIED='NO' ;



alter database recover managed standby database cancel

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session
alter database recover managed standby database disconnect from session;

============  REAL TIME APPLY =======

select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

  select database_role , open_mode from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

SQL> select name ,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
WFLPRD    READ ONLY WITH APPLY

SQL> select dest_id , recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID RECOVERY_MODE
---------- -----------------------
         1 MANAGED




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