10 Replies Latest reply: Mar 10, 2010 4:05 PM by 757050 RSS

    Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang

    65484
      alter table Schema.table no flashback archive...Hangs

      This command will run but Oracle is taking a very long time to actually remove the table
      from Total Recall or even it hangs for 4-5 days.
      You can query dba_flashback_archive_tables and it will still the table is there


      When we tried to re-issue the ALTER TABLE command to disable flashback archive,

      we got ORA-55602: The table "Schema.Table" is not enabled for
      Flashback Archive.


      If you try to re-enable the table for Total Recall you get an error

      ORA-55624: The table "string"."string" cannot be enabled for Flashback Archive at this point
      Cause: An attempt was made to enable Flashback Archive again on a table which was just disabled.
      Action: Try again later.

      Again suggesting that a process is falling behind and just needs to catch up.

      I Guess FlashBack Database Archive (FBDA) process is slow writing to the system tables....
      and if thats not finished we cant disable the recall...Can we make this process fast

      Have anyone encountered this before....


      Thanks in Advance
      Gagan
        • 1. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
          316713
          Hi Gagan,

          Did you ever find any solution for your issue? What was the problem?

          I am facing same issue and there is not much help out here.

          Thanks
          Yogesh
          • 2. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
            757050
            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?
            • 3. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
              Loek
              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.
              • 4. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                316713
                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.
                • 5. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                  Loek
                  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]

                  (quote)
                  1.5.1.1 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.
                  (end quote)

                  I'm confused, since in my experience and that of other people I asked, it does not do what is claimed here.
                  • 6. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                    316713
                    Thanks for the info.

                    This is very interesting. The restrictions I stated are from 11g R1, which is what we are using right now.

                    But the documentation you mentioned is from 11g R2. It looks to me that Oracle has removed most of the restrictions in the new release.

                    Have you tried the 11g R2 yourself?
                    • 7. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                      Loek
                      We are using 11.2.0.1.0, that's why I find it puzzling. We are still researching the issue, and also working with Oracle support on the issue. I will post my findings as things move forward.
                      • 8. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                        316713
                        Thanks.

                        I will try to install the new release and test it myself.

                        Meanwhile, I will appreciate if you can please keep this forum updated with your findings.

                        If the database starts behaving true to the documentation, then suddenly this option will be very valuable to lots of people.
                        • 9. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                          Loek
                          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.
                          • 10. Re: Oracle 11g Total Recall: alter table Schema.table no flashback archive.Hang
                            757050
                            Loek is right.

                            11G Release 2 (11.2.0.1) 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 11.2.0.1.


                            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:

                            Linux x86
                            Linux x86-64
                            Solaris (SPARC) (64-bit)
                            Solaris (x86-64)
                            HP-UX Itanium
                            AIX (PPC64)

                            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.

                            Thanks,
                            Khushboo Srivastava