Data Guard: Gap sequence analysis sql returns inconsistent results from primary
Every four hours, we run gap sequence analysis of a four node primary database and a standby physical database.The data guard is active on both of them. The sql (given below) returns inconsistent results from primary and does not return sequences for all the four threads. This happens at least once in a day. What is the reason and what can be done?
SELECT distinct ARCH.THREAD# ||':'||
ARCH.SEQUENCE# ||':'||
APPL.SEQUENCE# ||':'||
(ARCH.SEQUENCE# - APPL.SEQUENCE#)
FROM (SELECT THREAD# ,SEQUENCE# FROM GV$ARCHIVED_LOG
WHERE (THREAD#,FIRST_TIME )
IN (SELECT THREAD#,MAX(FIRST_TIME)