GoldenGate – Downstream architecture

How to extract data using GoldenGate without overloading your primary database or connecting to it directly

You have received a request to extract data using GoldenGate, but your primary database is overloaded or you have a strong security posture that prohibits other applications besides your main application to connect to your database. How do you accomplish this?

GoldenGate has an architecture called Downstream that lets you extract data from a shell database (this database only receives the data changed). In this article, I will show you how to deploy this.

Topology

The topology here is very simple:

For source, we need to setup a archive dest like we do when setting up a Data Guard config.

Copy the password file to Downstream host:

scp orapwdb* oracle@dbprd:/u01/app/oracle/product/19.0.0/dbhome_1/dbs

Add a tnsnames entry pointing to the downstream database:

DB19C =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ol79-db-ogg.myguest.virtualbox.org)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cdb1)
)
)

To configure log shipping, we need to change this 3 parameters, in my case I’m using the _2 but choose one that you are not using.

LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_STATE_2 -> Configure this after configuring both source and target.
LOG_ARCHIVE_CONFIG

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DB19C ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=cdb1' scope=both sid='*';

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11g,cdb1)' scope=both sid='*';

Configuring the Mining DB

Add Standby Log files following the rule: number of redo groups + 1:

ALTER DATABASE ADD STANDBY LOGFILE size 50m;

Configure the location for redo log generated by the mining db, its important to not mix the mining db and received logs:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/oradata/CDB1/local VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)';

Configure the location for the received redo logs:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/u01/oradata/CDB1/remote VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)';

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(db11g,cdb1)' scope=both sid='*';

After everything is configured, enable the dest in source and in mining db:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

Run a switch logfile in source db and see if the log is shipped to mining db:

GoldenGate

Once the log shipping is in place, we need to set up GoldenGate and create two connections, one to the source database and one to the mining database.

Add trandata to source db

And create an extract:

If you are using the Micros-services architecture its easy, just check the Downstream capture option and select both databases:

If you are using the command line, you will need these parameters in your parameter file:

USERIDALIAS db11g DOMAIN OracleGoldenGate ->  This points to source DB
TRANLOGOPTIONS MININGUSERALIAS cdb DOMAIN OracleGoldenGate -> This points to mining DB

Bonus – Real Time Capture

GoldenGate Downstream enables you to use a parameter called downstream_real_time_mine. With this parameter enabled, GoldenGate does not wait for the switch (and shipping) to occur to mine the needed data. Instead, it receives data into the standby redo log files and mines it immediately.

Your parameter file will look like this:

USERIDALIAS db11g DOMAIN OracleGoldenGate
TRANLOGOPTIONS MININGUSERALIAS cdb DOMAIN OracleGoldenGate
TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y)

And you can validate if the Extract is using Real Time mode with this query, you need to see at least one ACTIVE group:

select GROUP#,THREAD#,STATUS from V$STANDBY_LOG;

If Real Time capture is disabled, the lag will increase until the redo shipping:

Hope this article has been helpful in configuring the downstream architecture. If you have any questions, please feel free to ask.