最近有个客户在做迁移oracle到Oceanbase时，使用的是原厂的OMS数据迁移同步工具，在迁移或数据同步完数据库发现数据存在差异, 应该是一种基于logminer的log stream形式，发现一张表含有Lob字段在logminer的视图中对应的rowid只有update，没有insert， 经过事务xid的查找发现insert所对应的是ROW_ID为”AAAAAAAAAAAAAAAAAA”,显然这是一个无效的rowid, 如果是基于rowid那同步数据就可能丢失了。我发现oracle11g和23c这方面还有点差异，简单记录。
环境11.2.0.4
create table anbob.TEST_LOG
(
id number
, fullname varchar2(50)
, age number
, description clob
);
CREATE TABLE anbob.TEST (
ID INTEGER,
COL1 VARCHAR2(4000),
COL2 INTEGER
);
select dbms_flashback.get_system_change_number from dual;
insert into anbob.TEST_LOG(id,fullname,age) values(1,'anbob builder',35);
update anbob.TEST_LOG set description=to_clob('anbob.com') where id=1;
INSERT INTO anbob.TEST_LOG(id,fullname,age,description) values(2,'anbob builder',35, DBMS_RANDOM.STRING('U', 10));
commit;
INSERT INTO anbob.TEST (ID, COL1, COL2) VALUES (0, 'TEXT', 1);
commit;
select dbms_flashback.get_system_change_number from dual;
exec DBMS_LOGMNR.ADD_LOGFILE('+DATA/rac11g/onlinelog/group_2.261.1164470467', DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN=>24334811738, ENDSCN=>24334811859, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.NO_ROWID_IN_STMT + DBMS_LOGMNR.NO_SQL_DELIMITER);
Note:
创建了一个包含lob的表test_lob和一个不含LOB的test表做为对比。
查看操作日志
SQL> r
1 SELECT
2 scn,start_scn,timestamp,xid,operation,table_name, row_id,sql_redo
3 FROM V$LOGMNR_CONTENTS
4 where SEG_OWNER='ANBOB' OR XID in('BD0B0400E0210000','C10B180009220000')
5* ORDER BY 1 asc
SCN START_SCN TIMESTAMP XID OPERATION TABLE_NAME ROW_ID SQL_REDO
--------------- --------------- ------------------- ---------------- --------------- --------------- ------------------------------------ ----------------------------------------------------------------------
24334811772 24334811772 2024-11-22 16:52:21 C10B180009220000 START AAAAAAAAAAAAAAAAAA set transaction read write
24334811772 24334811772 2024-11-22 16:52:21 C10B180009220000 INSERT TEST_LOG AAAAAAAAAAAAAAAAAA insert into "ANBOB"."TEST_LOG"("ID","FULLNAME","AGE","DESCRIPTION") v
alues ('1','anbob builder','35',EMPTY_CLOB())
24334811774 24334811772 2024-11-22 16:52:21 C10B180009220000 UPDATE TEST_LOG AAAd5rAAEAAEXYdAAA update "ANBOB"."TEST_LOG" set "DESCRIPTION" = NULL where "ID" = '1' a
nd "FULLNAME" = 'anbob builder' and "AGE" = '35'
24334811781 24334811772 2024-11-22 16:52:27 C10B180009220000 UPDATE TEST_LOG AAAAAAAAAAAAAAAAAA update "ANBOB"."TEST_LOG" set "DESCRIPTION" = EMPTY_CLOB() where "ID"
= '1' and "FULLNAME" = 'anbob builder' and "AGE" = '35'
24334811782 24334811772 2024-11-22 16:52:27 C10B180009220000 UPDATE TEST_LOG AAAd5rAAEAAEXYdAAA update "ANBOB"."TEST_LOG" set "DESCRIPTION" = 'anbob.com' where "ID"
= '1' and "FULLNAME" = 'anbob builder' and "AGE" = '35'
24334811814 24334811772 2024-11-22 16:52:53 C10B180009220000 INSERT TEST_LOG AAAAAAAAAAAAAAAAAA insert into "ANBOB"."TEST_LOG"("ID","FULLNAME","AGE","DESCRIPTION") v
alues ('2','anbob builder','35',EMPTY_CLOB())
24334811814 24334811772 2024-11-22 16:52:53 C10B180009220000 UPDATE TEST_LOG AAAd5rAAEAAEXYdAAB update "ANBOB"."TEST_LOG" set "DESCRIPTION" = 'BESVGEYSJO' where "ID"
= '2' and "FULLNAME" = 'anbob builder' and "AGE" = '35'
24334811820 24334811772 2024-11-22 16:52:59 C10B180009220000 COMMIT AAAAAAAAAAAAAAAAAA commit
24334811847 24334811847 2024-11-22 16:53:08 BD0B0400E0210000 START AAAd5uAAAAAAAAAAAA set transaction read write
24334811847 24334811847 2024-11-22 16:53:08 BD0B0400E0210000 INSERT TEST AAAd5uAAEAAEXY9AAA insert into "ANBOB"."TEST"("ID","COL1","COL2") values ('0','TEXT','1')
24334811851 24334811847 2024-11-22 16:53:11 BD0B0400E0210000 COMMIT AAAAAAAAAAAAAAAAAA commit
11 rows selected.
Note:
1，对于事务操作start,commit,rollback 对应的rowid 都是AAAAAAAAAAAAAAAAAA
2, 对于lob列的insert 不带lob col分自动分为2条，先insert lobcol= EMPTY_CLOB(), 再update lobcol=null, 第1个insert row_id=AAAAAAAAAAAAAAAAAA
3, 对于lob列的update同样分为2条， 先update lobcol= EMPTY_CLOB(), 再update lobcol=具体值, 第1个update row_idAAAAAAAAAAAAAAAAAA
4，对于clob列的insert 带lob col分自动分为2条，先insert lobcol= EMPTY_CLOB(), 再update lobcol=null,第1个insert row_id=AAAAAAAAAAAAAAAAAA
5, 并不是所有insert 对应的rowid都是AAAAAAAAAAAAAAAAAA, 对于不含lob的表 insert row_id为实际rowid.
Oralce 23c 测试
create table anbob.testtable
(
id number
, fullname varchar2(50)
, age number
, description clob
);
Table created.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
820499821
SQL> insert into anbob.testtable(id,fullname,age) values(1,'anbob builder',35);
1 row created.
SQL> update anbob.testtable set description=to_clob('anbob.com') where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
820500013
SQL>EXECUTE DBMS_LOGMNR.START_LOGMNR(STARTSCN=>820499821, ENDSCN=>820500013, -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + DBMS_LOGMNR.NO_ROWID_IN_STMT + DBMS_LOGMNR.NO_SQL_DELIMITER);
PL/SQL procedure successfully completed.
NOTE:
1, 23c不支持ORA-44609: CONTINOUS_MINE is desupported for use with DBMS_LOGMNR.START_LOGMNR.
2, 23c这里并没有做DBMS_LOGMNR.ADD_LOGFILE,看来已经可以自动查找
查看23c的logminer日志
SELECT
scn,start_scn,timestamp,xid,operation,table_name, row_id,sql_redo
FROM V$LOGMNR_CONTENTS
where SEG_OWNER='ANBOB' OR XID='0A0020004CC60200'
ORDER BY xid asc,sequence# asc
/
SCN START_SCN TIMESTAMP XID OPERATION TABLE_NAME ROW_ID SQL_REDO
---------- ---------- ------------------- ---------------- -------------------------------- ------------------------------ ------------------ ----------------------------------------
820499858 820499858 2024-11-23 00:18:37 0A0020004CC60200 START AAAsPAAAAAAAAAAAAA set transaction read write
820499858 820499858 2024-11-23 00:18:37 0A0020004CC60200 INSERT TESTTABLE AAAsPAAAdAAAKc8AAA insert into "ANBOB"."TESTTABLE"("ID","FU
LLNAME","AGE","DESCRIPTION") values ('1'
,'anbob builder','35',NULL)
820499988 820499858 2024-11-23 00:19:04 0A0020004CC60200 UPDATE TESTTABLE AAAsPAAAdAAAKc8AAA update "ANBOB"."TESTTABLE" set "DESCRIPT
ION" = 'anbob.com' where
820500008 820499858 2024-11-23 00:19:12 0A0020004CC60200 COMMIT AAAAAAAAAAAAAAAAAA commit
Note：
在23c中默认对于含有lob的列表DML并没有拆分为2条，没有先EMPTY_CLOB()。
注意： 对于基于日志的数据同步，还是要研究数据库不同版本的日志内容变化，这里11g和23c的不同还隐藏一个11g使用的是basefile，而23c是SECUREFILE, 和存储属性是否有没有关系？12c和19c又是什么样？时间关系请自行测试。
还有更多异构数据迁移问题隐藏在项目中，如果您有国产化改造咨询需求，可以考虑联系我们（www.anbob.com首页的联系方式）