說明

該文件用於解決同步中出現非法日期的報錯。

現象說明

在同步時，發現在應用端經常會出現非法日期的報錯，類似於月份無效等。因為 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

總結

透過該方法，同步軟體可以正常同步資料，目標庫也能插入非法日期，不過查到的日期和生產一樣是一個損壞的日期。

Oracle非法日期處理方案

說明

現象說明

方案

解決方案模擬

建立測試表

同步test 表

測試合法時間插入

測試非法時間插入

修改備端欄位型別

源端同步軟體生成定義檔案

修改目標端應用程式

檢查目標端測試表

總結

相關文章

Oracle非法日期 處理方案

說明

現象說明

方案

解決方案模擬

建立測試表

同步test 表

測試合法時間插入

測試非法時間插入

修改備端欄位型別

源端同步軟體生成定義檔案

修改目標端應用程式

檢查目標端測試表

總結

相關文章

Oracle非法日期處理方案