Multiple Oracle V$ARCHIVED_LOG entries with same SCN range

[Chris Cranford]

Hi all -

A recent issue (DBZ-3341 [1]) was reported by a user using Oracle Data Guard where the log picking logic was selecting a log entry in V$ARCHIVED_LOG that wasn't available to mine.  The row itself had STATUS='A' and DELETED='NO'; however we noticed that that the row had STANDBY_DEST='YES' and was using a secondary destination id, DEST_ID=2.  We do not yet know from the user specifically the configuration of that destination in V$ARCHIVE_DEST_STATUS, but I'm thinking it makes sense to apply an explicit predicate condition to any query against V$ARCHIVED_LOG that limits the returned data-set to those that are associated with LOCAL destinations only.  In other words, we'd add the following predicate always:

WHERE DEST_ID IN ( SELECT DEST_ID FROM V$ARCHIVE_DEST_STATUS WHERE STATUS = 'VALID' AND TYPE  = 'LOCAL' )

For those of you using Data Guard or other Oracle tools that might interact with V$ARCHIVED_LOG and write additional rows, would that work for your environments?  I'd like to get this fix in before the final release next week but I wanted to get a consensus from those running Oracle since this is specifically a use case that I cannot setup locally and test specifically against.

Thanks,
Chris

[1]: https://issues.redhat.com/browse/DBZ-3341

[Matthew Tice]

Hi Chris, 

I ran this by our Oracle DBA and he noted:

"so that query would work SELECT DEST_ID FROM V$ARCHIVE_DEST_STATUS WHERE STATUS = 'VALID' AND TYPE  = 'LOCAL'  as long as there isn't two local destinations.  meaning the primary is copying archive logs to two different locations.  I spose if he adds a rownum =1 on there it would fix that issue as well"

Hopefully that's helpful.

Matt

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/096fb750-50ce-6659-9c96-3e16dd221cd7%40gmail.com.

[Milo van der Zee]

Hello,

I made it simple for myself by just adding a check during the adding of the logfiles.

if (deDuplicateList.contains(firstSCN)) continue

MAG,

Milo

Op do 25 mrt. 2021 om 12:01 schreef Matthew Tice <mjt...@gmail.com>:

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CA%2BtaBv8g6zu0qA2UTdN%3Do58f236QuNyui-H9PwWwOn4rvAy0YA%40mail.gmail.com.

[Chris Cranford]

Thanks Matt & Milo for your input!

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAO5pNX%2BQzB%2BKi6c1wdvG2Vo5DUHe2bHF8rGpA-vWaTp-tfDahw%40mail.gmail.com.