Browse oracle-l: This Month's Archive • Main Archive Page • Related posts • Previous by Date • Next by Date

Re: Advise needed on moving data with table changes

From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
To: jdunn@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 4 Dec 2006 02:45:45 -0800 (PST)

>>We have a new version of an application which involves lots of changes to
>>tables, e.g. columns added, columns deleted, columns renamed, columns moved 
>>to new
>>tables. We needed to upgrade the live system and move the data to the new 
>>table
>>formats. What is the best approach here? What oracle utilities will handle 
>>this best?

John

Key things to consider:

- how much is 'lots' of changes? how much risk is there? how risk averse are 
you (and your organisation)?

- Do you have existing tool support for ddl and data migration (eg Oracle 
Designer can generate your DDL for structural changes - but not for data 
upgrades)

- What is (how long is) your upgrade migration window - a weekend? end of 
quarter? and how many opportunities are there? I once worked on a migration 
that could only take place at the end of quarter 1 or quarter 3. Miss and 
there's a six month wait.

- How many environments need upgrading? Dev/Test/Prod at least; do you have 
multiple instances of the app?

- What is your capacity 'turning circle'? How much spare capacity (cpu, disk, 
etc) do you have to use during the migration? Do you need to (would it be cost 
effective to) hire in additional capacity?

- How would you undo the changes if anything went wrong? (environmental 
failure; migration problem; etc). 

- And how will you know if anything went wrong? How will you confirm the 
migration succeeded? Eg for an ERP you might want to run a trial balance before 
and after

There are a zillion mechanisms you could use (and I'm sure others will come up 
with more):
- ALTER TABLE in place
- CREATE TABLE AS SELECT
- Export, import, ...
- SQL*Loader 
- ETL/EAI tools like DataMirror Constellar Hub / Informatica / DataStage / 
Oracle Warehouse Builder (it's not just for Warehouses you know) ...

etc etc. All can work very well; all will have implications on the effort 
expended, the time taken to actually migrate and the undo route. And you should 
consider the skills you have available too; better to do a good job with a tool 
you understand than a dodgy job with a 'better' tool that you haven't fully 
understood.

The right answer for you is not necessarily the same as the right answer for 
someone else. So treat this like any development project and gather the 
requirements and constraints first before diving into which widget to use.

And remember: test, test and test again!

Good luck

Regards Nigel

Re: Advise needed on moving data with table changes

Other related posts: