Windows基于Oracle goldengate 19从ADG备库实时同步数据

最新推荐文章于 2025-05-14 19:56:41 发布

MrZhou周

最新推荐文章于 2025-05-14 19:56:41 发布

阅读量749

点赞数 17

分类专栏：数据库运维案例分享文章标签： windows oracle 数据库 OGG goldengate 实时同步

本文链接：https://blog.youkuaiyun.com/bing_yuan/article/details/147200412

版权

数据库运维案例分享专栏收录该内容

133 篇文章

订阅专栏

Windows基于Oracle goldengate 19从ADG备库实时同步数据

环境说明：
源端环境是一套各单实例的ADG，目标端是一个单实例的Oracle数据库，版本均为11.2.0.4，根据生产环境要求，任何对接生产数据的系统都不能直接对接主库，避免对业务产生直接的影响。

OGG版本：19.1

操作系统层面配置

1.增加ORACLE_HOME系统环境变量
2.修改swap虚拟内存配置大小为128GB

注：OGG安装步骤在此省略，安装比较简单，请自行解决或者留言

数据库层面配置

1.源端和目标端创建ggs用于同步

create tablespace tbsogg datafile 'd:\oradata\orcl\tbsogg01.dbf' size 1024m autoextend on next 10m;

create user ggs identified by Caecaodb2017 default tablespace tbsogg temporary tablespace temp;
grant connect ,resource,unlimited tablespace to ggs;
grant execute on utl_file to ggs;
grant select any dictionary,select any table to ggs;
grant alter any table to ggs;
grant flashback any table to ggs;
grant execute on dbms_flashback to ggs;
grant execute on sys.DBMS_STREAMS to ggs;
grant execute on dbms_xstream_gg_adm to ggs;
grant execute on DBMS_CAPTURE_ADM to ggs;

2.源和目标段授权ggs对同步用户的表的读写权限

grant insert,update,delete on scims.tscim to ggs;

3.源和目标端开启数据的ogg权限（动态修改，无需重启服务）

alter system set enable_goldengate_replication=true;

4.源端和目标端开启最小化日志
登录到ADG主库执行，并切换日志使设置生效

alter database add supplemental log data;
select supplemental_log_data_min from v$database;
alter system switch logfile;

源端和目标端配置tnsnames.ora以及配置OGG登录别名


--创建用户凭证别名
add credentialstore
--创建到源端的登录别名
--登录源端主库别名
alter credentialstore add user ggs@prod, password Password alias s_prod
--登录源端备库别名
alter credentialstore add user ggs@std, password Password alias s_std

--目标端登录别名
alter credentialstore add user ggs@target, password Password alias s_target


--测试验证
dblogin useridalias s_prod
dblogin useridalias s_std
dblogin useridalias s_target

源端和目标端MGR进程配置

edit param mgr 

