Set Up Data Replication with Oracle GoldenGate Microservices Architecture

Use this quickstart to configure data replication using Oracle GoldenGate Microservices Architecture for a multitenant container database with two pluggable databases to demonstrate data replication from an Oracle to Oracle database in a HUB configuration.

Note:

This quickstart does not perform an initial load instantiation and assumes that the tables and data are the same in the source and target endpoints.

Workflow showing data replication for an on-premises Oracle GoldenGate deployment.

The source and target databases in this diagram refer to the container and pluggable databases (PDBs).

Container Database (CDB$ROOT) Process Names	Pluggable Database (DBEAST) Process Names	Pluggable Database (DBWEST) Process Name
`CDB$ROOT` database user: `c##ggadmin` Database credential alias: `cggnorth`	Database user: `ggadmin` Database alias: `ggeast` Extract: `exte`	Database user: `ggadmin` Database alias: `ggwest` Extract: `extw`

Configure and Set Privileges for Oracle Multitenant Database

In Oracle database, you need to enable replication for Oracle GoldenGate and assign privileges to the database user at the CDB level and the pluggable database (PDB) level.

The database is in ARCHIVELOG mode and FORCE LOGGING and Supplemental Logging is enabled. For the container database, assign the following privileges to the common user (cdb$root):

CDB User Privileges

## CGGNORTH DATABASE SETUP AT CDB LEVEL
ALTER SESSION SET CONTAINER=cdb$root;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE;
ALTER SYSTEM SET STREAMS_POOL_SIZE=2G;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ARCHIVE LOG LIST;
CREATE TABLESPACE GG_DATA DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M;
CREATE USER c##ggadmin IDENTIFIED BY PASSWORD CONTAINER=ALL DEFAULT TABLESPACE GG_DATA TEMPORARY TABLESPACE TEMP;
GRANT ALTER SYSTEM TO c##ggadmin CONTAINER=ALL;
GRANT DBA TO c##ggadmin CONTAINER=ALL;
GRANT CREATE SESSION TO c##ggadmin CONTAINER=ALL;
GRANT ALTER ANY TABLE TO c##ggadmin CONTAINER=ALL;
GRANT RESOURCE TO c##ggadmin CONTAINER=ALL;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE(' c##ggadmin ',CONTAINER=>'ALL');

Source PDB User Privileges (DBEAST)

ALTER SESSION SET CONTAINER=dbeast;
CREATE TABLESPACE GG_DATA DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON NEXT 100M;
CREATE USER ggadmin IDENTIFIED BY PASSWORD CONTAINER=CURRENT; 
GRANT CREATE SESSION TO ggadmin CONTAINER=CURRENT;
GRANT ALTER ANY TABLE TO ggadmin CONTAINER=CURRENT;
GRANT RESOURCE TO ggadmin CONTAINER=CURRENT;
GRANT DBA TO ggadmin CONTAINER=CURRENT;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

Target PDB User Privileges (DBWEST):

ALTER SESSION SET CONTAINER=dbwest;
CREATE USER ggadmin IDENTIFIED BY PASSWORD CONTAINER=CURRENT;
GRANT ALTER SYSTEM TO ggadmin CONTAINER=CURRENT;
GRANT CREATE SESSION TO ggadmin CONTAINER=CURRENT;
GRANT ALTER ANY TABLE TO ggadmin CONTAINER=CURRENT;
GRANT RESOURCE TO ggadmin CONTAINER=CURRENT;
GRANT DBA TO ggadmin CONTAINER=CURRENT;
GRANT DV_GOLDENGATE_ADMIN, DV_GOLDENGATE_REDO_ACCESS TO GGADMIN CONTAINER=CURRENT;
EXEC DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ggadmin');

Note:

Granting DBA role is not mandatory for every user. Privileges should be granted depending on the actions that the user needs to perform on the database. For example, to grant DML operation privileges to insert, update, and delete transactions to ggadmin, use the

GRANT
          ANY INSERT/UPDATE/DELETE

privileges and to further allow users to work with tables and indexes as part of DML operations, use the

GRANT CREATE/DROP/ALTER ANY
          TABLE/INDEX

privileges. In this quickstart, the assumption is that the database user is a database administrator. See Grant User Privileges for Oracle Database 21c and Lower and Configure a Multitenant Container Database to know more about specific privilege requirements.

Configure the Replication Process from Oracle GoldenGate MA Web Interface

Data replication processes include Extracts, Replicats processes, along with Distribution Paths (DISTPATH) or Receiver Paths or Target-Initiated Paths (RECVPATH).

Using the following steps, you'll be able to configure data capture (Extract) and apply (Replicat) processes. You'll also be able to test if the replication has started. The DISTPATH process is not used for this configuration.

Step 1: Add Database Credentials from the Administration Service

In this section, you'll add the database credentials to connect to the source and target databases using EZConnect.

Keep your database user credentials, which created in the previous session, ready. You'll use them to connect Oracle GoldenGate to the database server.
Open the Service Manager login page in a web browser and log in to the Service Manager with your Oracle GoldenGate administrator user credentials. If logging in for the first time, you have to log in with the administrator account user credentials, created when adding your deployment with Oracle GoldenGate Configuration Assistant wizard.
From the Service Manager Overview page, click the port number for the Administration Service of the deployment.

This opens the Administration Service login page.
Log in to the Administration Service using the same credentials, which you used to log in to the Service Manager. The Administration Service Overview page is displayed.
Click the Application Navigation icon to open the left-navigation pane and click Configuration to open the Database tab of the Configuration page.
Click the plus (+) sign in the Credentials section to begin adding database user credentials.
You need to add connections for container database (CDB) and pluggable databases (PDBs). Each CDB is used to capture (Extract) from the source database and PDB for delivery (Replicat).

