使用OGG 21.3远程实时互相同步Oracle 11.2.0.4（双主）
👉 本文共约2117个字，系统预计阅读时间或需8分钟。
类似文章
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 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 | -- 创建专用网络 docker network create --subnet=172.72.7.0/24 ora-network -- OGG机器 docker rm -f lhrogg213oracle docker run -d --name lhrogg213oracle -h lhrogg213oracle \ --net=ora-network --ip 172.72.7.16 \ -p 19391:3389 -p 19004:9004 -p 17809-17819:7809-7819 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/ogg213oracle: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 -- ora11ga docker rm -f ora11ga docker run -d --name ora11ga -h ora11ga \ -p 1514:1521 --net=ora-network --ip 172.72.7.14 \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init -- ora11gb docker rm -f ora11gb docker run -d --name ora11gb -h ora11gb \ -p 1515:1521 --net=ora-network --ip 172.72.7.15 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true \ lhrbest/oracle_11g_ee_lhr_11.2.0.4:2.0 init -- 数据库配置 1.开启数据库归档--如果没有开启 2.开启数据库级别附加日志--如果没有开始最小附加日志 3.开启强制日志--如果没有开启强制日志 4.设置ENABLE_GOLDENGATE_REPLICAT参数为TRUE 5.创建OGG用户包括包括源端用户、目标端用户以及OGG抽取用户 -- 2个库做配置 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; select name,supplemental_log_data_min , force_logging, log_mode from v$database; alter system set streams_pool_size = 128M; alter system set sga_max_size = 2g scope=spfile; alter system set sga_target = 2g scope=spfile; alter system set pga_aggregate_target=1g; startup force -- OGG管理用户 CREATE USER ogg identified by lhr; GRANT DBA to ogg; grant SELECT ANY DICTIONARY to ogg; GRANT EXECUTE ON SYS.DBMS_LOCK TO ogg; grant select any transaction to ogg; grant select any table to ogg; grant flashback any table to ogg; grant alter any table to ogg; exec dbms_goldengate_auth.grant_admin_privilege('OGG','*',TRUE); -- 业务用户 CREATE USER lhr identified by lhr; alter user lhr identified by lhr; GRANT DBA to lhr ; grant SELECT ANY DICTIONARY to lhr; GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr; -- 启动监听 vi /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora lsnrctl start lsnrctl status -- 源端数据初始化 /usr/local/swingbench/bin/oewizard -s -create -c /usr/local/swingbench/wizardconfigs/oewizard.xml -create \ -version 2.0 -cs //172.72.7.14/LHR11G -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 object_type,status,count(*) from dba_objects where owner='LHR' group by object_type,status; select sum(bytes)/1024/1024 from dba_segments where owner='LHR'; -- 检查键是否正确：https://www.xmmup.com/ogg-01296-biaoyouzhujianhuoweiyijiandanshirengranshiyongquanbulielaijiexixing.html -- 否则OGG启动后，会报错：OGG-01296、OGG-06439、OGG-01169 Encountered an update where all key columns for target table LHR.ORDER_ITEMS are not present. select owner, constraint_name, constraint_type, status, validated from dba_constraints where owner='LHR' and VALIDATED='NOT VALIDATED'; select 'alter table lhr.'||TABLE_NAME||' enable validate constraint '||CONSTRAINT_NAME||';' from dba_constraints where owner='LHR' and VALIDATED='NOT VALIDATED'; -- 删除外键 SELECT 'ALTER TABLE LHR.'|| D.TABLE_NAME ||' DROP constraint '|| D.CONSTRAINT_NAME||';' FROM DBA_constraints d where owner='LHR' and d.CONSTRAINT_TYPE='R'; select count(*) from LHR.ORDER_ITEMS union all select count(*) from LHR.LOGON union all select count(*) from LHR.CUSTOMERS union all select count(*) from LHR.ORDERS union all select count(*) from LHR.PRODUCT_DESCRIPTIONS union all select count(*) from LHR.ORDERENTRY_METADATA union all select count(*) from LHR.CARD_DETAILS union all select count(*) from LHR.PRODUCT_INFORMATION union all select count(*) from LHR.ADDRESSES union all select count(*) from LHR.WAREHOUSES union all select count(*) from LHR.INVENTORIES ; -- 解决OGG-02912 Patch 17030189 is required on your Oracle mining database for trail format RELEASE 12.2 or later find / -name prvtlmpg.plb sqlplus sys/lhr@172.72.7.14/lhr11g as sysdba @/ogg/prvtlmpg.plb sqlplus sys/lhr@172.72.7.15/lhr11g as sysdba @/ogg/prvtlmpg.plb Enter Integrated Capture mining user: OGG |
创建身份证明
1 2 3 4 5 6 7 | add credentialstore alter credentialstore add user ogg@172.72.7.14/LHR11G, password lhr alias ora11ga alter credentialstore add user ogg@172.72.7.15/LHR11G, password lhr alias ora11gb INFO CREDENTIALSTORE dblogin useridalias ora11ga sqlplus ogg/lhr@172.72.7.14/LHR11G |
结果：
1 2 3 4 5 6 7 8 9 10 11 | GGSCI (lhrogg213oracle) 2> INFO CREDENTIALSTORE Reading from credential store: Default domain: OracleGoldenGate Alias: ora11ga Userid: ogg@172.72.7.14/LHR11G Alias: ora11gb Userid: ogg@172.72.7.15/LHR11G |
源端添加SCHEMATRANDATA
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 | dblogin useridalias ora11ga ADD SCHEMATRANDATA LHR INFO SCHEMATRANDATA LHR GGSCI (lhrogg213oracle as ogg@LHR11G) 5> INFO SCHEMATRANDATA LHR 2022-06-25 09:35:29 INFO OGG-06480 Schema level supplemental logging, excluding non-validated keys, is enabled on schema "LHR". 2022-06-25 09:35:29 INFO OGG-01980 Schema level supplemental logging is enabled on schema "LHR" for all scheduling columns. 2022-06-25 09:35:29 INFO OGG-10462 Schema "LHR" have 11 prepared tables for instantiation. col TABLE_OWNER format a12 SELECT * FROM dba_capture_prepared_tables d where TABLE_OWNER='LHR'; TABLE_OWNER TABLE_NAME SCN TIMESTAMP SUPPLEME SUPPLEME SUPPLEME SUPPLEME ------------ ------------------------------ ---------- ------------------- -------- -------- -------- -------- LHR CUSTOMERS 1546665 2022-06-24 12:40:05 NO NO NO NO LHR ADDRESSES 1546659 2022-06-24 12:40:05 NO NO NO NO LHR CARD_DETAILS 1546662 2022-06-24 12:40:05 NO NO NO NO LHR WAREHOUSES 1546689 2022-06-24 12:40:05 NO NO NO NO LHR ORDER_ITEMS 1546680 2022-06-24 12:40:05 NO NO NO NO LHR ORDERS 1546677 2022-06-24 12:40:05 NO NO NO NO LHR INVENTORIES 1546668 2022-06-24 12:40:05 NO NO NO NO LHR PRODUCT_INFORMATION 1546686 2022-06-24 12:40:05 NO NO NO NO LHR LOGON 1546671 2022-06-24 12:40:05 NO NO NO NO LHR PRODUCT_DESCRIPTIONS 1546683 2022-06-24 12:40:05 NO NO NO NO LHR ORDERENTRY_METADATA 1546674 2022-06-24 12:40:05 NO NO NO NO 11 rows selected. -- select * from sys.streams$_prepare_object; |
源端和目标端创建心跳表（脉动表）
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | dblogin useridalias ora11ga dblogin useridalias ora11gb ADD HEARTBEATTABLE INFO HEARTBEATTABLE select * from ogg.gg_lag; select * from ogg.GG_LAG_HISTORY; OGG@lhrsdb> col tname format a20 OGG@lhrsdb> select * from tab; TNAME TABTYPE CLUSTERID -------------------- -------------------------- ---------- GG_HEARTBEAT TABLE GG_HEARTBEAT_HISTORY TABLE GG_HEARTBEAT_SEED TABLE GG_LAG VIEW GG_LAG_HISTORY VIEW |
配置ora11ga到ora11gb的实时同步
1 | rm -rf /ogg/dirdat/* |
创建extract进程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | ADD EXTRACT exta INTEGRATED TRANLOG BEGIN NOW ADD EXTTRAIL ./dirdat/ea EXTRACT exta dblogin useridalias ora11ga REGISTER EXTRACT exta DATABASE edit params exta EXTRACT exta USERIDALIAS ora11ga DDL INCLUDE MAPPED DDLOPTIONS REPORT TRANLOGOPTIONS EXCLUDETAG 99 EXTTRAIL ./dirdat/ea TABLE LHR.*; -- 启动mgr和exta start mgr start exta |
创建replicate进程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | ADD REPLICAT repb INTEGRATED EXTTRAIL ./dirdat/ea ,begin now edit params repb REPLICAT repb USERIDALIAS ora11gb DBOPTIONS ENABLE_INSTANTIATION_FILTERING DDL INCLUDE MAPPED DDLOPTIONS REPORT DBOPTIONS SETTAG 99 MAP lhr.*, TARGET lhr.*; -- 不启动repb |
impdp+network导入目标端数据
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 | -- 让源端一直产生事务 /usr/local/swingbench/bin/charbench -c /usr/local/swingbench/configs/SOE_Server_Side_V2.xml \ -u lhr -p lhr -cs //172.72.7.14/LHR11G -dt thin -uc 10 \ -a -v "users,tpm,tps,dml,cpu" \ -rr 5 -rt "00:30" -min 50 -max 50 -r "/tmp/test_lhrsdb.xml" -- 目标端 create directory d1 as '/home/oracle/'; grant all on directory d1 to public; create public database link DBL connect to system identified by lhr using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = 172.72.7.14)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = LHR11G )))'; impdp lhr/lhr@127.0.0.1/LHR11G directory=D1 \ NETWORK_LINK=DBL exclude=statistics parallel=16 \ cluster=no schemas=LHR TABLE_EXISTS_ACTION=REPLACE -- 也可以使用expdp+impdp expdp lhr/lhr directory=DATA_PUMP_DIR dumpfile=LHR.dmp SCHEMAS=LHR scp /opt/oracle/admin/lhrsdb/dpdump/LHR.dmp 172.72.7.5:/home/oracle/ conn lhr/lhr@127.0.0.1/pdb2 create directory d1 as '/home/oracle/'; grant all on directory d1 to public; impdp lhr/lhr@127.0.0.1/pdb2 DIRECTORY=D1 schemas=LHR dumpfile=LHR.dmp TABLE_EXISTS_ACTION=REPLACE |
导入完成后，查看目标库：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | alter PACKAGE "LHR"."ORDERENTRY" compile; alter PACKAGE "LHR"."ORDERENTRY" compile body; col SOURCE_OBJECT_NAME format a30 select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects; SOURCE_OBJECT_NAME INSTANTIATION_SCN IGNORE_SCN ------------------------------ ----------------- ---------- WAREHOUSES 2159700 0 ORDERENTRY_METADATA 2159687 0 PRODUCT_INFORMATION 2159670 0 PRODUCT_DESCRIPTIONS 2159652 0 ORDER_ITEMS 2159623 0 ORDERS 2159612 0 LOGON 2159584 0 CUSTOMERS 2159557 0 CARD_DETAILS 2159551 0 ADDRESSES 2159539 0 INVENTORIES 2158976 0 11 rows selected. |
后续启用replicate进程后，会自动从这些scn进行应用。
启用replicate进程
在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | LHR@ora12c> select count(*) from "LHR"."ORDERS"; COUNT(*) ---------- 13019 LHR@ora19c> select count(*) from "LHR"."ORDERS"; COUNT(*) ---------- 4562 -- 启动repb start repb -- 查询延迟 col incoming_path format a30 SELECT d.incoming_path,d.incoming_heartbeat_age FROM ogg.gg_lag d; |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
测试DDL和DML同步
在Oracle 11g a端建表和插入数据：
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 | create table lhr.t1 (id number primary key,name varchar2(255)); insert into lhr.t1 select object_id,object_name from dba_objects where object_id<=20; commit; select count(*) from lhr.t1; LHR@lhr11gb> select count(8) from lhr.t1; COUNT(8) ---------- 19 SYS@LHR11G> TRUNCATE TABLE LHR.T1; Table truncated. GGSCI (lhrogg213oracle) 26> stats repb, table lhr.t1, TOTAL Sending STATS request to Replicat group REPB ... Start of statistics at 2022-06-27 11:27:20. Integrated Replicat statistics: Total transactions 525.00 Redirected 0.00 Replicated procedures 0.00 DDL operations 2.00 Stored procedures 0.00 Datatype functionality 0.00 Operation type functionality 0.00 Event actions 0.00 Direct transactions ratio 0.38% DDL replication statistics: *** Total statistics since replicat started *** Operations 2.00 Mapped operations 2.00 Unmapped operations 0.00 Other operations 0.00 Excluded operations 0.00 Errors 0.00 Retried errors 0.00 Discarded errors 0.00 Ignored errors 0.00 Replicating from LHR.T1 to LHR.T1: *** Total statistics since 2022-06-27 11:25:19 *** Total inserts 19.00 Total updates 0.00 Total deletes 0.00 Total upserts 0.00 Total discards 0.00 Total operations 19.00 End of statistics. |
可见，DDL和DML同步都已经完成，truncate语句也支持同步！
通过restful查看OGG状态
1 2 3 4 5 6 7 | EDIT PARAMS ./GLOBALS ENABLEMONITORING start PMSRVR http://127.0.0.1:9004/groups |
配置ora11gb到ora11ga的实时同步
创建extract进程
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | ADD EXTRACT extb INTEGRATED TRANLOG BEGIN NOW ADD EXTTRAIL ./dirdat/eb EXTRACT extb dblogin useridalias ora11gb REGISTER EXTRACT extb DATABASE edit params extb EXTRACT extb USERIDALIAS ora11gb DDL INCLUDE MAPPED DDLOPTIONS REPORT TRANLOGOPTIONS EXCLUDETAG 99 EXTTRAIL ./dirdat/eb TABLE LHR.*; start extb |
创建replicate进程
1 2 3 4 5 6 7 8 9 10 11 12 13 | ADD REPLICAT repa INTEGRATED EXTTRAIL ./dirdat/eb edit params repa REPLICAT repa USERIDALIAS ora11ga DDL INCLUDE MAPPED DDLOPTIONS REPORT DBOPTIONS SETTAG 99 MAP lhr.*, TARGET lhr.*; start repa |
注意：反向同步的时候，不能再加“DBOPTIONS ENABLE_INSTANTIATION_FILTERING”参数了。
测试DDL和DML同步
在Oracle 11g b端操作：
1 2 3 4 | create table lhr.t2 (id number primary key,name varchar2(255)); insert into lhr.t2 select object_id,object_name from dba_objects where object_id<=20; commit; select count(8) from lhr.t2; |
在a端可以看到新建的表和数据。
1 2 3 4 5 6 7 8 9 10 11 | GGSCI (lhrogg213oracle as ogg@LHR11G) 218> info all Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING JAGENT STOPPED PMSRVR RUNNING EXTRACT RUNNING EXTA 00:00:24 00:00:03 EXTRACT RUNNING EXTB 00:00:02 00:00:02 REPLICAT RUNNING REPA 00:00:00 00:00:03 REPLICAT RUNNING REPB 00:00:00 00:00:09 |
可见，DDL和DML同步都已经完成！
反向同步也正常，可以作为回退方案！！！
OGG健康巡检
参考：https://www.xmmup.com/ogg-for-oraclejiankangxunjianguanfangjiaoben.html
1 2 3 4 | cd /ogg/healthcheck/ sqlplus sys/lhr@172.72.7.14/LHR11G as sysdba @ogghc_install.sql @ogghc_run.sql |
其它SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT * FROM dba_apply_error; SELECT * FROM dba_apply_progress; SELECT * FROM dba_capture; SELECT * FROM dba_capture_prepared_schemas; -- 源端 SELECT * FROM dba_capture_prepared_tables d where TABLE_OWNER='LHR'; -- 目标端scn select source_object_name, instantiation_scn, ignore_scn from dba_apply_instantiated_objects; -- 捕获进程对数据库中表的支持级别的信息 SELECT * FROM dba_goldengate_support_mode d where d.owner='LHR'; -- 显示所有没有主索引和非空唯一索引的表 SELECT * 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
解决：
1 2 3 4 5 6 7 | find / -name prvtlmpg.plb sqlplus sys/lhr@172.72.7.14/lhr11g as sysdba SQL> @/ogg/prvtlmpg.plb Enter Integrated Capture mining user: OGG |
总结
1、OGG 21.3可以远程捕获和投递Oracle 11.2.0.4
2、建议使用最新版的OGG，配置远程捕获和远程投递
3、注意反向同步的时候，不能再加“DBOPTIONS ENABLE_INSTANTIATION_FILTERING”参数了
4、配置extract和replicate进程的时候，注意查看相关的告警信息，例如trail文件是否已存在
