package com.alibaba.otter.canal.server.embedded.handle.oracle; import com.alibaba.otter.canal.instance.core.CanalInstance; import com.alibaba.otter.canal.meta.FileMixedMetaManager; import com.alibaba.otter.canal.parse.CanalEventParser; import com.alibaba.otter.canal.parse.inbound.mysql.MysqlEventParser; import com.alibaba.otter.canal.parse.support.AuthenticationInfo; import com.alibaba.otter.canal.protocol.FlatMessage; import com.alibaba.otter.canal.protocol.Message; import com.alibaba.otter.canal.server.embedded.handle.Constant; import com.alibaba.otter.canal.server.embedded.handle.NotMysqlHandler; import com.alibaba.otter.canal.server.exception.CanalServerException; import com.alibaba.otter.canal.utils.OracleUtil; import com.google.common.collect.Lists; import org.apache.commons.lang.StringUtils; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.util.CollectionUtils; import java.io.IOException; import java.io.PrintWriter; import java.io.StringWriter; import java.sql.*; import java.text.SimpleDateFormat; import java.util.Date; import java.util.*; import java.util.concurrent.atomic.AtomicLong; /** * @author : yangpeng * create at: 2022/5/21 13:09 * @description: oracle连接实现 */ public class OracleHandler implements NotMysqlHandler { private static final Logger logger = LoggerFactory.getLogger(OracleHandler.class); private final static String ORACLE_DRIVER_URL = "jdbc:oracle:thin:@"; private final static String DATA_DICTIONARY_PATH = "/oracle/oradata/orcl/logmnr"; private Connection connection; private AuthenticationInfo authInfo; private AtomicLong currentScn = new AtomicLong(0); private Map<String, List<String>> pkMaps; private List<String> whiteList; //归档日志本文件最后一个点位-1, 下一个文件的开始点位 private Long nextChange = null; private Long firstChange = null; private String currentModel; private String currentRedoGroups; private String lastRedoGroups; private String opt = ">="; private List<String> action = new ArrayList<>(); static { //加载驱动 try { Class.forName("oracle.jdbc.OracleDriver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } @Override public void subscribe(CanalInstance canalInstance) { //数据库基本信息 CanalEventParser eventParser = canalInstance.getEventParser(); MysqlEventParser mysqlEventParser = (MysqlEventParser) eventParser; this.authInfo = mysqlEventParser.getMasterInfo(); connect(); String filterRegex = authInfo.getFilterRegex(); if(!org.springframework.util.StringUtils.isEmpty(filterRegex) && !filterRegex.endsWith(".*")){ whiteList = Arrays.asList(filterRegex.split(",")); } logger.warn("订阅实例: {}, 过滤规则: {}, 初始 SCN: {}", canalInstance.getDestination(), filterRegex, currentScn.get()); // 新增日志 String index = FileMixedMetaManager.getScnLocal(canalInstance.getDestination()); //首次启动, 文件中的index是0, 同步类型: 全量+增量时使用衔接点位作为开始点位, //增量时衔接点位可能为空, 为空时获取系统最新点位作为开始点位 if("0".equals(index)){ String scn = authInfo.getTimestamp(); if(StringUtils.isEmpty(scn)) scn = getStartPosition(); try { this.currentScn.set(Long.valueOf(scn)); FileMixedMetaManager.saveScnLocal(canalInstance.getDestination(), Long.valueOf(scn)); } catch (IOException e) { e.printStackTrace(); } }else{ //index.scn文件中是非0点位, 说明当前实例运行被停止了或者被删除后实例重建了, 再次启动时比较文件点位和衔接点位 //衔接点位大于文件点位说明是删除后实例重建了, 删除后重建实例应使用最新的衔接点位开始增量同步 //衔接点位小于等于文件点位说明是暂停后重启了, 暂停后重启应使用文件中点位继续增量同步 String scn = authInfo.getTimestamp(); if(!StringUtils.isEmpty(scn) && Long.valueOf(scn) > Long.valueOf(index)){ try { this.currentScn.set(Long.valueOf(scn)); FileMixedMetaManager.saveScnLocal(canalInstance.getDestination(), Long.valueOf(scn)); } catch (IOException e) { e.printStackTrace(); } }else{ opt = ">"; this.currentScn.set(Long.valueOf(index)); } } if(StringUtils.isEmpty(authInfo.getAction())){ action.add("INSERT"); action.add("UPDATE"); action.add("DELETE"); }else{ action = Arrays.asList(authInfo.getAction().split(",")); } logger.warn("开始分析的点位:"+this.currentScn.get()); currentRedoGroups = queryCurrentRedoGroups(); lastRedoGroups = currentRedoGroups; queryLastArchivedLogScn(); authInfo.setArchived(true); updateSettings(); //获取主键 pkMaps = new HashMap<>(); for (String table : whiteList) { List<String> pkNames = getPkNames(authInfo.getDefaultDatabaseName(), table); pkMaps.put(authInfo.getDefaultDatabaseName()+"."+table, pkNames); } } private String queryCurrentRedoGroups(){ //获取当前正在使用的redo日志文件的相关组信息 Statement statement = null; ResultSet resultSet = null; try { statement = connection.createStatement(); resultSet = statement.executeQuery("SELECT GROUP# FROM V$LOG WHERE STATUS = 'CURRENT'"); resultSet.next(); return resultSet.getString(1); } catch (SQLException e) { logger.warn("Query Current Redo Group failed!"); } finally { close(statement, resultSet); } return ""; } private void queryLastArchivedLogScn(){ Statement statement = null; ResultSet resultSet = null; try { logger.warn("获取最后一个归档日志的开始点位和结束点位..."); statement = connection.createStatement(); String sql = "SELECT FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 ORDER BY RECID DESC"; logger.warn(sql); resultSet = statement.executeQuery(sql); if(resultSet.next()){ firstChange = resultSet.getLong(1); nextChange = resultSet.getLong(2); } } catch (Exception e) { logger.warn("query last one Archive log startScn and endScn failed!"); } finally { close(statement, resultSet); } } private void isArchived(ArrayList<String> logFiles){ Statement statement = null; ResultSet resultSet = null; try { logger.warn("分析归档日志..."); currentModel = "archive"; statement = connection.createStatement(); String sql = "SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND " + "FIRST_CHANGE# <= "+currentScn.get()+" AND NEXT_CHANGE# > "+currentScn.get(); logger.warn(sql); logger.warn("### [ARCHIVE] 查询归档日志: {}", sql); resultSet = statement.executeQuery(sql); logger.warn("分析归档日志，当前 SCN: {}, 匹配条件: FIRST_CHANGE# <= {} AND NEXT_CHANGE# > {}", currentScn.get(), currentScn.get(), currentScn.get()); // 新增日志 if(resultSet.next()){ String NAME = resultSet.getString(1); firstChange = resultSet.getLong(2); nextChange = resultSet.getLong(3); logger.warn("找到归档日志文件: {}, SCN 范围: [{}, {})", NAME, firstChange, nextChange); // 新增日志 //添加归档日志 logFiles.add(NAME); if(!Objects.equals(currentRedoGroups, lastRedoGroups)){ lastRedoGroups = currentRedoGroups; } } if(CollectionUtils.isEmpty(logFiles) && !Objects.equals(currentRedoGroups, lastRedoGroups)){ int num = 0; while (true){ resultSet = statement.executeQuery(sql); if(resultSet.next()){ String NAME = resultSet.getString(1); firstChange = resultSet.getLong(2); nextChange = resultSet.getLong(3); //添加归档日志 logFiles.add(NAME); } //在线日志写入到归档异步进行, 可能刚切换归档日志还没有落地, 若没获取到日志文件就阻塞, 最大60秒 if(CollectionUtils.isEmpty(logFiles)){ Thread.sleep(1000L); num++; logger.warn("在线日志写入到归档未完成, 线程已等待: "+num+"秒"); if(num >= 60) { logger.warn("在线日志写入到归档未完成等待60秒未完成, 默认放弃分析本次归档日志, 可能造成数据丢失, 丢失可能发生的点位:"+currentScn.get()); break; } } else{ lastRedoGroups = currentRedoGroups; break; } } } } catch (Exception e) { logger.warn("Archive log parsing failed!"); } finally { close(statement, resultSet); } } private void addLogfile(ArrayList<String> logFiles){ if(CollectionUtils.isEmpty(logFiles)){ Statement statement = null; ResultSet resultSet = null; try { logger.warn("分析在线日志..."); currentModel = "online"; statement = connection.createStatement(); //分析前校验日志组是否发生了变更, 发生变更的话从分析归档开始 String group = queryCurrentRedoGroups(); if(!currentRedoGroups.equals(group)){ currentRedoGroups = group; isArchived(logFiles); }else{ String sql = "SELECT f.member,g.group#,g.FIRST_CHANGE# FROM v$log g left join v$logfile f on g.group# = f.group# " + "where g.FIRST_CHANGE# <= " + currentScn.get() + " AND g.NEXT_CHANGE# > " + currentScn.get(); resultSet = statement.executeQuery(sql); logger.warn(sql); logger.warn("### [ONLINE] 查询在线日志: {}", sql); while (resultSet.next()) { String fileName = resultSet.getString(1); if(!fileName.contains("sredo")){ logFiles.add(fileName); firstChange = resultSet.getLong(3); } } } } catch (SQLException e) { logger.warn("Description Failed to query online logs!"); } finally { close(statement, resultSet); } } } private void offsetLogfile(ArrayList<String> logFiles){ if(CollectionUtils.isEmpty(logFiles)){ Statement statement = null; ResultSet resultSet = null; try { logger.warn("补偿措施, 分析最新的在线日志..."); currentModel = "online"; statement = connection.createStatement(); String sql = "SELECT f.member,g.group#,g.FIRST_CHANGE# FROM v$log g left join v$logfile f on g.group# = f.group# where g.status = 'CURRENT'"; resultSet = statement.executeQuery(sql); logger.warn(sql); while (resultSet.next()) { String fileName = resultSet.getString(1); if(!fileName.contains("sredo")){ logFiles.add(fileName); firstChange = resultSet.getLong(3); } } if(!CollectionUtils.isEmpty(logFiles)){ currentScn.set(firstChange); opt = ">="; } } catch (SQLException e) { logger.warn("Description Failed to query online logs!"); } finally { close(statement, resultSet); } } } private void setSqlTrace() { String setSqlTraceSql = "BEGIN\n" + " DBMS_SESSION.SET_SQL_TRACE(FALSE);\n" + " END;"; logger.warn(setSqlTraceSql); getCallableStatement(setSqlTraceSql); } private void setLogAnalyseDir(){ String createDictSql = "BEGIN dbms_logmnr_d.build(dictionary_filename =>'" + authInfo.getOraName() + "', dictionary_location =>'" + DATA_DICTIONARY_PATH + "'); END;"; logger.warn(createDictSql); getCallableStatement(createDictSql); } private void addAllfile(ArrayList<String> logFiles){ logger.warn("添加 LogMiner 日志文件: {}", logFiles); // 新增日志 StringBuilder sbSQL = new StringBuilder(); //logmnr最大分析文件个数3 for (int i = 0; i < logFiles.size(); i++) { if (i == 0) { sbSQL.append("BEGIN\n"); } sbSQL.append("dbms_logmnr.add_logfile(logfilename=>'").append(logFiles.get(i)).append("',options=>dbms_logmnr.") .append(i == 0 ? "new" : "addfile").append(");\n"); } logFiles.clear(); sbSQL.append("END;\n"); logger.warn(sbSQL.toString()); getCallableStatement(sbSQL.toString()); } public void updateSettings() { ArrayList<String> logFiles = new ArrayList<>(); // 禁用当前会话的SQL跟踪功能 setSqlTrace(); // 设置日志分析目录 // setLogAnalyseDir(); // 是否分析归档日志 isArchived(logFiles); // 分析在线日志 addLogfile(logFiles); // 归档和在线都没有符合点位的文件, 可能是停机久了再次启动时归档文件被迁移或删除了, 从最新的在线日志文件开始点位开始分析 if (logFiles.isEmpty()) offsetLogfile(logFiles); // 归档丢失, 在线不存在, 数据库可能出现故障 if (logFiles.isEmpty()) throw new RuntimeException("The Oracle log file was not read"); // 添加所有日志文件 addAllfile(logFiles); logger.warn("### [LOGMINER-CFG] 模式: {}, 起始SCN: {}, 加载文件: {}", currentModel, currentScn.get(), logFiles); try { // 开启logmnr视图 String startsql = "BEGIN\n" + "dbms_logmnr.start_logmnr(startScn=>'" + currentScn.get() + "'" //开始的scn号 // + ",dictfilename=>'" + DATA_DICTIONARY_PATH + "/" + authInfo.getOraName() + "'" //字典路径 + ",options=>" + " dbms_logmnr.dict_from_online_catalog" + //使用在线数据字典来查看相关的元数据信息 " + dbms_logmnr.skip_corruption" + //跳过损坏的日志快 " + dbms_logmnr.no_sql_delimiter" + //去掉SQL分隔符 " + dbms_logmnr.no_rowid_in_stmt" + //去掉SQL中的ROWID // " + dbms_logmnr.committed_data_only" + //去掉未提交事务的数据 " + dbms_logmnr.string_literals_in_stmt) ;\n" //输出SQL语句中的字符串常量 + "END;"; logger.warn(startsql); logger.warn("### [DICTIONARY] 使用{}数据字典", startsql.contains("dict_from_online_catalog") ? "在线" : "持久化"); getCallableStatement(startsql); logger.warn("dbms_logmnr successfully! 当前分析模式: " + currentModel); } catch (Exception e) { logger.error(e.getMessage(), e); } } private void logSwitchVerify(){ String crgs = queryCurrentRedoGroups(); if(StringUtils.isNotEmpty(crgs) && !crgs.equals(currentRedoGroups)){ logger.warn("### [REDO-SWITCH] 日志组切换! 旧组: {} → 新组: {}", currentRedoGroups, crgs); currentRedoGroups = crgs; unsubscribe(); updateSettings(); } } @Override public synchronized Message getWithoutAck(CanalInstance canalInstance) { try { if (connection.isClosed()) { reconnect(); } } catch (Exception e) { logger.error(e.getMessage(), e); } logSwitchVerify(); List<FlatMessage> data = new ArrayList<>(); Statement statement = null; ResultSet resultSet = null; String sql =""; try { statement = connection.createStatement(); long endScn = currentScn.get(); String queryLogSql = "SELECT scn,operation,sql_redo,table_space,seg_owner,table_name,timestamp,csf FROM v$logmnr_contents " + "where scn %s %d and table_name = '%s' and operation in('INSERT','DELETE','UPDATE') order by scn asc"; //logger.warn(String.format(queryLogSql, opt, currentScn.get(), whiteList.get(0))); resultSet = statement.executeQuery(String.format(queryLogSql, opt, currentScn.get(), whiteList.get(0))); opt = ">"; long lastScn = currentScn.get(); while (resultSet.next()) { endScn = resultSet.getLong("scn"); String operation = resultSet.getString(Constant.operation); if(!action.contains(operation.toUpperCase())) continue; // String segOwner = resultSet.getString(Constant.seg_owner); // String tableSpace = resultSet.getString(Constant.table_space); String tableName = resultSet.getString(Constant.table_name); sql = resultSet.getString(Constant.sql_redo); logger.warn("### [ORACLE-LOG] 解析到原始SQL - SCN: {}, 操作: {}, 表: {}, SQL: {}", endScn, operation, tableName, sql); if(StringUtils.isEmpty(sql)){ logger.error(operation + "->" + tableName+", sql为空默认跳过"); continue; } FlatMessage flatMessage = new FlatMessage(); String timestamp = resultSet.getString(Constant.timestamp); if(!StringUtils.isEmpty(timestamp)){ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); long es = sdf.parse(timestamp).getTime() / 1000; flatMessage.setEs(es); }else{ long es = new Date().getTime() / 1000; flatMessage.setEs(es); } flatMessage.setDatabase(authInfo.getDefaultDatabaseName()); flatMessage.setTable(tableName); flatMessage.setIsDdl(false); flatMessage.setType(operation); flatMessage.setTs(System.currentTimeMillis()); flatMessage.setSql(sql); OracleUtil.toMqformat(flatMessage);//转换sql为mq所需json格式 if(null == flatMessage.getData()) { logger.warn("异常SQL: " + sql); continue; } logger.warn("### [CANAL-MSG] 转换后消息 - 类型: {}, 主键: {}, 数据: {}", flatMessage.getType(), flatMessage.getPkNames(), flatMessage.getData()); //主键设置 List<String> list = pkMaps.get(authInfo.getDefaultDatabaseName() + "." + tableName); if(!CollectionUtils.isEmpty(list)){ flatMessage.setPkNames(list); }else{ flatMessage.setPkNames(new ArrayList<>()); } data.add(flatMessage); } if(endScn != currentScn.get()){ logger.warn("### [SCN-UPDATE] 更新SCN: 旧={}, 新={}", currentScn.get(), endScn); currentScn.set(endScn); FileMixedMetaManager.saveScnLocal(canalInstance.getDestination(), currentScn.get()); } //本次的归档日志已分析结束, 结束本次会话, 开始下次分析 if("archive".equals(currentModel) && CollectionUtils.isEmpty(data)){ currentScn.set(nextChange); opt = ">="; FileMixedMetaManager.saveScnLocal(canalInstance.getDestination(), currentScn.get()); logger.warn("本轮归档日志已分析结束, 结束本次会话, 开始下次分析, 开始点位: "+nextChange); unsubscribe(); updateSettings(); } if (!CollectionUtils.isEmpty(data)) { return new Message(lastScn, data, Constant.NOT_MYSQL); } } catch (Exception e) { logger.error("oracle logminer select v$logmnr_contents failed"); logger.warn(sql); logger.error("### [ERROR] SQL解析异常: {}", sql, e); StringWriter sw = new StringWriter(); PrintWriter pw = new PrintWriter(sw); // 将出错的栈信息输出到printWriter中 e.printStackTrace(pw); pw.flush(); sw.flush(); try { sw.close(); } catch (IOException ex) { throw new RuntimeException(ex); } pw.close(); unsubscribe(); updateSettings(); } finally { close(statement, resultSet); } return new Message(-1, true, null); } //TRIGGER private String getStartPosition() { String sql = "SELECT MAX(CURRENT_SCN) CURRENT_SCN FROM GV$DATABASE"; Statement statement = null; ResultSet resultSet = null; try { statement = connection.createStatement(); resultSet = statement.executeQuery(sql); while (resultSet.next()) { long scn = resultSet.getLong(1); return String.valueOf(scn); } } catch (SQLException e) { e.printStackTrace(); } finally { close(statement, resultSet); } return "0"; } public List<String> getPkNames(String datasourceName, String tableName) { List<String> pkNames = Lists.newArrayList(); Statement statement = null; ResultSet resultSet = null; logger.warn("### [PRIMARY-KEY] 查询主键: {}.{}", datasourceName, tableName); try { DatabaseMetaData metaData = connection.getMetaData(); resultSet = metaData.getPrimaryKeys(datasourceName, null, tableName); while (resultSet.next()) { String pkName = resultSet.getString("COLUMN_NAME"); // +++ 新增: 记录找到的主键 +++ logger.warn("### [PRIMARY-KEY] 找到主键列: {}", pkName); pkNames.add(pkName); } if (pkNames.isEmpty()) { logger.error("### [PRIMARY-KEY-WARN] 表 {}.{} 未找到主键!", datasourceName, tableName); } else { logger.warn("### [PRIMARY-KEY] 共找到 {} 个主键列", pkNames.size()); } return pkNames; } catch (Exception e) { logger.error(datasourceName+"."+tableName+"oracle get table primary key returns null", e); } finally { close(statement, resultSet); } return null; } private void close(Statement statement, ResultSet resultSet){ try { if (resultSet != null) resultSet.close(); } catch (SQLException e1) { e1.printStackTrace(); } finally { try { if (statement != null) statement.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Override public void connect() { String jdbcUrl = ORACLE_DRIVER_URL + authInfo.getAddress().getHostString() + ":" + authInfo.getAddress().getPort() + ":" + authInfo.getServerName(); try { this.connection = DriverManager.getConnection(jdbcUrl, authInfo.getUsername(), authInfo.getPassword()); logger.warn("成功连接到 Oracle: {}:{}", authInfo.getAddress().getHostString(), authInfo.getAddress().getPort()); // 新增日志 } catch (SQLException e) { e.printStackTrace(); } } @Override public void reconnect() { disconnect(); connect(); } @Override public void disconnect() { if (connection != null) { try { unsubscribe(); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } } @Override public void ack(CanalInstance canalInstance) { return; } @Override public boolean checkConsistent(CanalInstance canalInstance) { CanalEventParser eventParser = canalInstance.getEventParser(); MysqlEventParser parser = (MysqlEventParser) eventParser; AuthenticationInfo newAuthInfo = parser.getMasterInfo(); String newUrl = newAuthInfo.getAddress().getHostName() + newAuthInfo.getAddress().getPort(); String oldUrl = this.authInfo.getAddress().getHostName() + this.authInfo.getAddress().getPort(); if (Objects.equals(newUrl, oldUrl)) { return true; } return false; } @Override //回退到指定位置 public void rollback(CanalInstance canalInstance, Long batchId) { try { FileMixedMetaManager.saveScnLocal(canalInstance.getDestination(), batchId); currentScn.set(batchId); } catch (IOException e) { e.printStackTrace(); } logger.warn("回退到 SCN: {}, 实例: {}", batchId, canalInstance.getDestination()); // 新增日志 } public void getCallableStatement(String sql) { CallableStatement callableStatement = null; try { callableStatement = connection.prepareCall(sql); callableStatement.execute(); } catch (SQLException e) { logger.error(e.getMessage(), e); e.printStackTrace(); StringWriter sw = new StringWriter(); PrintWriter pw = new PrintWriter(sw); // 将出错的栈信息输出到printWriter中 e.printStackTrace(pw); logger.error("执行 SQL 失败: {}, 错误码: {}, 堆栈: {}", sql, e.getErrorCode(), sw.toString()); // 新增日志 pw.flush(); sw.flush(); try { sw.close(); } catch (IOException ex) { throw new RuntimeException(ex); } pw.close(); if (e.getErrorCode() == 1291) { throw new CanalServerException("1291"); } } finally { close(callableStatement, null); } } /** * description: 关闭查询会话 * * @param {} * @return void */ public void unsubscribe() { logger.warn("### [SESSION-END] 结束会话! 当前SCN: {}", currentScn.get()); String endLogmnrSql = "BEGIN\n" + " dbms_logmnr.end_logmnr;\n" + " END;"; logger.warn(endLogmnrSql); getCallableStatement(endLogmnrSql); logger.warn("end_logmnr succeeded ！"); }你觉得增量数据未同步的原因是啥 分析下代码 }