谈谈最近碰到的一个OGG同步问题。首先希望大家要养成一种好习惯,经常看抽取进程日志的习惯,通常位于:/dirrpt/repsm.dsc。正常情况下,这个日志文档是不写入数据的,包括出现warnning也不会写入。一旦发现这个文档不断增大,不要盲目得增加文件大小。而是要分析是不是出问题了。
最近,我的这份文档进程会报:
OCI Error ORA-01843: not a valid month (status = 1843). INSERT INTO "ZG"."INVOICE_201603" ("INVOICE_ID","BILLING_CYCLE_ID","DISPLAY_CYCLE_NAME","BILL_REQUIRE_ID","STAFF_ID","ACC_NBR","INVOICE_AMOUNT","COUNT","PRINT_COUNT","SUPPLY_COUNT","PRINT_FLAG","INVOICE_TYPE","INVOICE_CODE","INVOICE_BATCH_CODE","INVOICE_INSIDE_CODE","ACCT_ID","IMAGE","ACCT_NAME","ACCT_CODE","PAY_ADDR","STAFF_CODE","CUST_BANK_NAME","BANK_ACCT_CUST_NO","BANK_ACCT_CUST_NAME","PARTNER_BANK_NAME","PARTNER_BANK_ACCT_NO","PARTNER_BANK_ACCT_NAME","TAB_ID","STATE","PAY_DATE","CREATED_DATE","REGION_ID","AGREEMENT_CODE","CORP_ORG_ID","LIST_NO","PAYMENT_ID","CRM_INVC_TYPE","REVERSE_INVOICE_ID","REVERSE_INVOICE_CODE","REVERSE_LIST_NO","ORG_ID","REMARK","LAST_PRINT_DATE"
VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12,:a13,:a14,:a15,:a16,:a17,:a18,:a19,:a20,:a21,:a22,:a23,:a24,:a25,:a26,:a27,:a28,:a29,:a30,:a31,:a32,:a33,:a34,:a35,:a36,:a37,:a38,:a39,:a40,:a41,:a42)
Operation failed at seqno 2676 rba 74673130
Discarding record on action DISCARD on error 1843
Problem replicating ZG.INVOICE_201603 to ZG.INVOICE_201603
Mapping problem with insert record (target format)...
ACCT_CODE = 3019036
PARTNER_BANK_ACCT_NO = NULL
PARTNER_BANK_ACCT_NAME = NULL
TAB_ID = 0
STATE = 3
PAY_DATE = 0001-00-00 00:00:00
CREATED_DATE = 2016-03-17 10:41:17
REGION_ID = 591
AGREEMENT_CODE = NULL
CORP_ORG_ID = 1009
LIST_NO = NULL
PAYMENT_ID = 716032472039910
CRM_INVC_TYPE = 0
REVERSE_INVOICE_ID = 108745172
REVERSE_INVOICE_CODE = NULL
REVERSE_LIST_NO = NULL
ORG_ID = 0
REMARK = NULL
LAST_PRINT_DATE = NULL
仔细一分析原来是原表的pay_date日期记录成了0001-00-00.这个日期是不合法。所以就需要针对这个表的pay_date进行转化。
MAP ZG.INVOICE_201603, target ZG.INVOICE_201603, filter ( @GETENV("TRANSACTION","CSN"
> 13801696602863), COLMAP(usedefaults, PAY_DATE = @IF(@STRNCMP(PAY_DATE, "0001-00-00", 10)=0,"0001-01-01 00:00:00",PAY_DATE));
这个修改完成后,并不能就此罢手了。因为之前历史未导入的数据改如何处理。下面就给大家说下我的解决方案,如果大家有更好的方式,希望能够共享下。
首先我需要目标端的replicate进程。----stop repsm
其次:在源端将漏抽取的这些表都导出dump出来。
1)select current_scn from v$database;
2)写好par文档。
zg.par
directory=expdp_dir
dumpfile=zg20160616_%U.dmp
logfile=zg20160616.log
COMPRESSION=ALL
CLUSTER=N
parallel=8
FLASHBACK_SCN=13801753279324 ----注意要输入刚刚查好的scn号
TABLES=ZG.INVOICE_201311,ZG.INVOICE_201312,ZG.INVOICE_201401,ZG.INVOICE_201402,ZG.INVOICE_201403,ZG.INVOICE_201404,ZG.INVO
ICE_201405,ZG.INVOICE_201406,ZG.INVOICE_201407,ZG.INVOICE_201408,ZG.INVOICE_201409,ZG.INVOICE_201410,ZG.INVOICE_201411,ZG.
INVOICE_201412,ZG.INVOICE_201501,ZG.INVOICE_201502,ZG.INVOICE_201503,ZG.INVOICE_201504,ZG.INVOICE_201505,ZG.INVOICE_201506
,ZG.INVOICE_201507,ZG.INVOICE_201508,ZG.INVOICE_201509,ZG.INVOICE_201510,ZG.INVOICE_201511,ZG.INVOICE_201512,ZG.INVOICE_20
1601,ZG.INVOICE_201602,ZG.INVOICE_201603,ZG.INVOICE_201604,ZG.INVOICE_201605,ZG.INVOICE_201606,ZG.INVOICE_201607,ZG.INVOIC
E_201608,ZG.INVOICE_201609,ZG.INVOICE_201610,ZG.INVOICE_201611,ZG.INVOICE_201612,ZG.INVOICE_201701,ZG.INVOICE_201702,ZG.IN
VOICE_201703,ZG.INVOICE_201704
3)expdp zg/zg parfile=zg.par
4)到目标端。将这些dump文档导入进去。
目标端zg.pardirectory=expdp_dir
dumpfile=zg20160616_%U.dmp
CLUSTER=N
TABLE_EXISTS_ACTION=replace
REMAP_TABLESPACE=DATA1
ATA1,DATA01
ATA1
logfile=zg20160616.log
parallel=8
TABLES=ZG.INVOICE_201311,ZG.INVOICE_201312,ZG.INVOICE_201401,ZG.INVOICE_201402,ZG.INVOICE_201403,ZG.INVOICE_201404,ZG.INVOICE_201405,ZG.INVOICE_201406,ZG.INVOICE_201407,ZG.INVOICE_201408,ZG.INVOICE_201409,ZG.INVOICE_201410,ZG.INVOICE_201411,ZG.INVOICE_201412,ZG.INVOICE_201501,ZG.INVOICE_201502,ZG.INVOICE_201503,ZG.INVOICE_201504,ZG.INVOICE_201505,ZG.INVOICE_201506,ZG.INVOICE_201507,ZG.INVOICE_201508,ZG.INVOICE_201509,ZG.INVOICE_201510,ZG.INVOICE_201511,ZG.INVOICE_201512,ZG.INVOICE_201601,ZG.INVOICE_201602,ZG.INVOICE_201603,ZG.INVOICE_201604,ZG.INVOICE_201605,ZG.INVOICE_201606,ZG.INVOICE_201607,ZG.INVOICE_201608,ZG.INVOICE_201609,ZG.INVOICE_201610,ZG.INVOICE_201611,ZG.INVOICE_201612,ZG.INVOICE_201701,ZG.INVOICE_201702,ZG.INVOICE_201703,ZG.INVOICE_201704
5)导入成功后,则修改/dirrpm/repsm.rpm文件。
将这些表的scn改成刚刚查的源端的scn。
6)在目标端打开start repsm。就完成了整个恢复。
需要注意的的是:停止replicate进程需要在在源端导出dump的之前。否则,在导入dump的时候,会报这些表正在被占用的错误。