CODEWITHSUNDEEP
oracle-databaseoracle12cflashback
mediocre_10
mediocre_10

Reputation: 61

Cannot drop Oracle flashback data archive

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.

Upvotes: 3

Views: 1368

Answers (2)

balaji polakampalli
balaji polakampalli

Reputation: 335

Once a table is disabled for FDA, the table is marked for FDA purge. It requires some time for the associated HIST and TCRV tables to be dropped. This task is done asynchronously. So if you try to drop the Flashback Archive, then you might run into this issue. You should wait for some time (atmost 5 mins) before you drop the flashback archive. This also extends to dropping the table space associated with flashback archive.

Upvotes: 1

Mark Stewart
Mark Stewart

Reputation: 2098

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.

Upvotes: 2

Related Questions