Enable Downstream Extract to Work with ADG

In a cascaded downstream capture environment, the downstream database does not connect directly to the source database. It uses the Active Data Guard (ADG) as a reference.

Extract must be started using the sourceless option so that it does not connect to the source database and instead connects to ADG using FETCHUSERID or FETCHUSERIDALIAS when it needs to fetch any non-native datatypes. For example, FETCH operations are processed on the ADG database as this instance is open in read-only mode. Other operations that cannot be processed on the ADG instance, such as creating the dictionary build, are redirected from the ADG to the source database.

When registering a downstream Extract, Oracle GoldenGate connects to ADG as source database instead of the database where the redo originates. ADG redirection is supported for the following commands and parameters:

  • SCHEMATRANDATA

  • TRANDATA

  • FLUSH SEQUENCE

  • TRACETABLE

  • HEARTBEATTABLE

  • REGISTER EXTRACT

Note:

SCHEMATRANDATA and TRANDATA, even though the command is executed on the standby redo log, the actual log groups are created and maintained on the primary database where the actual DML operations take place.
ADG Redirection is available with Oracle Database 21c and higher. It also supports wildcard registration. The following example shows the Extract parameter file for the downstream Extract, when using ADG:
EXTRACT EXTDSC
NOUSERID
TRANLOGOPTIONS MININGUSERALIAS cgg_cdbDSC_src DOMAIN OracleGoldenGate
TRANLOGOPTIONS INTEGRATEDPARAMS (DOWNSTREAM_REAL_TIME_MINE Y) FETCHUSERIDALIAS cgg_cdbADG_src DOMAIN OracleGoldenGate 

EXTTRAIL cascade/ea 
SOURCECATALOG CDBNORTH_PDB01
DDL INCLUDE MAPPED
TABLE HR.*;

Here are the steps to enable downstream Extract to work with ADG Standby:

  1. Add an additional LOG_ARCHIVE_DESTINATION_N (LAD) on the ADG standby, as shown in the following example: 

    ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='service=mining_db_service_name ASYNC NOREGISTER VALID_FOR(STANDBY_LOGFILES,STANDBY_ROLES) DB_UNIQUE_NAME=3rd_db_unique_name' scope=both

    This step transports and generates the standby_logfiles for an ADG standby.

  2. Set the LOG_ARCHIVE_CONFIG on the ADG standby to ship the logs to the mining database, as shown in the following example: 

    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='dg_config' scope=both;

    db_config is the database unique name of the first, second, and third databases.

  3. On the mining database, set up the location to store the incoming standby_logfiles on the mining database: 

    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='location= DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILE,ALL_ROLES)' scope=both

    The location is the database recovery file destination.

  4. Run LOG_ARCHIVE_CONFIG on the mining database, so that the Extract process is able to read them on the mining database, as shown in the following example: 

    ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='dg_config' scope=both

    Here, db_config is the database unique name of the first, second, and third databases.

  5. For a downstream Extract, you need to ensure that the database connections are appropriately configured. When registering the Extract, make sure that DBLOGIN connection is made to the ADG Standby, that is open for read-only activity.

  6. To add the Extract and register it, use the following command:

    DBLOGIN USERID ggadmin@cdbADG_src, PASSWORD ggadmin
MININGDBLOGIN USERID ggadmin@cgg_cdbDSC, password ggadmin

    cdbADG_src is the ADG not primary.

    cgg_cdbDSC is the mining database.

  7. Now, register an Extract that uses the NOUSERID parameter:
    ADD EXTRACT exte, INTEGRATED TRANLOG, BEGIN NOW REGISTER EXTRACT exte DATABASE
  8. After the Extract is registered, you can use this Extract to mine data and start the Extract normally.