赞
踩
源端目标端配置环境变量
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
alter system set enable_goldengate_replication=true scope=both sid='*';
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
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;
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
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.*
GGSCI () 2> edit param ./GLOBALS
GGSCHEMA goldengate
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.*;
GGSCI>start mgr
GGSCI> start extzf
确认目标端个数,提前规划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.*;
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
GGSCI>dblogin userid goldengate,password goldengate
GGSCI>ADD CHECKPOINTTABLE goldengate.chkpoint
GGSCI () 3> edit params ./GLOBALS
GGSCHEMA goldengate
CHECKPOINTTABLE goldengate.chkpoint
#初始配置时使用如下命令 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.*;
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
GGSCI> start dpezf
目标端启动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 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.*;
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 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.*;
添加新的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
首先配置远端抽取进程及投递进程
添加复制进程
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
添加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
源端目标端数据导入完成后,删除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>
$ 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没有索引,添加索引问题解决
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
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。