END;
2025-06-05 13:51:09.307 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析归档日志...
2025-06-05 13:51:09.308 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# <= 7914757418 AND NEXT_CHANGE# > 7914757418
2025-06-05 13:51:09.333 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.add_logfile(logfilename=>'/DATA/app/product/12.2.0/dbhome_1/dbs/arch/1_74286_1100144861.dbf',options=>dbms_logmnr.new);
END;
2025-06-05 13:51:09.352 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.start_logmnr(startScn=>'7914757418',options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.skip_corruption + dbms_logmnr.no_sql_delimiter + dbms_logmnr.no_rowid_in_stmt + dbms_logmnr.string_literals_in_stmt) ;
END;
2025-06-05 13:51:09.362 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - dbms_logmnr successfully! 当前分析模式: archive
2025-06-05 13:51:09.732 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 本轮归档日志已分析结束, 结束本次会话, 开始下次分析, 开始点位: 7914757422
2025-06-05 13:51:09.732 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.end_logmnr;
END;
2025-06-05 13:51:09.733 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - end_logmnr succeeded ！
2025-06-05 13:51:09.733 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
DBMS_SESSION.SET_SQL_TRACE(FALSE);
END;
2025-06-05 13:51:09.734 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析归档日志...
2025-06-05 13:51:09.734 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# <= 7914757422 AND NEXT_CHANGE# > 7914757422
2025-06-05 13:51:09.750 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析在线日志...
2025-06-05 13:51:09.768 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 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# <= 7914757422 AND g.NEXT_CHANGE# > 7914757422
2025-06-05 13:51:09.768 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.add_logfile(logfilename=>'/DATA/app/oradata/orcl/redo02.log',options=>dbms_logmnr.new);
END;
2025-06-05 13:51:09.772 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.start_logmnr(startScn=>'7914757422',options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.skip_corruption + dbms_logmnr.no_sql_delimiter + dbms_logmnr.no_rowid_in_stmt + dbms_logmnr.string_literals_in_stmt) ;
END;
2025-06-05 13:51:09.786 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - dbms_logmnr successfully! 当前分析模式: online
2025-06-05 13:54:02.535 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.end_logmnr;
END;
2025-06-05 13:54:02.537 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - end_logmnr succeeded ！
2025-06-05 13:54:02.537 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
DBMS_SESSION.SET_SQL_TRACE(FALSE);
END;
2025-06-05 13:54:02.538 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析归档日志...
2025-06-05 13:54:02.538 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# <= 7914822313 AND NEXT_CHANGE# > 7914822313
2025-06-05 13:54:02.555 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.add_logfile(logfilename=>'/DATA/app/product/12.2.0/dbhome_1/dbs/arch/1_74287_1100144861.dbf',options=>dbms_logmnr.new);
END;
2025-06-05 13:54:02.560 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.start_logmnr(startScn=>'7914822313',options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.skip_corruption + dbms_logmnr.no_sql_delimiter + dbms_logmnr.no_rowid_in_stmt + dbms_logmnr.string_literals_in_stmt) ;
END;
2025-06-05 13:54:02.569 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - dbms_logmnr successfully! 当前分析模式: archive
2025-06-05 13:54:02.980 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 本轮归档日志已分析结束, 结束本次会话, 开始下次分析, 开始点位: 7914822317
2025-06-05 13:54:02.980 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.end_logmnr;
END;
2025-06-05 13:54:02.982 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - end_logmnr succeeded ！
2025-06-05 13:54:02.982 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
DBMS_SESSION.SET_SQL_TRACE(FALSE);
END;
2025-06-05 13:54:02.983 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析归档日志...
2025-06-05 13:54:02.983 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# <= 7914822317 AND NEXT_CHANGE# > 7914822317
2025-06-05 13:54:03.010 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析在线日志...
2025-06-05 13:54:03.040 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 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# <= 7914822317 AND g.NEXT_CHANGE# > 7914822317
2025-06-05 13:54:03.041 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.add_logfile(logfilename=>'/DATA/app/oradata/orcl/redo03.log',options=>dbms_logmnr.new);
END;
2025-06-05 13:54:03.045 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.start_logmnr(startScn=>'7914822317',options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.skip_corruption + dbms_logmnr.no_sql_delimiter + dbms_logmnr.no_rowid_in_stmt + dbms_logmnr.string_literals_in_stmt) ;
END;
2025-06-05 13:54:03.054 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - dbms_logmnr successfully! 当前分析模式: online
2025-06-05 14:00:13.564 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.end_logmnr;
END;
2025-06-05 14:00:13.570 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - end_logmnr succeeded ！
2025-06-05 14:00:13.570 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
DBMS_SESSION.SET_SQL_TRACE(FALSE);
END;
2025-06-05 14:00:13.571 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析归档日志...
2025-06-05 14:00:13.571 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# <= 7914891702 AND NEXT_CHANGE# > 7914891702
2025-06-05 14:00:13.587 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.add_logfile(logfilename=>'/DATA/app/product/12.2.0/dbhome_1/dbs/arch/1_74288_1100144861.dbf',options=>dbms_logmnr.new);
END;
2025-06-05 14:00:13.600 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.start_logmnr(startScn=>'7914891702',options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.skip_corruption + dbms_logmnr.no_sql_delimiter + dbms_logmnr.no_rowid_in_stmt + dbms_logmnr.string_literals_in_stmt) ;
END;
2025-06-05 14:00:13.608 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - dbms_logmnr successfully! 当前分析模式: archive
2025-06-05 14:00:13.971 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 本轮归档日志已分析结束, 结束本次会话, 开始下次分析, 开始点位: 7914891706
2025-06-05 14:00:13.971 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.end_logmnr;
END;
2025-06-05 14:00:13.973 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - end_logmnr succeeded ！
2025-06-05 14:00:13.973 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
DBMS_SESSION.SET_SQL_TRACE(FALSE);
END;
2025-06-05 14:00:13.974 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析归档日志...
2025-06-05 14:00:13.974 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - SELECT NAME, FIRST_CHANGE#, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# <= 7914891706 AND NEXT_CHANGE# > 7914891706
2025-06-05 14:00:14.005 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 分析在线日志...
2025-06-05 14:00:14.014 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - 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# <= 7914891706 AND g.NEXT_CHANGE# > 7914891706
2025-06-05 14:00:14.014 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.add_logfile(logfilename=>'/DATA/app/oradata/orcl/redo01.log',options=>dbms_logmnr.new);
END;
2025-06-05 14:00:14.018 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - BEGIN
dbms_logmnr.start_logmnr(startScn=>'7914891706',options=> dbms_logmnr.dict_from_online_catalog + dbms_logmnr.skip_corruption + dbms_logmnr.no_sql_delimiter + dbms_logmnr.no_rowid_in_stmt + dbms_logmnr.string_literals_in_stmt) ;
END;
2025-06-05 14:00:14.043 [pool-34-thread-1] WARN c.a.o.c.server.embedded.handle.oracle.OracleHandlerV3 - dbms_logmnr successfully! 当前分析模式: online
这个日志执行结果 ，下面是代码， 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.*;
import java.util.concurrent.atomic.AtomicLong;
/**
* @author : yangpeng
* create at: 2022/5/21 13:09
* @description: oracle连接实现
*/
@Deprecated
public class OracleHandlerV3 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 String queryLogSql;
private Map<String, List<String>> pkMaps;
private List<String> whiteList;
private String currentRedoGroups;
private boolean isSwitch = true;
private int pageSize = 500;
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(","));
whiteList = new ArrayList<>();
for (String table : filterRegex.split(",")) {
whiteList.add(table.toUpperCase());
}
}
String index = FileMixedMetaManager.getScnLocal(canalInstance.getDestination());
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{
this.currentScn.set(Long.valueOf(index));
}
//queryCurrentArchivedLogInfo();
currentRedoGroups = queryCurrentRedoGroups();
authInfo.setArchived(true);
updateSettings();
//获取主键
pkMaps = new HashMap<>();
for (String table : whiteList) {
List<String> pkNames = getPkNames(authInfo.getDefaultDatabaseName(), table);
//pkMaps.put(authInfo.getDefaultDatabaseName()+"."+table, pkNames);
String key = (authInfo.getDefaultDatabaseName() + "." + table).toUpperCase();
pkMaps.put(key, pkNames != null ? pkNames : Collections.emptyList());
}
}
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'");
StringBuilder sb = new StringBuilder();
while (resultSet.next()) {
sb.append(resultSet.getString(1));
}
if(sb.length() > 0){
return sb.toString();
}
} catch (SQLException e) {
logger.warn("Query Current Redo Group failed!");
} finally {
close(statement, resultSet);
}
return "";
}
// private void queryCurrentArchivedLogInfo(){
// //获取当前最新的归档日志信息
// Statement statement = null;
// ResultSet resultSet = null;
// try {
// statement = connection.createStatement();
// resultSet = statement.executeQuery("SELECT NAME, RECID FROM " +
// "(SELECT NAME, RECID FROM v$archived_log WHERE DEST_ID = 1 ORDER BY RECID DESC) WHERE ROWNUM = 1");
// if (resultSet.next()) {
// //lastArchivedLogName = resultSet.getString(1);
// //lastArchivedLogRecid = resultSet.getLong(2);
// //logger.warn("1 - > lastArchivedLogName: " + lastArchivedLogName);
// }
// } catch (SQLException e) {
// logger.warn("Query Current Archived Log failed!");
// } finally {
// close(statement, resultSet);
// }
// }
private void isArchived(Statement statement, ResultSet resultSet, ArrayList<String> logFiles){
if(authInfo.isArchived()){
try {
logger.warn("分析归档日志...");
statement = connection.createStatement();
String sql = "SELECT NAME, NEXT_CHANGE# FROM v$archived_log WHERE DEST_ID = 1 AND FIRST_CHANGE# >= "+currentScn+" ORDER BY RECID ASC";
resultSet = statement.executeQuery(sql);
String NAME;
while(resultSet.next()){
NAME = resultSet.getString(1);
//添加归档日志
logFiles.add(NAME);
}
} catch (SQLException e) {
logger.warn("Archive log parsing failed!");
e.printStackTrace();
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();
} finally {
close(statement, resultSet);
}
}
}
private void addLogfile(Statement statement, ResultSet resultSet, ArrayList<String> logFiles){
if(CollectionUtils.isEmpty(logFiles)){
try {
logger.warn("分析在线日志...");
statement = connection.createStatement();
resultSet = statement.executeQuery("select member from v$logfile");
while (resultSet.next()) {
String fileName = resultSet.getString(1);
if(!fileName.contains("sredo")){
logFiles.add(fileName);
}
}
} catch (SQLException e) {
logger.warn("Description Failed to query online logs!");
} finally {
close(statement, resultSet);
}
}
}
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){
StringBuilder sbSQL = new StringBuilder();
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());
}
private void appendSql(){
//logmner视图查询
StringBuilder queryStr = new StringBuilder();
queryStr.append("SELECT scn,operation,sql_redo,table_space,table_name,timestamp,csf FROM v$logmnr_contents where 1=1");
queryStr.append(" AND seg_type_name='TABLE'");
queryStr.append(" AND table_space!='SYSTEM'");
String filter = authInfo.getFilter();
if (filter.contains(".*") && !".*..*".equals(filter)) {
queryStr.append(" AND seg_owner='").append(authInfo.getDefaultDatabaseName()).append("'");
}
if (!filter.contains(".*") && !".*..*".equals(filter)) {
queryStr.append(" AND seg_owner='").append(authInfo.getDefaultDatabaseName()).append("'");
queryStr.append(" AND table_name in(").append(authInfo.getTable()).append(")");
}
queryStr.append(" AND scn > " + "?");
queryStr.append(" AND rownum < ").append(pageSize);
queryStr.append(" order by scn asc ");
queryStr.append(" AND operation IN ('INSERT','UPDATE','DELETE')");
this.queryLogSql = queryStr.toString();
}
public void updateSettings() {
ArrayList<String> logFiles = new ArrayList<>();
Statement statement = null;
ResultSet resultSet = null;
try {
// 是否分析归档日志
isArchived(statement, resultSet, logFiles);
// 分析在线日志
addLogfile(statement, resultSet, logFiles);
if (logFiles.isEmpty()) throw new RuntimeException("The Oracle log file was not read");
// 设置日志分析目录
setLogAnalyseDir();
// 添加所有日志文件
addAllfile(logFiles);
try {
// 开启logmnr视图
String startsql = "BEGIN\n"
+ "dbms_logmnr.start_logmnr(startScn=>'" + currentScn.get() //开始的scn号
+ "',dictfilename=>'" + DATA_DICTIONARY_PATH + "/" + authInfo.getOraName() //路径
+ "',options=>" +
" dbms_logmnr.primary_key" +
" +dbms_logmnr.skip_corruption" +
" + dbms_logmnr.no_sql_delimiter" +
" + dbms_logmnr.no_rowid_in_stmt" +
" + dbms_logmnr.committed_data_only" +
" + dbms_logmnr.string_literals_in_stmt) ;\n"
+ "END;";
logger.warn(startsql);
getCallableStatement(startsql);
} catch (Exception e) {
logger.error("oracle logminer Unsubscribe failed:{}", e);
// 开启logmnr视图
String startsql = "BEGIN\n"
+ "dbms_logmnr.start_logmnr(startScn=>'" + 0 //开始的scn号
+ "',dictfilename=>'" + DATA_DICTIONARY_PATH + "/" + authInfo.getOraName() //路径
+ "',options=>" +
" dbms_logmnr.primary_key" +
" +dbms_logmnr.skip_corruption" +
" + dbms_logmnr.no_sql_delimiter" +
" + dbms_logmnr.no_rowid_in_stmt" +
" + dbms_logmnr.committed_data_only" +
" + dbms_logmnr.string_literals_in_stmt) ;\n"
+ "END;";
logger.warn(startsql);
getCallableStatement(startsql);
}
//logmner视图查询
appendSql();
logger.info("Subscribe to Oracle {} successfully!", authInfo.getAddress() + ":" + authInfo.getDefaultDatabaseName());
} catch (Exception e) {
throw new CanalServerException("Failed to subscribe to Oracle logs. Procedure:::", e);
} finally {
close(statement, resultSet);
}
}
private void logSwitchVerify(){
String crgs = queryCurrentRedoGroups();
if(StringUtils.isNotEmpty(crgs) && !crgs.equals(currentRedoGroups)){
currentRedoGroups = crgs;
unsubscribe();
//oracle切换日志组时可能存在日志没来得及分析的遗漏问题, 此时应分析最近的归档日志, 且只分析一次, 处理掉可能遗漏的数据后再开启分析在线日志
authInfo.setArchived(true);
isSwitch = true;
updateSettings();
}
}
@Override
public synchronized Message getWithoutAck(CanalInstance canalInstance) {
try {
if (connection.isClosed()) {
reconnect();
}
} catch (Exception e) {
logger.error(e.getMessage(), e);
}
logSwitchVerify();
String findSql = this.queryLogSql.replace("?", currentScn.toString());
logger.warn(findSql);
List<FlatMessage> data = new ArrayList<>();
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery(findSql);
//获取历史标识
long lastScn = currentScn.get();
Long endScn = null;
while (resultSet.next()) {
long scn = resultSet.getLong(Constant.scn);
if (scn <= currentScn.get()) continue;
String operation = resultSet.getString(Constant.operation);
String sql = resultSet.getString(Constant.sql_redo);
if(StringUtils.isEmpty(sql)) continue;
// String tableSpace = resultSet.getString(Constant.table_space);
String tableName = resultSet.getString(Constant.table_name);
String timestamp = resultSet.getString(Constant.timestamp);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
long es = sdf.parse(timestamp).getTime() / 1000;
FlatMessage flatMessage = new FlatMessage();
flatMessage.setDatabase(authInfo.getDefaultDatabaseName());
flatMessage.setTable(tableName);
flatMessage.setIsDdl(false);
flatMessage.setType(operation);
flatMessage.setEs(es);
flatMessage.setTs(System.currentTimeMillis());
flatMessage.setSql(sql);
OracleUtil.toMqformat(flatMessage);//转换sql为mq所需json格式
//主键设置
//List<String> list = pkMaps.get(authInfo.getDefaultDatabaseName() + "." + tableName);
String key = (authInfo.getDefaultDatabaseName() + "." + tableName).toUpperCase();
List<String> list = pkMaps.get(key);
if (list == null || list.isEmpty()) {
logger.warn("表 {} 无主键信息! 操作: {}, SQL: {}",
tableName, operation, sql);
} else {
logger.info("主键信息: table={}, pk={}", tableName, list);
}
if(!CollectionUtils.isEmpty(list)){
flatMessage.setPkNames(list);
}else{
flatMessage.setPkNames(new ArrayList<>());
}
data.add(flatMessage);
endScn = scn;
}
if (!CollectionUtils.isEmpty(data)) {
if(null != endScn) currentScn.set(endScn);
FileMixedMetaManager.saveScnLocal(canalInstance.getDestination(), currentScn.get());
return new Message(lastScn, data, Constant.NOT_MYSQL);
}
} catch (Exception e) {
logger.error("oracle logminer Unsubscribe failed:{}", e);
unsubscribe();
updateSettings();
} finally {
close(statement, resultSet);
if(isSwitch){
if(!CollectionUtils.isEmpty(data) && data.size() >= (pageSize-1)){
//data不等于空, 且条目数大于等于每次获取的日志数量, 可能说明从归档日志中一次不足以拿完全部日志, 多循环几次
}else{
isSwitch = false;
authInfo.setArchived(false);
unsubscribe();
updateSettings();
}
}
}
return new Message(-1, true, null);
}
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;
try {
DatabaseMetaData metaData = connection.getMetaData();
String tableNameUpper = tableName.toUpperCase();
resultSet = metaData.getPrimaryKeys(datasourceName, null, tableNameUpper);
while (resultSet.next()) {
pkNames.add(resultSet.getString("COLUMN_NAME"));
}
return pkNames;
} catch (Exception e) {
logger.error(datasourceName+"."+tableName+"oracle get table primary key returns null", e);
return Collections.emptyList();
} finally {
close(statement, resultSet);
}
}
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());
} 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();
}
}
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);
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);
}
}
public void getStatement(String sql) {
try (Statement statement = connection.createStatement()) {
statement.execute(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* description: 关闭查询会话
*
* @param {}
* @return void
*/
public void unsubscribe() {
getStatement("BEGIN\n"
+ "dbms_logmnr.end_logmnr;\n"
+ "END;");
logger.warn("Unsubscribing succeeded ！");
}
}
怎么在代码加日志打印来排查 ，解析到的oracle归档日志中的sql是否正确，尤其是update和insert，来排查增量数据没有增加的原因
最新发布
本文介绍了如何使用SQL进行时间范围搜索，特别是在处理毫秒级时间戳时的方法。通过将时间戳转换为日期并进行比较，可以有效地在数据库中找到特定时间段内的记录。同时，也展示了如何找出日期最小的时间戳对应的日期。
扫一扫
3074
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
