OGG 实用案例（一）-oracle 同步kafka
01、源库安装ogg
见OGG 安装 博客
02、源库配置oracle环境
环境说明：
|
主机 |
角色 |
Ogg版本 |
|
192.168.192.26 |
源库 |
191004_fbo_ggs_Linux_x64_shiphome.zip |
|
192.168.193.221 |
目标库中间件Kafka |
OGG_BigData_Linux_x64_19.1.0.0.5.zip |
01）、创建ogg同步用户
SQL>create tablespace ogg datafile '/u02/oradata/ogg.dbf' size 5000M; SQL>create user ogg identified by "ogg" default tablespace ogg temporary tablespace TEMP;
02）、权限赋予
GRANT CONNECT TO ogg; GRANT ALTER ANY TABLE TO ogg; GRANT ALTER SESSION TO ogg; GRANT CREATE SESSION TO ogg; GRANT FLASHBACK ANY TABLE TO ogg; GRANT SELECT ANY DICTIONARY TO ogg; GRANT SELECT ANY TABLE TO ogg; GRANT RESOURCE TO ogg; GRANT SELECT ANY TRANSACTION TO ogg;
03）、归档模式开启
已开启则忽略
SQL>archive log list; SQL>shutdown immediate; SQL>startup mount; SQL>alter database archivelog; SQL>alter database open; SQL>alter system switch logfile;
04）、开启oracle最小附加日志
Oracle 执行查询语句，确保显示结果为yes
Select force_logging,SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI, SUPPLEMENTAL_LOG_DATA_FK, SUPPLEMENTAL_LOG_DATA_ALL from v$database;
Min列不为YES则执行开启操作：
alter database add supplemental log data ; alter system switch logfile;
ALL 列如果开启则关闭
alter database drop supplemental log data (ALL) columns;
未成功则执行一下语句关闭：
alter database drop supplemental log data (primary key, unique,foreign key) columns; alter database drop supplemental log data ; alter system switch logfile;
05）、开启强制日志模式
alter database force logging;
06）、环境变量配置
Vi /home/oracle/.bash_profile export OGG_HOME=/home/oracle/app/ogg/ export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:/usr/bin
03、配置源库ogg环境
01）、初始化ogg
当前ogg 默认需要安装在oracle 用户下操作。
Su – oracle . ~/.bash_profile Cd /home/oracle/app/ogg/ [oracle@hso32-db-test ogg]$ . ~/.bash_profile [oracle@hso32-db-test ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (hso32-db-test) 1> create subdirs Creating subdirectories under current directory /home/oracle/app/ogg Parameter file /home/oracle/app/ogg/dirprm: already exists. Report file /home/oracle/app/ogg/dirrpt: already exists. Checkpoint file /home/oracle/app/ogg/dirchk: already exists. Process status files /home/oracle/app/ogg/dirpcs: already exists. SQL script files /home/oracle/app/ogg/dirsql: already exists. Database definitions files /home/oracle/app/ogg/dirdef: already exists. Extract data files /home/oracle/app/ogg/dirdat: already exists. Temporary files /home/oracle/app/ogg/dirtmp: already exists. Credential store files /home/oracle/app/ogg/dircrd: already exists. Masterkey wallet files /home/oracle/app/ogg/dirwlt: already exists. Dump files /home/oracle/app/ogg/dirdmp: already exists. GGSCI (hso32-db-test) 2> exit
当前环境配置成功
04、创建源库测试表，测试数据
01) 、创建测试用户，表
SQL> create user test_ogg identified by oracle ; User created. SQL> grant dba to test_ogg; Grant succeeded. SQL> conn test_ogg Enter password: Connected. SQL> create table test_ogg( id int,name varchar(20),primary key(id)); Table created.
02) 、配置Oracle 数据支持脚本
配置squence支持
在/home/oracle/app/ogg/下执行 Sqlplus / as sysdba @seqence.sql 键入schemas ：test_ogg
配置支持脚本
SQL> @marker_setup SQL> @ddl_setup SQL> @role_setup SQL> grant GGS_GGSUSER_ROLE to goldengate; SQL> @ddl_enable SQL> @marker_status.sql SQL> @?/rdbms/admin/dbmspool.sql SQL> @ddl_pin.sql ogg
05、配置源库ogg 配置文件
01）、配置mgr进程
登入ogg
Cd /home/oracle/app/ogg/ ./ggsci GGSCI (hso32-db-test) 1> dblogin userid ogg password ogg; Successfully logged into database. GGSCI (hso32-db-test as ogg@hso32) 9> edit param mgr PORT 7809 DYNAMICPORTLIST 7840-7850 AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3 PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
Ps:
PORT即mgr的默认监听端口；
DYNAMICPORTLIST动态端口列表，当指定的mgr端口不可用时，会在这个端口列表中选择一个，最大指定范围为256个；
AUTORESTART重启参数设置表示重启所有EXTRACT进程，最多5次，每次间隔3分钟；PURGEOLDEXTRACTS即TRAIL文件的定期清理
02）、配置ext抽取数据进程
GGSCI (hso32-db-test as ogg@hso32) 11> exit param extkafka EXTRACT extkafka dynamicresolution SETENV (ORACLE_SID="hso32") SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8") userid ogg,password ogg EXTTRAIL ./dirdat/to table test_kingle.oggest; table test_ogg.test_ogg;
ps：
第一行指定extract进程名称；
dynamicresolution动态解析；
SETENV设置环境变量，这里分别设置了Oracle数据库以及字符集；
userid ggs,password ggs即OGG连接Oracle数据库的帐号密码，使用上面创建的账号；
exttrail定义trail文件的保存位置以及文件名，注意这里文件名只能是2个字母，其余部分OGG会补齐；
table即复制表的表名，支持*通配，必须以;结尾
03）、配置push 发送进程
GGSCI (hso32-db-test as ogg@hso32) 11> exit param pukafka EXTRACT pukafka passthru dynamicresolution userid ogg,password ogg RMTHOST 10.118.193.223, MGRPORT 7809 RMTTRAIL ./dirdat/to table test_kingle.oggest; table test_ogg.test_ogg;
ps，
第一行指定extract进程名称；
passthru即禁止OGG与Oracle交互，我们这里使用pump逻辑传输，故禁止即可；dynamicresolution动态解析；
userid ogg,password ogg即OGG连接Oracle数据库的帐号密码rmthost和mgrhost即目标端(kafka)OGG的mgr服务的地址以及监听端口；
rmttrail即目标端trail文件存储位置以及名称。
04）、加入需要监控的表（下面的所有操作都是基于本次操作进行）
下面包含 linux 同步到windwos 都是基于这个完成，添加监控表至关重要。
GGSCI (hso32-db-test as ogg@hso32) 21> add trandata test_ogg.test_ogg 2021-07-29 09:03:01 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST_OGG.TEST_OGG. 2021-07-29 09:03:01 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST_OGG.TEST_OGG. 2021-07-29 09:03:01 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST_OGG.TEST_OGG. GGSCI (hso32-db-test as ogg@hso32) 22> info trandata test_ogg.test_ogg Logging of supplemental redo log data is enabled for table TEST_OGG.TEST_OGG. Columns supplementally logged for table TEST_OGG.TEST_OGG: "ID". Prepared CSN for table TEST_OGG.TEST_OGG: 5982609614988
05）、配置defile 文件
Oracle 到其他数据属于异构操作，需要定义映射关系
GGSCI (hso32-db-test as ogg@hso32) 21>edit param test_ogg defsfile ./dirdef/ test_ogg.test_ogg userid ogg,password ogg table test_ogg. test_ogg
生成完成后，选择在ogg主目录下，并且oracle用户下执行
./defgen paramfile dirprm/test_ogg.prm
执行成功后没有报错的话，会在./dirdef下面生成一个文件，我们需要把这个文件拷贝到目标库ogg主目录下的diedef目录下即可
06、目标库221配置
01）、安装 java
当前环境为绿色版，我直接解压到/usr/local/java 目录下即可，环境根据自己的配置
配置环境变量
Vi ~/.bash_porofile export ZOO_HOME=/root/ogg/zookeeper/ export KAFKA_HOME=/root/ogg/kafka/ export OGG_HOME=/root/ogg/ogg/ export RUN_AS_USER=root export JAVA_HOME=/usr/local/java/ export CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar export PATH=$PATH:$JAVA_HOME/bin:$ZOO_HOME/bin/:$JAVA_HOME/jre/bin/:$KAFKA_HOME/bin export LD_LIBRARY_PATH=$OGG_HOME/lib/:/usr/bin:$OGG_HOME/:$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server:$JAVA_HOMEjre/lib/amd64/libjsig.so:$JAVA_HOME/jre/lib/amd64/server/libjvm.so:/usr/lib
02）、安装 配置zookeeper
Tar xf apache-zookeeper-3.6.3.tar.gz –C /root/ogg/ zookeeper Cd /root/ogg/ zookeeper
编辑配置文件
[root@RHEL44223 zookeeper]# cat conf/zoo.cfg tickTime=2000 initLimit=10 syncLimit=5 dataDir=/root/ogg/zoo/data dataLogDir=/root/ogg/zoo/log clientPort=2181
启动 zoo
Cd /root/ogg/zookeeper/bin ./zkServer.sh start
启动成功即可
03）、安装配置kafka
解压kafka
Tar xf kafka_2.12-2.8.0.tgz –c /roo/ogg/kafka
配置kafka配置文件
Cd kafka Vi config/server.properties broker.id=0 listeners=PLAINTEXT://10.118.193.223:9092 host.name=10.118.193.223 num.network.threads=3 num.io.threads=8 socket.send.buffer.bytes=102400 socket.receive.buffer.bytes=102400 socket.request.max.bytes=104857600 log.dirs=/root/ogg/zoo/kafka-logs num.partitions=1 num.recovery.threads.per.data.dir=1 offsets.topic.replication.factor=1 transaction.state.log.replication.factor=1 transaction.state.log.min.isr=1 log.retention.hours=168 log.segment.bytes=1073741824 log.retention.check.interval.ms=300000 zookeeper.connect=localhost:2181 zookeeper.connection.timeout.ms=18000 group.initial.rebalance.delay.ms=0
启动 kafka
kafka-server-start.sh -daemon ./config/server.properties
04）、安装ogg（root用户即可）
解压ogg
Tar xf OGG_BigData_Linux_x64_19.1.0.0.5.tar –C /root/ogg/ogg/
初始化ogg
Cd /root/ogg/ogg/ ./ggsci [oracle@RHEL44223 ogg]$ ./ggsci Oracle GoldenGate Command Interpreter for Oracle Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO Linux, x64, 64bit (optimized), Oracle 11g on Oct 17 2019 23:13:12 Operating system character set identified as UTF-8. Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved. GGSCI RHEL44223) 1> create subdirs Creating subdirectories under current directory /home/oracle/app/ogg Parameter file /home/oracle/app/ogg/dirprm: already exists. Report file /home/oracle/app/ogg/dirrpt: already exists. Checkpoint file /home/oracle/app/ogg/dirchk: already exists. Process status files /home/oracle/app/ogg/dirpcs: already exists. SQL script files /home/oracle/app/ogg/dirsql: already exists. Database definitions files /home/oracle/app/ogg/dirdef: already exists. Extract data files /home/oracle/app/ogg/dirdat: already exists. Temporary files /home/oracle/app/ogg/dirtmp: already exists. Credential store files /home/oracle/app/ogg/dircrd: already exists. Masterkey wallet files /home/oracle/app/ogg/dirwlt: already exists. Dump files /home/oracle/app/ogg/dirdmp: already exists. GGSCI (RHEL44223) 2> exit
05) 配置ogg 配置文件
<01>、配置mgr
GGSCI (RHEL44223) 4> edit param mgr PORT 7809 DYNAMICPORTLIST 7840-7850 AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3 PURGEOLDEXTRACTS /root/ogg/ogg/dirdat/*, USECHECKPOINTS, MINKEEPDAYS 3
<02>、配置 应用配置文件
GGSCI (RHEL44223) 5> edit param REKAFKA REPLICAT rekafka sourcedefs /root/ogg/ogg/dirdef/test_kingle.oggtest TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka.props REPORTCOUNT EVERY 1 MINUTES,RATE GROUPTRANSOPS 10000 MAP test_kingle.oggtest,TARGET test_kingle.oggtest; MAP test_ogg.test_ogg,TARGET test_ogg.test_ogg;
REPLICATE rekafka定义rep进程名称；
sourcedefs即在4.6中在源服务器上做的表映射文件；
TARGETDB LIBFILE即定义kafka一些适配性的库文件以及配置文件，配置文件位于OGG主目录下的dirprm/kafka.props；
REPORTCOUNT即复制任务的报告生成频率；
GROUPTRANSOPS为以事务传输时，事务合并的单位，减少IO操作；MAP即源端与目标端的映射关系
<03>、配置checkpoint
GGSCI (RHEL44223) 5>Edit param ./GLOBALS checkpointtable test_kingle.checkpoint
<04>、配置kafka 控制文件
Cd /root/ogg/ogg/dirprm [root@RHEL44223 dirprm]# cat rekafka.prm REPLICAT rekafka sourcedefs /root/ogg/ogg/dirdef/test_kingle.oggtest TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka.props REPORTCOUNT EVERY 1 MINUTES,RATE GROUPTRANSOPS 10000 MAP test_kingle.oggtest,TARGET test_kingle.oggtest; MAP test_ogg.test_ogg,TARGET test_ogg.test_ogg; [root@RHEL44223 dirprm]# cat custom_kafka_producer.properties bootstrap.servers=10.118.193.223: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 batch.size=102400 linger.ms=10000 [root@RHEL44223 dirprm]#、
06) 、创建kafka主题
查看主题有哪些 kafka-topics.sh --list --zookeeper localhost:2181 创建主题testogg kafka-topics.sh --create --zookeeper localhost:2181 --replication-factor 1 --partitions 1 --topic testogg 再次查看是否存在 kafka-topics.sh --list --zookeeper localhost:2181
07、26同步221进程启动配置
01）、源库26 ogg 操作
通过上面编辑的配置文件添加进程，配置操作都是是使用的相对路径，注意自己的环境是不是一直。
Cd /home/oracle/app/ogg/ GGSCI (hso32-db-test) 1> dblogin userid ogg password ogg; Successfully logged into database. GSCI (hso32-db-test as ogg@hso32) 41> add extract extkafka,tranlog,begin now EXTRACT added. GGSCI (hso32-db-test as ogg@hso32) 42> add exttrail ./dirdat/to,extract extkafka EXTTRAIL added. GGSCI (hso32-db-test as ogg@hso32) 43> edit param pukafka GGSCI (hso32-db-test as ogg@hso32) 44> add extract pukafka,exttrailsource ./dirdat/to EXTRACT added. GGSCI (hso32-db-test as ogg@hso32) 45> add rmttrail ./dirdat/to,extract pukafka RMTTRAIL added.
02)、目标库221 添加进程
通过刚刚编辑的配置文件配置进程
GGSCI (RHEL44223) 5> add replicat rekafka exttrail ./dirdat/to,checkpointtable test_kingle.checkpoint
所有配置完成后，进程启动
03）、启动同步进程
进程启动注意事项，一定要遵守启动顺序，方式获取不到数据
启动1：源库mgr 启动
Start mgr
启动2：目标库mgr 启动
Start mgr
启动3：源库ext和pu 进程
Start extkafka
Start pukafka
启动4：目标库
Start REKAFKA
Info all 查看启动进程状态
等待启动成功后查看kafka消息队列
[root@RHEL44223 ogg]# kafka-console-consumer.sh --bootstrap-server 10.118.193.223:9092 --topic testogg --from-beginning {"table":"TEST_OGG.TEST_OGG","op_type":"I","op_ts":"2021-07-29 09:15:58.595966","current_ts":"2021-07-29T10:18:15.963000","pos":"00000000020000002057","after":{"ID":1,"NAME":"1"}} {"table":"TEST_OGG.TEST_OGG","op_type":"I","op_ts":"2021-07-29 09:15:58.595966","current_ts":"2021-07-29T10:18:16.295000","pos":"00000000020000002191","after":{"ID":2,"NAME":"1"}}
源库模拟测试插入的时候看是否会有数据出来，如果没有出现可以查看相应日志文件
Ogg 日志文件 在ogg目录下ggserrot.log
或者命令行查看
View report rekafka
后面跟随为进程名称 可以通过info all 打印出来 group 列就是了。
人生就像一滴水，非要落下才后悔！
--kingle
