本文旨在从安装部署中了解OGG的知识
一、 OGG理论概念
1.1 简单介绍
1.2 同步过程
1.3 Oracle Golden Gate简要体系图
二、 测试实验
2.1 实验环境介绍
2.2 源端MySQL数据库配置
- 1. 创建操作系统MySQL用户
- 2. 镜像 & yum源
- 3. 安装数据库
- 4. 拷贝初始化参数文件到指定位置并修改配置文件
- 5. 设置root账户登陆密码
- 6. 查看参数是否生效
- 7. 创建即将同步的表
- 8. 安装MySQL数据库的GoldenGate软件（MySQL用户安装）
- 9. 源端配置OGG诸进程，尚不用启动进程
- 10. 目标端ogg配置
- 11. 目标端创建OGG管理用户和测试用户以及测试表
- 12. 目标端安装OGG软件
- 13. 源端配置OGG诸进程，尚不用启动进程
- 14. 按照顺序开启源端、目标端OGG进程
- 15. 模拟源端数据库业务持续运行
- 16. 查看源端、目标端数据情况
- 17. 查看目标端、远端进程信息
- 18. 进行dml测试
一、OGG理论概念
目前许多客户环境都已经在广泛使用OGG。由于工作需要，近期我也会系统的学习下OGG，目前学习主要以OGG在Oracle数据库之间的同步来说明一些基本概念，后续看时间情况可能了解OGG对其他主流数据库的同步。
1.1 简单介绍
> Oracle Golden Gate软件 是一种基于日志的结构化数据复制备份软件，它 通过解析源数据库在线日志或归档日志获得数据的增量变化，再将这些变化 应用到目标数据库，从而 实现源数据库与目标数据库同步。
> Oracle Golden Gate可以在异构的IT基础结构（包括几乎所有常用操作系统平台和数据库平台）之间实现大量数据亚秒一级的实时复制,从而可以在应急系统、在线报表、实时数据仓库供应、交易跟踪、数据同步、集中/分发、容灾、数据库升级和移植、双业务中心等多个场景下应用。
> Oracle Golden Gate可以实现一对一、广播(一对多)、聚合(多对一)、双向、点对点、级联等 多种灵活的拓扑结构。
假设已有源数据库和目标数据库，且分别部署好OGG软件。
源数据库部署的OGG，简称 源OGG；
目标数据库部署的OGG，简称 目标OGG。
1.2 同步的过程
就是源OGG的 EXTRACT进程从源数据库的 联机redologs中抽取获得变化，把它写到 本地trail文件中，然后源OGG的 Data pump进程再从 本地trail文件中抽取变化传给目标OGG的 collector进程，目标的collector进程再把这些变化写入 目标trail文件中，最后目标OGG的 Replicat进程把目标trail文件中记录的变化应用到目标数据库中。
如果要同步的表数量比较多或是业务有区分，需要分开管理，OGG是支持分开多个extract进程，多个datapump进程，多个replicat进程，这样，可以启动或停止其中任意一类同步，而不影响其他的同步。
需要注意的是，每套OGG所有的进程都是由其Manager进程来管理的，也就是说Manager进程负责管理，启动关闭其他进程。OGG用组来表示它的进程，上面简单提到的extract、replicat这些进程，实际上需要各自的参数文件、检查点文件等。这些内容共同组成一个组，且每个组需要有一个组名字。
1.3 Oracle Golden Gate的简要体系图
- OGG的检查点信息
对于源OGG只能以文件形式默认存放在GGHOME/dirchk目录下，一个组对应一个文件。
而对于目标OGG检查点信息可以存放在数据库表中，且建议存放到数据库表中。
对于源数据库，如果要使用OGG，需要配置supplemental logging，原因是OGG相当于逻辑同步，除了要记录被修改的记录，还要把能够标识这条记录的信息同时记录在日志中。这样的话，Oracle默认情况是不达标的，需要额外开启supplemental logging才可以。
具体做法是：在数据库级别开启最小附加日志，在需要同步的表级别开启详细附加日志。
# 数据库级别开启强制记录日志和最小附加日志：
SQL> alter database force logging;
SQL> alter database add supplemental log data;
#表级别开启详细附加日志（这里举例是jy用户下的t_second表）：
GGSCI> add trandata jy.t_second
对于 目标数据库，配置检查点信息到目标数据库表中，具体做法：
1.目标GG的全局参数(./globals)配置
checkpointtable ggt.chkpt
2.命令添加checkpointtable
GGSCI> add checkpointtable ggt.chkpt
此外数据库需要 启用Golden Gate复制参数：enable_goldengate_replication
SQL> alter system set enable_goldengate_replication=true;
二、测试实验
2.1 实验环境介绍
---------- ----------
| | | |
| | | |
| | | |
| | | |
| MySQL | ------------> | Oracle |
| | | |
| | | |
| | | |
---------- ----------
Source Host :awen001(MySQL) Target Host :awen002(Oracle)
IP Addr :192.168.1.21 IP Addr :192.168.1.22
Manager Account:root(MySQL) Manager Account:ggt_admin(Oracle)
Srouce DB :test01(MySQL) Target Account :result01(Oracle)
Source Table :Mar01 Target Table :Mar02
OS :CentOS 7.3 OS :CentOS 7.3
OGG安装文件
191003_ggs_Linux_x64_MySQL_64bit.zip
191004_fbo_ggs_Linux_x64_shiphome.zip
2.2 源端MySQL数据库配置
1. 创建操作系统MySQL用户
userdel mysqlgroupadd -g 1007 mysql
useradd -u 1103 -g mysql mysql
echo "mysql" | passwd --stdin mysql
id mysql
特别声明：创建MySQL用户的目的是要把Oracle Golden Gate安装到MySQL用户下，否则当OGG读取MySQL二进制索引文件
[log-bin-index=/var/lib/mysql/binary-log.index]的时候，会因为没有相关权限而影响OGG的部署。
2. 镜像 & yum源
查看服务器挂载镜像以及yum源配置
[root@awen001 ~]# df -h | grep CentOS
/dev/sr0 4.1G 4.1G 0 100% /run/media/root/CentOS 7 x86_64
[root@awen001 ~]# cat /etc/yum.repos.d/yum.repo
[redhat7.6]
name=redhat7.6
baseurl=file:///run/media/root/CentOS\ 7\ x86_64/
gpgcheck=0
enabled=1
[root@awen001 ~]# yum repolist
已加载插件：fastestmirror, langpacks
Loading mirror speeds from cached hostfile
源标识 源名称 状态
!redhat7.6 redhat7.6 3,831
repolist: 3,831
3. 安装数据库
CentOS 7自带MariaDB替代默认的MySQL客户端安装包。MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。
yum install -y mariadb*
[root@awen001 ~]# yum install -y mariadb*
已加载插件：fastestmirror, langpacks
redhat7.6 | 3.6 kB 00:00:00
Loading mirror speeds from cached hostfile
软件包 1:mariadb-libs-5.5.52-1.el7.x86_64 已安装并且是最新版本
正在解决依赖关系
--> 正在检查事务
---> 软件包 mariadb.x86_64.1.5.5.52-1.el7 将被 安装
---> 软件包 mariadb-bench.x86_64.1.5.5.52-1.el7 将被 安装
--> 正在处理依赖关系 perl(GD)，它被软件包 1:mariadb-bench-5.5.52-1.el7.x86_64 需要
--> 正在处理依赖关系 perl(DBI)，它被软件包 1:mariadb-bench-5.5.52-1.el7.x86_64 需要
---> 软件包 mariadb-devel.x86_64.1.5.5.52-1.el7 将被 安装
--> 正在处理依赖关系 openssl-devel(x86-64)，它被软件包 1:mariadb-devel-5.5.52-1.el7.x86_64 需要
---> 软件包 mariadb-server.x86_64.1.5.5.52-1.el7 将被 安装
--> 正在处理依赖关系 perl-DBD-MySQL，它被软件包 1:mariadb-server-5.5.52-1.el7.x86_64 需要
---> 软件包 mariadb-test.x86_64.1.5.5.52-1.el7 将被 安装
--> 正在处理依赖关系 perl(Test::More)，它被软件包 1:mariadb-test-5.5.52-1.el7.x86_64 需要
--> 正在处理依赖关系 perl(Env)，它被软件包 1:mariadb-test-5.5.52-1.el7.x86_64 需要
--> 正在检查事务
---> 软件包 openssl-devel.x86_64.1.1.0.1e-60.el7 将被 安装
--> 正在处理依赖关系 krb5-devel(x86-64)，它被软件包 1:openssl-devel-1.0.1e-60.el7.x86_64 需要
---> 软件包 perl-DBD-MySQL.x86_64.0.4.023-5.el7 将被 安装
---> 软件包 perl-DBI.x86_64.0.1.627-4.el7 将被 安装
--> 正在处理依赖关系 perl(RPC::PlServer) >= 0.2001，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
--> 正在处理依赖关系 perl(RPC::PlClient) >= 0.2000，它被软件包 perl-DBI-1.627-4.el7.x86_64 需要
---> 软件包 perl-Env.noarch.0.1.04-2.el7 将被 安装
---> 软件包 perl-GD.x86_64.0.2.49-3.el7 将被 安装
---> 软件包 perl-Test-Simple.noarch.0.0.98-243.el7 将被 安装
--> 正在检查事务
---> 软件包 krb5-devel.x86_64.0.1.14.1-26.el7 将被 安装
--> 正在处理依赖关系 libverto-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要
--> 正在处理依赖关系 libselinux-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要
--> 正在处理依赖关系 libcom_err-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要
--> 正在处理依赖关系 keyutils-libs-devel，它被软件包 krb5-devel-1.14.1-26.el7.x86_64 需要
---> 软件包 perl-PlRPC.noarch.0.0.2020-14.el7 将被 安装
--> 正在处理依赖关系 perl(Net::Daemon) >= 0.13，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Net::Daemon::Test)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Net::Daemon::Log)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在处理依赖关系 perl(Compress::Zlib)，它被软件包 perl-PlRPC-0.2020-14.el7.noarch 需要
--> 正在检查事务
---> 软件包 keyutils-libs-devel.x86_64.0.1.5.8-3.el7 将被 安装
---> 软件包 libcom_err-devel.x86_64.0.1.42.9-9.el7 将被 安装
---> 软件包 libselinux-devel.x86_64.0.2.5-6.el7 将被 安装
--> 正在处理依赖关系 libsepol-devel(x86-64) >= 2.5-6，它被软件包 libselinux-devel-2.5-6.el7.x86_64 需要
--> 正在处理依赖关系 pkgconfig(libsepol)，它被软件包 libselinux-devel-2.5-6.el7.x86_64 需要
--> 正在处理依赖关系 pkgconfig(libpcre)，它被软件包 libselinux-devel-2.5-6.el7.x86_64 需要
---> 软件包 libverto-devel.x86_64.0.0.2.5-4.el7 将被 安装
---> 软件包 perl-IO-Compress.noarch.0.2.061-2.el7 将被 安装
--> 正在处理依赖关系 perl(Compress::Raw::Zlib) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
--> 正在处理依赖关系 perl(Compress::Raw::Bzip2) >= 2.061，它被软件包 perl-IO-Compress-2.061-2.el7.noarch 需要
---> 软件包 perl-Net-Daemon.noarch.0.0.48-5.el7 将被 安装
--> 正在检查事务
---> 软件包 libsepol-devel.x86_64.0.2.5-6.el7 将被 安装
---> 软件包 pcre-devel.x86_64.0.8.32-15.el7_2.1 将被 安装
---> 软件包 perl-Compress-Raw-Bzip2.x86_64.0.2.061-3.el7 将被 安装
---> 软件包 perl-Compress-Raw-Zlib.x86_64.1.2.061-4.el7 将被 安装
--> 解决依赖关系完成
依赖关系解决
==========================================================================================================================================================
Package 架构 版本 源 大小
==========================================================================================================================================================
正在安装:
mariadb x86_64 1:5.5.52-1.el7 redhat7.6 8.7 M
mariadb-bench x86_64 1:5.5.52-1.el7 redhat7.6 387 k
mariadb-devel x86_64 1:5.5.52-1.el7 redhat7.6 750 k
mariadb-server x86_64 1:5.5.52-1.el7 redhat7.6 11 M
mariadb-test x86_64 1:5.5.52-1.el7 redhat7.6 8.1 M
为依赖而安装:
keyutils-libs-devel x86_64 1.5.8-3.el7 redhat7.6 37 k
krb5-devel x86_64 1.14.1-26.el7 redhat7.6 651 k
libcom_err-devel x86_64 1.42.9-9.el7 redhat7.6 31 k
libselinux-devel x86_64 2.5-6.el7 redhat7.6 186 k
libsepol-devel x86_64 2.5-6.el7 redhat7.6 74 k
libverto-devel x86_64 0.2.5-4.el7 redhat7.6 12 k
openssl-devel x86_64 1:1.0.1e-60.el7 redhat7.6 1.2 M
pcre-devel x86_64 8.32-15.el7_2.1 redhat7.6 479 k
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 redhat7.6 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 redhat7.6 57 k
perl-DBD-MySQL x86_64 4.023-5.el7 redhat7.6 140 k
perl-DBI x86_64 1.627-4.el7 redhat7.6 802 k
perl-Env noarch 1.04-2.el7 redhat7.6 16 k
perl-GD x86_64 2.49-3.el7 redhat7.6 173 k
perl-IO-Compress noarch 2.061-2.el7 redhat7.6 260 k
perl-Net-Daemon noarch 0.48-5.el7 redhat7.6 51 k
perl-PlRPC noarch 0.2020-14.el7 redhat7.6 36 k
perl-Test-Simple noarch 0.98-243.el7 redhat7.6 170 k
事务概要
==========================================================================================================================================================
安装 5 软件包 (+18 依赖软件包)
总下载量：33 M
安装大小：244 M
Downloading packages:
----------------------------------------------------------------------------------------------------------------------------------------------------------
总计 14 MB/s | 33 MB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
正在安装 : 1:mariadb-5.5.52-1.el7.x86_64 1/23
正在安装 : perl-Net-Daemon-0.48-5.el7.noarch 2/23
正在安装 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 3/23
正在安装 : libverto-devel-0.2.5-4.el7.x86_64 4/23
正在安装 : perl-GD-2.49-3.el7.x86_64 5/23
正在安装 : perl-Test-Simple-0.98-243.el7.noarch 6/23
正在安装 : libcom_err-devel-1.42.9-9.el7.x86_64 7/23
正在安装 : perl-Env-1.04-2.el7.noarch 8/23
正在安装 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 9/23
正在安装 : perl-IO-Compress-2.061-2.el7.noarch 10/23
正在安装 : perl-PlRPC-0.2020-14.el7.noarch 11/23
正在安装 : perl-DBI-1.627-4.el7.x86_64 12/23
正在安装 : perl-DBD-MySQL-4.023-5.el7.x86_64 13/23
正在安装 : 1:mariadb-server-5.5.52-1.el7.x86_64 14/23
正在安装 : pcre-devel-8.32-15.el7_2.1.x86_64 15/23
正在安装 : libsepol-devel-2.5-6.el7.x86_64 16/23
正在安装 : libselinux-devel-2.5-6.el7.x86_64 17/23
正在安装 : keyutils-libs-devel-1.5.8-3.el7.x86_64 18/23
正在安装 : krb5-devel-1.14.1-26.el7.x86_64 19/23
正在安装 : 1:openssl-devel-1.0.1e-60.el7.x86_64 20/23
正在安装 : 1:mariadb-devel-5.5.52-1.el7.x86_64 21/23
正在安装 : 1:mariadb-test-5.5.52-1.el7.x86_64 22/23
正在安装 : 1:mariadb-bench-5.5.52-1.el7.x86_64 23/23
验证中 : 1:mariadb-test-5.5.52-1.el7.x86_64 1/23
验证中 : keyutils-libs-devel-1.5.8-3.el7.x86_64 2/23
验证中 : 1:openssl-devel-1.0.1e-60.el7.x86_64 3/23
验证中 : 1:mariadb-devel-5.5.52-1.el7.x86_64 4/23
验证中 : libsepol-devel-2.5-6.el7.x86_64 5/23
验证中 : pcre-devel-8.32-15.el7_2.1.x86_64 6/23
验证中 : libselinux-devel-2.5-6.el7.x86_64 7/23
验证中 : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 8/23
验证中 : perl-Env-1.04-2.el7.noarch 9/23
验证中 : libcom_err-devel-1.42.9-9.el7.x86_64 10/23
验证中 : perl-Test-Simple-0.98-243.el7.noarch 11/23
验证中 : perl-GD-2.49-3.el7.x86_64 12/23
验证中 : libverto-devel-0.2.5-4.el7.x86_64 13/23
验证中 : 1:mariadb-server-5.5.52-1.el7.x86_64 14/23
验证中 : 1:mariadb-bench-5.5.52-1.el7.x86_64 15/23
验证中 : perl-DBI-1.627-4.el7.x86_64 16/23
验证中 : perl-DBD-MySQL-4.023-5.el7.x86_64 17/23
验证中 : 1:mariadb-5.5.52-1.el7.x86_64 18/23
验证中 : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 19/23
验证中 : perl-Net-Daemon-0.48-5.el7.noarch 20/23
验证中 : perl-IO-Compress-2.061-2.el7.noarch 21/23
验证中 : perl-PlRPC-0.2020-14.el7.noarch 22/23
验证中 : krb5-devel-1.14.1-26.el7.x86_64 23/23
已安装:
mariadb.x86_64 1:5.5.52-1.el7 mariadb-bench.x86_64 1:5.5.52-1.el7 mariadb-devel.x86_64 1:5.5.52-1.el7 mariadb-server.x86_64 1:5.5.52-1.el7
mariadb-test.x86_64 1:5.5.52-1.el7
作为依赖被安装:
keyutils-libs-devel.x86_64 0:1.5.8-3.el7 krb5-devel.x86_64 0:1.14.1-26.el7 libcom_err-devel.x86_64 0:1.42.9-9.el7
libselinux-devel.x86_64 0:2.5-6.el7 libsepol-devel.x86_64 0:2.5-6.el7 libverto-devel.x86_64 0:0.2.5-4.el7
openssl-devel.x86_64 1:1.0.1e-60.el7 pcre-devel.x86_64 0:8.32-15.el7_2.1 perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7
perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 perl-DBD-MySQL.x86_64 0:4.023-5.el7 perl-DBI.x86_64 0:1.627-4.el7
perl-Env.noarch 0:1.04-2.el7 perl-GD.x86_64 0:2.49-3.el7 perl-IO-Compress.noarch 0:2.061-2.el7
perl-Net-Daemon.noarch 0:0.48-5.el7 perl-PlRPC.noarch 0:0.2020-14.el7 perl-Test-Simple.noarch 0:0.98-243.el7
完毕！
View Code
4. 拷贝初始化参数文件到指定位置并修改配置文件
mv /etc/my.cnf /etc/my.cnf.bak
cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
--参数文件
# Example MariaDB config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MariaDB plays
# an important part, or systems up to 128M where MariaDB is used together with
# other programs (such as a web server)
#
# MariaDB programs look for option files in a set of
# locations which depend on the deployment platform.
# You can copy this option file to one of those
# locations. For information about these locations, do:
# 'my_print_defaults --help' and see what is printed under
# Default options are read from the following files in the given order:
# More information at: http://dev.mysql.com/doc/mysql/en/option-files.html
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--help" option.
# The following options will be passed to all MariaDB clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MariaDB server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
log-bin-index=/var/lib/mysql/binary-log.index
# binary logging format - mixed recommended
binlog_format=row
# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
# MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
# where you replace <host>, <user>, <password> by quoted strings and
# <port> by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave - required
#master-host = <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user = <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password = <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port = <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
View Code
--MariaDB启停命令
systemctl stop mariadb.service
systemctl start mariadb.service
systemctl status mariadb.service
[root@awen001 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: inactive (dead)
[root@awen001 ~]#
[root@awen001 ~]#
[root@awen001 ~]#
[root@awen001 ~]# systemctl start mariadb.service
[root@awen001 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
Active: active (running) since 四 2020-03-26 14:51:37 CST; 1s ago
Process: 3667 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 3590 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 3666 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─3666 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─3949 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/lib/mysql/awen001.u...
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: The latest information about MariaDB is available at http://mariadb.org/.
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: You can find additional information about the MySQL part at:
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: http://dev.mysql.com
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: Support MariaDB development by buying support/new features from MariaDB
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: Corporation Ab. You can contact us about this at sales@mariadb.com.
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: Alternatively consider joining our community based development effort:
3月 26 14:51:35 awen001.us.oracle.com mariadb-prepare-db-dir[3590]: http://mariadb.com/kb/en/contributing-to-the-mariadb-project/
3月 26 14:51:35 awen001.us.oracle.com mysqld_safe[3666]: 200326 14:51:35 mysqld_safe Logging to '/var/lib/mysql/awen001.us.oracle.com.err'.
3月 26 14:51:35 awen001.us.oracle.com mysqld_safe[3666]: 200326 14:51:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
3月 26 14:51:37 awen001.us.oracle.com systemd[1]: Started MariaDB database server.
View Code
5. 设置root账户登陆密码
/usr/bin/mysql -u root -S /var/lib/mysql/mysql.sockset password for 'root'@'localhost'=password('123');flush privileges;
6. 查看参数是否生效
show variables like 'log_bin';
show master status;show variables like '%binlog%';
MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 458 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_annotate_row_events | OFF |
| binlog_cache_size | 32768 |
| binlog_checksum | NONE |
| binlog_direct_non_transactional_updates | OFF |
| binlog_format | ROW |
| binlog_optimize_thread_scheduling | ON |
| binlog_stmt_cache_size | 32768 |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+
12 rows in set (0.01 sec)
View Code
7. 创建即将同步的表
create database test01;
use test01;
show tables;
create table Mar01 (id int,name varchar(10)) engine=innodb;
查看表结构和表属性
MariaDB [test01]> show table status from test01 like 'Mar01';
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Mar01 | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 0 | 10485760 | NULL | 2020-03-26 14:55:48 | NULL | NULL | latin1_swedish_ci | NULL | | |
+-------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)
View Code
8. 安装MySQL数据库的GoldenGate软件（MySQL用户安装）
创建安装目录并解压安装包
su - mysql
mkdir -p /home/mysql/ogg
mv ggs_Linux_x64_MySQL_64bit.tar ogg/
cd ogg/
tar -xvf ggs_Linux_x64_MySQL_64bit.tar
9. 源端配置OGG诸进程，尚不用启动进程
配置进程（组）名没有具体的规范：
对于复杂环境，第一位字母，local、immdiate、remote 分别对应：l、i、r；
后面根据进程属性属于extract、datapump、replicat 而分别对应：x、p、r；[mysql@awen001 ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:41:32
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (awen001.us.oracle.com) 1> dblogin sourcedb test01@localhost:3306,userid root,password 123
Successfully logged into database.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 2> CREATE SUBDIRS
Creating subdirectories under current directory /home/mysql/ogg
Parameter file /home/mysql/ogg/dirprm: created.
Report file /home/mysql/ogg/dirrpt: created.
Checkpoint file /home/mysql/ogg/dirchk: created.
Process status files /home/mysql/ogg/dirpcs: created.
SQL script files /home/mysql/ogg/dirsql: created.
Database definitions files /home/mysql/ogg/dirdef: created.
Extract data files /home/mysql/ogg/dirdat: created.
Temporary files /home/mysql/ogg/dirtmp: created.
Credential store files /home/mysql/ogg/dircrd: created.
Masterkey wallet files /home/mysql/ogg/dirwlt: created.
Dump files /home/mysql/ogg/dirdmp: created.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 3> edit params mgr
GGSCI (awen001.us.oracle.com DBLOGIN as root) 4> view params mgr
port 7810 ---指定mgr进程通信端口
dynamicportlist 7800-8000 ---表示mgr进程可以为源与目的端动态通信指定端口
autorestart extract *,waitminutes 2,resetminutes 5 ---表示自动重启extract进程组，每2分钟尝试重启所有进程，重试5次
GGSCI (awen001.us.oracle.com DBLOGIN as root) 5> start mgr
Manager started.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 6> info mgr
Manager is running (IP port TCP:awen001.us.oracle.com.7810, Process ID 8021).
GGSCI (awen001.us.oracle.com DBLOGIN as root) 7> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (awen001.us.oracle.com DBLOGIN as root) 8> edit param lxggs1
GGSCI (awen001.us.oracle.com DBLOGIN as root) 9> view params lxggs1
extract lxggs1 ---定义抓取进程名称
setenv (MYSQL_HOME=”/var/lib/mysql”) ---设置系统环境变量，指定MYSSL_HOME
setenv (MYSQL_UNIX_PORT=”/var/lib/mysql/mysql.sock") ---设置系统环境变量，指定MYSQL_UNIX_PORT
tranlogoptions altlogdest /var/lib/mysql/binary-log.index ---抓取进程使用指定地址抓取binary log日志
sourcedb test01@localhost:3306,userid root,password 123 ---指定OGG登录的数据库和端口，使用的数据库用户名及密码
exttrail ./dirdat/e2 ---指定本地trail文件地址
dynamicresolution ---动态解析表名
gettruncates ---开启捕获truncate操作，两端都需要开启
table test01.Mar01; ---定义抓取的表
GGSCI (awen001.us.oracle.com DBLOGIN as root) 10> add extract lxggs1,tranlog,begin now
EXTRACT added.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 11> add exttrail ./dirdat/e2,extract lxggs1
EXTTRAIL added.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 12> edit params lpggs1
GGSCI (awen001.us.oracle.com DBLOGIN as root) 13> view params lpggs1
extract lpggs1 ---定义投递进程名称
rmthost 192.168.1.22,mgrport 7810 ---灾备端主机IP，管理进程端口号
rmttrail /u01/app/ogg/dirdat/e2 ---灾备端保存队列文件的目录
passthru ---不与数据库交互，前提是源库与目标库objects必须一致
gettruncates ---开启捕获truncate操作，两端都需要开启
table test01.Mar01; ---定义投递的表
GGSCI (awen001.us.oracle.com DBLOGIN as root) 14> add extract lpggs1,exttrailsource ./dirdat/e2
EXTRACT added.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 15> add rmttrail /u01/app/ogg/dirdat/e2,extract lpggs1
RMTTRAIL added.
GGSCI (awen001.us.oracle.com DBLOGIN as root) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED LPGGS1 00:00:00 00:00:19
EXTRACT STOPPED LXGGS1 00:00:00 00:01:08
GGSCI (awen001.us.oracle.com DBLOGIN as root) 17> edit params defgen
GGSCI (awen001.us.oracle.com DBLOGIN as root) 18> view params defgen
defsfile ./dirdef/defgen.prm
sourcedb test01@localhost:3306,userid root,password 123
table test01.Mar01;
GGSCI (awen001.us.oracle.com DBLOGIN as root) 19> exit
[mysql@awen001 ogg]$ ./defgen paramfile dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for MySQL
Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144
Linux, x64, 64bit (optimized), MySQL Enterprise on Sep 7 2019 08:46:10
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
Starting at 2020-03-26 15:10:03
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Nov 22 16:42:41 UTC 2016, Release 3.10.0-514.el7.x86_64
Node: awen001.us.oracle.com
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 8062
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile ./dirdef/defgen.prm
sourcedb test01@localhost:3306,userid root,password ***
table test01.Mar01;
Retrieving definition for test01.Mar01.
Definitions generated for 1 table in ./dirdef/defgen.prm.
[mysql@awen001 ogg]$ exit
登出
[mysql@awen001 ogg]$ scp dirdef/defgen.prm oracle@192.168.1.22:/u01/app/
The authenticity of host '192.168.1.22 (192.168.1.22)' can't be established.
ECDSA key fingerprint is 41:d6:1f:36:5a:6b:be:d9:47:ee:aa:37:05:2b:de:8a.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.22' (ECDSA) to the list of known hosts.
oracle@192.168.1.22's password:
defgen.prm 100% 1198 1.2KB/s 00:00
[mysql@awen001 ogg]$
10. 目标端ogg配置
--创建OGG数据目录
mkdir -p /u01/app/ogg
--在目标端创建完OGG子目录以后，将defgen文件复制到指定位置
mv defgen.prm ogg/dirdef
--开启监听
lsnrctl status
--数据库开启GoldenGate参数
alter system set enable_goldengate_replication=true sid='*';
11. 目标端创建OGG管理用户和测试用户以及测试表
--创建OGG管理用户
create user ggt_admin identified by ggt_admin;
grant dba to ggt_admin;
--创建OGG测试用户
create user result01 identified by result01;
grant resource,connect to result01;
--在该用户下创建测试表
conn result01/result01
create table result01.Mar02(id int,name char(10));
12. 目标端安装OGG软件
选择11g OGG软件，下一步：
选择软件安装路径，继续：
配置无误，点击下一步：
13. 源端配置OGG诸进程，尚不用启动进程
[oracle@awen002 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 US-ASCII.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (awen002.us.oracle.com) 1> CREATE SUBDIRS
Creating subdirectories under current directory /u01/app/ogg
Parameter file /u01/app/ogg/dirprm: created.
Report file /u01/app/ogg/dirrpt: created.
Checkpoint file /u01/app/ogg/dirchk: created.
Process status files /u01/app/ogg/dirpcs: created.
SQL script files /u01/app/ogg/dirsql: created.
Database definitions files /u01/app/ogg/dirdef: created.
Extract data files /u01/app/ogg/dirdat: created.
Temporary files /u01/app/ogg/dirtmp: created.
Credential store files /u01/app/ogg/dircrd: created.
Masterkey wallet files /u01/app/ogg/dirwlt: created.
Dump files /u01/app/ogg/dirdmp: created.
GGSCI (awen002.us.oracle.com) 2> edit params ./GLOBALS
GGSCI (awen002.us.oracle.com) 3> view params ./GLOBALS
ggschema result01
enablemonitoring
checkpointtable result01.chkpt
GGSCI (awen002.us.oracle.com) 4> dblogin userid ggt_admin@192.168.1.22/prod,password ggt_admin
Successfully logged into database.
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 5> add checkpointtable
No checkpoint table specified. Using GLOBALS specification ggt_admin.chkpt...
Successfully created checkpoint table ggt_admin.chkpt.
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 6> edit params mgr
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 7> view params mgr
PORT 7810
dynamicportlist 7800-8000
autorestart extract *,waitminutes 2,resetminutes 5
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 8> start mgr
Manager started.
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 9> info mgr
Manager is running (IP port TCP:awen002.us.oracle.com.7810, Process ID 4657).
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 10> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
PMSRVR STOPPED
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 11> edit param rrggt1
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 12> view param rrggt1
replicat rrggt1 ---定义应用进程名称
sourcedefs ./dirdef/defgen.prm ---定义defgen文件
userid ggt_admin,password ggt_admin ---ogg库管理员账户密码
reperror default,discard ---定义出错以后进程的响应，一般可以定义为两种：
ABEND，即一旦出现错误即停止复制，此为缺省配置；
DISCARD，出现错误后继续复制，只是把错误的数据放到discard文件中；
discardfile ./dirrpt/rrggt1.dsc,append,megabytes 50 ---定义discardfile文件位置，文件路径必须为相对路径，如果处理中有记录出错会写入到此文件中，记录模式为追加，文件最大设置为50Mb
dynamicresolution ---动态解析表名
gettruncates ---开启捕获truncate操作，两端都需要开启
map test01.Mar01,target result01.Mar02; ---配置源表目标表映射关系
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 13> add replicat rrggt1,exttrail ./dirdat/e2
REPLICAT added.
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 14> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
PMSRVR STOPPED
REPLICAT STOPPED RRGGT1 00:00:00 00:00:03
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 15>
14. 按照顺序开启源端、目标端OGG进程
受限开启源端的EXTRACT进程、DATA PUMP进程
GGSCI (awen001.us.oracle.com) 5> start extract lxggs1
Sending START request to MANAGER ...
EXTRACT LXGGS1 starting
GGSCI (awen001.us.oracle.com) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED LPGGS1 00:00:00 01:20:00
EXTRACT RUNNING LXGGS1 01:20:44 00:00:05
GGSCI (awen001.us.oracle.com) 7> start extract lpggs1
Sending START request to MANAGER ...
EXTRACT LPGGS1 starting
GGSCI (awen001.us.oracle.com) 8>
GGSCI (awen001.us.oracle.com) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING LPGGS1 00:00:00 01:20:20
EXTRACT RUNNING LXGGS1 00:00:00 00:00:05
GGSCI (awen001.us.oracle.com) 9>
GGSCI (awen001.us.oracle.com) 9> info extract lxggs1
EXTRACT LXGGS1 Last Started 2020-03-26 16:28 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Process ID 8863
VAM Read Checkpoint 2020-03-26 15:08:12.172467
GGSCI (awen001.us.oracle.com) 10> info extract lpggs1
EXTRACT LPGGS1 Last Started 2020-03-26 16:29 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:01 ago)
Process ID 8871
Log Read Checkpoint File /home/mysql/ogg/dirdat/e2000000000
First Record RBA 1370
GGSCI (awen001.us.oracle.com) 11> stats ext lxggs1
Sending STATS request to EXTRACT LXGGS1 ...
No active extraction maps.
GGSCI (awen001.us.oracle.com) 12> stats ext lpggs1
Sending STATS request to EXTRACT LPGGS1 ...
No active extraction maps.
GGSCI (awen001.us.oracle.com) 13>
然后开启目标端REPLICATE进程
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 15> start replicat RRGGT1
Sending START request to MANAGER ...
REPLICAT RRGGT1 starting
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
JAGENT STOPPED
PMSRVR STOPPED
REPLICAT RUNNING RRGGT1 00:00:00 00:00:01
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 17> info REPLICAT RRGGT1
REPLICAT RRGGT1 Last Started 2020-03-26 16:36 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:02 ago)
Process ID 5104
Log Read Checkpoint File /u01/app/ogg/dirdat/e2000000000
First Record RBA 0
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 18> stats REPLICAT RRGGT1
Sending STATS request to REPLICAT RRGGT1 ...
No active replication maps.
15. 模拟源端数据库业务持续运行
OGG的单向配置比较简单，但实际生产过程很多业务要求不间断运行， 所以往创建的表中不断插入数据，简单模拟在业务不间断运行场景下OGG的配置，
并观察进程信息。
--创建每隔10秒向gss_admin库的Mar01表插入一条数据的procedure；
use test01select count(*) from Mar01;delimiter //
create procedure per2()
begin
declare id int;
set id=1;
while id < 1000 do
insert into Mar01(id,name) values(id,concat("fan",id));
select sleep(10);
set id=id+1;
end while;
end
//
call per2();
->//
16. 查看源端、目标端数据情况
MariaDB [test01]> select count(*) from Mar01;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
SQL> select count(*) from result01.Mar02;
COUNT(*)
----------
7
17. 查看目标端、远端进程信息
GGSCI (awen001.us.oracle.com) 13> info extract lxggs1
EXTRACT LXGGS1 Last Started 2020-03-26 16:28 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 8863
VAM Read Checkpoint 2020-03-26 16:45:36.000000
Log Number: 3
Record Offset: 4479
GGSCI (awen001.us.oracle.com) 14> info extract lpggs1
EXTRACT LPGGS1 Last Started 2020-03-26 16:29 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:07 ago)
Process ID 8871
Log Read Checkpoint File /home/mysql/ogg/dirdat/e2000000000
2020-03-26 16:45:36.000000 RBA 4762
GGSCI (awen001.us.oracle.com) 15> stats ext lxggs1
Sending STATS request to EXTRACT LXGGS1 ...
Start of Statistics at 2020-03-26 16:45:56.
Output to ./dirdat/e2:
Extracting from test01.Mar01 to test01.Mar01:
*** Total statistics since 2020-03-26 16:42:37 ***
Total inserts 20.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 20.00
*** Daily statistics since 2020-03-26 16:42:37 ***
Total inserts 20.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 20.00
*** Hourly statistics since 2020-03-26 16:42:37 ***
Total inserts 20.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 20.00
*** Latest statistics since 2020-03-26 16:42:37 ***
Total inserts 20.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 20.00
End of Statistics.
GGSCI (awen001.us.oracle.com) 16> stats ext lpggs1
Sending STATS request to EXTRACT LPGGS1 ...
Start of Statistics at 2020-03-26 16:46:01.
Output to /u01/app/ogg/dirdat/e2:
Extracting from test01.Mar01 to test01.Mar01:
*** Total statistics since 2020-03-26 16:42:40 ***
Total inserts 21.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 21.00
*** Daily statistics since 2020-03-26 16:42:40 ***
Total inserts 21.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 21.00
*** Hourly statistics since 2020-03-26 16:42:40 ***
Total inserts 21.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 21.00
*** Latest statistics since 2020-03-26 16:42:40 ***
Total inserts 21.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 21.00
End of Statistics.
------------------------------------------------------------------
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 19> info REPLICAT RRGGT1
REPLICAT RRGGT1 Last Started 2020-03-26 16:36 Status RUNNING
Checkpoint Lag 00:00:05 (updated 00:00:03 ago)
Process ID 5104
Log Read Checkpoint File /u01/app/ogg/dirdat/e2000000000
2020-03-26 16:46:14.531563 RBA 5475
GGSCI (awen002.us.oracle.com as ggt_admin@prod) 20> stats REPLICAT RRGGT1
Sending STATS request to REPLICAT RRGGT1 ...
Start of Statistics at 2020-03-26 16:46:27.
Replicating from test01.Mar01 to RESULT01.MAR02:
*** Total statistics since 2020-03-26 16:42:42 ***
Total inserts 23.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 23.00
*** Daily statistics since 2020-03-26 16:42:42 ***
Total inserts 23.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 23.00
*** Hourly statistics since 2020-03-26 16:42:42 ***
Total inserts 23.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 23.00
*** Latest statistics since 2020-03-26 16:42:42 ***
Total inserts 23.00
Total updates 0.00
Total deletes 0.00
Total upserts 0.00
Total discards 0.00
Total operations 23.00
End of Statistics.
18. 进行dml测试
对表Mar01进行inert、delete、update操作，最后truncate该表。
MariaDB [(none)]> select count(*) from test01.Mar01;
+----------+
| count(*) |
+----------+
| 32 |
+----------+
1 row in set (0.00 sec)
SQL> select count(*) from result01.Mar02;
COUNT(*)
----------
32
MariaDB [(none)]> insert into test01.Mar01 values (996,'YES');
Query OK, 1 row affected (0.00 sec)
SQL> select * from result01.Mar02 where id=996;
ID NAME
---------- --------------------
996 YES
MariaDB [(none)]> update test01.Mar01 set name='Yeap' where id=996;
Query OK, 1 rows affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SQL> select * from result01.Mar02 where id=996;
ID NAME
---------- --------------------
996 Yeap
View Code
MariaDB [(none)]> truncate test01.Mar01;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> select count(*) from test01.Mar01;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
SQL> select count(*) from result01.Mar02;
COUNT(*)
----------
0
至此OGG异构配置完成，测试结束。
-------------------------------------------------------------------------
