Flashback Schema ... any ideas
- From: Mihajlo Tekic <mihajlo.tekic@xxxxxxxxx>
- To: oracle-l <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 22 Feb 2012 09:17:05 -0600
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
Other related posts:
