oracle ogg输出格式,关于OGG同步中日期格式超出正常范围的问题处理

谈谈最近碰到的一个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&quot

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&quot

> 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的时候，会报这些表正在被占用的错误。