Oracle GoldenGate 19c Downstream Integrated Capture with ADG Fetch Redirection and Cascaded Archived Redo from Oracle Database 11.2.0.4
This article demonstrates Oracle GoldenGate 19c downstream Integrated Extract using archived redo cascaded through an Oracle Active Data Guard standby. FETCHUSERIDALIAS is used to redirect supported runtime fetch operations to the ADG standby, reducing the need for Extract to query the production primary database directly.
Oracle GoldenGate downstream capture is specifically designed to offload the primary/production database from where the data is being captured.
This architecture is useful for reducing the source database involvement and overhead but at the cost of additional implementation complexity compared to the normal GoldenGate configuration.
Environment
Unlike a conventional GoldenGate topology with a source and target database, downstream Integrated Extract introduces a separate mining database where the LogMiner server processes the source redo.
The mining database can be configured on the same host as the target database or a separate host. In this lab, I provisioned both the mining database and the target database on the same host.
Below is the environment used for this lab:
Source/Standby Platform : AIX on POWER, 64-bit
Mining/Target Platform : AIX on POWER, 64-bit
Source Database : 11.2.0.4
Standby Database : 11.2.0.4
Mining Database : 19c
Target Database : 19c
GoldenGate : 19c
Important Clarification
This configuration does not use the full Oracle GoldenGate ADG Redirection feature introduced for Oracle Database 21c and later. Instead, it uses Oracle GoldenGate 19c downstream Integrated Extract with archived redo cascaded through the Active Data Guard standby, while FETCHUSERIDALIAS redirects supported runtime fetch operations to the ADG standby. Administrative operations such as REGISTER EXTRACT and ADD SCHEMATRANDATA are still performed using a connection to the primary source database.
Oracle Database 11g Release 2 cascading redo transport is not supported in Data Guard configurations containing an Oracle RAC primary database or configurations managed by Data Guard Broker. The source database used in this lab is single instance.
Phase 1: OS Setup & Installation
The mining/target host requires GoldenGate binaries to be installed.
Setting up the GoldenGate Mining/Target Host:
1. Create the GoldenGate home and grant the necessary permissions.
mkdir -p /u01/app/oracle/product/ogg19
chown -R oracle:oinstall /u01/app/oracle/product/ogg19
chmod -R 775 /u01/app/oracle/product/ogg192. Edit .bash_profile:
export OGG_HOME=/u01/app/oracle/product/ogg193. Install the GoldenGate software:
unzip /u01/soft/<goldengate_software>
cd /u01/soft/<goldengate_software>/Disk1
./runInstaller4. From the Oracle GoldenGate directory run the GGSCI program.
Set DB environment:
. oraenv
cd $OGG_HOME./ggsci
GGSCI> CREATE SUBDIRS
GGSCI> EDIT PARAMS MGRPORT 7809GGSCI> START MGR
GGSCI> INFO MGR
Phase 2: Database Preparation
Source database preparation:
A GoldenGate user must be created in the source database. This user will be used to access the database by the GoldenGate processes for any operations that the ADG standby database cannot fulfill.
SHOW PARAMETER COMPATIBLE;Verify that the source database COMPATIBLE parameter is set to 11.2.0.4 or higher. This is required for the Oracle 11.2.0.4 Integrated Capture capabilities used in this configuration.
In the Source Database:
1. Enable ARCHIVELOG & Logging: The database must be in ARCHIVELOG mode with minimal supplemental logging enabled.
ARCHIVE LOG LIST;-- Run as SYSDBA if ARCHIVELOG mode is disabled
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;2. Create GoldenGate User
CREATE TABLESPACE GG_TBS DATAFILE '/path/to/<DB_NAME>/gg_tbs01.dbf' SIZE 100M AUTOEXTEND ON;
CREATE USER GGADMIN11G IDENTIFIED BY <password> DEFAULT TABLESPACE GG_TBS QUOTA UNLIMITED ON GG_TBS;
GRANT CONNECT, RESOURCE, DBA TO GGADMIN11G;GRANT SELECT ANY DICTIONARY TO GGADMIN11G;
GRANT FLASHBACK ANY TABLE TO GGADMIN11G;BEGIN
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(
GRANTEE => 'GGADMIN11G',
PRIVILEGE_TYPE => 'CAPTURE',
GRANT_SELECT_PRIVILEGES => TRUE,
DO_GRANTS => TRUE
);
END;
/ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;In the Mining Database:
3. Enable ARCHIVELOG.
ARCHIVELOG mode is recommended on the downstream mining database because Integrated Extract stores capture state in the mining database, allowing that state to be protected through database backups and recovery.
ARCHIVE LOG LIST;-- Run as SYSDBA if ARCHIVELOG mode is disabled
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;4. Create GoldenGate User
CREATE TABLESPACE GG_TBS DATAFILE '/path/to/<DB_NAME>/gg_tbs01.dbf' SIZE 100M AUTOEXTEND ON;
CREATE USER GGADMMINE IDENTIFIED BY <password> DEFAULT TABLESPACE GG_TBS QUOTA UNLIMITED ON GG_TBS;
GRANT CONNECT, RESOURCE, DBA TO GGADMMINE;GRANT SELECT ANY DICTIONARY TO GGADMMINE;
GRANT FLASHBACK ANY TABLE TO GGADMMINE;BEGIN
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(
GRANTEE => 'GGADMMINE',
PRIVILEGE_TYPE => 'CAPTURE',
GRANT_SELECT_PRIVILEGES => TRUE,
DO_GRANTS => TRUE
);
END;
/ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;ALTER SYSTEM SET STREAMS_POOL_SIZE=<appropriate_size> SCOPE=BOTH;In the Target Database:
5. Verify Target Database Configuration.
ARCHIVE LOG LIST;6. Create GoldenGate User
CREATE TABLESPACE GG_TBS DATAFILE '/path/to/<DB_NAME>/gg_tbs01.dbf' SIZE 100M AUTOEXTEND ON;
CREATE USER GGADMIN IDENTIFIED BY <password> DEFAULT TABLESPACE GG_TBS QUOTA UNLIMITED ON GG_TBS;
GRANT CONNECT, RESOURCE, DBA TO GGADMIN;GRANT SELECT ANY DICTIONARY TO GGADMIN;
GRANT FLASHBACK ANY TABLE TO GGADMIN;BEGIN
DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(
GRANTEE => 'GGADMIN',
PRIVILEGE_TYPE => 'APPLY',
GRANT_SELECT_PRIVILEGES => TRUE,
DO_GRANTS => TRUE
);
END;
/ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;ALTER SYSTEM SET STREAMS_POOL_SIZE=<appropriate_size> SCOPE=BOTH;There is no need to create a user in the standby database since the user created in the source will propagate into the standby side as well.
Phase 3: Configure Cascaded Redo Transport on the Standby Database
Execute the following on SOURCEDB, STANDBYDB, and MINEDB, replacing the values with the actual DB_UNIQUE_NAME values:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(SOURCEDB,STANDBYDB,MINEDB)' SCOPE=BOTH;Redo transport between the cascading standby and MINEDB must be authenticated. If remote password-file authentication is used, ensure that the sending database and MINEDB use compatible password files with the same SYS password. Configure the password file according to the storage method used by the environment, such as filesystem or ASM.
SHOW PARAMETER remote_login_passwordfile;Ensure that the MINEDB Oracle Net service is resolvable and reachable from the standby host. Configure the appropriate tnsnames.ora entry and use tnsping MINEDB to verify Oracle Net resolution. Redo-transport authentication should then be validated separately.
Configure the appropriate LOG_ARCHIVE_DEST_n on the standby database to cascade archived redo to the mining database.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3=
'SERVICE=MINEDB
ARCH
OPTIONAL
NOREGISTER
REOPEN=60
VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=MINEDB
TEMPLATE=+DATA/MINEDB/SRC_CASCADE_ARCH/SRC_%t_%s_%r.arc'
SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE SCOPE=BOTH;Validate the dest:
SELECT dest_id,
status,
destination,
db_unique_name,
error
FROM v$archive_dest_status
WHERE dest_id = 3;STATUS = VALID
ERROR = NULLCheck whether the configured location is receiving archived redo logs as expected.
Phase 4: Add schema-level supplemental logging
Create CREDENTIALSTORE to add user credentials.
GGSCI> ADD CREDENTIALSTOREAdd source, standby, mining and target user credentials:
GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin11g@SOURCEDB PASSWORD <password> ALIAS sourcegg
GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin11g@STANDBYDB PASSWORD <password> ALIAS standbygg
GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmmine@MINEDB PASSWORD <password> ALIAS mininggg
GGSCI> ALTER CREDENTIALSTORE ADD USER ggadmin@TARGETDB PASSWORD <password> ALIAS targetggSince the target and the mining database are in the same host, a single GGSCI client will be used throughout the lab.
GGSCI> DBLOGIN USERIDALIAS sourcegg
GGSCI> ADD SCHEMATRANDATA SCHEMA1 PREPARECSN NOWAITValidate:
GGSCI> INFO SCHEMATRANDATA SCHEMA1
Phase 5: Register the downstream Integrated Extract
Connect to both the source and mining databases through GGSCI:
GGSCI> DBLOGIN USERIDALIAS sourcegg
GGSCI> MININGDBLOGIN USERIDALIAS mininggg
GGSCI> REGISTER EXTRACT X_SRC DATABASE
Phase 6: Add the downstream Extract
Create the Extract
GGSCI> ADD EXTRACT X_SRC, INTEGRATED TRANLOG, BEGIN NOWAdd the local trail
GGSCI> ADD EXTTRAIL ./dirdat/xa, EXTRACT X_SRC
Phase 7: Create the Extract parameter file
GGSCI> EDIT PARAMS X_SRCEXTRACT X_SRC
NOUSERID
TRANLOGOPTIONS MININGUSERALIAS mininggg
TRANLOGOPTIONS INTEGRATEDPARAMS (DOWNSTREAM_REAL_TIME_MINE N)
FETCHUSERIDALIAS standbygg
EXTTRAIL ./dirdat/xa
TABLE SCHEMA1.*;Tables with FULL support can normally be captured entirely from redo. Objects reported as ID KEY require the FETCHUSERIDALIAS connection in this downstream sourceless Extract configuration. You can find the support mode of tables using the following query:
SELECT owner,
table_name,
support_mode
FROM dba_goldengate_support_mode
WHERE owner = 'SCHEMA1';
Phase 8: Start Extract before performing the initial load
Start the Extract process:
GGSCI> START EXTRACT X_SRCVerify the Extract is running without any issues:
GGSCI> INFO EXTRACT X_SRC, DETAIL
GGSCI> LAG EXTRACT X_SRCCheck the log file for any errors:
GGSCI> VIEW REPORT X_SRC
Phase 9: Perform the SCHEMA1 initial Data Pump export
Perform the export from the source database.
Make sure the EXPDP_DIR object is created beforehand in the database and also a sufficiently privileged account is used for the export.
expdp dpadmin@SOURCEDB DIRECTORY=EXPDP_DIR DUMPFILE=SCHEMA1_init_load.dmp LOGFILE=SCHEMA1_init_load.log SCHEMAS=SCHEMA1
Phase 10: Import Schema to Target Database
Transfer the dump file to the target server.
Import the dump file:
impdp dpadmin@TARGETDB DIRECTORY=EXPDP_DIR DUMPFILE=SCHEMA1_init_load.dmp LOGFILE=SCHEMA1_import.log SCHEMAS=SCHEMA1
Phase 11: Create a GoldenGate checkpoint table
GGSCI> DBLOGIN USERIDALIAS targetgg
GGSCI> ADD CHECKPOINTTABLE ggadmin.gg_checkpointValidate:
GGSCI> INFO CHECKPOINTTABLE ggadmin.gg_checkpointA checkpoint table transactionally stores Replicat’s recovery position in the target database, allowing Replicat to restart reliably from a consistent position after interruptions or failures.
Phase 12: Create Integrated Replicat
GGSCI> DBLOGIN USERIDALIAS targetgg
GGSCI> ADD REPLICAT R_TGT, INTEGRATED, EXTTRAIL ./dirdat/xa, CHECKPOINTTABLE ggadmin.gg_checkpoint
GGSCI> EDIT PARAMS R_TGTREPLICAT R_TGT
USERIDALIAS targetgg
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
DBOPTIONS SOURCE_DB_NAME SOURCEDB
MAP SCHEMA1.*, TARGET SCHEMA1.*;ENABLE_INSTANTIATION_FILTERING causes Replicat to use the per-table instantiation SCNs imported by Oracle Data Pump and discard trail records that precede each table’s instantiation point. SOURCE_DB_NAME needs the source database’s GLOBAL_NAME. Find the GLOBAL_NAME using SELECT GLOBAL_NAME FROM GLOBAL_NAME;
Phase 13: Start Replicat
GGSCI> START REPLICAT R_TGTVerify the Replicat is running without any issues:
GGSCI> INFO REPLICAT R_TGT, DETAIL
GGSCI> LAG REPLICAT R_TGT
GGSCI> STATS REPLICAT R_TGT, TOTALCheck the log file for any errors:
GGSCI> VIEW REPORT R_TGT
Basic Flow — Initial Creation
Below are the basic steps of the initial creation of a GoldenGate Extract and Replicat configuration in this lab.
ADD SCHEMATRANDATA + PREPARECSN
↓
REGISTER EXTRACT
↓
ADD EXTRACT
↓
START EXTRACT
↓
TAKE DUMP
↓
IMPORT DUMP
↓
ADD CHECKPOINTTABLE
↓
ADD REPLICAT
↓
START REPLICAT
Basic Flow — Adding New Schemas
Below are the steps you need to follow when adding new schemas to an existing Extract and Replicat.
ADD SCHEMATRANDATA + PREPARECSN
↓
STOP REPLICAT
↓
STOP EXTRACT
↓
ADD NEW SCHEMA TO EXTRACT
↓
START EXTRACT
↓
TAKE DUMP
↓
IMPORT DUMP
↓
ADD NEW SCHEMA TO REPLICAT
↓
START REPLICAT
Important Details
- I did not configure a separate GoldenGate data pump Extract in this scenario because the mining database and target database reside on the same host, allowing the downstream Integrated Extract to write directly to the local trail consumed by Replicat.
- Although separate Extract and Replicat processes can be created for individual schemas, doing so increases both GoldenGate process overhead and database side resource consumption. Unless workload isolation or performance requirements justify separate processes, consolidating multiple schemas into fewer Extract/Replicat groups can reduce overall memory and operational overhead.
Troubleshooting
- After immediately starting the Extract, the LAG AT CHKPT increases and the Extract does not start correctly.
One possible cause is that the downstream LogMiner session does not have access to the archived redo containing the required LogMiner dictionary.
SELECT capture_name,
extract_name,
state,
total_messages_captured,
total_messages_enqueued,
total_messages_sent,
bytes_of_redo_mined
FROM v$goldengate_capture;If the capture remains in DICTIONARY INITIALIZATION for an unusually long period, inspect the registered LogMiner redo and Extract/report logs. A WAITING FOR DICTIONARY REDO state specifically indicates that LogMiner is waiting for redo files containing the required dictionary build.
Check the logminer_id and FIRST_SCN:
SELECT capture_name,
status,
client_status,
logminer_id,
TO_CHAR(first_scn,
‘FM99999999999999999999’) AS first_scn,
TO_CHAR(start_scn,
‘FM99999999999999999999’) AS start_scn,
TO_CHAR(captured_scn,
‘FM99999999999999999999’) AS captured_scn,
error_number,
error_message
FROM dba_capture
WHERE capture_name = ‘OGG$CAP_X_SRC’;Change the capture_name as required.
Identify the source archive containing FIRST_SCN on the Source Database.
SET LINES 300
COLUMN name FORMAT A50
SELECT thread#,
sequence#,
TO_CHAR(first_change#,
‘FM99999999999999999999’) AS first_scn,
TO_CHAR(next_change#,
‘FM99999999999999999999’) AS next_scn,
dictionary_begin,
dictionary_end,
completion_time,
name
FROM v$archived_log
WHERE thread# = 1
AND first_change# <= <FIRST_SCN>
AND next_change# > <FIRST_SCN>
AND standby_dest = ‘NO’
ORDER BY sequence#;THREAD# = 1 is used in this example because the source database is single instance.
If the file exists physically but not registered, register it manually in the mining database.
ALTER DATABASE REGISTER LOGICAL LOGFILE ‘+DATA/MINEDB/SRC_CASCADE_ARCH/SRC_1_795931_859865870.arc’ FOR ‘OGG$CAP_X_SRC’;Change the logfile path to the one you configured in the LOG_ARCHIVE_DEST_n destination and make sure to add the correct log file number as well.
If the file does not exist physically you may have to manually copy the archived redo logfile to the mining server and register it.
Verify that the capture state progresses to CAPTURING CHANGES or WAITING FOR REDO.
SELECT capture_name,
extract_name,
state,
total_messages_captured,
total_messages_enqueued,
total_messages_sent,
bytes_of_redo_mined
FROM v$goldengate_capture;The Extract lag should then begin to decrease.
2. After all configurations have been successfully completed and the GoldenGate processes are running smoothly after a while the Extract stops processing logs and the lag increases.
One possible cause is a gap in the archived redo logs available to the downstream LogMiner session. Check the Extract report and capture state first, then verify whether the required archived redo sequences are available to the mining database using the below query:
SET LINESIZE 250
SET PAGESIZE 1000
WITH logs AS
(
SELECT l.thread#,
l.resetlogs_scn,
l.sequence#,
l.first_scn,
l.next_scn,
l.name,
LAG(l.sequence#) OVER
(PARTITION BY l.thread#, l.resetlogs_scn
ORDER BY l.sequence#) AS previous_sequence,
LAG(l.next_scn) OVER
(PARTITION BY l.thread#, l.resetlogs_scn
ORDER BY l.sequence#) AS previous_next_scn
FROM dba_logmnr_log l
JOIN v$goldengate_capture c
ON c.logminer_id = l.logmnr_session_id
WHERE c.extract_name = ‘X_SRC’
)
SELECT thread#,
resetlogs_scn,
previous_sequence + 1 AS missing_sequence_from,
sequence# — 1 AS missing_sequence_to,
previous_next_scn AS missing_scn_from,
first_scn AS missing_scn_to
FROM logs
WHERE previous_sequence IS NOT NULL
AND sequence# > previous_sequence + 1
ORDER BY resetlogs_scn,
thread#,
missing_sequence_from;Transfer the missing archived redo logs to the mining server and copy them to the correct location using the correct naming convention you used.
Register the archived redo logs with the mining database
ALTER DATABASE REGISTER LOGICAL LOGFILE ‘+DATA/MINEDB/SRC_CASCADE_ARCH/SRC_1_802347_859865870.arc’ FOR ‘OGG$CAP_X_SRC’;Change the location of the logfile to the location you configured in the log dest and make sure to add the correct log file number as well.
The Extract lag should gradually decrease.
Comments
Post a Comment