1 版本要求

  由于ogg版本差异会导致trail文件格式差异，zcbus暂时支持以下版本：

  • OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO
    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
  • OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
    Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
  • OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO —zcbus-8.1-16-20231113.tar.gz版本兼容
    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 19c on Oct 17 2019 21:16:29
  • OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_151212.0607_FBO —zcbus-8.1-16-20240929.tar.gz版本兼容
    Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.32 21609058 OGGCORE_11.2.1.0.0OGGBP_PLATFORMS_151212.0607_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Feb  7 2016 21:54:48
    注：
    （1）需要设置trail_file_old_version=1（OGG12.1以及之前Trail文件是6位，OGG版本大于等于12.2的Trail文件格式转为9位）
    （2）ogg11版本，配置extract进程时需要添加以下参数，否则update操作trail文件中不会记录before部分，而delete操作只会记录主键
    getupdatebefores
nocompressdeletes

2 兼容条件

  ZCBUS兼容OGG的模式，采用TRAIL文件缓存解析模式，即由OGG完成日志解析。ZCBUS自助完成全量数据发布，管理以及增量OGG TRAIL解析和分发管理模式。
  支持OGG分析源端trail文件和接收端的trail格式文件
  OGG模式，通常建议在生产读取一次数据，将数据传输到ZCBUS服务平台，ZCBUS会有一个服务接收端，接收OGG的PUMP进程上传的数据
 ZCBUS将收取的数据，翻译成ZCBUS所识别的数据，进行下一步数据同步延伸。

主要场景：业务量比较高的ORACLE数据库【50GB/小时以上或者1TB/天以上的业务库】。

最终支持如下几种分析日志模式：

  • 生产读取日志，生产发送到ZCBUS集群中
  • 生产读取日志，远程传输到集群中，配置完全兼容OGG配置模式，将数据输送到ZCBUS集群中
  • DOWNSTREAM读取日志模式，在第三节点日志分析，再此基础上兼容上述1,2两部分数据复制模式
  • DOWNSTREAM读取日志模式，在第三方节点上进行日志分析，再将数据直接输送到ZCBUS集群中

    即，继承OGG所有同步兼容模式【生产安装AGENTD，远程分析日志，只是ADG备端】

3 修改数据库配置

3.1 修改参数（重要）

ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;

3.2 开启归档和补充日志

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area  521936896 bytes
Fixed Size                  2254824 bytes
Variable Size             209717272 bytes
Database Buffers          306184192 bytes
Redo Buffers                3780608 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> alter database add supplemental log data;
Database altered.

SQL> alter database force logging;
Database altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
  • 查询修改结果
SQL>  select log_mode,supplemental_log_data_min,force_logging from v$database;
LOG_MODE     SUPPLEME FOR
------------ -------- ---
ARCHIVELOG   YES      YES

3.3 创建OGG用户及赋权

SQL> create tablespace oggtbs datafile '/home/oracle/orcldata/orcl/oggtbs.dbf' size 100M autoextend on;

Tablespace created.

SQL> create user ogg identified by ogg default tablespace oggtbs temporary tablespace temp;

User created.

SQL> grant connect,resource to ogg;

Grant succeeded.

SQL> grant execute on utl_file to ogg;

Grant succeeded.

3.4 OGG支持DDL的话需要执行下面的SQL,否则不需要

在数据库上执行GoldenGate脚本（开启DDL）
注意：一定要cd到ogg的目录下去执行，而且oracle用户的bash_profile里需要有export LD_LIBRARY_PATH=$ORACLE_HOME/lib

$ cd /data/ogg01

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 5 20:34:34 2016
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> @marker_setup.sql;

输入之前创建的ogg用户名

SQL> @ddl_setup.sql;

输入之前创建的ogg用户名

SQL> @role_setup.sql;

输入之前创建的ogg用户名

SQL>grant GGS_GGSUSER_ROLE to ogg;

Grant succeeded.

SQL>@ddl_enable.sql;

Trigger altered.

4 OGG配置

注意：一定要cd到ogg的目录下去执行
GoldenGate的日志位于其安装目录下的ggserr.log中，在配置过程中出现错误的时候可以通过日志排查问题。

4.1 启动两台数据库服务器的goldengate 的manager进程

$ cd /data/ogg01

./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 (localhost.localdomain) 1> create subdirs


GGSCI (localhost.localdomain) 1> edit params mgr

PORT 7809

