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_stdedit params extep
add extract extep, tranlog, begin now, threads 2
add exttrail ./dirdat/ep, extract extep, megabytes 100extract 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 100edit params dpepextract 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_scimsedit params repepreplicat 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=scimsImport: Release 11.2.0.4.0 - Production on 星期日 4月 13 09:42:31 2025Copyright (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 allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
EXTRACT RUNNING DPEP 00:00:00 00:00:09
EXTRACT RUNNING EXTEP 00:00:00 00:00:06GGSCI (SJZT-Backup-3 as ggs@sjztbak) 43> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING
REPLICAT RUNNING REPEP 00:00:00 00:00:03测试验证数据同步
ADG主库插入数据
ADG备库查询
目标端查询
目标端查看同步统计信息
GGSCI (SJZT-Backup-3 as ggs@sjztbak) 45> stats repepSending 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.00Total updates 1.00Total deletes 1.00Total upserts 0.00Total discards 0.00Total operations 5.00*** Daily statistics since 2025-04-14 03:56:08 ***Total inserts 3.00Total updates 1.00Total deletes 1.00Total upserts 0.00Total discards 0.00Total operations 5.00*** Hourly statistics since 2025-04-14 04:00:00 ***Total inserts 0.00Total updates 1.00Total deletes 1.00Total upserts 0.00Total discards 0.00Total operations 2.00*** Latest statistics since 2025-04-14 03:56:08 ***Total inserts 3.00Total updates 1.00Total deletes 1.00Total upserts 0.00Total discards 0.00Total operations 5.00End 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, 2022grant 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.TSCIM2025-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 ignoredSQL 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]
