3

Wanted to do a clean test on Flashback Data Archive so had to remove the existing.

I disabled FDA on all enabled tables and dropped FDA.

But still i see the record in DBA_FLASHBACK_ARCHIVE And DBA_FLASHBACK_ARCHIVE_TS

If i attempt to drop the tablespace, i get "ora-55641 cannot drop tablespace used by flashback data archive". When i drop FDA its successful and no errors.

I tried alter flashback data archive purge and i still could not drop TS

alter flashback archive fla1 purge all;

Then i tried to alter FDA and see if i can delink - alter flashback archive fla1 remove tablespace tbs2; I get the error "ORA-55626: Cannot remove the Flashback Archive's primary tablespace"

Can i know if any internal tables has to be cleaned.

wolφi
  • 8,091
  • 2
  • 35
  • 64
mediocre_10
  • 61
  • 2
  • 1
    To the user that has voted to close this question: I would like to keep the question open. While the day to day maintenance of Flashback Data Archive is truly a task for a database admin, we developer need to know that it exists an gather experience while trying it out. So often I have seen people writing lots of code with the same functionality as a built in Oracle feature because they don't know that the feature exists. – wolφi May 12 '20 at 09:27
  • Cannot reproduce. Can you show us the `CREATE` statements you've used? – wolφi May 12 '20 at 10:07
  • I am experiencing the same issue; Oracle 12.2, I've had issues with flashback data archive in a development instance, and trying to get rid of it and getting same issue. I'll likely need to open a ticket with Oracle. – Mark Stewart Aug 31 '20 at 22:12

1 Answers1

1

I had a similar situation; in my case I had been having some issues with Flashback Data Archive (Also Known as FBA, FBDA, FDA) with some queries, possibly due to materialized views. So to solve that problem, I dis-associated those tables from the FBDA, using DBMS_FLASHBACK_ARCHIVE.DISASSOCIATE_FBA(owner_name, table_name) which solved the performance problem, but of course those tables were no longer tracked.

Now, I've given up on the FBA on that instance for now, and when cleaning up, I hit the same ORA-55641 and also ORA-55626 when trying to clean up.

What I ended up doing was to re-associate the tables to the flashback data archive, and also (not sure if this was needed, but ...) I purged all records:

alter flashback archive ARCH_FLASHBACK_10_YEAR purge all;

-- Identify tables tied to the flashback archive; mine was named ARCH_FLASHBACK_10_YEAR 
select owner_name, table_name, FLASHBACK_ARCHIVE_NAME, 
    ARCHIVE_TABLE_NAME, status from DBA_FLASHBACK_ARCHIVE_TABLES;

-- look for ones with a STATUS of "DISASSOCIATED" and do the next two 
-- statements for those tables

exec DBMS_FLASHBACK_ARCHIVE.REASSOCIATE_FBA ('YOUR_OWNER', 'YOUR_TABLE')
alter table YOUR_OWNER.YOUR_TABLE no flashback archive;

-- Then query again.  When clean:

alter flashback archive ARCH_FLASHBACK_10_YEAR remove  tablespace ARCH_HIST;
drop  flashback archive ARCH_FLASHBACK_10_YEAR;

-- And if ARCH_HIST has no other data:
drop tablespace ARCH_HIST including contents and datafiles;

And after doing those steps I was able to drop the flashback archive, and drop the tablespace associated with that flashback archive.

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32