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.

Regards,

Krisztian

On Sat, 26 Mar 2011 12:25:44 -0700, prunoki wrote:

That's what an export is for. Before changes on a schema, always export
the schema. The people turned into enemies by the "web 2.0" would know
that. The dreaded "DBA" was expected to know how to manage software
installation, but the "DBA 2.0" is expected only to know which button on
the OEM to click. You, apparently, have the DBA 2.0 who didn't read the
installation plan and screamed when seeing that the plan doesn't include
the prior export.
Alternative is the "flashback database" command, which might work if your
undo tablespace is large enough. That, however, will not roll back only
one schema. If there is a physical standby, you can try it there, too.



--
http://mgogala.byethost5.com

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?

On Saturday, March 26, 2011 10:20:04 PM UTC+1, Mladen Gogala wrote:
Thanks a lot, going to check out flashback database.

K

prunoki <hegyvari (AT) ardents (DOT) hu> writes:

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

John Hurley wrote,on my timestamp of 27/03/2011 8:36 AM:
You mean there is a button in OEM to do that?

prunoki wrote,on my timestamp of 27/03/2011 6:25 AM:

Other than a flashback database - which (assuming you have lots of free disk
space) does the lot, not just selected ones - I know of no other way than good
old export/imp. Or a recovery from backup.
One thing I do is to have our DR system ready to go. If a table or two are lost
from production or test by an errant script, I can always wind up the DR system
and copy/exp-imp the missing tables from there.

On Sun, 27 Mar 2011 15:13:58 +1100, Noons wrote:


Oh boy! Nuno, you're cruel. I thought that I was rubbing it in with "DBA
2.0" lament, but you're just bad!



--
http://mgogala.byethost5.com

Nuno:

# One thing I do is to have our DR system ready to go.

Is there a button in OEM to do that?

On Mar 26, 5:36*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
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.)