本文详述了Oracle GoldenGate（OGG）的搭建部署过程，包括在CentOS 7.6上创建OGG用户，设置环境变量，安装软件，配置数据库环境，如开启归档模式，创建并授权Goldengate用户，以及OGG链路的配置，包括MANAGER、抽取、投递和复制进程。同时，文章还列举并解决了在OGG配置过程中常见的错误问题，如Address already in use和数据库登录信息未指定等。
前言
OGG用于Oracle数据库间，进行数据同步。
搭建环境：
操作系统：centos7.6
两台服务器（均部署了单节点的Oracle11G）
一、安装goldengate软件
OGG软件 在源端数据库所在服务器和目标端数据库所在服务器 都要安装。
1.1创建ogg用户
在操作系统中，创建ogg用户。
useradd -g dba -G oinstall -d /home/ogg ogg
1.2设置环境变量
可以在原来Oracle数据库的环境变量基础上进行修改：
cp /home/oracle/.bash_profile /home/ogg/
chown ogg:dba /home/ogg/.bash_profile
vim /home/ogg/.bash_profile
附件：我的配置文件如下最后一行是安装ogg添加的环境变量，其他的都是沿用的Oracle /home/oracle/.bash_profile 下的配置
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
ORACLE_SID=orcl
PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME ORACLE_SID PATH
#配置OGG添加
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib:$ORACLE_HOME/jdk/jre/lib/amd64/server
1.3.安装OGG软件
创建软件目录 /goldengate，赋权
mkdir /goldengate
chown -R ogg:dba /goldengate
上传ogg软件,解压文件
tar -xvf fbo_ggs_Linux_x64_ora11g_64bit.tar
1.4 OGG目录创建
[ogg@iZ2zehk9yepi9owu3yezm6Z goldengate]$./ggsci
GGSCI (ogg) 3> create subdirs （会自动创建）
Creating subdirectories under current directory /u01/ogg
Parameter files /u01/ogg/dirprm: already exists
Report files /u01/ogg/dirrpt: created
Checkpoint files /u01/ogg/dirchk: created
Process status files /u01/ogg/dirpcs: created
SQL script files /u01/ogg/dirsql: created
Database definitions files /u01/ogg/dirdef: created
Extract data files /u01/ogg/dirdat: created
Temporary files /u01/ogg/dirtmp: created
Stdout files /u01/ogg/dirout: created
二、数据库环境准备
2.1开启数据库归档模式
检查数据库是否归档模式，如果不是修改为归档模式（下图所示的是已经开启归档）。
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +ARCHDG
Oldest online log sequence 752673
Next log sequence to archive 752675
Current log sequence 752676
1)sql>shutdown immediate;
2)sql>startup mount;
3)sql>alter database archivelog;
4)sql>alter database open;
5)sql>archive log list;
没开启归档的话如下所示：
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 9
Current log sequence 11
2.2启动数据库附加日志
开启数据库追加模式
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL,force_logging from v$database;
Alter database add supplemental log data;
Alter database add supplemental log data(primary key,unique index) columns;
Alter database force logging;
2.3创建数据库goldengate用户
SQL> create user ggs identified by ggs;
2.4 Goldengate赋权
可以直接赋予个dba权限 grant dba to ggs;或者按照下面给权限
源端：
grant CONNECT, RESOURCE to ggs ;
grant CREATE SESSION, ALTER SESSION 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 SELECT ON DBA_CLUSTERS to ggs ;
grant SELECT ANY TRANSACTION to ggs ;
目标端：
grant CONNECT, RESOURCE to ggs ;
grant CREATE SESSION, ALTER SESSION to ggs ;
grant SELECT ANY DICTIONARY, SELECT ANY TABLE to ggs ;
grant CREATE TABLE to ggs ;
grant INSERT ANY TABLE to ggs ;
grant UPDATE ANY TABLE to ggs ;
grant DELETE ANY TABLE to ggs ;
三、OGG链路配置
3.1MANAGER（管理）进程配置
配置MGR参数文件
[ogg@iZ2zehk9yepi9owu3yezm6Z goldengate]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 21 2011 22:42:14
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 1> info all
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 2> create subdirs ==**如果前面没有创建目录的话先创建一下，不然后面配置文件会报错找不到目录**==
Creating subdirectories under current directory /goldengate
Parameter files /goldengate/dirprm: created
Report files /goldengate/dirrpt: created
Checkpoint files /goldengate/dirchk: created
Process status files /goldengate/dirpcs: created
SQL script files /goldengate/dirsql: created
Database definitions files /goldengate/dirdef: created
Extract data files /goldengate/dirdat: created
Temporary files /goldengate/dirtmp: created
Veridata files /goldengate/dirver: created
Veridata Lock files /goldengate/dirver/lock: created
Veridata Out-Of-Sync files /goldengate/dirver/oos: created
Veridata Out-Of-Sync XML files /goldengate/dirver/oosxml: created
Veridata Parameter files /goldengate/dirver/params: created
Veridata Report files /goldengate/dirver/report: created
Veridata Status files /goldengate/dirver/status: created
Veridata Trace files /goldengate/dirver/trace: created
Stdout files /goldengate/dirout: created
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 3> start manager ==直接启动管理进程的话，会报错 mgr配置文件不存在==
ERROR: Parameter file /goldengate/dirprm/mgr.prm does not exist.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 4> start mgr ==管理进程启动，start manager和start mgr 都可以==
ERROR: Parameter file /goldengate/dirprm/mgr.prm does not exist.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 6> edit params mgr ==编辑管理进程的配置文件，详细配置内容在下面==
==。注意：这个地方编辑的文件一定是mgr,不能是manager==
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 7> start manager ==编辑完成之后启动管理进程==
Manager started.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 8> info all ==查看进程状态==
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
管理进程配置文件如下（可以不用改）：
port 7809
dynamicportlist 7810-8000
autorestart er *, retries 5, waitminutes 3
purgeoldextracts ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 10
LAGCRITICALMINUTES 15
3.2抽取进程
源端配置抽取进程参数文件
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 11> edit params ehx2al
抽取进程参数文件如下：
extract ehx2al
SETENV (ORACLE_SID=oraaa) #此处为源端数据库实例名
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ggs ,password ggs
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
EXTTRAIL ./dirdat/ed
NUMFILES 3000
EOFDELAYCSECS 30
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
--BR BRINTERVAL 2H , BRDIR BR
GETUPDATEBEFORES
GETTRUNCATES
--NOCOMPRESSDELETES / COMPRESSDELETES
--WARNLONGTRANS 3H, CHECKINTERVAL 3M
table socct.OGG_TB;
源端添加配置进程
GGSCI > add extract ehx2al,tranlog,threads 1,begin now
GGSCI > add exttrail ./dirdat/ed,extract ehx2al,megabytes 200
3.3投递进程
源端配置投递进程参数文件
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 11> edit params phx2al
投递进程参数文件如下（要更改目标服务器的IP）：
extract phx2al
SETENV (ORACLE_SID=oraaa) #此处为源端数据库实例名
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD ggs
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
FLUSHCSECS 30
NUMFILES 3000
EOFDELAYCSECS 30
RMTHOST ********,MGRPORT 7809, TCPBUFSIZE 100000, TCPFLUSHBYTES 300000 #****是指的目标服务器的IP地址
RMTTRAIL ./dirdat/pd
GETTRUNCATES
PASSTHRU （pass through）
DYNAMICRESOLUTION
GETUPDATEBEFORES
table socct.OGG_TB;
源端添加配置进程
GGSCI > Add extract phx2al exttrailsource ./dirdat/ed
GGSCI > Add rmttrail ./dirdat/pd extract phx2al megabytes 200
3.4 复制进程
目标端复制（应用）进程配置。
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 11> edit params rhx2al
复制进程配置文件内容如下：
replicat rhx2al
SETENV (ORACLE_SID=orcl)
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD ggs
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND /DISCARD
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
GETTRUNCATES
NUMFILES 3000
EOFDELAYCSECS 30
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
GETUPDATEBEFORES
GROUPTRANSOPS 1
MAXTRANSOPS 1
HANDLECOLLISIONS
MAP socct.ogg_tb, TARGET jn_hs.ogg_tb;
添加复制进程
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 11> edit params rhx2al ==rhx2al为复制进程的名字==
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 12> info all ==查看目前进程情况==
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 13> add replicat rhx2al,exttrail ./dirdat/pd, checkpointtable
=添加复制进程，提示找不到检 查点的表，用下面的命令添加==
ERROR: Missing checkpoint TABLE entry.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 14> ADD REPLICAT rhx2al,EXTTRAIL ./dirdat/pd,checkpointtable ggs.checkpoint
REPLICAT added.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 15> info all ==查看进程状态==
Program Status Group Lag Time Since Chkpt
MANAGER STOPPED
REPLICAT STOPPED RHX2AL 00:00:00 00:00:03
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 17> start manager ==启动管理进程==
Manager started.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 18> start rhx2al ==启动复制进程==
Sending START request to MANAGER ...
REPLICAT RHX2AL starting
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 19> info all ==发现没起来==
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED RHX2AL 00:00:00 00:00:38
发现复制进程 rhx2al 没有起来，执行view report rhx2al，查看报错情况：
报错情况：直接看后面的error
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 20> view report rhx2al
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 11.1.1.1 OGGCORE_11.1.1_PLATFORMS_110421.2040
Linux, x64, 64bit (optimized), Oracle 11g on Apr 30 2011 18:57:18
Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.
Starting at 2020-05-15 10:37:05
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Tue Jun 18 16:35:19 UTC 2019, Release 3.10.0-957.21.3.el7.x86_64
Node: iZ2zehk9yepi9owu3yezm6Z
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 10847
Description:
***********************************************************************
** Running with the following parameters **
***********************************************************************
replicat rhx2al
SETENV (ORACLE_SID=orcl)
Set environment variable (ORACLE_SID=orcl)
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Userid ggs,PASSWORD ***
REPORT AT 01:59
REPORTROLLOVER AT 02:00
CACHEMGR, CACHESIZE 256MB
REPERROR DEFAULT,ABEND /DISCARD
2020-05-15 10:37:05 WARNING OGG-00364 REPERROR clause /DISCARD is not valid with DEFAULT/DEFAULT2. Parameter ignored.
DISCARDFILE ./dirrpt/extya.dsc, APPEND, MEGABYTES 1024
GETTRUNCATES
NUMFILES 3000
EOFDELAYCSECS 30
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DYNAMICRESOLUTION
GETUPDATEBEFORES
GROUPTRANSOPS 1
MAXTRANSOPS 1
HANDLECOLLISIONS
MAP scott.ogg_tb, TARGET jn_hs.ogg_tb;
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 256M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 512M
CACHESIZEMAX (strict force to disk): 433M
Database Version:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
Database Language and Character Set:
NLS_LANG = "AMERICAN_AMERICA.AL32UTF8"
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "ZHS16GBK"
Warning: your NLS_LANG setting does not match database server language setting.
Please refer to user manual for more information.
Source Context :
SourceModule : [er.main]
SourceID : [/scratch/sganti/view_storage/sganti_core_lin64/oggcore/OpenSys/src/app/er/rep.c]
SourceFunction : [extract_start_point]
SourceLine : [12948]
ThreadBacktrace : [7] elements
: [/goldengate/replicat(CMessageContext::AddThreadContext()+0x26) [0x5ecf16]]
: [/goldengate/replicat(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x5e39e2]]
: [/goldengate/replicat(_MSG_ERR_CHECKPOINT_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x92) [0x58f002]]
: [/goldengate/replicat() [0x4f40ed]]
: [/goldengate/replicat(main+0x52f) [0x506eef]]
: [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7f9d61610495]]
: [/goldengate/replicat(__gxx_personality_v0+0x1da) [0x4e399a]]
2020-05-15 10:37:05 ERROR OGG-00446 Checkpoint table GGS.CHECKPOINT does not exist. Please create the table or recreate the RHX2AL group using the correct table.
2020-05-15 10:37:05 ERROR OGG-01668 PROCESS ABENDING.
查看最后报错，发现是没有检查表的事儿。所以在目标数据库，建立检查表
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 31> dblogin userid ggs password ggs ==连接目标数据库 用户为ggs 密码为***==
Successfully logged into database.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 32> add checkpointtable ggs.checkpoint ==添加检查表==
Successfully created checkpoint table GGS.CHECKPOINT.
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 33> start rhx2al
Sending START request to MANAGER ...
REPLICAT RHX2AL starting
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 34> info all ==正常启动了==
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RHX2AL 00:00:00 00:00:01
3.5测试同步情况
源端数据库插入一条数据，目标端数据自动同步
四、报错信息汇总
4.1ERROR OGG-01224 Address already in use.
具体报错信息：
Source Context :
SourceModule : [mgr.main]
SourceID : [/scratch/aime1/adestore/views/aime1_dadvfg0702/oggcore/OpenSys/src/app/mgr/mgr.c]
SourceFunction : [init_functions]
SourceLine : [3361]
ThreadBacktrace : [8] elements
: [./mgr(CMessageContext::AddThreadContext()+0x26) [0x60c9a6]]
: [./mgr(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x603472]]
: [./mgr(_MSG_ERR_TCP_GENERIC(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x92) [0x5ddb62]]
: [./mgr(init_functions(int, char**)+0x916) [0x4c3bd6]]
: [./mgr(main_loop(int, char**)+0x42) [0x4cdd62]]
: [./mgr(main+0x1dc) [0x4ceebc]]
: [/lib64/libc.so.6(__libc_start_main+0x100) [0x383b81ed20]]
: [./mgr(__gxx_personality_v0+0x162) [0x4bab6a]]
2020-05-14 10:48:04 ERROR OGG-01224 Address already in use.
2020-05-14 10:48:04 ERROR OGG-01668 PROCESS ABENDING.
解决办法：
原因是之前启动了一个进程，杀掉，重新启动即可
[root@ogg~]# ps -ef | grep mgr
root 11 2 0 10:02 ? 00:00:00 [async/mgr]
postfix 1674 1667 0 10:03 ? 00:00:00 qmgr -l -t fifo -u
ogg 1981 1 0 10:09 ? 00:00:00 ./mgr PARAMFILE /goldengate/dirprm/mgr.prm REPORTFILE /goldengate/dirrpt/MGR.rpt PROCESSID MGR
root 2447 2426 0 10:52 pts/1 00:00:00 grep mgr
[root@ogg~]# kill -9 1981
4.2:OGG-01453 Database login information not specified in parameter file.
具体报错信息如下：
Bounded Recovery Parameter:
Options = BRRESET
BRINTERVAL = 4HOURS
BRDIR = /goldengate
CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 256M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 512M
CACHESIZEMAX (strict force to disk): 433M
Source Context :
SourceModule : [er.main]
SourceID : [/scratch/sganti/view_storage/sganti_core_lin64/oggcore/OpenSys/src/app/er/rep.c]
SourceFunction : [main]
SourceLine : [26012]
ThreadBacktrace : [6] elements
: [/goldengate/extract(CMessageContext::AddThreadContext()+0x26) [0x66a416]]
: [/goldengate/extract(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x7b2) [0x660ee2]]
: [/goldengate/extract(_MSG_ERR_STARTUP_DB_CONN_INFO_UNSPECIFIED(CSourceContext*, CMessageFactory::MessageDisposition)+0x87) [0x648977]]
: [/goldengate/extract(main+0xe11) [0x526111]]
: [/lib64/libc.so.6(__libc_start_main+0x100) [0x383b81ed20]]
: [/goldengate/extract(__gxx_personality_v0+0x1f2) [0x4f2bda]]
2020-05-14 11:16:57 ERROR OGG-01453 Database login information not specified in parameter file.
2020-05-14 11:16:57 ERROR OGG-01668 PROCESS ABENDING.
投递进程文件配置错误，未配置数据库相关信息
解决办法：
SETENV (ORACLE_SID=oraaa) #此处为源端数据库实例名
Userid ggs,PASSWORD ggs
4.3 运行状态都是running ，但是投递进程P不能够将数据发送
view report phx2aj 查看警告信息如下：
Waiting at EOF on input trail file ./dirdat/ed000003, which is not marked as complete; but succeeding trail file ./dirdat/ed000004 exists. If ALTERETROLLOVER has been performed on source extract, ALTER EXTSEQNO must be performed on each corresponding downstream reader.
解决办法：
上面文件到00003，我跳过这个文件，从4开始投递、复制
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 54> alter extract phx2al extseqno 4 extrba 0 = =源端OGG执行
GGSCI (iZ2zehk9yepi9owu3yezm6Z) 54> alter replicat rhx2al,extseqno 0,extrba 0 = =目标端OGG执行
五、常用简单命令
- info all --查看所有进程
- start 进程名称 --启动进程
- stop 进程名称 --停止进程
- info 进程名称 --查看该进程的状态 可以看一下处理到哪个文件、RBA号
- edit params 进程名称 --编辑进程配置文件
- view params 进程名称 --查看进程配置文件
- delete 进程名称 --删除进程
- view report 进程名称 --查看进程状态报告 当OGG不能正常同步数据时，排查报错情况
扫一扫
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
