Re: Flashback Schema ... any ideas

Create Data Guard as a physical standby. Stop redo apply to standby before changes. If the changes need to be reverted. Open the standby read write and datapump the old schema back using a network_link. Afterwards continue redo apply to the standby.

This is how you avoid taking the logical schema datapump backup before changes.
If something goes wrong you still need to replace the whole data in the schema. Even if less than 1% is changed.

--
Timo Raitalaakso
http://rafudb.blogspot.com

22.2.2012 17:17, Mihajlo Tekic kirjoitti:


> 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

>

> --

> http://www.freelists.org/webpage/oracle-l

>

>


--
http://www.freelists.org/webpage/oracle-l