In this lab host server operating system is Oracle Server 9.4 and Podman is used for setting up containers. Commands in this guide are suitable with this setup. They may not work for other types of OS or Linux distributions and container management software. Check their documentation.
Setup Software using Podman
We will be installing OGG Bigdata classic architecture lab using podman comtainer. Oracle has pre-build docker images for such setup (https://github.com/oracle/docker-images/tree/main/OracleGoldenGate) however, in this lab we are setting up container manually.
Open Firewall Ports
In case if OGG bigdata will receive trails from some source OGG system outside of host server, we need to be sure that host server accepts connections from other servers. OGG classic works on 2 different set of ports:
- Manager port: Every OGG classic home (software) has a manager process which listens on manager port while it is running.
- Dynamic manager ports: Manager process allocates these dynamic ports temporarily on runtime for communications and data transfer.
In this lab we reserve
- 4 (7806-7809) manager ports so that we can run up to 4 OGG homes at the same time.
- 11 (7810-7820) dynamic manager ports which are shared between all OGG homes.
sudo firewall-cmd --permanent --add-port={7820/tcp,7819/tcp,7818/tcp,7817/tcp,7816/tcp,7815/tcp,7814/tcp,7813/tcp,7812/tcp,7811/tcp,7810/tcp,7809/tcp,7808/tcp,7807/tcp,7806/tcp}
sudo firewall-cmd --reload
Setup Basic Container
We will be using podman volume for /u01 to setup all software under this path.
podman volume create oggbigdata01-data
podman run -dt --name oggbigdata01 -h oggbigdata01 -p 7806-7809:7806-7809 -p 7810-7820:7810-7820 -v "oggbigdata01-data:/u01" oraclelinux:8
podman exec -it oggbigdata01 bash
oggbigdata01:$ groupadd -g 54421 oinstall
oggbigdata01:$ groupadd -g 54322 dba
oggbigdata01:$ useradd -u 54321 -g oinstall -G dba oracle
oggbigdata01:$ yum install unzip
oggbigdata01:$ yum install which
oggbigdata01:$ yum install libnsl
oggbigdata01:$ yum install net-tools
oggbigdata01:$ exit
Setup OGG Home (software)
Download OGG bigdata software and JDK into some path at host server. For example /tmp/p36087484_2112001_Linux-x86-64.zip. Then use podman cp command to copy them into container:
podman cp p36087484_2112001_Linux-x86-64.zip oggbigdata01:/u01/p36087484_2112001_Linux-x86-64.zip
podman cp jdk-8u401-linux-x64.rpm oggbigdata01:/u01/jdk-8u401-linux-x64.rpm
Then enter container and setup software:
$ podman exec -it oggbigdata01 bash
oggbigdata01:$ cd /u01
oggbigdata01:$ unzip p36087484_2112001_Linux-x86-64.zip
oggbigdata01:$ mkdir ogg21bdc
oggbigdata01:$ mv ggs_Linux_x64_BigData_64bit.tar ogg21bdc/
oggbigdata01:$ cd ogg21bdc
# without --no-same-owner, tar gives "Cannot change ownership to uid 94110, gid 42424: Invalid argument" errors
oggbigdata01:$ tar xvf ggs_Linux_x64_BigData_64bit.tar --no-same-owner
oggbigdata01:$ cd ..
oggbigdata01:$ chown -R oracle:oinstall ogg21bdc
oggbigdata01:$ yum localinstall jdk-8u401-linux-x64.rpm
oggbigdata01:$ su - oraacle
# set JAVA_HOME and LD_LIB_PATH (put in user profile)
oggbigdata01:$ export JAVA_HOME=/usr/lib/jvm/jdk-1.8-oracle-x64
oggbigdata01:$ export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server:$LD_LIBRARY_PATH
oggbigdata01:$ cd /u01/ogg21bdc/
oggbigdata01:$ ./ggsci
Oracle GoldenGate for Big Data
Version 21.4.0.0.0 (Build 002)
Oracle GoldenGate Command Interpreter
Version 21.4.0.0.0 OGGCORE_21.4.0.0.0OGGRU_PLATFORMS_211022.1803
Oracle Linux 7, x64, 64bit (optimized), Generic on Oct 22 2021 23:14:43
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggbigdata01) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/ogg21bdc
Parameter file /u01/ogg21bdc/dirprm: created.
Report file /u01/ogg21bdc/dirrpt: created.
Checkpoint file /u01/ogg21bdc/dirchk: created.
Process status files /u01/ogg21bdc/dirpcs: created.
SQL script files /u01/ogg21bdc/dirsql: created.
Database definitions files /u01/ogg21bdc/dirdef: created.
Extract data files /u01/ogg21bdc/dirdat: created.
Temporary files /u01/ogg21bdc/dirtmp: created.
Credential store files /u01/ogg21bdc/dircrd: created.
Master encryption key wallet files /u01/ogg21bdc/dirwlt: created.
Dump files /u01/ogg21bdc/dirdmp: created.
Setup manager ports and start manager:
GGSCI (oggbigdata01) 11> view params mgr
port 7809
DYNAMICPORTLIST 7810-7820
GGSCI (oggbigdata01) 9> start mgr
Manager started.
GGSCI (oggbigdata01) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
To ingest data to Kafka, we need to setup kafka client on OGG bigdata server to be able to include kafka libs in OGG configuration.
$ podman exec -it oggbigdata01 bash
oggbigdata01:$ cd /u01
# You may check for the latest kafka software
oggbigdata01:$ curl -o kafka_2.13-3.7.1.tgz https://downloads.apache.org/kafka/3.7.1/kafka_2.13-3.7.1.tgz
oggbigdata01:$ tar xvfz kafka_2.13-3.7.1.tgz --no-same-owner
oggbigdata01:$ chown -R oracle:oinstall kafka_2.13-3.7.1
# Kafka libs are under /u01/kafka_2.13-3.7.1/libs
This may be a good point to commit container to create an image for OGG Bigdata classic.
$ podman commit oggbigdata01 oggbdcv01
Setup Kafka
In this lab we are testing Kafka and KafkaConnect handlers. We will be pulling latest Kafka container using podman-compose yaml configuration (https://github.com/apache/kafka/blob/d260b061803e3b9a2b4a0414f9a4f44b1f8d8f46/docker/examples/docker-compose-files/single-node/plaintext/docker-compose.yml) . KAFKA_ADVERTISED_LISTENERS is modified to correct problem with the connectivity from outside.
$ podman pull apache/kafka:latest
docker-compose.yml
---
version: '2'
services:
broker:
image: apache/kafka
hostname: broker
container_name: broker
ports:
- '9092:9092'
environment:
KAFKA_NODE_ID: 1
KAFKA_LISTENER_SECURITY_PROTOCOL_MAP: 'CONTROLLER:PLAINTEXT,PLAINTEXT:PLAINTEXT,PLAINTEXT_HOST:PLAINTEXT'
KAFKA_ADVERTISED_LISTENERS: 'PLAINTEXT_HOST://host.docker.internal:9092,PLAINTEXT://broker:19092'
KAFKA_PROCESS_ROLES: 'broker,controller'
KAFKA_CONTROLLER_QUORUM_VOTERS: '1@broker:29093'
KAFKA_LISTENERS: 'CONTROLLER://:29093,PLAINTEXT_HOST://:9092,PLAINTEXT://:19092'
KAFKA_INTER_BROKER_LISTENER_NAME: 'PLAINTEXT'
KAFKA_CONTROLLER_LISTENER_NAMES: 'CONTROLLER'
CLUSTER_ID: '4L6g3nShT-eMCtK--X86sw'
KAFKA_OFFSETS_TOPIC_REPLICATION_FACTOR: 1
KAFKA_GROUP_INITIAL_REBALANCE_DELAY_MS: 0
KAFKA_TRANSACTION_STATE_LOG_MIN_ISR: 1
KAFKA_TRANSACTION_STATE_LOG_REPLICATION_FACTOR: 1
KAFKA_LOG_DIRS: '/tmp/kraft-combined-logs'
# Create container
$ podman-compose up -d
# Check version and test
$ podman exec -it --user appuser broker bash
broker:$ cd /opt/kafka
broker:/opt/kafka$ bin/kafka-topics.sh --version
3.8.0
# ignore the warning, topic will be autocreated
broker:/opt/kafka$ bin/kafka-console-producer.sh --topic TEST01 --bootstrap-server host.containers.internal:9092
>this is test
[2024-07-28 12:10:47,554] WARN [Producer clientId=console-producer] Error while fetching metadata with correlation id 6 : {TEST01=UNKNOWN_TOPIC_OR_PARTITION} (org.apache.kafka.clients.NetworkClient)
>this is test 2
# ctrl-C to exit
broker:/opt/kafka$ bin/kafka-console-consumer.sh --topic TEST01 --from-beginning --bootstrap-server host.containers.internal:9092
this is test
this is test 2
# ctrl-C to exit
broker:/opt/kafka$ bin/kafka-topics.sh -delete -topic TEST01 --bootstrap-server host.containers.internal:9092
Setup OGG for Oracle Database as Source
We need an OGG source to generate trails from extracted logical change records (LCR) to deliver them to OGG Bigdata which will ingest them to target technology which is kafta in this lab. It is assumed that an OGG for Oracle Database is already in place to be used for this lab. If not, OGG for Oracle Database software can be installed on container we setup previously, or it can be installed on another container similary. However, an oracle database should also be created and configured to work with OGG for Oracle Database.
Maybe this guide will be updated at future with this setup and also with swingbench setup to generate transactions for testing.
Setup Replication
Source (OGG for Oracle Database)
Create extract:
GGSCI (myserver01.mydomain) 7> info CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: c##ggadmin
Userid: c##ggadmin@ORCL01
E01ORAC.prm
EXTRACT E01ORAC
USERIDALIAS c##ggadmin DOMAIN OracleGoldenGate
sourcecatalog MYDB
tranlogoptions excludetag 222
EXTTRAIL /u01/ggs21classic/dirdat/et
table soe.addresses;
table soe.customers;
table soe.orders;
table soe.order_items;
table soe.card_details;
table soe.logon;
table soe.product_information;
table soe.inventories;
table soe.product_descriptions;
table soe.warehouses;
table soe.orderentry_metadata;
table soe.cdrdemo;
table soe.test01, TOKENS (
TKOSUSER = @GETENV ('GGENVIRONMENT' , 'OSUSERNAME')
TKGROUP = @GETENV ('GGENVIRONMENT' , 'GROUPNAME')
TKHOST = @GETENV('GGENVIRONMENT' , 'HOSTNAME'));
table soe.test02;
table soe.test03;
table soe.SEG_;
table MYAPP.*;
GGSCI (myserver01.mydomain) 33> ADD extract E01ORAC, integrated tranlog, begin now;
Integrated Extract added.
GGSCI (myserver01.mydomain) 34> add exttrail /u01/ggs21classic/dirdat/et, extract E01ORAC
EXTTRAIL added.
GGSCI (myserver01.mydomain) 34> DBLOGIN USERIDALIAS c##ggadmin
Successfully logged into database CDB$ROOT.
GGSCI (myserver01.mydomain as c##ggadmin@orcl/CDB$ROOT) 68> REGISTER EXTRACT E01ORAC DATABASE CONTAINER (MYDB)
2024-07-28 17:01:48 INFO OGG-02003 Extract group E01ORAC successfully registered with database at SCN 62953303.
Create Pump:
P01ORAC.prm
EXTRACT P01ORAC
USERIDALIAS c##ggadmin DOMAIN OracleGoldenGate
RMTHOST oggbigdata01, MGRPORT 7809
RMTTRAIL /u01/ogg21bdc/dirdat/kt
PASSTHRU
TABLE *.*.*;
ADD EXTRACT P02ORAC, EXTTRAILSOURCE /u01/ggs21classic/dirdat/et
ADD RMTTRAIL /u01/ogg21bdc/dirdat/kt, EXTRACT P01ORAC
Target (OGG for Bigdata classic)
KAFKA
R01KK.prm
REPLICAT R01KK
TARGETDB LIBFILE libggjava.so SET property=dirprm/r01kk.properties
REPORTCOUNT EVERY 60 MINUTES, RATE
GROUPTRANSOPS 10
MAP *.*.*, TARGET *.*.*;
r01kk.properties
# Properties file for Replicat R01KK
#Kafka Handler Template
gg.handlerlist=kafkahandler
gg.handler.kafkahandler.type=kafka
#TODO: Set the name of the Kafka producer properties file.
gg.handler.kafkahandler.kafkaProducerConfigFile=r01kk-producer.properties
#TODO: Set the template for resolving the topic name.
gg.handler.kafkahandler.topicMappingTemplate=${tableName}
gg.handler.kafkahandler.keyMappingTemplate=${primaryKeys}
gg.handler.kafkahandler.SchemaTopicName = oggSchemaTopic
gg.handler.kafkahandler.mode=op
gg.handler.kafkahandler.format=json
gg.handler.kafkahandler.format.metaColumnsTemplate=${objectname[table]},${optype[op_type]},${timestamp[op_ts]},${currenttimestamp[current_ts]},${position[pos]}
#TODO: Set the location of the Kafka client libraries.
gg.classpath=/u01/kafka_2.13-3.7.1/libs/*
jvm.bootoptions=-Xmx2048m -Xms512m
javawriter.stats.full=TRUE
javawriter.stats.display=TRUE
gg.log=log4j
gg.log.level=info
gg.report.time=30sec
r01kk-producer.properties
bootstrap.servers = host.containers.internal:9092
acks = 1
reconnect.backoff.ms = 1000
value.serializer = org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer = org.apache.kafka.common.serialization.ByteArraySerializer
batch.size = 16384
linger.ms = 0
Create Replicat:
$ podman exec -it oggbigdata01 bash
oggbigdata01:$ cd /u01/ogg21bdc/
oggbigdata01:$ ./ggsci
Oracle GoldenGate for Big Data
Version 21.4.0.0.0 (Build 002)
Oracle GoldenGate Command Interpreter
Version 21.4.0.0.0 OGGCORE_21.4.0.0.0OGGRU_PLATFORMS_211022.1803
Oracle Linux 7, x64, 64bit (optimized), Generic on Oct 22 2021 23:14:43
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggbigdata01) 2> ADD REPLICAT R01KK EXTTRAIL /u01/ogg21bdc/dirdat/kt
Replicat added.
KAFKACONNECT
r02kk.prm
REPLICAT R02KK
TARGETDB LIBFILE libggjava.so SET property=dirprm/r02kk.properties
REPORTCOUNT EVERY 60 MINUTES, RATE
GROUPTRANSOPS 10
MAP *.*.*, TARGET *.*.*;
r02kk.properties
# Properties file for Replicat R02KK
#Kafkaconnect Handler Template
gg.handlerlist=kafkaconnect
gg.handler.kafkaconnect.type=kafkaconnect
#TODO: Set the name of the Kafkaconnect producer properties file.
gg.handler.kafkaconnect.kafkaProducerConfigFile=kafkaconnect01-producer.properties
#TODO: Set the template for resolving the topic name.
gg.handler.kafkaconnect.topicMappingTemplate=kkcnt_${tableName}
gg.handler.kafkaconnect.keyMappingTemplate=${currentTimestamp}
gg.handler.kafkaconnect.mode=op
gg.handler.kafkaconnect.messageFormatting=row
gg.handler.kafkaconnect.metaColumnsTemplate=${objectname[table]},${optype[op_type]},${timestamp[op_ts]},${currenttimestamp[current_ts]},${position[pos]},${primarykeycolumns[primary_keys]},${alltokens[tokens]}
#TODO: Set the location of the Kafkaconnect client libraries.
gg.classpath=/u01/kafka_2.13-3.7.1/libs/*
jvm.bootoptions=-Xmx2048m -Xms512m
javawriter.stats.full=TRUE
javawriter.stats.display=TRUE
gg.log=log4j
gg.log.level=info
gg.report.time=30sec
r02kk-producer.properties
bootstrap.servers = host.containers.internal:9092
acks = 1
reconnect.backoff.ms = 1000
#JSON Converter Settings
key.converter=org.apache.kafka.connect.json.JsonConverter
key.converter.schemas.enable=false
value.converter=org.apache.kafka.connect.json.JsonConverter
value.converter.schemas.enable=false
#Adjust for performance
batch.size=16384
linger.ms=0
# Fix https://support.oracle.com/knowledge/Middleware/2455697_1.html
converter.type=key
converter.type=value
converter.type=header
Create Replicat:
$ podman exec -it oggbigdata01 bash
oggbigdata01:$ cd /u01/ogg21bdc/
oggbigdata01:$ ./ggsci
Oracle GoldenGate for Big Data
Version 21.4.0.0.0 (Build 002)
Oracle GoldenGate Command Interpreter
Version 21.4.0.0.0 OGGCORE_21.4.0.0.0OGGRU_PLATFORMS_211022.1803
Oracle Linux 7, x64, 64bit (optimized), Generic on Oct 22 2021 23:14:43
Operating system character set identified as US-ASCII.
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (oggbigdata01) 2> ADD REPLICAT R02KK EXTTRAIL /u01/ogg21bdc/dirdat/kt
Replicat added.
TEST
Run Benchmark
$ cd swingbench/bin
$ ./charbench -c ../configs/SOE_Server_Side_V2.xml -cs //192.168.56.102:1521/mydb.mydomain -u soe -p welcome1 -v users,tpm,tps,cpu -rt 00:01
Check Replication
List the kafka topics:
$ podman exec -it --user appuser broker bash
broker:$ cd /opt/kafka
broker:/opt/kafka$ bin/kafka-topics.sh --list --bootstrap-server host.containers.internal:9092
ADDRESSES
CARD_DETAILS
CUSTOMERS
INVENTORIES
LOGON
ORDERS
ORDER_ITEMS
TEST01
kkcnt_ADDRESSES
kkcnt_CARD_DETAILS
kkcnt_CUSTOMERS
kkcnt_INVENTORIES
kkcnt_LOGON
kkcnt_ORDERS
kkcnt_ORDER_ITEMS
__consumer_offsets
oggSchemaTopic
Get contents of a topic:
$ podman exec -it --user appuser broker bash
broker:$ cd /opt/kafka
broker:/opt/kafka$
bin/kafka-console-consumer.sh --topic ADDRESSES --from-beginning --bootstrap-server host.containers.internal:9092 --max-messages 3
{"table":"MYDB.SOE.ADDRESSES","op_type":"I","op_ts":"2024-07-28 15:39:19.997958","current_ts":"2024-07-29 04:30:52.980000","pos":"00000000000000005022","after":{"ADDRESS_ID":2603168,"CUSTOMER_ID":2102806,"DATE_CREATED":"2024-07-28 17:39:00","HOUSE_NO_OR_NAME":"77","STREET_NAME":"Street Name","TOWN":"St.","COUNTY":"Berkshire","COUNTRY":"Brunei","POST_CODE":"Postcode","ZIP_CODE":null}}
{"table":"MYDB.SOE.ADDRESSES","op_type":"I","op_ts":"2024-07-28 15:39:20.997958","current_ts":"2024-07-29 04:30:53.857000","pos":"00000000000000048697","after":{"ADDRESS_ID":2603169,"CUSTOMER_ID":2102807,"DATE_CREATED":"2024-07-28 17:39:00","HOUSE_NO_OR_NAME":"15","STREET_NAME":"Street Name","TOWN":"Scarborough","COUNTY":"Glasgow","COUNTRY":"Brazil","POST_CODE":"Postcode","ZIP_CODE":null}}
{"table":"MYDB.SOE.ADDRESSES","op_type":"I","op_ts":"2024-07-28 15:39:20.997958","current_ts":"2024-07-29 04:30:53.879000","pos":"00000000000000051557","after":{"ADDRESS_ID":2603170,"CUSTOMER_ID":2102808,"DATE_CREATED":"2024-07-28 17:39:00","HOUSE_NO_OR_NAME":"8","STREET_NAME":"Street Name","TOWN":"Bristol","COUNTY":"Powys","COUNTRY":"Portugal","POST_CODE":"Postcode","ZIP_CODE":null}}
Script to delete topics for re-testing:
broker:/opt/kafka$ cat delete_kafka_topics.sh
#!/bin/bash
declare -a topiclist
topiclist=( $(bin/kafka-topics.sh --list --bootstrap-server host.containers.internal:9092|grep -v _consumer_offsets) )
for topic in "${topiclist[@]}"
do
echo Deleting topic $topic
bin/kafka-topics.sh -delete -topic $topic --bootstrap-server host.containers.internal:9092
done
echo List of topics:
bin/kafka-topics.sh --list --bootstrap-server host.containers.internal:9092
broker:/opt/kafka$ ./delete_kafka_topics.sh
Deleting topic ADDRESSES
Deleting topic CARD_DETAILS
Deleting topic CUSTOMERS
Deleting topic INVENTORIES
Deleting topic LOGON
Deleting topic ORDERS
Deleting topic ORDER_ITEMS
Deleting topic TEST01
Deleting topic oggSchemaTopic
List of topics:
TEST01
__consumer_offsets
To reposition replicat to start of the trail in order to re-test without need to execute betchmark again:
$ podman exec -it oggbigdata01 bash
oggbigdata01:$ cd /u01/ogg21bdc/
oggbigdata01:$ ./ggsci
GGSCI (oggbigdata01) 7> alter replicat R01KK EXTSEQNO 0 EXTRBA 0
2024-07-29 04:55:30 INFO OGG-06594 Replicat R01KK has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start R01KK with NOFILTERDUPTRANSACTIONS option.
Replicat altered.
GGSCI (oggbigdata01) 7> start replicat R02KK NOFILTERDUPTRANSACTIONS