Re: Flashback Schema ... any ideas

From: Niall Litchfield <>
Date: Wed, 22 Feb 2012 16:14:20 +0000
Message-ID: <>


I don't have a solution for 10.2 at all. For 11.2 and above the new Edition functionality will probably handle the schema evolution part of your requirement correctly. For the batch jobs I imagine you might need to implement savepoint functionality and conditional logic in the batch.


On Wed, Feb 22, 2012 at 3:17 PM, Mihajlo Tekic <>wrote:

> 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?
> Thanks,
> Mihajlo
> --

Niall Litchfield
Oracle DBA

Received on Wed Feb 22 2012 - 10:14:20 CST

Original text of this message