我們有個系統使用了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。十分感謝！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/54012.html
標籤：高級技術