--DYNAMICPORTLIST 7840-7939
-- AUTOSTART E *
--AUTORESTART EXTRACT *,RETRIES5,WAITMINUTES 3
--PURGEOLDEXTRACTS /ogg/app/dirdat/*,usecheckpoints, minkeepdays 3
--LAGREPORTHOURS 1
--LAGINFOMINUTES 30
--LAGCRITICALMINUTES 45

参数说明：
PORT：指定服务监听端口，默认端口为7809
DYNAMICPORTLIST：动态端口：可以制定最大256个可用端口的动态列表，当指定的端口不可用时，管理进程将会从列表中选择一个可用的端口，源端和目标段的Collector、Replicat、GGSCI进程通信也会使用这些端口；
COMMENT：注释行，也可以用–来代替;
AUTOSTART：指定在管理进程启动时自动启动哪些进程；E代表extract进程，R代表replicat进程
AUTORESTART：自动重启参数设置：本处设置表示每3分钟尝试重新启动所有EXTRACT进程，共尝试5次；
PURGEOLDEXTRACTS：定期清理trail文件设置：本处设置表示对于超过3天的trail文件进行删除。
LAGREPORT、LAGINFO、LAGCRITICAL：定义数据延迟的预警机制：本处设置表示MGR进程每隔1小时检查EXTRACT的延迟情况，如果超过了30分钟就把延迟作为信息记录到错误日志中，如果延迟超过了45分钟，则把它作为警告写到错误日志中。
GGSCI (localhost.localdomain) 1> start mgr
Manager started.
GGSCI (localhost.localdomain) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING

4.2 创建一个OGG捕获进程

#创建一个extract进程
GGSCI (localhost.localdomain) 1> add extract ext01,tranlog,begin now
EXTRACT added.

#添加extract进程的trail文件配置
#其中megabytes表示队列大小，本处为10M

GGSCI (localhost.localdomain) 2> add exttrail /data/ogg01/dirdat/ee,extract ext01,megabytes 10
EXTTRAIL added.

###配置extract 进程

GGSCI (localhost.localdomain) 3> edit params ext01
extract ext01
SETENV(ORACLE_SID="oracle12c")
SETENV(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid XSTRMADMIN, password XSTRMADMIN
EXTTRAIL /data/ogg01/dirdat/ee
TRANLOGOPTIONS ASMUSER sys@ASM,ASMPASSWORD oracle           ###ASM需要配置
ddl include all       ###开启ddl
DYNAMICRESOLUTION
table zcbus_001.*;


注：ogg11版本需要添加以下参数，否则update操作trail文件中不会记录before部分，而delete操作只会记录主键
getupdatebefores
nocompressdeletes

4.3 配置PUMP进程

4.3.1 添加pump进程

GGSCI (host01.example.com) 1> add extract pump01,exttrailsource /data/ogg01/dirdat/ee

EXTRACT added.

4.3.2 添加远程trail文件

GGSCI (zcbusdbs) 8> add rmttrail /data/oggdt/dirdat/zfl, extract pump01,megabytes 10

file portion must be two characters. ——存文件的路径必须是两个字符。

GGSCI (zcbusdbs) 9> add rmttrail /data/oggdt/dirdat/dt, extract pump01,megabytes 10

RMTTRAIL added.

GGSCI (host01.example.com) 3> edit param pump01

extract pump01
userid ogg, password ogg
dynamicresolution
passthru
rmthost 127.0.0.1, mgrport 7939,compress
rmttrail /data/oggdt/dirdat/dt
table zcbus_001.*;

4.3.3 添加补充日志

GGSCI (localhost.localdomain) 4> dblogin userid ogg@orcl_ogg1,password ogg

Successfully logged into database.

GGSCI (localhost.localdomain as ogg@aegisdb) 5> ADD TRANDATA orcl.*

4.3.4 启动捕获进程

[oracle@zcbusdbs ogg01]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.



GGSCI (zcbusdbs) 1> start mgr
Manager started.


GGSCI (zcbusdbs) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT01       00:00:00      00:00:00    


GGSCI (zcbusdbs) 3> stats ext01

Sending STATS request to EXTRACT EXT01 ...

Start of Statistics at 2021-01-02 19:11:28.

Output to /data/ogg01/dirdat/ee:

Extracting from ZCBUS_001.BUS_PUSH_EXCEPTION_DATA to ZCBUS_001.BUS_PUSH_EXCEPTION_DATA:

*** Total statistics since 2021-01-02 19:08:46 ***
        Total inserts                                    759.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                 759.00

*** Daily statistics since 2021-01-02 19:08:46 ***
        Total inserts                                    759.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                 759.00

*** Hourly statistics since 2021-01-02 19:08:46 ***
        Total inserts                                    759.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                 759.00

*** Latest statistics since 2021-01-02 19:08:46 ***
        Total inserts                                    759.00
        Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                 759.00
End of Statistics.

4.3.5 启动推送进程

GGSCI (zcbusdbs) 25> start pump01

Sending START request to MANAGER ...
EXTRACT PUMP01 starting

GGSCI (zcbusdbs) 23> info pump01

EXTRACT    PUMP01    Last Started 2021-04-23 08:50   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           7311
Log Read Checkpoint  File /data/ogg01/dirdat/ee000002216
                     2021-04-23 16:23:22.000000  RBA 228795

5 给zcbusserver容器挂载目录

本地路径：/data/oggdt/dirdat
容器映射路径：/usr/local/zcbus/cache/dirdat

  • docker路径查看
    docker info | grep "Docker Root Dir"
  • zcbusserver容器ID查看
    docker ps -a|grep zcbusserver|awk '{print $1}'
  • 关闭docker
    systemctl  stop docker
  • sudo su切换到root身份，进入对应zcbusserver容器目录
    cd docker路径/containers/zcbusserver容器ID*/
  • 修改hostconfig.json
    Binds格式：本地路径:容器映射路径:读写权限
    例如：
    "Binds":["/data/oggdt/dirdat:/usr/local/zcbus/cache/dirdat:rw"],
  • 修改config.v2.json
    例如:
    "MountPoints": {
"/usr/local/zcbus/cache/dirdat":{"Source":"/data/oggdt/dirdat","Destination":"/usr/local/zcbus/cache/dirdat","RW":true,"Name":"","Driver":"","Type":"bind","Relabel":"rw","Propagation":"rprivate","Spec":{"Type":"bind","Source":"/data/oggdt/dirdat","Target":"/usr/local/zcbus/cache/dirdat"},"SkipMountpointCreation":false}},
  • 启动docker
