FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY:

 

select LOG_ARCHIVED-LOG_APPLIED "LOG_GAP" from
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'),
(SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');

 

CHECK THE STATUS OF DIFFERENT PROCESS:

 

 

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY ;
 
PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_LOG          1      53056          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  0          0          0          0
RFS       IDLE                  1      53056      10935          2
RFS       IDLE                  0          0          0          0
 
9 rows selected.

 

 

LAST SEQUENCE RECEIVED AND LAST SEQUENCE APPLIED:

 

SQL> SELECT al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied" FROM
  2  (select thread# thrd, MAX(sequence#) almax FROM v$archived_log WHERE resetlogs_change#=(SELECT resetlogs_change#
  3   FROM v$database) GROUP BY thread#) al, (SELECT thread# thrd, MAX(sequence#) lhmax FROM v$log_history
WHERE resetlogs_change#=(SELECT resetlogs_change# FROM v$database) GROUP BY thread#) lh WHERE al.thrd = lh.thrd;  4
 
    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1             49482            49482

 

CHECK THE MESSAGES/ERRORS IN STNADBY DATABASE:

 

set pagesize 2000
set lines 2000
col MESSAGE for a90
select message,timestamp from V$DATAGUARD_STATUS where timestamp > sysdate - 1/6;
MESSAGE                                                                                    TIMESTAMP
------------------------------------------------------------------------------------------ ---------
RFS[48]: No standby redo logfiles created                                                  05-AUG-15
Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49481.log                       05-AUG-15
Media Recovery Waiting for thread 1 sequence 49482 (in transit)                            05-AUG-15
RFS[48]: No standby redo logfiles created                                                  05-AUG-15
Media Recovery Log /uv1010/arch/MRSX/arch_MRSX_779539386_1_49482.log                       05-AUG-15
Media Recovery Waiting for thread 1 sequence 49483 (in transit)                            05-AUG-15
 
6 rows selected.

 

CHECK THE NUMBER OF ARCHIVES GETTING GENERATING ON HOURLY BASIS:

 

SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999')      "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999')      "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999')      "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999')      "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999')      "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999')      "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999')      "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999')      "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999')      "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999')      "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999')      "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999')      "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999')      "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999')      "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999')      "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999')      "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999')      "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999')      "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999')      "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999')      "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999')      "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999')      "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999')      "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999')      "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/

Find LAG in dataguard with ORACLE RAC :

NOTE – The below script was provided by Ernest and Eli Dias from comment section.

— dg_lag_minutes.sql — takes ORACLE_SID as parameter

set echo on feed on term on
set linesize 120
col PRIMARY_TIME format a20
col STANDBY_COMPLETION_TIME format a23
spool dg_lag_minutes_&1..sql.log.txt
SELECT
prim.thread# thread,
prim.seq primary_seq,
to_char(prim.tm, ‘DD-MON-YYYY HH24:MI:SS’) primary_time,
tgt.thread# standby_thread,
tgt.seq standby_seq,
to_char(tgt.tm, ‘DD-MON-YYYY HH24:MI:SS’) standby_completion_time,
prim.seq – tgt.seq seq_gap,
( prim.tm – tgt.tm ) * 24 * 60 lag_minutes
FROM
(
SELECT
thread#,
MAX(sequence#) seq,
MAX(completion_time) tm
FROM
v$archived_log
GROUP BY
thread#
) prim,
(
SELECT
thread#,
MAX(sequence#) seq,
MAX(completion_time) tm
FROM
v$archived_log
WHERE
dest_id IN (
SELECT
dest_id
FROM
v$archive_dest
WHERE
target = ‘STANDBY’
)
AND applied = ‘YES’
GROUP BY
thread#
) tgt
WHERE
prim.thread# = tgt.thread#;
spool off