Some notes that I made on installing and configuring Oracle GoldenGate with Confluent Platform. Excuse the brevity, but hopefully useful to share!
I used the Oracle Developer Days VM for this - it’s preinstalled with Oracle 12cR2. Big Data Lite is nice but currently has an older version of GoldenGate.
Login to the VM (oracle/oracle) and then install some useful things:
sudo rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install -y screen htop collectl rlwrap p7zip unzip sysstat perf iotop
sudo su -
cd /etc/yum.repos.d/
wget http://download.opensuse.org/repositories/shells:fish:release:2/CentOS_7/shells:fish:release:2.repo
yum install fish
Check Oracle version etc:
Install OGG
Download both Oracle GoldenGate 12.3.0.1 and Oracle GoldenGate for Big Data 12.3.1.1.0. For reference, here is the Install guide.
Make sure installers are present on VM
[oracle@localhost ~]$ ls -l ~/Downloads/
total 610368
-rw-r--r--. 1 oracle oinstall 543200432 Sep 5 08:45 123010_fbo_ggs_Linux_x64_shiphome.zip
-rw-r--r--. 1 oracle oinstall 81812011 Sep 5 08:38 123110_ggs_Adapters_Linux_x64.zip
Unzip the OGG installer
[oracle@localhost Downloads]$ unzip 123010_fbo_ggs_Linux_x64_shiphome.zip
Build a response file (e.g. /tmp/oggcore.rsp
)
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA12c
SOFTWARE_LOCATION=/u01/app/ogg
START_MANAGER=true
MANAGER_PORT=7809
DATABASE_LOCATION=/u01/app/oracle/product/12.2/db_1/
INVENTORY_LOCATION=/u01/app/oraInventory/
UNIX_GROUP_NAME=oracle
Install OGG:
[oracle@localhost Disk1]$ ~/Downloads/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -nowait -responseFile /tmp/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 13557 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4088 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-09-05_09-34-29AM. Please wait ...[oracle@localhost Disk1]$ [WARNING] [INS-75014] Database version cannot be determined from the location specified.
CAUSE: The components inventory may be missing or corrupted in the location specified.
ACTION: Specify an alternate database location.
You can find the log of this install session at:
/u01/installervb/logs/installActions2017-09-05_09-34-29AM.log
…
…
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/installervb/logs/silentInstall2017-09-05_09-34-29AM.log' for more details.
Successfully Setup Software.
Check that OGG Manager is running
[oracle@localhost ogg]$ . oraenv
ORACLE_SID = [oracle] ? orcl12c
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base has been set to /u01/app/oracle/product/12.2/db_1
[oracle@localhost ogg]$ cd /u01/app/ogg/
[oracle@localhost ogg]$ rlwrap ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (localhost.localdomain) 1> info mgr
Manager is running (IP port localhost.localdomain.7809, Process ID 23231).
GGSCI (localhost.localdomain) 2>
Configure DB for OGG 🔗
Since the DB is multitenant, need to use integrated capture mode.
Add TNS entry (per doc) to /u01/app/oracle/product/12.2/db_1/network/admin/tnsnames.ora
:
OGG_ORCL12C =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl12c)
)
)
Next, set up Minimum Database-level Supplemental Logging
Launch SQL*Plus: rlwrap sqlplus SYS/oracle@orcl12c as sysdba
Run the following:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
SHUTDOWN IMMEDIATE
STARTUP MOUNT
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH;
EXIT
Configure OGG Extract 🔗
Launch OGG command line:
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [oracle] ? orcl12c
ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base has been set to /u01/app/oracle/product/12.2/db_1
[oracle@localhost ~]$
[oracle@localhost ~]$ cd /u01/app/ogg/
[oracle@localhost ogg]$ rlwrap ./ggs
ggsci ggserr.log
[oracle@localhost ogg]$ rlwrap ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jul 21 2017 23:31:13
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (localhost.localdomain) 1>
Register the integrated Extract process, for the Container Database (orcl
) - this’ll take a minute or two to complete:
DBLOGIN USERID SYSTEM@localhost:1521/orcl12c PASSWORD oracle
REGISTER EXTRACT EXT1 DATABASE CONTAINER (ORCL)
Enter commands to enable schema logging with all columns captured, on schema HR
in the pluggable DB (ORCL
):
ADD SCHEMATRANDATA ORCL.HR ALLCOLS
We can now define the extract itself:
ADD EXTRACT EXT1, INTEGRATED TRANLOG, BEGIN NOW
Write a trail file for the extract
ADD EXTTRAIL ./dirdat/lt EXTRACT EXT1
Specify parameters for the extract:
EDIT PARAM EXT1
In the edit session paste:
EXTRACT EXT1
USERID SYSTEM@OGG_ORCL12C, PASSWORD oracle
EXTTRAIL ./dirdat/lt
SOURCECATALOG ORCL
TABLE HR.*;
Save and close the file. Now we’re ready to start the extract.
From the GGSCI
prompt issue:
START EXT1
and check that it’s running:
INFO EXT1
Expected status:
EXTRACT EXT1 Last Started 2017-09-05 11:21 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Process ID 27550
Log Read Checkpoint Oracle Integrated Redo Logs
2017-09-05 11:21:18
SCN 0.1957461 (1957461)
If it doesn’t start successfully then check /u01/app/ogg/ggserr.log
.
Smoketest 🔗
Log into SQL*Plus
rlwrap sqlplus SYS/oracle@orcl as sysdba
Insert a row and commit:
SQL> INSERT INTO HR.REGIONS VALUES (42,'FOO');
1 row created.
SQL> commit;
Commit complete.
Fire up OGG’s logdump
(ref):
[oracle@localhost ogg]$ rlwrap ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 12.3.0.1.0 OGGCORE_12.3.0.1.0_PLATFORMS_170721.0154
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
Logdump 11 >GHDR ON
Logdump 12 >DETAIL ON
Logdump 13 >DETAIL DATA
Logdump 14 >OPEN /u01/app/ogg/dirdat/lt000000000
Current LogTrail is /u01/app/ogg/dirdat/lt000000000
Logdump 15 >
Check the extract trail file and see the record added (preceeded by the table metadata):
Install OGG-BD 🔗
Doc: Installing Oracle GoldenGate for Big Data
Unpack OGG-BD into target folder:
mkdir /u01/app/ogg-bd
cp ~/Downloads/123110_ggs_Adapters_Linux_x64.zip /u01/app/ogg-bd/
cd /u01/app/ogg-bd/
unzip 123110_ggs_Adapters_Linux_x64.zip
tar -xf ggs_Adapters_Linux_x64.tar
Set LD_LIBRARY_PATH
environment variable (this needs to be set each time you prior to launching the MGR process through ggsci
, otherwise the replicat will abort with the error OGG-15050 Oracle GoldenGate Delivery, rkconn.prm: Error loading Java VM runtime library: (2 No such file or directory).
). Ref
[oracle@localhost ogg-bd]$ export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64/server/
[oracle@localhost ~]$ echo $JAVA_HOME
/home/oracle/java/jdk1.8.0_131
[oracle@localhost ~]$ echo $LD_LIBRARY_PATH
/home/oracle/java/jdk1.8.0_131/jre/lib/amd64/server/
[oracle@localhost ~]$
Create initial folders and create manager config
oracle@localhost /u/a/ogg-bd> rlwrap ./ggsci
Oracle GoldenGate for Big Data
Version 12.3.1.1.0
Oracle GoldenGate Command Interpreter
Version 12.3.0.1.0 OGGCORE_OGGADP.12.3.0.1.0GA_PLATFORMS_170810.0015
Linux, x64, 64bit (optimized), Generic on Aug 10 2017 01:26:22
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (localhost.localdomain) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/ogg-bd
Parameter file /u01/app/ogg-bd/dirprm: created.
Report file /u01/app/ogg-bd/dirrpt: created.
Checkpoint file /u01/app/ogg-bd/dirchk: created.
Process status files /u01/app/ogg-bd/dirpcs: created.
SQL script files /u01/app/ogg-bd/dirsql: created.
Database definitions files /u01/app/ogg-bd/dirdef: created.
Extract data files /u01/app/ogg-bd/dirdat: created.
Temporary files /u01/app/ogg-bd/dirtmp: created.
Credential store files /u01/app/ogg-bd/dircrd: created.
Masterkey wallet files /u01/app/ogg-bd/dirwlt: created.
Dump files /u01/app/ogg-bd/dirdmp: created.
GGSCI (localhost.localdomain) 2> EDIT PARAM MGR
In the config file put:
PORT 7801
And then from the ggsci
prompt start the manager and confirm that it’s running
GGSCI (localhost.localdomain) 3> start mgr
Manager started.
GGSCI (localhost.localdomain) 4> info mgr
Manager is running (IP port localhost.localdomain.7801, Process ID 28707).
Install Confluent Platform 3.3
sudo rpm --import http://packages.confluent.io/rpm/3.3/archive.key
Add to /etc/yum.repos.d/confluent.repo
[Confluent.dist]
name=Confluent repository (dist)
baseurl=http://packages.confluent.io/rpm/3.3/7
gpgcheck=1
gpgkey=http://packages.confluent.io/rpm/3.3/archive.key
enabled=1
[Confluent]
name=Confluent repository
baseurl=http://packages.confluent.io/rpm/3.3
gpgcheck=1
gpgkey=http://packages.confluent.io/rpm/3.3/archive.key
enabled=1
Install Confluent Enterprise
sudo yum clean all
sudo yum install confluent-platform-2.11
Modify Oracle to shut down HTTP listener on port 8081 since we don’t need it and it clashes with Schema Registry. As SYSDBA run on each CDB/PDB run:
exec dbms_xdb.sethttpport(0);
Then stop/start the listener.
To start Confluent Platform run
confluent start
Configure & Smoke Test OGG-Kafka Connect → Kafka
Configure for OGG-BD Kafka Connect handler 🔗
(Doc)
Put these files in /u01/app/ogg-bd/dirprm
:
Be very careful with the above configuration files for any trailing whitespace - it can cause problem, detailed here.
Launch ggsci
:
cd /u01/app/ogg-bd && rlwrap ./ggsci
[Re-]Add replicat:
stop rkconn
pause 1
delete replicat rkconn
pause 1
add replicat rkconn, exttrail /u01/app/ogg/dirdat/lt
pause 1
start rkconn
Check status
info all
info rkconn
Expect:
GGSCI (localhost.localdomain) 37> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RKCONN 00:00:00 00:00:05
GGSCI (localhost.localdomain) 38> info rkconn
REPLICAT RKCONN Last Started 2017-09-27 13:50 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:09 ago)
Process ID 15843
Log Read Checkpoint File /u01/app/ogg/dirdat/lt000000002
2017-09-27 13:43:27.000000 RBA 2393
Check logfile /u01/app/ogg-bd/ggserr.log
and /u01/app/ogg-bd/dirrpt/*
for any errors.
Smoke test OGG – Kafka Connect –> Kafka 🔗
Insert a row in Oracle (as done already above)
-
Log into SQL*Plus
rlwrap sql SYS/oracle@orcl as sysdba
-
Insert a row and commit:
SQL> INSERT INTO HR.REGIONS VALUES (42,'FOO'); 1 row created. SQL> commit; Commit complete.
Check that the Kafka topic has been created
kafka-topics --zookeeper localhost:2181 --list
...
ora-ogg-HR-COUNTRIES-avro
ora-ogg-HR-REGIONS-avro
...
View the record
kafka-avro-console-consumer \
--bootstrap-server localhost:9092 \
--property schema.registry.url=http://localhost:8081 \
--property print.key=true \
--from-beginning \
--topic ora-ogg-HR-COUNTRIES-avro
Optionally install jq
(sudo yum install jq
) to pretty-print the JSON displayed (remember the message is still in Avro in Kafka internally though)
$ kafka-avro-console-consumer \
--bootstrap-server localhost:9092 \
--property schema.registry.url=http://localhost:8081 \
--from-beginning \
--topic ora-ogg-HR-COUNTRIES-avro|jq '.'
{
"table": {
"string": "ORCL.HR.COUNTRIES"
},
"op_type": {
"string": "I"
},
"op_ts": {
"string": "2017-09-12 22:26:11.000000"
},
"current_ts": {
"string": "2017-09-27 13:50:59.279000"
},
"pos": {
"string": "00000000010000002739"
},
"COUNTRY_ID": {
"string": "XX"
},
"COUNTRY_NAME": {
"string": "FOO"
},
"REGION_ID": {
"double": 42
}
}
Bonus: Install Swingbench and build/seed schema
unzip swingbench261046.zip
sudo mv swingbench /opt
Create the tablespace and user manually so that we can capture everything with GoldenGate (there’s probably a better way to do this?)
Define the Extract properties
cat >> /u01/app/ogg/dirprm/EXT_SOE.prm<<EOF
EXTRACT EXT_SOE
USERID SYSTEM@OGG_ORCL12C, PASSWORD oracle
EXTTRAIL ./dirdat/oe
SOURCECATALOG ORCL
TABLE SOE.*;
EOF
Then launch OGG
cd /u01/app/ogg/
rlwrap ./ggsci
and set up capture of the schema
DBLOGIN USERID SYSTEM@localhost:1521/orcl12c PASSWORD oracle
REGISTER EXTRACT EXT_SOE DATABASE CONTAINER (ORCL)
ADD SCHEMATRANDATA ORCL.SOE ALLCOLS
ADD EXTRACT EXT_SOE, INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/oe EXTRACT EXT_SOE
START EXT_SOE
Now run Swingbench oewizard
, and the creation of the tables and data load will be captured in OGG:
/opt/swingbench/bin/oewizard -cs localhost:1521/orcl -cl -scale 0.1 -dbap oracle -u soe -p soe -v -create
Swingbench output:
SwingBench Wizard
Author : Dominic Giles
Version : 2.6.0.1046
Running in Lights Out Mode using config file : ../wizardconfigs/oewizard.xml
[...]
============================================
| Datagenerator Run Stats |
============================================
Connection Time 0:00:00.003
Data Generation Time 0:01:03.240
DDL Creation Time 0:00:43.730
Total Run Time 0:01:46.979
Rows Inserted per sec 19,200
Data Generated (MB) per sec 1.9
Actual Rows Generated 2,116,908
Connecting to : jdbc:oracle:thin:@localhost:1521/orcl
Connected
Post Creation Validation Report
===============================
The schema appears to have been created successfully.
Valid Objects
=============
Valid Tables : 'ORDERS','ORDER_ITEMS','CUSTOMERS','WAREHOUSES','ORDERENTRY_METADATA','INVENTORIES','PRODUCT_INFORMATION','PRODUCT_DESCRIPTIONS','ADDRESSES','CARD_DETAILS'
Valid Indexes : 'PRD_DESC_PK','PROD_NAME_IX','PRODUCT_INFORMATION_PK','PROD_SUPPLIER_IX','PROD_CATEGORY_IX','INVENTORY_PK','INV_PRODUCT_IX','INV_WAREHOUSE_IX','ORDER_PK','ORD_SALES_REP_IX','ORD_CUSTOMER_IX','ORD_ORDER_DATE_IX','ORD_WAREHOUSE_IX','ORDER_ITEMS_PK','ITEM_ORDER_IX','ITEM_PRODUCT_IX','WAREHOUSES_PK','WHS_LOCATION_IX','CUSTOMERS_PK','CUST_EMAIL_IX','CUST_ACCOUNT_MANAGER_IX','CUST_FUNC_LOWER_NAME_IX','ADDRESS_PK','ADDRESS_CUST_IX','CARD_DETAILS_PK','CARDDETAILS_CUST_IX'
Valid Views : 'PRODUCTS','PRODUCT_PRICES'
Valid Sequences : 'CUSTOMER_SEQ','ORDERS_SEQ','ADDRESS_SEQ','LOGON_SEQ','CARD_DETAILS_SEQ'
Valid Code : 'ORDERENTRY'
Schema Created
oracle@localhost /o/swingbench>
For more details on how to do cool stuff with Swingbench data, including in KSQL for live joining of events with reference data, keep an eye on the Confluent blog…