Some time ago I read a question in one Linkedin group, talks about a problem with Goldengate data extraction on a database ho have a Physical Stanby replica.
The problem arises every month after a role change of the databases (is an obligatory role change, to check all works as expected on DR site). The new “primary” database runs some days in the remote DR site and after that is returned to origin server. On origin server a “gap” is produced on the redo flow used by Goldengate, the extract process abends on initial switchover, and after the roles are reverted again (with the second switchover) is unable to continue.
Oracle have some options to avoid this ( here, and here ) using Oracle Clusterware features to allow Goldengate to continue after switchover, basically is configure Goldengate in active-passive (or on High Availability) mode to allow it run always on the server with the active database. I proposed a different approach, a less know one I think, also mentioned by Oracle in some documentation ( Integrated Capture: Deploying Downstream For Data Guard jump to page 40 on this Oracle presentation).
The solution proposed is use a “Downstream Mining Database” to receive redo from the primary database, and configure Goldengate in integrated mode to capture changes from redo on this database. I have successfully implemented this solution on a 220.127.116.11 Database on Linux (note that the latest PSU’s applied on both origin and downstream databases is a requirement).
The proposed approach is not to give “High availability” to Oracle Goldengate extraction (the Downstream database is a single point of failure), instead is to “isolate” switchover and failover tasks on primary-standby databases from Goldengate data extraction.
This are some points to take into account for this configuration:
- On both primary and standby databases we must configure a “new” redo destination pointing to “Downstream” database, this redo destination must be only active when the database have the primary role (we must use the “VALID_FOR” parameter of ARCHIVE_LOG_DEST_n).
- We need another database (the downstream one) but not is necessary any extra software (clusterware, clustered filesystems, ..) on primary/standby or downstream databases/servers.The downstream database only contains Goldengate related tables (no user data).
- Works without issues with RAC configurations
- Offload most of the work necessary to extract trail data from redos to the server where downstream database is located. Only some selects are executed on origin databases by Goldengate to allow successful and complete extraction of data. The Goldengate process needs access to the primary database on every moment and this can be accomplished with a “TAF” connection string.
- Goldengate continues working (extracting data) without human intervention after role change of origin databases.