合 使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）
👉 本文共约3567个字，系统预计阅读时间或需14分钟。
目录
类似文章
OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。相关文章可以参考：
- 使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.dbaup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
- OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.dbaup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
- otter用于跨云RDS for mysql之间配置双主实时同步：https://www.dbaup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
- OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.dbaup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
- 使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.dbaup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
使用OGG for Oracle微服务
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | -- 创建专用网络 docker network create --subnet=172.72.7.0/24 ora-network -- OGG微服务 docker pull lhrbest/ogg213maoracle:v1.0 docker rm -f lhrogg213maoracle docker run -d --name lhrogg213maoracle -h lhrogg213maoracle \ --net=ora-network --ip 172.72.7.25 \ -p 9391:3389 -p 29000-29005:9000-9005 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/ogg213maoracle:v1.0 \ /usr/sbin/init -- oracle 压测工具 docker pull lhrbest/lhrdbbench:1.0 docker rm -f lhrdbbench docker run -d --name lhrdbbench -h lhrdbbench \ --net=ora-network --ip 172.72.7.26 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrdbbench:1.0 \ /usr/sbin/init -- ora19c docker rm -f ora19c docker run -d --name ora19c -h ora19c \ -p 1529:1521 --net=ora-network --ip 172.72.7.20 \ --privileged=true \ lhrbest/oracle19clhr_rpm_db_12.2.0.3:2.0 init -- ora21c docker rm -f ora21c docker run -d --name ora21c -h ora21c \ -p 1531:1521 --net=ora-network --ip 172.72.7.21 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/oracle21c_ee_db_21.3.0.0 \ /usr/sbin/init -- 数据库配置 1.开启数据库归档--如果没有开启 2.开启数据库级别附加日志--如果没有开始最小附加日志 3.开启强制日志--如果没有开启强制日志 4.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE 5.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户 select name,supplemental_log_data_min , force_logging, log_mode from v$database; alter database add supplemental log data; alter database add supplemental log data (all) columns; alter database force logging; alter system set enable_goldengate_replication=TRUE; alter system set streams_pool_size = 50M scope=spfile; CREATE USER ogg identified by lhr; GRANT DBA to ogg; grant SELECT ANY DICTIONARY to ogg; GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr; -- pdb CREATE USER ogg identified by lhr container=all; GRANT DBA to ogg container=all; GRANT SELECT ANY DICTIONARY to ogg container=all; GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr container=all; exec dbms_goldengate_auth.grant_admin_privilege('OGG',PRIVILEGE_TYPE=>'*',GRANT_SELECT_PRIVILEGES=>true); |
访问：http://192.168.66.35:29000 ，用户名：oggadmin，密码：lhr
创建身份证明
或直接访问：http://192.168.66.35:29001/
创建检查点表
添加trandata
添加脉动表
配置ora19c到ora21c的实时同步
创建extract进程
1 2 3 4 5 6 7 | extract ext19c USERIDALIAS ora19c, DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT TRANLOGOPTIONS EXCLUDETAG 99 exttrail ./dirdat/e1 table lhr.*; |
创建replicate进程
1 2 3 4 5 6 | REPLICAT rep19c USERIDALIAS ora21c DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT DBOPTIONS SETTAG 99 MAP lhr.*, TARGET lhr.*; |
测试同步
在Oracle 19c端建表和插入数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | create table t1 (id number primary key,name varchar2(255)); insert into t1 select object_id,object_name from dba_objects where object_id<=20; commit; select count(8) from t1; LHR@lhrsdb> create table t1 (id number primary key,name varchar2(255)); Table created. LHR@lhrsdb> insert into t1 select object_id,object_name from dba_objects where object_id<=20; 19 rows created. LHR@lhrsdb> commit; Commit complete. LHR@lhrsdb> select count(8) from t1; COUNT(8) ---------- 19 |
从源端查看：
查看目标端：
1 2 3 4 5 | LHR@127.0.0.1/pdb2> select count(8) from t1; COUNT(8) ---------- 19 |
可见，DDL和DML同步都已经完成！
配置ora21c到ora19c的实时同步
创建extract进程
1 2 3 4 5 6 7 | extract ext21 USERIDALIAS ora21c, DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT TRANLOGOPTIONS EXCLUDETAG 99 exttrail ./dirdat/e2 table lhr.*; |
创建replicate进程
1 2 3 4 5 6 | REPLICAT rep21 USERIDALIAS ora19c DOMAIN OGGMA DDL INCLUDE MAPPED DDLOPTIONS REPORT DBOPTIONS SETTAG 99 MAP lhr.*, TARGET lhr.*; |
测试同步
在Oracle 21c操作：
1 2 3 4 | create table t2 (id number primary key,name varchar2(255)); insert into t2 select object_id,object_name from dba_objects where object_id<=20; commit; select count(8) from t2; |
可见，DDL和DML同步都已经完成！
配置完成后的效果
简单测试以下DDL语句类型：
1 2 3 4 5 | create table drop table CREATE INDEX CTAS truncate |
都没有问题，其它类型没有测试！
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | [oracle@lhrogg213maoracle ~]$ adminclient Oracle GoldenGate Administration Client for Oracle Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047 Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved. Oracle Linux 7, x64, 64bit (optimized) on Jul 28 2021 12:32:46 Operating system character set identified as UTF-8. OGG (not connected) 1> CONNECT http://127.0.0.1:9000 deployment deploy213 as oggadmin password lhr OGG (http://127.0.0.1:9000 deploy213) 2> info all Program Status Group Type Lag at Chkpt Time Since Chkpt ADMINSRVR RUNNING DISTSRVR RUNNING PMSRVR RUNNING RECVSRVR RUNNING EXTRACT RUNNING EXT19C INTEGRATED 00:00:00 00:00:03 EXTRACT RUNNING EXT21 INTEGRATED 00:00:00 00:00:07 REPLICAT RUNNING REP19C INTEGRATED 00:00:00 00:00:06 REPLICAT RUNNING REP21 INTEGRATED 00:00:00 00:00:04 |
压测同步并查看检测数据
ora19c造数据：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | /usr/local/swingbench/bin/oewizard -s -create -c /usr/local/swingbench/wizardconfigs/oewizard.xml -create \ -version 2.0 -cs //172.72.7.20/lhrsdb -dba "sys as sysdba" -dbap lhr -dt thin \ -ts users -u lhr -p lhr -allindexes -scale 0.001 -tc 16 -v -cl col TABLE_NAME format a30 SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' ; select object_type,count(*) from dba_objects where owner='LHR' group by object_type; select sum(bytes)/1024/1024 from dba_segments where owner='LHR'; LHR@lhrsdb> SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' AND NUM_ROWS>=20 ; TABLE_NAME NUM_ROWS ------------------------------ ---------- CUSTOMERS 1000 ADDRESSES 1500 CARD_DETAILS 1500 WAREHOUSES 1000 ORDER_ITEMS 7110 ORDERS 1430 INVENTORIES 903126 PRODUCT_INFORMATION 1000 LOGON 2383 PRODUCT_DESCRIPTIONS 1000 ORDERENTRY_METADATA 0 |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
能不能出个传统安装方式的教程
参考：https://www.xmmup.com/dbbao63xiangmufenxiangshiyongora2pgqianyioracle19cdaopg13bingshiyongoggpeizhishi.html/3#421_yuan_duan_an_zhuangOGG_ruan_jian
这个教程感觉源端跟目标端是分开的，如果像本文那样的架构，独立部署，实现远程抽取和投递，传统方式怎么安装，并且源和目标端的数据库版本还不一样
一样的