Moving Data from One Server to Another

I receive new server with better hardware and want to move the production database from the old server to the new server.
basically want to migrate from oracle 9i release 2 to oracle 10G on the new server with minimum down time i have four schemas (40GB data in total).

What is the best way to move this to the new server with minimum downtime.
LVL 28
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

do the following

Step 1: On old server export database onto a XXX.dmp file 9i exp command
step2: ftp the xxx.dmp file to new server
step3: import the dmp file using imp command of 10g

this is the easiest

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Backup/restore is the best option with 40GB of database, even though export/import will not require you to have both 9i and 10g on the same system.
Your current scenario is migration from 9i to 10g and also change of hardware. You will need both 9i and 10g on any one of the systems.
1. backup from old system
2. install 9i and 10g on new system
3. restore the backup onto new system
4. upgrade from 9i to 10g on new system
5. uninstall 9i on new system.

You can also do the other way:
1. Install 10g on old system
2. upgrade from 9i to 10g
3. backup 10g database
4. restore the backup onto new system

Hi Badri,

What is the need of instaling old version and upgrade it to new version? Its criminal wastage of time and also Oracle export/Import is existing for the same exact reason- to copy the database from a lower version to upper version
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imran_fastAuthor Commented:
Creating dump will take 3 to 4 hours i have 26 GB of images data that alone will take 6 hours so total of 10 hours and import will take around 10 hours as well so you see the downtime will be 24 hours.

bot version on same machine then upgrade ok.

any other suggestion i want to minimize down time .
You can do the tasks in pahses
phase 1). Move the 9i database to new server
phase 2). upgrade the 9i database to 10g (on new server)

Phase 1).
1). Take the cold backup of 9i database
2). Restore it on the new server
3). Test that every thing is OK on new server.

-- downtime will only be the time to take cold backup and restore

If phase 1 completes then go for phase 2. Don't mix both tasks. Because if something goes wrong, then it will be very difficult for you to trace what happened.

Run the 9i database on new server for one or two weeks. if everything is OK, then plan the downtime for upgrade. In this way, your users did not feel downtime of 10 hours.
May be for phase 1 your downtime is 6 hours and for phase 2 your downtime is again 6 hours. so your users feel that the downtime is 6 hours only (but off course actuall y it is 12 hours for example)

Phas 2).
1). Use DBUA to upgrade the database. or
2) use manual upgrade method

The best option would be

If it is same plarofrm.. then

++ Install 9i on new server
++ use transportable tablespace option to move data  
++ Now upgrade to 10g on new server.


++ upgrade to 10g in old server
++ Install 10g on new server.
++ use transportable tablespace option to move data (in 10g you can do it in cross platform also)

with the above you will be saving lot of time
Hi ramumorla,
   export/import is a time consuming process and does have lots of implications.
Can you guess how much time will it take to export and how much more time is needed for an import of a 40GB database?
I accept the fact that export/import is a very good tool for upgrade but this option looses battel when the volume is high.
How can you say export import is a time consuming process? Are you doing on the network or on the sever? Please read my steps,  I wanted to run the export on the server itself.
Below metalink references may help you

Subject:       How To Use RMAN CONVERT DATABASE for Cross Platform Migration
        Doc ID:       Note:413586.1

Hi ramumorla,
    Yes, I have tried many times, export of a 5 GB of data is completed in less than one hour and then import takes 4-6 hours on our high end database servers on the same host.
Import is always 4-6 times slower than export. You may try on large volumes once.
I repeat, export/import is one good tool for smaller volume upgrades.
imran_fastAuthor Commented:
Hi the export is taking 6 hours and the import is taking the same.

Let me look in to the transportable tablespace option.
Use datapump instead of export/import.  It's much more efficient.  It will take a while, but it is much better than export/import is.  Plus, after 10g, export/import will no longer be supported.  We just migrated our database (3 terabytes) from one server to another and it took all day, but datapump was our only solution.
Export data pump is not available for 9i
Export import is the only way to do this in the most cleanest and simplest way, while if you consider time required to install 10g on the old machine and upgrade , backup, restore on a new machine, export is the simplest way... Please go through the process below.


To obtain the cleanest possible export, run INVALID.SQL script to check for invalid objects on 9i DB (source server) and run VALIDATE.SQL (UTLRP.SQL?)to recompile any invalid objects

Export the 9i DB

Shutdown the 9i DB to stop users from establishing a session

Copy the Oracle 9i EXPORT.DMP and IMPORT.PAR files to the 10g DB (destination server)

Ensure tablespaces have been created/configured in the 10g DB to coincide with the 9i DB

Take the 10g DB out of ARCHIVELOG mode

Import the 9i DB into the 10G DB

Check import logs for errors

Reset passwords for system (and other) 10g accounts back to the original 9i DB accounts

Make sure users are pointing to proper tablespaces

Run INVALID.SQL script on 10g DB again to check for invalid objects and run VALIDATE.SQL (UTLRP.SQL?) to recompile any invalid objects

Recompile all PL/SQL modules that may be in an INVALID state, including packages, and types on 10G DB using UTLRP.SQL (is this same as above?)

Check the local applications and DB links on the 10g DB

Ensure MS Access apps are able to connect and work

Give the Oracle Devs the OK to change the hardcoded connect string from the old to the new

Give the MS Access Devs the OK to change the ODBC Connect String from the old to the new

Deploy the new Oracle registry key LOCAL default parameter key connect string to all WS

Put the 10g DB back into ARCHIVELOG mode

Verify the nightly backup client is configured properly

Monitor the alert log and dump directories for possible issues
imran_fastAuthor Commented:
Ok i have decided to go with import export the reson why i choose that is

I have 30 GB of images and 10 GB of data the data is taking almost 2.5 hours to export and images are taking 4 hours.
What i have decided is to import the images on the new server and start tracking the new images.
once the images are imported i will stop the user from accessing the old server and export the data.
Import the data
import the new images may be 1 gb
and then get the user back online

After importing it to the new server do i have to rebuild the indexes on the table or not?
Imran, All the constraints/Indexes will be built for you withoiut you doing anything through import, But to make sure, just go through the logs that are generated by exp/imp commands
imran_fastAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.