Load data from Oracle CDC to Hazelcast via Striim

Before you Begin

  • Docker

  • DockerHub Account (required by Oracle Database Enterprise Edition)

Installation

  • Oracle Database Enterprise Edition

  • Striim Evaluation Edition

  • Hazelcast and Management Center

Striim

Striim is an end-to-end streaming platform for real-time integration and data analytics.

This tutorial uses Striim evaluation version hosted at Striim DockerHub.

To run Striim evaluation version, run the following Docker commands(remember to replace placeholders with actual values):

$ docker run -d --name striim -p 9080:9080 -e "STRIIM_ACCEPT_EULA=Y" -e "FIRST_NAME=yourname" -e "LAST_NAME=yoursurname" -e "COMPANY_NAME=yourcompanyname" -e "COMPANY_EMAIL_ADDRESS=yourcompanyemailaddress" striim/evalversion

Make sure that Striim started properly:

$ docker logs striim -f
...
started.
Please go to http://172.17.0.2:9080 or https://172.17.0.2:9081 to administer, or use console
Note: If you experience an expired license key issue please contact support@striim.com

If everything goes well, then Striim dashboard will be available under http://localhost:9080. You can use admin/admin as credentials.

Oracle Database Enterprise Edition

Oracle Database Docker Hub Page contains a Docker image as a container option for Oracle Database. Visit Oracle Database Docker Hub Page and click on Proceed Checkout. This will subscribe you to Developer Tier.

Make sure you are logged in to DockerHub:

$ docker login
Authenticating with existing credentials...
Login Succeeded

Run Oracle Database in a Docker container:

$ docker run -d -p 8080:8080 -p 1521:1521 --name oracledb store/oracle/database-enterprise:12.2.0.1

Check logs until you see SQL> ORACLE instance started.

$ watch docker logs oracledb

Hazelcast and Management Center

Start Hazelcast member and Management Center containers:

$ docker run -d --name mancenter -p 38080:8080 hazelcast/management-center:3.12.6

# extract management-center container's IP address
$ docker inspect --format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' mancenter

$ docker run -d --name hazelcast -p 5701:5701 -e MANCENTER_URL="http://{MAN_CENTER_CONTAINER_IP}:8080/hazelcast-mancenter"  hazelcast/hazelcast:3.12.6

Management Center dashboard will be available under http://localhost:38080/hazelcast-mancenter.

Check all containers are up and ready before going further:

$ docker ps
IMAGE                                       STATUS                    PORTS                                                      NAMES
hazelcast/hazelcast:3.12.6                  Up 16 minutes             0.0.0.0:5701->5701/tcp                                     hazelcast
hazelcast/management-center:3.12.6          Up 23 minutes             8081/tcp, 8443/tcp, 0.0.0.0:38080->8080/tcp                mancenter
striim/evalversion                          Up 25 minutes             1527/tcp, 0.0.0.0:9080->9080/tcp                           striim
store/oracle/database-enterprise:12.2.0.1   Up 25 minutes (healthy)   0.0.0.0:1521->1521/tcp, 0.0.0.0:8080->8080/tcp, 5500/tcp   oracledb

Configuring Oracle Database

Connect to Oracle DB:

$ docker exec -it oracledb bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

# Enter 'Oradoc_db1' as a password
$ SQL> conn sys as sysdba;

Enable ARCHIVELOG by following this link.

Enable SUPPLEMENTAL LOG DATA by following this link.

Create an Oracle User with LogMiner privileges:

create role c##striim_privs;
grant create session,execute_catalog_role,select any transaction,select any dictionary,logmining to c##striim_privs;
grant select on SYSTEM.LOGMNR_COL$ to c##striim_privs;
grant select on SYSTEM.LOGMNR_OBJ$ to c##striim_privs;
grant select on SYSTEM.LOGMNR_USER$ to c##striim_privs;
grant select on SYSTEM.LOGMNR_UID$ to c##striim_privs;
create user c##striim identified by striim container=all;
grant c##striim_privs to c##striim container=all;
alter user c##striim set container_data = (cdb$root, ORCLPDB1) container=current;

Create striim user to create/update database tables:

# Enter 'Oradoc_db1' as a password
$ SQL> conn sys as sysdba;

$ SQL> alter session set container=ORCLPDB1;
$ SQL> create user striim identified by striim;
$ SQL> grant connect, resource,dba to striim container=current;
$ SQL> alter user striim default role dba;

Create PRODUCT_INV table with Striim user:

$ SQL> conn striim/striim@orclpdb1
$ SQL> create table STRIIM.PRODUCT_INV(SKU NUMBER(19) not null primary key, LAST_UPDATED TIMESTAMP(6), NAME VARCHAR2(255 char), STOCK FLOAT not null);

