OGG安装部署步骤[Oracle to Oracle]

原创已于 2025-09-18 17:27:26 修改 · 680 阅读

11 ·

本内容遵循CC 4.0 BY-SA版权协议

GEO检测

标签

#oracle #数据库

于 2025-09-18 13:59:12 首次发布

OGG技术分享专栏收录该内容

7 篇文章

订阅专栏

1.背景描述

Golden Gate（OGG）作为一款成熟的数据复制与集成软件，其核心价值在于跨异构环境的实时数据同步能力。它通过解析数据库日志精准捕获数据变更，并以低延迟（通常可在亚秒级至数秒内）将变化应用于目标端，确保数据的高一致性和完整性。

凭借优异的实时性能和灵活的拓扑支持，OGG广泛应用于以下关键业务场景：

高可用与容灾保障：构建主备数据库实时同步，实现快速故障切换与业务连续性。
实时数据分析和报表：将在线交易数据同步至分析库或数据平台，支撑实时BI和决策看板。
数据仓库与湖仓供给：持续、高效地向数据仓库、数据湖输送事务数据，保持离线与在线数据同步。
查询负载分离：将读写流量分担至备库，减轻生产库压力，提升系统整体性能。
云端与混合云数据同步：实现本地到云、云与云之间、不同数据库类型之间的数据迁移和集成。
微服务数据协同与事件驱动：在分布式架构中可靠传递数据变更，支持服务间数据共享和消息发布。

此外，OGG提供高度灵活的部署方式，可支持一对一、一对多（广播）、多对一（聚合）、双向同步以及级联复制等多种拓扑结构，适应不同企业架构与数据流需求。

本次实验源端为192.168.0.132，目标端为192.168.0.154

2.前期配置

2.1 源库与目标库的配置（oracle数据库）

2.1.1 源库配置

数据库开启归档模式

SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;

开启强制记录日志模式

SQL> alter database force logging;

开启附加日志

SQL> alter database add supplemental log data;

查看结果

SQL> select LOG_MODE,SUPPLEMENTAL_LOG_DATA_MIN, FORCE_LOGGING from v$database;

前期准备
在数据库上创建goldengate用户并赋权，用于OGG同步

SQL> create tablespace goldengate datafile '/u01/app/oracle/oradata/ORCL/datafile/goldengate01.dbf' size 300m autoextend on ;
SQL> create user goldengate identified by goldengate default tablespace goldengate;
SQL> grant connect,resource,create session,alter session to goldengate;
SQL> grant flashback any table to goldengate; 
SQL> exec dbms_goldengate_auth.grant_admin_privilege('GOLDENGATE'); 
SQL> grant select any dictionary to goldengate;
SQL> alter user goldengate quota unlimited on goldengate;

开启enable_goldengate_replication

SQL> alter system set enable_goldengate_replication = true scope=both;

2.1.2 目标库配置

目标库不需开附加日志，创建用户及权限和源库一致

SQL> create tablespace goldengate datafile '/u01/app/oracle/oradata/ORCL/datafile/goldengate01.dbf' size 300m autoextend on ;
SQL> create user goldengate identified by goldengate default tablespace goldengate;
SQL> grant connect,resource,create session,alter session to goldengate;
SQL> grant flashback any table to goldengate;  
SQL> exec dbms_goldengate_auth.grant_admin_privilege('GOLDENGATE'); 
SQL> grant select any dictionary to goldengate;
SQL> alter user goldengate quota unlimited on goldengate;

另外需要以下权限：

SQL> grant insert any table,update any table,delete any table, drop any table, create any table,alter any table to goldengate;

开启enable_goldengate_replication

SQL> alter system set enable_goldengate_replication = true scope=both;

3.软件安装

[oracle@rac12c1 ~]$ vi /soft/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

INSTALL_OPTION= ORA12c
SOFTWARE_LOCATION=/u01/app/ogg
START_MANAGER=false

[oracle@rac12c1 ~]$ cd /soft/fbo_ggs_Linux_x64_shiphome/Disk1
[oracle@rac12c1 ~]$ ./runInstaller -silent -responseFile /soft/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp

