Note:

• This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
• It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.

Stream data using Oracle GoldenGate and Oracle GoldenGate for Big Data with different targets

Introduction

Oracle GoldenGate is a fully managed service that helps data engineers move data in real-time, at scale, from one or more data management systems to Oracle Cloud databases. Design, run, orchestrate, and monitor data replication tasks in a single interface without having to allocate or manage any compute environments.

Oracle GoldenGate for Big Data streams transactional data into Big Data and Cloud systems in real-time, without impacting the performance of source systems. It streamlines real-time data delivery into the most popular Big Data solutions, including Apache Hadoop, Apache HBase, Apache Hive, Confluent Kafka, NoSQL Databases, Elasticsearch, JDBC, Oracle Cloud, Amazon Web Services, Microsoft Azure Cloud, Google Cloud Platform, and Data Warehouses to facilitate improved insight and timely action.

The Oracle Cloud Infrastructure (OCI) Streaming service provides a fully managed, scalable, and durable solution for ingesting and consuming high-volume data streams in real-time. Use OCI Streaming for any use case in which data is produced and processed continually and sequentially in a publish-subscribe messaging model.

In this tutorial, you will learn how to stream data from Oracle Autonomous Database into two different targets, that is, Oracle Autonomous Database and Oracle Streams Topic.

Objective

• Set up two Oracle Autonomous Databases

  • In this example scenario, sourceDB has two tables Banks and Operacoes where data needs to be replicated to two different targets

  • Table Banks will be replicated to a target Oracle Autonomous Database called targetDB

  • Table Operacoes will be replicated to an OCI Streaming (kafka) topic with near real time stream

• Set up Oracle GoldenGate to replicate data from Source to Target database

• Publish table DMLs from Source database to an Oracle Cloud Infrastructure (OCI) Streaming (Kafka compatible) Target topic

Architecture

IMPORTANT: This sample architecture is for learning purposes only and to keep it simple, we are using a PUBLIC subnet, which means this should not be used for Production environments or real data processing.

For Production environments, you MUST use PRIVATE subnets and apply all the security best practices described here: Oracle Security Best Practices)

Prerequisites

• An Oracle Cloud Account - Get started with Oracle Cloud Infrastructure Free Tier
• A basic VCN (Virtual Cloud Network) with two subnets (Public and Private)
  You can follow this tutorial to create a brand new VCN QuickStart Networking
• Basic concepts of Oracle Database
• Basic concepts of Kafka Streaming

Task 1: Create a new compartment

• Log in to your Oracle Cloud account, click on the top left navigation menu, select Identity & Security, and then click Compartments.

  

Task 2: Create a new Oracle Autonomous Database named sourceDB

1. Click on the top left navigation menu, select Oracle Database, and then click Autonomous Database.

2. Select the compartment streaming created in the earlier step and click Create Autonomous Database.

   

3. Fill the information as shown in the following images.

   

   

   

Note: Make a note of the ADMIN password you have created.

Task 3: Create a new Oracle Autonomous Database named targetDB

1. Click on the top left navigation menu, select Oracle Database, and then click Autonomous Database.

2. Select the compartment created in the earlier step streaming and click Create Autonomous Database.

   

3. Fill the information as shown in the following images.

   

   

   

Note: Make a note of the ADMIN password you have created.

Task 4: Create a new schema and settings for sourceDB

1. Go to the sourceDB home page and click Database Actions to run SQL commands on the database and load data.

   

2. Download the findata_schema.sql file and copy the contents into the SQL execution window and run it as a SCRIPT.

   Note: Make sure you set a password for the FINDATA user, and take note for later steps in this tutorial.

   

3. Download the enable_gg.sql file and copy the contents into the SQL execution window and run it as a SCRIPT.

   Note: Make sure you set a password for the GGADMIN user, and take note for later steps in this tutorial.

   

Task 5: Load data from the banks_export.sql file into sourceDB

1. Go to the sourceDB home page and click Database Actions to run SQL commands on the database and load data.

   

2. Download the banks_export.sql file and copy the contents into the SQL execution window and run it as a SCRIPT.

   

3. Run a simple COUNT on the table and check the number of rows inserted.

   

Task 6: Create a new schema for targetDB and load data

• Repeat Task 4 and Task 5 for “targetDB”.

• Create the FINDATA table, enable GG and load data from table BANKS into the target database just like you did on the sourceDB but now connected on the targetDB.

Task 7: Create the transaction table called OPERACOES on sourceDB

1. Create a new table named FINDATA.OPERACOES in sourceDB only. This table will be the source data that Oracle GoldenGate for Big Data will capture and post on the OCI Streaming topic.

2. Go to the sourceDB home page and click Database Actions to run SQL commands on the database and load data.

   

3. Download the operacoes_table.sql file and copy the contents into the SQL execution window and run it as a SCRIPT.

This is an empty table that will receive the random data to start events on Oracle GoldenGate.

Task 8: Create an OCI Streaming Pool

1. Click on the top left navigation menu, select Analytics & AI, click Streaming, and then select Streaming Pool.

2. Click Create Stream Pool and fill the following information.

   

   

3. Click Create.

4. Click Kafka Connection Settings and take note of the information regarding the connection strings.

   

Task 9: Generate an AUTH TOKEN to access the OCI Streaming Pool

You can access OCI Streaming (Kafka API) and other resources in Oracle Cloud with an Auth Token associated to your user on Oracle Cloud Infrastructure Identity and Access Management (OCI IAM). In Kafka Connection Settings, the SASL Connection Strings has a parameter named password and an AUTH_TOKEN value as described in the previous task. To enable access to OCI Streaming, go to the OCI Console and create an AUTH TOKEN.

1. Click on the top left navigation menu, select Identity & Security, select Identity, Domains, Default Domain, Users.

2. Select the same user you have used to create the earlier resources such as Oracle Autonomous Database and OCI Stream Pool.

Note: Make a note of the AUTH TOKEN, you will need it for the next step.

Task 10: Create Oracle GoldenGate connections

In order to connect to the source and target databases, Oracle GoldenGate needs the proper connection setup.

1. Click on the top left navigation menu, select Oracle Database, then click GoldenGate.

2. Select option Connections from the left menu and click Create connection.

3. Create the connection for sourceDB.

   

   

4. Repeat the process and create the connection for targetDB.

   

   

5. Create the connection for your OCI Streaming pool DefaultPool.

   

   

At this point, you should have the following connections:

Task 11: Create Oracle GoldenGate deployment - Type: ORACLE

1. Click on the top left navigation menu, select Oracle Database, then click GoldenGate.

2. Select option Deployments from the left menu and click Create deployment.

   

   

Note: Make sure you save your oggadmin password.

Task 12: Create Oracle GoldenGate deployment - Type: BIGDATA

1. Click on the top left navigation menu, select Oracle Database, then click GoldenGate.

2. Select option Deployments from the left menu and click Create deployment.

   

   

   

3. After the deployments are created and active, proceed to the next step.

   

Task 13: Create connection between Oracle GoldenGate deployments

In order for the Oracle GoldenGate to communicate with the Oracle GoldenGate for Big Data, we need to create the connection.

1. Click on the top left navigation menu, select Oracle Database, then click GoldenGate.

2. Select option Connections from the left menu and click Create connection.

   

   

Your connections should look like this:

Task 14: Adding connections to the Oracle GoldenGate deployment - GGForOracle

1. Select the GGForOracle deployment, on the left menu click Assigned connections and then click Assign connection.

   

2. Add the connection for sourceDB.

   

3. Add the connection for targetDB.

   

4. Add the connection for GGOracletoGGBigData .

   

You should have the following connections on GGForOracle deployment:

Task 15: Adding connections to the Oracle GoldenGate deployment - GGforBigData

1. Select the GGforBigData deployment, on the left menu click Assigned connections and then click Assign connection.

   

   

You should have the following connections on GGforBigData deployment:

Task 16: Set up Oracle GoldenGate for GGforOracle

1. Select your GGForOracle deployment, and click Launch Console.

2. Enter the credentials oggadmin/yourpassword (the password you created during creation of the deployment).

   

3. Click on the navigation menu, then select Configuration, and then click + in order to add a new credential.

   

4. Add the credentials for the GGforBigData deployment.

   Note: Use the oggadmin you have set up during deployment creation.

   

5. Click to connect on the sourceDB and then click + on TRANDATA to add the needed tables for replication.

   

