RE: Flashback Schema ... any ideas
- From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
- To: <mihajlo.tekic@xxxxxxxxx>, "'oracle-l'" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 22 Feb 2012 10:27:31 -0500
How about flashback database? Are changes being made to other schemas?
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mihajlo Tekic
Sent: Wednesday, February 22, 2012 10:17 AM
To: oracle-l
Subject: Flashback Schema ... any ideas
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
-----
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2113/4825 - Release Date: 02/22/12
-----
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2113/4825 - Release Date: 02/22/12
--
//www.freelists.org/webpage/oracle-l
Other related posts:
