The requirement details are as below,
1) The database contains static as well as transactional data for telecom domain.
2) We have to move region-wise data from one database to another.
3) There are around 10 regions.
4) The region wise data extraction from source db is based on around 40 to 50 tables. Rest of the tables contains
static data & it will not change.
5) The volume is around 1 million subscribers per region.
6) The migration is required because
a) The client is upgrading its base product which uses this database
b) There is a change in structure of static tables
c) Hardware upgrade
d) The client want to start with single region on new database & rest of the regions will be operated from old
7) Keep execution time to very minimum.
I am thinking to have solution as below,
1) Create destination database with upgraded db structure (as mentioned in 6b)
2) Create database links to access source db in destination db.
3) Write SQL queries to fetch data from all the respective tables for a specific region
4) Write separate PL/SQL blocks for each table to fetch data from source db & insert into respective table in
a) Use FOR ALL & bulk collect to improve the performance
b) Divide table data into multiple chunks & execute parallel batches (around 10) to insert the data
5) Validate pre & post counts to verify the success of migration
you are talking about "one database and another". Are these both Oracle databases? Which version? I am asking because this forum is dedicated for migrations from non-Oracle databases to Oracle, using the SQL Developer Migration Workbench.
Is the data transfer a one-off transfer, or do you need to transfer (replicate) the data in regular intervals? Maybe that Oracle GoldenGate, at http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html , can be helpful for you.
The source are destination are both Oracle11g databases.
Do I need to use different forum? Please provide forum name.
There will be one time data transfer for each region.
Once the data for all the is regions is transferred from source to destination, only destination database/system will be operational & source database/system will be of no use.