Task 4: Configure the Oracle GoldenGate Environment

Perform the following steps to complete this task:

Step 4.1 - Create the Database Credentials
Step 4.2 - Set Up Schema Supplemental Logging
Step 4.3 - Create the Autostart Profile
Step 4.3 - Configure Oracle GoldenGate Processes

Step 4.1 - Create the 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.

As the oggadmin user, create the database credentials:

Log in into the Administration Service: https://gghub.example.com:443/deployment_name/adminsrvr
Click Configuration under Administration Service.
Click the plus (+) to Add Credentials under the Database tab.

Add the required information for the source and target CDB and PDB:

Data Center	Container	Domain	Alias	User ID
DC 1	CDB	GoldenGate	DC1_CDB	c##ggadmin@<tns_alias>
DC 1	PDB	GoldenGate	DC1_PDB	ggadmin@<tns_alias>
DC 2	CDB	GoldenGate	DC2_CDB	c##ggadmin@<tns_alias>
DC 2	PDB	GoldenGate	DC2_PDB	ggadmin@<tns_alias>

Step 4.2 - Setup 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/restart 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.

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
`TRANLOGOPTIONS HANDLEDLFAILOVER`	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.
`TRANLOGOPTIONS FAILOVERTARGETDESTID n`	For multiple standby configurations or cases when Data Guard Fast-Start failover is not enabled, set FAILOVERTARGETDESTID to standby demarcated by LOG_ARCHIV_DEST to ensure GoldenGate Extract never extract data that has not been received by target standby database. To determine the correct value for FAILOVERTARGETDESTID, use the LOG_ARCHIVE_DEST_N parameter from the GoldenGate source database which is used for sending redo to the source standby database. For example, if LOG_ARCHIVE_DEST_2 points to the standby database, then use a value of 2. 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 FAILOVERTARGETDESTID, use the LOG_ARCHIVE_DEST_N parameter from the GoldenGate source database which is used for sending redo to the source standby database. For example, if LOG_ARCHIVE_DEST_2 points to the standby database, then use a value of 2.
`TRANLOGOPTIONS HANDLEDLFAILOVER STANDBY_WARNING value`	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 STANDBY_WARNING <value> to the TRANLOGOPTIONS HANDLEDLFAILOVER parameter.
`TRANLOGOPTIONS HANDLEDLFAILOVER STANDBY_ABEND value`	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 HANDLEDLFAILOVER parameter. Add STANDBY_ABEND <value> to the TRANLOGOPTIONS HANDLEDLFAILOVER parameter.
`TRANLOGOPTIONS DLFAILOVER_TIMEOUT value`	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 TRANLOGOPTIONS HANDLEDLFAILOVER parameter.

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

Log in to the Oracle GoldenGate Administration Server
Click in 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 DC 1 CDB
- Intent: Unidirectional
- Begin: Now
- Trail Name: aa
- Credential Domain: GoldenGate
- Credential Alias: DC1_CDB
- Register to PDBs: PDB Name

Click Next and set parameters:

EXTRACT ext_1
USERIDALIAS DC1_CDB DOMAIN GoldenGate
EXTTRAIL aa
TRANLOGOPTIONS 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
For Oracle Data Guard configurations, add the TRANLOGOPTIONS parameter, if required, as explained earlier in this step:
- Add the parameter TRANLOGOPTIONS HANDLEDLFAILOVER
- Add the parameter TRANLOGOPTIONS FAILOVERTARGETDESTID <log_archive_dest_numer> only if Oracle Data Guard Fast-Start Failover (FSFO) is NOT in use.
Click Create and Run.

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 using MAX_APPLY_PARALLELISM and MIN_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.

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 DC 2 PDB
- Intent: Unidirectional
- Credential Domain: GoldenGate
- Credential Alias: DC2_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, data center as described in the following figure.

Figure 23-4 Oracle GoldenGate Distribution Path

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.5 - Distribution Path Recommendations

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

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, for example, OP2C.
Alias	The Alias is set to the credential alias wssnet.
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.3.5 - Distribution Path Recommendations

If there are any GoldenGate distribution paths sending trail files to the GGHub, after a role transition of the GGHub, the paths will need to be altered to send the trail files to the new primary GGHub system. This can be done using the following example REST call:

curl -s -K src_access.cfg
 https://Source_VIP/Source_Deployment_Name/distsrvr/services/v2/sources/Distribution_Path_Name
 -X PATCH --data '{"target":{"uri":"ogg://Target_VIP:9103/services/v2/targets?trail=dd"}}' | python
 -m json.tool

You can automate changing the source distribution path target address after a hub role transition using the sample shell script shown in Managing Planned and Unplanned Outages for Oracle GoldenGate Hub which is called by the acfs_standby CRS action script when a file system switchover/failover occurs.

The source distribution paths must be configured to restart automatically after it has failed so that if the target GoldenGate deployment relocates between Oracle RAC nodes or to the standby hub, the distribution paths will restart. If a distribution path was created without automatic restart enabled, it can be enabled through the distribution server web UI or a REST call. For example:

$ curl -s -K
 access.cfg https://<Source VIP>/<Source Deployment Name>/distsrvr/services/v2/sources/ggs_to_gghub
 -X PATCH --data '{"options":{"autoRestart":{"delay": 2,"retries": 10}}}' | python -m json.tool

To check the current configuration of a distribution path, use the following example:

$ curl -s -K
 access.cfg https://<Source VIP>/<Source Deployment Name>/distsrvr/services/v2/sources/ggs_to_gghub
 -X GET | python -m json.tool 

# Sample output:
"name": "scam_to_gghub", 
  "options": { 
     "autoRestart": { 
     "delay": 2, 
     "retries": 10 
  },

Step 4.4.4 - Set up a Heartbeat Table for Monitoring Lag Times

Use the instructions in 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 is developing between a source and target system.

This document guides 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 into a target tables that can be analyzed to determine when and when the lag is introduced between the source and target systems.