OGG安装部署
配置环境变量
源端目标端配置环境变量
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
PATH中加入$LD_LIBRARY_PATH
unset USERNAME
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db
export OGG_HOME=/goldengate
export ORACLE_SID=testdb
export ORACLE_TERM=xterm
export ORACLE_OWNER=oracle
export ORACLE_UNQNAME=testdb
export TNS_ADMIN=$ORACLE_HOME/network/admin
#export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
#export ORA_NLS10=$ORACLE_HOME/nls/data
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$PATH:/sbin:/usr/lbin:/usr/sbin:$JAVA_HOME/bin:$ORACLE_HOME/bin:$LD_LIBRARY_PATH:$OGG_HOME:.
export TMPDIR=/tmp
umask 022
修改GOLDENGATE参数
alter system set enable_goldengate_replication=true scope=both sid='*';
创建GOLDENGATE用户
create tablespace goldengate datafile '/oradata/PAYDB/datafile/goldengate01.dbf' size 1024m autoextend on;
create user goldengate identified by goldengate default tablespace goldengate;
grant connect,resource,dba to goldengate;
安装包准备
191004_fbo_ggs_Linux_x64_shiphome
unzip 191004_fbo_ggs_Linux_x64_shiphome
ls
fbo_ggs_Linux_x64_shiphome
编辑response文件
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/goldengate
START_MANAGER=false
cd fbo_ggs_Linux_x64_shiphome/Disk1/response
more oggcore.rsp
####################################################################
## Copyright(c) Oracle Corporation 2019. All rights reserved. ##
## ##
## Specify values for the variables listed below to customize ##
## your installation. ##
## ##
## Each variable is associated with a comment. The comment ##
## can help to populate the variables with the appropriate ##
## values. ##
## ##
## IMPORTANT NOTE: This file should be secured to have read ##
## permission only by the oracle user or an administrator who ##
## own this installation to protect any sensitive input values. ##
## ##
####################################################################
#-------------------------------------------------------------------------------
# Do not change the following system generated value.
#-------------------------------------------------------------------------------
oracle.install.responseFileVersion=/oracle/install/rspfmt_ogginstall_response_schema_v19_1_0
################################################################################
## ##
## Oracle GoldenGate installation option and details ##
## ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the installation option.
# Specify ORA19c for installing Oracle GoldenGate for Oracle Database 19c or
# ORA18c for installing Oracle GoldenGate for Oracle Database 18c or
# ORA12c for installing Oracle GoldenGate for Oracle Database 12c or
# ORA11g for installing Oracle GoldenGate for Oracle Database 11g
#-------------------------------------------------------------------------------
INSTALL_OPTION=ORA11g
#-------------------------------------------------------------------------------
# Specify a location to install Oracle GoldenGate
#-------------------------------------------------------------------------------
SOFTWARE_LOCATION=/goldengate
#-------------------------------------------------------------------------------
# Specify true to start the manager after installation.
#-------------------------------------------------------------------------------
START_MANAGER=false
#-------------------------------------------------------------------------------
# Specify a free port within the valid range for the manager process.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
MANAGER_PORT=
#-------------------------------------------------------------------------------
# Specify the location of the Oracle Database.
# Required only if START_MANAGER is true.
#-------------------------------------------------------------------------------
DATABASE_LOCATION=
################################################################################
## ##
## Specify details to Create inventory for Oracle installs ##
## Required only for the first Oracle product install on a system. ##
## ##
################################################################################
#-------------------------------------------------------------------------------
# Specify the location which holds the install inventory files.
# This is an optional parameter if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
INVENTORY_LOCATION=
#-------------------------------------------------------------------------------
# Unix group to be set for the inventory directory.
# This parameter is not applicable if installing on
# Windows based Operating System.
#-------------------------------------------------------------------------------
UNIX_GROUP_NAME=
软件安装
./runInstaller -silent -responseFile /soft/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
GGSCI () 6> CREATE SUBDIRS
配置数据库密码加密
GGSCI () 3> ENCRYPT PASSWORD goldengate BLOWFISH ENCRYPTKEY DEFAULT
Using Blowfish encryption with DEFAULT key.
Encrypted password: AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC
Algorithm used: BLOWFISH
源端
开启强制日志
select supplemental_log_data_min min,
supplemental_log_data_pk pk,
supplemental_log_data_ui ui,
supplemental_log_data_fk fk,
supplemental_log_data_all,
log_mode,
force_logging,
db_unique_name,
inst_id
from gv$database;
alter database force logging;
alter database add supplemental log data;
alter database add supplemental log data (primary key, unique,foreign key) columns;
alter system archive log current;
MGR配置
GGSCI () 3> view param mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
--AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7
USERID goldengate , password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
创建DDL触发器
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @ddl_enable.sql
添加表级附加日志
GGSCI () 2> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI () 3>
add trandata PAYO_SETTLEMENT.*
add trandata PAYO_TT.*
add trandata PAYMENT_XK01.*
GLOBAL配置
GGSCI () 2> edit param ./GLOBALS
GGSCHEMA goldengate
EXTRACT配置
GGSCI> ADD EXTRACT extzf, tranlog, begin now,threads 2
GGSCI> ADD EXTTRAIL ./dirdat/zf, EXTRACT extzf, megabytes 200
GGSCI () 5> view param extzf
EXTRACT extzf
setenv (ORACLE_SID="testdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate,password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
--EXTTRAIL ./dirdat/zf,FORMAT RELEASE 12.1
--EXTTRAIL ./dirdat/zf, MEGABYTES 200
ENCRYPTTRAIL AES128, KEYNAME secretkey1
EXTTRAIL ./dirdat/zf
--GETTRUNCATES
--THREADOPTIONS PROCESSTHREADS EXCEPT 2
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/extzf.dsc, APPEND
DISCARDROLLOVER AT 03:00
--DYNAMICRESOLUTION
WARNLONGTRANS 2h,CHECKINTERVAL 5m
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS EXCLUDEUSER goldengate
--DDL
DDL INCLUDE MAPPED
BR BROFF
--DDLOPTIONS ADDTRANDATA, REPORT
--included table list
table TEST1.*;
table TEST2.*;
启动MGR
GGSCI>start mgr
启动EXTRACT
GGSCI> start extzf
PUMP配置
确认目标端个数，提前规划PUMP名称
GGSCI> ADD EXTRACT dpezf,EXTTRAILSOURCE ./dirdat/zf
GGSCI> add rmttrail ./dirdat/zf, ext dpezf, megabytes 200
GGSCI () 7> view param dpezf
EXTRACT dpezf
userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
--RMTHOST 192.168.1.181, MGRPORT 7839, COMPRESS, TCPBUFSIZE 60000, TCPFLUSHBYTES 65000, ENCRYPT BLOWFISH, KEYNAME messagekey
RMTHOST 192.168.1.181, MGRPORT 7839, COMPRESS, TCPBUFSIZE 60000, TCPFLUSHBYTES 65000
PASSTHRU
numfiles 5000
DYNAMICRESOLUTION
RMTTRAIL ./dirdat/zf
table PAYO_SETTLEMENT.*;
table PAYO_TT.*;
table PAYMENT_XK01.*;
目标端
MGR配置
GGSCI () 2> view param mgr
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 5, RESETMINUTES 50
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
GLOBAL配置
GGSCI>dblogin userid goldengate,password goldengate
GGSCI>ADD CHECKPOINTTABLE goldengate.chkpoint
GGSCI () 3> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint
REPLICAT配置
#初始配置时使用如下命令
GGSCI > add replicat repzf, exttrail ./dirdat/zf, checkpointtable goldengate.chkpoint
#ogg运行一段时间后，需要使用如下命令，否则无法正常同步数据，时间可以使用新加复制进程的时间点
GGSCI > add replicat repzf, exttrail ./dirdat/zf, checkpointtable goldengate.chkpoint,begin 2023-05-22 15:28:00
GGSCI () 3> view param repzf
REPLICAT repzf
setenv (ORACLE_SID="testdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
DDL INCLUDE MAPPED,EXCLUDE OBJTYPE 'INDEX'
MAP TEST1.*,TARGET TEST1.*;
MAP TEST2.*,TARGET TEST2.*;
启动MGR
GGSCI> start mgr
初始化
源端导出数据
根据实际情况定制脚本
more expdp.sh
getscn()
{
sqlplus -s / as sysdba <<EOF
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
col current_scn for 999999999999999999999999999
select current_scn from v\$database;
exit
EOF
}
scn=`getscn`
echo "scn1: $scn" > scn.txt
expdp \'/ as sysdba\' directory=dir1 schemas=PAYMENT dumpfile=rep_zf%u.dmp logfile=rep_zf.log parallel=4 CLUSTER=N filesize=2048m exclude=statistics flashback_scn=$scn
目标端导入数据
根据实际情况定制脚本
impdp \'/ as sysdba\' directory=dir1 dumpfile=rep_zf%u.dmp logfile=rep_zf.log parallel=4
数据同步
启动PUMP
源端启动pump
GGSCI> start dpezf
启动REPLICAT
目标端启动replicat
GGSCI>start repzf01 aftercsn $scn
传输加密
生成密钥，将ENCKEYS放入源端及目标端goldengate根目录
$ keygen 128 2
0x5FD70D3713180526757D8C77BEFC1521
0x0C9C7628D10BF5025FBE2B4B5DD0643F
cd goldengate
vim ENCKEYS
secretkey1 0x5FD70D3713180526757D8C77BEFC1521
secretkey2 0x0C9C7628D10BF5025FBE2B4B5DD0643F
cp ENCKEYS /goldengate
配置EXTRACT
EXTRACT extzf
setenv (ORACLE_SID="hisdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate,password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
--EXTTRAIL ./dirdat/zf,FORMAT RELEASE 12.1
--EXTTRAIL ./dirdat/zf, MEGABYTES 200
ENCRYPTTRAIL AES128, KEYNAME secretkey1
EXTTRAIL ./dirdat/zf
--GETTRUNCATES
--THREADOPTIONS PROCESSTHREADS EXCEPT 2
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/extzf.dsc, APPEND
DISCARDROLLOVER AT 03:00
--DYNAMICRESOLUTION
WARNLONGTRANS 2h,CHECKINTERVAL 5m
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS EXCLUDEUSER goldengate
--DDL
DDL INCLUDE MAPPED
BR BROFF
--DDLOPTIONS ADDTRANDATA, REPORT
--included table list
table TEST1.*;
table TEST2.*;
配置PUMP进程
EXTRACT dpezf
userid goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
--RMTHOST 10.126.122.181, MGRPORT 7839, COMPRESS, TCPBUFSIZE 60000, TCPFLUSHBYTES 65000, ENCRYPT BLOWFISH, KEYNAME messagekey
DECRYPTTRAIL AES128, KEYNAME secretkey1
RMTHOST 10.126.9.170, MGRPORT 7839, COMPRESS, TCPBUFSIZE 60000, TCPFLUSHBYTES 65000
ENCRYPTTRAIL AES128, KEYNAME secretkey2
PASSTHRU
numfiles 5000
--DYNAMICRESOLUTION
RMTTRAIL ./dirdat/zf
table TEST1.*;
table TEST2.*;
配置REPLICAT
REPLICAT repzf
setenv (ORACLE_SID="testdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
DDL INCLUDE MAPPED
DECRYPTTRAIL AES128, KEYNAME secretkey2
MAP TEST1.*,TARGET TEST1.*;
MAP TEST2.*,TARGET TEST2.*;
OGG后期变更
添加PUMP进程
添加新的PUMP进程，开启时默认从zf000000000开始抽取，当前已不存在该文件，且新加PUMP进程仅需要从数据库当前状态开始同步即可，报错情况如下
2021-08-18 14:58:55 INFO OGG-01052 No recovery is required for target file ./dirdat/zf000000000, at RBA 0 (file not opened).
方法1：
stop dpezf01
alter extract dpezf01,begin 2021-08-18 18:00:00
start dpezf01
方法2：
#参考修改复制进程的RBA号和文件号#
stop dpezf01
alter repsxx, extseqno 707, extrba 170600713
start dpezf01
添加REPLICAT进程
首先配置远端抽取进程及投递进程
添加复制进程
add replicat repzf13, exttrail ./dirdat/zf, checkpointtable goldengate.chkpoint
alter replicat repzf13, exttrail ./dirdat/zf, begin 2023-05-22 15:28:00
配置复制进程
REPLICAT repzf13
setenv (ORACLE_SID="testdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf13.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
DDL INCLUDE MAPPED
DECRYPTTRAIL AES128, KEYNAME secretkey2
MAP TEST13.*,TARGET TEST13.*;
启动复制进程
start repzf13
添加同步表
EXTRACT配置
添加TABLEEXCLUDE
TABLEEXCLUDE TEST1.TABLE01
TABLEEXCLUDE TEST2.TABLE02
重启EXTRACT
stop extzf
start extzf
导入新表
源端目标端通过数据泵或其他方式导入新表，表传输前设置为只读状态。
添加表级附加日志
select 'add trandata feehis.'||table_name from dba_tables where table_name in
('TABLE03',
'TABLE04');
GGSCI (paydb) 2> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (paydb) 3>
add trandata TEST1.TABLE03
add trandata TEST2.TABLE04
EXTRACT配置
源端目标端数据导入完成后，删除TABLEEXCLUDE
TABLEEXCLUDE TEST1.TABLE1
TABLEEXCLUDE TEST1.TABLE2
重启EXTRACT
stop extzf
start extzf
重新同步表
确认表的附加日志
GGSCI (hisdb) 6> info all
GGSCI (hisdb) 7> view param EXTZF
EXTRACT extzf
setenv (ORACLE_SID="hisdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate,password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
--EXTTRAIL ./dirdat/zf,FORMAT RELEASE 12.1
--EXTTRAIL ./dirdat/zf, MEGABYTES 200
--ENCRYPTTRAIL AES128, KEYNAME secretkey1
EXTTRAIL ./dirdat/zf
--GETTRUNCATES
--THREADOPTIONS PROCESSTHREADS EXCEPT 2
DBOPTIONS ALLOWUNUSEDCOLUMN
FETCHOPTIONS NOUSESNAPSHOT
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/extzf.dsc, APPEND
DISCARDROLLOVER AT 03:00
--DYNAMICRESOLUTION
WARNLONGTRANS 2h,CHECKINTERVAL 5m
TRANLOGOPTIONS DBLOGREADER
TRANLOGOPTIONS EXCLUDEUSER goldengate
--DDL
DDL INCLUDE MAPPED
BR BROFF
--DDLOPTIONS ADDTRANDATA, REPORT
--TABLEEXCLUDE PAYMENTHIS.CONCURRENTSAMPLE
--TABLEEXCLUDE FEEHIS.JF_FEEVOUCHER12_08TEMP
--included table list
table TEST1.*;
table TEST2.*;
GGSCI (hisdb) 8> dblogin userid goldengate,password goldengate
Successfully logged into database.
GGSCI (hisdb as goldengate@hisdb) 9> info trandata TEST1.TABLE1
2021-08-19 09:20:38 INFO OGG-10471 ***** Oracle Goldengate support information on table FEEHIS.JF_FEEVOUCHER20210101 *****
Oracle Goldengate support native capture on table FEEHIS.JF_FEEVOUCHER20210101.
Oracle Goldengate marked following column as key columns on table TEST1.TABLE1: SYSTEMNO.
Logging of supplemental redo log data is enabled for table TEST2.TABLE2.
Columns supplementally logged for table TEST3.TABLE3: "SYSTEMNO".
Prepared CSN for table TEST3.TABLE3: 294182839
GGSCI (hisdb as goldengate@hisdb) 10>
基于SCN号导表
$ more expdpogg_tzhisdb.sh
getscn()
{
sqlplus -s / as sysdba <<EOF
set heading off
set feedback off
set pagesize 0
set verify off
set echo off
col current_scn for 999999999999999999999999999
select current_scn from v\$database;
exit
EOF
}
scn=`getscn`
echo "scn1: $scn" > TEST_scn.txt
expdp \'/ as sysdba\' directory=dir1 parfile=TEST.tb dumpfile=feehis20210819_%u.dmp logfile=TEST_20210819.log parallel=4 CLUSTER=N filesize=1024m exclude=statistics COMPRESSION=all flashback_scn=$scn
$ more TEST_scn.txt
scn1: 304343655
$ ll TEST_scn.txt
-rw-r--r-- 1 oracle oinstall 21 Aug 19 09:08 FEEHIS_TZHISDB_scn.txt
将表传至目标端
$ scp -r -l15000 /backup/expdir/test20210819_*.dmp oracle@192.168.1.11:/backup/expdir/
目标端复制进程排除此表
排除重新同步的表
GGSCI () 2> edit param REPZF04
REPLICAT repzf04
setenv (ORACLE_SID="hisdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf04.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
--DDL INCLUDE MAPPED,EXCLUDE OBJTYPE 'INDEX'
--DDLERROR DEFAULT IGNORE RETRYOP
DECRYPTTRAIL AES128, KEYNAME secretkey2
MAPEXCLUDE TEST1.TABLE1
MAP TEST1.*,TARGET TEST1.*;
新加复制进程，用于单独同步该表，暂不启动
GGSCI () 3> view param REPZF08
REPLICAT repzf08
setenv (ORACLE_SID="hisdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf08.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
--DDL INCLUDE MAPPED,EXCLUDE OBJTYPE 'INDEX'
DECRYPTTRAIL AES128, KEYNAME secretkey2
MAP TEST1.TABLE1,TARGET TEST1.TABLE1;
GGSCI () 3> add replicat repzf08, exttrail ./dirdat/zf, checkpointtable goldengate.chkpoint
在目标端库导入表
$ more impdpfeehis.sh
impdp \'/ as sysdba\' directory=DIR1 dumpfile=test_%u.dmp logfile=test.log TABLE_EXISTS_ACTION=REPLACE
启动复制进程
导入完成之后启动“新增用于重新同步表的专用复制进程
info all
start repzf08 aftercsn 304343655
停止抽取进程
stop extzf
检查复制情况
info REPZF04,detail
GGSCI () 2> info REPZF04 detail
REPLICAT REPZF04 Last Started 2021-08-19 09:10 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:27 ago)
Process ID 187013
Log Read Checkpoint File /goldengate/dirdat/zf000000001 -------------文件号
First Record RBA 4339 -------------RBA号
Current Log BSN value: (requires database login)
Last Committed Transaction CSN value: (requires database login)
info REPZF08,detail
GGSCI () 3> info REPZF08 detail,
REPLICAT REPZF08 Last Started 2021-08-18 14:46 Status STOPPED
Checkpoint Lag 00:00:00 (updated 18:46:29 ago)
Log Read Checkpoint File /goldengate/dirdat/zf000000000
First Record RBA 0
Current Log BSN value: (requires database login)
确保两个进程的文件号和RBA号相同后，停止两个复制进程
stop REPZF04
stop REPZF08----------------不再启动
修改两个复制进程
GGSCI () 2> edit param REPZF04
REPLICAT repzf04
setenv (ORACLE_SID="hisdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf04.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
--DDL INCLUDE MAPPED,EXCLUDE OBJTYPE 'INDEX'
--DDLERROR DEFAULT IGNORE RETRYOP
DECRYPTTRAIL AES128, KEYNAME secretkey2
MAP TEST1.*,TARGET TEST1.*;
GGSCI (tzhisdb) 2> edit param REPZF08
REPLICAT repzf08
setenv (ORACLE_SID="hisdb")
setenv (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
USERID goldengate, password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
DBOPTIONS DEFERREFCONST
REPORT AT 01:59
REPORTCOUNT EVERY 1 MINUTES, RATE
REPORTROLLOVER AT 02:00
REPERROR DEFAULT, ABEND
numfiles 5000
--DBOPTIONS ALLOWUNUSEDCOLUMN
MAXTRANSOPS 50000
GROUPTRANSOPS 2000
CHECKPOINTSECS 40
--HANDLECOLLISIONS
assumetargetdefs
ALLOWNOOPUPDATES
DISCARDFILE ./dirrpt/repzf08.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 02:00
DYNAMICRESOLUTION
--GETTRUNCATES
--DDL INCLUDE MAPPED,EXCLUDE OBJTYPE 'INDEX'
DECRYPTTRAIL AES128, KEYNAME secretkey2
--MAP FEEHIS.JF_FEEVOUCHER20210101,TARGET TEST1.TABLE1;
重启原来的复制进程
stop REPZE04
start REPZF04
REPZF08不再启动或删除此进程
启动抽取进程
start extzf
部分报错
目标端没有索引
有必要的话需要排查目标端没有约束的表
GGSCI () 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPZF 00:00:00 00:00:04
REPLICAT RUNNING REPZF01 00:00:00 00:00:05
REPLICAT RUNNING REPZF02 00:00:00 00:00:06
REPLICAT RUNNING REPZF03 00:00:00 00:00:03
REPLICAT RUNNING REPZF04 00:00:00 00:00:05
REPLICAT RUNNING REPZF05 00:00:00 00:00:04
REPLICAT RUNNING REPZF06 00:00:00 00:00:06
REPLICAT APPEND REPZF07 00:00:00 00:00:02
view report repzf07
2021-08-04 12:13:55 WARNING OGG-01431 Aborted grouped transaction on PAYMENTHIS.COLLECTEDTX202104, Mapping error.
2021-08-04 12:13:55 WARNING OGG-01003 Repositioning to rba 740485 in seqno 71.
2021-08-04 12:13:55 WARNING OGG-01151 Error mapping from test1.table1 to test1.table1.
表test1.table1没有索引，添加索引问题解决
添加PUMP进程
2021-08-18 14:58:55 INFO OGG-01052 No recovery is required for target file ./dirdat/zf000000000, at RBA 0 (file not opened).
设置PUPM进程抽取起始时间点
stop dpezf01
alter extract dpezf01,begin 2021-08-18 18:00:00
start dpezf01
新加复制进程
DML不能正常同步，查看复制进程状态，Trail文件及RBA指向都是0，实际上这些文件已经清除
指定最新的文件号及rba号，有新的DML操作即可直接复制
alter repzf11, extseqno 432, extrba 9636
880
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
