场景
Oracle同步数据最佳的解决方案是自家的ogg,但是考虑到成本,需要找到其他的解决方案。如果是MySQL通过kafka同步,问题简单的多,因为阿里巴巴的开源数据同步方案——canel是最佳的解决方案。但是,canel只开源了MySQL数据库同步方案,Oracle同步没有开源出来。
借鉴canel的思路,canel通过实时获取MySQL的binlog日志做数据同步,Oracle有没有类似的方法呢
答案是肯定的,那就是Oracle的日志分析工具——logmner。通过实时获取logmner的重做日志,筛选出需要的信息,发送到kafka即可。
topic数据格式
以下是一个消息报文例子
{
"sqlList": [
{
"dbType": "Oracle",
"key": "3172712",
"oprType": "UPDATE",
"tableName": "PRODUCT_CHANNEL_CFG",
"status": "1",
"dbSql": "update \"PRODUCT_CHANNEL_CFG\" set \"STATUS\" = '1', \"OPR_CODE\" = '2' where \"PRODUCT_ID\" = '21000004' and \"CHANNEL_ID\" = '12345678' and \"STATUS\" = '1' and \"INURE_TIME\" = TO_DATE('01-1月 -19', 'DD-MON-RR') and \"EXPIRE_TIME\" = TO_DATE('01-1月 -19', 'DD-MON-RR') and \"OPR_CODE\" = '1' and \"EFFT_TYPE\" = '1' and \"CREATE_ID\" = '1000815' and \"CREATE_TIME\" = TO_DATE('23-8月 -19', 'DD-MON-RR') and \"MODIFY_ID\" IS NULL and \"MODIFY_TIME\" IS NULL;"
},
{
"dbType": "MySql",
"key": "3172712",
"oprType": "UPDATE",
"tableName": "PRODUCT_CHANNEL_CFG",
"status": "1",
"dbSql": "update product_channel_cfg set status = '1', opr_code = '2' where product_id = '21000004' and channel_id = '12345678' and status = '1' and inure_time = str_to_date('20190101', '%Y%m%d%H') and expire_time = str_to_date('20190101', '%Y%m%d%H') and opr_code = '1' and efft_type = '1' and create_id = '1000815' and create_time = str_to_date('20190823', '%Y%m%d%H') and modify_id is null and modify_time is null;"
}
]
}
代码及步骤
- 首先,你要开启Oracle的重做日志,以及补充日志,具体步骤这不再赘述
- 代码如下:
AcceptBean
import java.util.List;
public class AcceptBean {
private List<InputBean> sqlList;
public List<InputBean> getSqlList() {
return sqlList;
}
public void setSqlList(List<InputBean> sqlList) {
this.sqlList = sqlList;
}
}
InputBean
public class InputBean {
private String key;
private String dbType;
private String dbSql;
public String getDbType() {
return dbType;
}
public void setDbType(String dbType) {
this.dbType = dbType;
}
public String getDbSql() {
return dbSql;
}
public void setDbSql(String dbSql) {
this.dbSql = dbSql;
}
public String getKey() {
return key;
}
public void setKey(String key) {
this.key = key;
}
}
KafkaProducerDemo
import org.apache.kafka.clients.producer.*;
import java.util.Properties;
public class KafkaProducerDemo {
private final Producer<String,String> kafkaProducer;
public final static String TOPIC="POC_TOPIC";
public KafkaProducerDemo(){
kafkaProducer=createKafkaProducer() ;
}
private Producer<String,String> createKafkaProducer(){
Properties props = new Properties();
props.put("bootstrap.servers", "127.0.0.1:9092");
props.put("acks", "all");
props.put(<