当前位置：首页 » 《随便一记》 » 正文

【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12.2实时异构同步Oracle数据部署方案(下)

12 人参与  2024年04月19日 13:08  分类 : 《随便一记》  评论

点击全文阅读


系列文章目录

【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12.2实时异构同步Oracle数据部署方案(上)
【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12.2实时异构同步Oracle数据部署方案(中)
【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12.2实时异构同步Oracle数据部署方案(下)

文章目录

系列文章目录前言八、配置支持DDL同步操作1、@marker_setup.sql2、@ddl_setup.sql3、@role_setup.sql4、@ddl_enable.sql5、@marker_status.sql 九、配置主库到备库同步（支持DDL）1、源端配置2、源端新增抽取进程配置3、源端配置投递进程配置4、源端增加抽取进程5、源端增加投递进程6、源端添加表级别附加日志7、启动Extract和PUMP进程8、目的端编辑REPLICAT进程配置9、添加应用进程10、源端导出emp.dept表并传到目的端应用10.1、源端通过数据泵导出表10.2、目的端配置导入目录10.3、将源端导出的dump文件传到目的端10.4、目标端导入数据到原表10.5、OGG库审计表数据初始化10.6、OGG库添加ETLTIME字段10.7、OGG库ETLTIME字段数据初始化10.8、OGG库添加索引，防止进程lag过高 11、启动应用进程12、验证12.1、源端操作12.2、目的端查看 总结

前言

博主所在单位目前使用Oracle GoldenGate将各个业务生产库汇聚到一起做数仓实时ODS平台，源端库可能涉及Oracle、Mysql、达梦、Guassdb库。
之前写过一系列关于GoldenGate异构同步Mysql、Kafka、Kylin、Flink做实时计算的场景文章。但是突然发现，卧槽最最最应该第一个做的Oracle->Oracle的实时异构同步文档竟然没写！
来弥补一下这个空白！！！
好了，扯远了，回归技术！
下面是我给大家的生产环境下，如何部署GoldenGate12C及异构实时同步数据的解决方案。本文主要介绍如何实现实时异构的进行Oracle->Oracle的数据同步，这种数据同步适用于灾备、升级、实时ODS等场景使用。我这里给大家了三个GoldenGate部署方式，看大家喜欢用哪种吧！！
注意：本部署方案分为三章节，三章节的重点分别是：

GoldenGate12C安装前，数据库层面的准备工作！！！ 给你GoldenGate12C的三种部署方式，看你喜欢哪种部署方式！！！ 配置支持DDL操作的实时数据同步！！！

八、配置支持DDL同步操作

在源库执行，根据提示输入ogg管理账户：ogg

[oracle@source ogg12]$ cd $GGHOME[oracle@source ogg12]$ sqlplus / as sysdba

执行如下脚本：

1、@marker_setup.sql

SQL>  @marker_setup.sqlMarker setup scriptYou will be prompted for the name of a schema for the Oracle GoldenGate database objects.NOTE: The schema must be created prior to running this script.NOTE: Stop all DDL replication before starting this installation.Enter Oracle GoldenGate schema name:OGGMarker setup table script complete, running verification script...Please enter the name of a schema for the GoldenGate database objects:Setting schema name to OGGMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OKScript complete.

2、@ddl_setup.sql

该脚本创建了进行DDL抽取所需要的触发器和包。
主要提示：执行此脚本时，所有发出DDL的会话都必须关闭并重新连接，否则可能会发生错误6508（找不到被调用的程序单元）并且DDL操作可能会失败。这是因为一个已知的Oracle bug＃2747350。
未关闭的DDL会话会以列表的形式显示出来。

3、@role_setup.sql

该脚本删除并且重建DDL同步所需要的角色，它授权管理账户对DDL对象上的DML权限
根据提示执行如下授权操作：

Grant GGS_GGSUSER_ROLE to ogg;

4、@ddl_enable.sql

改脚本创建DDL触发器，以捕获DDL操作，用于向marker和history表插入DDL信息

5、@marker_status.sql

验证ddl安装
如下即正常安装ddl支持

SQL> @marker_status.sqlPlease enter the name of a schema for the GoldenGate database objects:OGGSetting schema name to OGGMARKER TABLE-------------------------------OKMARKER SEQUENCE-------------------------------OK

