In this article we would configure Oracle GoldenGate for Big Data Kafka Handler to replicate change capture data from a Oracle GoldenGate trail to a Kafka topic. We would use 2 different servers, hosing multiple applications. Please find below details:-
Host : fangorn.oradba.com
Database : Oracle 11gR2
GG : Oracle GoldenGate (12.2.0.1.0)
Host : hyd01db001.oradba.com
Zookeeper : zookeeper-3.4.10
Kafka : Kafka 2.11-0.11
GG : Oracle GoldenGate for Big Data (12.2.0.1.0)
Note: Throughout the entire installation and configuration, my OS user is "oracle" on both servers, unless mentioned.
Update host entries on both server:-
[oracle@fangorn ~]$ sudo su -
[root@fangorn ~]# vi /etc/hosts
172.3.21.102 hyd01db001.oradba.com hyd01db001
172.3.21.32 fangorn.oradba.com fangorn
Then copy the same /etc/hosts file on hyd01db001 server as well
[root@fangorn ~]# scp /etc/hosts root@172.3.21.102:/etc/
Installing JAVA on HYD01DB001
Lets check Java is configured on hyd01db001 server.
[oracle@hyd01db001 ~]$ type java
java is /usr/bin/java
[oracle@hyd01db001 ~]$ readlink -f /usr/bin/java
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131.x86_64/jre/bin/java
[oracle@hyd01db001 ~]$ type javac
javac is /usr/bin/javac
[oracle@hyd01db001 ~]$ readlink -f /usr/bin/javac
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131.x86_64/bin/javac
Let's get rid of this open source java and install Oracle Java. Switch to root user
[oracle@hyd01db001 ~]$ sudo su -
Check for JDK
[root@hyd01db001 ~]# rpm -qa|grep -i java
java-1.6.0-openjdk-1.6.0.41-1.13.13.1.el6_8.x86_64
java-1.7.0-openjdk-devel-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
java-1.7.0-openjdk-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
java-1.6.0-openjdk-devel-1.6.0.41-1.13.13.1.el6_8.x86_64
Uninstall RPMs
[root@hyd01db001 ~]# rpm -e java-1.6.0-openjdk-devel-1.6.0.41-1.13.13.1.el6_8.x86_64
[root@hyd01db001 ~]# rpm -e java-1.6.0-openjdk-1.6.0.41-1.13.13.1.el6_8.x86_64
[root@hyd01db001 ~]# rpm -e java-1.7.0-openjdk-devel-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
[root@hyd01db001 ~]# rpm -e java-1.7.0-openjdk-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
Installing Java
[root@hyd01db001 ~]# cd /opt
[root@hyd01db001 opt]# wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u144-b01/090f390dda5b47b9b721c7dfaa008135/jdk-8u144-linux-x64.tar.gz"
[root@hyd01db001 opt]# tar -xzvf jdk-8u144-linux-x64.tar.gz
[root@hyd01db001 opt]# cd jdk1.8.0_144/
[root@hyd01db001 jdk1.8.0_144]# alternatives --install /usr/bin/java java /opt/jdk1.8.0_144/bin/java 2
In case we have multiple java configured, ensure we set the binaries to the current installed Java. Type the desired selection number to set any Java version. However, since I have earlier removed open source Java, I don't have multiple version on this server.
[root@hyd01db001 jdk1.8.0_144]# alternatives --config java
There is 1 program that provides 'java'.
Selection Command
-----------------------------------------------
*+ 1 /opt/jdk1.8.0_144/bin/java
Enter to keep the current selection[+], or type selection number: 1
[root@hyd01db001 jdk1.8.0_144]# alternatives --install /usr/bin/jar jar /opt/jdk1.8.0_144/bin/jar 2
[root@hyd01db001 jdk1.8.0_144]# alternatives --set jar /opt/jdk1.8.0_144/bin/jar
[root@hyd01db001 jdk1.8.0_144]# alternatives --install /usr/bin/javac javac /opt/jdk1.8.0_144/bin/javac 2
[root@hyd01db001 jdk1.8.0_144]# alternatives --set javac /opt/jdk1.8.0_144/bin/javac
[root@hyd01db001 jdk1.8.0_144]# exit
Add below lines to .bash_profile and source it:
[oracle@hyd01db001 ~]$ vi ~/.bash_profile
JAVA_HOME=/opt/jdk1.8.0_144; export JAVA_HOME
JRE_HOME=/opt/jdk1.8.0_144/jre; export JRE_HOME
export PATH=$PATH:${JAVA_HOME}/bin:$JAVA_HOME/db/bin:${JRE_HOME}/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${JAVA_HOME}/lib:${JAVA_HOME}/jre/lib/amd64/server
[oracle@hyd01db001 ~]$ . ~/.bash_profile
Installing Zookeeper on HYD01DB001
I'm using Zookeeper 3.4.10 version, downloaded from http://www-eu.apache.org/dist/zookeeper/zookeeper-3.4.10/
I have saved binary tar file [zookeeper-3.4.10.tar.gz] on below location;
[oracle@hyd01db001 ~]$ cd /oradba/app/apache/
[oracle@hyd01db001 apache]$ ls -lrth stage/
total 34M
-rwxr-x--- 1 oracle dba 34M Jul 28 13:44 zookeeper-3.4.10.tar.gz
Let's untar it;
[oracle@hyd01db001 apache]$ tar -xzvf stage/zookeeper-3.4.10.tar.gz
[oracle@hyd01db001 apache]$ cd zookeeper-3.4.10/conf
[oracle@hyd01db001 conf]$ cp zoo_sample.cfg zoo.cfg
Creating new directory for Zookeeper data
[oracle@hyd01db001 conf]$ mkdir /oradba/app/apache/zoo-data
Update dataDir location and save the file
[oracle@hyd01db001 conf]$ vi zoo.cfg
dataDir=/oradba/app/apache/zoo-data
[oracle@hyd01db001 conf]$ export ZOOKEEPER_HOME=/oradba/app/apache/zookeeper-3.4.10
[oracle@hyd01db001 bin]$ cd $ZOOKEEPER_HOME
We won't start Zookeeper right now. Instead would start once Kafka is configured.
Installing Kafka on HYD01DB001
I'm using Kafka 2.11-0.11, downloaded from https://www.apache.org/dyn/closer.cgi?path=/kafka/0.11.0.0/kafka_2.11-0.11.0.0.tgz
Saved the binary tar file on below location;
[oracle@hyd01db001 apache]$ pwd
/oradba/app/apache
[oracle@hyd01db001 apache]$ ls -lrth stage/
total 38M
-rwxr-x--- 1 oracle dba 34M Jul 28 13:44 zookeeper-3.4.10.tar.gz
-rwxr-x--- 1 oracle dba 4.3M Jul 28 14:06 kafka_2.11-0.11.0.0.tgz
Let's untar it;
[oracle@hyd01db001 apache]$ tar -xzvf stage/kafka_2.11-0.11.0.0.tgz
[oracle@hyd01db001 apache]$ ls -lrh
total 16K
drwxr-xr-x 10 oracle dba 4.0K Jul 28 14:03 zookeeper-3.4.10/
drwxr-xr-x 3 oracle dba 4.0K Jul 28 14:03 zoo-data/
drwxr-xr-x 2 oracle dba 4.0K Jul 28 14:31 stage/
drwxr-xr-x 6 oracle dba 4.0K Jun 23 03:39 kafka_2.11-0.11.0.0/
[oracle@hyd01db001 apache]$ cd kafka_2.11-0.11.0.0/
Update dataDir location
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ vi config/zookeeper.properties
dataDir=/oradba/app/apache/zoo-data
Since we have not changed default port for zookeeper client, we stick to 2181 on this file as well
Creating a log directory for Kafka
[oracle@hyd01db001 apache]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ export KAFKA_HOME=/oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ mkdir kafka-log
Update the log directory on server.properties file
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ vi config/server.properties
log.dirs=/oradba/app/apache/kafka_2.11-0.11.0.0/kafka-log
Startup Zookeeper
oracle@hyd01db001 apache]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/zookeeper-server-start.sh config/zookeeper.properties >logs/zookeeper-runtime.log 2>&1 &
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ jps
2929 TMMain
11906 QuorumPeerMain
3306 Jps
Start Kafka
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-server-start.sh config/server.properties > logs/kafka-runtime.log 2>&1 &
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ jps
2929 TMMain
3873 Kafka
11906 QuorumPeerMain
4170 Jps
Configure Database for goldengate on Fangorn
[oracle@fangorn ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 28 17:33:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
GNSH1 READ WRITE
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> -- Enable force logging and minimal supplemental logging
SQL> alter database add supplemental log data;
SQL> alter database force logging;
SQL> select log_mode, force_logging, supplemental_log_data_min from v$database;
LOG_MODE FOR SUPPLEME
------------ --- --------
ARCHIVELOG YES YES
SQL> alter system set enable_goldengate_replication=true sid='*' scope=both;
SQL> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- -----------
enable_goldengate_replication boolean TRUE
SQL> create user gg_admin identified by gg_admin default tablespace users temporary tablespace temp;
SQL> grant create session, create table, alter any table, alter system, dba to gg_admin;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('gg_admin','*', TRUE, TRUE);
Installing Goldengate on fangorn
Software location:
[oracle@fangorn stage]$ pwd
/oradba/app/stage
[oracle@fangorn stage]$ ls –lrth
total 454M
-rw-r--r-- 1 oracle dba 454M Jan 19 2016 V100692-01.zip
[oracle@fangorn stage]$ unzip V100692-01.zip
[oracle@fangorn stage]$ ls –lrth
total 454M
drwxr-xr-x 3 oracle dba 4.0K Dec 12 2015 fbo_ggs_Linux_x64_shiphome/
-rw-r--r-- 1 oracle dba 1.6K Jan 19 2016 OGG-12.2.0.1-README.txt
-rw-r--r-- 1 oracle dba 276K Jan 19 2016 OGG-12.2.0.1.1-ReleaseNotes.pdf
-rw-r--r-- 1 oracle dba 454M Jan 19 2016 V100692-01.zip
[oracle@fangorn stage]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@fangorn Disk1]$ ls –lrth
total 16K
drwxr-xr-x 4 oracle dba 4.0K Dec 12 2015 install/
drwxr-xr-x 11 oracle dba 4.0K Dec 12 2015 stage/
-rwxr-xr-x 1 oracle dba 918 Dec 12 2015 runInstaller
drwxrwxr-x 2 oracle dba 4.0K Dec 12 2015 response/
Creating response file [find attached response file for 11g Database version]
[oracle@fangorn Disk1]$ vi /oradba/app/oggcore.rsp
[oracle@fangorn Disk1]$ ./runInstaller -silent -responseFile /oradba/app/oggcore.rsp -waitforcompletion
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 48221 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-07-28_05-02-24PM. Please wait ...You can find the log of this install session at:
/oradba/app/oraInventory/logs/installActions2017-07-28_05-02-24PM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/oradba/app/oraInventory/logs/silentInstall2017-07-28_05-02-24PM.log' for more details.
Successfully Setup Software.
Connecting to GGSCI prompt and creating sub-directories
[oracle@fangorn Disk1]$ cd /oradba/app/gghome/
[oracle@fangorn gghome]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (fangorn.oradba.com) 1> create subdirs
Creating subdirectories under current directory /oradba/app/gghome
Parameter files /oradba/app/gghome/dirprm: created
Report files /oradba/app/gghome/dirrpt: created
Checkpoint files /oradba/app/gghome/dirchk: created
Process status files /oradba/app/gghome/dirpcs: created
SQL script files /oradba/app/gghome/dirsql: created
Database definitions files /oradba/app/gghome/dirdef: created
Extract data files /oradba/app/gghome/dirdat: created
Temporary files /oradba/app/gghome/dirtmp: created
Credential store files /oradba/app/gghome/dircrd: created
Masterkey wallet files /oradba/app/gghome/dirwlt: created
Dump files /oradba/app/gghome/dirdmp: created
GGSCI (fangorn.oradba.com) 2> exit
Creating KEY using AES-256 cipher [TOTALLY OPTIONAL]
[oracle@fangorn gghome]$ ./keygen 256 1
0x995671248E346B4814E9A17910811A16914B8B5F6BC15F697672417C8367AA5A
[oracle@fangorn gghome]$ cat > ENCKEYS
gg_owner 0x995671248E346B4814E9A17910811A16914B8B5F6BC15F697672417C8367AA5A
^C
[oracle@fangorn gghome]$ cat ENCKEYS
gg_owner 0x995671248E346B4814E9A17910811A16914B8B5F6BC15F697672417C8367AA5A
Encrypting gg_admin password [TOTALLY OPTIONAL]
GGSCI (fangorn.oradba.com) 1> encrypt password gg_admin AES256 encryptkey gg_owner
Encrypted password: AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC
Algorithm used: AES256
Creating and Starting Manager Process
GGSCI (fangorn.oradba.com) 2> edit param mgr
PORT 7809
USERID gg_admin@gnsh1, PASSWORD AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC, AES256, ENCRYPTKEY gg_owner
PURGEOLDEXTRACTS /oradba/app/gghome/dirdat/*, USECHECKPOINTS
GGSCI (fangorn.oradba.com) 3> start mgr
Manager started.
GGSCI (fangorn.oradba.com) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Adding Schematrandata for "U1" database user
GGSCI (fangorn.oradba.com) 5> dblogin USERID gg_admin@gnsh1, PASSWORD AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC, AES256, ENCRYPTKEY gg_owner
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 7> info schematrandata u1
2017-07-28 18:07:54 INFO OGG-01786 Schema level supplemental logging is disabled on schema U1.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 8> add schematrandata u1 allcols
2017-07-28 18:08:15 INFO OGG-01788 SCHEMATRANDATA has been added on schema u1.
2017-07-28 18:08:15 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema u1.
2017-07-28 18:08:15 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema u1.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 9> info schematrandata u1
2017-07-28 18:08:24 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema U1.
2017-07-28 18:08:24 INFO OGG-01981 Schema level supplemental logging is enabled on schema U1 for all columns.
2017-07-28 18:08:24 INFO OGG-10462 Schema U1 have 1 prepared tables for instantiation.
Configuring and Starting Extract
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 12> edit param eu1
EXTRACT eu1
USERID gg_admin@gnsh1, PASSWORD AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC, AES256, ENCRYPTKEY gg_owner
EXTTRAIL ./dirdat/u1
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 4194304
TRANLOGOPTIONS DBLOGREADERBUFSIZE 4194304
TABLE u1.*;
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 13> ADD EXTRACT eu1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 14> ADD EXTTRAIL ./dirdat/u1, EXTRACT eu1, megabytes 100
EXTTRAIL added.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EU1 00:00:00 00:00:11
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 16> start eu1
Sending START request to MANAGER …
EXTRACT EU1 starting
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 17> info eu1
EXTRACT EU1 Last Started 2017-07-28 18:27 Status RUNNING
Checkpoint Lag 00:04:40 (updated 00:00:01 ago)
Process ID 25462
Log Read Checkpoint Oracle Redo Logs
2017-07-28 18:22:46 Seqno 44, RBA 54767120
SCN 0.0 (0) Configuring and Starting PumpGGSCI (fangorn.oradba.com as gg_admin@gnsh1) 18> edit param pu1
EXTRACT pu1
RMTHOST hyd01db001.oradba.com, MGRPORT 7809, TIMEOUT 30
RMTTRAIL /oradba/app/gghome/dirdat/u1
PASSTHRU
TABLE u1.*;
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 29> add extract pu1 EXTTRAILSOURCE /oradba/app/gghome/dirdat/u1
EXTRACT added.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 30> add rmttrail /oradba/app/gghome/dirdat/u1, extract pu1 , megabytes 100
RMTTRAIL added.
Installing Goldengate Big Data Adapter on HYD01DB001
Software Location:
[oracle@hyd01db001 stage]$ pwd
/oradba/app/stage
[oracle@hyd01db001 stage]$ ls –lrth
total 200M
-rw-r--r-- 1 oracle dba 200M May 18 2016 V138402-01.zip
Unzip the software
[oracle@hyd01db001 stage]$ unzip V138402-01.zip
Archive: V138402-01.zip
inflating: ggs_Adapters_Linux_x64.tar
inflating: OGGBD-12.2.0.1.1-README.txt
inflating: OGG_BigData_12.2.0.1.1_Release_Notes.pdf
[oracle@hyd01db001 stage]$ cd ../gghome/
[oracle@hyd01db001 gghome]$ pwd
/oradba/app/gghome
[oracle@hyd01db001 gghome]$ tar -xvf /oradba/app/stage/ggs_Adapters_Linux_x64.tar
Creating Goldengate sub-directories
[oracle@hyd01db001 gghome]$ ./ggsci
GGSCI (hyd01db001.oradba.com) 2> create subdirs
Creating subdirectories under current directory /oradba/app/gghome
Parameter files /oradba/app/gghome/dirprm: created
Report files /oradba/app/gghome/dirrpt: created
Checkpoint files /oradba/app/gghome/dirchk: created
Process status files /oradba/app/gghome/dirpcs: created
SQL script files /oradba/app/gghome/dirsql: created
Database definitions files /oradba/app/gghome/dirdef: created
Extract data files /oradba/app/gghome/dirdat: created
Temporary files /oradba/app/gghome/dirtmp: created
Credential store files /oradba/app/gghome/dircrd: created
Masterkey wallet files /oradba/app/gghome/dirwlt: created
Dump files /oradba/app/gghome/dirdmp: created
Configuring and Starting Manager
GGSCI (hyd01db001.oradba.com) 3> edit param mgr
PORT 7809
GGSCI (hyd01db001.oradba.com) 5> start mgr
Manager started.
GGSCI (hyd01db001.oradba.com) 6> exit
Configuring and Starting Replicat - [Kafka handle]
[oracle@hyd01db001 gghome]$ vi dirprm/kafka.props
gg.handlerlist=kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
gg.handler.kafkahandler.TopicName=u1topic
gg.handler.kafkahandler.format=xml
gg.handler.kafkahandler.SchemaTopicName=mySchemaTopic
gg.handler.kafkahandler.BlockingSend=false
gg.handler.kafkahandler.includeTokens=false
##
## Goldengate will consider entire transaction
## as a single message
##
## gg.handler.kafkahandler.mode=tx
##
## Goldengate will consider every operation
## as single message
##
gg.handler.kafkahandler.mode=op
#gg.handler.kafkahandler.maxGroupSize=100, 1Mb
#gg.handler.kafkahandler.minGroupSize=50, 500Kb
goldengate.userexit.timestamp=utc
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE
gg.log=log4j
gg.log.level=INFO
gg.report.time=30sec
gg.classpath=dirprm/:/oradba/app/apache/kafka_2.11-0.11.0.0/libs/*:
javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar
[oracle@hyd01db001 gghome]$ vi dirprm/custom_kafka_producer.properties
bootstrap.servers=hyd01db001.oradba.com:9092
acks=1
compression.type=gzip
reconnect.backoff.ms=1000
value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
# 100KB per partition
batch.size=102400
linger.ms=10000
[oracle@hyd01db001 gghome]$ vi dirprm/rkaf.prm
REPLICAT rkaf
TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
MAP u1.*, TARGET u1topic.*;
Create Kafka Topicoracle@hyd01db001 ~]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-topics.sh --zookeeper hyd01db001.oradba.com:2181 --create --topic u1topic --partitions 1 --replication-factor 1
Created topic "u1topic".
List of Topics
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-topics.sh --zookeeper hyd01db001.oradba.com:2181 --list
ADD and Start GG_BigData_Adapter Replicatoracle@hyd01db001 ~]$ cd /oradba/app/gghomeoracle@hyd01db001 gghome]$ ./ggsciGGSCI (hyd01db001.oradba.com) 1> add replicat rkaf exttrail ./dirdat/u1 begin now
REPLICAT added.
GGSCI (hyd01db001.oradba.com) 2> start rkaf
Start Kafka Consumer to read messages
=====================================
[oracle@hyd01db001 ~]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-console-consumer.sh --bootstrap-server hyd01db001.oradba.com:9092 --topic u1topic
SAMPLE XML KAFKA OUTPUT
Output explanation:
operation table = <topic_name>.<table_name>
type = type of DML like I: Insert, U: Update, D: Delete
ts = timestamp
pos = refers to RBA of the trailfile. We can traceback using Logdump utility
numCols = number of columns
INSERT
<operation table='U1TOPIC.T2' type='I' ts='2017-07-28 14:30:26.012988' current_ts='2017-07-28T20:00:32.188000' pos='00000000000000004254' numCols='3'>
<col name='SR_NO' index='0'>
<before missing='true'/>
<after><![CDATA[2]]></after>
</col>
<col name='NAME' index='1'>
<before missing='true'/>
<after><![CDATA[JEFF]]></after>
</col>
<col name='REGION' index='2'>
<before missing='true'/>
<after><![CDATA[UTAH]]></after>
</col>
</operation>
UPDATE
<operation table='U1TOPIC.T2' type='U' ts='2017-07-28 14:34:08.013062' current_ts='2017-07-28T20:04:15.314000' pos='00000000000000004390' numCols='3'>
<col name='SR_NO' index='0'>
<before><![CDATA[2]]></before>
<after><![CDATA[2]]></after>
</col>
<col name='NAME' index='1'>
<before><![CDATA[JEFF]]></before>
<after><![CDATA[JOHN]]></after>
</col>
<col name='REGION' index='2'>
<before><![CDATA[UTAH]]></before>
<after><![CDATA[UTAH]]></after>
</col>
</operation>
DELETE
<operation table='U1TOPIC.T2' type='D' ts='2017-07-28 14:35:03.013005' current_ts='2017-07-28T20:05:10.336000' pos='00000000000000004563' numCols='3'>
<col name='SR_NO' index='0'>
<before><![CDATA[2]]></before>
<after missing='true'/>
</col>
<col name='NAME' index='1'>
<before><![CDATA[JOHN]]></before>
<after missing='true'/>
</col>
<col name='REGION' index='2'>
<before><![CDATA[UTAH]]></before>
<after missing='true'/>
</col>
</operation>
Goldengate Response File used in Fangorn Server
[oracle@fangorn ~]$ cat /oradba/app/oggcore.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/oradba/app/gghome
START_MANAGER=false
DATABASE_LOCATION=/oradba/app/oracle/product/11204
INVENTORY_LOCATION=/oradba/app/oraInventory
UNIX_GROUP_NAME=dba
Host : fangorn.oradba.com
Database : Oracle 11gR2
GG : Oracle GoldenGate (12.2.0.1.0)
Host : hyd01db001.oradba.com
Zookeeper : zookeeper-3.4.10
Kafka : Kafka 2.11-0.11
GG : Oracle GoldenGate for Big Data (12.2.0.1.0)
Note: Throughout the entire installation and configuration, my OS user is "oracle" on both servers, unless mentioned.
Update host entries on both server:-
[oracle@fangorn ~]$ sudo su -
[root@fangorn ~]# vi /etc/hosts
172.3.21.102 hyd01db001.oradba.com hyd01db001
172.3.21.32 fangorn.oradba.com fangorn
Then copy the same /etc/hosts file on hyd01db001 server as well
[root@fangorn ~]# scp /etc/hosts root@172.3.21.102:/etc/
Installing JAVA on HYD01DB001
Lets check Java is configured on hyd01db001 server.
[oracle@hyd01db001 ~]$ type java
java is /usr/bin/java
[oracle@hyd01db001 ~]$ readlink -f /usr/bin/java
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131.x86_64/jre/bin/java
[oracle@hyd01db001 ~]$ type javac
javac is /usr/bin/javac
[oracle@hyd01db001 ~]$ readlink -f /usr/bin/javac
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.131.x86_64/bin/javac
Let's get rid of this open source java and install Oracle Java. Switch to root user
[oracle@hyd01db001 ~]$ sudo su -
Check for JDK
[root@hyd01db001 ~]# rpm -qa|grep -i java
java-1.6.0-openjdk-1.6.0.41-1.13.13.1.el6_8.x86_64
java-1.7.0-openjdk-devel-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
java-1.7.0-openjdk-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
java-1.6.0-openjdk-devel-1.6.0.41-1.13.13.1.el6_8.x86_64
Uninstall RPMs
[root@hyd01db001 ~]# rpm -e java-1.6.0-openjdk-devel-1.6.0.41-1.13.13.1.el6_8.x86_64
[root@hyd01db001 ~]# rpm -e java-1.6.0-openjdk-1.6.0.41-1.13.13.1.el6_8.x86_64
[root@hyd01db001 ~]# rpm -e java-1.7.0-openjdk-devel-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
[root@hyd01db001 ~]# rpm -e java-1.7.0-openjdk-1.7.0.131-2.6.9.0.0.1.el6_8.x86_64
Installing Java
[root@hyd01db001 ~]# cd /opt
[root@hyd01db001 opt]# wget --no-cookies --no-check-certificate --header "Cookie: gpw_e24=http%3A%2F%2Fwww.oracle.com%2F; oraclelicense=accept-securebackup-cookie" "http://download.oracle.com/otn-pub/java/jdk/8u144-b01/090f390dda5b47b9b721c7dfaa008135/jdk-8u144-linux-x64.tar.gz"
[root@hyd01db001 opt]# tar -xzvf jdk-8u144-linux-x64.tar.gz
[root@hyd01db001 opt]# cd jdk1.8.0_144/
[root@hyd01db001 jdk1.8.0_144]# alternatives --install /usr/bin/java java /opt/jdk1.8.0_144/bin/java 2
In case we have multiple java configured, ensure we set the binaries to the current installed Java. Type the desired selection number to set any Java version. However, since I have earlier removed open source Java, I don't have multiple version on this server.
[root@hyd01db001 jdk1.8.0_144]# alternatives --config java
There is 1 program that provides 'java'.
Selection Command
-----------------------------------------------
*+ 1 /opt/jdk1.8.0_144/bin/java
Enter to keep the current selection[+], or type selection number: 1
[root@hyd01db001 jdk1.8.0_144]# alternatives --install /usr/bin/jar jar /opt/jdk1.8.0_144/bin/jar 2
[root@hyd01db001 jdk1.8.0_144]# alternatives --set jar /opt/jdk1.8.0_144/bin/jar
[root@hyd01db001 jdk1.8.0_144]# alternatives --install /usr/bin/javac javac /opt/jdk1.8.0_144/bin/javac 2
[root@hyd01db001 jdk1.8.0_144]# alternatives --set javac /opt/jdk1.8.0_144/bin/javac
[root@hyd01db001 jdk1.8.0_144]# exit
Add below lines to .bash_profile and source it:
[oracle@hyd01db001 ~]$ vi ~/.bash_profile
JAVA_HOME=/opt/jdk1.8.0_144; export JAVA_HOME
JRE_HOME=/opt/jdk1.8.0_144/jre; export JRE_HOME
export PATH=$PATH:${JAVA_HOME}/bin:$JAVA_HOME/db/bin:${JRE_HOME}/bin
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:${JAVA_HOME}/lib:${JAVA_HOME}/jre/lib/amd64/server
[oracle@hyd01db001 ~]$ . ~/.bash_profile
Installing Zookeeper on HYD01DB001
I'm using Zookeeper 3.4.10 version, downloaded from http://www-eu.apache.org/dist/zookeeper/zookeeper-3.4.10/
I have saved binary tar file [zookeeper-3.4.10.tar.gz] on below location;
[oracle@hyd01db001 ~]$ cd /oradba/app/apache/
[oracle@hyd01db001 apache]$ ls -lrth stage/
total 34M
-rwxr-x--- 1 oracle dba 34M Jul 28 13:44 zookeeper-3.4.10.tar.gz
Let's untar it;
[oracle@hyd01db001 apache]$ tar -xzvf stage/zookeeper-3.4.10.tar.gz
[oracle@hyd01db001 apache]$ cd zookeeper-3.4.10/conf
[oracle@hyd01db001 conf]$ cp zoo_sample.cfg zoo.cfg
Creating new directory for Zookeeper data
[oracle@hyd01db001 conf]$ mkdir /oradba/app/apache/zoo-data
Update dataDir location and save the file
[oracle@hyd01db001 conf]$ vi zoo.cfg
dataDir=/oradba/app/apache/zoo-data
[oracle@hyd01db001 conf]$ export ZOOKEEPER_HOME=/oradba/app/apache/zookeeper-3.4.10
[oracle@hyd01db001 bin]$ cd $ZOOKEEPER_HOME
We won't start Zookeeper right now. Instead would start once Kafka is configured.
Installing Kafka on HYD01DB001
I'm using Kafka 2.11-0.11, downloaded from https://www.apache.org/dyn/closer.cgi?path=/kafka/0.11.0.0/kafka_2.11-0.11.0.0.tgz
Saved the binary tar file on below location;
[oracle@hyd01db001 apache]$ pwd
/oradba/app/apache
[oracle@hyd01db001 apache]$ ls -lrth stage/
total 38M
-rwxr-x--- 1 oracle dba 34M Jul 28 13:44 zookeeper-3.4.10.tar.gz
-rwxr-x--- 1 oracle dba 4.3M Jul 28 14:06 kafka_2.11-0.11.0.0.tgz
Let's untar it;
[oracle@hyd01db001 apache]$ tar -xzvf stage/kafka_2.11-0.11.0.0.tgz
[oracle@hyd01db001 apache]$ ls -lrh
total 16K
drwxr-xr-x 10 oracle dba 4.0K Jul 28 14:03 zookeeper-3.4.10/
drwxr-xr-x 3 oracle dba 4.0K Jul 28 14:03 zoo-data/
drwxr-xr-x 2 oracle dba 4.0K Jul 28 14:31 stage/
drwxr-xr-x 6 oracle dba 4.0K Jun 23 03:39 kafka_2.11-0.11.0.0/
[oracle@hyd01db001 apache]$ cd kafka_2.11-0.11.0.0/
Update dataDir location
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ vi config/zookeeper.properties
dataDir=/oradba/app/apache/zoo-data
Since we have not changed default port for zookeeper client, we stick to 2181 on this file as well
Creating a log directory for Kafka
[oracle@hyd01db001 apache]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ export KAFKA_HOME=/oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ mkdir kafka-log
Update the log directory on server.properties file
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ vi config/server.properties
log.dirs=/oradba/app/apache/kafka_2.11-0.11.0.0/kafka-log
Startup Zookeeper
oracle@hyd01db001 apache]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/zookeeper-server-start.sh config/zookeeper.properties >logs/zookeeper-runtime.log 2>&1 &
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ jps
2929 TMMain
11906 QuorumPeerMain
3306 Jps
Start Kafka
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-server-start.sh config/server.properties > logs/kafka-runtime.log 2>&1 &
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ jps
2929 TMMain
3873 Kafka
11906 QuorumPeerMain
4170 Jps
Configure Database for goldengate on Fangorn
[oracle@fangorn ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 28 17:33:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name, open_mode from gv$database;
NAME OPEN_MODE
--------- --------------------
GNSH1 READ WRITE
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> -- Enable force logging and minimal supplemental logging
SQL> alter database add supplemental log data;
SQL> alter database force logging;
SQL> select log_mode, force_logging, supplemental_log_data_min from v$database;
LOG_MODE FOR SUPPLEME
------------ --- --------
ARCHIVELOG YES YES
SQL> alter system set enable_goldengate_replication=true sid='*' scope=both;
SQL> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- -----------
enable_goldengate_replication boolean TRUE
SQL> create user gg_admin identified by gg_admin default tablespace users temporary tablespace temp;
SQL> grant create session, create table, alter any table, alter system, dba to gg_admin;
SQL> exec dbms_goldengate_auth.grant_admin_privilege('gg_admin','*', TRUE, TRUE);
Installing Goldengate on fangorn
Software location:
[oracle@fangorn stage]$ pwd
/oradba/app/stage
[oracle@fangorn stage]$ ls –lrth
total 454M
-rw-r--r-- 1 oracle dba 454M Jan 19 2016 V100692-01.zip
[oracle@fangorn stage]$ unzip V100692-01.zip
[oracle@fangorn stage]$ ls –lrth
total 454M
drwxr-xr-x 3 oracle dba 4.0K Dec 12 2015 fbo_ggs_Linux_x64_shiphome/
-rw-r--r-- 1 oracle dba 1.6K Jan 19 2016 OGG-12.2.0.1-README.txt
-rw-r--r-- 1 oracle dba 276K Jan 19 2016 OGG-12.2.0.1.1-ReleaseNotes.pdf
-rw-r--r-- 1 oracle dba 454M Jan 19 2016 V100692-01.zip
[oracle@fangorn stage]$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
[oracle@fangorn Disk1]$ ls –lrth
total 16K
drwxr-xr-x 4 oracle dba 4.0K Dec 12 2015 install/
drwxr-xr-x 11 oracle dba 4.0K Dec 12 2015 stage/
-rwxr-xr-x 1 oracle dba 918 Dec 12 2015 runInstaller
drwxrwxr-x 2 oracle dba 4.0K Dec 12 2015 response/
Creating response file [find attached response file for 11g Database version]
[oracle@fangorn Disk1]$ vi /oradba/app/oggcore.rsp
[oracle@fangorn Disk1]$ ./runInstaller -silent -responseFile /oradba/app/oggcore.rsp -waitforcompletion
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 48221 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-07-28_05-02-24PM. Please wait ...You can find the log of this install session at:
/oradba/app/oraInventory/logs/installActions2017-07-28_05-02-24PM.log
The installation of Oracle GoldenGate Core was successful.
Please check '/oradba/app/oraInventory/logs/silentInstall2017-07-28_05-02-24PM.log' for more details.
Successfully Setup Software.
Connecting to GGSCI prompt and creating sub-directories
[oracle@fangorn Disk1]$ cd /oradba/app/gghome/
[oracle@fangorn gghome]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Dec 12 2015 00:54:38
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2015, Oracle and/or its affiliates. All rights reserved.
GGSCI (fangorn.oradba.com) 1> create subdirs
Creating subdirectories under current directory /oradba/app/gghome
Parameter files /oradba/app/gghome/dirprm: created
Report files /oradba/app/gghome/dirrpt: created
Checkpoint files /oradba/app/gghome/dirchk: created
Process status files /oradba/app/gghome/dirpcs: created
SQL script files /oradba/app/gghome/dirsql: created
Database definitions files /oradba/app/gghome/dirdef: created
Extract data files /oradba/app/gghome/dirdat: created
Temporary files /oradba/app/gghome/dirtmp: created
Credential store files /oradba/app/gghome/dircrd: created
Masterkey wallet files /oradba/app/gghome/dirwlt: created
Dump files /oradba/app/gghome/dirdmp: created
GGSCI (fangorn.oradba.com) 2> exit
Creating KEY using AES-256 cipher [TOTALLY OPTIONAL]
[oracle@fangorn gghome]$ ./keygen 256 1
0x995671248E346B4814E9A17910811A16914B8B5F6BC15F697672417C8367AA5A
[oracle@fangorn gghome]$ cat > ENCKEYS
gg_owner 0x995671248E346B4814E9A17910811A16914B8B5F6BC15F697672417C8367AA5A
^C
[oracle@fangorn gghome]$ cat ENCKEYS
gg_owner 0x995671248E346B4814E9A17910811A16914B8B5F6BC15F697672417C8367AA5A
Encrypting gg_admin password [TOTALLY OPTIONAL]
GGSCI (fangorn.oradba.com) 1> encrypt password gg_admin AES256 encryptkey gg_owner
Encrypted password: AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC
Algorithm used: AES256
Creating and Starting Manager Process
GGSCI (fangorn.oradba.com) 2> edit param mgr
PORT 7809
USERID gg_admin@gnsh1, PASSWORD AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC, AES256, ENCRYPTKEY gg_owner
PURGEOLDEXTRACTS /oradba/app/gghome/dirdat/*, USECHECKPOINTS
GGSCI (fangorn.oradba.com) 3> start mgr
Manager started.
GGSCI (fangorn.oradba.com) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
Adding Schematrandata for "U1" database user
GGSCI (fangorn.oradba.com) 5> dblogin USERID gg_admin@gnsh1, PASSWORD AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC, AES256, ENCRYPTKEY gg_owner
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 7> info schematrandata u1
2017-07-28 18:07:54 INFO OGG-01786 Schema level supplemental logging is disabled on schema U1.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 8> add schematrandata u1 allcols
2017-07-28 18:08:15 INFO OGG-01788 SCHEMATRANDATA has been added on schema u1.
2017-07-28 18:08:15 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema u1.
2017-07-28 18:08:15 INFO OGG-01977 SCHEMATRANDATA for all columns has been added on schema u1.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 9> info schematrandata u1
2017-07-28 18:08:24 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema U1.
2017-07-28 18:08:24 INFO OGG-01981 Schema level supplemental logging is enabled on schema U1 for all columns.
2017-07-28 18:08:24 INFO OGG-10462 Schema U1 have 1 prepared tables for instantiation.
Configuring and Starting Extract
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 12> edit param eu1
EXTRACT eu1
USERID gg_admin@gnsh1, PASSWORD AADAAAAAAAAAAAIAHEMEMAOJOIHBTGGJNCLDSJNBTEIFHBAJWDBGBBWDTHLAQDKDLCWFDCGJBBVAQCVAXIYDLHMJJJOFNDRDAJNJSFADLJBFRGYC, AES256, ENCRYPTKEY gg_owner
EXTTRAIL ./dirdat/u1
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS BUFSIZE 4194304
TRANLOGOPTIONS DBLOGREADERBUFSIZE 4194304
TABLE u1.*;
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 13> ADD EXTRACT eu1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 14> ADD EXTTRAIL ./dirdat/u1, EXTRACT eu1, megabytes 100
EXTTRAIL added.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 15> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EU1 00:00:00 00:00:11
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 16> start eu1
Sending START request to MANAGER …
EXTRACT EU1 starting
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 17> info eu1
EXTRACT EU1 Last Started 2017-07-28 18:27 Status RUNNING
Checkpoint Lag 00:04:40 (updated 00:00:01 ago)
Process ID 25462
Log Read Checkpoint Oracle Redo Logs
2017-07-28 18:22:46 Seqno 44, RBA 54767120
SCN 0.0 (0) Configuring and Starting PumpGGSCI (fangorn.oradba.com as gg_admin@gnsh1) 18> edit param pu1
EXTRACT pu1
RMTHOST hyd01db001.oradba.com, MGRPORT 7809, TIMEOUT 30
RMTTRAIL /oradba/app/gghome/dirdat/u1
PASSTHRU
TABLE u1.*;
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 29> add extract pu1 EXTTRAILSOURCE /oradba/app/gghome/dirdat/u1
EXTRACT added.
GGSCI (fangorn.oradba.com as gg_admin@gnsh1) 30> add rmttrail /oradba/app/gghome/dirdat/u1, extract pu1 , megabytes 100
RMTTRAIL added.
Installing Goldengate Big Data Adapter on HYD01DB001
Software Location:
[oracle@hyd01db001 stage]$ pwd
/oradba/app/stage
[oracle@hyd01db001 stage]$ ls –lrth
total 200M
-rw-r--r-- 1 oracle dba 200M May 18 2016 V138402-01.zip
Unzip the software
[oracle@hyd01db001 stage]$ unzip V138402-01.zip
Archive: V138402-01.zip
inflating: ggs_Adapters_Linux_x64.tar
inflating: OGGBD-12.2.0.1.1-README.txt
inflating: OGG_BigData_12.2.0.1.1_Release_Notes.pdf
[oracle@hyd01db001 stage]$ cd ../gghome/
[oracle@hyd01db001 gghome]$ pwd
/oradba/app/gghome
[oracle@hyd01db001 gghome]$ tar -xvf /oradba/app/stage/ggs_Adapters_Linux_x64.tar
Creating Goldengate sub-directories
[oracle@hyd01db001 gghome]$ ./ggsci
GGSCI (hyd01db001.oradba.com) 2> create subdirs
Creating subdirectories under current directory /oradba/app/gghome
Parameter files /oradba/app/gghome/dirprm: created
Report files /oradba/app/gghome/dirrpt: created
Checkpoint files /oradba/app/gghome/dirchk: created
Process status files /oradba/app/gghome/dirpcs: created
SQL script files /oradba/app/gghome/dirsql: created
Database definitions files /oradba/app/gghome/dirdef: created
Extract data files /oradba/app/gghome/dirdat: created
Temporary files /oradba/app/gghome/dirtmp: created
Credential store files /oradba/app/gghome/dircrd: created
Masterkey wallet files /oradba/app/gghome/dirwlt: created
Dump files /oradba/app/gghome/dirdmp: created
Configuring and Starting Manager
GGSCI (hyd01db001.oradba.com) 3> edit param mgr
PORT 7809
GGSCI (hyd01db001.oradba.com) 5> start mgr
Manager started.
GGSCI (hyd01db001.oradba.com) 6> exit
Configuring and Starting Replicat - [Kafka handle]
[oracle@hyd01db001 gghome]$ vi dirprm/kafka.props
gg.handlerlist=kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties
gg.handler.kafkahandler.TopicName=u1topic
gg.handler.kafkahandler.format=xml
gg.handler.kafkahandler.SchemaTopicName=mySchemaTopic
gg.handler.kafkahandler.BlockingSend=false
gg.handler.kafkahandler.includeTokens=false
##
## Goldengate will consider entire transaction
## as a single message
##
## gg.handler.kafkahandler.mode=tx
##
## Goldengate will consider every operation
## as single message
##
gg.handler.kafkahandler.mode=op
#gg.handler.kafkahandler.maxGroupSize=100, 1Mb
#gg.handler.kafkahandler.minGroupSize=50, 500Kb
goldengate.userexit.timestamp=utc
goldengate.userexit.writers=javawriter
javawriter.stats.display=TRUE
javawriter.stats.full=TRUE
gg.log=log4j
gg.log.level=INFO
gg.report.time=30sec
gg.classpath=dirprm/:/oradba/app/apache/kafka_2.11-0.11.0.0/libs/*:
javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=ggjava/ggjava.jar
[oracle@hyd01db001 gghome]$ vi dirprm/custom_kafka_producer.properties
bootstrap.servers=hyd01db001.oradba.com:9092
acks=1
compression.type=gzip
reconnect.backoff.ms=1000
value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
# 100KB per partition
batch.size=102400
linger.ms=10000
[oracle@hyd01db001 gghome]$ vi dirprm/rkaf.prm
REPLICAT rkaf
TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
MAP u1.*, TARGET u1topic.*;
Create Kafka Topicoracle@hyd01db001 ~]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-topics.sh --zookeeper hyd01db001.oradba.com:2181 --create --topic u1topic --partitions 1 --replication-factor 1
Created topic "u1topic".
List of Topics
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-topics.sh --zookeeper hyd01db001.oradba.com:2181 --list
ADD and Start GG_BigData_Adapter Replicatoracle@hyd01db001 ~]$ cd /oradba/app/gghomeoracle@hyd01db001 gghome]$ ./ggsciGGSCI (hyd01db001.oradba.com) 1> add replicat rkaf exttrail ./dirdat/u1 begin now
REPLICAT added.
GGSCI (hyd01db001.oradba.com) 2> start rkaf
Start Kafka Consumer to read messages
=====================================
[oracle@hyd01db001 ~]$ cd /oradba/app/apache/kafka_2.11-0.11.0.0
[oracle@hyd01db001 kafka_2.11-0.11.0.0]$ bin/kafka-console-consumer.sh --bootstrap-server hyd01db001.oradba.com:9092 --topic u1topic
SAMPLE XML KAFKA OUTPUT
Output explanation:
operation table = <topic_name>.<table_name>
type = type of DML like I: Insert, U: Update, D: Delete
ts = timestamp
pos = refers to RBA of the trailfile. We can traceback using Logdump utility
numCols = number of columns
INSERT
<operation table='U1TOPIC.T2' type='I' ts='2017-07-28 14:30:26.012988' current_ts='2017-07-28T20:00:32.188000' pos='00000000000000004254' numCols='3'>
<col name='SR_NO' index='0'>
<before missing='true'/>
<after><![CDATA[2]]></after>
</col>
<col name='NAME' index='1'>
<before missing='true'/>
<after><![CDATA[JEFF]]></after>
</col>
<col name='REGION' index='2'>
<before missing='true'/>
<after><![CDATA[UTAH]]></after>
</col>
</operation>
UPDATE
<operation table='U1TOPIC.T2' type='U' ts='2017-07-28 14:34:08.013062' current_ts='2017-07-28T20:04:15.314000' pos='00000000000000004390' numCols='3'>
<col name='SR_NO' index='0'>
<before><![CDATA[2]]></before>
<after><![CDATA[2]]></after>
</col>
<col name='NAME' index='1'>
<before><![CDATA[JEFF]]></before>
<after><![CDATA[JOHN]]></after>
</col>
<col name='REGION' index='2'>
<before><![CDATA[UTAH]]></before>
<after><![CDATA[UTAH]]></after>
</col>
</operation>
DELETE
<operation table='U1TOPIC.T2' type='D' ts='2017-07-28 14:35:03.013005' current_ts='2017-07-28T20:05:10.336000' pos='00000000000000004563' numCols='3'>
<col name='SR_NO' index='0'>
<before><![CDATA[2]]></before>
<after missing='true'/>
</col>
<col name='NAME' index='1'>
<before><![CDATA[JOHN]]></before>
<after missing='true'/>
</col>
<col name='REGION' index='2'>
<before><![CDATA[UTAH]]></before>
<after missing='true'/>
</col>
</operation>
Goldengate Response File used in Fangorn Server
[oracle@fangorn ~]$ cat /oradba/app/oggcore.rsp
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/oradba/app/gghome
START_MANAGER=false
DATABASE_LOCATION=/oradba/app/oracle/product/11204
INVENTORY_LOCATION=/oradba/app/oraInventory
UNIX_GROUP_NAME=dba
No comments:
Post a Comment