Downstream Extract for Oracle GoldenGate Deployment

A downstream Oracle GoldenGate deployment allows you to offload the source database redo logs to a downstream mining database. This enables reducing the load on the source database for mining.

A downstream mining database accepts archived logs and online redo logs from a source database. This implies that a downstream Extract can exist in two modes:

Real-Time Mining mode: In Real-time mining mode, the redo records are processed from the online or standby redo logs immediately, so, there is no delay in processing the database changes.
Archive-Log-Only mode: In archive log mining mode, the redo records are not processed until they are archived into a redo log file.

In a typical downstream deployment, a source database has the primary Extract and a downstream database with a downstream Extract. In the following diagram, CDBNORTH is the source database and CDBSOUTH is the target database. There is a mining database between the source and target with the database logmining server that transfers redo logs from the downstream Extract to the target.

Workflow between source multitenant container database and a downstream database with logmining server.

Another option to set up a downstream Extract is to use Extract registration using Active Data Guard (ADG) redirection for the mining database.

This approach uses an ADG configured in a cascaded mode to transport redo logs to a downstream mining database which is then referred by a downstream Extract, reducing the overhead on the source database, as shown in the following diagram.

Downstream Database connected to Active Data Guard (ADG)

For configuration details about setting up a cascaded downstream environment, see Enable Downstream Extract to Work with ADG.

Configure the Source and Downstream Databases

In this section, you will learn about the parameters and user management configurations required on database side.

Oracle GoldenGate User in Source and Downstream Databases

The Oracle GoldenGate Administrator user needs to exist on each database. In the container databases, a common user is required as the Extract will be used within the Root container.

To set up users for Oracle Database 23ai and higher, the OGG_CAPTURE and OGG_APPLY user role needs to be assigned to the new user. Refer to Grant User Privileges for Oracle Database 23ai and Higher for details.

To set up users for Oracle Database 21c and lower, PL/SQL packages are used. The following example shows the creation of a user and the associated privileges granted to this user when using Oracle GoldenGate with Oracle Database 21c and lower:


CREATE USER c##ggadmin IDENTIFIED BY password CONTAINER = all;
ALTER USER c##ggadmin SET CONTAINER_DATA = all CONTAINER = current;
GRANT ALTER SESSION TO c##ggadmin CONTAINER = all;
GRANT connect, resource TO c##ggadmin CONTAINER = all;
 
BEGIN
  DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE
    ( grantee                 => 'C##GGADMIN'
    , privilege_type          => 'CAPTURE'
    , grant_select_privileges =>  TRUE
    , container               => 'ALL'
    );
END;
/

Note:

The common user (c##ggadmin) is required for the downstream database only.

Configure Database Connections

Database Net Service Requirements

The source and downstream databases can be accessed using the Database Net Services in both directions.

In Oracle GoldenGate, connections for the source database (CDBNORTH_root) and the downstream database (cdbDSC_root) can be set up. The source database connection is only needed to store dictionary data, during Extract registeration. You can use the FETCHUSERID parameter to connect to the downstream mining database (cdbDSC_root) for fetches at runtime. With ADG redirection, the registering of Extract can be redirected to the source from an ADG. For setting up connections, make sure that you have the database credentials for these users, to create credentials from the Oracle GoldenGate deployment.

You can test the Net Service with SQL*Plus, which is part of the Oracle GoldenGate shiphome.

Database Prerequisites

For configuring database connections, make sure that the following settings are done on the source and downstream database instances:

All databases must include:

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE;
STREAMS_POOL_SIZE must be set. For example

ALTER SYSTEM SET STREAMS_POOL_SIZE = 1G;
All databases must be in Archive-Log-Only Mode.

ALTER DATABASE ARCHIVE LOG;
(Recommended) All databases must be in Force Logging mode.

ALTER DATABASE FORCE LOGGING;
Source database must have minimal supplemental logging enabled.
The downstream database must have different settings for the following:
- DBID
- DBNAME
- DB_UNIQUE_NAME
- GLOBAL_NAME
In a downstream environment, the unique database name matches the database name.
The password files (orapwSID) of the source and downstream database are identical. So, the remote_login_passwordfile must be set to SHARED or EXCLUSIVE.

Configure Parameters for Downstream Database

Use this section to configure parameters for real-time mining mode and archive-log-only mode, as needed.

Parameter Settings for Downstream Extract in Real-Time Mining Mode

For downstream Extract in Real-Time Mining mode, the primary prerequisite is that the downstream database must have standby redo log files configured correctly.

Downstream Real-Time Mining is enabled with an explicit setting within the Extract Parameter file. The configuration of the LAD is independent from this mode. If the TRANLOGOPTIONS INTEGRATEDPARAMS (REAL_TIME_MINE Y) parameter was not already set, you need to set it to enable downstream Extract for real-time mining mode:

TRANLOGOPTIONS INTEGRATEDPARAMS (DOWNSTREAM_REAL_TIME_MINE Y)

Note:

There can be only one real-time Extract on the downstream database for a given source database. Other Extracts have to be non-realtime. This is a restriction from redo transport.

The local backup uses the Flash Recovery Area (FRA), which also manages the foreign archive log files. The following command shows the local backup being set up using the FRA:

ALTER SYSTEM  
    SET log_archive_config='DG_CONFIG=(CDBNORTH, cdbDSC)'; 
ALTER SYSTEM set FAL_SERVER = 'CDBNORTH_root';

Standby Redo Log Files must exist on the downstream database. If there are n online redo log files at the source, then there should be n+1 standby redo log files at the target of the same size.

Create the Standby Redo Log Files

The following steps outline the procedure for adding standby redo log files to the downstream mining database. The following summarizes the rules for creating the standby redo logs:

Each standby redo log file must be at least as large as the largest redo log file of the redo source database. For administrative ease, Oracle recommends that all redo log files at source database and the standby redo log files at the downstream mining database be of the same size.
The standby redo log must have at least one more redo log group than the redo log at the source database, for each redo thread at the source database.

The specific steps and SQL statements that are required to add standby redo log files depend on your environment. See Creating a Physical Standby Database for detailed instructions about adding standby redo log files to a database.

Note:

If there will be multiple source databases sending redo to a single downstream mining database, only one of those sources can send redo to the standby redo logs of the mining database. An Extract process that mines the redo from this source database can run in real-time mode. All other source databases must send only their archived logs to the downstream mining database, and the Extracts that read this data must be configured to run in archived-log-only mode.

To Create the Standby Redo Log Files

In SQL*Plus, connect to the source database as an administrative user.
Determine the size of the source log file. Make note of the results.
```
SELECT BYTES FROM V$LOG;
```
Determine the number of online log file groups that are configured on the source database. Make note of the results.
```
SELECT COUNT(GROUP#) FROM V$LOG;
```
Connect to the downstream mining database as an administrative user.
Add the standby log file groups to the mining database. The standby log file size must be at least the size of the source log file size. The number of standby log file groups must be at least one more than the number of source online log file groups. This applies to each instance (thread) in a RAC installation. So if you have n threads at the source database, each having m redo log groups, you should configure n*(m+1) redo log groups at the downstream mining database.
The following example shows three standby log groups.
```
ALTER DATABASE ADD STANDBY LOGFILE GROUP 3
('/oracle/dbs/slog3a.rdo', '/oracle/dbs/slog3b.rdo') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/oracle/dbs/slog4.rdo', '/oracle/dbs/slog4b.rdo') SIZE 500M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/oracle/dbs/slog5.rdo', '/oracle/dbs/slog5b.rdo') SIZE 500M;
```

Confirm that the standby log file groups were added successfully.

SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS
FROM V$STANDBY_LOG;

The output should be similar to the following:

GROUP#     THREAD#    SEQUENCE#  ARC STATUS
---------- ---------- ---------- --- ----------
         3          0          0 YES UNASSIGNED
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED

Ensure that log files from the source database are appearing in the location that is specified in the LOCATION attribute of the local LOG_ARCHIVE_DEST_n that you set. You might need to switch the log file at the source database to see files in the directory.

Configure the Database to Archive Standby Redo Log Files Locally

This procedure configures the downstream mining database to archive the standby redo logs that receive redo data from the online redo logs of the source database. Keep in mind that foreign archived logs should not be archived in the recovery area of the downstream mining database.

To Archive Standby Redo Logs Locally:

At the downstream mining database, set the second archive log destination in the LOG_ARCHIVE_DEST_n initialization parameter as shown in the following example.
```
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='LOCATION=/home/arc_dest/srl_dbms1 
VALID_FOR=(STANDBY_LOGFILE,PRIMARY_ROLE)'
```
Oracle recommends that foreign archived logs (logs from remote source databases) be kept separate from local mining database log files, and from each other. You must not use the recovery area of the downstream mining database to stage foreign archived logs.
Enable the LOG_ARCHIVE_DEST_2 parameter you set in the previous step as shown in the following example.
```
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE
```

Parameter Settings for Downstream Extract in Archive- Log-Only Mode

For setting up a downstream Extract in either Archive-Log-Only Mining mode or the Real-Time Mining mode, you must set up the redo transport. Include the following parameters when setting up the downstream Extract in Archive-Log-Only mode:

LOG_ARCHIVE_CONFIG
LOG ARCHIVE DESTINATION (LAD)
- LOG_ARCHIVE_DEST_1 for local backup of all databases
- LOG_ARCHIVE_DEST_2 service for redo transport on the source database
  
  Note:
  LOG_ARCHIVE_DEST_2 is independent from the use of Real Time Mining.
- Local database backup destination is set using the Flash Recovery Area
FAL_SERVER
All the required system parameters can be set dynamically with scope=both

The Archive Log Destination (LAD) parameter settings are required for the local backup destination and redo transport services on the source databases.

Set up the LAD parameters for local backup destination, using the following commands:

ALTER SYSTEM SET db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area';
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
ALTER SYSTEM SET log_archive_dest_1 = 'USE_DB_RECOVERY_FILE_DEST';

Set up the LAD parameter options for redo transport services on the source database using the following commands:

ALTER SYSTEM SET log_archive_config = 'DG_CONFIG = (CDBNORTH, cdbDSC)';
ALTER SYSTEM
      SET log_archive_dest_2='SERVICE=cdbDSC_root
                              ASYNC 
                              NOREGISTER                              
                              VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) 
                              DB_UNIQUE_NAME=cdbDSC';