We’ll use/populate the newly created table in the next steps. By the way, this user will be used by our Spring application.

Configuring Striim

  1. Download driver jar(ojdbc8.jar) from Oracle website. OracleReader needs this jar to connect DB so follow the steps below to add it into striim container:

    $ docker cp path/to/your/ojdbc8.jar striim:/opt/striim/lib/ojdbc8.jar

$ docker exec -it striim chown striim:striim /opt/striim/lib/ojdbc8.jar
$ docker exec -it striim chmod +x /opt/striim/lib/ojdbc8.jar

    Install the jar into your local Maven repository:

    $ mvn install:install-file -Dfile=path/to/your/ojdbc8.jar -DgroupId=com.oracle
 -DartifactId=ojdbc8 -Dversion=12.2.0.1 -Dpackaging=jar

  2. Clone the project then build the project to create pojo-0.0.1-SNAPSHOT.jar:

     $ git clone https://github.com/hazelcast-guides/striim-cdc.git
 $ cd striim-cdc/pojo
 $ mvn install

  3. To use HazelcastWriter, you need to POJO jar and ORM file, you can find details about these files, here. You can copy POJO jar and ORM file to striim container using below commands:

    $ docker cp ./pojo/target/pojo-0.0.1-SNAPSHOT.jar striim:/opt/striim/lib/pojo-0.0.1-SNAPSHOT.jar
$ docker cp ./config/product_inv_orm.xml striim:/opt/striim/

$ docker exec -it striim chown striim:striim /opt/striim/lib/pojo-0.0.1-SNAPSHOT.jar
$ docker exec -it striim chown striim:striim /opt/striim/product_inv_orm.xml
$ docker exec -it striim chmod +x /opt/striim/lib/pojo-0.0.1-SNAPSHOT.jar

  4. After all changes restart your container and proceed to the next steps:

    $ docker restart striim

Install OracleHazelcastCDC App into Striim

In the previous sections, we installed the required software for the sample application. We’ll now install OracleHazelcastCDC app through Striim dashboard.

There are two ways to install OracleHazelcastCDC App. We recommend using Using TQL file (Quick Setup)

  • Using TQL file (Quick Setup)

  • Configuring through Striim dashboard

Configuring Oracle Database CDC connection Using TQL file (Quick Setup)

  1. Change {ORACLE_DB_ADDRESS} and {HZ_IP_ADDRESS} placeholders with your HOST IP addresses at config/OracleHazelcastCDC.tql.

You can modify these values before deploying the app as well.

  1. Go to Create App Page and select Import Existing App and choose .tql which you already modified.

  2. Deploy and Run CDC application: Run CDC Application

Configuring Oracle Database CDC connection on Striim dashboard

  1. To create a new app, select Start with Template then Oracle CDC to Hazelcast:

    Create New App

    Use OracleHazelcastCDC or another name as an Application Name.

  2. Enter your Oracle DB data and credentials:

DB Connection Creds
DB Connection Control

  • localhost or IP address of oracledb container does not work for Connection URL so you need to use your HOST IP address.

  • As you can see above, the service section of Connection URL is configured as a /orclpdb1.localdomain, not as :ORCLCDB. If you configure service as a :ORCLCDB, STRIIM application or C##STRIIM common user can not reach/list PRODUCT_INV table which is under STRIIM local user because of CDB specific bug at Striim template itself. We’ll update this info with the correct ones before deploying the application. By the way, we have already contacted them and reported this issue. They will provide to fix at future releases. If you use Oracle DB without CDB, you are not affected by this issue.

    1. Select source table:

Source Table

Configuring Hazelcast Writer on Striim dashboard

  1. Put ORM file location(/opt/striim/product_inv_orm.xml) and Hazelcast cluster info:

    Hazelcast Connection

  2. Check ORM mapping details:

    ORM Mapping

  3. Choose related DataStream from Input From dropdown and save Target:

    Hazelcast Target

Apply OracleReader changes and Deploy & Run the CDC application

  1. After all configuration steps finally, your CDC application is created. Before deploy and create an application, as mentioned at Configuring Oracle Database CDC connection on Striim dashboard section, you need to update Connection URL and Tables section like this to run CDC application without any issue:

    Update Reader

  2. As a final step, go to enable OracleReader’s Support PDB and CDB option:

    Enable CDB Support

  3. Deploy and Run CDC application:

    Run CDC Application

Start Spring Boot Application to populate a database

Run spring-boot application:

$ mvn spring-boot:run

Check up

  1. Check application loading data from OracleReader to HazelcastWriter. Verify throughput on the screen with a similar number like 46 msg/s on the screenshot below.

    RunningApplication

  2. Check Hazelcast Map(ProductInv) size from Management Center,http://localhost:38080/hazelcast-mancenter/dev/maps/ProductInv:

    ProductInvMap