Mir Sayeed Hassan – Oracle Blog

Verify Primary & Standby Database are sync & applied logs

ON PRIMARY:

col MEMBER FORMAT A100
set linesize 200

sql> SELECT   a.thread#,  b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq   ARC_DIFF FROM (SELECT  thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a,           (SELECT  thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

ON STANDBY:

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

 SQL> SELECT thread#, max(SEQUENCE#) FROM V$ARCHIVED_LOG where APPLIED='YES' group by thread#;

Or

SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;

Or

SQL> SELECT * FROM V$DATAGUARD_STATS WHERE NAME='apply lag';

Or

SQL> SELECT DEST_ID "ID", STATUS "DB_status",DESTINATION "Archive_dest", ERROR "Error" FROM V$ARCHIVE_DEST WHERE DEST_ID =2;

Or

SQL> SELECT THREAD#, count(*) FROM V$ARCHIVED_LOG where applied='NO' group by thread# ORDER BY THREAD#;

Note:

If you find the above results are any difference in archivelog or db is not sync – Perform the appropriate solution