port 7810
dynamicportlist 7810-7820
autorestart extract *,retries 5,waitminutes 3
purgeoldextracts ./dirdat/*, usecheckpoints,minkeepdays 3
accessrule, prog *, ipaddr *, allow
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45

登录ADG主库增加trandata

链接主库添加 trandata
dblogin useridalias s_prod
add trandata scims.TSCIM
info trandata scims.tscim

在ADG备库创建抽取进程

配置抽取进程


dblogin useridalias s_std

edit params extep
add extract extep, tranlog, begin now, threads 2
add exttrail ./dirdat/ep, extract extep, megabytes 100

extract extep
useridalias s_prod
exttrail ./dirdat/ep
warnlongtrans 3h, checkinterval 10m
tranlogoptions logretention enabled
tranlogoptions minefromactivedg
--tables
table scims.tscim;

在ADG备库配置投递进程

add extract dpep, exttrailsource ./dirdat/ep
add rmttrail ./dirdat/ep, extract dpep, megabytes 100

edit params dpep

extract dpep
rmthost 10.128.21.22, mgrport 7810
rmttrail ./dirdat/ep
gettruncates
passthru
table scims.tscim;

在配置目标库应用进程

dblogin useridalias s_target
add checkpointtable ggs.ckptab_scims
add replicat repep, exttrail ./dirdat/ep, checkpointtable ggs.ckptab_scims

edit params repep

replicat repep
useridalias s_target
discardfile ./dirrpt/repep.dsc, append, megabytes 100
gettruncates
--allownoopupdates
--assumetargetdefs
--dboptions suppresstriggers
--dboptions deferrefconst
--handlecollisions
map scims.tscim ,target scims.tscim;

数据初始化

初始化数据可以有多种方式，这里使用的使expdp/impdp的方式，由于创建的测试表是空表，如果在已经运行的系统种，需要使用flashbackup_scn导出，启动应用进程的时候根据scn启动

源端导出数据：

expdp \"/ as sysdba\" dumpfile=scims.dmp logfile=expscims.log schemas=scims

目标端导入：

C:\Windows\system32>impdp \"/ as sysdba\" dumpfile=SCIMS.DMP logfile=impscims.log schemas=scims

Import: Release 11.2.0.4.0 - Production on 星期日 4月 13 09:42:31 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_SCHEMA_01"
启动 "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=SCIMS.DMP logfile=impscims.log schemas=scims
处理对象类型 SCHEMA_EXPORT/USER
处理对象类型 SCHEMA_EXPORT/SYSTEM_GRANT
处理对象类型 SCHEMA_EXPORT/ROLE_GRANT
处理对象类型 SCHEMA_EXPORT/DEFAULT_ROLE
处理对象类型 SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
处理对象类型 SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE
处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA
. . 导入了 "SCIMS"."TSCIM"                                 0 KB       0 行
处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
作业 "SYS"."SYS_IMPORT_SCHEMA_01" 已于 星期日 4月 13 09:42:33 2025 elapsed 0 00:00:02 成功完成

启动源和目标端的同步进程

GGSCI (SJZT-Backup-2 as ggs@orcl) 65> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     DPEP        00:00:00      00:00:09
EXTRACT     RUNNING     EXTEP       00:00:00      00:00:06


GGSCI (SJZT-Backup-3 as ggs@sjztbak) 43> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     REPEP       00:00:00      00:00:03

测试验证数据同步

ADG主库插入数据
在这里插入图片描述
ADG备库查询

目标端查询

目标端查看同步统计信息

GGSCI (SJZT-Backup-3 as ggs@sjztbak) 45> stats repep

Sending STATS request to REPLICAT REPEP ...

Start of Statistics at 2025-04-14 04:29:28.

Replicating from SCIMS.TSCIM to SCIMS.TSCIM:

*** Total statistics since 2025-04-14 03:56:08 ***
        Total inserts                                      3.00
        Total updates                                      1.00
        Total deletes                                      1.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

*** Daily statistics since 2025-04-14 03:56:08 ***
        Total inserts                                      3.00
        Total updates                                      1.00
        Total deletes                                      1.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

*** Hourly statistics since 2025-04-14 04:00:00 ***
        Total inserts                                      0.00
        Total updates                                      1.00
        Total deletes                                      1.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   2.00

*** Latest statistics since 2025-04-14 03:56:08 ***
        Total inserts                                      3.00
        Total updates                                      1.00
        Total deletes                                      1.00
        Total upserts                                      0.00
        Total discards                                     0.00
        Total operations                                   5.00

End of Statistics.

附配置过程种的问题


2025-04-13 06:53:49  WARNING OGG-02551  ORACLE_HOME is not set to Oracle software directory.

2025-04-13 06:53:49  WARNING OGG-25108  Failed to set the Oracle session tag: ORA-04060: 权限不足以执行 DBMS_STREAMS.SET_TAG
ORA-06512: 在 "SYS.DBMS_STREAMS", line 16
ORA-06512: 在 line 1.
Successfully logged into database.
After upgrade to 11.2.0.1 DBMS_STREAMS.SET_TAG fails: ORA-4060 Insufficient Privileges (Doc ID 1275958.1)
Last updated on FEBRUARY 04, 2022

grant execute on dbms_streams_adm to ggs;

grant execute on dbms_xstream_gg_adm to ggs;
grant execute on DBMS_CAPTURE_ADM to ggs;

GGSCI (SJZT-Backup-2 as ggs@orcl) 6> add trandata scims.TSCIM

2025-04-13 07:37:04  INFO    OGG-15132  Logging of supplemental redo data enabled for table SCIMS.TSCIM.

2025-04-13 07:37:04  INFO    OGG-15133  TRANDATA for scheduling columns has been added on table SCIMS.TSCIM.

2025-04-13 07:37:04  WARNING OGG-00706  Failed to add supplemental log group on table SCIMS.TSCIM due to ORA-06550: 第 1
 行, 第 46 列:
PL/SQL: ORA-00904: : 标识符无效
ORA-06550: 第 1 行, 第 39 列:
PL/SQL: SQL Statement ignored
ORA-06550: 第 1 行, 第 109 列:
PLS-00201: 必须声明标识符 'DBMS_XSTREAM_GG_ADM'
ORA-06550: 第 1 行, 第 109 列:
PL/SQL: Statement ignored
ORA-06550: 第 1 行, 第 156 列:
PLS-00201: 必须声明标识符 'DBMS_CAPTURE_ADM'
ORA-06550: 第 1 行, 第 156 列:
PL/SQL: Statement ignored
ORA-06550: 第 1 行, 第 267 列:
PLS-00201: 必须声明标识符 'DBMS_XSTREAM_GG_ADM'
ORA-06550: 第 1 行, 第 267 列:
PL/SQL: Statement ignored
 SQL DECLARE saved_sync varchar2(4); BEGIN select dbms_xstream_gg_adm.synchronization into saved_sync from dual; dbms_xs
tream_gg_adm.synchronization := 'NONE'; DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(table_name => '"SCIMS"."TSCIM"', su
pplemental_logging => 'none'); dbms_xstream_gg_adm.synchronization := saved_sync; END;.

GGSCI (SJZT-Backup-2 as ggs@orcl) 7>





2025-04-13 08:06:03  WARNING OGG-01842  CACHESIZE PER DYNAMIC DETERMINATION (32G) LESS THAN RECOMMENDED: 64G (64bit system)
vm found: 41.54G
Check swap space. Recommended swap/extract: 128G (64bit system).




2025-04-13 08:48:39  ERROR   OGG-00868  The number of Oracle redo threads (2) is not the same as the number of checkpoint threads
(1). EXTRACT groups on RAC systems should be created with the THREADS parameter (e.g., ADD EXT <group name>, TRANLOG, THREADS 2, B
EGIN...).

2025-04-13 08:48:39  ERROR   OGG-01668  PROCESS ABENDING.



插入数据的时候抽取进程失败
2025-04-13 09:50:22  ERROR   OGG-00717  Found unsupported in-memory undo record in sequence 13, at RBA 1632272, with SCN 0.987876
(987876) ... Minimum supplemental logging must be enabled to prevent data loss.

***********************************************************************
*                   ** Run Time Statistics **                         *
***********************************************************************


Report at 2025-04-13 09:50:23 (activity since 2025-04-13 09:50:20)

select supplemental_log_data_min from v$database;



目标段写入失败，权限不足 
2025-04-14 03:48:39  WARNING OGG-01004  Aborted grouped transaction on SCIMS.TSCIM, Database error 1031 (OCI Error ORA-01031: 权限
不足 (status = 1031), SQL <INSERT INTO "SCIMS"."TSCIM" ("TID","TNAME","AGE") VALUES (:a0,:a1,:a2)>).

2025-04-14 03:48:39  WARNING OGG-01003  Repositioning to rba 1955 in seqno 0.

2025-04-14 03:48:39  WARNING OGG-01154  SQL error 1031 mapping SCIMS.TSCIM to SCIMS.TSCIM OCI Error ORA-01031: 权限不足 (status =
1031), SQL <INSERT INTO "SCIMS"."TSCIM" ("TID","TNAME","AGE") VALUES (:a0,:a1,:a2)>.

Source Context :
  SourceModule            : [er.replicat.errors]
  SourceID                : [er/replicat/reperrors.cpp]
  SourceMethod            : [ggs::er::ReplicatContext::repError]