Oracle Goldengate continuity after Standby Switchover

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 11.2.0.4 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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s