Hi Tom,

I have observed a limitation with Flashback Data Archive. Once Flashback Data Archive is disabled for a table, all the information in History table until that point is lost. Once Flashback Data Archive is enabled again for the same table, we get data from that point which means only incremental data is available.

Am I right? Does historical data is lost once we disable Flashback Data Archive (alter table <table_name> no flashback archive)? Here is one sample example :

create table test100(id number);

alter table test100 flashback archive TESTFDA2;
alter table test100 succeeded.

insert into test100 values (100);
update test100 set id = 1000;
commit;
select * from TEST;
ID
1000

select * from user_flashback_archive_tables where table_name = 'test100';
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
TEST100 TESTUSER2 TESTFDA2 SYS_FBA_HIST_167611 ENABLED

When I try to query the history table, I am getting output as below :
select * from sys_fba_hist_167611;
RID STARTSCN ENDSCN XID OPERATION ID
AAAo67AAQAAACXrAAA 137935804 137936204 00050018000249E4 I 100

I am able to query versions
select * from test100 versions between scn minvalue and maxvalue;
ID
1000
100

Until this everything is fine. Atleast I am able to query history table. But once I disable and enable again the problem comes.

alter table test100 no flashback archive;
alter table test100 succeeded.

select * from user_flashback_archive_tables where table_name = 'test100';
no rows selected (as FDA is disabled).

Now I am trying to enable again.

alter table test100 flashback archive TESTFDA2;
alter table test100 succeeded.

select * from user_flashback_archive_tables where table_name = 'test100';
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
TEST100 TESTUSER2 TESTFDA2 SYS_FBA_HIST_167611 ENABLED

When I try to query the history table, I am getting output as below :
select * from sys_fba_hist_167611;
ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
Error at Line: 17 Column: 14

Here I am not able to query at all. Because History table is not present in database.

Still I am able to query versions even though history table is not existing.
select * from test100 versions between scn minvalue and maxvalue;
ID
1000
100

The history table is showing in the above view, but not actually existing. It is coming into picture only when I perform any DML operations again. But it is not storing the previous data i.e. before disabling.

For eg, delete from test100;
1 rows deleted

commit;
commited

Now, I am able to query history table.

select * from sys_fba_hist_167611;
RID STARTSCN ENDSCN XID OPERATION ID
AAAo67AAQAAACXrAAA 137940222 137940222 00050008000249E7 D 1000
AAAo67AAQAAACXrAAA (null) 137940222 (null) (null) 1000

Finally when I try to query versions,
select * from test100 versions between scn minvalue and maxvalue;
ID
1000
1000

My questions are:
--> From above test case it is evident that upon disabling Flashback data archive, history is being lost. After enabling FDA again, history table is getting created but with new data. There may be situations in which Flashback data archive needs to be disabled and re enabled. Business cutover may take place in normal business days which means that concerned team need to extract data from table before disabling FDA. They may not be available all the time when needed, which may create data loss. Is there any way to overcome this?
--> After disabling FDA, history table is lost. Even after enabling FDA, history table is not created until fresh DML operations are performed. So how can I get data through versions query? Is it referring Undo table? Also please note that last versions query's output is different.

Please clarify on this.

Thanks,
Srikanth