OGG How to Resync Tables / Schemas on Different SCN s in a Single Replicat
To resync one or more tables/schemas on different SCN's using a single or minimum number of replicats without using handlecollisions.
For example, when doing migrations and parallel exports are run.
This can be done with Oracle goldenGate (OGG) 11.1.1.0.x or higher.
Consider the following example with the schema HR and two tables TCUSTORD,
TCUSTMER
The syntax given below is for OGG v11.x.
Please see the NOTE at the end for OGG v12 and higher
1. Capture the SCN information in the extract trails using tokens
table hr.*, Tokens (tk-csn = @GETENV ("TRANSACTION", "CSN")) ;
Step 1 can be skipped if using OGG v11.1.1.1.x and it is needed if using OGG
v11.1.1.0.x
On OGG v11.1.1.1.x and higher the TOKEN is not needed in the extract as the CSN/SCN is written
by default to the OGG trails
2. Do an SCN based export on source for the interested tables (first TCUSTMER,
then TCUSTORD in
our example)
exp hr/hr tables=hr.tcustmer grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=4746443
Then run another export on source for TCUSTORD at a different scn
exp hr/hr tables=hr.tcustord grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=4747033
3. Run the imports on target
4. Add the replicat on target to the beginning of the extract trail
Example parameter file:
replicat r1
userid myogguser password xyz
assumetargetdefs
discardfile dirrpt\r1.dsc, purge
map hr.tcustmer, target myogguser.* , Filter ( @NUMSTR (@TOKEN
("TK-CSN")) > 4746443);
map hr.tcustord, target myogguser.* , Filter ( @NUMSTR (@TOKEN
("TK-CSN")) > 4747033);
This is used to resync one or more tables/ schemas of different SCN's in the
same replicat.
If not using tokens in the extract on step 1 (OGG versions 11.1 and higher) then the equivalent
mapping to use the default SCN token from the trail would be
map hr.tcustmer,
target myogguser.* , FILTER ( @GETENV ("TRANSACTION",
"CSN") > 4746443);
map hr.tcustord, target myogguser.* , FILTER ( @GETENV
("TRANSACTION", "CSN") > 4747033);
This example is for a DML only replication setup using OGG version 11.1.1.0.x or higher
NOTE: For OGGv12 the double quotes needs to be replaced with single quotes since OGG v12 uses ANSI SQL parameter by default
sample1;
1、停止抽取进程、传输进程、复制进程
stop e*
stop p*
stop r*
2、修改抽取进程、传输进程配置，新增表同步
extract:
table MM_PS.T_MCOUPONSINFO_HIS;
pump:
table MM_PS.T_MCOUPONSINFO_HIS;
3、源端增加表附加日志，并启动抽取、传输进程
dblogin userid goldengate password 123
add trandata MM_PS.T_MCOUPONSINFO_HIS
start e*
start p*
4、源端数据库获取当前最新scn
select dbms_flashback.get_system_change_number current_scn from dual;
10930121916224
5、源端数据库按照指定的scn导出表数据
exp mmdb03/*** direct=y rows=y buffer=64000000 flashback_scn=10930121916224 constraints=n grants=n triggers=n
STATISTICS=none file=/ogghome/oggexp/ogg.dmp log=/ogghome/oggexp/ogg.log tables=MM_PS.T_MCOUPONSINFO_HIS
6、将导出的数据导入到目标端数据库
imp mmdb03/*** file=/ogghome/oggexp/ogg.dmp log=/ogghome/oggexp/ogg.log full=y ignore=y buffer=64000000
7、目标端数据库禁掉新增表上的trigger与有级联删除的约束
select owner,table_name,TRIGGER_NAME from dba_triggers where table_name ='T_MCOUPONSINFO_HIS';
8、修改复制进程配置，增加复制表map关系，用filter指定复制scn
map MM_PS.T_MCOUPONSINFO_HIS, target MM_PS.T_MCOUPONSINFO_HIS, filter ( @getenv("TRANSACTION", "CSN") > 10930121916224);
9、启动进程
start r*
10、检查表数据同步情况
http://blog.itpub.net/24996904/viewspace-1226192/
