Replicate data from YugabyteDB to Autonomous AI Transaction Processing

Learn to replicate data from a YugabyteDB source to an Autonomous AI Transaction Processing target using OCI GoldenGate.

Before you begin

To successfully complete this quickstart, ensure you have:

A YugabyteDB installation to serve as the source database
An Autonomous AI Transaction Processing instance to serve as the target database.
Prepared YugabyteDB for Oracle GoldenGate

Set up YugabyteDB

To set up your YugabyteDB instance:

If using Oracle Cloud Compute to host YugabyteDB, open port 5433:

sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload
sudo firewall-cmd --list-all

Open port 5433 in your VCN's security list.
Connect to YugabyteDB.
Downlaod the sample data.

Run the following commands to set up the user. Ensure that you replace the <password> placeholder with an actual password.

create user ggadmin with password '<password>';
alter user ggadmin with SUPERUSER;
GRANT ALL PRIVILEGES ON DATABASE ociggll TO ggadmin;

Environment set up: Autonomous AI Transaction Processing (ATP)

Download and unzip the sample database schema.
Unlock the GGADMIN user.
1. In the Oracle Cloud console, select your ATP instance from the Autonomous AI Databases page to view its details and access Database Actions.
2. Select Database Actions, and then select Database Users.
3. Locate GGADMIN, and then select its ellipsis menu (three dots) and select Edit.
4. In the Edit User panel, enter the GGADMIN password, confirm the password, and then deselect Account is Locked.
5. Select Apply Changes.
Load the target sample schema and data.
1. From the Database Actions menu, select SQL.
2. Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet.
3. Select Run Script. The Script Output tab displays confirmation messages.
4. Clear the SQL worksheet and then copy and paste the SQL from OCIGGLL_OCIGGS_SRC_MIRROR_USER_SEED_DATA.sql.
5. To verify that the tables were created successfully, close the SQL window and reopen it again. In the Navigator tab, look for the SRC_OCIGGLL schema and then select tables from their respective dropdowns.

Task 1: Create the OCI GoldenGate resources

This quickstart example requires deployments and connections for both the source and target.

Create a deployment for the source PostgreSQL database.
Create a deployment for the target Autonomous AI Transaction Processing instance.
Create a connection to the source PostgreSQL database.
1. For Type, ensure that you select YugabyteDB.
2. For Database name, enter the YugabyteDB name.
3. For Host, enter the public IP of the Compute instance that YugabyteDB runs on.
4. For Port, enter 5433.
5. For Username, enter ggadmin.
6. For Password, enter a password.
7. For Security Protocol, select Plain.
Create connection for the target Autonomous AI Transaction Processing instance.
Create a connection to GoldenGate, and then assign this connection to the source PostgreSQL deployment.
Assign the source connection to the source PostgreSQL deployment.
Assign the target connection to the target Oracle deployment.

Task 2: Enable supplemental logging

To enable supplemental logging:

Launch the PostgreSQL GoldenGate deployment console:
1. From the Deployments page, select the PostgreSQL deployment to view its details.
2. On the PostgreSQL deployment details page, select Launch console.
3. On the deployment console sign in page, enter the GoldenGate admin credentials provided in Task 1, step 1.
  
  Note: Sign in is required if IAM wasn’t selected as the credential store when creating a deployment.
In the deployment console, select DB Connections in the left navigation, then the source PostgreSQL database, and then Trandata.
On the Trandata page, next to TRANDATA Information, select Add Trandata (plus icon).
In the Trandata panel, for Schema Name, enter src_ociggll, and then select Submit.

Note: Use the search field to search for src_ociggll and verify the tables were added.

Task 3: Create the Extracts

Add the Change Data Capture Extract:
1. In the left navigation, select Extracts,
2. On the Extracts page, select Add Extract (plus icon), and then complete the fields as follows:
  - On the Extract Information page:
    1. For Extract type, select Change Data Capture Extract.
    2. For Process Name, enter a name for the Extract, such as ECDC.
    3. Select Next.
  - On the Extract Options page:
    1. For Source credentials, select Oracle GoldenGate from the Domain dropdown
    2. Select the source PostgreSQL database from the Alias dropdown.
    3. For Extract Trail Name, enter a two-character trail name, such as C1.
    4. Select Next.
  - On the Extract Parameters page, replace MAP *.*, TARGET *.*; with the following:
