This content has been marked as final. Show 10 replies
We are facing the same issue. We disabled Flashback archiving on a table then performed a DDL (adding a constraint).
When we try to re-enable Flashback Archive we get the error "ORA-55624: The table "USER"."TABLE" cannot be enabled for Flashback Archive at this point" .
When we try to enable it after 24-48 hours it works. Why does it take so long to re-enable Flashback Archive after a DDL is performed?
We are also stuck on this problem. It also appears if you do manage to re-enable flashback on the table after disabling it, you have lost whatever data was in the flashback archive at that point. It seems the "total recall" option so much advertised, only works if you never perform DDL on your flashback-ed tables. I don't know about you guys/girls, but I have never had a live system that didn't require some DDL changes over time. If you need to perform DDL on your structures you can pretty much forget about using flashback at this point in time it appears.
I'm hoping someone will disagree and point me to the solution to this problem.
Yes, you are right regarding losing the flashback if we need to run few DDLs on that table.
You can not
1. Drop a column
2. change column definition
3. Add or remove constraint
You can Add new columns.
So you need to make sure that you backup the archived data before you make any changes.
You can extract it using 'AS OF' queries into a new table.
This is not a very convenient way and restricts us in many ways. Thus it diminishes the 'advertised' benefits of FDA.
In addition, we are facing the above mentioned issue. It seems like a fresh bug. And it is pretty significant bug. Oracle is working on that issue right now for us (for last two weeks) and have not found any solution.
Fortunately for us, we are still in ST mode and can afford the wait.
That's what I have encountered to, in practice. However Oracle states:
[Oracle 11g Release 2 new features|http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm]
188.8.131.52 Flashback Data Archive Support for DDLs
Oracle Database 11g Release 2 (11.2) users can now use most DDL commands on tables that are being tracked with Flashback Data Archive. This includes:
Add, Drop, Rename, Modify Column
Drop, Truncate Partition
Rename, Truncate Table
Add, Drop, Rename, Modify Constraint
For more complex DDL (for example, upgrades and split table), the Disassociate and Associate PL/SQL procedures can be used to temporarily disable Total Recall on specified tables. The Associate procedure enforces schema integrity after association; the base table and history table schemas must be the same.
This feature makes it much easier to use the Total Recall option with complex applications that require the ability to modify the schema.
I'm confused, since in my experience and that of other people I asked, it does not do what is claimed here.
It looks like we have solved our problems; it was a combination of factors for us.
Originally in order to make DDL changes we would disable flashback, apply the DDL and then re-enable flashback on the table. This initally resulted in a crashed instance because of runaway growth of the undo tablespace. That in itself was caused by using a version 10 client on an 11g database (so don't try this at home kids). Once both the database and client used were upgraded to the latest 11 release, there was no need anymore to disable flashback while performing DDL. Table changes appear to work (add/drop constraints, add columns). We are still in the process of testing everything, but it's looking good so far.
Loek is right.
11G Release 2 (184.108.40.206) now does support DDLs on Flashback Data Archive enabled tables whereas 11G Release 1 does not support DDLs. You do not need to disable FBDA in order to perform DDLs on 220.127.116.11.
So I suggest that you upgrade your database to release 2.
However, Oracle has not released 11.2 version for all platforms. Only the following platforms are supported:
Solaris (SPARC) (64-bit)
Also, to re-iterate to what Loek said -- performing DDL on flashback archive enabled tables from a 10G client on a windows box will crash the instance by growing the UNDO tablespace out of bounds. You can't use 11G Release 1 client to perform the DDLs either.
We are on Linux 64 bit and I simply performed the DDL for the tables from the SQL client (11.2) on the linux server and it worked like a charm.