rollback schema changes

#11

On Mar 28, 2:33*am, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:

Quote:

Nuno:

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

Is there a button in OEM to do that?

LOL!

#12

On Mar 28, 1:39*am, onedbguru <onedbg... (AT) yahoo (DOT) com> wrote:

Quote:

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

#13

Mladen Gogala wrote,on my timestamp of 27/03/2011 3:57 PM:

Quote:

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

Your very good health, my friend!

#14

The Guru:

# Not everyone CAN do an export... and restoring some of these
databases can also be out of the question.

Where exactly did I say that an export/import would work in "EVERY"
case?

# *Depending on the schema changes, the developers may need to write
that "UNDO" script in addition to the "update" script.

You are arguing the obvious here. Any major operation probably has a
QA process and an undo script is probably necessary ( and hopefully
well documented and already been tested for different failure
scenarios ) ...

But we are often dealing in this newsgroup with people that do not
( for various reasons ) understand the basics many times. That's just
the way it is.

#15

On Mar 26, 9:25*pm, prunoki <hegyv... (AT) ardents (DOT) hu> wrote:

Quote:

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

If the script drops objects, you could try the recycle bin.

Matthias

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

rollback schema changes

comp.databases.oracle.server comp.databases.oracle.server

Re: rollback schema changes - 03-27-2011 , 09:34 PM

Re: rollback schema changes - 03-28-2011 , 03:25 AM

Re: rollback schema changes - 03-28-2011 , 04:36 AM

Re: rollback schema changes - 03-28-2011 , 06:30 AM

Re: rollback schema changes - 03-28-2011 , 10:31 AM