Re: Flashback Schema ... any ideas
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: mihajlo.tekic@xxxxxxxxx
- Date: Wed, 22 Feb 2012 16:14:20 +0000
Mihajlo,
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.
regards
Niall
On Wed, Feb 22, 2012 at 3:17 PM, Mihajlo Tekic <mihajlo.tekic@xxxxxxxxx>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
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
Other related posts:
