Goldengate参数_Formatsql
程序员文章站
2022-06-14 20:35:11
...
闲着没事做,突然看到了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 DELETE FROM ogg_test WHERE owner='SYS' AND rownum 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, 感谢原作者分享。