To learn more about the LOG_ARCHIVE_DEST parameter and its attributes, see LOG_ARCHIVE_DEST_n in the Database Reference guide.

Configure Oracle GoldenGate Extract for Downstream Deployment

In this section, you will learn about the parameters required by Oracle GoldenGate to set up connections with source and downstream databases and configure Extract for downstream database.

Configure Oracle GoldenGate for Downstream Database

The downstream Extract parameter file must include the following parameters for setting up a downstream deployment:

TRANLOGOPTIONS MININGUSERALIAS: Sets the connection to the downstream mining database for internal metadata queries.
FETCHUSERIDALIAS: Connects to the source database for fetching data that cannot be natively extracted out of the transaction log.
TRANLOGOPTIONS INTEGRATEDPARAMS (DOWNSTREAM_REAL_TIME_MINE Y): If set to Y, then Extract captures changes from the standby redo logs files at the downstream database. If not set, then Extract captures from the archive log files.

Following is an example of the Extract parameter file including these parameters:

EXTRACT EXTDSC  
USERIDALIAS cgg_CDBNORTH 
TRANLOGOPTIONS MININGUSERALIAS cgg_cdbDSC 
TRANLOGOPTIONS INTEGRATEDPARAMS (DOWNSTREAM_REAL_TIME_MINE Y)
FETCHUSERIDALIAS cgg_CDBNORTH DOMAIN OracleGoldenGate 

EXTTRAIL Downstream/ea 
SOURCECATALOG CDBNORTH_PDB1
DDL INCLUDE MAPPED
TABLE HR.*;

Follow the given steps to set up the Oracle GoldenGate deployment for downstream Extract:

Add credentials for the source database:

ALTER CREDENTIALSTORE ADD USER c##ggadmin@CDBNORTH_root ALIAS cgg_CDBNORTH DOMAIN OracleGoldenGate PASSWORD password

Add credentials for the downstream database:

ALTER CREDENTIALSTORE ADD USER c##ggadmin@cdbDSC_root ALIAS cgg_cdbDSC DOMAIN OracleGoldenGate PASSWORD password

Use DBLOGIN to connect to the source database and downstream database from Oracle GoldenGate:
```
DBLOGIN USERIDALIAS cgg_CDBNORTH 
```
```
MININGDBLOGIN USERIDALIAS cgg_cdbDSC
```
In this example, cgg_CDBNORTH is the credential alias for the source database CDBNORTH and cgg_cdbDSC is the credential alias for the mining database.
Add and register an Extract, edit the parameter file to include the Extract parameters provided previously, add the Extract trail name and location, start the Extract:
```
ADD EXTRACT extdsc, INTEGRATED TRANLOG, BEGIN NOW
REGISTER EXTRACT extdsc, DATABASE CONTAINER (CDBNORTH_PDB01)

ADD EXTTRAIL Downstream/ea, EXTRACT extdsc
START extdsc
```

Configure Cascaded Downstream Extract Using Active Data Guard

In this section, you will learn about the parameters and configurations required when connecting downstream database to the source database using Active Data Guard (ADG).

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:

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

Now, register an Extract that uses the NOUSERID parameter:

ADD EXTRACT exte, INTEGRATED TRANLOG, BEGIN NOW REGISTER EXTRACT exte DATABASE

After the Extract is registered, you can use this Extract to mine data and start the Extract normally.