4.OGG配置

4.1 初始化配置

进入命令界面，创建Goldengate 子目录（源、目标都要操作）

[oracle@rac12c1 ~]$ /u01/app/ogg/ggsci 

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.2.2 OGGCORE_12.2.0.2.0_PLATFORMS_170630.0419_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Jun 30 2017 16:12:28
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.



GGSCI (rac12c1) 1> create subdirs

Creating subdirectories under current directory /u01/app/ogg

Parameter files                /u01/app/ogg/dirprm: already exists
Report files                   /u01/app/ogg/dirrpt: already exists
Checkpoint files               /u01/app/ogg/dirchk: already exists
Process status files           /u01/app/ogg/dirpcs: already exists
SQL script files               /u01/app/ogg/dirsql: already exists
Database definitions files     /u01/app/ogg/dirdef: already exists
Extract data files             /u01/app/ogg/dirdat: already exists
Temporary files                /u01/app/ogg/dirtmp: already exists
Credential store files         /u01/app/ogg/dircrd: already exists
Masterkey wallet files         /u01/app/ogg/dirwlt: already exists
Dump files                     /u01/app/ogg/dirdmp: already exists

4.2 源端和目标端checkpoint表及MGR进程配置

配置全局参数文件（源、目标都要操作）

GGSCI (rac12c1) 2> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.ggschkpt
SYSLOG NONE

用goldengate用户连接数据库（源、目标都要操作）

GGSCI (rac12c1) 4> dblogin userid goldengate password goldengate;
Successfully logged into database.

在数据库生成checkpoint表（源、目标都要操作）

GGSCI (rac12c1 as goldengate@rac11) 6> add CHECKPOINTTABLE goldengate.ggschkpt                                   

Successfully created checkpoint table goldengate.ggschkpt.

编辑MGR进程参数（源、目标都要操作）

GGSCI (rac12c1 as goldengate@rac11) 7> edit params mgr
PORT 7809
DYNAMICPORTLIST 7800-7810
USERID goldengate,PASSWORD goldengate
AUTORESTART ER *,RETRIES 3, WAITMINUTES 5, RESETMINUTES 60
LAGREPORTMINUTES 10
LAGCRITICALMINUTES 10
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 12
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYHOURS 3
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 14, FREQUENCYHOURS 3

启动MGR（源、目标都要操作）

GGSCI (rac12c1 as goldengate@rac11) 7> start mgr
Manager started.

4.3 源端创建测试表

SQL> conn fuse
Enter password: 
Connected.

SQL> create table data1 as select * from user_objects;

Table created.

SQL> select count(*) from fuse.data1;

  COUNT(*)
----------
     89105

SQL> alter table data1 add constraint pk_data1_id primary key(object_id);

Table altered.

SQL>

4.4 源端配置DDL同步

赋予权限（源端）

[oracle@rac12c1 ~]$ cd /u01/app/ogg/
[oracle@rac12c1 ogg]$
SQL> conn / as sysdba
Connected.

SQL> grant execute on utl_file  to goldengate;

SQL> @@marker_setup
Enter Oracle GoldenGate schema name:goldengate

SQL> @@ddl_setup
Enter Oracle GoldenGate schema name:goldengate

SQL> @@role_setup
Enter Oracle GoldenGate schema name:goldengate

SQL> GRANT GGS_GGSUSER_ROLE TO GoldenGate;

SQL> @@ddl_enable

4.5 添加表级附加日志

添加表级附加日志（源端）

GGSCI (rac12c1) 1> dblogin userid goldengate password goldengate;
Successfully logged into database.

GGSCI (rac12c1 as goldengate@orcl) 2> add trandata FUSE.DATA1

Logging of supplemental redo data enabled for table FUSE.DATA1.
TRANDATA for scheduling columns has been added on table 'FUSE.DATA1'.
TRANDATA for instantiation CSN has been added on table 'FUSE.DATA1'.
GGSCI (rac12c1 as goldengate@orcl) 3>

4.6 源端添加Extract进程

