Re: rollback schema changes

From: prunoki <>
Date: Mon, 28 Mar 2011 01:25:54 -0700 (PDT)
Message-ID: <>

On Mar 28, 1:39 am, onedbguru <> wrote:
> On Mar 26, 5:36 pm, John Hurley <> wrote:
> > Mladen:
> > # That's what an export is for. Before changes on a schema, always
> > export the schema.
> > Nobody really exports schema's before making database changes do
> > they?  ( Done in sarcastic voice ... ).
> > Nobody really exports schemas and/or the whole database on a daily and/
> > or weekly basis just in case do they?
> John,
> Not everyone CAN do an export... and restoring some of these databases
> can also be out of the question.  Depending on the schema changes, the
> developers may need to write that "UNDO" script in addition to the
> "update" script.   I once worked at telco where we used ASM to move
> one of these V-V-VLDBs ONLINE from one storage array vendor to another
> storage array vendor which took 27 DAYS at a rate of 300GB+/hr WHILE
> adding 1TB/day... Using storage array technology, we recovered back to
> a point in time in just a few minutes during a test.
> prunoki ,
> As others have pointed out, IF you have LOTS of additional storage,
> use FLASHBACK database which we have used in some of our regression
> test databases. Beware, this tool restores ALL schema in the database
> back to that point in time.  If the database/schema are small enough -
> use expdp.  Also for dev/test environments look at the REMAP_*
> parameters of expdp/impdp, there is a lot you ca do without having to
> write user create scripts (except for any permissions on SYS. schema.)

The DB in question is around 1.4TB. Copying over from production is not feasible, sometimes we migrate 10-14 versions in 4-7 weeks and loosing all the configuration the users do during that time is not acceptable. The database is "ours", so it is not a problem if we restore the whole beast to the time before the migration. I do not know how much space you need for that, given that no one uses the db during the scripts and the scripts themselves take only 10-15 minutes to finish. Most of the time they do not even change lots of the data. Flashback is a way to go back in time without the hassle of a full restore, it seems the way to go.

K Received on Mon Mar 28 2011 - 03:25:54 CDT

Original text of this message