oracle近实时数据同步方案整理

准备阶段

创建测试表

create table TEST1
(
  tid         NUMBER,
  uname       VARCHAR2(64),
  age         NUMBER,
  remark      CLOB,
  photo       BLOB,
  create_time DATE,
  update_time TIMESTAMP(6)
)

操作记录

insert into test1 values(1,'测试1',12,'测试1',null,sysdate,systimestamp);
commit;

update test1 t set t.remark='测试修改1' where t.tid=1;
commit;

insert into test1 values(2,'测试2',12,'测试2',null,sysdate,systimestamp);
delete test1 where tid=1 ;
commit;

操作时记录的SCN

SELECT CURRENT_SCN FROM V$DATABASE;
--67082081200 创建完表后，插入数据前记录的SCN
--67082081298 插入第一条数据后记录的SCN
--67082081941 修改完tid=1后记录的SCN
--67082083966 删除tid=1，新增tid=2后记录的SCN

方案一：闪回版本查询(查看某一时间段内数据的变化，默认900s内)

轮询查询，有记录发送数据到处理组件的同时改变SCN继续轮询，无记录改变SCN继续轮询。
第一次查询(15分钟内)

SELECT versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation,
       rowid,
       TID,UNAME,AGE,REMARK,PHOTO,CREATE_TIME,UPDATE_TIME
  FROM test1 VERSIONS BETWEEN SCN 67082081200 AND 67082083966
  order by 1;

第二次查询(超过15分钟)

SELECT versions_startscn,
       versions_starttime,
       versions_endscn,
       versions_endtime,
       versions_xid,
       versions_operation,
       rowid,
       TID,UNAME,AGE,REMARK,PHOTO,CREATE_TIME,UPDATE_TIME
  FROM test1 VERSIONS BETWEEN TIMESTAMP sysdate-1/24/(60*15) AND sysdate
  order by 1;

方案二：闪回查询

轮询查询，有记录发送数据到处理组件的同时改变SCN继续轮询，无记录改变SCN继续轮询。

SELECT NVL(CU.ROWID,OL.ROWID) ROW_ID,CU.*,OL.*,
       CASE WHEN CU.ROWID IS NULL THEN 'DELETE'
            WHEN OL.ROWID IS NULL THEN 'INSERT'
            ELSE DECODE(DBMS_LOB.COMPARE(CU.TID||CU.UNAME||CU.AGE||CU.REMARK||CU.PHOTO||CU.CREATE_TIME||CU.UPDATE_TIME,
                                         OL.TID||OL.UNAME||OL.AGE||OL.REMARK||OL.PHOTO||OL.CREATE_TIME||OL.UPDATE_TIME),
                        '0',
                        '9', --没有变化的数据
                        '2') --被修改的数据
        END OP
FROM TEST1 AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP) CU  --基于当前SCN查询
FULL JOIN TEST1 AS OF SCN 67082081298 OL   --基于插入第一条数据TID=1后的SCN查询
ON CU.ROWID=OL.ROWID

方案三：FLASHBACK_TRANSACTION_QUERY结合闪回查询

轮询FLASHBACK_TRANSACTION_QUERY获取想要对象的操作SCN和ROW_ID,有记录则基于闪回查询根据ROW_ID获取数据并发送数据到处理组件同时继续轮询，无记录则继续轮询

SELECT * FROM FLASHBACK_TRANSACTION_QUERY ;

方案四：基于LogMiner的日志挖掘

1.查找需要的重做日志或归档日志

SELECT TO_CHAR(A0.GROUP#) GROUP#,TO_CHAR(A0.THREAD#) THREAD#,TO_CHAR(A0.SEQUENCE#) SEQUENCE#,A0.ARCHIVED,A0.STATUS,A0.FIRST_CHANGE#,A0.NEXT_CHANGE#,
TO_CHAR(A0.FIRST_TIME,'YYYY.MM.DD HH24:MI:SS') FIRST_TIME,TO_CHAR(A0.NEXT_TIME,'YYYY.MM.DD HH24:MI:SS') NEXT_TIME,
A1.TYPE,MIN(A1.MEMBER) FILE_NAME
FROM V$LOG A0
INNER JOIN V$LOGFILE A1 ON A0.GROUP#=A1.GROUP# 
GROUP BY A0.GROUP#,A0.THREAD#,A0.THREAD#,A0.SEQUENCE#,A0.ARCHIVED,A0.STATUS,A0.FIRST_CHANGE#,A0.NEXT_CHANGE#,A0.FIRST_TIME,A0.NEXT_TIME,A1.TYPE,A0.MEMBERS
ORDER BY A0.FIRST_CHANGE#;

SELECT A.NAME FILE_NAME,TO_CHAR(A.THREAD#) THREAD#,TO_CHAR(A.SEQUENCE#) SEQUENCE#, 
A.FIRST_CHANGE#,A.NEXT_CHANGE#,
TO_CHAR(A.FIRST_TIME,'YYYY.MM.DD HH24:MI:SS') FIRST_TIME,TO_CHAR(A.NEXT_TIME,'YYYY.MM.DD HH24:MI:SS') NEXT_TIME
FROM V$ARCHIVED_LOG A 
WHERE A.NAME IS NOT NULL AND A.STATUS='A' AND A.ARCHIVED='YES'
AND A.DEST_ID = (SELECT DEST_ID
                       FROM V$ARCHIVE_DEST_STATUS
                      WHERE STATUS = 'VALID'
                        AND TYPE = 'LOCAL'
                        AND ROWNUM = 1)
ORDER BY A.FIRST_CHANGE#;

2.指定 Logminer 重做日志文件

call SYS.DBMS_LOGMNR.add_logfile('/opt/oracle/oradata/ORCLCDB/redo01.log', 1);
call SYS.DBMS_LOGMNR.add_logfile('/opt/oracle/oradata/ORCLCDB/redo02.log', 3);
call SYS.DBMS_LOGMNR.add_logfile('/opt/oracle/oradata/ORCLCDB/redo03.log', 3);

3.启动 LogMiner

call sys.dbms_logmnr.start_logmnr(startScn => 67012319068, endScn => 67012319785, OPTIONS => 4+16+64);

4.在同一会话查询V$ LOGMNR_CONTENTS找感兴趣数据
5.重复上述操作

总结

比较	闪回版本查询	闪回查询	FLASHBACK_TRANSACTION_QUERY结合闪回查询	基于LogMiner的日志挖掘
缺点	1.时间范围和undo_retention参数值有关，默认900s，超过这个值查询会报ORA-30052。 2.基于单表的查询，并发查询会占用数据库连接。	1.单表数据量大查询很慢。 2. 基于单表的查询，并发查询会占用数据库连接。	1.不适用于容器数据库的只读备库。 2.对于操作频繁的数据库查询很慢。 3.必须开最小追加日志。	1.不适用于只读备库。 2.必须开归档和最小追加日志。 3.大字段处理、日期类函数处理、sql解析
开发难度	较容易	较容易	较容易	难

参考链接

1.ChunJun Oracle LogMiner 实时采集基本原理
2.Oracle数据库闪回功能的详解和相关命令
3.flashback_transaction_query 查询慢的问题