合 使用OGG 21.3远程实时互相同步Oracle 11.2.0.4(双主)
类似文章
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 |