使用OGG 21.3远程实时互相同步Oracle 11.2.0.4（双主）

小麦苗DBA宝典

发布于 2022-11-07 19:10:31

1.5K1

发布于 2022-11-07 19:10:31

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

类似文章

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
;

创建身份证明

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

结果：

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

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;

源端和目标端创建心跳表（脉动表）

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进程

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进程

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导入目标端数据

-- 让源端一直产生事务
/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

导入完成后，查看目标库：

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进程

在启用之前，可以查询表数据，发现和源端数据量相差很大，因为源端一直在做压测产生新数据。

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端建表和插入数据：

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状态

EDIT PARAMS ./GLOBALS
ENABLEMONITORING

start PMSRVR

http://127.0.0.1:/groups

配置ora11gb到ora11ga的实时同步

创建extract进程

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进程

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端操作：

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端可以看到新建的表和数据。

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

cd /ogg/healthcheck/
sqlplus sys/lhr@172.72.7.14/LHR11G as sysdba
@ogghc_install.sql
@ogghc_run.sql

其它SQL

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

解决：

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”参数了。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2022-10-14，如有侵权请联系 cloudcommunity@tencent.com 删除

html

本文分享自 DB宝微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度