ogg21.3安装以及单向同步配置
一、数据库配置
1.源端、目标端数据库分别创建表空间、用户
--源端
create tablespace GOLDENGATE datafile '/u01/oradata/umpdb/goldengate01.dbf' size 100M autoextend on next 50M maxsize unlimited;
create user goldengate identified by "goldengate" default tablespace GOLDENGATE temporary tablespace TS_TEMP profile DEFAULT;
grant create session,alter session to goldengate;
grant alter system to goldengate;
grant resource to goldengate;
grant connect to goldengate;
grant select any dictionary to goldengate;
grant flashback any table to goldengate;
grant select any table to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant select on dba_clusters to goldengate;
grant execute on dbms_flashback to goldengate;
grant create table to goldengate;
grant create sequence to goldengate;
grant alter any table to goldengate;
grant dba to goldengate;
grant lock any table to goldengate;
--目标端
create tablespace GOLDENGATE datafile '/u01/oradata/umpdbbak/goldengate01.dbf' size 100M autoextend on next 50M maxsize unlimited;
create user goldengate identified by "goldengate" default tablespace GOLDENGATE temporary tablespace TS_TEMP profile DEFAULT;
grant create session,alter session to goldengate;
grant alter system to goldengate;
grant resource to goldengate;
grant connect to goldengate;
grant select any dictionary to goldengate;
grant flashback any table to goldengate;
grant select any table to goldengate;
grant insert any table to goldengate;
grant update any table to goldengate;
grant delete any table to goldengate;
grant select on dba_clusters to goldengate;
grant execute on dbms_flashback to goldengate;
grant create table to goldengate;
grant create sequence to goldengate;
grant alter any table to goldengate;
grant dba to goldengate;
grant lock any table to goldengate;
2.源端数据库设置为归档模式，并打开集中日志
sqlplus / as sysdba
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data(primary key,unique,foreign key) columns;
alter system switch logfile;
archive log list；--检查是否开启归档 --未打开
shutdown immediate;
startup mount;--启动实例
alter database archivelog;--开启归档
alter database open;--打开数据库，此模式为数据库的正常模式
archive log list；--检查是否开启归档 --已打开
3.源端、目标端分别设置参数
alter system set enable_goldengate_replication=true scope=both;
二、源端ogg安装配置（oracle用户下）
1.下载
下载ogg21.3,分别上传至源端和目标端的/u01/ogg21.3 目录下，并解压
注：该目录不是安装目录，只用作临时目录
2.解压
3.修改环境变量
vi ~/.bash_profile
添加：
export OGG_HOME=/u01/ogg
export LD_LIBRARY_PATH=$OGG_HOME:$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/lib
export PATH=$PATH:$OGG_HOME:$ORACLE_HOME/bin:/usr/sbin:/usr/local/bin:/usr/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
--立即生效
source ~/.bash_profile
4.静默安装
vi fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp
配置以下参数：
INSTALL_OPTION=ora19c
SOFTWARE_LOCATION=/u01/ogg
UNIX_GROUP_NAME=oinstall
cd /u01/ogg21.3/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/
./runInstaller -silent -nowait -responseFile /u01/ogg21.3/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp
5.进入ogg安装目录
cd /u01/ogg
进入管理台
./ggsci
登录数据库
dblogin userid goldengate@umpdb,password goldengate
create subdirs
add trandata ump.ot_message_app --添加要收集日志的表(以此为例)
登录sql查看已添加的表：
select supplemental_log_data_min,supplemental_log_data_pk,supplemental_log_data_ui,supplemental_log_data_fk,supplemental_log_data_all from v$database;
select owner,table_name,log_group_name,log_group_type, decode(always,'ALWAYS','Unconditional',NULL,'Conditional') always from dba_log_groups order by owner,table_name,log_group_name;
select * from
(select owner,table_name from dba_tables where owner in ('UMP')
minus select owner,table_name from dba_log_groups)
order by owner,table_name;
6.配置管理进程
Edit param mgr
添加以下内容：
PORT 7809
DYNAMICPORTLIST 7810-7980
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3, RESETMINUTES 60
PURGEOLDEXTRACTS ./dirdat/*,USECHECKPOINTS,MINKEEPDAYS 3
PURGEDDLHISTORY MINKEEPDAYS 7,MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
Start mgr --启动管理进程
7.配置抽取进程
ADD EXTRACT EXT_001, TRANLOG, begin now
add exttrail ./dirdat/et,extract EXT_001
register extract ext_001 database
edit param EXT_001
添加以下内容：
EXTRACT EXT_001
SETENV (ORACLE_SID =umpdb)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME="/u01/app/oracle/product/19.3.0.0/dbhome_1")
USERID goldengate@umpdb, PASSWORD goldengate
DISCARDFILE ./dirrpt/EXT_001.dsc, APPEND, MEGABYTES 100
EOFDELAYCSECS 50
FLUSHCSECS 10
--THREADOPTIONS INQUEUESIZE 2000
--WILDCARDRESOLVE DYNAMIC
--dynamicresolution
GETTRUNCATES
GETUPDATEBEFORES
GETUPDATEAFTERS
CACHEMGR CACHESIZE 2G
CHECKPOINTSECS 3
--TRANLOGOPTIONS DBLOGREADER
--TRANLOGOPTIONS CONVERTUCS2CLOBS
WARNLONGTRANS 4H, CHECKINTERVAL 30M
EXTTRAIL ./dirdat/et
DDL INCLUDE MAPPED
DDLOPTIONS ADDTRANDATA, REPORT
TABLEEXCLUDE UMP.OT_MESSAGE_AUTH;
TABLEEXCLUDE UMP.OT_MESSAGE_BATCH;
TABLEEXCLUDE UMP.OT_MESSAGE_UPLINKSM;
TABLEEXCLUDE UMP.OT_MESSAGE_MO;
TABLEEXCLUDE UMP.OT_MESSAGE_DAY_REPORT;
TABLEEXCLUDE UMP.OT_MESSAGE_MONTH_REPORT;
TABLEEXCLUDE UMP.OT_MESSAGE_YEAR_REPORT;
TABLEEXCLUDE UMP.OT_MESSAGE_SEASON_REPORT;
TABLEEXCLUDE UMP.OT_MESSAGE_YX_DAY_CTL;
TABLEEXCLUDE UMP.OT_MESSAGE_YX_HIS_20190902;
TABLE UMP.PUSHMESSAGEDETAIL;
TABLE UMP.PUSHMESSAGEDETAIL_HIS;
TABLE UMP.OT_MESSAGE_APP;
TABLE UMP.OT_MESSAGE_APP_HIS;
TABLE UMP.PUSHMESSAGEDETAIL_TAG;
TABLE UMP.PUSHMESSAGEDETAIL_TAG_HIS;
TABLE UMP.PUSHMESSAGEDETAIL_YW;
TABLE UMP.PUSHMESSAGEDETAIL_YW_HIS;
8.配置投递进程
ADD EXTRACT PMP_001, exttrailsource ./dirdat/et
ADD rmttrail ./dirdat/et,extract PMP_001,megabytes 500
edit param PMP_001
添加以下内容：
EXTRACT pmp_001
PASSTHRU
RMTHOST 38.62.89.35, MGRPORT 7809
RMTTRAIL ./dirdat/et
DYNAMICRESOLUTION
GETTRUNCATES
TABLE UMP.PUSHMESSAGEDETAIL;
TABLE UMP.PUSHMESSAGEDETAIL_HIS;
TABLE UMP.OT_MESSAGE_APP;
TABLE UMP.OT_MESSAGE_APP_HIS;
TABLE UMP.PUSHMESSAGEDETAIL_TAG;
TABLE UMP.PUSHMESSAGEDETAIL_TAG_HIS;
TABLE UMP.PUSHMESSAGEDETAIL_YW;
TABLE UMP.PUSHMESSAGEDETAIL_YW_HIS;
如果报错：ERROR OGG-02022 Logmining server does not exist on this Oracle database.
则执行命令:register extract ext_001 database
9.启动抽取、投递进程
Start EXT_001
Start PMP_001
注：如果有进程没有启动，可以执行view report EXT_001 查看日志
三、目标端ogg安装配置（oracle用户下）
1.与源端配置1~4相同
2.进入ogg
Cd /u01/ogg
./ggsci
dblogin userid goldengate@umpdbbak,password goldengate
create subdirs
3.配置管理进程
edit param mgr
添加以下内容：
PORT 7809
Start mgr
4.配置检查点
add checkpointtable goldengate.checkpoint
sh netstat -ntpl |grep 7809
sh ps -ef|grep mgr |grep -v grep
5.配置复制进程
add replicat R_UMP,exttrail ./dirdat/et,checkpointtable goldengate.checkpoint
edit param R_UMP
添加以下内容：
REPLICAT r_ump
SETENV (ORACLE_SID = umpdbbak)
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate@umpdbbak, PASSWORD goldengate
REPERROR DEFAULT, ABEND
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/r_ump.dsc, APPEND, MEGABYTES 1024
DDL INCLUDE MAPPED
--WILDCARDRESOLVE DYNAMIC
--Dynamicresolution
ALLOWNOOPUPDATES
HANDLECOLLISIONS
REPORT AT 02:00
reportrollover at 02:00
DDLOPTIONS REPORT
GETTRUNCATES
--CHECKSEQUENCEVALUE
MAP UMP.PUSHMESSAGEDETAIL, TARGET UMP.PUSHMESSAGEDETAIL;
MAP UMP.PUSHMESSAGEDETAIL_HIS, TARGET UMP.PUSHMESSAGEDETAIL_HIS;
MAP UMP.OT_MESSAGE_APP, TARGET UMP.OT_MESSAGE_APP;
MAP UMP.OT_MESSAGE_APP_HIS, TARGET UMP.OT_MESSAGE_APP_HIS;
MAP UMP.PUSHMESSAGEDETAIL_TAG, TARGET UMP.PUSHMESSAGEDETAIL_TAG;
MAP UMP.PUSHMESSAGEDETAIL_TAG_HIS, TARGET UMP.PUSHMESSAGEDETAIL_TAG_HIS;
MAP UMP.PUSHMESSAGEDETAIL_YW, TARGET UMP.PUSHMESSAGEDETAIL_YW;
MAP UMP.PUSHMESSAGEDETAIL_YW_HIS, TARGET UMP.PUSHMESSAGEDETAIL_YW_HIS;
6.启动复制进程
Start R_UMP
- 以上配置完成后，可测试数据同步
四、可能出现的问题及排查
抽取进程启动成功后抽取失败导致数据不同步问题
先删除现有的extract抽取进程，然后重新创建
重新创建后，会存在抽取进程写入的trail文件序号与投递进程的读取trail文件序号不同，需要保证抽取进程、投递进程、复制进程的trail文件序号一致。
上图就是序号不一致导致的同步失败，可以通过重启进程、修改文件名等操作来保证序号一致。
以上是重新创建抽取进程的过程，重新创建投递、复制进制也类似，在此不做列出。
导致ogg同步失败的另一个原因是源库与目标库初始数据有差异，可通过以下操作保持数据一致：
1.expdp/impdp数据泵，在导出数据之前应确保要同步的表没有业务数据写入，必要时可以把数据库设置为只读模式。
2.dblink,针对少量几张表可以用dblink同步
3.复制进程配置HANDLECOLLISIONS参数（生产慎用），这个参数的作用简单来说是操作目标端没有的数据时，先执行插入操作。
标签：同步,UMP,grant,--,PUSHMESSAGEDETAIL,单向,ogg21.3,table,goldengate From： https://blog.csdn.net/qq_20665933/article/details/144381467