```
TABLE SRC_OCIGGLL.*;
```
3. Select Create and Run.
Add the Initial Load Extract:
1. On the Extracts page, select Add Extract, and then complete the Add Extract form as follows:
  - On the Extract Information page:
    1. For Extract type, select Initial Load Extract.
    2. For Process Name, enter a name, such as EIL.
    3. Select Next.
  - On the Extract Options page:
    1. For Source credentials, select Oracle GoldenGate from the Domain dropdown.
    2. Select the PostgreSQL database from the Alias, dropdown.
    3. For Extract Trail Name, enter a two-character trail name, such as I1.
    4. Select Next.
  - On the Extract Parameters page, replace the contents of the textarea with the following:
```
EXTRACT EIL
USERIDALIAS PostgreSQL_Compute, DOMAIN OracleGoldenGate
EXTFILE I1, PURGE
TABLE src_ociggll.*;
```
    Note: Ensure that you remove the SOURCEDB parameter in front of USERIDALIAS before you move on.
2. Select Create and Run.

You return to the Extracts page, where you can observe the Extract starting.

Task 4: Create the Distribution Path for Initial Load Extract

On the deployment details page, select Launch Console, and then log in as the admin user.
If using IAM credential store, proceed to the Create a Distribution Path step. If using GoldenGate credential store, create a user with which the source GoldenGate uses to connect to the target GoldenGate.
1. In the navigation menu, select User Administration.
2. Select Add New User (plus icon), complete the fields as follows, and then select Submit:
  - For Username, enter ggsnet.
  - For Role, select Operator.
  - Enter the password twice for verification.
In the source PostgreSQL deployment console, create a Path Connection for the user created in the previous step.
1. In the navigation menu, select Path Connections.
2. Select Add Path Connection (plus icon), complete the fields as follows, and then select Submit:
  - For Credential Alias, enter dpuser.
  - For User ID, enter ggsnet
  - For Password, enter the same password used in the previous step.
Create a Distribution Path.
1. In the service menu bar, select Paths, and then select Add Distribution Path (plus icon).
2. Complete the Add Path form as follows:
  - On the Path Information page:
    1. For Path Name, enter a name for this path.
    2. Select Next.
  - On the Source Options page:
    1. For Source Extract, leave blank.
    2. For Trail Name, enter the Initial Load Extract trail name (I1).
    3. Select Next.
  - On the Target Options page:
    1. For Target, select wss.
    2. For Target Host, enter the target deployment URL, without the https:// or any trailing slashes.
    3. For Port Number, enter 443.
    4. For Trail Name, enter I1.
    5. For Target Authentication Method, select UserID Alias.
    6. For Domain, enter the domain name created in the previous step.
    7. For Alias, enter the alias created in the previous step (dpuser).
    8. Select Next.
3. Select Create and Run.
You return to the Distribution Service page where you can review the path created.
In the target deployment console, review the Receiver path created as a result of the Distribution path:
1. Select Paths.
2. Review the Path details.

Task 5: Add a Replicat for Initial Load

In the target Oracle deployment, add a Checkpoint table.
1. In the navigation menu, select DB Connections
2. Select the target Autonomous AI Database instance, and then Checkpoints.
3. On the Checkpoints page, select Add Checkpoint (plus icon).
4. In the Checkpoint panel, for Schema Table, enter SRCMIRROR_OCIGGLL.CHECKTABLE.
5. Select Submit.
Add the Replicat.
1. In the navigation menu, select Replicats.
2. On the Replicats page, select Add Replicat (plus icon), then complete the Add Replicat fields as follows:
  - On the Replicat Information page:
    1. For Replicat type, select Nonintegrated Replicat.
    2. For Process Name, enter a name, such as RIL.
    3. Select Next
  - On the Replicat Options page:
    1. For Replicat Trail Name, enter the name of the Trail from Task 2 (I1).
    2. For Target Credentials, select the Domain and Alias for the Autonomous AI Database connection.
    3. For Checkpoint Table, select the Checkpoint table you created in Step 1.
    4. Select Next.
  - On the Parameter File page, replace MAP *.*, TARGET *.*; with the following:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