6. Add new record for FINDATA.BANKS table and click Submit.

   

7. Add new record for FINDATA.OPERACOES table and click Submit.

   

8. To check if the tables are added, click on the filter for FINDATA.*.

   

9. Click to connect on the targetDB and then click + on CHECKPOINT.

   

   

10. Enter FINDATA.CHECKTABLE and click Submit .

    

11. Now click on the left menu Overview option to include the new extract.

    

    

    

    

    • Include the following parameters:

      EXTRACT EXT
      USERIDALIAS sourceDB DOMAIN OracleGoldenGate
      EXTTRAIL E1
      table FINDATA.operacoes;
      table FINDATA.banks;
      
      

12. Click Actions, Start to start the extract.

    

13. Now, create a new replicat to apply the replication to the targetDB.

    

    

    

    • Include the following parameters:

      REPLICAT REP
      USERIDALIAS targetDB DOMAIN OracleGoldenGate
      MAP FINDATA.BANKS, TARGET FINDATA.BANKS;
      

    Note: Only the FINDATA.BANKS table is being replicated here. This is because we are replicating it to the targetDB. The other table FINDATA.OPERACOES will be replicated to the OCI Streaming topic later in this tutorial.

14. Click Action, Start to start the replicat.

    

    

15. Check if the replication is working for the FINDATA.BANKS table: Go to your database actions page for sourceDB and insert a new record.

    INSERT INTO FINDATA.BANKS VALUES (999, 999, 'XXXXXX','TEST GOLDENGATE','TESTGG');
    COMMIT;
    

    

16. Open your database actions page for targetDB and run the query below to check if the record is created.

     select * from FINDATA.BANKS WHERE ID_SEQ = 999;
    
    

    

17. Click on the Distribution Service tab and add a new path to send data to GGforBigData.

    

    

    

18. Click Action, Start to start the service distribution.

    

    

Task 17: Setup Oracle GoldenGate for Big Data - GGforBigData

1. Select your GGforBigData deployment, and click Launch Console.

2. Enter the credentials oggadmin/yourpassword (the password you created during creation of the deployment).

   

3. Click on the Receiver Service tab and check if it is working already. It should look like this, since we already did all the needed settings for the connection between both Oracle GoldenGate deployments.

   

4. Now we need to set up the REPLICAT in order to publish the data on the OCI Streaming topic.

5. Click on the Administration Service tab.

   

   

   

   • Include the following parameters:

      REPLICAT STRM
      TARGETDB LIBFILE libggjava.so SET property=/u02/Deployment/etc/conf/ogg/STRM.properties
      MAP FINDATA.operacoes, TARGET FINDATA.operacoes , THREADRANGE(1-5);
     
     

     

   • Just add the ${tableName} on this line and use all the default values provided.

     

6. Click Action, Start to start the replicat.

   

   • Check if the status is green:

     

Task 18: Verify replication to the OCI Streaming topic

1. Go to your database actions page for sourceDB and insert some rows in the FINDATA.OPERACOES table.

   • You can use the load_random_data.sql script to insert random data into the FINDATA.OPERACOES table.

   • This script will add 1.000 rows on FINDATA.OPERACOES and will be enough to check if the replication is working as expected.

     

2. Check if there is any record in FINDATA.OPERACOES by running the following SQL command.

   SELECT COUNT(*) FROM FINDATA.OPERACOES;
   

   

3. Click on the top left navigation menu, select Analytics & AI, click Streaming, and select Stream from the left menu.

4. Check if a new TOPIC is automatically created by Oracle GoldenGate for Big Data.

   

5. Try to consume some messages and check if they are available in the topic by clicking Load Messages.

   

Important Note: Load Messages only works for messages consumed in the last MINUTE, this means if you take longer than that after running the LOAD_RANDOM_DATA.SQL script, you will not see any result.

To see results, go to your database and run the LOAD_RANDOM_DATA.SQL script again and then go back to Load Messages and you will see your messages.

Acknowledgments

• Author - Joao Tarla (Oracle LAD A-Team Solution Engineer)

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.

---

Title and Copyright Information

Stream data using Oracle GoldenGate and Oracle GoldenGate for Big Data with different targets

F80321-01

April 2023

Copyright © 2023, Oracle and/or its affiliates.