Re: Data Migration options - oracle to oracle

So I'd say acually you need separate project called let's say "customer data migration", and you need custom code for that. Data pump/databse link/old imp-exp/goldenGate/ whatever other data movement mechanism is only a small part of the problem. If you have hundreds of tables and serious business logic, I cannot imagine how automated tool can make some automated data movemen from one tables to many and consolidate from many tables to one. That sounds like artificial intelligence, which is not very developed at least now :)

> Most of the target tables have Triggers enabled, that need to capture
> migration data for audit.
>

Actually you don't. If the functionality of audit is known, you can easily simulate it using some procs/scripts. If the trigger simply copies each row to another audit table, you don't need to do that on the row by row basis. You can write a simple insert that AFTER the current business data load simply copies ALL new data to audit tables all together. It would be muuuch faster. Of course you have to disable application while you are loading business data.

>
> Given that we need to migrate every month after we go-live, there will be
> existing customers who will be using this application tables. So, we will
> not able to disable constraints/indexes/triggers on Target database.
>

I'd say there are more questions:
1) what will be after the customer is migrated - only new data source will be used? If no how you will migrate changes made in the source? 2) Do you have some common info, for example, classifiers? What if someone changes them in source AFTER you have started overall migration process? I.e. before each migration stage most probably you need to synchronize them. 3) Adding new data is wayyyy muuuch easier in empty schema than schema that already contain data. If it already contain data, you have many problems like:
- identifiers generated from sequences are overlapping - natural identifiers for the same objects are different e.g. "system a" has Jhon Smith with SSN 545454564, but "system b" has John Smyth with SSN 545454564. Are they the same?
- identifiers are the same, but the meaning is different e.g. London, but one in KY, the other in OH, and noone in UK :)

>
> If we had all tables partitioned by customer, this would have been easier
> to load staging table and exchange partition. Unfortunately, I was told
> that option is maintenance nightmare.
>
> This anyway doesn't sound usable considering "50% of table data to target
database need transformation [..]"

Bottomline, we need to migrate only data from source to target db without
> impacting production system with performance to migrate 100 customers
> data(450 tables for each customer) in 24 hours.
>
> Umm, people and management always want something. Question is - are their
requirements real? Even if they are real, how much it costs? It could be that your management cannot imagine what kind of problems actually migration projects have. So either you or some other person have to collect all of them and provide info why these are serious problems. Even better (for most management types) for each problem provide at least one solution, telling them how it could be solved. If you have more than one solution you can compare them.

Some years ago I wrote a paper about all that here http://www.gplivna.eu/papers/legacy_app_migration.htm Since then I participated in a pair of other projects involving data migration and we used more or less the same approach.

Gints

--
http://www.freelists.org/webpage/oracle-l