类似文章
OGG有传统的经典架构，也有最新的微服务，2个都可以远程捕获和应用数据，对数据库服务器是0侵入，而传统的经典架构是纯命令行模式，最新的微服务架构是图形化界面操作，几乎所有操作都可以在界面进行。相关文章可以参考：
使用OGG for MySQL微服务快速双向同步RDS数据库：https://www.xmmup.com/shiyongoggweifuwukuaisushuangxiangtongburdsshujuku.html
OGG用于跨云RDS for MySQL之间配置双主实时同步–OGG远程捕获和投递：https://www.xmmup.com/oggyongyukuayunrdszhijianpeizhishuangzhushishitongbuyuanchengbuhuohetoudi.html
otter用于跨云RDS for mysql之间配置双主实时同步：https://www.xmmup.com/otteryongyukuayunrdszhijianpeizhishuangzhushishitongbu.html
OGG用于PG数据库之间双主实时同步（RDS for PG亦可）–OGG远程捕获和投递：https://www.xmmup.com/oggyongyupgshujukuzhijianshuangzhushishitongburds-for-pgyikeoggyuanchengbuhuohetoudi.html
使用OGG for PG微服务快速双向同步RDS数据库（双主）：https://www.xmmup.com/shiyongogg-for-pgweifuwukuaisushuangxiangtongburdsshujukushuangzhu.html
使用阿里的开源工具otter，这个方案经过验证是可行的。阿里数据同步工具Otter和Canal简介请参考：https://www.xmmup.com/alishujutongbugongjuotterhecanaljianjie.html
使用OGG for Oracle微服务双向同步Oracle数据库搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-oracleweifuwushuangxiangtongbuoracleshujukuhanddl.html
使用OGG for mysql微服务搭建双主架构（含DDL）：https://www.xmmup.com/shiyongogg-for-mysqlweifuwudajianshuangzhujiagouhanddl.html
使用数据泵基于flashback_scn+OGG微服务零停机迁移12c到19c：https://www.xmmup.com/shiyongshujubengjiyuflashback_scnoggweifuwulingtingjiqianyi12cdao19c.html
使用数据泵+OGG微服务新参数ENABLE_INSTANTIATION_FILTERING零停机迁移12c到19c ：https://www.xmmup.com/shiyongshujubengoggweifuwuxincanshuenable_instantiation_filteringlingtingjiqianyi12cdao19c.html
使用数据泵+OGG远程捕获投递服务
1-- 创建专用网络
2docker network create --subnet=172.72.7.0/24 ora-network
3
4
5-- OGG机器
6docker rm -f lhrogg213oracle
7docker run -d --name lhrogg213oracle -h lhrogg213oracle \
8 --net=ora-network --ip 172.72.7.16 \
9 -p 19391:3389 -p 17809-17819:7809-7819 \
10 -v /sys/fs/cgroup:/sys/fs/cgroup \
11 --privileged=true lhrbest/ogg213oracle:v1.0 \
12 /usr/sbin/init
13
14
15-- oracle 压测工具
16docker pull lhrbest/lhrdbbench:1.0
17
18docker rm -f lhrdbbench
19docker run -d --name lhrdbbench -h lhrdbbench \
20 --net=ora-network --ip 172.72.7.26 \
21 -v /sys/fs/cgroup:/sys/fs/cgroup \
22 --privileged=true lhrbest/lhrdbbench:1.0 \
23 /usr/sbin/init
24
25
26
27
28-- ora11ga
29docker rm -f ora11ga
30docker run -d --name ora11ga -h ora11ga \
31 -p 1514:1521 --net=ora-network --ip 172.72.7.14 \
32 --privileged=true \
33 lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
34
35
36-- ora11gb
37docker rm -f ora11gb
38docker run -d --name ora11gb -h ora11gb \
39 -p 1515:1521 --net=ora-network --ip 172.72.7.15 \
40 -v /sys/fs/cgroup:/sys/fs/cgroup \
41 --privileged=true \
42 lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init
43
44
45
46 -- 数据库配置
471.开启数据库归档--如果没有开启
482.开启数据库级别附加日志--如果没有开始最小附加日志
493.开启强制日志--如果没有开启强制日志
504.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE
515.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户
52
53
54
55-- 2个库做配置
56alter database add supplemental log data;
57alter database add supplemental log data (all) columns;
58alter database force logging;
59alter system set enable_goldengate_replication=TRUE;
60
61select name,supplemental_log_data_min , force_logging, log_mode from v$database;
62
63
64alter system set streams_pool_size = 128M;
65alter system set sga_max_size = 2g scope=spfile;
66alter system set sga_target = 2g scope=spfile;
67alter system set pga_aggregate_target=1g;
68startup force
69
70
71-- OGG管理用户
72CREATE USER ogg identified by lhr;
73GRANT DBA to ogg;
74grant SELECT ANY DICTIONARY to ogg;
75GRANT EXECUTE ON SYS.DBMS_LOCK TO ogg;
76grant select any transaction to ogg;
77grant select any table to ogg;
78grant flashback any table to ogg;
79grant alter any table to ogg;
80
81exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE);
82
83
84-- 业务用户
85CREATE USER lhr identified by lhr;
86GRANT DBA to lhr ;
87grant SELECT ANY DICTIONARY to lhr;
88GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr;
89
90
91
92-- 启动监听
93lsnrctl start
94lsnrctl status
95
96
97
98-- 源端数据初始化
99/usr/local/swingbench/bin/oewizard -s -create -c /usr/local/swingbench/wizardconfigs/oewizard.xml -create \
100-version 2.0 -cs //172.72.7.4/lhrsdb -dba "sys as sysdba" -dbap lhr -dt thin \
101-ts users -u lhr -p lhr -allindexes -scale 0.001 -tc 16 -v -cl
102
103
104col TABLE_NAME format a30
105SELECT a.table_name,a.num_rows FROM dba_tables a where a.OWNER='LHR' ;
106select object_type,count(*) from dba_objects where owner='LHR' group by object_type;
107select object_type,status,count(*) from dba_objects where owner='LHR' group by object_type,status;
108select sum(bytes)/1024/1024 from dba_segments where owner='LHR';
109
110-- 检查键是否正确：https://www.xmmup.com/ogg-01296-biaoyouzhujianhuoweiyijiandanshirengranshiyongquanbulielaijiexixing.html
111-- 否则OGG启动后，会报错：OGG-01296、OGG-06439、OGG-01169 Encountered an update where all key columns for target table LHR.ORDER_ITEMS are not present.
112select owner, constraint_name, constraint_type, status, validated
113from dba_constraints
114where owner='LHR'
115and VALIDATED='NOT VALIDATED';
116
117select 'alter table lhr.'||TABLE_NAME||' enable validate constraint '||CONSTRAINT_NAME||';'
118from dba_constraints
119where owner='LHR'
120and VALIDATED='NOT VALIDATED';
121
122
123-- 删除外键
124SELECT 'ALTER TABLE LHR.'|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';'
125FROM DBA_constraints d where owner='LHR' and d.CONSTRAINT_TYPE='R';
126
127
128select count(*) from LHR.ORDER_ITEMS
129union all
130select count(*) from LHR.LOGON
131union all
132select count(*) from LHR.CUSTOMERS
133union all
134select count(*) from LHR.ORDERS
135union all
136select count(*) from LHR.PRODUCT_DESCRIPTIONS
137union all
138select count(*) from LHR.ORDERENTRY_METADATA
139union all
140select count(*) from LHR.CARD_DETAILS
141union all
142select count(*) from LHR.PRODUCT_INFORMATION
143union all
144select count(*) from LHR.ADDRESSES
145union all
146select count(*) from LHR.WAREHOUSES
147union all
148select count(*) from LHR.INVENTORIES
149;
创建身份证明
1add credentialstore
2alter credentialstore add user ogg@172.72.7.14/LHR11G, password lhr alias ora11ga
3alter credentialstore add user ogg@172.72.7.15/LHR11G, password lhr alias ora11gb
4INFO CREDENTIALSTORE
5
6dblogin useridalias ora11ga
7sqlplus ogg/lhr@172.72.7.14/LHR11G
结果：
1GGSCI (lhrogg213oracle) 2> INFO CREDENTIALSTORE
2
3Reading from credential store:
4
5Default domain: OracleGoldenGate
6
7 Alias: ora11ga
8 Userid: ogg@172.72.7.14/LHR11G
9
10 Alias: ora11gb
11 Userid: ogg@172.72.7.15/LHR11G
源端添加SCHEMATRANDATA
1dblogin useridalias ora11ga
2ADD SCHEMATRANDATA LHR
3INFO SCHEMATRANDATA LHR
4
5GGSCI (lhrogg213oracle as ogg@LHR11G) 5> INFO SCHEMATRANDATA LHR
6
72022-06-25 09:35:29 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "LHR".
8
92022-06-25 09:35:29 INFO OGG-01980 Schema level supplemental logging is enabled on schema "LHR" for all scheduling columns.
10
112022-06-25 09:35:29 INFO OGG-10462 Schema "LHR" have 11 prepared tables for instantiation.
12
13
14
15
16col TABLE_OWNER format a12
17SELECT * FROM dba_capture_prepared_tables d where TABLE_OWNER='LHR';
18
19TABLE_OWNER TABLE_NAME SCN TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME
20------------ ------------------------------ ---------- ------------------- -------- -------- -------- --------
21LHR CUSTOMERS 1546665 2022-06-24 12:40:05 NO NO NO NO
22LHR ADDRESSES 1546659 2022-06-24 12:40:05 NO NO NO NO
23LHR CARD_DETAILS 1546662 2022-06-24 12:40:05 NO NO NO NO
24LHR WAREHOUSES 1546689 2022-06-24 12:40:05 NO NO NO NO
25LHR ORDER_ITEMS 1546680 2022-06-24 12:40:05 NO NO NO NO
26LHR ORDERS 1546677 2022-06-24 12:40:05 NO NO NO NO
27LHR INVENTORIES 1546668 2022-06-24 12:40:05 NO NO NO NO
28LHR PRODUCT_INFORMATION 1546686 2022-06-24 12:40:05 NO NO NO NO
29LHR LOGON 1546671 2022-06-24 12:40:05 NO NO NO NO
30LHR PRODUCT_DESCRIPTIONS 1546683 2022-06-24 12:40:05 NO NO NO NO
31LHR ORDERENTRY_METADATA 1546674 2022-06-24 12:40:05 NO NO NO NO
32
3311 rows selected.
34-- select * from sys.streams$_prepare_object;
源端和目标端创建心跳表（脉动表）
1dblogin useridalias ora11ga
2dblogin useridalias ora11gb
3ADD HEARTBEATTABLE
4INFO HEARTBEATTABLE
5
6select * from ogg.gg_lag;
7select * from ogg.GG_LAG_HISTORY;
8
9OGG@lhrsdb> col tname format a20
10OGG@lhrsdb> select * from tab;
11
12TNAME TABTYPE CLUSTERID
13-------------------- -------------------------- ----------
14GG_HEARTBEAT TABLE
15GG_HEARTBEAT_HISTORY TABLE
16GG_HEARTBEAT_SEED TABLE
17GG_LAG VIEW
18GG_LAG_HISTORY VIEW
配置ora11ga到ora11gb的实时同步
创建extract进程
1ADD EXTRACT exta INTEGRATED TRANLOG BEGIN NOW
2ADD EXTTRAIL ./dirdat/ea EXTRACT exta
3
4dblogin useridalias ora11ga
5REGISTER EXTRACT exta DATABASE
6
7edit params exta
8EXTRACT exta
9USERIDALIAS ora11ga
10DDL INCLUDE MAPPED
11DDLOPTIONS REPORT
12TRANLOGOPTIONS EXCLUDETAG 99
13EXTTRAIL ./dirdat/ea
14TABLE LHR.*;
创建replicate进程
1ADD REPLICAT repb INTEGRATED EXTTRAIL ./dirdat/ea
2
3
4edit params repb
5
6REPLICAT repb
7USERIDALIAS ora11gb
8DBOPTIONS ENABLE_INSTANTIATION_FILTERING
9DDL INCLUDE MAPPED
10DDLOPTIONS REPORT
11DBOPTIONS SETTAG 99
12MAP lhr.*, TARGET lhr.*;
impdp+network导入目标端数据
1-- 让源端一直产生事务
2/usr/local/swingbench/bin/charbench -c /usr/local/swingbench/configs/SOE_Server_Side_V2.xml \
3-u lhr -p lhr -cs //172.72.7.14/LHR11G -dt thin -uc 10 \
4-a -v "users,tpm,tps,dml,cpu" \
5-rr 5 -rt "00:30" -min 50 -max 50 -r "/tmp/test_lhrsdb.xml"
6
7
8-- 目标端
9create directory d1 as '/home/oracle/';
10grant all on directory d1 to public;
11create public database link DBL
12connect to system identified by lhr
13 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
14(HOST = 172.72.7.14)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = LHR11G )))';
15
16
17impdp lhr/lhr@127.0.0.1/LHR11G directory=D1 \
18NETWORK_LINK=DBL exclude=statistics parallel=16 \
19cluster=no schemas=LHR TABLE_EXISTS_ACTION=REPLACE
20
21
22
23-- 也可以使用expdp+impdp
24expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=LHR.dmp SCHEMAS=LHR
25scp /opt/oracle/admin/lhrsdb/dpdump/LHR.dmp 172.72.7.5:/home/oracle/
26
27conn lhr/lhr@127.0.0.1/pdb2
28create directory d1 as '/home/oracle/';
29grant all on directory d1 to public;
30impdp lhr/lhr@127.0.0.1/pdb2 DIRECTORY=D1 schemas=LHR dumpfile=LHR.dmp TABLE_EXISTS_ACTION=REPLACE
导入完成后，查看目标库：
1alter PACKAGE "LHR"."ORDERENTRY" compile;
2alter PACKAGE "LHR"."ORDERENTRY" compile body;
3
4
5col SOURCE_OBJECT_NAME format a30
6select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects;
7
8SOURCE_OBJECT_NAME INSTANTIATION_SCN IGNORE_SCN
9------------------------------ ----------------- ----------
10WAREHOUSES 2159700 0
11ORDERENTRY_METADATA 2159687 0
12PRODUCT_INFORMATION 2159670 0
13PRODUCT_DESCRIPTIONS 2159652 0
14ORDER_ITEMS 2159623 0
15ORDERS 2159612 0
16LOGON 2159584 0
17CUSTOMERS 2159557 0
18CARD_DETAILS 2159551 0
19ADDRESSES 2159539 0
20INVENTORIES 2158976 0
21
2211 rows selected.
后续启用replicate进程后，会自动从这些scn进行应用。
启用replicate进程
在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。
1LHR@ora12c> select count(*) from "LHR"."ORDERS";
2
3 COUNT(*)
4----------
5 13019
6
7LHR@ora19c> select count(*) from "LHR"."ORDERS";
8
9 COUNT(*)
10----------
11 4562
12
13
14-- 查询延迟
15col incoming_path format a30
16SELECT d.incoming_path,d.incoming_heartbeat_age FROM ogg.gg_lag d;
测试DDL和DML同步
在Oracle 11g a端建表和插入数据：
1create table lhr.t1 (id number primary key,name varchar2(255));
2insert into lhr.t1 select object_id,object_name from dba_objects where object_id<=20;
3commit;
4select count(*) from lhr.t1;
5
6
7LHR@lhrsdb> select count(8) from lhr.t1;
8
9 COUNT(8)
10----------
11 19
可见，DDL和DML同步都已经完成！
通过restful查看OGG状态
1EDIT PARAMS ./GLOBALS
2ENABLEMONITORING
3
4start PMSRVR
5
6http://127.0.0.1:9004/groups
配置ora11gb到ora11ga的实时同步
创建extract进程
1ADD EXTRACT extb INTEGRATED TRANLOG BEGIN NOW
2ADD EXTTRAIL ./dirdat/eb EXTRACT extb
3
4dblogin useridalias ora11gb
5REGISTER EXTRACT extb DATABASE
6
7edit params extb
8EXTRACT extb
9USERIDALIAS ora11gb
10DDL INCLUDE MAPPED
11DDLOPTIONS REPORT
12TRANLOGOPTIONS EXCLUDETAG 99
13EXTTRAIL ./dirdat/eb
14TABLE LHR.*;
创建replicate进程
1ADD REPLICAT repa INTEGRATED EXTTRAIL ./dirdat/eb
2
3
4edit params repa
5
6REPLICAT repa
7USERIDALIAS ora11ga
8DDL INCLUDE MAPPED
9DDLOPTIONS REPORT
10DBOPTIONS SETTAG 99
11MAP lhr.*, TARGET lhr.*;
注意：反向同步的时候，不能再加“DBOPTIONS ENABLE_INSTANTIATION_FILTERING”参数了。
测试DDL和DML同步
在Oracle 11g b端操作：
1create table lhr.t2 (id number primary key,name varchar2(255));
2insert into lhr.t2 select object_id,object_name from dba_objects where object_id<=20;
3commit;
4select count(8) from lhr.t2;
在a端可以看到新建的表和数据。
1GGSCI (lhrogg213oracle as ogg@LHR11G) 218> info all
2
3Program Status Group Lag at Chkpt Time Since Chkpt
4
5MANAGER RUNNING
6JAGENT STOPPED
7PMSRVR RUNNING
8EXTRACT RUNNING EXTA 00:00:24 00:00:03
9EXTRACT RUNNING EXTB 00:00:02 00:00:02
10REPLICAT RUNNING REPA 00:00:00 00:00:03
11REPLICAT RUNNING REPB 00:00:00 00:00:09
可见，DDL和DML同步都已经完成！
反向同步也正常，可以作为回退方案！！！
OGG健康巡检
参考：https://www.xmmup.com/ogg-for-oraclejiankangxunjianguanfangjiaoben.html
1cd /ogg/healthcheck/
2sqlplus sys/lhr@172.72.7.14/LHR11G as sysdba
3@ogghc_install.sql
4@ogghc_run.sql
其它SQL
1SELECT * FROM dba_apply_error;
2SELECT * FROM dba_apply_progress;
3
4SELECT * FROM dba_capture;
5SELECT * FROM dba_capture_prepared_schemas;
6
7
8-- 源端
9SELECT * FROM dba_capture_prepared_tables d where TABLE_OWNER='LHR';
10
11-- 目标端scn
12select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects;
13
14
15
16-- 捕获进程对数据库中表的支持级别的信息
17SELECT * FROM dba_goldengate_support_mode d where d.owner='LHR';
18
19-- 显示所有没有主索引和非空唯一索引的表
20SELECT * FROM DBA_GOLDENGATE_NOT_UNIQUE d where d.owner='LHR';
报错 OGG-02912
OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later
解决：
1find / -name prvtlmpg.plb
2sqlplus sys/lhr@172.72.7.14/lhr11g as sysdba
3
4SQL> @/ogg/prvtlmpg.plb
5
6Enter Integrated Capture mining user: OGG
总结
1、OGG 21.3可以远程捕获和投递Oracle 11.2.0.4
2、建议使用最新版的OGG，配置远程捕获和远程投递
3、注意反向同步的时候，不能再加“DBOPTIONS ENABLE_INSTANTIATION_FILTERING”参数了。
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
