Task 4: Configure the Oracle GoldenGate Environment
Step 4.1 - Create Database Credentials
With the Oracle GoldenGate deployment created, use the Oracle GoldenGate Administration Service home page to create the database credentials using the above TNS alias names. See figure 4 below for an example of the database credential creation using the TNS alias.
From a client machine with access to the GGHUB, create a ssh tunnel to connect to the Oracle GoldenGate Administration Service:
$ ssh -N -L <local_port>:<vip>:443 -p 22 <gghub-node>
As the oggadmin user, create the database credentials:
- Log in into the Administration Service: https://localhost:<localPort>/<instance_name>/adminsrvr.
- Click Configuration under Administration Service.
- Click the plus button to Add Credentials under the Database tab.
- Add the required information for the source and target CDB and PDB
as shown in the table:
Region Container Domain Alias User ID Region 1 CDB GoldenGate Reg1_CDB c##ggadmin@<tns_alias> Region 1 PDB GoldenGate Reg1_PDB ggadmin@<tns_alias> Region 2 CDB GoldenGate Reg2_CDB c##ggadmin@<tns_alias> Region 2 PDB GoldenGate Reg2_PDB ggadmin@<tns_alias>
Step 4.2 - Set Up Schema Supplemental Logging
- Log in to the Oracle GoldenGate Administration Server.
- Click Configuration under Administration Service.
- Click the Connect to database button under Actions for the Source Database (Reg_CDB).
- Click the plus button (Add TRANDATA) to Add TRANDATA for the Schema or Tables.
Step 4.3 - Create the Autostart Profile
Create a new profile to automatically start the Extract and Replicat processes when the Oracle GoldenGate Administration Server is started. Then, restart if any Extract or Replicat processes are abandoned. With GoldenGate Microservices, auto start and restart is managed by Profiles.
Using the Oracle GoldenGate Administration Server GUI, create a new profile that can be assigned to each of the Oracle GoldenGate processes:
- Log in to the Administration Service on the Source and Target GoldenGate.
- Click on Profile under Administration Service.
- Click the plus (+) sign next to Profiles on the Managed Process Settings home page.
- Enter the details as follows:
- Profile Name: Start_Default
- Description: Default auto-start/resteart profile
- Default Profile: Yes
- Auto Start: Yes
- Auto Start Options
- Startup Delay: 1 min
- Auto Restart: Yes
- Auto Restart Options
- Max Retries: 5
- Retry Delay: 30 sec
- Retries Window: 30 min
- Restart on Failure only: Yes
- Disable Task After Retries Exhausted: Yes
- Click Submit
Step 4.4 - Configure Oracle GoldenGate Processes
When creating Extract, Distribution Paths, and Replicat processes with Oracle GoldenGate Microservices Architecture, all files that need to be shared between the GGHub nodes are already shared with the deployment files stored on a shared file system.
Listed below are essential configuration details recommended for running Oracle GoldenGate Microservices on GGhub for Extract, Distribution Paths, and Replicat processes.
Perform the following sub-steps to complete this step:
- Step 4.4.1 - Extract Configuration
- Step 4.4.2 - Replicat Configuration
- Step 4.4.3 - Distribution Path Configuration
- Step 4.4.4 - Set up a Heartbeat Table for Monitoring Lag Times
The main goal is to prevent data divergence between GoldenGate replicas and their associated standby databases. This section focuses on configuring Extract so that GoldenGate Extract never gets ahead of the standby database which can result in data divergence.
GoldenGate Parameter | Description | Recommendations |
---|---|---|
|
This is mandatory setting for Data Guard configurations that have Oracle GoldenGate to ensure GoldenGate Extract never extract data that has not been received by standby database. The HANDLEDLFAILOVER stands for handle DATA LOSS for Data Guard failover. The following parameter must be added to the Extract process parameter fileto avoid losing transactions and resulting in logical data inconsistencies after data loss Data Guard failover event. When the two primary tried to reconcile, this parameter ensures that all transactions can be reconciled since the new primary (old standby) is not further behind as expected. Prevents Extract from extracting redo data from the source database, and writing to the trail file data that has not yet been applied to the Oracle Data Guard standby database. If this parameter is not specified, after a data loss failover, it is possible to have data in the target database that is not present in the source database, leading to data divergence and logical data inconsistencies. |
MANDATORY when the source database is configured with Data Guard in Max Availaibility or Max Performance mode. |
|
For multiple standby configurations or cases when
Data Guard Fast-Start failover is not enabled, set
When not using Data Guard Fast Start Failover (FSFO) in the source database, this parameter Identifies which standby database the Extract process must remain behind, with regard to not extracting redo data that has not yet been applied to the Oracle Data Guard standby database. |
MANDATORY when not using FSFO in the source database. To determine the correct value for
|
|
The amount of time before a warning message is written to the Extract report file, if Extract is stalled, due to being unable to query the source database standby apply progress. This can occur after a Data Guard failover when the old primary database is not currently available. The default is 60 seconds. |
OPTIONAL if want to adjust the timing of when the warning message is written to the Extract report file. Add |
|
The amount of time before Extract abends, if Extract is stalled, due to being unable to query the standby apply progress. The default is 30 minutes. |
OPTIONAL if want to adjust the amount of time it
takes Extract to abend, when the source database standby is not
accessible to enforce the Add |
|
The amount of time Extract will run on the new source primary database, after a Data Guard role transition, before it will check the status of the standby database. If standby database is not available after the DLFAILOVER_TIMEOUT, Extract will abend. The default is 300 seconds. NOTE: If during normal operations of the source Oracle Data Guard configuration, the standby database becomes unavailable, Extract will stop extracting data from the source database to prevent possible data divergence with the GoldenGate target database due to the HANDLEDLFAILOVER parameter. The DLFAILOVER_TIMEOUT parameter does not take effect when a Data Guard failover has not occurred, and there are no messages output to the Extract report file. |
OPTIONAL. if you want to adjust the amount of time an Extract can
run on a new primary source database, after a role transition,
when the standby is not yet available to honor the
|
Refer to the Reference for Oracle
GoldenGate for more information about the Extract
TRANLOGOPTIONS
parameters.
When creating an Extract using the Oracle GoldenGate Administration Service GUI interface, leave the Trail SubDirectory parameter blank so that the trail files are automatically created in the deployment directories stored on the shared file system. The default location for trail files is the /<deployment directory>/var/lib/data directory.
Note:
To capture from a multitenant database, you must use an Extract configured at the root level using a c## account. To apply data into a multitenant database, a separate Replicat is needed for each PDB because a Replicat connects at the PDB level and doesn't have access to objects outside of that PDB.Step 4.4.1 - Extract Configuration
Create the Extract:
- Log in to the Oracle GoldenGate Administration Server.
- Click Overview under Administration Service.
- Click the plus button to Add Extract.
- Select Integrated Extract.
- Add the required information as follows:
- Process Name: EXT_1
- Description: Extract for Region 1 CDB
- Intent: Unidirection
- Begin: Now
- Trail Name: aa
- Credential Domain: GoldenGate
- Credential Alias: Reg1_CDB
- Register to PDBs: PDB Name
- Click Next and set
parameters.
EXTRACT ext_1 USERIDALIAS Reg1_CDB DOMAIN GoldenGate EXTTRAIL aaTRANLOGOPTIONS HANDLEDLFAILOVER TRANLOGOPTIONS FAILOVERTARGETDESTID 2 SOURCECATALOG PDB_NAME TABLE OWNER.*;
- Click Next.
- If using CDB Root Capture from PDB, add the
SOURCECATALOG
parameter with the PDB Name. - Click Create and Run.
Note:
For ADB-D deployments, the extract requires a connection to the PDB rather than the CDB.See Oracle GoldenGate Extract Failure or Error Conditions Considerations for more information.
Step 4.4.2 - Replicat Configuration
Oracle generally recommends using integrated parallel Replicat which offers better apply performance for most workloads when the GGHub is in the same region as the target Oracle GoldenGate database.
The best apply performance can be achieved when the network latency between the GGHub and the target database is as low as possible. The following configuration is recommended for the remote Replicat running on the Oracle GGHub.
APPLY_PARALLELISM
– Disables automatic parallelism, instead of usingMAX_APPLY_PARALLELISM
andMIN_APPLY_PARALLELISM
, and allows the highest amount of concurrency to the target database. It is recommended to set this as high as possible based on available CPU of the hub and the target database server.MAP_PARALLELISM
– Should be set with a value of 2 to 5. With a larger number of appliers, increasing the Mappers increases the ability to hand work to the appliers.BATCHSQL
– applies DML using array processing which reduces the amount network overheads with a higher latency network. Be aware that if there are many data conflicts, BATCHSQL results in reduced performance, as rollback of the batch operations followed by a re-read from trail file to apply in non-batch mode.
Add a Replicat:
After you’ve set up your database connections and verified them, you can add a Replicat for the deployment by following these steps:
- Log in to the Oracle GoldenGate Administration Server.
- Click theplus (+) sign next to Replicats on the Administration Service home page. The Add Replicat page is displayed.
- Select a Replicat type and click Next.
- Enter the details as follows:
- Process Name: REP_1
- Description: Replicat for Region 2 PDB
- Intent: Unidirectional
- Credential Domain: GoldenGate
- Credential Alias: Reg2_PDB
- Source: Trail
- Trail Name: aa
- Begin: Position in Log
- Checkpoint Table: "GGADMIN"."CHKP_TABLE"
- Click Next.
-
From the Action Menu, click Details to edit the Replicat Parameters:
REPLICAT REP_1 USERIDALIAS Reg2_PDB DOMAIN GoldenGate MAP <SOURCE_PDB_NAME>.<OWNER>.*, TARGET <OWNER>.*;
- From the Action Menu, click Start.
Step 4.4.3 - Distribution Path Configuration
Distribution paths are only necessary when trail files need to be sent to an additional Oracle GoldenGate Hub in a different, or even the same, region as described in the following figure.
Figure 20-4 Oracle GoldenGate Distribution Path
![Oracle GoldenGate Distribution Path Oracle GoldenGate Distribution Path](img/gghub-trail-distribution-path.png)
When using Oracle GoldenGate Distribution paths with the NGINX Reverse Proxy, additional steps must be carried out to ensure the path client and server certificates are configured.
More instructions about creating distribution paths are available in Using Oracle GoldenGate Microservices Architecture. A step-by-step example is in the following video, “Connect an on-premises Oracle GoldenGate to OCI GoldenGate using NGINX,” to correctly configure the certificates.
Here are the steps performed in this sub-step:
- Step 4.4.3.1 - Download the Target Server’s Root Certificate, and then upload it to the source Oracle GoldenGate
- Step 4.4.3.2 - Create a user in the Target Deployment for the Source Oracle GoldenGate to use
- Step 4.4.3.3 - Create a Credential in the Source Oracle GoldenGate
- Step 4.4.3.4 - Create a Distribution Path on the Source Oracle GoldenGate to the Target Deployment
Step 4.4.3.1 - Download the Target Server’s Root Certificate, and then upload it to the source Oracle GoldenGate
Download the target deployment server’s root certificate and add the CA certificate to the source deployment Service Manager.
- Log in to the Administration Service on the Target GoldenGate.
- Follow “Step 2 - Download the target server’s root certificate” in the video “Connect an on-premises Oracle GoldenGate to OCI GoldenGate using NGINX.”
Step 4.4.3.2 - Create a user in the Target Deployment for the Source Oracle GoldenGate to use
Create a user in the target deployment for the distribution path to connect to:
- Log in to the Administration Service on the Target GoldenGate.
- Click on Administrator under Administration Service.
- Click the plus (+) sign next to Users.
- Enter the details as follows:
- Username: ggnet
- Role: Operator
- Type: Password
- Click Submit
Step 4.4.3.3 - Create a Credential in the Source Oracle GoldenGate
Create a credential in the source deployment connecting the target deployment with the user created in the previous step. For example, a domain of OP2C and an alias of WSSNET.
- Log in to the Administration Service on the Source Oracle GoldenGate.
- Click in Configuration under Administration Service.
- Click the plus (+) sign next to Credentials on the Database home page.
- Enter the details as follows:
- Credential Domain: OP2C
- Credential Alias: wssnet
- User ID: ggnet
- Click Submit
Step 4.4.3.4 - Create a Distribution Path on the Source Oracle GoldenGate to the Target Deployment
A path is created to send trail files from the Distribution Server to the Receiver Server. You can create a path from the Distribution Service. To add a path for the source deployment:
- Log in to the Distribution Service on the Source Oracle Goldengate.
- Click the plus (+) sign next to Path on the Distribution Service home page. The Add Path page is displayed.
- Enter the details as follows:
Option Description Path Name
Select a name for the path.
Source: Trail Name
Select the Extract name from the drop-down list, which populates the trail name automatically. If it doesn’t, enter the trail name you provided while adding the Extract.
Generated Source URI
Specify localhost for the server’s name; this allows the distribution path to be started on any of the Oracle RAC nodes.
Target Authentication Method
Use ‘UserID Alias’ Target Set the Target transfer protocol to wss (secure web socket). Set the Target Host to the target hostname/VIP that will be used for connecting to the target system along with the Port Number that NGINX was configured with (default is 443).
Domain
Set the Domain to the credential domain created above in Step 11.3.3, for example, OP2C.
Alias
The Alias is set to the credential alias wssnet, also created in Step 11.3.3.
Auto Restart Options
Set the distribution path to restart when the Distribution Server starts automatically. This is required, so that manual intervention is not required after a RAC node relocation of the Distribution Server. It is recommended to set the number of Retries to 10. Set the Delay, which is the time in minutes to pause between restart attempts, to 1.
- Click Create Path.
- From the Action Menu, click Start.
Step 4.4.4 - Set up a Heartbeat Table to Monitor Lag Times
Follow Steps to add Heartbeat Table in OCI GoldenGate to implement the best practices for creating a heartbeat process that can be used to determine where and when lag are developing between a source and target system.
This document walks you through the step-by-step process of creating the necessary tables and added table mapping statements needed to keep track of processing times between a source and target database. Once the information is added into the data flow, the information is then stored in a target table that can be analyzed to determine when and where the lag is being introduced between the source and target systems.