类似文章
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远程捕获投递服务
-- 创建专用网络
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 : -p -17819:-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 : --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 : --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 = M;
alter system set sga_max_size = g scope=spfile;
alter system set sga_target = g scope=spfile;
alter system set pga_aggregate_target=g;
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;
GRANT DBA to lhr ;
grant SELECT ANY DICTIONARY to lhr;
GRANT EXECUTE ON SYS.DBMS_LOCK TO lhr;
-- 启动监听
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.4/lhrsdb -dba "sys as sysdba" -dbap lhr -dt thin
-ts users -u lhr -p lhr -allindexes -scale 0.001 -tc -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)// 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
;
创建身份证明
代码语言：javascript复制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
结果：
代码语言：javascript复制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
代码语言：javascript复制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;
源端和目标端创建心跳表（脉动表）
代码语言：javascript复制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的实时同步
创建extract进程
代码语言：javascript复制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
EXTTRAIL ./dirdat/ea
TABLE LHR.*;
创建replicate进程
代码语言：javascript复制ADD REPLICAT repb INTEGRATED EXTTRAIL ./dirdat/ea
edit params repb
REPLICAT repb
USERIDALIAS ora11gb
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DBOPTIONS SETTAG 99
MAP lhr.*, TARGET lhr.*;
impdp network导入目标端数据
代码语言：javascript复制-- 让源端一直产生事务
/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
导入完成后，查看目标库：
代码语言：javascript复制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进程
在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。
代码语言：javascript复制LHR@ora12c> select count(*) from "LHR"."ORDERS";
COUNT(*)
----------
13019
LHR@ora19c> select count(*) from "LHR"."ORDERS";
COUNT(*)
----------
4562
-- 查询延迟
col incoming_path format a30
SELECT d.incoming_path,d.incoming_heartbeat_age FROM ogg.gg_lag d;
测试DDL和DML同步
在Oracle 11g a端建表和插入数据：
代码语言：javascript复制create table lhr.t1 (id number primary key,name varchar2());
insert into lhr.t1 select object_id,object_name from dba_objects where object_id<=;
commit;
select count(*) from lhr.t1;
LHR@lhrsdb> select count() from lhr.t1;
COUNT(8)
----------
19
可见，DDL和DML同步都已经完成！
通过restful查看OGG状态
代码语言：javascript复制EDIT PARAMS ./GLOBALS
ENABLEMONITORING
start PMSRVR
http://127.0.0.1:/groups
配置ora11gb到ora11ga的实时同步
创建extract进程
代码语言：javascript复制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
EXTTRAIL ./dirdat/eb
TABLE LHR.*;
创建replicate进程
代码语言：javascript复制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.*;
注意：反向同步的时候，不能再加“DBOPTIONS ENABLE_INSTANTIATION_FILTERING”参数了。
测试DDL和DML同步
在Oracle 11g b端操作：
代码语言：javascript复制create table lhr.t2 (id number primary key,name varchar2());
insert into lhr.t2 select object_id,object_name from dba_objects where object_id<=;
commit;
select count() from lhr.t2;
在a端可以看到新建的表和数据。
代码语言：javascript复制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
代码语言：javascript复制cd /ogg/healthcheck/
sqlplus sys/lhr@172.72.7.14/LHR11G as sysdba
@ogghc_install.sql
@ogghc_run.sql
其它SQL
代码语言：javascript复制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
解决：
代码语言：javascript复制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”参数了。