Use the EZconnect syntax to configure the database connection. You need the username, password, hostname, port number, and service name connection information to use the EZConnect syntax.

Here's the syntax that you need to specify in the User ID field:

username@hostname:port/service_name

Here's an example for setting the User ID with EZConnect:

c##ggadmin@dc.example.com:1521/dc1.example.com

The following screen shows the database credential (cggnorth) for connecting to the user c#ggadmin added to the credentials list. You can also see the credentials being added for the Alias (ggeast) for connecting to the DBEAST PDB.
Click on the blue icon in the Actions column to connect to the database. The icon turns blue when the connection is successful.

After connecting to the database, the sections to add checkpoint table, TRANDATA, and heartbeat table are displayed.

Step 2: Add TRANDATA, Heartbeat, and Checkpoint Tables

In this section, you will add TRANDATA for the source database to enable writing information to the redo logs. This would ensure that the rows added to the source database are uniquely identified on the target database and are updated. You'll add heartbeat tables for the source and target databases to monitor any possible lags. You will also add a checkpoint table for the target database to ensure that if there is a failure, then the Extract and Replicat processes can restart from the point of failure.

Add TRANDATA to the source connection. Use the TRANDATA Information section to set up database logging properties. This is an essential step to enable supplemental logging and ensure that the data is written to the database redo log.

After you add the trandata, you can search for the schema for which you've add the trandata, using the search icon. This will display the trandata information. The following image shows the trandata information for the HR schema in the pluggable database DBEAST.

See Configure Logging Properties to learn the steps for configuring the logging properties at the Schema, Table or Procedure level.
To set up the checkpoint table for Replicat, you need to connect to the target database credentials (ggwest) from the Credentials section.
Click the plus sign (+) to add the checkpoint table for the target (pluggable) database.

Click Submit. The checkpoint table is added.

Also see the Before Adding an Extract section, for details on creating heartbeat tables.
Add the heartbeat tables for both source and target endpoints by connecting to ggeast and ggwest database credential aliases. Add the heartbeat table by clicking the plus sign.
Click Submit after adjusting the heartbeat options.

Step 3: Add an Extract

In this section, you will add an Extract process (exte). The Extract process captures data from the source database and writes it to a trail file (ea).

Click the Overview option from the left-navigation pane of the Administration Service and click the plus sign (+) from the Extract section.
From Add Extract wizard, select Integrated Extract.

Note:
Before creating Replicat, you need to create an initial load Extract when starting the replication process for the first time. To learn about the initial load Extract and it's use case, see Add Initial Load Extract Using the Admin Client.
Click Next and specify the Extract options in the Extract Options screen. See the detailed steps to add an Extract from the Add a Primary Extract section.

If you are creating the Extract for a pluggable database, then you'll see option Register to PDBs as soon as you enter the credentials domain and alias. Select the PDB in the container database that you want to use for replication.
After you enter the options for the Extract, click Next. The next screen displays the Extract parameter file to help you review the Extract settings.
Here's the Extract parameter file for the Extract exte:
```
EXTRACT exte
USERIDALIAS cggnorth DOMAIN OracleGoldenGate
EXTTRAIL east/ea
SOURCECATALOG DBEAST
DDL INCLUDE MAPPED
TABLE hr.*;
```
Review these settings and update the Extract configuration as needed.

For multitenant databases, you need to add entries for Extract to capture from multiple pluggable databases to a single trail. In the parameter file, source objects must be specified in TABLE and SEQUENCE statements with their fully qualified three-part names in the format of container.schema.object or using the SOURCECATALOG parameter with two-part names schema.object.
Click Create and Run to start your Extract.

Step 4: Add a Replicat

In this section, you will add a Replicat process (repe). The Replicat process delivers the change data from the trail file (ea) created by the Extract, to the target database. Replicat reads the trail file on the target database, reconstructs the DML or DDL operations, and applies them to the target database.

Before you Add a Replicat, make sure that you added your checkpoint table for the target database (DBWEST) by connecting to the ggwest database credentials.
Select a Replicat type to deliver data to the target database. Follow the wizard to complete adding a Replicat. See Add a Replicat.
Enter the Parallel Nonintegrated Replicat options in the Replicat Options screen.
Click Next to view the Replicat Parameter File screen. All the parameters that you have specified are available for review here.
For multitenant container databases, Replicat can only apply to one pluggable database. To specify the correct one, use a SQL*Net connect string for the database user that you specify with the USERID or USERIDALIAS parameter. For example: ggadmin@DBWEST. In the parameter file, specify only the schema.object in the TARGET portion of the MAP statements. In the MAP portion, identify source objects captured from more than one pluggable database with their three-part names or use the SOURCECATALOG parameter with two-part names.

Here's a sample of the Replicat Parameter File:
```
REPLICAT repe
USERIDALIAS ggwest DOMAIN OracleGoldenGate
--DDL EXCLUDE ALL
DDLERROR default discard
REPERROR (default,discard)
DDLOPTIONS REPORT
SOURCECATALOG DBEAST
MAP hr.*, TARGET hr.*;
```

After the Replicat starts successfully, you can see the Extract and Replicat processes in running state on the Administration Service Overview page.

Administration Service Overview page with Extract and Replicat in running state.

Step 5: Test the Replication

To test if the replication has started, try insert, update, or delete operations on your database and then follow these steps:

Click Action from the Extract (exte) section and click Details.
Click the Statistics tab. You'll see additions to the Insert, Updates, or Deletes columns on this page.

Also see the Statistics tab using the Replicat Details option. You would see the updates in the Table Statistics section.

Parent topic: Quickstarts