九、配置主库到备库同步（支持DDL）

需求：将源端scott用户下的emp,dept 表通过ogg同步到目的端 ，逻辑同生产ODS

1、源端配置

MGR进程配置（源端和目的端都做下面操作，添加的参数也一样）：

GGSCI (source) 6> info all    Program     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING   GGSCI (source) 7> edit params mgr--加入下面内容PORT 7809DYNAMICPORTLIST 7810-7860AUTORESTART ER *, RETRIES 3, WAITMINUTES 5PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 30lagreporthours 1laginfominutes 30lagcriticalminutes 60--保存退出后，重启管理进程，让配置生效：GGSCI (source) 8> stop mgrManager process is required by other GGS processes.Are you sure you want to stop it (y/n)?ySending STOP request to MANAGER ...Request processed.Manager stopped.GGSCI (source) 9> start mgrManager started.GGSCI (source) 10> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING

2、源端新增抽取进程配置

GGSCI (source) 12> edit params e_sc    --加入如下内容：extract e_scuserid ogg,password oggsetenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)setenv(ORACLE_SID="orcl")reportcount every 30 minutes,ratenumfiles 5000discardfile ./dirrpt/e_sc.dsc,append,megabytes 1000warnlongtrans 2h,checkinterval 30mexttrail ./dirdat/scdboptions allowunusedcolumntranlogoptions archivedlogonlytranlogoptions altarchivelogdest primary /u01/archddl include mappedddloptions addtrandata,reportnotcpsourcetimernocompressupdatesfetchoptions USEROWIDNOCOMPRESSDELETES----------scott.EMPtable SCOTT.EMP,tokens(TKN-CSN = @GETENV('TRANSACTION', 'CSN'),TKN-COMMIT-TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),TKN-OP-TYPE = @GETENV ('GGHEADER', 'OPTYPE'));----------SCOTT.DEPTtable SCOTT.DEPT,tokens(TKN-CSN = @GETENV('TRANSACTION', 'CSN'),TKN-COMMIT-TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),TKN-OP-TYPE = @GETENV ('GGHEADER', 'OPTYPE'));

3、源端配置投递进程配置

GGSCI (source) 13>edit params d_sc--加入如下内容：extract d_scrmthost 192.168.1.10,mgrport 7809,compressuserid ogg,password oggPASSTHRUnumfiles 5000rmttrail ./dirdat/scdynamicresolutiontable scott.*;

4、源端增加抽取进程

GGSCI (source) 16> add extract e_sc,tranlog,begin nowEXTRACT added.GGSCI (source) 17> add exttrail ./dirdat/sc,extract e_sc,megabytes 500EXTTRAIL added.

5、源端增加投递进程

add extract d_sc,exttrailsource ./dirdat/scadd rmttrail ./dirdat/sc,extract d_sc,megabytes 500

6、源端添加表级别附加日志

GGSCI (source) 40> dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (source as ogg@orcl) 41> add trandata SCOTT.EMPLogging of supplemental redo data enabled for table SCOTT.EMP.TRANDATA for scheduling columns has been added on table 'SCOTT.EMP'.TRANDATA for instantiation CSN has been added on table 'SCOTT.EMP'.GGSCI (source as ogg@orcl) 42> GGSCI (source as ogg@orcl) 42> add trandata SCOTT.DEPTLogging of supplemental redo data enabled for table SCOTT.DEPT.TRANDATA for scheduling columns has been added on table 'SCOTT.DEPT'.TRANDATA for instantiation CSN has been added on table 'SCOTT.DEPT'.GGSCI (source as ogg@orcl) 43> info trandata SCOTT.EMPLogging of supplemental redo log data is enabled for table SCOTT.EMP.Columns supplementally logged for table SCOTT.EMP: EMPNO.Prepared CSN for table SCOTT.EMP: 1108355

7、启动Extract和PUMP进程

GGSCI (source) 38> start er *Sending START request to MANAGER ...EXTRACT D_SC startingSending START request to MANAGER ...EXTRACT E_SC startingGGSCI (source) 39> info allProgram     Status      Group       Lag at Chkpt  Time Since ChkptMANAGER     RUNNING                                           EXTRACT     RUNNING     D_SC        00:00:00      00:00:04    EXTRACT     RUNNING     E_SC        00:00:00      00:00:04

