# OGG12c_to_kafka
## 实现原理
```
首先简单说一下ogg-kafka的结构实现原理：
1. OGG抽取进程EXT1从源端Oracle12c上面抽取schema(DATAPIPELINE)中的DML操作，并将数据记录在本地的 dirdat/lt 中；
2. OGG传输进程EXTDP1从本地的 dirdat/lt 中获得数据并传输到 OGG for BD 的 dirdat/rt 中；
3. OGG for BD 被动的收到数据之后由自己的复制队列进程RCONF将数据按照kafka约定的方法和接口将数据分发到kafka队列中去。
```
## 开启的数据库配置
```
sqlplus / as sysdba
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;（临时生效）
ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION=TRUE SCOPE=BOTH/SPFILE;（永久生效：使用spfile那么需要startup force或者shutdown immediate再startup-）
```
## 创建ogg用户(本生产环境中使用system用户，此操作可选)
```
CREATE TABLESPACE OGG_TBS DATAFILE '/storage/oracle/app/oracle/oradata/orcl/ogg_tbs01.dbf' SIZE 500M;
CREATE USER oggadmin IDENTIFIED BY oggadmin DEFAULT TABLESPACE ogg_tbs QUOTA UNLIMITED ON ogg_tbs;
```
## ogg相关授权
```
GRANT CONNECT, RESOURCE TO oggadmin;
GRANT EXECUTE ON UTL_FILE TO oggadmin;
GRANT CONNECT TO OGGADMIN;
GRANT ALTER ANY TABLE TO OGGADMIN;
GRANT ALTER SESSION TO OGGADMIN;
GRANT CREATE SESSION TO OGGADMIN;
GRANT FLASHBACK ANY TABLE TO OGGADMIN;
GRANT SELECT ANY DICTIONARY TO OGGADMIN;
GRANT SELECT ANY TABLE TO OGGADMIN;
GRANT RESOURCE TO OGGADMIN;
GRANT SELECT ANY TABLE TO OGGADMIN;
GRANT EXECUTE ANY TYPE TO OGGADMIN;
GRANT SELECT ANY TRANSACTION TO OGGADMIN;
```
## 在源端部署ogg相关服务
### 123010_fbo_ggs_Linux_x64_shiphome.zip
### 解压ogg安装包到以下路径
### fbo_ggs_Linux_x64_shiphome/Disk1/response
```
####################################################################
## Copyright(c) Oracle Corporation 2016. All rights reserved. ##
## ##
## Specify values for the variables listed below to customize ##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## can help to populate the variables with the appropriate ##
## values. ##
## ##
## IMPORTANT NOTE: This file should be secured to have read ##
## permission only by the oracle user or an administrator who ##
## own this installation to protect any sensitive input values. ##
## ##
####################################################################
#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v12_1_2
################################################################################
## ##
## Oracle GoldenGate installation option and details ##
## ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA12c for installing Oracle GoldenGate for Oracle Database 12c and
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA12c
**### ORA11g或者ORA12c**
#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/storage/oracle/app/ogg
**### ogg软件的安装路径**
#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=true
**### 设置自动启动建议开启建议开启)**
#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=7809
**### 设置mgr进程默认端口**
#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=/storage/oracle/app/oracle/product/12.1.0/dbhome_1
**### 设置$ORACLE_HOME**
################################################################################
## ##
## Specify details to Create inventory for Oracle installs ##
## Required only for the first Oracle product install on a system. ##
## ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=
#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=oracle
**### 设置安装ogg的用户组**
```
### 创建ogg工作目录
create subdirs
```
Creating subdirectories under current directory /oracle/app/ogg_bd
Parameter files /oracle/app/ogg_bd/dirprm: already exists
Report files /oracle/app/ogg_bd/dirrpt: already exists
Checkpoint files /oracle/app/ogg_bd/dirchk: already exists
Process status files /oracle/app/ogg_bd/dirpcs: already exists
SQL script files /oracle/app/ogg_bd/dirsql: already exists
Database definitions files /oracle/app/ogg_bd/dirdef: already exists
Extract data files /oracle/app/ogg_bd/dirdat: already exists
Temporary files /oracle/app/ogg_bd/dirtmp: already exists
Credential store files /oracle/app/ogg_bd/dircrd: already exists
Masterkey wallet files /oracle/app/ogg_bd/dirwlt: already exists
Dump files /oracle/app/ogg_bd/dirdmp: already exists
```
### 在ogg安装路径下执行以下SQL脚本开放相应操作(按顺序执行)
```
SQL> @ marker_setup.sql
SQL> @ ddl_setup.sql
SQL> @ role_setup.sql
SQL> @ role_setup.sql
```
### 122011_ggs_Adapters_Linux_x64.zip
### 将ogg for bigdata解压到ogg并行路径下
### 检查
--------------以上为部署OGG，以下为配置
## 启动阶段
### 配置ogg的MGR进程
rlwrap ./ogg/ggsci
### 如果启动时有库文件缺失按照以下方法处理
```
[oracle@alioracle ~]$ find /oracle -name *.so
...
[oracle@alioracle ~]$ ln 最后一个文件路径 ogg路径
```
```
EDIT PARAM MGR
DynamicPortList 20000-20099
PurgeOldExtracts ./dirdat/*, UseCheckPoints, MinKeepHours 2
Autostart Extract E*
AUTORESTART Extract *, WaitMinutes 1, Retries 3
```
start mgr
### 配置oggBD的MGR进程
rlwrap ./oggBD/ggsci
```
EDIT PARAM MGR
PORT 7810
```
start mgr
### 配置抽取EXTRACT
rlwrap ./ogg/ggsci
### 添加数据源schema对应的日志
```
DBLOGIN USERID SYSTEM@localhost:1522/orcl PASSWORD welcome1
ADD SCHEMATRANDATA DATAPIPELINE ALLCOLS
```
### 在数据源上注册抽取进程
```
DBLOGIN USERID SYSTEM PASSWORD welcome1
REGISTER EXTRACT EXT1 DATABASE CONTAINER (ORCL)
REGISTER EXTRACT EXT1 DATABASE \\如果不是CBD
```
### 定义抽取进程
```
ADD SCHEMATRANDATA DATAPIPELINE
ADD EXTRACT EXT1, INTEGRATED TRANLOG, BEGIN NOW
ADD EXTTRAIL ./dirdat/lt EXTRACT EXT1
```
### 编辑并启动抽取进程
EDIT PARAM EXT1
```
EXTRACT EXT1
USERID SYSTEM, PASSWORD welcome1
EXTTRAIL ./dirdat/lt, ,FORMAT LEVEL 5
SOURCECATALOG ORCL
TABLE DATAPIPELINE.*;
NOCOMPRESSUPDATES
GETUPDATEBEFORES
```
START EXT1
### 编辑并启动传输进程
EDIT PARAM EXTDP1
```
EXTRACT EXTDP1
RMTHOST LOCALHOST, MGRPORT 7810
USERID SYSTEM, PASSWORD DataPipeline123
RMTTRAIL ./dirdat/rt
TABLE DATAPIPELINE.*;
RMTTRAIL ./dirdat/rt, FORMAT LEVEL 5
```
```
ADD EXTRACT EXTDP1 EXTTRAILSOURCE ./dirdat/lt BEGIN NOW
ADD RMTTRAIL ./dirdat/rt EXTRACT EXTDP1
```
START EXTDP1
## 配置复制队列
### 配置 Replica (ogg-bd/dirprm/rconf.prm)
vim dirprm/rconf.prm
```
REPLICAT rconf
TARGETDB LIBFILE libggjava.so SET property=dirprm/conf.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 1000
MAP *.*, TARGET *.*;
```
### 配置Handler (ogg-bd/dirprm/conf.props)
```
gg.handlerlist=confluent
#The handler properties
gg.handler.confluent.type=oracle.goldengate.kafkaconnect.KafkaConnectHandler
gg.handler.confluent.kafkaProducerConfigFile=confluent.properties
gg.handler.confluent.mode=tx
gg.handler.confluent.sourceRecordGeneratorClass=oracle.goldengate.kafkaconnect.DefaultSourceRecordGenerator
#The formatter properties
gg.handler.confluent.format=oracle.goldengate.kafkaconnect.formatter.KafkaConnectFormatter
gg.handler.confluent.format.insertOpKey=I
gg.handler.confluent.format.updateOpKey=U
gg.handler.confluent.format.deleteOpKey=D
gg.handler.confluent.format.treatAllColumnsAsStrings=false
gg.handler.confluent.format.iso8601Format=false
gg.handler.confluent.format.pkUpdateHandling=update
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
#Set the classpath here
gg.classpath=dirprm/:/oracle/app/ogg_bd/ggjava/resources/lib*:/oracle/app/ogg_bd/jars/*
/**
注意/oracle/app/ogg_bd/jars/ 路径下的JAR包需要从我司服务器中下载，下载链接为：
http://pocbucket-1252847339.cosbj.myqcloud.com/JARS.zip?sign=H7kj0MvwTxqpOicwi1HyP3rTTCNhPTEyNTI4NDczMzkmaz1BS0lEblN3TFpldlI3Y3Y2QTJhRGFtbWZOc0JLSDFheEc5VWQmZT0xNTE4MjMwMTgzJnQ9MTUxNTYzODE4MyZyPTEwNDkxNzU2MDkmZj0vSkFSUy56aXAmYj1wb2NidWNrZXQ=
**/
javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=.:ggjava/ggjava.jar:./dirprm
```
### 配置 Kafka Connect (ogg-bd/dirprm/confluent.properties)
```
bootstrap.servers=192.168.1.237:9092
\\kafka队列的地址和端口
value.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
key.serializer=org.apache.kafka.common.serialization.ByteArraySerializer
schema.registry.url=http://192.168.1.237:8081
key.converter.schema.registry.url=http://192.168.1.237:8081
value.converter.schema.registry.url=http://192.168.1.237:8081
\\kafka对应的schema信息的地址和端口
value.converter=io.confluent.connect.avro.AvroConverter
key.converter=io.confluent.connect.avro.AvroConverter
internal.value.converter=org.apache.kafka.connect.json.JsonConverter
internal.key.converter=org.apache.kafka.connect.json.JsonConverter
```
### 启动oggBD
rlwrap ./ggsci
```
ADD REPLICAT RCONF, EXTTRAIL ./dirdat/rt
```
start rconf
### 查看ogg日志
view report rconf
### 查看进程状态
info all/进程名
### how to build
```
# mvn clean install
# mv target/confluent-lib to ogg_bd
```
### 配置docker-compose.yml
在配置文件中 sourceoracle下: 如果有ORACLE_MODE=LOGMINER 去掉这行
在manager下设置 NEED_SWITCH_TOPIC=true
### TypeMapping
需要做好两端之间的类型完全匹配(ogg-bd <-> dp-data-system) 目前数据类型全部匹配 (自己手建表),需要完全类型验证，并记录类型转换表
### Troubleshooting
```
* Windows 配置提示jvm.dll LOAD ERROR
配置JAVA PATH,LD_LIBRARY_PATH=XX\jdk1.8.0_121\jre\bin;XX\jdk1.8.0_121\jre\bin\server\jvm.dll
* 插入数据后，kafka中没有数据
检查ogg/dirdat 看最近更新事件，如果没有变化，证明ext1 出现异常，如果ogg-bd数据没有更新 检查extdp1是否出现问题
* 能请求到 ogg-bd Kafka Connect 的8083 端口吗？
暂时没有找到方案，可能是配置式不对
* After 中只有update的column，没有全部column数据
https://docs.oracle.com/goldengate/gg121211/gg-adapter/GADAD/java_msgcap_parsing.htm#GADAD141 在ext1.prm 中添加 NOCOMPRESSUPDATES GETUPDATEBEFORES
* for big data 找不到dll / so
创建GLOBALS 添加 ENABLEMONITORAGENT， 重启mgr 注意要在一个 ssh会话下使用
```
### ogg配置好之后需要依赖
ogg配置好之后需要依赖java环境，配置java环境变量
```
ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/java/jdk1.8.0_144/jre/lib/amd64/server:$LD_LIBRARY_PATH
export ORACLE_SID=orcl
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/bin:$JAVA_HOME/bin
export JAVA_HOME=/usr/java/jdk1.8.0_144
export JAVA_JRE=/usr/java/jdk1.8.0_144/jre
export PATH ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH
```
转载须注明出处与链接：
https://github.com/TurboChang/DataPipeline/wiki/oracle12c_ogg_kafka/_edit
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2018-1-29 12:40:41
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对