Set Up Data Replication with Oracle GoldenGate Microservices Architecture
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.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 |
---|---|---|
|
|
|
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
):
## 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');
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');
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:
GrantingDBA
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 theDBEAST
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 databaseDBEAST
.
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 Extract and Replicat Processes 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
andSEQUENCE
statements with their fully qualified three-part names in the format ofcontainer.schema.object
or using theSOURCECATALOG
parameter with two-part namesschema.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
orUSERIDALIAS
parameter. For example:ggadmin@DBWEST
. In the parameter file, specify only theschema.object
in theTARGET
portion of theMAP
statements. In theMAP
portion, identify source objects captured from more than one pluggable database with their three-part names or use theSOURCECATALOG
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.
Step 5: Test the Replication
-
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.