Replicate data from HeatWave on OCI to Autonomous AI Lakehouse

Learn how to use OCI GoldenGate to replicate data from HeatWave on OCI to Autonomous AI Lakehouse.

Before you begin

To successfully complete this quickstart, you must have the following:

OCI Bastion, to connect to HeatWave on OCI, load the sample database, and perform inserts
A HeatWave on OCI database to serve as the source database
An Autonomous AI Lakehouse instance to serve as the target database

Environment set up: HeatWave on OCI

To set up the environment for this Quickstart:

Download the sample data script, and then run the script on the HeatWave on OCI database to create the database and load the data.

Create a ggadmin user using the following script. Remember to replace <ggadmin-password> with a valid password:

CREATE USER 'ggadmin' IDENTIFIED BY '<ggadmin-password>';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, CREATE,CREATE VIEW, EVENT, INSERT, UPDATE, DROP,EXECUTE, DELETE ON *.* TO 'ggadmin';

Open port 3306, through which OCI GoldenGate can connect.
1. In the Oracle Cloud console, locate the subnet that the HeatWave on OCI database uses.
2. In the security list of the subnet, create an Ingress rule for port TCP/3306.
Download the target schema scripts, and then run the scripts in the Autonomous AI Lakehouse instance to create the schema and tables. You can use the Autonomous AI Lakehouse Database Actions SQL tool to run the scripts:
1. In the Oracle Cloud console, open the Autonomous AI Lakehouse database details page, and then select Database Actions.
2. In Database Actions, under Development, select SQL.
3. In the SQL tool, copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet, and then select Run Script. If successful, the Script Output tab displays confirmation messages.
4. Clear the SQL worksheet, and then copy and paste only the Create Table scripts from OCIGGLL_OCIGGS_SRC_MIRROR_USER_SEED_DATA.sql. The data will be loaded in a later task.
  
  Tip: You may need to run each Create Table statement separately for the SQL tool to execute the scripts successfully.
5. To verify 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 the 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 HeatWave on OCI database.
Create a deployment for the target Autonomous AI Lakehouse.
Create a connection to the source HeatWave on OCI database.
Create connection for the target Autonomous AI Lakehouse.
Create a connection to GoldenGate, and then assign this connection to the source MySQL deployment.
Assign the source connection to the source MySQL deployment.
Assign the target connection to the target Oracle deployment.

Task 2: Create the Extracts

On the Deployments page, select the MySQL deployment created in Task 1.
On the deployment details page, select Launch Console.
Sign in to the source MySQL deployment console using the Administrator credentials specified when you created the deployment in Task 1.
In the navigation menu, select DB Connections.
Review the credentials for the HeatWave on OCI connection. Take note of the Domain and Alias.
Add and run a Change Data Capture Extract with the follow values:
- For Extract Type, select Change Data Capture.
- For Process Name, enter a name for the Extract process.
- For Domain, select the domain.
- For Alias, select the alias.
- For Extract Trail Name, enter a name for the Trail file.
- On the Parameter File page, add the following:
```
TABLE SRC_OCIGGLL.*;
```
Add and run an Initial Load Extract with the follow values:
- For Extract Type, select Initial Load Extract.
- For Process Name, enter a name.
- On the Parameter File page, add the following:
```
TABLE SRC_OCIGGLL.*;
```

Task 3: Create the Distribution Path for an Initial Load Extract

On the Deployments page, select the target Autonomous AI Database deployment.
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 MySQL deployment console, create a Path Connection for the user created in the previous step.
1. In the navigaton 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 Distribution Service, 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.
Review the Distribution Path:
1. Select Receiver Service.
2. Review the Distribution Path details.

Task 4: Add a Replicat for the Initial Load Extract

In the target OCI GoldenGate deployment console, add a checkpoint table.
1. Open the navigation menu and then select DB Connections.
2. Select Connect to database.
3. In the navigation menu, select Checkpoint.
4. In the Checkpoint page, select Add Checkpoint(plus icon).
5. For Checkpoint Table, enter SRCMIRROR_OCIGGLL.CHECKTABLE.
6. Select Submit.
  
  Tip: The screen will not refresh on submit. To confirm the checkpoint table was added, enter the SRCMIRROR_OCIGGLL.CHECKTABLE into the search field and then select Search.
Add the Replicat:
1. Select Administrator Service, and then select Add Replicat (plus icon).
2. On the Add Replicat page, complete the following form fields, and then select Next.
  - For Replicat type, select Nonintegrated Replicat.
  - For Process Name, enter a name, such as RIL.
3. On the Replicat Options page, complete the following form fields, and then select Next:
  - For Name, enter the name of the Trail from Task 2 (I1).
  - For Domain, select the domain for the Autonomous AI Database connection.
  - For Alias, select the alias of the Autonomous AI Database connection.
  - For Checkpoint Table, select the Checkpoint table you created in Step 1.
4. On the Managed Options page, leave the fields as they are, and select Next.
5. On the Replicat Parameters page, add the following mapping, and then select Create and run:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
You're returned to the Overview 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.

Task 5: Verify the initial load

In the Oracle Cloud console, open Database Actions from the Autonomous AI Lakehouse database details page.
In Database Actions, under Development, select SQL.
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 a Change Data Capture

In the source 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.
In the target deployment console, select Receiver Service, and then review the Receiver path created.

Task 7: Add a Replicat for Change Data Capture

Add the Replicat with the following values:
1. On the Replicat Information page:
  - For Replicat Type, select Coordinated Replicat.
  - For Process Name, enter a name, such as RCDC.
2. On the Replicat Options page:
  - For Replicat Trail, enter the name of the Trail from Task 2 (C1).
  - For Domain, select the domain for the Autonomous AI Database connection.
  - For Alias, enter the Credential Alias.
  - For Checkpoint Table, select the Checkpoint table you created.
3. On the Replicat Parameters page, add the following mapping, and then select Create and run:
```
MAP SRC_OCIGGLL.*, TARGET SRCMIRROR_OCIGGLL.*;
```
On the Administration Service Overview 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 the Initial Load records are all loaded before continuing.
Return to the Administration Service Overview page and then start the Coordinated Replicat (RCDC).
After starting the Coordinated Replicat, review its Details and Statistics to view the number of Inserts.

Task 8: Verify Change Data Capture

Perform updates to the source HeatWave on OCI database to verify replication to Autonomous AI Lakehouse.

In OCI Bastion, create an SSH port forwarding session using MySQL IP and port 3306. Add your public SSH key.
Connect to MySQL in Cloud Shell using your private key and port 3306.
After connecting successfully, run the following command:
```
mysqlsh admin@localhost:3306 --sql
```

Run the following script to perform inserts into the HeatWave on OCI database:

use SRC_OCIGGLL;
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);
commit;

In the source MySQL deployment console, select the Change Data Capture Extract name, and then select Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.

Note: If the Extract captured no inserts, then restart the Extract.