2024年大数据最全【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12，大数据开发工作资料

最新推荐文章于 2024-05-23 18:49:18 发布

2401_84592231

最新推荐文章于 2024-05-23 18:49:18 发布

阅读量630

点赞数 29

分类专栏：程序员文章标签：大数据

本文链接：https://blog.csdn.net/2401_84592231/article/details/138458991

版权

程序员专栏收录该内容

58 篇文章 0 订阅

订阅专栏

网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。

需要这份系统化资料的朋友，可以戳这里获取

一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

前言

博主所在单位目前使用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.sql

Marker setup script

You 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:OGG


Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

Script 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.sql
Please enter the name of a schema for the GoldenGate database objects:
OGG
Setting schema name to OGG

MARKER TABLE
-------------------------------
OK

MARKER SEQUENCE
-------------------------------
OK

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

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

1、源端配置

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

GGSCI (source) 6> info all    

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING   
GGSCI (source) 7> edit params mgr
--加入下面内容
PORT 7809
DYNAMICPORTLIST 7810-7860
AUTORESTART ER \*, RETRIES 3, WAITMINUTES 5
PURGEOLDEXTRACTS ./dirdat/\*, USECHECKPOINTS, MINKEEPDAYS 30
lagreporthours 1
laginfominutes 30
lagcriticalminutes 60
--保存退出后，重启管理进程，让配置生效：
GGSCI (source) 8> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)?y

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (source) 9> start mgr
Manager started.
GGSCI (source) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

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

GGSCI (source) 12> edit params e_sc    
--加入如下内容：
extract e_sc
userid ogg,password ogg
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
setenv(ORACLE_SID="orcl")
reportcount every 30 minutes,rate
numfiles 5000
discardfile ./dirrpt/e_sc.dsc,append,megabytes 1000
warnlongtrans 2h,checkinterval 30m
exttrail ./dirdat/sc
dboptions allowunusedcolumn
tranlogoptions archivedlogonly
tranlogoptions altarchivelogdest primary /u01/arch
ddl include mapped
ddloptions addtrandata,report
notcpsourcetimer
nocompressupdates
fetchoptions USEROWID
NOCOMPRESSDELETES
----------scott.EMP
table SCOTT.EMP,tokens(
TKN-CSN = @GETENV('TRANSACTION', 'CSN'),
TKN-COMMIT-TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'),
TKN-OP-TYPE = @GETENV ('GGHEADER', 'OPTYPE')
);
----------SCOTT.DEPT
table 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_sc
rmthost 192.168.1.10,mgrport 7809,compress
userid ogg,password ogg
PASSTHRU
numfiles 5000
rmttrail ./dirdat/sc
dynamicresolution
table scott.\*;

4、源端增加抽取进程

GGSCI (source) 16> add extract e_sc,tranlog,begin now
EXTRACT added.

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

5、源端增加投递进程

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

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

GGSCI (source) 40> dblogin userid ogg,password ogg
Successfully logged into database.

GGSCI (source as ogg@orcl) 41> add trandata SCOTT.EMP

Logging 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.DEPT

Logging 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.EMP

Logging 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 starting

Sending START request to MANAGER ...
EXTRACT E_SC starting


GGSCI (source) 39> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     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_sc
setenv(NLS_LANG=AMERICAN_AMERICA.UTF8)
userid ogg,password ogg
reportcount every 30 minutes,rate
reperror default,abend
numfiles 20000
checkpointsecs 40
assumetargetdefs
discardfile ./dirrpt/r_sc.dsc,append,megabytes 1000
allownoopupdates
ddl &
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 report
allowduptargetmap
----------EMP
getinserts
getupdates
getdeletes
noupdatedeletes
map SCOTT.EMP,target SCOTT.EMP,keycols(EMPNO),colmap(
usedefaults,
etltime=@DATENOW()
);
ignoreinserts
ignoreupdates
getdeletes
INSERTDELETES
map SCOTT.EMP,target SCOTT.EMP_HIS,keycols(EMPNO),colmap(
usedefaults,
etltime=@DATENOW()
);
NOINSERTDELETES
updatedeletes
getinserts
getupdates
getdeletes
map 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()
);
ignoreinserts
getupdates
ignoredeletes
map 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;
----------DEPT
getinserts
getupdates
getdeletes
noupdatedeletes
map SCOTT.DEPT,target SCOTT.DEPT,keycols(DEPTNO),colmap(
usedefaults,
etltime=@DATENOW()
);
ignoreinserts
ignoreupdates
getdeletes
INSERTDELETES
map SCOTT.DEPT,target SCOTT.DEPT_HIS,keycols(DEPTNO),colmap(
usedefaults,
etltime=@DATENOW()
);
NOINSERTDELETES
updatedeletes
getinserts
getupdates
getdeletes
map 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()
);
ignoreinserts
getupdates
ignoredeletes
map 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 ogg
Successfully logged into database.

GGSCI (ogg as ogg@ogg) 9> ADD CHECKPOINTTABLE ogg.ckpt
Successfully created checkpoint table ogg.ckpt.

GGSCI (ogg as ogg@ogg) 10> add replicat r_sc,exttrail ./dirdat/sc checkpointtable ogg.ckpt

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

TTABLE ogg.ckpt
Successfully created checkpoint table ogg.ckpt.

GGSCI (ogg as ogg@ogg) 10> add replicat r_sc,exttrail ./dirdat/sc checkpointtable ogg.ckpt



[外链图片转存中...(img-j5NvLeW9-1714867611120)]
[外链图片转存中...(img-ngSRTiFb-1714867611121)]
[外链图片转存中...(img-UkpAaVvX-1714867611121)]

**既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！**

**由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/topics/618545628)**

2401_84592231

关注

29
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
2024年大数据最全【大数据实时数据同步】超级详细的生产环境OGG(GoldenGate)12，大数据开发工作资料

博主所在单位目前使用Oracle GoldenGate将各个业务生产库汇聚到一起做数仓实时ODS平台，源端库可能涉及Oracle、Mysql、达梦、Guassdb库。之前写过一系列关于GoldenGate异构同步Mysql、Kafka、Kylin、Flink做实时计算的场景文章。但是突然发现，卧槽最最最应该第一个做的Oracle->Oracle的实时异构同步文档竟然没写！来弥补一下这个空白！！！好了，扯远了，回归技术！
复制链接

扫一扫