Re: rollback schema changes
From: prunoki <hegyvari_at_ardents.hu>
Date: Mon, 28 Mar 2011 01:25:54 -0700 (PDT)
Message-ID: <4ddf6d73-902e-44a6-8157-3cba0785020c_at_k30g2000yqb.googlegroups.com>
On Mar 28, 1:39 am, onedbguru <onedbg..._at_yahoo.com> wrote:
> On Mar 26, 5:36 pm, John Hurley <hurleyjo..._at_yahoo.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.)
Date: Mon, 28 Mar 2011 01:25:54 -0700 (PDT)
Message-ID: <4ddf6d73-902e-44a6-8157-3cba0785020c_at_k30g2000yqb.googlegroups.com>
On Mar 28, 1:39 am, onedbguru <onedbg..._at_yahoo.com> wrote:
> On Mar 26, 5:36 pm, John Hurley <hurleyjo..._at_yahoo.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 Received on Mon Mar 28 2011 - 03:25:54 CDT