Goldengate參數_Formatsql

來源：懂視網責編：小采時間：2020-11-09 13:08:34

Goldengate參數_Formatsql

Goldengate參數_Formatsql:閑著沒事做，突然看到了Ogg的Foarmsql參數，可以把抽取的數據打印成Sql文本 1. 配置ogg抽取 GGSCI enmotech 1 ADD EXTRACT e_sql, tranlog, BEGIN nowEXTRACT added.GGSCI enmotech 2 edit params e_sqlEXTRACT

推薦度：

點擊下載本文 文檔為doc格式

導讀Goldengate參數_Formatsql:閑著沒事做，突然看到了Ogg的Foarmsql參數，可以把抽取的數據打印成Sql文本 1. 配置ogg抽取 GGSCI enmotech 1 ADD EXTRACT e_sql, tranlog, BEGIN nowEXTRACT added.GGSCI enmotech 2 edit params e_sqlEXTRACT

閑著沒事做，突然看到了Ogg的Foarmsql參數，可以把抽取的數據打印成Sql文本 1. 配置ogg抽取 GGSCI enmotech 1 ADD EXTRACT e_sql, tranlog, BEGIN nowEXTRACT added.GGSCI enmotech 2 edit params e_sqlEXTRACT e_sqlsetenv NLS_LANG = AMERICAN_AMERICA.ZHS

閑著沒事做，突然看到了Ogg的Foarmsql參數，可以把抽取的數據打印成Sql文本

1.配置ogg抽取

GGSCI (enmotech) 1> ADD EXTRACT e_sql, tranlog, BEGIN now
EXTRACT added.
 
 
GGSCI (enmotech) 2> edit params e_sql
 
EXTRACT e_sql
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
userid goldengate , password AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC,ENCRYPTKEY DEFAULT
FORMATSQL ORACLE, NONAMES
EXTTRAIL ./dirdat/es
 
 
TABLE ogg1.ogg_test;
TABLE ogg1.test1;
 
 
ADD EXTTRAIL ./dirdat/es, EXTRACT E_SQL
 
 
GGSCI (enmotech) 4> START E_SQL
 
Sending START request TO MANAGER ...
EXTRACT E_SQL starting

2.插入數據測試

SQL> SELECT * FROM tab;
 
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
OGG_TEST TABLE
TEST1 TABLE
 
SQL> SELECT * FROM test1;
 
 ID NAME DATE1
---------- -------------------- -----------------
 3 travel 20140514 15:15:57
 1 travel 20140514 15:14:49
 
SQL> INSERT INTO test1 VALUES (4,'travel1',sysdate);
 
1 ROW created.
 
SQL> commit;
 
Commit complete.
 
SQL> SELECT * FROM test1;
 
 ID NAME DATE1
---------- -------------------- -----------------
 3 travel 20140514 15:15:57
 1 travel 20140514 15:14:49
 4 travel1 20140610 20:52:21
 
SQL> INSERT INTO test1 VALUES (4,'travel1',sysdate);
 
1 ROW created.
 
SQL> commit;
 
Commit complete.
 
SQL>
SQL> DELETE FROM ogg_test WHERE owner='sys' AND rownum < 10;
 
0 ROWS deleted.
 
SQL> DELETE FROM ogg_test WHERE owner='SYS' AND rownum < 10;
 
9 ROWS deleted.
 
SQL> COMMIT;
 
Commit complete.
 
SQL> INSERT INTO test1 VALUES (4,'travel1',TO_DATE('2011-01-01','YYYY-MM-DD'));
 
1 ROW created.
 
SQL> commit;
 
Commit complete.
 
SQL> SELECT * FROM test1;
 
 ID NAME DATE1
---------- -------------------- -----------------
 3 travel 20140514 15:15:57
 1 travel 20140514 15:14:49
 4 travel1 20140610 20:52:21
 4 travel1 20140610 20:56:33
 4 travel1 20110101 00:00:00

查看trail文件

╭─root@enmotech ~
╰─? tail -100f /u01/ogg1/dirdat/es000000
--B,2014-06-10:20:52:23.000000,1402404743,486
INSERT INTO OGG1.TEST1 (ID,NAME,DATE1) VALUES ('4','travel1','');
COMMIT WORK;
--B,2014-06-10:20:56:35.000000,1402404995,486
INSERT INTO OGG1.TEST1 VALUES ('4','travel1','');
COMMIT WORK;
--B,2014-06-10:20:58:16.000000,1402405096,486
DELETE FROM OGG1.OGG_TEST WHERE ID='1';
DELETE FROM OGG1.OGG_TEST WHERE ID='2';
DELETE FROM OGG1.OGG_TEST WHERE ID='3';
DELETE FROM OGG1.OGG_TEST WHERE ID='4';
DELETE FROM OGG1.OGG_TEST WHERE ID='5';
DELETE FROM OGG1.OGG_TEST WHERE ID='6';
DELETE FROM OGG1.OGG_TEST WHERE ID='7';
DELETE FROM OGG1.OGG_TEST WHERE ID='8';
DELETE FROM OGG1.OGG_TEST WHERE ID='9';
COMMIT WORK;
--B,2014-06-10:21:00:09.000000,1402405209,486
INSERT INTO OGG1.TEST1 VALUES ('4','travel1','');
COMMIT WORK;

