利用OGG实现Oracle到Kafka到Greenplum的增量数据同步

墨墨导读：的全过程。墨天轮主页：https://www.modb.pro/u/6722背景在大数据库时代，数据经常需要在不同的数据库之间流动、整合，并要求具有一定的实时性，传统的通过脚本定时，批量同步的方式根本无法满足需求。本文基于Oracle OGG，Kafka消息队列实现Oracle到Greenplum之间的准实时同步（实测延时在ms级别）。一、环境准备–版本OGG版本IP源端oracle 12.2.0.1123012_fbo_ggs_Linux_x64_shiphome.zip192.168.11.151目标端kafka 2.12OGG_BigData_Linux_x64_12.3.2.1.1.zip192.168.11.165/4/3二、ORACLE,GREENPLUM,OGG安装此过程所用方法比较通用，此处不再赘述。三、源库配置源库需要开启归档，配置ogg用户，表空间及相关参数等，此处不再详细赘述四、源端OGG配置1. 进程配置mgr.prmPORT 7809extn.prmextract extnuserid C##GGADMIN@ora12c,password GGadmin_123DISCARDFILE ./dirrpt/extn.dsc, APPEND, MEGABYTES 1024EXTTRAIL ./dirdat/naGETUPDATEBEFORESNOCOMPRESSUPDATESNOCOMPRESSDELETESDYNAMICRESOLUTION--源库是一个CDB，本次测试抽取pdb_test下面的两张表table pdb_test.test.ccps_traderecord;table pdb_test.test.ccps_unnormal_traderecord;dpen.prmextract dpenRMTHOST 192.168.11.165, MGRPORT 7809RMTTRAIL ./dirdat/naDYNAMICRESOLUTIONTABLE pdb_test.test.*;2. 创建进程add extract extn, TRANLOG, BEGIN NOWadd exttrail ./dirdat/na, EXTRACT extn, MEGABYTES 200ADD EXTRACT dpen, EXTTRAILSOURCE ./dirdat/naADD RMTTRAIL ./dirdat/na, EXTRACT dpen, MEGABYTES 200五、目标端OGG配置1. 配置JDBC Hander参数示例目录下面有配置模板，复制过来即可cp /home/oracle/ogg/AdapterExamples/big-data/kafka/* /home/oracle/ogg/dirprmcustom_kafka_producer.properties：bootstrap.servers=localhost:9092acks=1reconnect.backoff.ms=1000value.serializer=org.apache.kafka.common.serialization.ByteArraySerializerkey.serializer=org.apache.kafka.common.serialization.ByteArraySerializer# 100KB per partitionbatch.size=16384linger.ms=0max.request.size = 5024000send.buffer.bytes = 5024000kafka.propsgg.handlerlist = kafkahandlergg.handler.kafkahandler.type=kafkagg.handler.kafkahandler.KafkaProducerConfigFile=custom_kafka_producer.properties#The following resolves the topic name using the short table namegg.handler.kafkahandler.topicMappingTemplate=oggtopic#The following selects the message key using the concatenated primary keys#gg.handler.kafkahandler.keyMappingTemplate=${primaryKeys}gg.handler.kafkahandler.format=jsongg.handler.kafkahandler.SchemaTopicName=mySchemaTopicgg.handler.kafkahandler.BlockingSend =falsegg.handler.kafkahandler.includeTokens=falsegg.handler.kafkahandler.mode=txgoldengate.userexit.writers=javawriterjavawriter.stats.display=TRUEjavawriter.stats.full=TRUEgg.log=log4jgg.log.level=INFOgg.report.time=30sec#Sample gg.classpath for Apache Kafkagg.classpath=dirprm/:/home/oracle/kafka/libs/*#Sample gg.classpath for HDP#gg.classpath=/etc/kafka/conf:/usr/hdp/current/kafka-broker/libs/*javawriter.bootoptions=-Xmx512m -Xms512m -Djava.class.path=ggjava/ggjava.jar2. 进程配置mgr.prmport 7809repn.prmreplicat repngetenv(JAVA_HOME)getenv(CLASSPATH)getenv(PATH)getenv(LD_LIBRARY_PATH)SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")TARGETDB LIBFILE libggjava.so SET property=./dirprm/kafka.propsSOURCEDEFS /home/oracle/ogg/dirdef/source.defREPORTCOUNT EVERY 1 MINUTES, RATE--GROUPTRANSOPS 10000MAP pdb_test.test.ccps_traderecord, TARGET ccps.ccps_traderecord;MAP pdb_test.test.ccps_unnormal_traderecord, TARGET ccps.ccps_unnormal_traderecord;3. 创建进程add replicat repn, exttrail ./dirdat/na六、目标端kafka安装配置1. 安装解压即可（此版本kafka本身已经集成zookeeper，不需要单独安装。）2. 配置因为是测试环境，此处列出的都是一些关键性参数，至于其他性能相关参数根据自己需要调整即可。consumer.propertiesbootstrap.servers=localhost:9092server.propertieslisteners=PLAINTEXT://localhost:9092zookeeper.connect=localhost:2181zookeeper.propertiesclientPort=21813. 启动zookeeper[oracle@gpmaster kafka]$ bin/zookeeper-server-start.sh -daemon config/zookeeper.properties[oracle@gpmaster kafka]$ jps18001 Jps2505 QuorumPeerMain4. 启动kafka[oracle@gpmaster kafka]$ bin/kafka-server-start.sh config/server.properties [oracle@gpmaster kafka]$ jps18016 Kafka2505 QuorumPeerMain18458 Jps5. kafka常用命令显示所有topic（其中oggtopic就是用于此次测试的topic，kafka.props中配置的名字）：[oracle@gpmaster bin]$ kafka-topics.sh --list --zookeeper localhost:2181__consumer_offsetsmySchemaTopicoggtestoggtopictest生产消息：[oracle@gpmaster bin]$ kafka-console-producer.sh --broker-list localhost:9092 --topic test>test kafka!!!!>消费消息：[oracle@gpmaster bin]$ kafka-console-consumer.sh --bootstrap-server localhost:9092 --topic test test kafka!!!!七、编写代码将数据从kafka加载到GPfrom kafka import KafkaConsumerimport jsonimport psycopg2import timeimport redbconn = psycopg2.connect(dbname = "devin",user = "ccps",password = "xjlove1224",host = "192.168.11.165",port = "5432")consumer = KafkaConsumer('oggtopic',group_id = "oggtopic",auto_offset_reset='earliest',bootstrap_servers=['localhost:9092'])for msg in consumer: message = msg.value mesg = message.decode('utf-8') msglist = re.findall(r'(.*?}})',mesg) --切分代码细节略，需要注意NULL，各种特殊字符以及异常的处理。八、测试测试环境，目标端没有做初始化，运行结果如下：抽取到kafka的数据，json格式：{"table":"CCPS.CCPS_UNNORMAL_TRADERECORD","op_type":"I","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.146000","pos":"00000000000026312368","after":{"UTR_ID":923628,"UTR_NO":"200818181648559171322","UTR_MER_NO":191713,"UTR_GW_NO":19171301,"UTR_MER_ORDERNO":"1597745803378","UTR_CURRENCY":"USD","UTR_AMOUNT":"0.01","UTR_BANK_CODE":null,"UTR_CHA_CODE":null,"UTR_RETURNURL":null,"UTR_WEBSITE":"http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp","UTR_SUBMITURL":"http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay","UTR_PAYSTARTTIME":"2020-08-18 18:16:48.233971000","UTR_PAYTIME":null,"UTR_PAYENDTIME":null,"UTR_ERRORCODE":"50000","UTR_ERRORREASON_OUT":"Unpaid","UTR_ERRORREASON_IN":"Unpaid","UTR_MER_REMARK":"2fang","UTR_RISKINFO":null,"UTR_SETSCORE":null,"UTR_TOTALSCORE":null,"UTR_PASSRISKINFO":null,"UTR_PM_ID":1,"UTR_CARDNO_ENCRYPT":null,"UTR_NOTICEURL":null,"UTR_BILL_ADDRESS_WHITE":null,"UTR_BILL_ADDRESS_EXTRA1_WHITE":null,"UTR_LOGO_URL":null,"UTR_LANGUAGE":null,"UTR_PAY_EXPIRATIONTIME":null,"UTR_MCC":null,"UTR_PM_NAME":"Credit Card","UTR_TRADE_BANK_NAME":null,"UTR_EXPIRATION_TIME":null}}{"table":"CCPS.CCPS_UNNORMAL_TRADERECORD","op_type":"U","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.167000","pos":"00000000000026316287","before":{},"after":{"UTR_NO":"200818181648559171322","UTR_CARDNO_ENCRYPT":"GuL6A5JN4rwAaHb8fPSAMdhxRA9CL1RVIjWwP81YujHEvXuz0YnmBssWqslPmsAgsuPeKBdQarDb4B23oy0C6wMJYner9LKmRVbuNfuGummiWnRmHAM9Dn2dJKcujEPProxYeBWUMbEhk9V54Q018cML1bcf3fvJjKL/vt4xBbk="}}{"table":"CCPS.CCPS_UNNORMAL_TRADERECORD","op_type":"D","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.167001","pos":"00000000000026316959","before":{"UTR_ID":923628,"UTR_NO":"200818181648559171322","UTR_MER_NO":191713,"UTR_GW_NO":19171301,"UTR_MER_ORDERNO":"1597745803378","UTR_CURRENCY":"USD","UTR_AMOUNT":"0.01","UTR_BANK_CODE":null,"UTR_CHA_CODE":null,"UTR_RETURNURL":null,"UTR_WEBSITE":"http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp","UTR_SUBMITURL":"http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay","UTR_PAYSTARTTIME":"2020-08-18 18:16:48.233971000","UTR_PAYTIME":null,"UTR_PAYENDTIME":null,"UTR_ERRORCODE":"50000","UTR_ERRORREASON_OUT":"Unpaid","UTR_ERRORREASON_IN":"Unpaid","UTR_MER_REMARK":"2fang","UTR_RISKINFO":null,"UTR_SETSCORE":null,"UTR_TOTALSCORE":null,"UTR_PASSRISKINFO":null,"UTR_PM_ID":1,"UTR_CARDNO_ENCRYPT":"GuL6A5JN4rwAaHb8fPSAMdhxRA9CL1RVIjWwP81YujHEvXuz0YnmBssWqslPmsAgsuPeKBdQarDb4B23oy0C6wMJYner9LKmRVbuNfuGummiWnRmHAM9Dn2dJKcujEPProxYeBWUMbEhk9V54Q018cML1bcf3fvJjKL/vt4xBbk=","UTR_NOTICEURL":null,"UTR_BILL_ADDRESS_WHITE":null,"UTR_BILL_ADDRESS_EXTRA1_WHITE":null,"UTR_LOGO_URL":null,"UTR_LANGUAGE":null,"UTR_PAY_EXPIRATIONTIME":null,"UTR_MCC":null,"UTR_PM_NAME":"Credit Card","UTR_TRADE_BANK_NAME":null,"UTR_EXPIRATION_TIME":null}}{"table":"CCPS.CCPS_TRADERECORD","op_type":"I","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.168000","pos":"00000000000026318029","after":{"TR_ID":787160,"TR_NO":"200818181648559171322","TR_MER_ORDERNO":"1597745803378","TR_MER_NO":191713,"TR_GW_NO":19171301,"TR_CURRENCY":"USD","TR_AMOUNT":0.01,"TR_STATUS":-1,"TR_TRADE_RATE":0.0100,"TR_SPP_CURRENCY":"USD","TR_SPP":0,"TR_AGENT_NO":0,"TR_AGENT_RATE":null,"TR_AGENT_SPP_CURRENCY":null,"TR_AGENT_SPP":null,"TR_RESEVER_RATE":0.1000,"TR_RATE_VALUE":1,"TR_BANKCURRENCY":"USD","TR_BANKAMOUT":0.01,"TR_BANK_CODE":"OPBank","TR_CHA_CODE":121,"TR_ISDELAY":0,"TR_CHA_RATE":0.0100,"TR_BANK_SPP_CURRENCY":"USD","TR_BANK_SPP":1.00,"TR_CHA_SETT_BANK":null,"TR_BANKORDERNO":null,"TR_BANKRETURNCODE":null,"TR_BANKINFO":null,"TR_PAYSTARTTIME":"2020-08-18 18:16:48.139000000","TR_PAYENDTIME":null,"TR_DATETIME":"2020-08-18 18:16:48.346618000","TR_BANKDATETIME":null,"TR_RETURNURL":null,"TR_WEBSITE":"http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp","TR_SUBMITURL":"http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay","TR_CHECKED":0,"TR_CHECKDATETIME":null,"TR_REFUNDMENT":0,"TR_PROTEST":0,"TR_CONGEAL":0,"TR_DELIVERY":null,"TR_REFUNDMENT_AMOUNT":0,"TR_PROTEST_AMOUNT":0,"TR_EXP_AMOUNT":0,"TR_CONGEAL_AMOUNT":0,"TR_UNSETT_AMOUNT":null,"TR_TS_ID":0,"TR_TS_STATUS":0,"TR_RS_ID":0,"TR_RS_STATUS":0,"TR_AS_ID":0,"TR_AS_STATUS":0,"TR_QUERYNO":null,"TR_AUTHORIZELD":null,"TR_BATCHNO":null,"TR_TERMINALNO":null,"TR_ISDCC":0,"TR_TS_BATCH":0,"TR_ISTSCHECK":0,"TR_RS_BATCH":0,"TR_ISRSCHECK":0,"TR_ISDAYCHECK":0,"TR_ISEXPLAIN":-2,"TR_ISFRAUD":0,"TR_ISCOMPLAIN":0,"TR_INF_TYPE":2,"TR_ISLOCK":0,"TR_FEE_FAIL_MER":0,"TR_FEE_SUCCESS_MER":0,"TR_FEE_SUCCESS_AFTER_MER":0,"TR_IS_BACK_MER":0,"TR_IS_BACK_AFTER_MER":0,"TR_FEE_FAIL_AGENT":0,"TR_FEE_SUCCESS_AGENT":0,"TR_FEE_SUCCESS_AFTER_AGENT":0,"TR_IS_BACK_AGENT":0,"TR_IS_BACK_AFTER_AGENT":0,"TR_FEE_FAIL_CHA":1,"TR_FEE_SUCCESS_CHA":1,"TR_FEE_SUCCESS_AFTER_CHA":1,"TR_IS_BACK_CHA":1,"TR_IS_BACK_AFTER_CHA":1,"TR_REMARK":"2fang","TR_BANKTRADETIME":null,"TR_CARDTYPE":1,"TR_REFERENCE":"200818181648559171322","TR_IS_REPAY":0,"TR_RISKINFO":null,"TR_SETSCORE":0,"TR_TOTALSCORE":98.00,"TR_PASSRISKINFO":null,"TR_PM_ID":1,"TR_AUTH_TYPE":0,"TR_SEND_CR_FLAG":null,"TR_SEND_MER_FLAG":null,"TR_REFCHAPRO_FLAG":null,"TR_CLEAR_AMOUNT":null,"TR_CLEAR_TIME":null,"TR_CLEAR_STATUS":null,"TR_BANK_CHARGES":null,"TR_CLEAR_CURRENCY":null,"TR_AUTH_OP_TIME":null,"TR_DEBITTIME":"2020-08-18 18:16:48.346618000","TR_SETT_RATE":1,"TR_SETT_AMOUNT":0.01,"TR_SETT_CURRENCY":"USD","TR_TRANRETURNCODE":null,"TR_TRANINFO":null,"TR_WHETHER_3D":"0","TR_ECI_VALUE":null,"TR_ISAUTH":0,"TR_SETTLEMENT":0,"TR_NOTICEURL":null,"TR_NOTICESTATUS":null,"TR_CHARESERVERATE":0.0100,"TR_REFUSEAMOUNT":0,"TR_REFOUNDAMOUNT":0,"TR_BAILAMOUNT":null,"TR_IS_MER_REFOUND":0,"TR_IS_MER_REFUSE":0,"TR_SETT_SPP":0,"TR_BILL_ADDRESS":"OPBANK 账单地址","TR_PAY_SOLUTIONS":null,"TR_PAY_INSTALLCOUNT":null,"TR_PAY_INSTALLRATE":null,"TR_SPP_RATE":1,"TR_RATE_INSTALLRATE":0,"TR_MER_LOGOURL":null,"TR_CLEAR_BATCHNO":null,"TR_CLEAR_REMARK":null,"TR_IS_MER_REFOUND_PART":0,"TR_BILL_ADDRESS_WHITE":null,"TR_BILL_ADDRESS_EXTRA1_WHITE":null,"TR_PRIOR_CHANNEL":0,"TR_SETTLEMENT_BANK":null,"TR_SUB_BATCHNO_BANK":null,"TR_SUB_BANK_NO":null,"TR_BANK_REMARK":null,"TR_CONTRACTTYPE":302,"TR_SETTLEMENTBODY":8,"TR_CHANNELBODY":2,"TR_SETTLEMENTAMONT":null,"TR_TSINGLEFEE":null,"TR_TREFUNDFEE":null,"TR_TRATE":null,"TR_TRATERETURN":null,"TR_TAXATION":null,"TR_MIN_TRADE_FEE":0,"TR_BANK_RATE_HKD":7.75081,"TR_SETT_RATE_HKD":7.75081,"TR_RETURN_AVS":null,"TR_RETURN_CVV":null,"TR_TYPE":1,"TR_SETT_STATUS":null,"TR_REFUND_LOCK":null,"TR_MCC":5722,"TR_UPDATE_ORDER_STATUS":0,"TR_MANUAL_LIQUIDATION_REMARK":null,"TR_OP_ENTITY":1,"TR_TRADE_BANK_NAME":null,"TR_CLEAR_RATE":null,"TR_INTERCHANGE_FEE":null,"TR_SCHEME_FEE":null,"TR_ACQUIRER_FEE":null,"TR_CLEAR_TRADE_RATE":null,"TR_CROSS_BORDER_FEE":null,"TR_AVS_OUT_RESULT":null,"TR_CAVV":null,"TR_BANK_PID":null,"TR_EXPIRATION_TIME":null,"TR_TCHARGEBACK_FEE":null,"TR_TCONGEAL_FEE":null,"TR_FXAJD_FEE":null,"TR_TOTHER_FEE":null}}{"table":"CCPS.CCPS_TRADERECORD","op_type":"U","op_ts":"2020-08-18 18:15:48.048933","current_ts":"2020-08-18T18:15:54.168001","pos":"00000000000026320941","before":{},"after":{"TR_NO":"200818181648559171322","TR_STATUS":1,"TR_BANKORDERNO":null,"TR_BANKRETURNCODE":"80000","TR_BANKINFO":"Transaction Approved1","TR_BANKDATETIME":"2020-08-18 18:16:48.360706000","TR_CHECKED":0,"TR_CHECKDATETIME":"2020-08-18 18:16:48.000000000","TR_QUERYNO":null,"TR_AUTHORIZELD":null,"TR_BATCHNO":null,"TR_TERMINALNO":null,"TR_AUTH_TYPE":0,"TR_DEBITTIME":"2020-08-18 18:16:48.360706000","TR_TRANRETURNCODE":"10000","TR_TRANINFO":"Transaction Approved","TR_WHETHER_3D":"0","TR_ECI_VALUE":null,"TR_NOTICESTATUS":2,"TR_PAY_SOLUTIONS":"Transaction Approved3","TR_BANK_REMARK":";;","TR_RETURN_AVS":null,"TR_RETURN_CVV":null,"TR_UPDATE_ORDER_STATUS":0}}{"table":"CCPS.CCPS_TRADERECORD","op_type":"U","op_ts":"2020-08-18 18:16:01.042146","current_ts":"2020-08-18T18:16:06.549000","pos":"00000000000026321695","before":{},"after":{"TR_NO":"160627142027150120836","TR_RS_ID":1000779,"TR_RS_STATUS":2,"TR_UPDATE_ORDER_STATUS":0}}python解析后的SQL：insert into CCPS_UNNORMAL_TRADERECORD(UTR_ID, UTR_NO, UTR_MER_NO, UTR_GW_NO, UTR_MER_ORDERNO, UTR_CURRENCY, UTR_AMOUNT, UTR_BANK_CODE, UTR_CHA_CODE, UTR_RETURNURL, UTR_WEBSITE, UTR_SUBMITURL, UTR_PAYSTARTTIME, UTR_PAYTIME, UTR_PAYENDTIME, UTR_ERRORCODE, UTR_ERRORREASON_OUT, UTR_ERRORREASON_IN, UTR_MER_REMARK, UTR_RISKINFO, UTR_SETSCORE, UTR_TOTALSCORE, UTR_PASSRISKINFO, UTR_PM_ID, UTR_CARDNO_ENCRYPT, UTR_NOTICEURL, UTR_BILL_ADDRESS_WHITE, UTR_BILL_ADDRESS_EXTRA1_WHITE, UTR_LOGO_URL, UTR_LANGUAGE, UTR_PAY_EXPIRATIONTIME, UTR_MCC, UTR_PM_NAME, UTR_TRADE_BANK_NAME, UTR_EXPIRATION_TIME) values (923628, '200818181648559171322', 191713, 19171301, '1597745803378', 'USD', '0.01', NULL, NULL, NULL, 'http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp', 'http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay', '2020-08-18 18:16:48.233971000', NULL, NULL, '50000', 'Unpaid', 'Unpaid', '2fang', NULL, NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Credit Card', NULL, NULL);update CCPS_UNNORMAL_TRADERECORD set UTR_CARDNO_ENCRYPT = 'GuL6A5JN4rwAaHb8fPSAMdhxRA9CL1RVIjWwP81YujHEvXuz0YnmBssWqslPmsAgsuPeKBdQarDb4B23oy0C6wMJYner9LKmRVbuNfuGummiWnRmHAM9Dn2dJKcujEPProxYeBWUMbEhk9V54Q018cML1bcf3fvJjKL/vt4xBbk=' where UTR_NO = '200818181648559171322'delete from CCPS_UNNORMAL_TRADERECORD where UTR_ID = 923628insert into CCPS_TRADERECORD(TR_ID, TR_NO, TR_MER_ORDERNO, TR_MER_NO, TR_GW_NO, TR_CURRENCY, TR_AMOUNT, TR_STATUS, TR_TRADE_RATE, TR_SPP_CURRENCY, TR_SPP, TR_AGENT_NO, TR_AGENT_RATE, TR_AGENT_SPP_CURRENCY, TR_AGENT_SPP, TR_RESEVER_RATE, TR_RATE_VALUE, TR_BANKCURRENCY, TR_BANKAMOUT, TR_BANK_CODE, TR_CHA_CODE, TR_ISDELAY, TR_CHA_RATE, TR_BANK_SPP_CURRENCY, TR_BANK_SPP, TR_CHA_SETT_BANK, TR_BANKORDERNO, TR_BANKRETURNCODE, TR_BANKINFO, TR_PAYSTARTTIME, TR_PAYENDTIME, TR_DATETIME, TR_BANKDATETIME, TR_RETURNURL, TR_WEBSITE, TR_SUBMITURL, TR_CHECKED, TR_CHECKDATETIME, TR_REFUNDMENT, TR_PROTEST, TR_CONGEAL, TR_DELIVERY, TR_REFUNDMENT_AMOUNT, TR_PROTEST_AMOUNT, TR_EXP_AMOUNT, TR_CONGEAL_AMOUNT, TR_UNSETT_AMOUNT, TR_TS_ID, TR_TS_STATUS, TR_RS_ID, TR_RS_STATUS, TR_AS_ID, TR_AS_STATUS, TR_QUERYNO, TR_AUTHORIZELD, TR_BATCHNO, TR_TERMINALNO, TR_ISDCC, TR_TS_BATCH, TR_ISTSCHECK, TR_RS_BATCH, TR_ISRSCHECK, TR_ISDAYCHECK, TR_ISEXPLAIN, TR_ISFRAUD, TR_ISCOMPLAIN, TR_INF_TYPE, TR_ISLOCK, TR_FEE_FAIL_MER, TR_FEE_SUCCESS_MER, TR_FEE_SUCCESS_AFTER_MER, TR_IS_BACK_MER, TR_IS_BACK_AFTER_MER, TR_FEE_FAIL_AGENT, TR_FEE_SUCCESS_AGENT, TR_FEE_SUCCESS_AFTER_AGENT, TR_IS_BACK_AGENT, TR_IS_BACK_AFTER_AGENT, TR_FEE_FAIL_CHA, TR_FEE_SUCCESS_CHA, TR_FEE_SUCCESS_AFTER_CHA, TR_IS_BACK_CHA, TR_IS_BACK_AFTER_CHA, TR_REMARK, TR_BANKTRADETIME, TR_CARDTYPE, TR_REFERENCE, TR_IS_REPAY, TR_RISKINFO, TR_SETSCORE, TR_TOTALSCORE, TR_PASSRISKINFO, TR_PM_ID, TR_AUTH_TYPE, TR_SEND_CR_FLAG, TR_SEND_MER_FLAG, TR_REFCHAPRO_FLAG, TR_CLEAR_AMOUNT, TR_CLEAR_TIME, TR_CLEAR_STATUS, TR_BANK_CHARGES, TR_CLEAR_CURRENCY, TR_AUTH_OP_TIME, TR_DEBITTIME, TR_SETT_RATE, TR_SETT_AMOUNT, TR_SETT_CURRENCY, TR_TRANRETURNCODE, TR_TRANINFO, TR_WHETHER_3D, TR_ECI_VALUE, TR_ISAUTH, TR_SETTLEMENT, TR_NOTICEURL, TR_NOTICESTATUS, TR_CHARESERVERATE, TR_REFUSEAMOUNT, TR_REFOUNDAMOUNT, TR_BAILAMOUNT, TR_IS_MER_REFOUND, TR_IS_MER_REFUSE, TR_SETT_SPP, TR_BILL_ADDRESS, TR_PAY_SOLUTIONS, TR_PAY_INSTALLCOUNT, TR_PAY_INSTALLRATE, TR_SPP_RATE, TR_RATE_INSTALLRATE, TR_MER_LOGOURL, TR_CLEAR_BATCHNO, TR_CLEAR_REMARK, TR_IS_MER_REFOUND_PART, TR_BILL_ADDRESS_WHITE, TR_BILL_ADDRESS_EXTRA1_WHITE, TR_PRIOR_CHANNEL, TR_SETTLEMENT_BANK, TR_SUB_BATCHNO_BANK, TR_SUB_BANK_NO, TR_BANK_REMARK, TR_CONTRACTTYPE, TR_SETTLEMENTBODY, TR_CHANNELBODY, TR_SETTLEMENTAMONT, TR_TSINGLEFEE, TR_TREFUNDFEE, TR_TRATE, TR_TRATERETURN, TR_TAXATION, TR_MIN_TRADE_FEE, TR_BANK_RATE_HKD, TR_SETT_RATE_HKD, TR_RETURN_AVS, TR_RETURN_CVV, TR_TYPE, TR_SETT_STATUS, TR_REFUND_LOCK, TR_MCC, TR_UPDATE_ORDER_STATUS, TR_MANUAL_LIQUIDATION_REMARK, TR_OP_ENTITY, TR_TRADE_BANK_NAME, TR_CLEAR_RATE, TR_INTERCHANGE_FEE, TR_SCHEME_FEE, TR_ACQUIRER_FEE, TR_CLEAR_TRADE_RATE, TR_CROSS_BORDER_FEE, TR_AVS_OUT_RESULT, TR_CAVV, TR_BANK_PID, TR_EXPIRATION_TIME, TR_TCHARGEBACK_FEE, TR_TCONGEAL_FEE, TR_FXAJD_FEE, TR_TOTHER_FEE) values (787160, '200818181648559171322', '1597745803378', 191713, 19171301, 'USD', 0.01, -1, 0.01, 'USD', 0, 0, NULL, NULL, NULL, 0.1, 1, 'USD', 0.01, 'OPBank', 121, 0, 0.01, 'USD', 1.0, NULL, NULL, NULL, NULL, '2020-08-18 18:16:48.139000000', NULL, '2020-08-18 18:16:48.346618000', NULL, NULL, 'http://192.168.11.98:7105/Test/jsp/testPayTwo.jsp', 'http://192.168.11.98:7105/PaymentGateway/gateway/directservice/pay', 0, NULL, 0, 0, 0, NULL, 0, 0, 0, 0, NULL, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, 0, 0, 0, 0, 0, 0, -2, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 1, '2fang', NULL, 1, '200818181648559171322', 0, NULL, 0, 98.0, NULL, 1, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2020-08-18 18:16:48.346618000', 1, 0.01, 'USD', NULL, NULL, '0', NULL, 0, 0, NULL, NULL, 0.01, 0, 0, NULL, 0, 0, 0, 'OPBANK 账单地址', NULL, NULL, NULL, 1, 0, NULL, NULL, NULL, 0, NULL, NULL, 0, NULL, NULL, NULL, NULL, 302, 8, 2, NULL, NULL, NULL, NULL, NULL, NULL, 0, 7.75081, 7.75081, NULL, NULL, 1, NULL, NULL, 5722, 0, NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);update CCPS_TRADERECORD set TR_STATUS = 1,TR_BANKORDERNO = NULL,TR_BANKRETURNCODE = '80000',TR_BANKINFO = 'Transaction Approved1',TR_BANKDATETIME = '2020-08-18 18:16:48.360706000',TR_CHECKED = 0,TR_CHECKDATETIME = '2020-08-18 18:16:48.000000000',TR_QUERYNO = NULL,TR_AUTHORIZELD = NULL,TR_BATCHNO = NULL,TR_TERMINALNO = NULL,TR_AUTH_TYPE = 0,TR_DEBITTIME = '2020-08-18 18:16:48.360706000',TR_TRANRETURNCODE = '10000',TR_TRANINFO = 'Transaction Approved',TR_WHETHER_3D = '0',TR_ECI_VALUE = NULL,TR_NOTICESTATUS = 2,TR_PAY_SOLUTIONS = 'Transaction Approved3',TR_BANK_REMARK = ';;',TR_RETURN_AVS = NULL,TR_RETURN_CVV = NULL,TR_UPDATE_ORDER_STATUS = 0 where TR_NO = '200818181648559171322'update CCPS_TRADERECORD set TR_RS_ID = 1000779,TR_RS_STATUS = 2,TR_UPDATE_ORDER_STATUS = 0 where TR_NO = '160627142027150120836'在GP数据库验证结果（测试数据及DML较多，随机抽了一条验证）：devin=> select TR_STATUS,TR_BANKRETURNCODE,TR_BANKDATETIME,TR_CHECKDATETIME,TR_TRANINFO,TR_BANK_REMARK from CCPS_TRADERECORD where TR_NO = '200818181648559171322'; tr_status | tr_bankreturncode | tr_bankdatetime | tr_checkdatetime | tr_traninfo | tr_bank_remark -----------+-------------------+----------------------------+---------------------+----------------------+---------------- 1 | 80000 | 2020-08-18 18:16:48.360706 | 2020-08-18 18:16:48 | Transaction Approved | ;;(1 row)至此，数据同步成功。作者肖杰：云和恩墨Oracle技术顾问，长期服务于银行，金融，能源等行业的数据中心，8年数据库运维经验，擅长Oracle，PostgreSQL等数据库高可用设计和运维故障处理，异常恢复，升级迁移，性能优化。墨天轮原文链接：https://www.modb.pro/db/29348（复制到浏览器中打开或者点击“阅读原文”立即查看）。