源端
1、解压安装
$ mkdir -p /opt/ogg
$ chown -R oracle:oinstall /opt/ogg
$ unzip <GoldenGate x.x.x.x.x for Oracle on Linux x86-64>.zip
$ cd fbo_ggs_Linux_x64_shiphome/Disk1/
$ cat response/oggcore.rsp | grep -Ev "^#|^$"
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
INSTALL_OPTION=ORA12c
SOFTWARE_LOCATION=/opt/ogg
START_MANAGER=false
MANAGER_PORT=
DATABASE_LOCATION=
INVENTORY_LOCATION=
UNIX_GROUP_NAME=
2、 配置OGG环境变量(oracle 数据库及OGG源端环境变量)
$ vim /etc/profile
export ORACLE_BASE=/opt/oracle
export ORACLE_SID=oggtest
#export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
#export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
export ORACLE_HOME=$ORACLE_BASE/product/12/db_1
export OGG_HOME=/opt/ogg
export PATH=$OGG_HOME:$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:/usr/lib
export LANG="zh_CN.UTF-8"
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.AL32UTF8"
export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
静默安装#填写oggcore.rsp的绝对路径地址
$ ./runInstaller -silent -responseFile </absolute/path>
注意:
</absolute/path>:要赶写绝对路径
!!!至此,GoldenGate安装完成
查看依赖是否正常
$ ldd ggsci
3.目标端
3.2.1、解压
$ mkdir -p /opt/ogg
$ unzip Oracle GoldenGate x.x.x.x.x for Big Dataon Linux x86-64.zip
$ tar zxvf OGG_BigData_Linux_x64_x.x.x.x.x.tar -C /opt/ogg
$ vim /etc/profile
export JAVA_HOME=<本地 JAVA 目录>
export CLASSPATH=$JAVA_HOME/lib/tools.jar:$JAVA_HOME/lib/dt.jar:.
export PATH=$JAVA_HOME/bin:$PATH
export OGG_HOME=/opt/ogg
export LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server:$JAVA_HOME/jre/lib/amd64/libjsig.so:$JAVA_HOME/jre/lib/amd64/server/libjvm.so:$OGG_HOME/lib
export PATH=$OGG_HOME:$PATH
$ source /etc/profile
4、oracle打开归档模式
$ su - oracle
$ export ORACLE_SID=oggtest
$ sqlplus / as sysdba
执行下面的命令查看当前是否为归档模式
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Current log sequence 14
若为Disabled,手动打开即可
SQL> conn / as sysdba (以DBA身份连接数据库)
SQL> shutdown immediate (立即关闭数据库)
SQL> startup mount (启动实例并加载数据库,但不打开)
SQL> alter database archivelog; (更改数据库为归档模式)
SQL> alter database open; (打开数据库)
SQL> alter system archive log start; (启用自动归档)
再执行一下
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用sql
存档终点 USE_DB_RECOVERY_FILE_DEST
最早的联机日志序列 1867
下一个存档日志序列 1869
当前日志序列 1869
SQL>
可以看到为Enabled,则成功打开归档模式。
4.1 Oracle打开日志相关
OGG基于辅助日志等进行实时传输,故需要打开相关日志确保可获取事务内容,通过下面的命令查看该状态
SQL> select force_logging, supplemental_log_data_min from v$database;
FORCE_ SUPPLEMENTAL_LOG
------ ----------------
NO NO
若为NO,则需要通过命令修改
SQL> alter database force logging;
SQL> alter database add supplemental log data;
再查看一下为YES即可
SQL> select force_logging, supplemental_log_data_min from v$database;
FORCE_ SUPPLEMENTAL_LOG
------ ----------------
YES YES
4.2、 oracle创建复制用户(可省略)
首先root用户建立相关文件夹,并赋予权限
# mkdir -p /u01/app/oracle/oggdata/orcl
# chown -R oracle:oinstall /u01/app/oracle/oggdata/orcl
然后执行下面sql
# sqlplus / as sysdba
SQL> create tablespace ogg_tbs datafile size 1G autoextend on maxsize 10G;
Tablespace created.
SQL> create user ogg identified by ogg default tablespace ogg_tbs account unlock;
注意:此用户用来登录源端 OGG
User created.
SQL> grant dba to ogg;
Grant succeeded.
SQL> alter system set enable_goldengate_replication=true scope=both;
5、OGG初始化
# cd /opt/ogg
# ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (ambari.master.com) 1> create subdirs
Creating subdirectories under current directory /opt/ogg
Parameter files /opt/ogg/dirprm: created
Report files /opt/ogg/dirrpt: created
Checkpoint files /opt/ogg/dirchk: created
Process status files /opt/ogg/dirpcs: created
SQL script files /opt/ogg/dirsql: created
Database definitions files /opt/ogg/dirdef: created
Extract data files /opt/ogg/dirdat: created
Temporary files /opt/ogg/dirtmp: created
Stdout files /opt/ogg/dirout: created
若已经创建,则会显示 already exists.
GGSCI 2>
5.1、Oracle创建测试表
创建一个用户,在该用户下新建测试表,用户名、密码、表名均为 test_ogg。
注意:此用户是用来操作数据表
# sqlplus / as sysdba
sys@xxx> create user test_ogg identified by test_ogg default tablespace test_ogg;
sys@xxx> grant dba to test_ogg;
sys@xxx> conn test_ogg/test_ogg;
sys@xxx> create table tbl_ogg(id int, name varchar(20), ts current_timestamp, primary key(id));
5.2、OGG源端配置
5.2.1、配置OGG的全局变量
先切换到oracle用户下
$ su - oracle
$ cd /opt/ogg
$ ggsci
GGSCI (henghe26 as fund@ods) 1> dblogin userid ogg password ogg
Successfully logged into database.
GGSCI 2> edit param ./globals (可跳过)
oggschema ogg
5.2.2、 配置管理器mgr
GGSCI (henghe26 as fund@ods) 3> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS /opt/ogg/dirdat/*,usecheckpoints, minkeepdays 3
LAGREPORTMINUTES 1
LAGINFOMINUTES 15
LAGCRITICALMINUTES 45
5.2.3、 添加复制表
GGSCI 5> add trandata test_ogg.tbl_ogg
GGSCI 6> info trandata test_ogg.tbl_ogg
GGSCI 7>
5.2.4、 配置extract进程
GGSCI (henghe26 as fund@ods) 7> edit param ext__test_ogg__tbl_ogg
extract ext_ts
SETENV (ORACLE_SID=orcl12)
SETENV (NLS_LANG=american_america.AL32UTF8)
userid ogg@orcl12,password ogg
-- 生产需要添加 standby 日志抽取参数
TRANLOGOPTIONS EXCLUDEUSER ogg
TRANLOGOPTIONS DBLOGREADER
exttrail /opt/ogg/dirdat/ts
UPDATERECORDFORMAT FULL
NOCOMPRESSDELETES
EOFDELAY 3
NUMFILES 5000
DISCARDFILE /opt/ogg/dirrpt/ts.dsc,MEGABYTES 1000
REPORTCOUNT EVERY 1 SECONDS,RATE
--DDL
DDL INCLUDE MAPPED
DDLOPTIONS NOCROSSRENAME REPORT --开启DDL报告
table test_ogg.tbl_ogg;
添加extract进程:
GGSCI 16> add extract ext__test_ogg__tbl_ogg,tranlog,begin now
EXTRACT added.
添加trail文件的定义与extract进程绑定:
GGSCI 17> add exttrail /opt/ogg/dirdat/to,extract ext__test_ogg__tbl_ogg
5.2.5、 配置pump进程
pump进程本质上来说也是一个extract,只不过他的作用仅仅是把trail文件传递到目标端,配置过程和extract进程类似,只是逻辑上称之为pump进程
GGSCI 9> edit param pmp__test_ogg__tbl_ogg
EXTRACT pmp_ts
USERID ogg@orcl12,password ogg
passthru
dynamicresolution
rmthost <源端IP> mgrport 7809
rmttrail /opt/ogg/dirdat/to
table test_ogg.tbl_ogg;
分别将本地trail文件和目标端的trail文件绑定到extract进程:
GGSCI 1> add extract pmp__test_ogg__tbl_ogg,exttrailsource /opt/ogg/dirdat/to
EXTRACT added.
GGSCI 2> add rmttrail /opt/ogg/dirdat/to,extract pmp__test_ogg__tbl_ogg
RMTTRAIL added.
5.3、OGG目标端配置
5.3.1、开启kafka服务
# cd /opt/kafka_2.11-1.1.0/
# bin/zookeeper-server-start.sh config/zookeeper.properties
# bin/kafka-server-start.sh config/server.properties
5.3.2、配置管理器mgr
注意:目标端 mgr 与源端 mgr 一致
GGSCI 65> edit param mgr
PORT 7809
DYNAMICPORTLIST 7810-7909
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS /opt/ogg/dirdat/*,usecheckpoints, minkeepdays 3
5.3.3、配置checkpoint (可跳过)
checkpoint即复制可追溯的一个偏移量记录,在全局配置里添加checkpoint表即可。
edit param ./GLOBALS
CHECKPOINTTABLE test_ogg__tbl_ogg.checkpoint
5.3.4、配置replicate进程
GGSCI 67> edit param rekafka__test_ogg__tbl_ogg
REPLICAT rekafka
sourcedefs /opt/ogg/dirdef/test_ogg__tbl_ogg
TARGETDB LIBFILE libggjava.so SET property=dirprm/kafka__test_ogg__tbl_ogg.props
REPORTCOUNT EVERY 1 MINUTES, RATE
GROUPTRANSOPS 10000
DLLOPTIONS REPORT
MAP test_ogg.tbl_ogg, TARGET test_ogg.tbl_ogg;
GGSCI 70>
5.3.5、配置kafka
# vim $OGG_HOME/dirprm/kafka__test_ogg__tbl_ogg.props
gg.handlerlist=kafkahandler
gg.handler.kafkahandler.type=kafka
gg.handler.kafkahandler.format.includePrimaryKeys=true
gg.handler.kafkahandler.KafkaProducerConfigFile=producer__test_ogg__tbl_ogg.properties
gg.handler.kafkahandler.topicMappingTemplate=topic__test_ogg__tbl_ogg
gg.handler.kafkahandler.format=json
gg.handler.kafkahandler.mode=op
gg.classpath=dirprm/:/<kafka lib>/*:/opt/ogg/:/opt/ogg/lib/*
注意:
kafka lib:需要在现场确认填写
# vim $OGG_HOME/dirprm/producer__test_ogg__tbl_ogg.properties
bootstrap.servers=<broker list>
acks=-1
compression.type=none
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
注意:
broker list:需要在现场确认填写
5.3.6、添加trail文件到replicate进程
GGSCI > add replicat rekafka exttrail /opt/ogg/dirdat/to,checkpointtable test_ogg__tbl_ogg.checkpoint
注意:
test_ogg__tbl_ogg.checkpoint 为 5.3.3 配置
6、测试
6.1 启动所有进程
在源端和目标端的OGG命令行下使用start [进程名]的形式启动所有进程。
启动顺序按照源mgr——目标mgr——源extract——源pump——目标replicate来完成。
全部需要在ogg目录下执行ggsci目录进入ogg命令行。
源端依次是
ggsci> start mgr
ggsci> start ext__test_ogg__tbl_ogg
ggsci> start pmp__test_ogg__tbl_ogg
目标端
ggsci> start mgr
ggsci> start rekafka__test_ogg__tbl_ogg
可以通过info all 或者info [进程名] 查看状态,所有的进程都为RUNNING才算成功
源端
GGSCI (henghe26 as fund@ods) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ext__test_ogg__tbl_ogg 00:00:00 00:00:10
EXTRACT RUNNING pmp__test_ogg__tbl_ogg 00:00:00 00:00:05
GGSCI 14>
目标端
GGSCI 71> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING rekafka__test_ogg__tbl_ogg 00:00:00 00:00:08
GGSCI 72>
6.2 异常解决
如果有不是RUNNING可通过查看日志的方法检查解决问题,具体通过下面两种方法
vim ggser.log
或者ogg命令行,以rekafka进程为例
GGSCI 2> view report rekafka__test_ogg__tbl_ogg