這里發現時間字段ogg沒有處理正確，查看mos

Oracle GoldenGate - Version 11.2.1.0.0 and later
Information in this document applies to any platform.
SYMPTOMS
 
 
The output trail generated by "FORMATSQL ORACLE" parameter is not converting date and time columns compatible to Oracle format.
SQL statement generated has keyword "" values for date/time columns
 
 
Example
 
Extract parameter file
**********************
extract eformat
userid pjacob password xxxx
formatsql oracle
EXTTRAIL ./dirdat/fo
table pjacob.test*;
 
Sql
****
SQL> create table test_tab (a number, b varchar(10), c date, d timestamp);
SQL> insert into test_tab values(2,'asd',sysdate,current_timestamp);
 
 
result
*********
 
$ (aixvm-02) \> cat fo000000
--B,2012-11-06:12:28:15.000000,1352233695,2672
INSERT INTO PJACOB.TEST_TAB (A,B,C,D) VALUES ('2','asd','','');
COMMIT WORK;
 
Instead of  it should have displayed the date and time correctly
 
 
 
 
CAUSE
 
The issue is due to the BUG 14059898
 
SOLUTION
 
The solution is to use a OGG build which have the fix for BUG 14059898.
 
The fix is available from v11.2.1.0.5.

升級到11.2.1.0.6后

╭─oracle@enmotech /u01/ogg1
╰─? ./ggsci
 
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.6_03 16934271 17205864_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 7 2013 21:22:29
 
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
 
 
 
GGSCI (enmotech) 1> start mgr
 
Manager started.
 
 
GGSCI (enmotech) 2> info all
 
Program Status Group Lag at Chkpt Time Since Chkpt
 
MANAGER RUNNING
EXTRACT ABENDED EXT1 00:00:00 653:17:00
EXTRACT STOPPED E_SQL 00:00:00 00:00:56
REPLICAT ABENDED RPEE 00:00:00 653:17:11
 
 
GGSCI (enmotech) 3> start e_sql
 
Sending START request to MANAGER ...
EXTRACT E_SQL starting
 
 
GGSCI (enmotech) 6>
 
 
SQL> insert into test1 values (4,'travel1',TO_DATE('2011-01-01','YYYY-MM-DD'));
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> insert into test1 values (4,'travel1',sysdate);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test1;
 
 ID NAME DATE1
---------- -------------------- -----------------
 3 travel 20140514 15:15:57
 1 travel 20140514 15:14:49
 4 travel1 20140610 20:52:21
 4 travel1 20140610 20:56:33
 4 travel1 20110101 00:00:00
 4 travel1 20110101 00:00:00
 4 travel1 20140610 21:19:26
 
7 rows selected.
 
SQL> update test1 set DATE1=sysdate where id=4;
 
5 rows updated.
 
SQL> commit;
 
Commit complete.
 
SQL>

再次查看

--B,2014-06-10:21:19:12.000000,1402406352,486
INSERT INTO OGG1.TEST1 VALUES ('4','travel1',TO_DATE('2011-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'));
COMMIT WORK;
--B,2014-06-10:21:19:30.000000,1402406370,486
INSERT INTO OGG1.TEST1 VALUES ('4','travel1',TO_DATE('2014-06-10:21:19:26','YYYY-MM-DD:HH24:MI:SS'));
COMMIT WORK;
--B,2014-06-10:21:20:28.000000,1402406428,486
UPDATE OGG1.TEST1 SET ID='4',NAME='travel1',DATE1=TO_DATE('2014-06-10:21:20:26','YYYY-MM-DD:HH24:MI:SS') WHERE ID='4' AND NAME='travel1' AND DATE1=TO_DATE('2014-06-10:20:52:21','YYYY-MM-DD:HH24:MI:SS');
UPDATE OGG1.TEST1 SET ID='4',NAME='travel1',DATE1=TO_DATE('2014-06-10:21:20:26','YYYY-MM-DD:HH24:MI:SS') WHERE ID='4' AND NAME='travel1' AND DATE1=TO_DATE('2014-06-10:20:56:33','YYYY-MM-DD:HH24:MI:SS');
UPDATE OGG1.TEST1 SET ID='4',NAME='travel1',DATE1=TO_DATE('2014-06-10:21:20:26','YYYY-MM-DD:HH24:MI:SS') WHERE ID='4' AND NAME='travel1' AND DATE1=TO_DATE('2011-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS');
UPDATE OGG1.TEST1 SET ID='4',NAME='travel1',DATE1=TO_DATE('2014-06-10:21:20:26','YYYY-MM-DD:HH24:MI:SS') WHERE ID='4' AND NAME='travel1' AND DATE1=TO_DATE('2011-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS');
UPDATE OGG1.TEST1 SET ID='4',NAME='travel1',DATE1=TO_DATE('2014-06-10:21:20:26','YYYY-MM-DD:HH24:MI:SS') WHERE ID='4' AND NAME='travel1' AND DATE1=TO_DATE('2014-06-10:21:19:26','YYYY-MM-DD:HH24:MI:SS');
COMMIT WORK;

暫時沒想過這個功能用在什么地方有好處

原文地址：Goldengate參數_Formatsql, 感謝原作者分享。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com