GGSCI (rac12c1 as goldengate@orcl) 1> edit param ext_data
EXTRACT ext_data
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.2.0/dbhome_1")
SETENV (ORACLE_SID="rac11")
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate,PASSWORD goldengate
EXTTRAIL ./dirdat/gs
TRANLOGOPTIONS  DBLOGREADER
BR ,BRINTERVAL 20M
GETREPLICATES
CACHEMGR CACHESIZE 2GB
GETTRUNCATES
DDL INCLUDE MAPPED , OBJTYPE 'TABLE' &
INCLUDE MAPPED OBJTYPE 'INDEX'
DDLOPTIONS ADDTRANDATA RETRYOP RETRYDELAY 10 MAXRETRIES 10
DDLOPTIONS REPORT
DISCARDFILE ./dirrpt/ext_data.dsc,APPEND,MEGABYTES 1000
DISCARDROLLOVER AT 6:00
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS,RATE
FETCHOPTIONS FETCHPKUPDATECOLS
REPORT AT  0:01
FETCHOPTIONS MISSINGROW ABEND
STATOPTIONS REPORTFETCH
WARNLONGTRANS 1H,CHECKINTERVAL 10m
DYNAMICRESOLUTION
TABLE fuse.data1;

GGSCI (rac12c1 as goldengate@orcl) 2> add extract ext_data,tranlog,threads 1,begin now

GGSCI (rac12c1 as goldengate@orcl) 3> add exttrail ./dirdat/gs, extract ext_data, megabytes 1024

GGSCI (rac12c1 as goldengate@orcl) 4> start ext_data

4.7 源端添加DataPump进程

GGSCI (rac12c1 as goldengate@orcl) 4>  edit param dpg_data

EXTRACT dpg_data
PASSTHRU
DYNAMICRESOLUTION
RMTHOST 192.168.0.154,MGRPORT 7809
RMTTRAIL ./dirdat/gs
DISCARDFILE ./dirrpt/dpg_data.dsc,APPEND,MEGABYTES 100
DISCARDROLLOVER AT 6:00
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 1 HOURS,RATE
TABLE fuse.data1;

添加DataPump进程，指定使用Trail文件的位置

GGSCI (rac12c1 as goldengate@orcl) 61> add extract dpg_data, exttrailsource ./dirdat/gs

GGSCI (rac12c1 as goldengate@orcl) 62> add RMTTRAIL ./dirdat/gs, extract dpg_data, megabytes 1024

GGSCI (rac12c1 as goldengate@orcl) 63> start dpg_data

4.8 目标端添加Replicat进程

GGSCI > edit param rep_data

REPLICAT rep_data
SETENV (ORACLE_HOME = "/u01/app/oracle/product/12.1/db_1" )
SETENV (ORACLE_SID = "ORCLOGG")
SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
USERID goldengate password goldengate
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
DISCARDFILE ./dirrpt/rep_data.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 6:00
REPERROR (DEFAULT, ABEND)  
DDL INCLUDE MAPPED , OBJTYPE 'TABLE' &
INCLUDE MAPPED OBJTYPE 'INDEX'
DDLOPTIONS REPORT
REPORTROLLOVER AT 6:00
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORT AT  0:01
STATOPTIONS RESETREPORTSTATS
NUMFILES 150
GETTRUNCATES
DYNAMICRESOLUTION
ALLOWNOOPUPDATES
GROUPTRANSOPS 1000
MAP fuse.data1 , TARGET fuse.data1 ;

add replicat rep_data,exttrail ./dirdat/gs,checkpointtable goldengate.ggschkpt

5.初始化数据并启用复制进程

源库查看当前SCN

SQL> select to_char(current_scn) from v$database; 

CURRENT_SCN
-----------
1085057

导出源表数据

expdp fuse/fuse directory=expdir TABLES=fuse.data1 DUMPFILE=data1.dmp flashback_scn=185021713

dump文件拷贝到目标端，导入数据

impdp fuse/fuse dumpfile=data1.dmp directory=impdir

目标端开启复制进程，OGG配置完成

GGSCI > start replicat rep_data,aftercsn 185021713