systemctl  start docker

6 WEB-DB配置

ogg_type：如果是分析ogg的源端，则设置0，目标端则设置1，默认是源端
ogg_service：如果是分析ogg源端，则设置extract服务名，如果是目标端，则设置trail文件的前缀名，比如dt
ogg_home：如果是分析ogg源端，则设置ggsci工具所在的目录，如果是目标端，则设置trail文件的目录，比如/usr/local/zcbus/cache/dirdat

6.1 分析ogg的源端方式DB配置如下

6.2 分析ogg的目标端方式DB配置如下

7 补充说明

7.1 OGG安装

7.1.1 软件下载

GoldenGate下载地址为：https://www.oracle.com/middleware/technologies/goldengate-downloads.html

7.1.2 配置环境变量

[oracle@localhost ~]$ more /home/oracle/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
     . ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export OGG_HOME=/u01/app/ogg
export PATH=$OGG_HOME:$ORACLE_HOME/bin:$PATH:$HOME/bin
关键配置
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME

7.1.3 创建OGG运行目录

mkdir -p /data/ogg01

7.1.4 解压软件

unzip 191004_fbo_ggs_Linux_x64_shiphome.zip

解压之后获得的文件

[oracle@oracle1 tmp]$ ls
191004_fbo_ggs_Linux_x64_shiphome.zip  fbo_ggs_Linux_x64_shiphome  OGG-19.1.0.0-README.txt  OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf

7.1.5 调整OGG相关配置文件

[oracle@oracle1 tmp]$ cd fbo_ggs_Linux_x64_shiphome/
[oracle@oracle1 fbo_ggs_Linux_x64_shiphome]$ ls
Disk1
[oracle@oracle1 fbo_ggs_Linux_x64_shiphome]$ cd Disk1/
[oracle@oracle1 Disk1]$ ls
install  response  runInstaller  stage
[oracle@oracle1 Disk1]$ pwd
/home/oracle/tmp/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@oracle1 Disk1]$ cat response/oggcore.rsp | grep -v "^#|^$"
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0 #标注响应文件版本，默认即可
INSTALL_OPTION=ORA12c  #11g数据库选择ORA11g，12C选择ORA12c
SOFTWARE_LOCATION=/data/ogg01 #安装目录
START_MANAGER=false
MANAGER_PORT=
DATABASE_LOCATION=
INVENTORY_LOCATION=
UNIX_GROUP_NAME=

7.1.6 安装

./runInstaller  -silent  -responseFile /data/OGG/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

至此 OGG 安装完成
注意安装目录必须使用绝对路径

7.2 目标端安装并启动mgr

7.2.1 下载

wget 101.201.81.45:8888/ZCBUS/ogg_mgr_min.tar.gz

7.2.2 解压安装

tar xvfmz ogg_mgr_min.tar.gz

7.2.3 启动

[oracle@zcbusdbs oggdt]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Apr 16 2018 00:53:30
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.



GGSCI (zcbusdbs) 1> start mgr
Manager started.

GGSCI (zcbusdbs) 2> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
文档更新时间: 2024-09-28 21:47   作者：阿力