Re: rollback schema changes
Date: Sun, 27 Mar 2011 16:39:35 -0700 (PDT)
On Mar 26, 5:36 pm, John Hurley <hurleyjo..._at_yahoo.com> wrote:
> # 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?
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.
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.) Received on Sun Mar 27 2011 - 18:39:35 CDT