oracle flashback data archive闪回数据归档XID重用导致闪回查询数据重复

[zhjh256]

我们有个系统使用了Oracle flashback data archive闪回数据归档特性来作为基于时间点的恢复机制，在频繁插入、更新期间发现SYS_FBA_HIST_NNNN表中的XID被两个事务重用了，导致start_scn相同，于是在执行as of scn/timestamp查询的时候，相同rowid的记录会出来两条，无论是oracle 11.2.0.4还是oracle 18c都能重现，前者几乎100%，后者概率低很多（一开始LZ本地用18c跑了连续四五次都没有出现，以为解决了，后来测试又验证出来了）。因为公司metalink账号都被停了，所以只能求助了，麻烦各位有mos账号的同学或者有遇到的同学帮忙查下是否是bug，或者对应的补丁。完整重现现象示例如下：
create flashback archive default fda_test_1day tablespace "USERS" retention 1 day;
create table xyz_test (
c_tenantid varchar2(20) DEFAULT ' ' NOT NULL,
c_tacode varchar2(2) DEFAULT ' ' ,
c_fundacco varchar2(12) DEFAULT ' ' ,
c_agencyno varchar2(9) DEFAULT ' ' ,
c_netno varchar2(9) DEFAULT ' ' ,
c_tradeacco varchar2(24) DEFAULT ' ' ,
c_fundcode varchar2(12) DEFAULT ' ' ,
c_sharetype char(1) DEFAULT ' ' ,
d_cdate number(8,0) DEFAULT 0 ,
c_cserialno varchar2(20) DEFAULT ' ' NOT NULL,
c_sourcetype char(1) DEFAULT ' ' ,
f_remainshares number(16,2) DEFAULT 0.0 ,
d_registdate number(8,0) DEFAULT 0 ,
f_oricfmbalance number(16,2) DEFAULT 0.0 ,
f_oricfmshares number(16,2) DEFAULT 0.0 ,
f_orinetvalue number(7,4) DEFAULT 0.0 ,
c_orisource char(1) DEFAULT ' ' ,
f_ruleagio number(5,4) DEFAULT 1.0 ,
f_oriagio number(5,4) DEFAULT 1.0 ,
f_maxallotratio number(5,4) DEFAULT 0.0 ,
f_minredeemratio number(5,4) DEFAULT 0.0 ,
c_holdflag char(1) DEFAULT ' ' ,
c_acceptmode char(1) DEFAULT ' ' ,
c_firstcserailno varchar2(20) DEFAULT ' ' ,
c_fundmethod varchar2(2) DEFAULT ' ' ,
c_bankno varchar2(60) DEFAULT ' ' ,
c_subfundmethod varchar2(2) DEFAULT ' ' ,
f_managefare number(16,2) DEFAULT 0.0 ,
f_interestshare number(16,2) DEFAULT 0.0 ,
d_lastdeductdate number(8,0) DEFAULT 0 ,
f_income number(16,2) DEFAULT 0.0 ,
f_newincome number(16,2) DEFAULT 0.0 ,
c_specialcode varchar2(20) DEFAULT ' ' ,
f_pendsubmit02shr number(16,2) DEFAULT 0.0 ,
f_pendsubmit03shr number(16,2) DEFAULT 0.0 ,
f_pendsubmit50shr number(16,2) DEFAULT 0.0 ,
f_assignshare number(16,2) DEFAULT 0.0 ,
f_pendsubmit13shr number(16,2) DEFAULT 0.0 ,
f_pendsubmit16shr number(16,2) DEFAULT 0.0 ,
f_lastremainshares number(16,2) DEFAULT 0.0 ,
f_pendsubmit14shr number(16,2) DEFAULT 0.0 ,
f_pendsubmit15shr number(16,2) DEFAULT 0.0 ,
f_lastincome number(16,2) DEFAULT 0.0 ,
d_orilastdeductdate number(8,0) DEFAULT 0 ,
f_costprice number(15,12) DEFAULT 0.0 ,
f_lastmanagefare number(16,2) DEFAULT 0.0 ,
f_apportionratio number(9,8) DEFAULT 0.0 ,
f_fareratio number(5,4) DEFAULT 0.0 ,
f_backfareratio number(9,8) DEFAULT 0.0 ,
f_minbackratio number(5,4) DEFAULT 0.0 ,
f_orifareratio number(5,4) DEFAULT 0.0 ,
d_cyclestartdate number(8,0) DEFAULT 0 ,
d_cycleenddate number(8,0) DEFAULT 0 ,
d_cyclenextdate number(8,0) DEFAULT 0 ,
l_cycle number(10,0) DEFAULT 0 ,
f_evennetvalue number(7,4) DEFAULT 0.0 ,
c_protectflag char(1) DEFAULT ' ' ,
f_backfare number(16,2) DEFAULT 0.0 ,
f_costfare number(16,2) DEFAULT 0.0 ,
c_lastoutfundcode varchar2(20) DEFAULT ' ' ,
f_redeemedbaseshr number(16,2) DEFAULT 0.0 ,
l_incserno number(20,0) DEFAULT 0 ,
f_lastdeductnetvalue number(7,4) DEFAULT 0.0 ,
f_lastdeductasset number(16,2) DEFAULT 0.0 ,
f_newnetvalue number(7,4) DEFAULT 0.0 ,
f_newasset number(16,2) DEFAULT 0.0 ,
c_actcode varchar2(20) DEFAULT ' '
) enable row movement,rowdependencies,flashback archive;
alter table xyz_test add primary key(c_cserialno, c_tenantid);
-- 创建测试存储过程
create or replace procedure insert_sharedetail is
i number;
fundcode VARCHAR2(10);
begin
i:=0;
loop
i:=i+1;
if i>2000 then
exit;
end if;
fundcode := replace(lpad(floor(i/100000),6),' ','0');
INSERT INTO xyz_test(C_TENANTID, C_TACODE, C_FUNDACCO, C_AGENCYNO, C_NETNO, C_TRADEACCO, C_FUNDCODE, C_SHARETYPE, D_CDATE, C_CSERIALNO, C_SOURCETYPE, F_REMAINSHARES, D_REGISTDATE, F_ORICFMBALANCE, F_ORICFMSHARES, F_ORINETVALUE, C_ORISOURCE, F_RULEAGIO, F_ORIAGIO, F_MAXALLOTRATIO, F_MINREDEEMRATIO, C_HOLDFLAG, C_ACCEPTMODE, C_FIRSTCSERAILNO, C_FUNDMETHOD, C_BANKNO, C_SUBFUNDMETHOD, F_MANAGEFARE, F_INTERESTSHARE, D_LASTDEDUCTDATE, F_INCOME, F_NEWINCOME, C_SPECIALCODE, F_PENDSUBMIT02SHR, F_PENDSUBMIT03SHR, F_PENDSUBMIT50SHR, F_ASSIGNSHARE, F_PENDSUBMIT13SHR, F_PENDSUBMIT16SHR, F_LASTREMAINSHARES, F_PENDSUBMIT14SHR, F_PENDSUBMIT15SHR, F_LASTINCOME, D_ORILASTDEDUCTDATE, F_COSTPRICE, F_LASTMANAGEFARE, F_APPORTIONRATIO, F_FARERATIO, F_BACKFARERATIO, F_MINBACKRATIO, F_ORIFARERATIO, D_CYCLESTARTDATE, D_CYCLEENDDATE, D_CYCLENEXTDATE, L_CYCLE, F_EVENNETVALUE, C_PROTECTFLAG, F_BACKFARE, F_COSTFARE, C_LASTOUTFUNDCODE, F_REDEEMEDBASESHR, L_INCSERNO, F_LASTDEDUCTNETVALUE, F_LASTDEDUCTASSET, F_NEWNETVALUE, F_NEWASSET, C_ACTCODE) VALUES ('*', 'F6', 'F60000000031', '002', '002', '110002003', fundcode, 'A', '20180914', replace(lpad(i,20),' ','0'), '0', '10000.50', '20180914', '10000.50', '10000.50', '1', '0', '1', '1', '0', '0', '0', ' ', '6449654002077306883', ' ', NULL, ' ', NULL, '0', NULL, NULL, NULL, ' ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '1', NULL, '0', '0', '0', '0', NULL, NULL, NULL, NULL, NULL, '0', '0', NULL, '0', NULL, NULL, '6449654002081501184', '0', '0', '0', '0', ' ');
end loop;
commit;
end insert_sharedetail;
/

create or replace procedure update_sharedetail is
i number;
fundcode VARCHAR2(10);
begin
update xyz_test set c_fundcode = '027010';
commit;
end update_sharedetail;
/

create or replace procedure delete_sharedetail is
i number;
fundcode VARCHAR2(10);
begin
delete from xyz_test;
commit;
end delete_sharedetail;
/

create or replace procedure sharedetail is
i number;
fundcode VARCHAR2(10);
begin
i:=0;
loop
i:=i+1;
if i>200 then
exit;
end if;
insert_sharedetail();
update_sharedetail();
delete_sharedetail();
end loop;
end sharedetail;

/


-- 调用测试，在纯SSD笔记本下，执行大约500秒，在HDD下可能会半个小时左右。
call sharedetail();


-- 查询被重用且start_scn不正确的xid，如果记录为空，代表没有被覆盖，如果不为空，代表有被覆盖。
-- 有可能一次运行不出现，多跑两次一定会出来。此时as of timestamp会有两条相同记录，闪回不应该如此。
select * from dba_flashback_archive_tables where table_name='XYZ_TEST'; -- 查询对应的闪回归档历史表
select to_char(scn_to_timestamp(startscn),'yyyy-MM-dd HH24:mi:SS:FF'),
to_char(scn_to_timestamp(endscn),'yyyy-MM-dd HH24:mi:SS:FF'),
to_char(scn_to_timestamp(nextstart),'yyyy-MM-dd HH24:mi:SS:FF'),
to_char(scn_to_timestamp(nextend),'yyyy-MM-dd HH24:mi:SS:FF'),
startscn,
endscn,
nextstart,
nextend,
errxid,
a.*
from (
select lead(startscn, 1, dbms_flashback.get_system_change_number) over (partition by c_cserialno order by endscn, startscn) nextstart,
lead(endscn, 1, dbms_flashback.get_system_change_number) over (partition by c_cserialno order by endscn, startscn) nextend,
lead(xid, 1, null) over(partition by c_cserialno order by endscn, startscn) errxid,
a.*
from SYS_FBA_HIST_116447 a) a -- SYS_FBA_HIST_116447为上面查询出来对应的闪回归档历史表
where endscn < startscn or nextstart < endscn or nextend < nextstart
order by a.c_cserialno, a.endscn, a.startscn;


若上述信息不够精确，相关同学也可以直接QQ联系我，740989022。十分感谢！