This content has been marked as final. Show 1 reply
If your are facing missing archive logs in Oracle streams , the STATE column in V$STREAMS_CAPTURE view will show you WAITING FOR REDO.
during this you can run below QUERY to find sequence of missing archive logs:
SELECT THREAD, seq + 1 first_seq_missing, next_seq - 1 last_seq_missing,
next_seq - seq - 1 missing_count
FROM (SELECT thread# THREAD, sequence# seq,
LEAD (sequence#, 1, sequence#) OVER (PARTITION BY thread# ORDER BY sequence#)
FROM dba_registered_archived_log, dba_capture
WHERE capture_name = '&capture_name'
AND consumer_name = capture_name)
WHERE next_seq - seq > 1
ORDER BY 1, 2;
2)Once you get list of missing archive logs , you can check them physically by checking on archive log path.
3) if the archived log is not in location then, it is missing.
4) if archived log is present in location, means it is not registered, then register them manually for CAPTURE process to pick them up.