Replicate Data from Autonomous AI Transaction Processing to Confluent Kafka

This quickstart demonstrates how to replicate data from Autonomous AI Transaction Processing to Confluent Kafka.

Before you begin

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

• A source Autonomous AI Transaction Processing instance.

• A Confluent Platform instance.

Environment set up: Autonomous AI Transaction Processing

If you don’t already have a source database set up for replication, you can follow these steps to load a sample schema to use for this quickstart. This quickstart uses Autonomous AI Transaction Processing for the source database.

To set up the source Autonomous AI Transaction Processing:

1. Download and unzip the sample database schema.

2. In the Oracle Cloud console, select your Autonomous AI Transaction Processing (ATP) instance from the Autonomous AI Databases page to view its details and access Database Actions.

3. Unlock the GGADMIN user:

   1. Select Database actions, then select Database Users.

   2. Locate GGADMIN and then select its ellipsis menu (three dots) and select Edit.

   3. In the Edit User panel, enter the GGADMIN password, confirm the password, and then deselect Account is Locked.

   4. Select Apply Changes.

4. Load the source sample schema and data:

   1. From the Database actions menu, under Development, select SQL.

   2. Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ATP.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 script from OCIGGLL_OCIGGS_SRC_USER_SEED_DATA.sql.

      Tip: You may need to run each statement separately for the SQL tool to execute the scripts successfully.

   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.

5. Enable supplemental logging:

   1. Clear the SQL Worksheet.

   2. Enter the following statement, and then select Run Statement:

      ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA;

Environment set up: Confluent Cloud

If you don’t already have a target Confluent Cloud cluster, topic and schema, you can do the following:

1. Create a cluster.

2. Create an API Key for the Cluster. Note the API Key and Secret for the next steps.

3. Enable Confluent Schema Registry, and then create an API Key for Confluent Cloud Confluent Schema Registry. Note the API Key and Secret for the next steps.

4. Create a topic in the cluster. Note the name of the topic for the next steps.

Task 1: Create the OCI GoldenGate resources

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

1. Create an Oracle deployment for the source Autonomous AI Transaction Processing instance.

2. Create a Big Data deployment for the target Confluent Cloud.

3. Create a connection to the source Autonomous AI Transaction Processing.

4. Create a Confluent Kafka connection.

5. Create a Confluent Schema Registry.

6. Create a connection to GoldenGate, and then assign this connection to the source Oracle deployment.

7. Assign the Autonomous AI Transaction Processing connection to the source Oracle deployment.

8. Assign Confluent Kafka and Confluent Schema Registry connections to the target Big Data deployment.

Task 2: Add the Extract

1. On the Deployments page, select the source Autonomous AI Transaction Processing deployment.

2. On the deployment details page, select Launch Console.

3. Log in with the source deployment's administrator username and password.

4. Add an Extract.

Task 3: Add and run the Distribution Path

1. If using GoldenGate credential store, create a user for the Distribution Path in the target Big Data deployment, otherwise skip to Step 3.

2. In the source GoldenGate deployment console, add a Path Connection for the user created in Step 1.

   1. In the source GoldenGate deployment console, select Path Connections in the left navigation.

   2. Select Add Path Connection (plus icon), and then complete the following:

      1. For Credential Alias, enter GGSNetwork.

      2. For User ID, enter the name of the user created in Step 1.

      3. Enter the user's password twice for verification.

   3. Select Submit.

      The path connection appears in the Path Connections list.

3. In the source deployment console, add a Distribution Path with the following values:

   1. On the Source Options page:

      • For Source Extract, select the Extract created in Task 2.

      • For Trail Name, enter a two-character name, such as E1.

   2. On the Target Options page:

      • For Target Host, enter the host domain of the target deployment.

      • For Port Number, enter 443.

      • For Trail Name, enter a two-character name, such as E1.

      • For Alias, enter the Credential Alias created in Step 2.

4. In the target Big Data deployment console, review the Receiver Path created as a result of the Distribution Path.

   1. In the target Big Data deployment console, select Receiver Service.

   2. Review the path details. This path was created as a result of the Distribution Path created in the previous step.

Task 4: Add and run the Replicat

1. In the target Big Data deployment console navigation menu, select Replicats, then Add Replicat (plus icon).

2. Add a Replicat with the following values:

   1. On the Replicat Information page, under Replicat type, select Classic Replicat, enter a Process Name, and then select Next.

   2. On the Replication Options page:

      • For Name, enter the name of the Trail from Task 2.

      • For Domain, select a domain.

      • For Alias, select the Confluent Kafka connection created in Task 1.

      • For Converter, select Avro.

      • For Confluent Schema Registry, select the Confluent Schema Registry connection created in Task 1.

      • For Checkpoint Table, select the checkpoint table you created for the target deployment.

   3. On the Replicat Parameters page, replace MAP *.*, TARGET *.*; with:

      MAP SRC_OCIGGLL.SRC_CUSTOMER, Table SRC.CUSTOMER;

3. Select Create and Run.

You return to the Overview page, where you can review the Replicat details.

Task 5: Verify the replication

Perform some updates to the source Autonomous AI Transaction Processing instance to verify replication to Confluent Kafka Cloud.

1. In the Oracle Cloud console, open the navigation menu, select Oracle AI Database, and then select Autonomous AI Transaction Processing.

2. In the list of Autonomous AI Transaction Processing instances, select your source instance to view its details.

3. On the database details page, select Database actions.

   Note: You should be automatically logged in. If not, log in with the database credentials.

4. On the Database actions home page, select SQL.

5. Enter the following into the worksheet and select Run Script.

   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);

6. In the source ATP deployment console, select the Extract name, and then select Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.

7. In the target Big Data OCI GoldenGate deployment console, select the Replicat name, and then select Statistics. Verify that SRC_OCIGGLL.SRC_CITY has 10 inserts.

8. In the Oracle Cloud console, navigate to the Oracle Object Storage bucket and check its contents.

Task 6: (Optional) Verify the replication in Confluent CLI

1. Install Confluent CLI in your system.

2. Execute:

   export PATH=$(pwd)/bin:$PATH
   confluent login

3. List environments and select your environment

   confluent environment list
   confluent environment use <your_environment_ID>

4. List clusters and select your cluster

   confluent kafka cluster list
   confluent kafka cluster use <your_cluster_id>

5. Store API Key and Secret locally

   confluent api-key store <cluster_api_key> <cluster_api_secret>
   confluent api-key use <cluster_api_key> --resource <cluster_id>

6. List topics

   confluent kafka topic list

7. View messages

   confluent kafka topic consume --value-format avro --from-beginning <topic_name>