Re: rollback schema changes

From: Tim X <>
Date: Sun, 27 Mar 2011 11:19:30 +1100
Message-ID: <>

prunoki <> writes:

> Hello,
> Is there a way to rollback schema changes, other than restoring from
> backup until a specific time or SCN? The database in question is 10g.
> I mean without writing a complete script to undo the changes of
> course.
> The goal would be to undo the effects of a faulty script which
> generates an error midway, but has made several alter tables and such.
> If I want to run the full, corrected script again, it would be nice
> not to spend a day performing a restore.

Are you saying it takes a day to refresh your development systems from production snapshots? You must have a *huge* database. We run several fairly large instances and it only takes an hour or two to refresh a dev or uat system from current production data.

I suspect the real answer to your problem is to investigate how you refresh development/testing systems from current production data. This should be a reliable and reasonably fast process to encourage thorough testing of scripts etc. Without such a process, developers will be tempted to run scripts in production systems that have not first been run in a test system which reflects recent production data and configuration. You will get the "No, we didn't run it in development after the last change because it was only a minor edit and it would have taken another two days to get the fix into production." syndrome.

I would also suggest having multiple scripts and never mixing up DDL and DML in the same script. In fact, I would argue you need to also consider writing the 'undo' script. Despite thorough testing, things can still go wrong. If your restore process takes a day, the undo script may be the only acceptable way to rollback the changes, getting the prod system back into a known 'good' state and buying you time to investigate what whent wrong. Far better to spend developer resources in testing and preparation than potentially cripple the business for a day while critical systems are being restored from backups etc. Frequently, a little additional scripting to set the stage for rolling back changes is much much better than relying on restores or export/import as it can be much faster (depending on what is changing of course).


tcross (at) rapttech dot com dot au
Received on Sat Mar 26 2011 - 19:19:30 CDT

Original text of this message