说明
该文档用于解决同步中出现非法日期的报错。
现象说明
在同步时，发现在应用端经常会出现非法日期的报错，类似于月份无效等。因为 oracle 对 date 类型是会做检查的，当不在指定的范围内时，就会报错，最终导致同步异常。而在源端能将这种非法的日期插入，是因为在应用端做过处理，所以非法日期能进入数据库而不报错，但是在数据库中查到的日期都是 0000-00-00 。
方案
l 规范应用
l 目标端date类型改为char型，通过OGG做转换后同步（前提是客户接受字段类型改变）
解决方案模拟
下面将模拟通过方案二解决报错的过程（数据同步软件已经安装）
创建测试表
创建测试表 test ，插入部分数据
SQL> create table test (id number,name varchar2(100),birthday date);
Table created.
SQL> insert into test values (1,'ALICE',to_date('1991-01-01','yyyy-mm-dd'));
1 row created.
SQL> insert into test values (2,'BOB',to_date('1960-11-21','yyyy-mm-dd'));
1 row created.
SQL> insert into test values (3,'SUNNY',to_date('1989-09-03','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
select * from test;
ID NAME BIRTHDAY
---------- -------------------- ------------
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
同步test 表
初始化 test 表到目标端，并将 test 表加到数据同步软件中（过程略）
查看目标端数据库
SQL> select * from test;
ID NAME BIRTHDAY
---------- -------------------- ------------
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
测试合法时间插入
源端插入正常的时间
SQL> insert into test values (4,'SONIA',to_date('1997-04-14','yyyy-mm-dd'));
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME BIRTHDAY
---------- -------------------- ------------
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
4 SONIA 14-APR-97
目标端查看：
SQL> select * from test;
ID NAME BIRTHDAY
---------- -------------------- ---------
4 SONIA 14-APR-97
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
数据可以正常同步。
测试非法时间插入
源端插入非法日期
SQL> insert into test values (5,'PAIGE',to_date('0001-01-01','yyyy-mm-dd')-1);
1 row created.
SQL> commit;
Commit complete.
select id,name,to_char(BIRTHDAY,'yyyy-mm-dd') from test;
ID NAME TO_CHAR(BI
---------- -------------------- ----------
1 ALICE 1991-01-01
2 BOB 1960-11-21
3 SUNNY 1989-09-03
4 SONIA 1997-04-14
5 PAIGE 0000-00-00 # 非法日期
目标端发现没有新插入的非法日期这条记录
查看同步软件日志，发现报错如下：
[oracle@slave odc]$ tail -f ggserr.log
2017-11-09 06:06:52 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: REPLICAT SM_REP started.
2017-11-09 06:06:52 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: No unique key is defined for table 'TEST'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-11-09 06:13:55 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: No unique key is defined for table 'T2'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-11-09 06:25:07 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: OCI Error ORA-01841: ( 完整 ) 年份值必须介于 -4713 和 +9999 之间 , 且不为 0 (status = 1841). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ZMY"."TEST" ("ID","NAME","BIRTHDAY") VALUES (:a0,:a1,:a2).
2017-11-09 06:25:07 WARNING OGG-01004 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: Aborted grouped transaction on 'ZMY.TEST', Database error 1841 (OCI Error ORA-01841: ( 完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0 (status = 1841). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ZMY"."TEST" ("ID","NAME","BIRTHDAY") VALUES (:a0,:a1,:a2)).
2017-11-09 06:25:07 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: Repositioning to rba 1490 in seqno 2.
2017-11-09 06:25:07 WARNING OGG-01154 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: SQL error 1841 mapping ZMY.TEST to ZMY.TEST OCI Error ORA-01841: ( 完整) 年份值必须介于 -4713 和 +9999 之间, 且不为 0 (status = 1841). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "ZMY"."TEST" ("ID","NAME","BIRTHDAY") VALUES (:a0,:a1,:a2).
2017-11-09 06:25:07 WARNING OGG-01003 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: Repositioning to rba 1490 in seqno 2.
2017-11-09 06:25:07 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: Error mapping from ZMY.TEST to ZMY.TEST.
2017-11-09 06:25:07 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, sm_rep.prm: PROCESS ABENDING.
日志显示年份不在规定范围内，无法插入数据库。
修改备端字段类型
增加一个临时列，类型为需要的 varchar2
SQL> alter table test add date_temp varchar2(100);
Table altered.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME BIRTHDAY DATE_TEMP
---------- -------------------- ------------------- -------------------------------
4 SONIA 14-APR-97
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
Rename 原字段
SQL> alter table test rename column birthday to birthday_bak;
Table altered.
SQL> select * from test;
ID NAME BIRTHDAY_BAK DATE_TEMP
---------- -------------------- ---------------------------------------- ------------------------------
4 SONIA 14-APR-97
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(100)
BIRTHDAY_BAK DATE
DATE_TEMP VARCHAR2(100)
将原列上的数据更新到新加的临时列
SQL> update test set date_temp = cast(birthday_bak as varchar2(100));
4 rows updated.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME BIRTHDAY_BAK DATE_TEMP
---------- -------------------- ---------------------------------------- ------------------------------
4 SONIA 14-APR-97 14-APR-97
1 ALICE 01-JAN-91 01-JAN-91
2 BOB 21-NOV-60 21-NOV-60
3 SUNNY 03-SEP-89 03-SEP-89
删除原列
SQL> alter table test drop column birthday_bak;
Table altered.
SQL> commit;
Commit complete.
SQL> select * from test;
ID NAME DATE_TEMP
---------- -------------------- ------------------------------
4 SONIA 14-APR-97
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
修改新增列为原列名称
SQL> alter table test rename column date_temp to birthday;
SQL> commit;
Commit complete.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(100)
BIRTHDAY VARCHAR2(100)
源端同步软件生成定义文件
编辑
GGSCI (rac2) 3> edit params defgen
defsfile /odc/dirdef/sm.def purge
userid odc, password odc
table zmy.t2;
table zmy.test;
desfile ： def 文件输出路径
table: 可以指定 table ，也可以使用 account.* 到处相应账户下所有表结构信息。
生成目标端需要的定义文件
[oracle@rac2 ~]$ cd /odc
[oracle@rac2 odc]$ ./defgen paramfile /odc/dirprm/defgen.prm
会在 /odc/dirdef 路径下生成 sm.def 文件。
并将产生的定义文件传输到目标端的 dirdef 目录下
修改目标端应用进程
编辑对应的应用进程参数文件
添加参数 SOURCEDEFS
GGSCI (slave) 2> view param sm_rep
replicat sm_rep
setenv (NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK")
userid odc, password odcASSUMETARGETDEFS
ALLOWNOOPUPDATES
SOURCEDEFS /odc/dirdef/sm.def
DBOPTIONS DEFERREFCONST
--batchsql
ddlerror 955 ignore
ddlerror 1917 ignore
ddlerror 24344 ignore
ddl include mapped
DISCARDFILE ./dirrpt/sm.dsc, APPEND megabytes 20
DISCARDROLLOVER on sunday
map zmy.test,target zmy.test;
修改后启动进程
GGSCI (slave) 3> start am_rep
GGSCI (slave) 4> info all
MANAGER RUNNING
REPLICAT RUNNING SM_REP 00:00:00 00:00:00
进程正常同步
检查目标端测试表
查看到数据正常插入
SQL> select * from test;
ID NAME BIRTHDAY
---------- -------------------- -------------------------------------------------------------------------
4 SONIA 14-APR-97
1 ALICE 01-JAN-91
2 BOB 21-NOV-60
3 SUNNY 03-SEP-89
5 PAIGE 0000-12-31:00:00:00
总结
通过该方法，同步软件可以正常同步数据，目标库也能插入非法日期，不过查到的日期和生产一样是一个损坏的日期。