Hello All,
My Primary and standby database are in sync but v$dataguard_stats on standby server showing 8 hrs delay between Primary and standby.
DB Version:11.2.0.1.0
Server :AIX
Primary:
SQL> select /*+ FULL(c) PARALLEL(c, 35) */ distinct SEQUENCE# "Last Sequence Generated" FROM V$ARCHIVED_LOG c WHERE (THREAD#,FIRST_TIME ) IN (select /*+ FULL(c) PARALLEL(c, 35) */ THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG c GROUP BY THREAD#);
Last Sequence Generated
-----------------------
4521
Standby:
SQL> SELECT APPL.SEQUENCE# "Last Sequence Applied"
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,
2 3 (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
4 WHERE ARCH.THREAD# = APPL.THREAD#;
Last Sequence Applied
---------------------
4521
SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
2 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,
3 (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#;
4
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 4521 4521 0
SQL> select name, 'Hours', to_number(substr(value,5,2)) from v$dataguard_stats where name = 'apply lag' ;
NAME 'HOUR TO_NUMBER(SUBSTR(VALUE,5,2))
-------------------------------- ----- ----------------------------
apply lag Hours 8------------------------------------------------------------------>>>Problem area