Oracle-L: Re: data migration strategies

From: Stephen Evans <evans036_at_mc.duke.edu>
Date: Fri, 31 Jan 2003 06:58:58 -0800
Message-ID: <F001.005405DE.20030131065858@fatcity.com>

vijaya,

the ideal way of limiting down time is to design a process that :

unload the legacy databases while they are available to users
load the oracle database
shtudown legacy systems
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)
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" <srinivasa_v_at_infosys.com> 01/30/2003 11:57 PM

        To:     <evans036_at_mc.duke.edu>
        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

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.
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:evans036_at_mc.duke.edu]
                 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" <srinivasa_v_at_infosys.com>

Sent by: root_at_fatcity.com

01/30/2003 03:58 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

        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: srinivasa_v_at_infosys.com
 
                 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: ListGuru_at_fatcity.com (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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Evans
  INET: evans036_at_mc.duke.edu

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: ListGuru_at_fatcity.com (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).

Received on Fri Jan 31 2003 - 08:58:58 CST