Re: rollback schema changes

> 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).

Tim

-- 
tcross (at) rapttech dot com dot au