Flashback Schema ... any ideas

From: Mihajlo Tekic <mihajlo.tekic_at_gmail.com>
Date: Wed, 22 Feb 2012 09:17:05 -0600
Message-ID: <CAGWRspZdtT+dB83MBku1UC2fB4CSq=ognQRr6b9mud1hCd0wiA_at_mail.gmail.com>

Hello everyone,
Has anyone looking for solutions that perform flashback schema similar to flashback table?

The solution we are looking for should be able to rollback changes done during schema evolutions or some batch jobs. Hence it should be able to handle DML, DDL and DCL changes.

We are looking for solution applicable to 10.2 and 11.2 databases.

At this moment we export the schema (DataPump) before running the evolution scripts and re-import back in a case we need to roll back the changes. Although this may work nice for smaller schemas it does not for bigger ones since the entire schema needs to be imported compared to the overall changes made to the data set (sometimes less than 1% of the data is affected and needs to be rolled back)

“Do It Yourself” solution is always an option. We currently consider the following:

  1. Use flashback table to rollback entire schema to a certain point in time (using guaranteed undo retention)
    ------>Prior to flashback, disable all referential integrity constraints,
    flashback tables, enable constraints back (users are not allowed to establish connections while flashback is in progress)
    ------>Cons: DDL and DCL changes cannot be rolled back.; Requires
    additional UNDO;
  2. Use TTS
    ------>Export tablespaces of the affected schema using TTS. Run the
    evolution scripts. Re-import the tablespaces in a case rollback is needed
    ------>Cons: Standby databases may require additional attention; may not be
    feasible for tablespace is shared with a schema that is not affected by the change

It looks like in 11.2 there we have more flexibility with the options available, but again, more or less it should be Do It Yourself and rolling back DDL/DCL changes is still a challenge.

At the end, one can argue that for every evolution script there should be a rollback script that will undo the changes. I agree with that, however in the real world, these scripts may contain flaws hence other options should be available.

Has anyone of you implemented solution for the same problem? Anyone aware of other solutions?



Received on Wed Feb 22 2012 - 09:17:05 CST

Original text of this message