>>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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Dec 04 2006 - 04:45:45 CST