8、目的端编辑REPLICAT进程配置

GGSCI (ogg) 1> edit params mgr---加入下面配置replicat r_scsetenv(NLS_LANG=AMERICAN_AMERICA.UTF8)userid ogg,password oggreportcount every 30 minutes,ratereperror default,abendnumfiles 20000checkpointsecs 40assumetargetdefsdiscardfile ./dirrpt/r_sc.dsc,append,megabytes 1000allownoopupdatesddl &include mapped &exclude objname scott.*_audit &exclude optype create &        objtype 'table' &exclude optype drop &        objtype 'table' &exclude objtype 'index' &        objname scott.*_his &exclude instr 'constraint' &--exclude instr 'null' &exclude instr 'trigger' &exclude instr 'rename to' &exclude instr 'grant' &exclude instr 'revoke' &exclude instr 'analyze'ddloptions reportallowduptargetmap----------EMPgetinsertsgetupdatesgetdeletesnoupdatedeletesmap SCOTT.EMP,target SCOTT.EMP,keycols(EMPNO),colmap(usedefaults,etltime=@DATENOW());ignoreinsertsignoreupdatesgetdeletesINSERTDELETESmap SCOTT.EMP,target SCOTT.EMP_HIS,keycols(EMPNO),colmap(usedefaults,etltime=@DATENOW());NOINSERTDELETESupdatedeletesgetinsertsgetupdatesgetdeletesmap SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap(EMPNO=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,before.EMPNO,EMPNO),csn=@token('tkn-csn'),optime=@token('tkn-commit-ts'),optype=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,'DELETE',@token('tkn-op-type')),inserttime=@eval(@strfind(@token('tkn-op-type'),'INSERT')>0,@token('tkn-commit-ts')),curdate=@DATENOW());ignoreinsertsgetupdatesignoredeletesmap SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap(usedefaults,csn=@token('tkn-csn'),optime=@token('tkn-commit-ts'),optype=@case(@token('tkn-op-type'),'PK UPDATE','INSERT'),inserttime=@token('tkn-commit-ts'),curdate=@DATENOW()),filter(@strfind(@token('tkn-op-type'),'PK UPDATE') >0),insertallrecords;----------DEPTgetinsertsgetupdatesgetdeletesnoupdatedeletesmap SCOTT.DEPT,target SCOTT.DEPT,keycols(DEPTNO),colmap(usedefaults,etltime=@DATENOW());ignoreinsertsignoreupdatesgetdeletesINSERTDELETESmap SCOTT.DEPT,target SCOTT.DEPT_HIS,keycols(DEPTNO),colmap(usedefaults,etltime=@DATENOW());NOINSERTDELETESupdatedeletesgetinsertsgetupdatesgetdeletesmap SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(DEPTNO=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,before.DEPTNO,DEPTNO),csn=@token('tkn-csn'),optime=@token('tkn-commit-ts'),optype=@if(@strfind(@token('tkn-op-type'),'PK UPDATE')>0,'DELETE',@token('tkn-op-type')),inserttime=@eval(@strfind(@token('tkn-op-type'),'INSERT')>0,@token('tkn-commit-ts')),curdate=@DATENOW());ignoreinsertsgetupdatesignoredeletesmap SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(usedefaults,csn=@token('tkn-csn'),optime=@token('tkn-commit-ts'),optype=@case(@token('tkn-op-type'),'PK UPDATE','INSERT'),inserttime=@token('tkn-commit-ts'),curdate=@DATENOW()),filter(@strfind(@token('tkn-op-type'),'PK UPDATE') >0),insertallrecords;

9、添加应用进程

GGSCI (ogg) 8> dblogin userid ogg,password oggSuccessfully logged into database.GGSCI (ogg as ogg@ogg) 9> ADD CHECKPOINTTABLE ogg.ckptSuccessfully created checkpoint table ogg.ckpt.GGSCI (ogg as ogg@ogg) 10> add replicat r_sc,exttrail ./dirdat/sc checkpointtable ogg.ckpt

10、源端导出emp.dept表并传到目的端应用

10.1、源端通过数据泵导出表

[oracle@source ogg12]$ mkdir /u01/exp--创建目录[oracle@source ogg12]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 20 18:34:35 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create directory expdir as '/u01/exp';Directory created.SQL> grant read,write on directory  expdir to scott;Grant succeeded.SQL> grant dba to scott;--这个权限是为了测试方便所以给的dba权限。生产慎用Grant succeeded.SQL> set num 50SQL> select current_scn from v$database;---查询当前scn       CURRENT_SCN--------------------------------------------------   1108836SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@source ~]$ expdp \' / as sysdba\' directory=EXPDIR dumpfile=expdp_20180920.dmp logfile=expdp_20180920.log tables=SCOTT.EMP,SCOTT.DEPT exclude=ref_constraint flashback_scn=1108836 Export: Release 11.2.0.4.0 - Production on Thu Sep 20 18:45:47 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsFLASHBACK automatically enabled to preserve database integrity.Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=EXPDIR dumpfile=expdp_20180920.dmp logfile=expdp_20180920.log tables=SCOTT.EMP,SCOTT.DEPT exclude=ref_constraint flashback_scn=1108836 Estimate in progress using BLOCKS method...Processing object type TABLE_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128 KBProcessing object type TABLE_EXPORT/TABLE/PROCACT_INSTANCEProcessing object type TABLE_EXPORT/TABLE/TABLEProcessing object type TABLE_EXPORT/TABLE/INDEX/INDEXProcessing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows. . exported "SCOTT"."EMP"                               8.562 KB      14 rowsMaster table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded******************************************************************************Dump file set for SYS.SYS_EXPORT_TABLE_01 is:  /u01/exp/expdp_20180920.dmpJob "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Thu Sep 20 18:46:21 2018 elapsed 0 00:00:24

10.2、目的端配置导入目录

