OGG DownStream 部署：主要的目的是为了减轻源数据库负载。为什么选择OGG DownStream 部署？在许多情况下，公司不希望将主生产数据库用于下游层，以提取数据用于集成或报告目的。相反，他们希望数据库仅为其核心关键应用程序连接。在其他一些场景中，无法访问主要生产数据库，例如SaaS或应用程序供应商的数据库等等。为了缓解问题或解决挑战并实现实时数据提取，ogg设计了GoldenGate下游捕获架构。通过GoldenGate下游部署，您可以将从源数据库提取数据的负担转移到中间或目标服务器。这种设计涉及从不同的数据库中提取数据，通常称为挖掘数据库。作为下游部署的一部分，源数据库和挖掘数据库是单独的数据库，而日志挖掘服务器位于下游数据库中。使用重做传输（类似于Data Guard）将日志从源数据库通过网络传送到下游数据库。GoldenGate使用下游数据库中的日志挖掘服务器从重做日志（或归档文件）中提取更改。挖掘数据库只是一个空数据库。与DataGuard一样，重做日志不应用于下游数据库，挖掘数据库只接收来自主源数据库的重做日志流。在任何给定时间点，只能为下游数据库服务器上的单个源数据库设置实时挖掘。如果实现涉及复制多个源数据库，则必须配置下游捕获的归档日志模式。下游的归档日志模式不是实时捕获。
下面我们将提供一个案例：将实时重做日志数据传输到下游挖掘数据库的详细分步说明。本案例涉及到三个数据库，
源数据库 : PRIMDB
源库PDB NAME : PDB1
下游挖掘数据库: MINDB
目标数据库 : TGTDB
所有的3个数据库都运行在19c上面
先决条件
检查补充日志
SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;
如果结果为“YES”，则数据库符合Oracle GoldenGate要求。
如果结果是
NO, 执行下面的sql进行修改。
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ENABLE_GOLDENGATE_REPLICATION PARAMETER
SQL> alter system set enable_goldengate_replication=true scope=both sid=’*’;
SQL> show parameter ENABLE_GOLDENGATE_REPLICATION
Value should be True.
CREATE USERS AND TABLESPACES IN CDB+PDB
SQL> create tablespace TBS_GGS datafile ‘+DATAC1’ size 500m;
Tablespace created.
SQL> create user c##ggadmin identified by “Password_123” default tablespace TBS_GGS temporary tablespace temp;
User created.
SQL> alter user c##ggadmin quota unlimited on TBS_GGS;
User altered.
SQL> alter user c##ggadmin identified by “Password_123”;
User altered.
SQL> exec dbms_goldengate_auth.grant_admin_privilege(‘c##ggadmin’,container=>’all’);
PL/SQL procedure successfully completed.
SQL> show pdbs
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL> alter session set container=PDB1;
Session altered.
SQL> create tablespace TBS_GGS datafile ‘+DATAC1’ size 500m;
Tablespace created.
SQL> create user c##ggadmin identified by “Password_123” default tablespace TBS_GGS temporary tablespace temp;
User created.
SQL> alter user c##ggadmin quota unlimited on TBS_GGS;
User altered.
SQL> alter user c##ggadmin identified by “Password_123”;
User altered.
GRANT PRIVILEGES in CDB + PDB
grant connect,resource to c##ggadmin;
grant create session to c##ggadmin;
grant select any dictionary, select any table,SELECT ANY TRANSACTION to c##ggadmin;
grant create table, alter any table to c##ggadmin;
grant flashback any table to c##ggadmin;
grant execute on dbms_flashback to c##ggadmin;
grant execute on utl_file to c##ggadmin;
grant select on system.logmnr_session$ to c##ggadmin;
CHECK CONNECTIVITY
sqlplus /nolog connect c##ggadmin/Pasword_124@CONNECTION_STRING
源库配置
SQL> show parameter LOG_ARCHIVE_CONFIG NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string SQL> Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB, MINDB)' scope=both sid='*'; System altered. SQL> show parameter LOG_ARCHIVE_CONFIG NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string DG_CONFIG=(PRIMDB, MINDB) SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO SQL> alter session set container=PDB1; Session altered. SQL> ALTER PLUGGABLE DATABASE ADD SUPPLEMENTAL LOG DATA; Pluggable database altered.
将挖掘数据库的TNS条目添加到源数据库中。 [root@ admin]# vi tnsnames.ora MINDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 20.20.0.21)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MINDB) ) )
从源库上拷贝密码文件到下游挖掘数据库
可以参考部署ADG的文章(Doc ID
1984091.1)
使用SYS帐户和TNSPING测试源数据库和挖掘数据库之间的连接。
从下游挖掘数据库上测试 : [oracle@admin]$ sqlplus sys@ MINDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 9 14:38:27 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password: Last Successful login time: Thu Oct 9 14:38:44 2025 Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production Version 19.25.0.0.0
SQL>
[oracle@admin]$ sqlplus sys@ PRIMDB as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 9 14:39:22 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password: Last Successful login time: Thu Oct 9 14:39:58 2025 Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production Version 19.25.0.0.0 SQL>
CONNECTION SUCCESSFUL
从源库上进行测试 :
[oracle@ admin]$ sqlplus sys@ PRIMDB as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 9 14:41:17 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2024, Oracle. All rights reserved.
Enter password: Last Successful login time: Thu Oct 9 14:42:37 2025 Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production Version 19.25.0.0.0
SQL>
[oracle@ admin]$ sqlplus sys@ MINDB as sysdba SQL*Plus: Release 19.0.0.0.0 – Production on Thu Oct 9 14:45:17 2025 Version 19.25.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Enter password: Last Successful login time: Thu Oct 9 14:46:33 2025 Connected to: Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 – Production Version 19.25.0.0.0 SQL>
CONNECTION SUCCESSFUL
下游挖掘数据库配置
SQL> Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMDB, MINDB)' scope=both sid='*'; System altered. SQL> alter system set db_recovery_file_dest='/oradata/fast_recovery_area' scope=both; System altered. SQL> alter system set db_recovery_file_dest_size=700G scope=both; System altered. SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=mindb' scope=both; System altered. SQL> alter system set log_archive_dest_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILE,ALL_ROLES)' scope=both; System altered.
Copy
在源库上配置传输日志
SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_3 string enable SQL> alter system set log_archive_dest_state_3=DEFER; System altered. SQL> Alter system set LOG_ARCHIVE_DEST_3='SERVICE=MINDB ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) REOPEN=10 DB_UNIQUE_NAME=MINDB' scope=both sid='*'; System altered. SQL> show parameter LOG_ARCHIVE_DEST_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_3 string SERVICE=MINDB ASYNC NOREGIST ER VALID_FOR=(ONLINE_LOGFILES, PRIMARY_ROLE) REOPEN=10 DB_UNI QUE_NAME=MINDB Check the REDO LOG SIZE SQL> set pagesize 5000 set lines 200 column REDOLOG_FILE_NAME format a60 SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB FROM v$log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP#; GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB ---------- ---------- ---------- --- ---------------- -------------------------------------------------- ---------- 12 1 106 NO CURRENT +DATAC1/PRIMDB/ONLINELOG/group_12.4132.1121368637 10240 13 1 102 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_13.4133.1121368637 10240 14 1 103 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_14.4134.1121368637 10240 15 1 104 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_15.4135.1121368637 10240 16 1 105 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_16.4136.1121368637 10240 22 2 101 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_22.2846.1121368265 10240 23 2 102 NO CURRENT +DATAC1/PRIMDB/ONLINELOG/group_23.4128.1121368269 10240 24 2 99 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_24.4129.1121368275 10240 25 2 100 YES INACTIVE +DATAC1/PRIMDB/ONLINELOG/group_25.4130.1121368279 10240 26 2 0 YES UNUSED +DATAC1/PRIMDB/ONLINELOG/group_26.4176.1121528989 10240 10 rows selected.
Copy
在下游挖掘数据库上配置standby日志
THREAD 1 : SQL> alter database add logfile thread 1 group 101 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 1 group 102 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 1 group 103 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 1 group 104 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 1 group 105 '+DATAC1' size 10240M; Database altered. THREAD 2 : SQL> alter database add logfile thread 2 group 201 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 2 group 202 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 2 group 203 '+DATAC1' size 10240M; Database altered. SQL> alter database add logfile thread 2 group 204 '+DATAC1' size 10240M; Database altered. Drop the old Redolog group & Thread SQL> alter database drop logfile group 2; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database drop logfile group 4; Database altered. SQL> alter database drop logfile group 5; Database altered. SQL> alter database drop logfile group 7; Database altered. SQL> alter database drop logfile group 8; Database altered. Now the REDO Logs on MINING DB is exactly the same as Source DB. GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB ---------- ---------- ---------- --- ---------------- ------------------------------------------------------------ ---------- 101 1 10 YES INACTIVE +DATAC1/MINDB/ONLINELOG/group_101.4196.1121528385 10240 102 1 11 NO CURRENT +DATAC1/MINDB/ONLINELOG/group_102.4197.1121528395 10240 103 1 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_103.4198.1121528401 10240 104 1 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_104.4199.1121528411 10240 105 1 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_105.4200.1121528421 10240 201 2 7 NO CURRENT +DATAC1/MINDB/ONLINELOG/group_201.4201.1121528583 10240 202 2 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_202.4202.1121528591 10240 203 2 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_203.4203.1121528599 10240 204 2 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_204.4204.1121528609 10240 205 2 0 YES UNUSED +DATAC1/MINDB/ONLINELOG/group_205.4175.1121529059 10240 10 rows selected. ADD STANDBY REDO LOGS to Mining Database : Note -> Create Standby redo log files (same size as online redo log files and number of groups should be one greater than existing online redo log groups) alter database add standby logfile thread 1 group 301 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 1 group 302 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 1 group 303 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 1 group 304 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 1 group 305 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 1 group 306 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 2 group 401 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 2 group 402 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 2 group 403 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 2 group 404 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 2 group 405 '+DATAC1' size 10240M reuse; alter database add standby logfile thread 2 group 406 '+DATAC1' size 10240M reuse; check Standby-Redo log size set pagesize 5000 set lines 200 column REDOLOG_FILE_NAME format a60 SELECT a.GROUP#, a.THREAD#, a.SEQUENCE#, a.ARCHIVED, a.STATUS, b.MEMBER AS REDOLOG_FILE_NAME, (a.BYTES/1024/1024) AS SIZE_MB FROM v$standby_log a JOIN v$logfile b ON a.Group#=b.Group# ORDER BY a.GROUP#; GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB ---------- ---------- ---------- --- ---------- ------------------------------------------------------------ ---------- 301 1 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_301.4174.1121617673 10240 302 1 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_302.4163.1121617711 10240 303 1 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_303.4161.1121617715 10240 304 1 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_304.4166.1121617721 10240 305 1 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_305.4165.1121617727 10240 306 1 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_306.4164.1121617733 10240 401 2 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_401.4205.1121617817 10240 402 2 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_402.4206.1121617823 10240 403 2 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_403.4207.1121617829 10240 404 2 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_404.4208.1121617835 10240 405 2 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_405.4209.1121617839 10240 406 2 0 YES UNASSIGNED +DATAC1/MINDB/ONLINELOG/group_406.4210.1121617845 10240 12 rows selected. SQL> alter system set LOG_ARCHIVE_DEST_2='LOCATION=+RECOC1 VALID_FOR=(STANDBY_LOGFILES, ALL_ROLES)' scope=both; System altered.
Copy
在源库上启用LOG_ARCHIVE_DEST_STATE_3
ENABLE LOG_ARCHIVE_DEST_STATE_3 ON SOURCE DATABASE SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE; System altered. SQL> show parameter log_archive_dest_state_3 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_3 string ENABLE SQL> ALTER SYSTEM SWITCH ALL LOGFILE; System altered.
Copy
检查日志是否传输到下游挖掘数据库中
源库上切换日志
SQL> ALTER SYSTEM SWITCH ALL LOGFILE; System altered.
Copy
配置下游挖掘库ogg的配置
配置mgr进程
GGSCI (zlf-downstream) 4> view param mgr port 7809 LAGREPORTHOURS 5 LAGINFOMINUTES 5 LAGCRITICALMINUTES 15 --AUTOSTART extract e* --AUTIRESTART extract p* PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 14 accessrule,prog *,ipaddr *,allow
Copy
配置抽取ext1进程
GGSCI (zlf-downstream) 5> view param ext1 EXTRACT ext1 setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK) USERID oggadmin@PRIMDB PASSWORD oggadmin TRANLOGOPTIONS MININGUSER oggadmin MININGPASSWORD oggadmin TRANLOGOPTIONS INTEGRATEDPARAMS (downstream_real_time_mine Y) TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 3072) DISCARDFILE ./dirrpt/ext1.dsc, APPEND, MEGABYTES 1024 LOGALLSUPCOLS UPDATERECORDFORMAT FULL DBOPTIONS ALLOWUNUSEDCOLUMN REPORTCOUNT EVERY 1 MINUTES, RATE WARNLONGTRANS 2h,CHECKINTERVAL 300 DDL INCLUDE MAPPED, EXCLUDE OBJTYPE 'tablespace',EXCLUDE OBJTYPE 'JOB', EXCLUDE OBJTYPE 'TRIGGER', EXCLUDE OBJTYPE 'SNAPSHOT', EXCLUDE OBJTYPE 'SNAPSHOT LOG', EXCLUDE INSTR 'shrink space CHECK' DDLOPTIONS ADDTRANDATA TRANLOGOPTIONS FETCHPARTIALLOB getUpdateBefores NOCOMPRESSDELETES EXTTRAIL ./dirdat/lt TABLE TEST.*;
Copy
配置pump进程
GGSCI (zlf-lis-ogg-downstream) 6> view param ptest EXTRACT PTEST setenv (NLS_LANG = AMERICAN_AMERICA.ZHS16GBK) PASSTHRU RMTHOST 20.20.20.20, MGRPORT 7809 RMTTRAIL ./dirdat/cs DYNAMICRESOLUTION TABLE TEST.*;
Copy
编辑推荐：
下一篇：
相关推荐
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效
2 月 9 日消息，雷神 (THUNDEROBOT) 现已宣布推出基于英
-
制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用
2 月 10 日消息，制造商 Musnap 现已在海外推出一款 Oce
热文推荐
- 第47期 OGG DownStream 部署
第47期 OGG DownStream 部署
26-03-03 - Oracle 数据库巡检脚本（增强版）
Oracle 数据库巡检脚本（增强版）
26-03-03 - 数据库管理-第374期 23ai：弃用的功能、视图与参数（20251013）
- 破解Oracle难题：用搜索引擎+AI高效处理cursor:pin S等待事件
- 商汤科技押注“1+X”：是破局利刃，还是盲目挣扎？
商汤科技押注“1+X”：是破局利刃，还是盲目挣扎？
26-03-03 - 数据库管理-第375期 26ai：除了改名，还有什么（20251015）
数据库管理-第375期 26ai：除了改名，还有什么（20251015）
26-03-03 - 数据库深夜革命，Oracle 26ai重磅发布！
数据库深夜革命，Oracle 26ai重磅发布！
26-03-03 - 位图索引的妙用
位图索引的妙用
26-03-03 - 数据库管理-第366期 Oracle 19c 多租户 ADG 环境的陷阱（20250911）
- 百奥赛图与中国创新药的全球突围
百奥赛图与中国创新药的全球突围
26-03-03
