vijaya,
the ideal way of limiting down time is to design a process that :
1) unload the legacy databases while they are available to users
2) load the oracle database
3) shtudown legacy systems
4) unload ONLY the changes from the legacy databases that were made since the unload in step 1 (insert,update & delete triggers an legacy tables canbe used to id changes since last unload)
5) load the oracle database
note that steps 4 & 5 can be iteratively (ofcourse without the shutdown) until the amount of data remaining is manageable
since it is often difficult to identify changes from a certain point in time (this has to done at the application level) the above steps may not be possible. But often times you can take this approach for some of your data, there by reducing some of the downtime of the conversion.
you need to determine which tables will be on the critical path. ie which are your largest tables.
for the very large tables find some way of logically splitting them into smaller chunks that can be unloaded in parallel
be sure the network connectivity between you machines is at its optimum. if the network speed is the bottleneck, parallelism will not help you. it may be worth your while to have the machines physically connected with multiple NICs
you need to design a process and run it to determine where you bottlenecks are so that they can be addressed
piping is also a useful tool to reduce data transfer times. for example, send the unload (eg bcp) output to stdout and pipe that directly into the sqlldr process.
timing is important. take advantage of index build time to transfer other table data into oracle (especially useful if the network is you bottle neck)
design you big oracle tables such that partitions are indendent of each other (ie no global indexes) so that they can loaded & indexes built indepednently of each other
have multiple temp tablesapces that reside on different controllers that each loader/index builder will also help
i could go on forever. what to do really depends on teh specifics of your system. but you will need to test & try different techniques to determine whta works best for you
btw, i dont know of any doc (other than the std oracle docs) that may help you
good luck,
steve
| "Vijaya Chander V.S" <[EMAIL PROTECTED]>
01/30/2003 11:57 PM
|
To: <[EMAIL PROTECTED]> cc: Subject: Hi Stephen Evans |
Hi Stephen Evans,
Thank you for your earlier response to the migration issue.Here i want to eloborate this specific requirement
so that you can advise us more.The requirement goes like this:
We have a Sybase database and one non-proprietory DB in our old system and these two database are being
used by multiple applications. Now a new application was built which has all the features of old applications with some
enhancements which works on Oracle 9i DB(New DB). Our task is to migrate the data from Sybase and
non-proprietory DB's to Oracle.
1.An important thing is that the data models of all the 3 DB's are different, i.e., column data in in old DB's may
not go into same table in new DB, it may go into other table's column.
2. Some objects like procedures/functions/packages need to be moved into new DB.
These are some sugggestions/solutions for the above requirement:
Sybase to Oracle
1. Oracle Migration Work Bench(OMWB) is very good at transferring objects & data from sybase to oracle.
Its a free tool from Oracle and if needed we can download it for testing.
2. Another option is to use Bulk Copy utility(BCP) to get the data out of Sybase into some flat files,
then use SQL*Loader to get the data into some Oracle staging tables. Then use pl/sql procs to move
the data to it's final destination.
Non-proprietory DB to Oracle
If we need to migrate from non-proprietory db to oracle then writing some programs in non-proprietory
language which can dump data into files and then using SQL*Loader to get the data into the production tables.
Please give your suggestions like which is better way of doing it as we need to do this migration with very less downtime.
Can we parallelyze this process of data loading. If yes how? Please forward some pointers like documents or
URL's to howto go about this migration and how much effort goes into it.
> Regards,
> Vijaya Chander V.S
> Oracle DBA
> Infosys Technologies Limited
>
-----Original Message-----
From: Stephen Evans [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 30, 2003 PM 07:25
To: Multiple recipients of list ORACLE-L
Subject: Re: data migration strategies
vijaya,
partition your bigger tables so that they can be loaded by sqlldr via direct path in parallel
drop indexes (especially global indexes) before loading
do some testing first to determine optimum number of parallel streams from a cpu usage standpoint and an io contention standpoint.
try and separate segments that are loaded in parallel by disk & controller
consider striping (raid 1+0) across 7 devices or so.
good luck,
steve
"Vijaya Chander V.S" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
01/30/2003 03:58 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: data migration strategies
Hi,
Thank you for your responses.
What kind of strategies should be followed while migrating data from Sybase to Oracle
and non-propriatory databases to Oracle so that the down time is as low as possible.
The Sybase database size is 2TB and other DB's total make up to 400GB
any pointers will be helpful to us...
regards,
Vijaya Chander V.S
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vijaya Chander V.S
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