[oracle@ogg ~]$ mkdir /u01/imp[oracle@ogg ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 20 18:34:24 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> create directory expdir as '/u01/exp';Directory created.SQL> create directory impdir as '/u01/imp';Directory created.SQL> grant read,write on directory  impdir to scott;Grant succeeded.

10.3、将源端导出的dump文件传到目的端

[oracle@source ~]$ scp /u01/exp/expdp_20180920.* 192.168.1.10:/u01/imp/oracle@192.168.1.10's password: expdp_20180920.dmp                                                                                                                                         100%  168KB 168.0KB/s   00:00    expdp_20180920.log

10.4、目标端导入数据到原表

impdp \' / as sysdba\' directory=IMPDIR dumpfile=expdp_20180920.dmp logfile=expdp_20180920.log

10.5、OGG库审计表数据初始化

create table SCOTT.EMP_AUDIT(  EMPNO   NUMBER(4),  CSN        NUMBER,  OPTIME     TIMESTAMP(6),  OPTYPE     VARCHAR2(20),  INSERTTIME TIMESTAMP(6),  CURDATE    TIMESTAMP(6));create table SCOTT.DEPT_AUDIT(  DEPTNO   NUMBER(2),  CSN        NUMBER,  OPTIME     TIMESTAMP(6),  OPTYPE     VARCHAR2(20),  INSERTTIME TIMESTAMP(6),  CURDATE    TIMESTAMP(6));insert into SCOTT.EMP_AUDIT select EMPNO,1108836,sysdate,'INITIAL',sysdate,'' from SCOTT.EMP;insert into SCOTT.DEPT_AUDIT select DEPTNO,1108836,sysdate,'INITIAL',sysdate,'' from SCOTT.DEPT;commit;

10.6、OGG库添加ETLTIME字段

---目标表alter table  scott.emp add etltime timestamp;alter table  scott.dept add etltime timestamp;---HIS表alter table  scott.emp_HIS add etltime timestamp;alter table  scott.dept_HIS add etltime timestamp;

10.7、OGG库ETLTIME字段数据初始化

----目标表update scott.emp set etltime=sysdate;update scott.dept set etltime=sysdate;commit;

10.8、OGG库添加索引，防止进程lag过高

CREATE INDEX scott.emp_idx  ON scott.emp_audit(empno);CREATE INDEX scott.dept_idx  ON scott.dept_audit(deptno);

11、启动应用进程

start replicat r_sc aftercsn 1108836

12、验证

12.1、源端操作

[oracle@source ~]$ sqlplus scott/tigerSQL*Plus: Release 11.2.0.4.0 Production on Thu Sep 20 19:11:57 2018Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter table dept add test number;Table altered.SQL> insert into dept values(50,'a','a','1');1 row created.SQL> alter table dept drop column test;Table altered.SQL> alter system switch logfile;System altered.SQL> alter system switch logfile;alter system switch logfile;System altered.

12.2、目的端查看

在这里插入图片描述
看一下日志，DDL也正常应用了：

2018-09-20 19:00:49  INFO    OGG-06510  Using the following key columns for target table SCOTT.DEPT_AUDIT: DEPTNO.2018-09-20 19:00:49  INFO    OGG-03010  Performing implicit conversion of column data from character set UTF-8 to zhs16gbk.2018-09-20 19:00:49  INFO    OGG-00482  DDL found, operation [alter table dept drop column test  (size 34)].2018-09-20 19:00:49  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [alter table "SCOTT"."DEPT" drop column test  (size 44)].2018-09-20 19:00:49  INFO    OGG-00487  DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT].2018-09-20 19:00:49  INFO    OGG-01407  Setting current schema for DDL operation to [SCOTT].2018-09-20 19:00:49  INFO    OGG-00484  Executing DDL operation.2018-09-20 19:00:50  INFO    OGG-00483  DDL operation successful.2018-09-20 19:00:50  INFO    OGG-01408  Restoring current schema for DDL operation to [OGG].2018-09-20 19:00:50  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [alter table "SCOTT"."DEPT_HIS" drop column test  /* GOLDENGATE_DDL_REPLICATION */ (size 81)].2018-09-20 19:00:50  INFO    OGG-00487  DDL operation included [include mapped], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_HIS].2018-09-20 19:00:50  INFO    OGG-01407  Setting current schema for DDL operation to [SCOTT].2018-09-20 19:00:50  INFO    OGG-00484  Executing DDL operation.2018-09-20 19:00:50  INFO    OGG-00483  DDL operation successful.2018-09-20 19:00:50  INFO    OGG-01408  Restoring current schema for DDL operation to [OGG].2018-09-20 19:00:50  INFO    OGG-00489  DDL is of mapped scope, after mapping new operation [alter table "SCOTT"."DEPT_AUDIT" drop column test  /* GOLDENGATE_DDL_REPLICATION */ (size 83)].2018-09-20 19:00:50  INFO    OGG-00488  DDL operation excluded [exclude objname scott.*_audit], optype [ALTER], objtype [TABLE], objowner [SCOTT], objname [DEPT_AUDIT].

总结

我用了三个章节介绍完了生产环境下如何实时异构同步Oracle->Oracle，且其中包括了如何初始化以及增量应该从哪个时间点开始才能够保证数据不重复、不丢失，如果有用到的各位可以作为参考！！！


点击全文阅读


本文链接：http://zhangshiyu.com/post/97558.html

<< 上一篇 下一篇 >>

  • 评论（0）
  • 赞助本站

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

最新文章

  • 前端常见的十种布局
  • 使用Vue3调用高德地图接口（基于后端同学想搞前端地图的手把手式教程）
  • (精品)当真千金失聪后小说
  • (抖音)穿成女配要逃婚 主角沈千千程奕
  • 《九龙护体，无敌天下》最新章节 苏墨董小宛全文阅读
  • 作者序临写的宠妾灭妻，和离后被暴君宠野了小说大结局全章节阅读
  • 完整版小说免费阅读开枝散叶苏颜林琅_开枝散叶苏颜林琅热门小说大全 -
  • 免费阅读无弹窗军婚甜蜜蜜，兵王他不撒手了(徐子矜陆寒洲)_军婚甜蜜蜜，兵王他不撒手了徐子矜陆寒洲小说完结版 -
  • Win 11操作系统Vmware Ubuntu 22.04(2023最新版)图形化桌面的安装与设置
  • 《迟到的深情，不要也罢》最新章节免费阅读by夏小霜无广告小说
  • 王多鱼王小云全文阅读最新 王多鱼王小云小说目录
  • 顾延沈慕雅全集小说_重生：从报警抓校花开始完结版阅读

    关于我们 | 我要投稿 | 免责申明

    Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1