3. Select Create and Run.
  
  You return to the Replicats page, where you can review the Replicat details.
Select the Replicat (RIL) and view its Details.
Select Statistics and review the number of Inserts. Refresh the page.
- If the number of Inserts doesn't change, then all the records from the Initial Load have been loaded and you can stop the Replicat (RIL)
- If the number of Inserts continues to increase, then keep refreshing the page until the Initial Load records are all loaded before continuing.
Verify the Initial Load.
1. In the Oracle Cloud console, on the Autonomous AI Database details page, select Database actions and then select SQL.
2. In the SQL tool, enter each of the following statements into the worksheet and select Run Statement:
```
SELECT * FROM SRCMIRROR_OCIGGLL.SRC_CITY;
SELECT * FROM SRCMIRROR_OCIGGLL.SRC_CUSTOMER;
```
The output should return the data that was loaded into the target database tables as a result of the Initial Load.

Task 6: Create a Distribution Path for Change Data Capture Extract

In the source PostgreSQL deployment console, select Distribution Service.
Select Add Distribution Path.
Complete the Add Path form as follows:
1. On the Path Information page:
  1. For Path Name, enter a name.
  2. Select Next.
2. On the Source Options page:
  1. For Source Extract, select the Change Data Capture Extract (ECDC).
  2. For Trail Name, select the Change Data Capture trail file (C1).
  3. Select Next.
3. On the Target Options page:
  1. For Target, select wss.
  2. For Target Host, enter the target deployment console URL (you can find this on the deployment details page, without the https:// or any trailing slashes).
  3. For Port Number, enter 443.
  4. For Trail Name, enter C1.
  5. For Target Authentication Method, select UserID Alias.
  6. For Domain, enter the domain name.
  7. For Alias, enter the alias.
4. Select Create Path and Run.

Task 7: Add a Replicat for Change Data Capture

In the target Oracle deployment console, add a Replicat.
1. In the Administration Service navigation menu, select Replicats.
2. On the Replicats page, select Add Replicat (plus icon), and then complete the Add Replicat form as follows:
  - On the Replicat Information page:
    1. For Replicat type, select Nonintegrated Replicat.
    2. For Process Name, enter a name, such as RCDC.
    3. Select Next.
  - On the Replicat Options page:
    1. For Replicat Trail Name, enter the name of the Trail from Task 3 (C1).
    2. For Target Credentials, select the Domain and Alias for the Autonomous AI Database connection.
    3. For Checkpoint Table, select the Checkpoint table.
  - On the Replicat Parameters page, replace MAP *.*, TARGET *.*; with the following mapping:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
3. Select Create. Do not run the Replicat.
On the Replicats page, select the Replicat for Initial Load (RIL) and view its Details.
Select Statistics and review the number of Inserts. Refresh the page.
- If the number of Inserts doesn't change, then all the records from the Initial Load have been loaded and you can stop the Replicat (RIL)
- If the number of Inserts continues to increase, then keep refreshing the page until theInitial Load records are all loaded before continuing.
Note: If you don’t see any Inserts, select Performance Metrics Service, select the Extract, and then select Database Statistics.
Return to the Replicats page and then start the Replicat for Change Data Capture (RCDC).
After starting the Replicat for Change Data Capture, review its Details and Statistics to view the number of Inserts.

Verify replication:

Run the following script to perform inserts into the PostgreSQL database:

Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1000,'Houston',20,743113);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1001,'Dallas',20,822416);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1002,'San Francisco',21,157574);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1003,'Los Angeles',21,743878);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1004,'San Diego',21,840689);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1005,'Chicago',23,616472);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1006,'Memphis',23,580075);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1007,'New York City',22,124434);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1008,'Boston',22,275581);
Insert into src_ociggll.src_city (CITY_ID,CITY,REGION_ID,POPULATION) values (1009,'Washington D.C.',22,688002);

In the source PostgreSQL deployment console, select the Change Data Capture Extract (ECDC), and then select Statistics. Verify that src_ociggll.src_city has 10 inserts.

Note: If the Extract captured no inserts, then restart the ECDC Extract.
In the target Oracle deployment console, select the Replicat name (RCDC), view its Details, and then check Statistics. Verify that SRCMIRROR_OCIGGLL.SRC_CITY has 10 inserts.